Zero-Downtime Database Migration Strategies for High-Throughput SaaS

A schema change on a table doing 4,000 writes/second doesn't fail loudly. It fails as a lock that queues every write behind it, which looks like an outage even though nothing "went down." The pattern that avoids this (expand/contract with dual-writing and shadow reads) is well understood in principle and poorly executed in practice, usually because the backfill step gets rushed. This is the version with the parts that actually go wrong included.

The four phases, and why order matters

1. Expand: add the new column/table, nullable, no default that requires a table rewrite.
2. Dual-write: application writes go to both old and new schema, synchronously, in the same transaction.
3. Backfill: a batched job fills in the new schema for rows written before dual-writing started.
4. Contract: once new-schema data is verified complete and correct, cut reads over, then drop the old column.

The failure mode that skips straight to pain: doing the backfill *before* dual-writing is live. Rows written during the backfill window, after the backfill query already read past them, never get filled in: you end up with a gap that's invisible until someone queries a row from that exact window and gets nulls. Dual-writing has to be deployed and confirmed running before the backfill starts, not after.

Expand: adding the column without a table lock

On Aurora PostgreSQL, ADD COLUMN with no default is fast (metadata-only, no rewrite): the problem is teams that add a column with a non-null default, which on older Postgres versions forces a full table rewrite under an ACCESS EXCLUSIVE lock. Postgres 11+ (Aurora PostgreSQL is compatible with recent major versions) fixed constant defaults to be metadata-only too, but don't rely on version behavior you haven't confirmed. Write the migration defensively regardless:

-- Safe on any version: nullable, no default, no rewrite, no lock
-- beyond a brief ACCESS EXCLUSIVE to update the catalog.
ALTER TABLE orders ADD COLUMN total_cents BIGINT;

-- Add the constraint separately, NOT VALID first, so it doesn't
-- scan the whole table while holding a lock:
ALTER TABLE orders ADD CONSTRAINT total_cents_positive
  CHECK (total_cents >= 0) NOT VALID;

-- Validate it later, which takes a lighter lock (SHARE UPDATE
-- EXCLUSIVE) and lets writes continue concurrently:
ALTER TABLE orders VALIDATE CONSTRAINT total_cents_positive;

Dual-write: the part people get wrong is error handling

The obvious implementation writes to the new column in the same code path as the old one. The part that gets skipped: what happens when the new-schema write fails but the old-schema write already succeeded? If you let that exception propagate and fail the whole request, you've turned a migration into a reliability regression: the new schema isn't validated yet, and now it's on the critical path for every write.

def save_order(order: Order, conn) -> None:
    with conn.transaction():
        conn.execute(
            "UPDATE orders SET total = %s WHERE id = %s",
            (order.total, order.id),
        )
        try:
            conn.execute(
                "UPDATE orders SET total_cents = %s WHERE id = %s",
                (int(order.total * 100), order.id),
            )
        except Exception:
            # Log and continue: the old column is still the source
            # of truth during dual-write. A failed new-column write
            # here becomes a backfill gap, not a request failure,
            # and the shadow-read comparison below will surface it.
            logger.exception("dual-write to total_cents failed", order_id=order.id)

Putting both writes in the same transaction (rather than a separate async write) matters specifically because it's the only way to guarantee they can't diverge from a crash mid-request: either both commit or neither does. The try/except is there so a *logic* failure in the new path (a constraint violation, a type coercion bug) doesn't take down the old path that's still authoritative.

Shadow reads: validating correctness before anyone trusts the new path

Before cutting reads over, run both queries and compare, serving only the old result:

def get_order_total(order_id: str, conn) -> Decimal:
    old_value = conn.execute(
        "SELECT total FROM orders WHERE id = %s", (order_id,)
    ).fetchone()[0]

    if random.random() < SHADOW_READ_SAMPLE_RATE:  # e.g. 0.05
        new_value_cents = conn.execute(
            "SELECT total_cents FROM orders WHERE id = %s", (order_id,)
        ).fetchone()[0]
        if new_value_cents is None or Decimal(new_value_cents) / 100 != old_value:
            metrics.increment("shadow_read.mismatch", tags=[f"table:orders"])

    return old_value  # old path stays authoritative until Contract
}

Sample rather than compare on every read: at meaningful throughput, doubling every read query to validate a migration is its own capacity problem. A 5% sample is enough to catch a systemic bug (a rounding error, a timezone mismatch) within minutes; it won't catch a one-in-a-million edge case quickly, but that's what the backfill's own row-count and checksum comparison is for.

Backfill: batched, rate-limited, resumable

The batch size and pacing matter more than the query logic. An unthrottled backfill on a multi-million-row table will spike write IOPS and replica lag simultaneously, and on Aurora specifically, sustained replica lag past your application's staleness tolerance can cause read-replica queries to serve stale data silently, not error. Worth monitoring during the backfill, not just before it.

BATCH_SIZE = 5_000

def backfill_total_cents(conn):
    last_id = None
    while True:
        rows = conn.execute(
            """
            SELECT id, total FROM orders
            WHERE total_cents IS NULL
              AND ($1::uuid IS NULL OR id > $1)
            ORDER BY id
            LIMIT %s
            """,
            (last_id, BATCH_SIZE),
        ).fetchall()

        if not rows:
            break

        conn.executemany(
            "UPDATE orders SET total_cents = %s WHERE id = %s",
            [(int(r.total * 100), r.id) for r in rows],
        )
        last_id = rows[-1].id

        # Throttle against replica lag, not a fixed sleep: a fixed
        # sleep is either too conservative at 3am or too aggressive
        # during a traffic spike.
        lag = get_cloudwatch_metric("AuroraReplicaLag", stat="Maximum")
        if lag > 200:  # ms
            time.sleep(min(30, lag / 1000))

ORDER BY id with a keyset cursor (id > $1) instead of OFFSET matters at scale: OFFSET on a large table gets slower every batch because Postgres still has to scan and discard every earlier row, while a keyset cursor uses the primary key index directly regardless of how far into the backfill you are.

Contract: don't drop the old column on the same deploy as the cutover

Flip reads to the new column, then wait (a full billing cycle if the table has anything to do with billing, at minimum a few days of full production traffic) before dropping the old column. This is the cheapest insurance in the entire process: if the cutover reveals a bug the shadow reads didn't catch, you want the old column and the old read path both still present and revertible with a config flag, not a rollback migration under incident pressure.

-- Only after the bake period, and only after confirming nothing
-- still references the old column:
ALTER TABLE orders DROP COLUMN total;

The whole sequence (expand, dual-write, backfill, verify, cutover, bake, contract) is slower than a direct migration by design. Every phase exists because something specific goes wrong without it, not because more steps look more careful.