Zero-Downtime Schema Migrations on a Database You Can't Stop

The migration itself is almost never the hard part. Writing the ALTER TABLE takes a minute. The hard part is that the table has 400 million rows, the application is mid-deploy so two versions of the code are talking to the database simultaneously, and the business has no maintenance window because customers in three time zones are transacting right now.

We have run schema changes against systems where a two-minute lock would have been a reportable incident. The technique is not clever - it is the same small set of patterns applied with discipline, plus a rollback plan you actually believe in.

---

The Constraint That Drives Everything

During any rolling deploy, old code and new code run at the same time against one database. That window might be thirty seconds or thirty minutes, but it always exists.

Everything follows from this. The schema must be simultaneously compatible with the version of the application you are replacing and the version you are deploying. A migration that is only valid after the deploy completes will break every request served by an old instance in the interim.

There is no moment when the schema and the code change together. Design for the overlap or be surprised by it.

---

Expand, Migrate, Contract

Every non-trivial change decomposes into three deploys. Splitting a full_name column into first_name and last_name looks like this.

Expand. Add the new columns as nullable. Do not touch the old one. Deploy this alone. The schema now supports both shapes and the running application does not know or care that the new columns exist. This deploy is trivially safe and trivially reversible.

Migrate. Deploy code that writes to both old and new columns on every insert and update, while still reading from the old one. Then backfill existing rows in batches. When the backfill completes and you have verified consistency, deploy code that reads from the new columns. The old column is still being written, so a rollback at this point is instant.

Contract. Once the new path has run in production long enough that you are confident - days, not minutes - deploy code that stops writing the old column. Then, in a separate later deploy, drop it.

Three or four deploys to rename a column feels absurd the first time. It stops feeling absurd the first time you can roll back a bad release at 2am without data loss because the old column was still there and still current.

Never drop in the same release that stops writing. Keep the column, unused, for at least one full release cycle. The cost is some disk. The alternative is discovering during an incident that your rollback target needs a column that no longer exists.

---

The Locks That Will Bite You

The specific behaviors differ by engine, but the categories are universal, and the assumptions people carry from one engine to another are a reliable source of outages.

Adding a column with a default. Modern Postgres and MySQL handle this without rewriting the table. Older versions rewrite every row while holding a lock. Know which one you are running before you assume it is free.

Adding an index. A plain index build locks writes for the duration. Postgres has CREATE INDEX CONCURRENTLY, MySQL has online DDL - use them, and know their limitations. A concurrent build takes considerably longer and can fail partway, leaving an invalid index you must clean up before retrying.

Adding a NOT NULL constraint. This scans the whole table. On Postgres, add a validated CHECK constraint first with NOT VALID, validate it separately without a heavy lock, then promote. Doing it directly on a large table is a long lock.

Adding a foreign key. Same shape - the validation scan is the expensive part, and it can be deferred and run separately.

The lock queue is the real danger. This is the one that catches experienced people. When your DDL statement waits for a lock, every subsequent query on that table queues behind it, including reads. A migration that would have taken 50 milliseconds can take down a table for a minute because it got stuck behind one long-running transaction and then blocked everything arriving after it. Always set a short lock timeout and retry rather than letting a statement wait indefinitely.

---

Backfills Are Batch Jobs, Not Migrations

The most common self-inflicted outage we see is a single UPDATE statement across a large table inside the migration script. It holds locks, generates enormous replication lag, and if it fails at 80 percent you have no idea which rows were touched.

Backfills belong outside the migration, as a resumable batch job with these properties:

• Bounded batches. A few thousand rows at a time, ordered by primary key, with the last processed key persisted so the job resumes rather than restarts.

• Throttled and adaptive. Sleep between batches. Watch replication lag and back off automatically when it climbs. A backfill that finishes in two hours without anyone noticing beats one that finishes in twenty minutes and pages the on-call.

• Idempotent. Re-running a batch must be harmless, because you will re-run batches.

• Observable. Rows processed, rows remaining, current lag, estimated completion. A backfill you cannot watch is a backfill you cannot safely abort.

• Verified before cutover. Before switching reads to the new column, run a consistency check comparing old and new across a sample - or the full table if you can afford it. Discovering a backfill bug after cutover is significantly worse than delaying cutover a day.

---

The Rollback Plan Matters More Than the Migration

