The Zero-Downtime Database Migration Playbook

Most requests for a "zero-downtime migration" arrive after a downtime migration has already gone wrong once. The good news is that the technique for avoiding it is mechanical, not clever: every schema change that requires downtime can be decomposed into a sequence of smaller changes that don't, and the sequence is nearly always the same shape regardless of the database engine.

The expand-contract pattern, applied literally

The core discipline is expand-contract: add the new structure alongside the old, migrate reads and writes over, then remove the old structure — never mutate a live column or table in place. For a column rename, that means: add the new column, dual-write both columns from the application layer, backfill the new column from the old in batches small enough not to saturate replication lag, switch reads to the new column behind a flag, verify, then drop the old column in a separate deploy once you're confident nothing still references it. Each of those steps is independently safe and independently reversible. Collapsing them into one migration is what produces a lock that stalls production traffic.

Locks are the real enemy, not schema changes

Teams often blame "the migration" for an outage when the actual cause was a specific DDL statement taking an exclusive lock on a hot table. Adding a column with a non-null default, adding an index without a concurrent build option, or adding a foreign key constraint that requires a full table scan are the usual culprits. On Postgres, that means CREATE INDEX CONCURRENTLY, adding columns as nullable first and backfilling the default separately, and validating foreign keys with NOT VALID followed by a later VALIDATE CONSTRAINT that doesn't hold a blocking lock. On MySQL/InnoDB, it means checking whether your DDL is actually online (ALGORITHM=INPLACE) for your specific engine version rather than assuming. The migration tool matters less than knowing, statement by statement, which locks each one takes and for how long against your actual table size.

Backfills are a rate-limiting problem, not a script

A backfill that runs as a single unbounded UPDATE is a common way to turn a routine migration into an incident — it holds locks proportional to the batch size, competes with production traffic for I/O, and can bloat replication lag past your failover threshold. We run backfills in small, resumable batches, throttled against live replication lag and query latency metrics rather than a fixed sleep interval, with a kill switch that a human can hit without redeploying anything. Treat the backfill as a long-running background job with its own observability, not a one-time script you run and forget.

Dual-write windows need an integrity check, not just faith

The riskiest part of expand-contract is the window where both old and new structures are live and diverging is possible — a failed write to the new column that succeeds on the old one, for instance. We run a continuous reconciliation query during that window that flags rows where old and new disagree, and we don't proceed to the contract phase until that count has been at zero for a defined soak period, not just briefly. Skipping the soak period is the single most common cause of the "the migration looked done, then we found orphaned data three weeks later" class of bug.

Practice the rollback, not just the forward migration

Every migration plan we write includes a tested rollback for each individual step, because the value of decomposing a migration into small steps evaporates if you can only roll back the whole sequence at once. If step four of seven causes a problem, you want to revert step four, not spend an incident deciding whether to attempt a risky forward fix under pressure. Rollback plans that were never actually executed in staging are, in our experience, wrong about half the time — so we run them for real before the migration ships.