Why a Simple Rename Causes an Outage
You want to rename email to email_address. One migration, one line. In development it works immediately.
In production, a deployment is not atomic. For some window, old instances and new instances are both serving traffic. Old code selects email; new code selects email_address. Whichever name the column currently has, half your fleet is issuing queries against a column that does not exist.
The window might be thirty seconds during a rolling deploy, or an hour if the rollout is gradual. It does not matter — errors during it are real. And if you need to roll the application back, the schema has already moved and the old code cannot run at all.
The root cause is a coupling: the migration assumes exactly one version of the application exists. That assumption is false in any environment that does not take downtime.
Backward and Forward Compatibility
The property to aim for is that at every point in time, the schema is compatible with both the currently deployed code and the code about to be deployed.
That sounds like it forbids change. It does not — it forbids doing a breaking change in one step. Any breaking change can be decomposed into a sequence of individually compatible ones. That decomposition is the expand-migrate-contract pattern.
Expand
Add the new structure alongside the old. Nothing is removed and nothing is renamed, so all existing code continues to work unchanged. New columns must be nullable or have defaults, because existing rows have no value for them.
Migrate
Move data and readers to the new structure while both exist. Backfill existing rows, have the application write to both, then switch reads over. Every intermediate state is one where both old and new code function.
Contract
Only once nothing references the old structure, remove it. This is a separate deployment, usually days or weeks later, and it is the step most likely to be done too early.
The Rename, Done Properly
Six deployments. It looks heavy for a rename, and each individual step is trivial and safe.
- Add the new column, nullable. Schema change only, no code change. Existing code does not know it exists.
- Deploy code that writes both columns and reads the old one. Every new or updated row now has both values populated. Reads are unchanged, so behaviour is identical.
- Backfill in batches. Copy old to new for pre-existing rows, in bounded chunks with a pause between them. Never a single statement over the whole table — that holds locks and generates transaction log for the entire duration.
- Verify, then deploy code that reads the new column. Confirm zero rows have a null new value where the old one is populated, then switch reads. Writes still go to both, so rollback remains available.
- Deploy code that writes only the new column. The old column is now unreferenced but still present, which means a rollback is still possible for a while.
- Drop the old column. Last, and only after confirming nothing reads it.
The critical property is that you can stop or roll back after any step. Compare that to the single-migration version, where the only recovery from a problem is rolling the schema forward under pressure.
Operations by Risk
| Operation | Risk | Notes |
|---|---|---|
| Add nullable column | Low | Safe on modern engines. Metadata-only. |
| Add column with default | Low to high | Metadata-only on recent versions; a full table rewrite on older ones. Check yours. |
| Add index | Medium | Use the concurrent or online form, or it blocks writes for the build. |
| Add NOT NULL to existing column | High | Requires a full validation scan. Add as an unvalidated check first, then validate. |
| Change column type | High | Usually a rewrite. Do it as a new column plus backfill instead. |
| Rename column or table | High | Fast in the database, but breaks code compatibility. Always expand-contract. |
| Add foreign key | High | Validates every existing row and locks both tables. Add unvalidated, then validate. |
| Drop column | Medium | Cheap to execute, irreversible. The risk is doing it too early. |
The pattern worth extracting: several operations have a two-phase form — add the constraint as unvalidated, then validate separately — that converts one long lock into two short ones. Learn which of these your engine supports before you need them.
Backfilling Without Causing an Incident
The backfill is the step most likely to cause the outage you were trying to avoid, because it is the only step that touches a lot of rows.
- Batch by primary key range, not with OFFSET. Offset pagination re-scans everything it skips, so each batch is slower than the last. Ranges stay constant.
- Keep batches small and pause between them. A few thousand rows, then a short sleep. This leaves room for production traffic and keeps replication lag under control.
- Make it resumable and idempotent. Record progress and filter to rows not yet migrated. The backfill will be interrupted; assume it.
- Watch replication lag as the stop signal. A backfill that outruns replicas will affect read traffic even though the primary looks healthy. Pause when lag grows.
- Never rely on the backfill alone. Rows written during the backfill must also get the new value, which is why the dual-write deployment comes first. Backfill without dual-write leaves a gap of rows written mid-migration.
For a one-off correction or a lookup table being seeded as part of a migration, generating a batched INSERT script is often simpler than writing a backfill job. The Develop Box Utilities Excel-to-SQL generator produces batched, dialect-correct statements against a staging table, which keeps the load fast and reviewable — and reading the generated script before running it is a genuine safety step that a hand-rolled job does not offer.
Making It Routine
- Separate schema and code deployments. If a migration runs as part of application startup, you cannot sequence the six steps independently. This is the structural prerequisite for everything above.
- Require every migration to be reversible, or explicitly marked otherwise. Forcing the author to write the down migration surfaces irreversible steps at review time rather than at 2am.
- Test against a production-sized copy. A migration that takes 40 ms on 1,000 rows may take 40 minutes on 40 million. Row count is the variable that matters and it is the one dev databases lack.
- Set a lock timeout on migrations. Better for a migration to fail fast than to sit holding a lock while a queue of queries builds behind it.
- Schedule the contract step as real work. Dropping the old column is a task that gets forgotten because nothing breaks when it is skipped. Schemas accumulate abandoned half-migrations this way, and each one confuses the next person.
Frequently Asked Questions
Six deployments for a rename seems excessive. Is it always necessary?
No. If you can take a maintenance window, or the table is tiny and unreferenced, a single migration is fine and the ceremony is waste. The pattern earns its cost when the table is serving live traffic and downtime is not acceptable. Judge by blast radius, not by principle.
Can I use a view to alias the old column name instead?
For table renames this works well and is a genuine shortcut: rename the table and create a view under the old name. For column renames within a table it is more awkward, since you would need to rename the table and expose a view with both column names. Views also complicate writes unless they are updatable. Useful tool, not a universal replacement.
How long should I wait before the contract step?
Long enough that you would no longer roll the application back that far, and long enough to have covered every periodic job. A weekly report or a monthly batch may be the only remaining reader, so waiting less than a full cycle of your longest-period job is how the old column turns out to still be needed. Two weeks is a common floor.
Conclusion: Decompose the Breaking Change
Zero-downtime migration is not a special technique so much as a refusal to assume one version of the application exists. Once you accept that old and new code overlap, the requirement follows directly: every intermediate schema state must work for both.
Add before removing. Backfill in resumable batches while dual-writing. Move readers, then writers, then drop. Each step is individually dull and reversible, which is exactly the property you want from something running against a live database.