Ask this before every migration: if we need to revert the application deploy fifteen minutes from now, does the schema still work?

If the answer is no, the migration is not safe yet regardless of how carefully it was written. That is what makes expand-migrate-contract worth its ceremony - at every intermediate state, the previous version of the code still runs correctly.

Some changes are genuinely irreversible. Dropping a column destroys data. Changing a column type may lose precision. For those, the rollback plan is a verified backup and a documented, rehearsed restore procedure - and a decision made deliberately with the people who own the data, not on a Friday afternoon by whoever wrote the ticket.

Migrations run separately from application deploys. If the migration is coupled to the deploy pipeline, rolling back the deploy either attempts to roll back the schema, which is often destructive, or leaves you in a state nobody designed for. Decouple them and you can revert the code without touching the data.

---

The Changes That Need More Than Expand-Contract

Some migrations do not decompose neatly, and knowing which ones is what keeps you out of trouble.

Changing a column type. Adding a new column of the target type, dual-writing with an explicit conversion, and backfilling is the safe path. Doing it in place risks a full table rewrite under lock, and worse, silent data loss if the conversion narrows the range. Always run the conversion over a production snapshot first and count the rows that would change value.

Splitting or merging tables. This is expand-migrate-contract applied at a larger scale, usually with a period of dual-write across both shapes and a longer verification window. The tricky part is not the mechanics but the transactional boundary: writes that used to be atomic in one table are now spread across two, and that has to be handled deliberately.

Adding a uniqueness constraint to existing data. The constraint will fail if duplicates exist, and on a large table you will not find out until the end of a long scan. Find and resolve the duplicates as a separate exercise first, then add the constraint as its own step.

Changing a primary key. Rare, painful, and effectively a table migration with a cutover rather than a schema change. Plan it as a project, not a ticket.

Anything involving a large table on a heavily replicated cluster. The bottleneck often is not the primary at all - it is the replicas falling behind and read queries returning stale data. Watch lag, not just the primary duration.

---

Running the Migration

The operational discipline around the moment of execution matters as much as the SQL.

Set a lock timeout on every DDL statement. A short one, in the range of a few seconds. If the lock cannot be acquired, the statement fails cleanly and retries rather than queueing behind a long transaction and blocking every query that arrives after it. This single setting prevents the most common migration outage.

Retry with backoff. With a short lock timeout, transient contention causes failures. An automatic retry loop turns those into a non-event.

Check for long-running transactions first. An open transaction holding a conflicting lock will block your DDL, which will then block everything else. Query for transactions older than a few seconds before starting, and either wait or deal with them.

Run during a low-traffic window when one exists. Zero-downtime technique does not mean traffic is irrelevant - it means you are not required to stop. Lower concurrency still means less contention and a smaller window for anything unexpected.

Have someone watching. Replication lag, error rate, and connection count, in real time, with a clear abort threshold agreed before starting. The person running it should not also be the person deciding whether to abort.

Write down the abort procedure before you begin. Not during. Knowing exactly which command cancels the operation, and what state that leaves things in, is what makes the difference between a clean abort and improvisation under pressure.

---

Making It Routine

The teams that do this well have made it boring rather than heroic.

Test against production-shaped data. A migration that runs in 40 milliseconds against 500 seeded rows tells you nothing. Run it against a restored production snapshot and record the timing.

Lint migrations in CI. Automated checks that reject a non-concurrent index build, a missing lock timeout, or a DROP COLUMN in the same release that stopped writing it. Cheap to build, and it catches the mistakes that experienced engineers make when they are in a hurry.

Require an explicit rollback note. A one-line answer to "what happens if we revert the app after this runs?" in every migration PR. The discipline of writing it catches most of the unsafe ones.

Deploy migrations on their own. Separate, small, one change at a time. A release bundling six schema changes has a rollback story nobody can reason about.

---

Where to Start

Pick your next schema change and write it as three deploys instead of one. Set a lock timeout. Move the backfill into a resumable job. Write the rollback note. It takes maybe an extra hour and it converts a migration from an event into a routine change.

The wider question of getting a system to the point where changes like this are routine rather than terrifying is what our MVP to Production Engineering work is about, and the same care around data correctness shows up in Designing Idempotent, Versioned APIs.

A migration is not done when it runs. It is done when you can undo the deploy that depended on it.