Zero-Downtime Schema Migrations

In one line

Schema changes run while old and new application versions are both live, so every migration must be backward compatible. The expand–contract pattern adds new structures, dual-writes and backfills, switches reads, then removes the old. Versioned migration tools (Flyway, Liquibase) apply changes in order, and PostgreSQL DDL must be written to avoid long locks.

0/5 · 0%

Think of it like this

Renovating a road while traffic keeps flowing. You build the new lane beside the old, divert traffic in stages, and only then dig up the old lane. Closing the road to rebuild it would be a "big-bang migration".

Key ideas

01
Rolling deploys mean old code runs against the new schema for a while (and during a rollback). So: never drop or rename a column the running code uses; add columns as nullable or with a constant default; make new code tolerate both shapes.
02
Expand–contract rename (column name → full_name): (1) add full_name; (2) dual-write both (app or trigger); (3) backfill in batches; (4) switch reads to full_name; (5) stop writing name; (6) drop name in a later release.
03
Safe DDL in PostgreSQL: ADD COLUMN with a constant default is instant (PG 11+); CREATE INDEX CONCURRENTLY; ADD CONSTRAINT ... NOT VALID then VALIDATE; add NOT NULL via a validated CHECK (col IS NOT NULL) then SET NOT NULL (PG 12+ uses the check to skip the scan); changing a column type usually rewrites the table, so add a new column instead.
04
Always SET lock_timeout = '5s' in migrations and retry: a DDL waiting for its lock blocks every query queued behind it.
05
Backfills: batches of 1K–10K rows by primary key range, committed separately, throttled to watch replication lag, resumable; verify with counts and checksums. Flyway: versioned V7__add_full_name.sql files, checksums, and flyway validate in CI.

Code & diagrams

V42__add_not_null_safely.sqlsql

SET lock_timeout = '5s';

-- expand: new nullable column (instant)
ALTER TABLE customer ADD COLUMN full_name text;

-- backfill in batches (run from a job, repeated until 0 rows)
UPDATE customer SET full_name = name
WHERE id IN (SELECT id FROM customer WHERE full_name IS NULL LIMIT 5000);

-- enforce NOT NULL without a long lock
ALTER TABLE customer ADD CONSTRAINT customer_full_name_nn CHECK (full_name IS NOT NULL) NOT VALID;
ALTER TABLE customer VALIDATE CONSTRAINT customer_full_name_nn;   -- scans, but allows writes
ALTER TABLE customer ALTER COLUMN full_name SET NOT NULL;         -- instant: uses the valid check
ALTER TABLE customer DROP CONSTRAINT customer_full_name_nn;

-- index without blocking writes (cannot run inside a transaction block)
CREATE INDEX CONCURRENTLY customer_full_name_idx ON customer (full_name);

expand-contract.mermaiddiagram

Rendering diagram…

Interview problem

The problem

Split `address` out of a 300M-row users table

Users have address columns inline; you need a separate user_address table (users can have several). The table has 300M rows, 5K writes/sec, and deployments are rolling. Plan the migration with rollback at each step.

When it breaks

ALTER TABLE ... ALTER COLUMN TYPE on a large table

What you see

The table is rewritten under an ACCESS EXCLUSIVE lock for tens of minutes; the application is down.

Fix & prevent

Add a new column of the new type, dual-write, backfill, switch, drop, the same expand–contract steps.

Dropping a column while old pods still select it

What you see

During the rolling deploy, old pods fail with "column does not exist"; if the deploy is rolled back, the old version can't start.

Fix & prevent

Stop using the column in one release, drop it in a later one; ORMs must not select all columns implicitly after the drop.

Explain it without notes

Why must migrations be backward compatible with the previous application version?

Practice

Plan adding a UNIQUE constraint on email to a busy table containing duplicates.

Trade-offs

↔
Expand–contract takes several releases and temporary dual-writes, in exchange for zero downtime and safe rollback at every step.

Done when you can

I can plan and write zero-downtime migrations with safe DDL, backfills and rollback.

Command Palette