The scariest command in most codebases isn't rm -rf. It's ALTER TABLE — run against a production database, at deploy time, by a migration tool written to make development convenient, not to keep a live system live. Most teams learn this the same way: a migration that ran instantly against a 200-row dev database takes a lock on a 40-million-row production table, every request queues behind it, connection pools saturate, and the site is down until someone kills the query. No code was wrong. The schema change was fine. The choreography was wrong.
We build and operate web products for clients at Luminary, which means we ship schema changes to systems we don't get to turn off. The good news: zero-downtime migrations aren't dark magic. They're one pattern — expand, migrate, contract — plus a working knowledge of what your database actually does when you run DDL, plus a few rules about deploy ordering. This post covers all three, with Postgres as the primary lens because that's what we deploy most.
Why naive migrations take sites down
Three failure modes account for almost every migration-induced outage we've seen or read a postmortem about.
DDL takes locks, and locks queue
In Postgres, most forms of ALTER TABLE take an ACCESS EXCLUSIVE lock — the strongest there is, conflicting with everything including plain SELECTs. That alone isn't fatal; many DDL operations hold it for milliseconds. The killer is the lock queue.
Suppose an analytics query has been reading orders for four minutes. Your ALTER TABLE orders ... arrives and waits politely behind it. But Postgres lock queues are fair: every query arriving after your ALTER TABLE — including every trivial single-row SELECT your app fires per request — now waits behind it, which is waiting behind the analytics query. A DDL statement that would have executed in 5ms has taken your table offline for four minutes. The lock you take matters less than the lock you wait for.
Some DDL rewrites the whole table
Certain operations don't just lock — they physically rewrite every row: changing a column's type (in most cases), adding a column with a volatile default, CLUSTER, VACUUM FULL. On a large table that's minutes to hours of exclusive lock, double the disk usage, and a flood of WAL hammering your replicas. A rewrite during business hours is an outage you scheduled for yourself.
ORM auto-migrations run at deploy time
Most frameworks bundle migrations into deployment: the new code ships, prisma migrate deploy or rails db:migrate runs, the app boots. That coupling is convenient and dangerous. DDL runs at peak traffic with whatever locks it wants, a slow migration blocks your deploy pipeline (and your rollback), and the tool happily generates whatever SQL expresses your schema diff — including a table rewrite — without warning that the same statement behaves completely differently at production scale. Migration tools are diff engines, not safety engines. The safety has to come from you.
The pattern: expand, migrate, contract
Every safe schema change follows the same three-phase shape:
- Expand. Add the new thing alongside the old thing. New column, new table, new index — purely additive, invisible to running code.
- Migrate. Move behavior and data over gradually: write to both, backfill history in batches, then switch reads to the new thing.
- Contract. Once nothing reads or writes the old thing, remove it.
Each phase is a separate deploy, and at every intermediate point the system is fully consistent and fully working. That's the whole trick: you never make a change that the currently-running code can't tolerate, and you never make a change you can't pause halfway through.
Worked example: renaming a column the safe way
ALTER TABLE users RENAME COLUMN username TO handle is instant in Postgres — and it will still take your site down, because the moment it commits, every running app server that queries username starts throwing errors until the new code finishes rolling out. A rename is a breaking change smuggled inside a fast one. Here's the non-breaking version, as it actually ships:
Step 1 — Expand: add the new column. Nullable, no default. This is a metadata change, effectively instant.
ALTER TABLE users ADD COLUMN handle text;
Step 2 — Dual-write. Deploy app code that writes both columns on every insert and update, while still reading from username. If your ORM makes dual-writes awkward, a trigger works and covers writers you forgot about (admin scripts, other services):
CREATE OR REPLACE FUNCTION sync_username_to_handle() RETURNS trigger AS $$
BEGIN
NEW.handle := NEW.username;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER users_sync_handle
BEFORE INSERT OR UPDATE OF username ON users
FOR EACH ROW EXECUTE FUNCTION sync_username_to_handle();
From this point on, no new row can have username without handle.
Step 3 — Backfill in batches. Copy the historical rows. Never as one statement — see the backfill section below for why and how.
Step 4 — Switch reads. Deploy app code that reads handle. Writes still go to both. Let it soak; this is your cheap rollback point — flipping reads back is a config change, not a data operation.
Step 5 — Stop old writes. Deploy app code that no longer touches username, drop the trigger, and verify with logs or pg_stat_statements that nothing queries the old column anymore.
Step 6 — Contract: drop the old column.
ALTER TABLE users DROP COLUMN username;
DROP COLUMN is fast in Postgres (it doesn't rewrite; the column is just marked dead), but it's also the only irreversible step — which is exactly why it's last, days or weeks after everything else, when you have maximal evidence it's safe.
Six steps to rename a column feels absurd until you compare it to the alternative: one step and an outage. This is the discipline; the rest of this post is Postgres-specific detail in service of it.
Postgres specifics: what's instant, what isn't
Adding columns and defaults
ADD COLUMN with no default, or with a constant default, is metadata-only on any modern Postgres (11+, thanks to "fast defaults" — the default is stored once in the catalog and materialized lazily on read). The old add-nullable-then-backfill-the-default dance is unnecessary for constants:
-- Instant, even on a huge table:
ALTER TABLE orders ADD COLUMN status text NOT NULL DEFAULT 'pending';
-- Table rewrite — volatile default, every row must be computed:
ALTER TABLE orders ADD COLUMN ref uuid NOT NULL DEFAULT gen_random_uuid();
The volatile case (per-row UUIDs, random()) still rewrites. Handle it as expand/backfill: add the column nullable, backfill in batches, then add the constraint.
Indexes: always CONCURRENTLY
Plain CREATE INDEX blocks all writes to the table for the duration of the build. On a big table that's an outage. Use the concurrent form, always:
CREATE INDEX CONCURRENTLY idx_orders_customer_id ON orders (customer_id);
Caveats: it can't run inside a transaction block (many migration tools wrap every migration in one — opt out per-migration), it does more total work than a plain build, and if it fails or is cancelled it leaves an INVALID index that consumes write overhead until you DROP INDEX CONCURRENTLY and retry. Check for INVALID indexes after every concurrent build.
NOT NULL and foreign keys: NOT VALID, then VALIDATE
Adding a constraint naively forces a full-table scan under an exclusive lock — Postgres has to prove every existing row complies before it lets go. The escape hatch is two-phase validation. NOT VALID adds the constraint with only a brief lock and no scan: it's enforced for new writes immediately, but existing rows are taken on faith. Then VALIDATE CONSTRAINT does the long scan under a SHARE UPDATE EXCLUSIVE lock, which does not block reads or writes:
-- Foreign key, safely:
ALTER TABLE orders
ADD CONSTRAINT fk_orders_customer
FOREIGN KEY (customer_id) REFERENCES customers (id) NOT VALID;
-- Later (separate transaction; long-running but non-blocking):
ALTER TABLE orders VALIDATE CONSTRAINT fk_orders_customer;
For NOT NULL the story depends on your version. On Postgres 12–17, the trick is a CHECK constraint: add CHECK (col IS NOT NULL) NOT VALID, validate it, then run SET NOT NULL — Postgres sees the validated check constraint and skips the table scan — then drop the now-redundant check:
ALTER TABLE orders
ADD CONSTRAINT orders_status_not_null CHECK (status IS NOT NULL) NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT orders_status_not_null;
ALTER TABLE orders ALTER COLUMN status SET NOT NULL; -- scan skipped
ALTER TABLE orders DROP CONSTRAINT orders_status_not_null;
Postgres 18 finally made this first-class: NOT NULL constraints can be added NOT VALID directly and validated later, no check-constraint detour needed.
lock_timeout and retries: the safety net
Remember the lock-queue problem — fast DDL waiting behind a slow query takes the table down. The fix is to refuse to wait:
SET lock_timeout = '3s';
ALTER TABLE users ADD COLUMN handle text;
If the lock isn't acquired within three seconds, the statement fails instead of damming up the queue. Wrap it in a retry loop with backoff in your migration runner. A migration that retries five times over two minutes is invisible to users; one that waits two minutes for a lock is an incident. Set statement_timeout alongside it so a statement you expected to be instant can't silently become an hour-long rewrite. This one habit — lock_timeout on every DDL statement — prevents more outages than any other single change to your migration process.
Backfills that don't melt your replicas
The migrate phase usually touches millions of existing rows. One big UPDATE users SET handle = username WHERE handle IS NULL is a trap three times over: it locks every row it touches for the duration of one giant transaction, it generates a WAL burst that spikes replication lag, and in Postgres it produces a dead tuple per updated row, bloating the table faster than autovacuum can keep up.
Backfill in small batches, keyed by primary key — not OFFSET, which rescans everything it skips and degrades quadratically. Keyset batching stays fast at row one and at row fifty million:
-- Run repeatedly, advancing :last_id each iteration; stop when 0 rows updated.
WITH batch AS (
SELECT id FROM users
WHERE id > :last_id AND handle IS NULL
ORDER BY id
LIMIT 1000
FOR UPDATE SKIP LOCKED
)
UPDATE users u
SET handle = u.username
FROM batch
WHERE u.id = batch.id
RETURNING u.id;
Each batch is its own transaction: short locks, small WAL units, dead tuples that autovacuum can digest incrementally. SKIP LOCKED keeps the backfill from ever contending with live traffic — if the app is touching a row, skip it; the dual-write covers it anyway.
Then pace it. Sleep 50–500ms between batches, and check replication lag as you go:
SELECT client_addr,
pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS lag_bytes
FROM pg_stat_replication;
If lag climbs past your threshold, pause until it recovers. A backfill that takes six hours and nobody notices beats one that takes twenty minutes and pages the on-call. Run it as a supervised script or a background job with a progress counter — never inside a deploy-time migration (more on that below).
The deploy-order problem: N and N+1 must coexist
Here's the constraint that generates the whole expand/contract pattern: during any deploy, old code and new code run against the same database at the same time. Rolling deploys guarantee it — for some window, versions N and N+1 both serve traffic. And if you ever roll back, version N runs against the schema version N+1 created.
So the rule is: every schema state must be compatible with two adjacent code versions, and every code version must be compatible with two adjacent schema states. That's precisely why the rename took six steps — each step changes only one side of the contract at a time.
The corollary: migrations should deploy separately from code. Run migrations as their own pipeline step (or their own deploy entirely), verify they completed, then roll out the code that depends on them. This decouples the failure modes — a slow migration can't wedge your app rollout, and rolling back code never requires rolling back schema. It also forces the good habit: if the migration ships first, the schema change must be one that current production code tolerates, which is exactly the expand-phase property you wanted anyway.
Framework realities: Prisma, Drizzle, Rails, and friends
Migration tools solve a real problem — versioned, ordered, repeatable schema changes across environments. What they don't solve is safety. It's worth being precise about the boundary.
What they give you: a serialized history of changes, drift detection, generated DDL from schema diffs, and a single command in CI. What they don't give you: any awareness that orders has 80 million rows. Prisma will cheerfully generate a column-type change that rewrites the table. Drizzle will diff your schema and emit a plain CREATE INDEX. Rails' add_column with a default was a rewrite for years of Postgres history, and a rename in any of these tools generates an actual RENAME, breaking-deploy problem intact. The tool expresses your intent; the expand/contract choreography is on you, usually by hand-editing the generated SQL. Treat generated migrations as drafts, not artifacts.
Two mitigations we consider table stakes:
- Lint your migrations. Squawk is a Postgres migration linter that runs in CI and flags the classics: non-concurrent index builds,
ADD COLUMNwith volatile defaults, constraints added withoutNOT VALID, missinglock_timeout, type changes that rewrite. Rails teams havestrong_migrations, which does the same job as a runtime guard. A linter turns tribal knowledge into a failing check, which is the only form of tribal knowledge that survives team turnover. - Configure safe defaults. Set
lock_timeoutandstatement_timeoutin the migration runner's connection settings so every migration inherits them, instead of hoping every author remembers.
Schema migrations are not data migrations
A schema migration changes structure: add a column, create an index, add a constraint. A data migration changes contents: backfill fifty million rows, recompute denormalized totals, move data between tables. Migration tools happily let you put both in the same file. Don't.
Deploy-time migrations should be fast and boring — seconds, not hours. A big data move embedded in one holds your deploy pipeline hostage, runs unsupervised at whatever time the deploy happens, usually can't be paused or resumed, and if it dies at row 30 million leaves a half-applied migration your tool considers failed. Data migrations belong in background jobs or supervised scripts: resumable (persist the last-processed key), throttled, observable, decoupled from deploys. The schema migration creates the space; a separate process moves the stuff.
Rollback thinking: forward-only, but reversible steps
Most tools support "down" migrations, and in production they're mostly fiction. Reversing DROP COLUMN doesn't recover the data. Reversing a backfill is meaningless. And by the time you want to roll back, new writes have landed on the new schema. We treat production as forward-only: if a migration is wrong, you write a new migration that fixes it.
But forward-only doesn't mean reckless — it means designing each step so the recovery path is another cheap forward step. Expand/contract gives you this for free: at every intermediate stage of the rename, "rollback" is just redeploying the previous app version, because both schema states support both code versions. The only step with no undo is the final DROP, which is why it comes last, after a soak period, when the old column has been provably unread for days. Keep down migrations for local development if your tool wants them; don't build your incident plan on them.
MySQL, briefly
The pattern is identical; the mechanics differ. InnoDB's online DDL handles many operations without blocking reads or writes (ALGORITHM=INPLACE), and recent versions do some — like adding a column — as pure metadata changes (ALGORITHM=INSTANT). Always specify the algorithm explicitly so the statement fails rather than silently degrading to a copying operation. For cases online DDL can't handle, or where replica lag is unacceptable, the ecosystem's answer is external tools: gh-ost (GitHub's trigger-less tool, replaying changes via the binlog) and pt-online-schema-change (Percona's trigger-based equivalent). Both build a ghost copy of the table, migrate data in throttled chunks, and atomically swap — expand/migrate/contract, automated at the table level.
Test against production-shaped data
Every dangerous migration looks instant against dev seed data. The behaviors that hurt — lock queues, rewrites, replication lag — only exist at scale, so your rehearsal environment needs scale. In ascending order of fidelity: a synthetic dataset inflated to production row counts; a restored, PII-scrubbed production backup (real distributions, real bloat, real index sizes); or a copy-on-write branch of production, which several managed Postgres platforms now provide cheaply enough to make "run the migration against yesterday's production" a CI step rather than a ritual.
At minimum, before any non-trivial migration, run it against a production-sized copy and record: wall-clock time, locks taken (pg_locks while it runs), whether the table was rewritten (compare pg_relation_size before and after, or watch for the telltale disk spike), and WAL generated. If you can't answer "how long will this hold what lock," you're not ready to run it in production.
The checklist
Before any production migration, we want yes-answers to all of these:
- Is this change purely additive, or does it modify/remove something live code depends on? (If the latter: expand/contract it.)
- Does every statement set
lock_timeout, with a retry loop around DDL? - Any
CREATE INDEX→ is itCONCURRENTLY, outside a transaction, with a check forINVALIDafterward? - Any new constraint → is it
NOT VALIDfirst,VALIDATEsecond? - Any column default or type change → confirmed metadata-only, not a rewrite, on our Postgres version?
- Is all bulk data movement out of the deploy-time migration and into a batched, resumable, lag-aware backfill?
- Do migrations deploy before (and separately from) the code that needs them?
- Can version N of the app run correctly against the post-migration schema?
- Was the migration rehearsed against production-shaped data, with lock time and duration recorded?
- Is the destructive step (
DROP) its own migration, scheduled only after a soak period with evidence the old path is dead?
Takeaways
- Outages come from lock queues and table rewrites, not from DDL being inherently slow — a 5ms
ALTER TABLEwaiting behind a 4-minute query blocks everything behind it. - Expand, migrate, contract: add the new alongside the old, move over gradually, remove the old last. Every intermediate state must work with both adjacent code versions.
- A column rename is a breaking change; the safe version is add → dual-write → backfill → switch reads → stop old writes → drop.
- In Postgres: constant defaults are instant (11+), volatile defaults rewrite;
CREATE INDEX CONCURRENTLYalways; constraints viaNOT VALID+VALIDATE;NOT NULLvia the check-constraint trick (12–17) or nativelyNOT VALID(18+). lock_timeoutplus retries on every DDL statement is the single highest-leverage safety habit.- Backfill with keyset batches, short transactions, sleeps, and an eye on
pg_stat_replication— never one giantUPDATE, never inside a deploy-time migration. - ORMs generate intent, not safety. Hand-edit generated SQL when needed and lint migrations in CI (squawk, strong_migrations).
- Production is forward-only; design steps to be individually reversible and put the irreversible
DROPlast, after a soak. - Rehearse against production-shaped data. If you can't say how long a migration holds which lock, you don't know what it does.