Zero-Downtime Schema Migrations: Expand, Contract, Deploy in That Order

The deploy went green. The migration ran in under a second, the new column was there, and nothing in the log looked wrong. Then write latency on one table fell off a cliff for four minutes and took the checkout path with it. The statement responsible was an ordinary index build on a table that had grown past what anyone had in mind when the migration was written. The problem was not that indexing is expensive. It was that the statement needed a lock that excludes writes, and every write arriving behind it queued for the entire build.

Two different problems get filed under the same heading here, and they need different fixes. One is the lock a statement takes and how long it holds it. The other is that a schema change has two audiences at once — the code that is running during the deploy and the code that will be running after it — and a schema that satisfies only one of them is a broken deploy no matter how fast the migration ran. Fix the ordering and you still get taken down by a lock. Fix the locks and you still serve 500s from the half of the fleet on the old image. This is what expand and contract is for, and the lock discipline is what makes it survivable.

Two versions of the application are always running

A rolling deploy does not stop the world. Instances are replaced one at a time, so for the length of the rollout some requests are served by code that knows about the new column and some by code that does not. Blue-green has the same window on the other side of the switch. Even a single-server restart has one: the new process is up and querying while the old one finishes its in-flight requests, and any background worker, queue consumer or scheduled job is a separate deployment that will lag behind by minutes or hours. A mobile client pinned to an old API version is the same problem with a longer tail.

The useful way to hold all of that is a two-by-two. Rows are the code version, columns are the schema version, and only the diagonal is what your test suite exercises:

Both off-diagonal cells are reachable in production and neither is reachable in CI unless you build for it. The top-right is the deploy itself. The bottom-left is what happens when the new code turns out to be wrong and you redeploy the previous image — which is exactly the moment you are least able to also reverse the schema.

So the rule that generates everything else: every schema state must be correct for two consecutive code versions. A migration that satisfies that is safe to run before the code that uses it. A migration that does not is a synchronised cutover, and a synchronised cutover is downtime with extra steps.

Expand, then contract

The pattern is old enough to have several names — parallel change, expand and contract, the two-phase migration. It is worth restating as four rules because most unsafe migrations break one of them specifically.

Additive. The first phase only adds: a nullable column, a new table, a new index, a new constraint that is not yet enforced. Nothing that exists is renamed, retyped or removed, so old code continues to work unchanged.

Ignorable. Old code must be able to insert and update rows without knowing the new column exists, and the schema must not reject those rows. This is the rule people skip: adding NOT NULL to a column that only the new code populates means every write from an instance still on the old image fails. Enforce it after the old image is gone, not before.

Dual-written. The new code writes both the old and the new representation and reads the old one. One release later it reads the new one, falling back to the old if the new value is missing. Only after a backfill is verified does it stop writing the old.

Deleted late. The removal is a separate deploy, at least one release after the last reader disappeared, and only after you have evidence rather than an assumption that nothing reads it. Dropping a column is instantaneous and almost free; discovering at 02:00 that a report still selects it is neither.

The cost is honest and should be stated up front: a rename is not one deploy, it is five, spread over releases. That is the price of never taking a maintenance window, and it is why teams batch related changes into one coordinated rename instead of doing them one at a time for a quarter.

Postgres: the lock is the whole problem

Almost every DDL statement in Postgres takes an ACCESS EXCLUSIVE lock on the table and holds it until the transaction commits. That lock conflicts with reads as well as writes, which is why a migration can take an application down without a single error in the database log. What varies enormously is how long the lock is held — and whether the statement rewrites the table, which is a function of table size rather than of how the statement looks.

Four details in that table are worth their own paragraph, because each one has cost somebody a night.

The fast default only applies to constants. Since Postgres 11, ADD COLUMN x timestamptz DEFAULT now() is not a metadata change — a volatile default has to be evaluated per row, so the table is rewritten and the exclusive lock is held for the whole rewrite. Adding the column nullable and setting the default afterwards splits a multi-minute lock into two instant ones.

SET NOT NULL can borrow proof. Since Postgres 12, if a validated CHECK (col IS NOT NULL) constraint exists, the server skips the scan and SET NOT NULL becomes a catalogue update. The three-statement sequence in the table is the difference between an exclusive lock measured in seconds and one measured in the length of a full table scan.

A concurrent index build cannot run inside a transaction. CREATE INDEX CONCURRENTLY takes a weaker lock and does not block writes, but it takes two passes over the table and it cannot be wrapped in BEGIN. Plenty of migration runners wrap each file in a transaction by default, which turns this into a confusing syntax error at deploy time; the statement has to be marked non-transactional or run outside the runner. It also fails rather than rolling back cleanly: a deadlock or a unique violation leaves an INVALID index behind that still costs write throughput until somebody drops it and starts again.

