Database Migrations at Scale: Zero-Downtime Schema Changes and Rollback Safety

Database Migrations at Scale matter because a one-line schema change can lock a hot table. Writers queue, the pool fills, and the site stalls. You can still change the schema. You have to split the change into steps that old code can survive.

What a Safe Migration Is

A safe migration changes the schema while the app keeps serving traffic. Old code and new code run at the same time during a rollout. Each step must be valid for both. If a step needs the new binary only, you have a downtime window you did not plan.

The usual shape is expand, then migrate, then contract. Expand adds something nullable or optional. Migrate backfills and switches reads.

Contract removes the old shape only after no code uses it. Skip contract in the same release as expand.

Small databases hide the cost. A table lock of one second is a blip in staging. On a large table, the same lock waits on a long query and then blocks every writer. The outage is the lock queue, not the alter itself.

You also need a way back. Some steps cannot be reversed without data loss. Dropping a column is one of them.

Plan the undo before you run the forward step. If you cannot undo, do not ship that step on a Friday.

Why It Fails in Production

The common failure is a lock that outlives the statement you timed. An alter waits for a quiet snapshot. A report holds that snapshot open.

Your alter waits, and every later writer waits behind the alter. Users see timeouts. You see idle CPU.

I have seen a NOT NULL change do this. The rewrite looked fast in a copy of the table. In production it waited on one analytics query, then blocked checkout for minutes.

The fix was to cancel the alter, not to add capacity. Set a lock timeout so the alter gives up instead.

Backfills fail in a different way. One big update bloats the table, fills the log, and holds row locks in chunks that are still too wide. Replicas lag.

Read-your-writes breaks. A batched backfill with pauses keeps lag inside a bound you chose.

Code and schema can also drift. New code expects a column that the alter has not added yet. Or old code inserts without a value and a new constraint rejects it.

Both show up in the middle of a rolling deployment. The mixed fleet is the real test, not a green migration job.

How to Structure the Change

Start by writing the end state and the steps backward. If the end state drops a column, the step before that must stop all reads and writes of it. The step before that must move readers to the new column. Only then is the drop safe.

Add columns as nullable, or with a default the database can store without a full rewrite. PostgreSQL can add a constant default without rewriting every row in current versions. Still, confirm the version you run. The PostgreSQL ALTER TABLE notes list which forms rewrite the table.

Create indexes without blocking writes. In PostgreSQL, CREATE INDEX CONCURRENTLY builds the index in phases and cannot run inside a transaction block. If it fails, it can leave an invalid index.

Drop the invalid one and retry. Do not ship code that depends on the index until the build is valid.

MySQL online DDL covers many alters, but not all of them are in place. Some still copy the table. Check the operation table before you run it on a primary. The InnoDB online DDL matrix is the list that matters, not a blog summary.

Backfill and cutover

Backfill in batches. Pick a key range, update that range, and sleep. Watch replica lag and write latency.

If lag climbs, increase the sleep. If a batch hits a lock timeout, retry that range only. Do not restart from the first key unless you made the update idempotent.

Cut reads over before you cut writes, or the other way around, but never both in one blind deploy. A common path is dual write, then backfill, then read from the new column, then stop the old write. Each of those is its own release. Schema versioning strategies are the rules for how long both shapes live.

App deploys and schema deploys should be separate pipelines. A schema step that is fast and compatible can run first. The binary follows. If you bundle them, a failed binary rollback can leave you on a schema the old binary rejects.

Trade-offs You Should Weigh

A short maintenance window is honest when the table is huge and the change must rewrite it. You stop writers, run the alter, and come back. You trade a planned outage for a simpler script. You should not pretend a rewrite is online.

Expand and contract takes more releases and more code. Old and new shapes coexist. You pay engineering time and a period of dual writes.

You avoid a lock outage. For a busy primary, that trade is usually the right one.

A shadow table plus cutover copies data beside the live table and then swaps names. Copy tools can keep up with writes. The swap still needs a brief lock or a trigger gap. Test the catch-up lag before you trust the swap.

Kubernetes will roll pods while the schema job runs. Read the deployment rollout behavior so you know old pods stay until the new ones pass checks. Your schema must tolerate that overlap. A migration that assumes a single version will break the pods that have not restarted.

Pitfalls and Failure Modes

First, do not add a NOT NULL constraint until every row is filled and every writer supplies a value. Add the column, backfill, deploy writers, then validate. In PostgreSQL, NOT VALID checks new rows first.

You can validate later without blocking writes as hard. Still, validation takes a lock briefly. Use a lock timeout.

Second, a unique index build can fail because of duplicates your backfill created. Clean the duplicates before you build. If you build concurrently and it fails, remove the invalid index. Leaving it costs planning time and confuses the next person.

