Database Migration Strategies That Don't Wake You at 3 AM

# Database Migration Strategies That Don't Wake You at 3 AM

A schema change on a pet-project database is a CREATE TABLE and a coffee break. On a production database serving 50,000 requests per second? It's a high-risk operation that can bring your entire platform down.

The worst part isn't even the downtime — it's the partial downtime. A migration that succeeds on 8 out of 10 database nodes. A lock that stalls writes for thirty seconds. A column rename that breaks the deploy you just rolled out. These are the failures that wake you at 3 AM with a pager alert and no clear rollback path.

I've broken production databases more ways than I'd like to admit. Here's what works, what doesn't, and the specific patterns you need for zero-downtime schema changes.

Why Traditional Migrations Fail at Scale

Most frameworks ship with a migration system that assumes single-node databases and small datasets. Rails' ActiveRecord migrations, Django's makemigrations, Prisma Migrate — they all share two dangerous assumptions:

1. Migrations run instantly — They're just ALTER TABLE statements, right?
2. Old code is gone — Once you migrate, the previous version of your app doesn't exist anymore.

In practice, neither holds true.

The core insight: the application and the database must be compatible at every point during a deploy. That means every migration must be backward-compatible — older application code must still work with the new schema, and newer code must work with the old schema during rollback.

The Golden Rule: Expand, Migrate, Contract

Zero-downtime migrations follow a three-phase pattern known as Expand-Migrate-Contract (EMC) :

Phase 1: Expand

Add the new schema elements (columns, tables, indexes) without removing or breaking the old ones. Old code continues to work unchanged.

Phase 2: Migrate

Backfill data into the new schema, then update the application to read and write from both old and new locations. Deploy this phase as a separate release.

Phase 3: Contract

Once you're confident the new schema is handling all traffic correctly, remove the old schema elements. This is a separate deploy — and if you're nervous, it can wait days or weeks.

Let's walk through each phase with real examples.

Pattern 1: Adding a NOT NULL Column

This is the single most common migration that breaks production. You need a new column that shouldn't be null, so you write:

ALTER TABLE users ADD COLUMN timezone TEXT NOT NULL DEFAULT 'UTC';

On PostgreSQL, this rewrites the entire table. With a 50 GB users table and default max_wal_size, this migration will:

• Hold an ACCESS EXCLUSIVE lock for the entire duration
• Generate WAL equal to the full table size
• Potentially fill your disk and crash the database

The safe approach is a multi-step EMC pattern:

Step 1 — Add the column as nullable

-- Phase 1: Expand
ALTER TABLE users ADD COLUMN timezone TEXT;
CREATE INDEX CONCURRENTLY idx_users_timezone ON users (timezone);

// Phase 1 code — both old and new versions handle nullable
type User = {
  id: string;
  name: string;
  email: string;
  timezone: string | null; // nullable during transition
};

Step 2 — Backfill with a batched background job

// Phase 2: Migrate — backfill in batches
const BATCH_SIZE = 1000;

async function backfillTimezone(
  db: Pool,
  maxBatchLag: number = 10_000 // ms
): Promise<{ completed: number; remaining: number }> {
  let totalUpdated = 0;

  while (true) {
    const result = await db.query<{ id: string }>(
      `UPDATE users
       SET timezone = COALESCE(
         timezone,
         detect_timezone_from_region(country_code),
         'UTC'
       )
       WHERE id IN (
         SELECT id FROM users
         WHERE timezone IS NULL
         LIMIT $1
         FOR UPDATE SKIP LOCKED
       )
       RETURNING id`,
      [BATCH_SIZE]
    );

    if (result.rows.length === 0) break;
    totalUpdated += result.rows.length;

    // Rate-limit to avoid overwhelming the database
    if (result.rows.length >= BATCH_SIZE) {
      await sleep(maxBatchLag);
    }
  }

  const remaining = await db.queryOne<{ count: string }>(
    `SELECT COUNT(*) FROM users WHERE timezone IS NULL`
  );

  return { completed: totalUpdated, remaining: Number(remaining?.count ?? 0) };
}

Step 3 — Add a NOT NULL CHECK constraint (not ALTER)

-- Phase 2 continued: validate without a full table rewrite
ALTER TABLE users ADD CONSTRAINT users_timezone_not_null
  CHECK (timezone IS NOT NULL) NOT VALID;

This adds the constraint instantly — no table scan. Then validate it in the background:

ALTER TABLE users VALIDATE CONSTRAINT users_timezone_not_null;