Dropping is not reclaiming. DROP COLUMN only marks the attribute as gone; the space comes back when the table is rewritten or vacuumed over, whichever happens first. That is usually fine — but a team that drops a wide column expecting disk to return will be confused by the graph for a week.

Backfill is a job, not a migration

The moment a change needs existing rows to be transformed, the work stops being a migration. A single UPDATE across millions of rows runs as one transaction: it holds row locks for its whole duration, writes one enormous WAL stream, delays every replica, bloats the table, and if it fails at ninety per cent you get to start again. Nothing about it is resumable and everything about it is invisible until it is a problem.

Run it as a batch loop instead — outside the migration runner, with its own logging, its own throttle and the ability to stop and restart:

-- Repeat until it reports 0 rows. Keyset pagination, not OFFSET:
-- OFFSET re-scans every row it skips and gets slower with each batch.
WITH batch AS (
  SELECT id
  FROM   accounts
  WHERE  region_code IS NULL      -- the predicate is what makes it resumable
  ORDER  BY id
  LIMIT  1000
  FOR UPDATE SKIP LOCKED          -- a concurrent writer is skipped, not waited on
)
UPDATE accounts AS a
SET    region_code = region_for(a.country)   -- your mapping, in SQL or in the worker
FROM   batch
WHERE  a.id = batch.id;

The IS NULL predicate is doing more work than it looks like: it makes each batch idempotent, so a crash costs one batch rather than the whole run, and it makes a second pass cheap enough to schedule as a safety net.

That second pass matters because your dual write only covers writes that go through your application. An admin console, a bulk import, a support tool, a nightly job in another service, a colleague with a psql session — none of them know the new column exists, and all of them insert rows the app-level mapping never touches. Either the backfill runs to convergence after the code is fully rolled out, or you install a trigger for the duration of the change so that every writer maintains the new column whether or not it has been updated. The trigger is uglier and it is the more reliable of the two, because it does not depend on anyone remembering.

The gate before reads switch

A backfill that finished is not a backfill that worked. Before the release that switches reads to the new column, assert the invariant in both directions:

-- Both counts must be zero. A backfill that overwrote a correct value is
-- as wrong as one that missed a row, and only the second shows up as NULL.
SELECT
  count(*) FILTER (WHERE region_code IS NULL AND region IS NOT NULL) AS missed,
  count(*) FILTER (WHERE region_code IS DISTINCT FROM region)        AS mismatched
FROM accounts;

Run that against a replica, record the result somewhere a reviewer can see it, and treat a non-zero count as a blocked deploy rather than a rounding error. If the transform is more interesting than a lookup — a currency conversion, a normalised address — spot-check rows against the old value as well, because a systematic error passes a NULL check happily.

A rename, end to end

Renaming region to region_code is the smallest change that exercises every part of this. It takes five deploys and one job, and none of them are interesting on their own.

1. Deploy 1 — expand. One migration: ALTER TABLE accounts ADD COLUMN region_code text; Nullable, no default, no constraint. Old code is unaffected because it has never heard of the column.
2. Deploy 2 — dual write. New code writes both columns on every insert and update, and reads region. Writes that arrive from old instances still populate only region, which is why the next step exists.
3. Backfill job. Batched, throttled, restartable, logged. Runs after the rollout completes, not during it, and finishes with the two-count check above.
4. Deploy 3 — switch reads. Read region_code, fall back to region when it is null, keep writing both. The fallback is what makes this reversible: if the new column turns out to be wrong for a slice of rows, the old one is still there and still precise.
5. Deploy 4 — stop writing the old column. Only after the fallback has logged zero hits for a full release cycle. Now region is dead weight, and it is also the last cheap rollback you will have.
6. Deploy 5 — contract. Drop region. One-way. Wait until nothing could plausibly still read it: search the code, search the collected query text if you have it, and check the reports and exports before you believe the answer.

Deploy 5 is the one to be strict about. Until it runs, rolling back is a code deploy. After it runs, rolling back is a restore. That asymmetry is the entire reason the contract step waits a release instead of riding along with the read switch — and the reason a schema change should be reviewed by asking which of the five steps it is, not by asking whether the SQL looks correct.

Bound the wait instead of hoping

There is a failure mode that survives every rule above: the migration is safe, but it cannot get its lock. A long-running query holds a shared lock on the table, the ALTER TABLE queues behind it, and because Postgres grants locks roughly in arrival order, every subsequent read of that table queues behind the ALTER. One slow report becomes a table-wide outage that lasts as long as the report plus the migration — and the migration has not even started.

