A Runbook for Zero-Downtime Schema Migrations on a Live Database

Never combine adding a column with removing one in the same deploy. The additive half (new column, new table, new index built concurrently) can run against a live database without breaking the old application code, because the old code simply doesn't know the new column exists yet. The destructive half (dropping a column, renaming a table, removing an index) only runs after every instance of the application is confirmed to have stopped reading or writing the old shape.

This split turns one risky migration into two boring ones, each independently reversible.

Before the migration touches the schema in a breaking way, ship application code that writes to both the old and new column or table, and reads from whichever is present, preferring the new one. This dual-write window is where most of the real risk lives, because it's the period where a bug can silently desync the two representations.

Add a background job that compares old and new values on a sample of rows during this window and alerts on mismatch, so a desync surfaces before the cutover instead of after.

Backfill existing rows in small batches, ordered by primary key, with a short sleep between batches to keep replication lag and lock contention low. A single UPDATE statement across millions of rows holds locks and generates a replication spike that reads as a slowdown across every service sharing that database.

Run the backfill during a lower-traffic window even though it's technically safe at any time, because it gives you a wider margin if something behaves differently than staging predicted.

Flip reads to the new column or table first, behind a feature flag, and watch error rates for a full business cycle before touching writes. Only after reads are stable do you stop writing to the old shape. Only after writes have stopped for long enough that you're confident nothing in your fleet is still running old code do you run the destructive migration that removes the old column.

Each of these four sub-steps should be its own deploy, not one big cutover commit, so a problem at any stage rolls back to the previous stable step instead of to the start.

Before step 1 runs, write down exactly what "undo" means at each subsequent step: for the additive migration, it's dropping the new column, which is always safe since nothing depends on it yet. For the dual-write deploy, it's a flag flip back to old-only. For the backfill, it's simply stopping the job; partial backfills are harmless because reads still prefer the shape they're pointed at. Only the final destructive step has no rollback, which is exactly why it runs last, alone, and only once every other step has been stable for days.

Write down what undo means at each stage before you start:

• Additive migration: undo means dropping the new column or table, which is always safe because nothing depends on it yet.
• Dual-write deploy: undo is a flag flip back to writing the old shape only, with reads still served from what is present.
• Backfill: undo means stopping the job, since a partial backfill is harmless and can resume later from the last completed batch.
• Read cutover: undo is flipping the feature flag back, which is why reads move first and the destructive change runs last.

A migration can look completely healthy on application error dashboards while quietly building up replication lag that only shows up as stale reads on a read replica somewhere downstream. Add a lag check to every step of the runbook, not just the backfill step, since even the dual-write deploy adds write volume that a smaller replica can struggle to keep up with.

If lag climbs past a threshold you'd consider a real problem, pause the current step before it compounds. A backfill that's paused for an hour costs nothing. A backfill that pushes a replica far enough behind that a downstream service starts serving stale data is a much longer cleanup.