A schema change should be an ordinary deploy, not a maintenance window. The migration runs, the new code rolls out, and nobody sits refreshing a status page. That is achievable for almost every change you will ever need to make, but only if you stop treating the database and the application as things that change at the same instant. Zero-downtime migrations are not a tool you install. They are an order of operations you follow until it becomes habit.
Why the naive approach breaks production
The classic migration is one script: rename a column, change its type, add a NOT NULL, then deploy the matching code. On a laptop with twelve rows it works beautifully.
Production is less forgiving, for three reasons. Rolling deploys mean old and new versions of your code run side by side for minutes, so the schema has to satisfy both at once. Most schema statements take locks, and a queued ALTER TABLE blocks every query behind it, including short-timeout queries that would otherwise have been fine. And big tables take real time: rewriting tens of millions of rows can run for minutes while holding resources you would rather it released.
The expand and contract pattern
Almost every safe migration is the same idea in different clothing: split the change into a sequence of steps where each intermediate state works with the code currently deployed. The usual shape is expand, migrate, contract.
- Expand. Add the new column, table or index. Keep it optional — nullable, no volatile default, no constraint the old code can trip over.
- Deploy dual-write code. The application now writes to the old shape and the new one.
- Backfill. Copy existing rows into the new shape from a background job, in small batches.
- Switch reads. Point read paths at the new column and watch them for a day or two.
- Stop writing to the old shape. Remove the dual-write branch and deploy.
- Contract. Drop the old column, add NOT NULL, add constraints. This is the only destructive step, and it goes last.
Not every change needs all six steps. Adding a new table needs one. The pattern matters most when you are moving or reshaping data that already has readers.
Renaming a column without breaking anything
Say you want customers.email to become customers.email_address. Add the new column, deploy code that writes to both, backfill from the old column, switch reads to the new one, deploy again, then drop the old column in a later release. That is more steps than a single rename. Every step is dull, reversible and covered by normal monitoring, which is exactly the point. If something looks wrong at step four, you redeploy the previous release and investigate at your leisure.
Operations that quietly lock your tables
Locking is where good intentions meet reality. The statement you are running matters far less than what it takes out on the table while it runs.
- Adding a nullable column is quick on modern PostgreSQL and MySQL. Adding one with a volatile default can rewrite the whole table instead. Add it nullable, backfill, then set a default.
- Creating an index blocks writes unless you ask for otherwise. PostgreSQL has CREATE INDEX CONCURRENTLY; MySQL has online DDL options. Use them everywhere, including staging.
- Adding NOT NULL or a check constraint scans existing rows. Add the constraint as not yet validated, validate it in a separate statement, then tighten it.
- Changing a column type often rewrites the table. Treat it as a new column, a backfill and a swap.
- Foreign keys and unique constraints take locks on both tables and validate every row they can see.
Set a short lock timeout for migrations. If a statement cannot get its lock quickly, it should fail and be retried later rather than queueing behind a slow query and blocking the traffic behind it. A failed migration on a retry loop is an inconvenience; a blocked table is an incident.
Deploy code and schema in the right order
The rule of thumb is schema first, code second, cleanup third. Additive migrations are safe to run before the code that uses them. Destructive changes only run once nothing depends on the old shape. If you cannot describe a deployment where both releases work against the same schema, you have not finished splitting the change.
Keep migrations fast, deterministic and separate from backfills. A migration should finish in seconds as part of a deploy; a backfill is a long-running job that deserves batching, a pause between batches, progress logging and the ability to resume. Never bury a multi-million-row UPDATE inside a deploy step with a timeout.
Rehearse against a realistic copy of the data
Restore a recent production snapshot into a staging database nothing else is using, and run your migration there first. Measure how long it takes, watch the locks, and check the query plans. A rehearsal that takes four minutes on a copy of production will take four minutes on production. If it is too slow, change the plan before you ship it.
Batching and throttling backfills
Backfills are where zero-downtime migrations often fail in practice. A single UPDATE that touches every row will hold locks, generate huge WAL, and compete with production traffic. Instead, process rows in batches: select a chunk by primary key or creation time, update it, commit, sleep briefly, then continue. This keeps transaction sizes small and gives the database room to breathe.
Choose a batch size that finishes in well under a second—often a few hundred to a few thousand rows. Log progress so you can see how far you are. Make the job resumable: if it crashes, it should pick up where it left off rather than starting over. And run it during off-peak hours if possible, but design it to be safe at any time.
For very large tables, consider a dedicated backfill service or a queue of jobs. The key is that the backfill is decoupled from the deploy. The deploy adds the new column; the backfill runs independently and can take hours or days without blocking anyone.
Monitoring and verification
You cannot call a migration zero-downtime if you are not watching. Before you start, decide what "healthy" looks like: query latency, error rates, lock waits, replication lag. During each step, watch those metrics. After switching reads to the new column, compare results between old and new if you can—dual-read in a background job and log mismatches.
Set up alerts for lock contention and replication delay. If you see them spike, pause the migration, not the whole deploy. A migration that can be paused is a migration that can be reasoned about. And always keep the old path available until you are sure the new one is correct.
Rollback is a plan, not a panic
Every step should have a rollback. Because you split the change into small, reversible pieces, rolling back usually means redeploying the previous application version and leaving the schema as it is. The new column is still there, unused, and that is fine. You can drop it later when you are confident.
The only dangerous step is the final contract. Before you drop a column or add a constraint, make sure no code reads or writes it, and that you have a backup. If in doubt, wait another day. There is no prize for dropping a column quickly.
Zero-downtime migrations are less about clever SQL and more about disciplined sequencing. Make every step boring, observable, and reversible, and you will rarely need a maintenance window again.
