Database Migrations: Best Practices with Real Examples

What Are Database Migrations?

A database migration is a versioned, incremental change to your database schema — adding a table, renaming a column, adding an index, or changing a constraint. Migrations replace ad-hoc SQL scripts with a structured, repeatable process that every developer and every environment runs in exactly the same order.

Done well, migrations deploy without downtime. Done poorly, they lock tables, corrupt data, or leave your schema out of sync with your application code.

The Golden Rules of Migrations

1. 1Every migration has an up and a down — changes must be reversible
2. 2Migrations run in order — never edit a migration that has already run in production
3. 3One migration per change — keep each file focused
4. 4Test migrations on a copy of production data before deploying

A Basic Migration Structure

Most migration frameworks (Sequelize, Flyway, Liquibase, Knex) follow the same pattern. Here are the raw SQL equivalents:

Up migration — apply the change:

-- 20260501_01_add_phone_to_users.sql (up)
ALTER TABLE users
  ADD COLUMN phone VARCHAR(30);

Down migration — roll it back:

-- 20260501_01_add_phone_to_users.sql (down)
ALTER TABLE users
  DROP COLUMN phone;

Adding a Column Safely

Adding a nullable column is always safe — no downtime:

ALTER TABLE orders ADD COLUMN tracking_number VARCHAR(100);

Adding a NOT NULL column with no default on a large table locks the table while PostgreSQL backfills every row. Do it in three steps instead:

-- Step 1: Add nullable
ALTER TABLE orders ADD COLUMN tracking_number VARCHAR(100);

-- Step 2: Backfill in batches (no lock)
UPDATE orders SET tracking_number = 'UNKNOWN'
WHERE  tracking_number IS NULL
  AND  id BETWEEN 1 AND 100000;
-- repeat in application code or a cronjob

-- Step 3: Apply constraint once backfill is complete
ALTER TABLE orders ALTER COLUMN tracking_number SET NOT NULL;

Renaming a Column Without Downtime

Never rename a column in one step if your app is live — the old name disappears immediately and the app crashes. Use the expand-contract pattern:

-- Phase 1: Add new column and copy data
ALTER TABLE users ADD COLUMN full_name VARCHAR(200);
UPDATE users SET full_name = name;

-- Phase 2: Deploy app code that writes to BOTH columns
-- (Keep both columns in sync during transition)

-- Phase 3: After app is fully deployed, drop old column
ALTER TABLE users DROP COLUMN name;

Adding an Index Without Locking

CREATE INDEX locks the table in older PostgreSQL versions. Use CONCURRENTLY:

-- Safe for production — no table lock
CREATE INDEX CONCURRENTLY idx_orders_customer_id ON orders(customer_id);

This takes longer but doesn't block reads or writes.

Dropping a Table Safely

-- Step 1: Rename first (soft delete — easy to undo)
ALTER TABLE legacy_events RENAME TO _legacy_events_deprecated;

-- Step 2: After a week with no issues, drop permanently
DROP TABLE _legacy_events_deprecated;

Tracking Migration State

Most frameworks store a schema_migrations table:

CREATE TABLE schema_migrations (
  version    VARCHAR(255) PRIMARY KEY,
  applied_at TIMESTAMPTZ DEFAULT NOW()
);

-- After running a migration
INSERT INTO schema_migrations (version) VALUES ('20260501_01');

-- Check what has run
SELECT * FROM schema_migrations ORDER BY version;

With Sequelize (used in the PandaStack backend):

# Run all pending migrations
npx sequelize-cli db:migrate

# Roll back the most recent migration
npx sequelize-cli db:migrate:undo

# Check migration status
npx sequelize-cli db:migrate:status

Migration Checklist Before Deploying

# 1. Test on a production-size database clone
pg_dump -Fc -d production | pg_restore -d staging_clone

# 2. Time the migration
time psql -d staging_clone -f migration_up.sql

# 3. Verify row counts before and after
psql -d staging_clone -c "SELECT COUNT(*) FROM orders;"

# 4. Run the down migration and verify rollback works
psql -d staging_clone -f migration_down.sql

# 5. Deploy with a rollback plan ready

Large-Table Migrations: Batching Updates

Never run an unbounded UPDATE on a multi-million row table — it creates a huge transaction, generates excessive WAL, and blocks autovacuum:

-- Batch update in chunks of 10,000
DO $
DECLARE
  batch_size INT := 10000;
  last_id    BIGINT := 0;
  max_id     BIGINT;
BEGIN
  SELECT MAX(id) INTO max_id FROM orders;
  WHILE last_id < max_id LOOP
    UPDATE orders
    SET    status = 'archived'
    WHERE  id BETWEEN last_id + 1 AND last_id + batch_size
      AND  created_at < '2024-01-01';
    last_id := last_id + batch_size;
    PERFORM pg_sleep(0.1);  -- brief pause between batches
  END LOOP;
END $;

Managed Databases on PandaStack

[PandaStack](https://dashboard.pandastack.io) provides managed PostgreSQL, MySQL, Redis, and MongoDB. Run your migrations against the connection string from the Databases panel — the platform handles backups, so if a migration goes wrong you can restore to the pre-migration snapshot.

Docs: [docs.pandastack.io](https://docs.pandastack.io).

Summary

• Keep migrations small, focused, and reversible
• Use CONCURRENTLY for index creation in production
• Use the expand-contract pattern for column renames
• Batch large UPDATE statements to avoid blocking
• Always test on a production-size clone before deploying