The fix is a timeout short enough that the migration gives up before it becomes the head of the queue, plus a bounded retry with backoff:

-- On the migration session only, never the application's.
SET lock_timeout = '3s';         -- give up rather than queue behind a reader
SET statement_timeout = '30s';   -- a rewrite here is a bug, not a slow query

BEGIN;
ALTER TABLE accounts ADD COLUMN region_code text;
COMMIT;

Retry only what is worth retrying: lock timeouts, deadlocks and serialisation failures are contention and will probably succeed on the next attempt. A syntax error, a constraint violation or a statement timeout will fail identically twenty times, and each attempt holds the exclusive lock for the full timeout while it does. Fail those on the first pass and let a human look.

Two supporting details. Run migrations from exactly one place — a job, not every application instance at boot, and not two CI runners that both noticed the same commit — and serialise them with an advisory lock so a second migrator waits rather than interleaving. And set idle_in_transaction_session_timeout on the application's connections: a session that opened a transaction and went to lunch holds a shared lock on everything it read, and it will block a migration indefinitely without appearing in any query log.

When the change cannot be additive

Some changes have no safe additive form: retyping a large column, splitting a table, changing a primary key, moving a hot table onto partitions. Expand and contract still describes the shape, but the expand step is a new table rather than a new column.

Create the new table alongside the old, write to both from the application, backfill in batches, and verify with a row count and a content checksum rather than a spot check. Then cut over in a single short transaction that does nothing but rename — accounts to accounts_old, accounts_v2 to accounts — under the same lock_timeout. The exclusive lock is held for milliseconds because a rename is a catalogue change; everything expensive happened before it.

Two things make this a project rather than a migration. Foreign keys, views, triggers, functions and anything with the table name baked into a string all point at the old name and have to be recreated against the new one. And the old table has to stay writable until the cutover is proven, which means the dual write has to be genuinely dual for the whole window — including the paths nobody thought about, which is the same lesson as the backfill.

Test the off-diagonal cells

CI proves the diagonal. The two cells that break deploys and rollbacks need to be produced deliberately, and both are cheap once you decide to do it.

Restore a production-shaped copy — anonymised, and large enough that the lock behaviour is real rather than theoretical — then run the migration and, against the migrated schema, run the previous release's test suite. That is the top-right cell: old code, new schema. If it fails, the expand step is not additive and the deploy will fail in the middle of the rollout with half the fleet on the new image.

The bottom-left cell is the reverse: check out the new code, point it at the pre-migration schema, and run the new tests. If that fails, the new code cannot be rolled back, which is worth knowing before you need it. It also catches a specific and common bug — new code that reads a column the migration has not yet added, in a deploy pipeline that runs code and migration in the wrong order.

Then add a linter to the pipeline for the statements that are known to be dangerous regardless of context. Tools like squawk encode the lock table above as rules and fail a pull request that adds a column with a volatile default or calls SET NOT NULL without a validated check. It is the same class of gate as a lint rule for a dangerous API call: not a substitute for review, but it catches the change somebody wrote at the end of a long day, and it catches it before the deploy rather than during it.

What this means in practice

1. Classify the change before writing the SQL. Additive, or a cutover? If it renames, retypes or removes anything, it is a multi-deploy change and should be planned as one.
2. Never rename or retype in place. Add the new shape, dual write, backfill, switch reads with a fallback, stop writing the old, drop it a release later.
3. Keep the schema valid for the previous release. No NOT NULL, no constraint and no dropped column that the currently deployed image cannot satisfy.
4. Backfill as a resumable job with keyset pagination, a throttle and a convergence pass for writers outside the application.
5. Gate the read switch on a verification query, not on the job reporting success.
6. Set lock_timeout on every migration session and retry only contention errors, so a busy table produces a retry instead of an outage.
7. Run the previous release's tests against the new schema in CI, and let a migration linter reject the known-dangerous statements.

None of this is exotic and none of it is new: it is the same discipline as any other backwards-compatible interface change, applied to the interface with the longest-lived clients and the least tolerance for a bad afternoon. The teams that find it hard are usually not writing bad SQL. They are writing correct SQL and deploying it in an order that assumes only one version of the application exists — which is true in the test suite and never true in production.

If a schema change on your roadmap needs a maintenance window, or if nobody can say which deploy is allowed to drop the old column, tell us what the table looks like — this is ordinary work in our custom software practice, and it sits next to two adjacent problems we have written about: retries and compensation, which is what idempotency looks like once a workflow spans several systems, and flaky end-to-end tests, which is what happens when the environment a change is verified in stops resembling the one it ships to.