VALIDATE CONSTRAINT scans the table but only holds a SHARE UPDATE EXCLUSIVE lock — it doesn't block concurrent reads or writes. If it fails, you just drop the constraint and fix the remaining null rows.

Step 4 — Make it non-nullable (contract)

-- Phase 3: Contract
ALTER TABLE users ALTER COLUMN timezone SET NOT NULL;

Because the NOT VALID constraint already guarantees no nulls exist, this runs as a metadata-only operation — instant and lock-free.

Pattern 2: Renaming a Column

Renaming a column is the migration that trips everyone up. You can't just ALTER TABLE … RENAME COLUMN because old application code still references the old name.

The Dual-Write Pattern

// Phase 1: Expand — add the new column alongside the old
// Migration SQL:
// ALTER TABLE orders ADD COLUMN customer_id UUID;
// CREATE INDEX CONCURRENTLY … ON orders (customer_id);

// Phase 2: Dual-write — both old and new
class OrderRepository {
  async createOrder(input: CreateOrderInput): Promise<Order> {
    const result = await db.query(
      `INSERT INTO orders (user_id, customer_id, ...)
       VALUES ($1, $2, ...)
       RETURNING *`,
      [input.userId, input.customerId ?? input.userId]
    );
    return mapRow(result.rows[0]);
  }

  async getOrder(id: string): Promise<Order | null> {
    // Read from new column first, fall back to old
    const result = await db.query(
      `SELECT
         COALESCE(customer_id, user_id) AS customer_id,
         ...
       FROM orders WHERE id = $1`,
      [id]
    );
    return result.rows[0] ? mapRow(result.rows[0]) : null;
  }
}

Backfill customer_id

-- Batched backfill for the rename
UPDATE orders
SET customer_id = user_id
WHERE customer_id IS NULL
  AND id IN (SELECT id FROM orders WHERE customer_id IS NULL LIMIT 1000);

Contract — drop the old column

-- This is safe because:
-- 1. All rows have customer_id populated
-- 2. All code now reads from customer_id
-- 3. We can revert by adding user_id back if needed
ALTER TABLE orders DROP COLUMN user_id;

The dual-write period is your insurance. Deploy Phase 2 on Monday, watch the dashboards all week, and drop the old column on Friday. If something breaks, you can roll back the code without a database rollback.

Pattern 3: Changing a Column Type

Type changes (e.g., INTEGER → BIGINT, VARCHAR(50) → VARCHAR(255)) are deceptively dangerous. PostgreSQL's ALTER COLUMN … TYPE rewrites the entire table.

The Add-and-Swap Pattern

-- Phase 1: Expand — add new column with new type
ALTER TABLE events ADD COLUMN event_version_big BIGINT;
CREATE INDEX CONCURRENTLY idx_events_version_big ON events (event_version_big);

// Phase 2: Dual-write
async function recordEvent(event: Event): Promise<void> {
  await db.query(
    `INSERT INTO events (id, type, payload, event_version, event_version_big, created_at)
     VALUES ($1, $2, $3, $4, $5, $6)`,
    [
      event.id,
      event.type,
      event.payload,
      event.eventVersion,
      Number(event.eventVersion), // cast to BIGINT
      event.createdAt,
    ]
  );
}

async function getEvent(id: string): Promise<Event | null> {
  const result = await db.query(
    `SELECT
       id, type, payload,
       COALESCE(event_version_big, event_version::BIGINT) AS event_version,
       created_at
     FROM events WHERE id = $1`,
    [id]
  );
  return result.rows[0] ? mapEvent(result.rows[0]) : null;
}

Backfill with a trigger function

-- Backfill existing rows
UPDATE events
SET event_version_big = event_version::BIGINT
WHERE event_version_big IS NULL
  AND id IN (SELECT id FROM events WHERE event_version_big IS NULL LIMIT 500);

Contract — drop old column and rename

BEGIN;
ALTER TABLE events DROP COLUMN event_version;
ALTER TABLE events RENAME COLUMN event_version_big TO event_version;
ALTER TABLE events ALTER COLUMN event_version SET NOT NULL;
COMMIT;

Because this DDL runs in a transaction, there's a brief moment where neither column exists from the application's perspective. If you can't tolerate even that, use the Foreign Key Safety Net pattern (below).

Pattern 4: Large Table Index Creation

Creating an index on a 100M+ row table with a simple CREATE INDEX locks out writes. The fix is CONCURRENTLY:

-- Non-blocking index creation
CREATE INDEX CONCURRENTLY idx_orders_customer_created
ON orders (customer_id, created_at DESC);

Caveats:

• Takes longer (2×–3×) because PostgreSQL uses two table scans
• Can't run inside a transaction block
• If it fails (e.g., deadlock), leaves an invalid index — you have to DROP and retry
• Uses more resources (double the IOPs during the two-pass scan)

For the risk-averse, the index-only pattern is safer:

-- Step 1: Create with CONCURRENTLY (safe, but may fail)
CREATE INDEX CONCURRENTLY idx_orders_new ON orders (...) WHERE ...;

-- Step 2: If it succeeded
DROP INDEX CONCURRENTLY idx_orders_old;

Never use CREATE INDEX on a production table without CONCURRENTLY. Not once. Not even if "it's just 10 million rows."

Pattern 5: Removing a Column Safely

The safest way to remove a column is to never actually remove it:

-- Instead of DROP COLUMN, make it invisible to the application
-- Phase 1: Remove all code references to legacy_column
-- Phase 2: Once no code writes to it, ignore it in reads
-- Phase 3 (weeks later): DROP COLUMN IF EXISTS legacy_column;
ALTER TABLE users DROP COLUMN IF EXISTS legacy_column;

But here's the real pro tip: only drop columns during low-traffic windows, and have the backup ready. A DROP COLUMN that you need to revert is a PITR (point-in-time recovery) operation, which means data loss from the last backup.

The Rollback Plan Template

Every migration should come with a rollback plan before it runs. Here's a concrete template:

The Atomic Cutover Pattern with Foreign Keys

Sometimes you can't dual-write. Maybe the column rename affects 40 different queries and you can't find them all. In that case, use the Foreign Key Safety Net:

-- Create the new table alongside the old one
CREATE TABLE orders_v2 (LIKE orders INCLUDING ALL);
ALTER TABLE orders_v2 ADD COLUMN customer_id UUID;

-- Add a trigger to keep both tables in sync
CREATE OR REPLACE FUNCTION sync_orders_v2()
RETURNS TRIGGER AS $
BEGIN
  INSERT INTO orders_v2 (id, ..., customer_id)
  VALUES (NEW.id, ..., NEW.user_id)
  ON CONFLICT (id) DO UPDATE SET
    customer_id = NEW.user_id;
  RETURN NEW;
END;
$ LANGUAGE plpgsql;

CREATE TRIGGER trg_sync_orders_v2
AFTER INSERT OR UPDATE ON orders
FOR EACH ROW EXECUTE FUNCTION sync_orders_v2();

Now backfill orders_v2, then when you deploy, atomically switch:

// In a transaction:
// 1. RENAME orders TO orders_old;
// 2. RENAME orders_v2 TO orders;
// 3. Drop the trigger and old table when confident

await db.transaction(async (tx) => {
  await tx.query("ALTER TABLE orders RENAME TO orders_old");
  await tx.query("ALTER TABLE orders_v2 RENAME TO orders");
  // Application sees the new table instantly
});

Yes, the RENAME acquires ACCESS EXCLUSIVE locks. Yes, the window is small. But it's a single, atomic cutover — no dual-write complexity, no COALESCE everywhere.

What About ORMs?

ORMs make this harder, not easier. Prisma's migrate dev generates bare ALTER TABLE statements. TypeORM's synchronize is a production disaster waiting to happen. Knex and Sequelize let you write raw SQL, which is the only safe path for production migrations.

My recommendation: Use raw SQL migrations for schema changes, and treat ORM-generated migrations as dev-only tools. Test every migration against a copy of production data before running it.

The Pre-Migration Checklist

Before any production migration:

1. Run it against a production clone — Same data volume, same indexes, same connections
2. Set lock_timeout — SET lock_timeout = '5s'; on the migration connection to fail fast instead of blocking
3. Monitor pg_locks — Watch for lock queues forming
4. Have a dead man's switch — A background job that cancels the migration if it exceeds a time budget
5. Deploy in low traffic — Even "zero-downtime" migrations hit 99.9%, not 100%
6. Test the rollback — Not just the forward path

Final Thought

The fear of database migrations comes from treating them as one-shot deploys. When you think in phases — expand, migrate, contract — and build rollback into every step, schema changes become routine. Boring, even. And boring is exactly what you want from your database operations.

The patterns here have served us through hundreds of production migrations on PostgreSQL. They apply equally to MySQL, CockroachDB, and most SQL databases. The database isn't the bottleneck — your migration strategy is.