Zero-downtime migrations

Change the schema in steps the app can survive

A migration is dangerous when one command has to touch every row, or hold a lock that stops traffic, or land in the same instant as the code that depends on it. The expand-contract pattern removes all three by spreading a change across several small, independent deploys. You add the new shape, move data and code onto it while the old shape still works, and only then remove the old shape.

The rule underneath every step: at no point does a running version of the app depend on a change that has not shipped yet, and at no point is the old version broken by a change that has. That overlap is what lets you deploy and roll back one piece at a time.

Read the diagram top to bottom as six separate deploys. "Expand" is the first two: the column exists and is being written. The middle two move the reads. "Contract" is the last one, the only step that destroys anything, and it waits until nothing references the old column.

Add a column without rewriting the table

Adding a column is the most common migration and the easiest to get wrong on a large table. In PostgreSQL 11 and later, adding a column with a non-volatile (constant) default stores that default in the table's catalog and hands it back for existing rows, so the table is not rewritten and the lock is held only for a moment.

-- Fast: constant default, stored in catalog, no row rewrite.
ALTER TABLE users ADD COLUMN status text DEFAULT 'active';

-- Slow: a volatile default is evaluated per row, so the whole
-- table and its indexes are rewritten under ACCESS EXCLUSIVE.
ALTER TABLE users ADD COLUMN seen_at timestamptz DEFAULT clock_timestamp();

Wrong "Adding a column with a default always rewrites the table, so add it nullable and backfill by hand." A constant default has not required a rewrite since Postgres 11. Right a constant default is fine and fast; only a volatile default, a stored generated column, or an identity column forces the rewrite.

MySQL 8.4 has the same idea under a different name. Adding a column runs with ALGORITHM=INSTANT, which changes metadata only and permits concurrent reads and writes. Naming the algorithm makes the intent explicit and fails loudly if the operation cannot meet it:

ALTER TABLE users ADD COLUMN status VARCHAR(16), ALGORITHM=INSTANT;

Backfill in batches, never in one UPDATE

Filling an existing column for every row is the step people run as a single statement, and it is the step that takes the site down. One UPDATE users SET ... over a large table is a single transaction: it writes a version of every row, generates a huge amount of WAL, bloats the heap with dead tuples, and holds one snapshot open the whole time, which stalls vacuum and lags replicas.

Do it in bounded chunks, committing between them, keyed on the primary key so each batch is an indexed range:

-- Repeat, advancing :last_id, until no rows remain.
UPDATE users
SET email_normalized = lower(email)
WHERE id > :last_id AND id <= :last_id + 5000
  AND email_normalized IS NULL;

Each batch is a short transaction. Between batches you can pause, watch replica lag, and stop without leaving a half-finished lock. A migration framework or a small script drives the loop; the point is that no single statement is long.

Wrong "Batching is slower because of the per-statement overhead, so one UPDATE is more efficient." The single UPDATE is only faster on paper. In production it is the long transaction that bloats the table, blocks vacuum, and lags every replica behind the primary. Right bounded batches trade a little throughput for the ability to run without freezing live traffic.

Ship the schema and the code as separate deploys

The instinct to bundle the migration with the feature code into one atomic release is the instinct to fight. If the new column and the code that reads it go out together, there is a window where one server has the code but the column is missing, or the column exists but old servers still write only the old field. Both break.

Expand-contract fixes this by ordering: the column ships and is written before any code reads it, and old readers are gone before the old column is dropped. Each deploy is small enough to roll back on its own, because until the final drop every change is additive. The database stays compatible with both the version of the app you are deploying and the one you are replacing.

ACCESS EXCLUSIVE and the lock queue

The failure that turns a one-second migration into an outage is not the ALTER's own runtime. It is the queue. In PostgreSQL, most ALTER TABLE forms take an ACCESS EXCLUSIVE lock, which conflicts with every other lock mode, including the ACCESS SHARE that a plain SELECT acquires. So an ALTER cannot start while any transaction is reading the table. That much is expected. The part that bites: Postgres grants locks roughly first-come-first-served, so once the ALTER is waiting, every query that arrives after it waits behind it, even queries that would not have conflicted with the long read at all.

A metadata-only ALTER that would run in a millisecond still freezes the whole table for as long as the slowest transaction ahead of it. The fix is to cap the wait, not the work. Set a short lock_timeout on the migration session so the ALTER gives up instead of holding the queue, then retry:

SET lock_timeout = '2s';
ALTER TABLE orders ADD COLUMN note text;   -- fails fast if it cannot get the lock; retry

