Nobody fears adding a column. What teams fear — correctly — is the migration that locks a hot table for four minutes during peak traffic, or the deploy that assumes the new schema exists before every instance has actually picked it up. Zero-downtime migrations are not a tooling problem. They are a sequencing problem, and the sequence that works has a name: expand-contract.
We run this pattern on every production schema change now, after a migration early in the company's history took a client's checkout table offline for six minutes during a regional sale — a missing CONCURRENTLY on an index build that looked completely safe in staging, where the table was a tenth of the size. The pattern below is the direct result of that incident.
The expand-contract pattern
Every migration that cannot be a single atomic statement gets split into three phases, deployed as three separate releases.
Expand
Add the new column, table, or index alongside the old one. The application keeps writing to both — old and new — so nothing reads from a column that older running instances haven't started populating yet.
Migrate
Backfill historical data in throttled batches, off-peak, with a kill switch. Verify the backfill against a sample before trusting it at scale — a silent type-coercion bug in a backfill script is how you corrupt data quietly.
Contract
Once every instance reads exclusively from the new schema and the backfill is verified, remove the old column in its own deploy. This is the only destructive phase, and by the time it ships it carries zero behavioral risk.
Where teams get burned
- Deploying a schema change and the application code that depends on it in the same release — there is always a window where old and new instances run side by side during a rollout.
- Adding an index without a concurrent build — a standard index creation takes a table lock for its full duration, which on a large table means a multi-minute outage disguised as routine maintenance.
- Treating a column drop as reversible. It isn't. We keep dropped columns renamed and nullable for one full release cycle before actually removing them, specifically so a bad contract phase can be undone without a restore.
- Backfilling at full throttle. An unthrottled update across millions of rows competes with production traffic for the same I/O and connection pool, and that's how a migration becomes an incident report.
Our rollout checklist
Every migration above a trivial size goes through the same five gates before it touches production: a dry run against a production-sized snapshot, an explicit rollback plan written before the forward plan, a backfill throttle with a monitored kill switch, a feature flag gating the code path that depends on the new schema, and a scheduled off-peak window even though the migration is theoretically safe at any hour.
Testing a migration against something that actually resembles production
A migration that runs cleanly against a seeded staging database with ten thousand rows tells you almost nothing about how it behaves against a production table with two hundred million. Lock duration, backfill time, and replication lag all scale non-linearly with table size, and the only reliable way to catch that before it's an incident is to test against data that's the same order of magnitude.
- Run the dry run against an anonymized, production-sized snapshot — not a subset, not synthetic data generated to roughly the same row count.
- Time the backfill at the throttle rate you intend to use in production, not an unthrottled 'how fast can this possibly go' benchmark.
- Check replication lag during the dry run, not just query latency — a backfill that's fast on the primary but blows out the replica lag will still cause user-facing staleness.
In closing
Zero-downtime is not a property of the migration tool. It's a property of how the release is sequenced and how it was tested. Expand before you migrate, migrate before you contract, and never let a single deploy depend on both the old and new shape of the data being true at once.