Third, triggers that dual write can deadlock with the app. The trigger locks the other table while the app holds the first row. Keep trigger work tiny. If the dual write is heavy, do it in the app where you control order and retries.

Fourth, rollback of a destructive step is not a schema rollback. If you dropped a column, the old binary cannot read it back. Take a logical backup of that column before the drop, or delay the drop by a full release cycle. Rollback strategies for backend systems should name which steps are one way.

Fifth, statement timeouts in the migration tool can leave a half-built index or an open transaction. Know which steps are transactional. CREATE INDEX CONCURRENTLY is not.

A naive retry can start a second build. Make the runner idempotent and visible.

A Practical Expand Example

The steps below add a new status column without a table rewrite, backfill in ranges, and only then consider a constraint. Run the alter with a lock timeout so a busy table causes a retry instead of an outage. Do not drop the old column in this release.

SET lock_timeout = '2s';
ALTER TABLE orders ADD COLUMN status_v2 text;

UPDATE orders
SET status_v2 = status_text
WHERE order_key > 1000
  AND order_key <= 2000
  AND status_v2 IS NULL;

After the backfill, deploy code that writes both columns and reads the new one. Watch error rate and null counts. If nulls remain, the backfill missed a range or a writer still uses the old column only.

Fix that before any constraint. A common mistake I have seen is to add NOT NULL in the same pull request as the new column.

Order of work

Keep the runner boring. One step, one check, then the next. If a check fails, stop. Do not continue into contract steps on a partial expand.

1. Add the new column as nullable.
2. Dual write from the new binary.
3. Backfill old rows in small ranges.
4. Switch reads after nulls hit zero.
5. Drop the old column in a later release.

Performance, Scale, and Cost

Online index builds and backfills are IO and log heavy. They compete with user traffic. Run them when the primary has headroom, or throttle until replica lag stays inside your read budget. In an illustrative production range, an unthrottled backfill on a large table can push replica lag from under a second to minutes.

Disk is part of the plan. A shadow copy needs roughly another copy of the table plus indexes. A concurrent index needs space for the new index before the old one is dropped.

Fill the disk mid-build and you will stall writes. Check free space before you start, with margin for bloat.

Connection cost is easy to miss. A migration session that waits on a lock holds a backend. Your app pool then waits too.

A lock timeout of a couple of seconds keeps the migration from becoming a connection leak. Retry from the runner, not by leaving the session open.

Scale-out reads do not make alters cheaper. The alter still hits the primary. Extra replicas make lag more visible and make a bad backfill more expensive, because each replica replays the same updates. Throttle once, on the primary, and watch every replica.

Engineer time is the other cost. Expand and contract takes more reviews than one alter. That time is cheaper than a checkout outage on a large table.

Use the fast path only when the engine documents the change as metadata-only. When you are unsure, assume it locks.

API clients feel schema changes when response fields move. Coordinate with API versioning strategies so you do not rename a JSON field in the same hour you rewrite the column. Keep the external shape stable until the database cutover is done.

Key Takeaways

• Split schema changes into expand, migrate, and contract.
• Never drop or tighten a constraint in the same release that adds a column.
• Set a lock timeout on every alter so a wait cannot stall writers.
• Backfill in ranges and pause when replica lag grows.
• Keep old and new app versions able to run on the current schema.
• Treat drops as one-way steps and delay them.
• Separate the schema pipeline from the binary rollout.

FAQ

Can you run every alter online?

No. Some changes rewrite the table or take a strong lock. Read the engine matrix for your exact version.

If the form is not metadata-only, use expand and contract or a planned window. Guessing from a small staging table is how outages start.

When is it safe to drop the old column?

Drop it after every deploy, job, and report has stopped reading and writing it. Search the repo and the runtime metrics. Wait at least one full release.

Then drop in its own change with a backup of the values. If a rollback of the app is still possible, the column has to stay.

What if the backfill is too slow?

Slow is acceptable if lag and latency stay inside budget. Speed up only while those signals stay healthy. If you cannot finish in the time you have, narrow the change or move hot tenants first. A faster batch that stalls replicas is not a win.

Should the app start if the migration fails?

New pods should not assume a failed expand is present. Either block startup on a required column check or keep the code compatible with the old shape. A crash loop of new pods during a bad alter removes capacity. Prefer compatibility over a hard boot check for optional steps.

Take your next schema change and write the expand, backfill, read switch, and contract as separate steps. Put a lock timeout on the alter and a lag check on the backfill. Then ship the expand alone. If the mixed fleet stays healthy, schedule the contract for a later release after the old code is gone.

Last updated on 15 September 2026.