engineering
Zero-downtime Postgres migrations
Adding columns, renaming tables, and changing types without taking the site down.
Every serious schema change wants to be three deploys. New teams routinely ship them as one, take down production, and learn the rules the hard way.
Add-column with NOT NULL#
ALTER TABLE users ADD COLUMN tier TEXT NOT NULL rewrites every row. Worse, pre-11 versions hold an ACCESS EXCLUSIVE lock for the duration — reads and writes both block. The safe version:
- Deploy 1: add column as nullable with a default
- Deploy 2: backfill in batches, application writes to both old and new
- Deploy 3:
SET NOT NULL(validates, brief lock)
Renaming a column#
Never rename in place. ALTER TABLE foo RENAME COLUMN bar TO baz is trivial SQL, but your application still references bar. Release a version that reads both; release a version that writes the new name; release a version that drops the old.
Changing a column type#
ALTER COLUMN amount TYPE BIGINT rewrites rows on a large table. The right move is to add a new column, dual-write, backfill, cut over reads, then drop the old column. Boring and slow, but safe.
Foreign keys on populated tables#
Adding a FK without NOT VALID locks the referenced table for validation. Use ADD CONSTRAINT ... NOT VALID for the initial add, then VALIDATE CONSTRAINT in a separate transaction. No full-table lock.
Index creation#
CREATE INDEX CONCURRENTLY, always. Forgetting it blocks writes for the duration of the build.
Rehearse in staging with prod data#
A schema migration that takes 12 seconds on staging with 1k rows can take 12 hours in prod with 50M. Load production-sized data into staging before timing any migration you intend to ship.