Wrong "The ALTER is metadata-only, so it is instant and cannot cause downtime." Right its own work is instant; the danger is the ACCESS EXCLUSIVE request parking at the head of the lock queue behind one long read while new traffic stacks up behind it. A short lock_timeout with a retry loop is the standard guard, and it is why serious migration tooling wraps every DDL statement in one.

This is DDL lock behavior specifically. General lock mode theory, MVCC, and deadlock cycles live in the locking-and-deadlocks module; here the only question is which schema changes take which lock, and for how long.

Which changes rewrite, which only touch metadata

Before running a statement, know whether it rewrites the table (holds ACCESS EXCLUSIVE for the length of a full rewrite) or only edits catalog metadata (holds it for an instant). Version-anchored, for PostgreSQL 16 and MySQL 8.4 InnoDB:

Two entries are the ones interviews probe. On MySQL, a column-type change is ALGORITHM=COPY with no concurrent DML, which is exactly the case an online schema-change tool exists for. On Postgres, SET NOT NULL scans the entire table while holding ACCESS EXCLUSIVE, and there is a way around it.

Enforce NOT NULL and constraints without a full scan

A plain SET NOT NULL reads every row to prove none is null, and that scan runs under ACCESS EXCLUSIVE, so writes are locked out for its whole duration. Add the constraint in two moves instead. First a CHECK marked NOT VALID, which skips the scan and takes the lock only briefly, but is enforced against every new insert and update from that moment. Then VALIDATE CONSTRAINT, which checks the pre-existing rows under a SHARE UPDATE EXCLUSIVE lock that does not block writes:

ALTER TABLE users
  ADD CONSTRAINT email_normalized_not_null
  CHECK (email_normalized IS NOT NULL) NOT VALID;   -- brief lock, no scan

ALTER TABLE users VALIDATE CONSTRAINT email_normalized_not_null;  -- SHARE UPDATE EXCLUSIVE

Foreign keys follow the same shape: ADD ... NOT VALID then VALIDATE CONSTRAINT. And when you need an index during a migration, CREATE INDEX CONCURRENTLY builds without blocking reads or writes, at the cost of two table scans and one caveat: if it fails partway it leaves an invalid index behind that you must DROP INDEX before retrying, and it cannot run inside a transaction block.

Wrong "Split the migration into steps but run each ALTER as written; the engine handles the locking." Right the sequence only helps if each step is chosen to avoid a rewrite or a scan under ACCESS EXCLUSIVE. NOT VALID then VALIDATE, CONCURRENTLY, and constant defaults are the specific moves that keep the lock brief.

Online schema-change tools and the cutover

When a change forces a rewrite the database cannot do online (a MySQL column-type change, a primary-key rebuild), you stop fighting the ALTER and copy the table beside itself. Both major tools do the same three things: build an empty table with the new schema, copy existing rows in chunks, and keep the copy current with live writes until an atomic RENAME swaps it in. They differ in how they capture the live writes.

pt-online-schema-change is trigger-based. It puts AFTER triggers on the source table so every insert, update, and delete is mirrored into the copy. The cost is that each write now runs the trigger body inside the same transaction, competing for locks, and Percona's docs note the tool will not start if the table already has triggers of its own. gh-ost is triggerless: it connects as a replica and reads the binary log, then applies those changes to the ghost table asynchronously, off the write path. gh-ost's own documentation cites triggers running in the same transaction space as the source of lock contention and "near or complete lock downs" as the reason it avoids them.

Wrong "The tools make the migration lock-free, so a rename needs no application changes; the RENAME is atomic." Right the RENAME swaps table storage atomically and briefly, but it does not coordinate your application. A rename or a type change through these tools still rides on expand-contract at the app layer: the new column live, dual-writes in place, old readers gone, before you drop the old one.

Dual-write, dual-read, and staying revertible

The property that makes a migration zero-downtime is that every step before the last is additive and reversible. Dual-write is the hinge: for a window, the application writes both the old and new representation, so the two are kept in sync while you backfill history and move reads. Then a dual-read or fallback window lets you point reads at the new column while keeping the old one intact, so a bad deploy reverts by flipping reads back rather than by restoring data.

Sequence it so the safe overlap always exists. Dual-write goes live before the backfill, or new rows written during the copy are lost. Reads move to the new column before you enforce NOT NULL, so the constraint never fires on a row the app has not populated. And the destructive DROP COLUMN waits a deliberate window after the last deploy that referenced the old column, because that drop is the one step you cannot take back without a restore. Until then, any step can be undone by redeploying the previous version.