Why Zero-Downtime Schema Migrations Need a Pattern
The outage rarely comes from the migration being slow. It comes from the lock. A single ALTER TABLE takes an ACCESS EXCLUSIVE lock, and while it waits for that lock every subsequent query on the table queues behind it — including the fast ones that would have finished instantly. Your table is not slow; it is stopped.
This is why the lock queue is the real enemy. PostgreSQL grants locks roughly in order, so one blocked DDL statement sitting behind a long-running SELECT will pile every new reader up behind itself. A migration that would have taken 2ms takes down the service for 90 seconds because an analytics query held a read lock. Zero-downtime schema migrations are mostly about never letting that queue form.
The second problem is subtler: your schema and your code deploy at different moments. There is always a window where old application instances talk to a new schema, and no amount of careful DDL removes it. Any migration that assumes code and schema change simultaneously will break during every rollout.
Expand and Contract, in Three Deploys
The pattern that solves both problems is expand-contract, and its rule is simple: every intermediate state must be compatible with both the old and new code. You never change a thing; you add the new thing, move to it, then remove the old thing — across separate deploys.
Renaming a column shows it clearly. The tempting one-liner, ALTER TABLE users RENAME COLUMN email TO email_address, is instant and takes almost no lock. It also breaks every running instance of your old code the moment it commits. Instead:
Deploy 1 — expand. Add email_address as nullable. Change the code to write to both columns and read from the old one. Both old and new code work: old instances use email, which is still maintained.
Deploy 2 — migrate. Backfill email_address for existing rows in chunks. Then switch reads to the new column while still writing both. Roll back at any point by flipping reads back.
Deploy 3 — contract. Once no code path touches email, stop writing it, then drop it. This deploy is the only irreversible one, so it should come days later, not minutes.
Three deploys to rename a column feels absurd until the first time a one-liner takes production down mid-afternoon. The same reasoning drives the incremental cutover in our strangler fig migration guide — you are always one step from reverting.
The Two Settings That Prevent Most Outages
If you take one thing from this: set lock_timeout on every migration. It converts an outage into a failed job.
-- Fail fast rather than queueing behind a long reader
SET lock_timeout = '3s';
SET statement_timeout = '30s';
BEGIN;
ALTER TABLE users ADD COLUMN email_address text; -- metadata-only, instant
COMMIT;Without lock_timeout, that ALTER waits indefinitely for its lock and blocks everyone behind it while it waits. With it, the statement gives up after three seconds and your deploy fails loudly — which is a good outcome. Retry it in a loop during a quieter minute; a failed migration is a Slack message, a lock queue is an incident.
Before you run anything, check what is holding locks. A migration that fails is fine; a migration that fails after blocking traffic for a minute is not.
-- Who is blocking whom, right now
SELECT blocked.pid AS blocked_pid,
blocking.pid AS blocking_pid,
blocked.query AS blocked_query,
blocking.query AS blocking_query,
now() - blocking.xact_start AS blocking_age
FROM pg_stat_activity blocked
JOIN pg_stat_activity blocking
ON blocking.pid = ANY(pg_blocking_pids(blocked.pid))
WHERE blocked.wait_event_type = 'Lock';Know Which DDL Rewrites the Table
Modern PostgreSQL is far better than its reputation, and a lot of received wisdom is stale. Several operations that used to rewrite the whole table are now metadata-only, meaning they are effectively instant regardless of table size.
Safe and instant: adding a nullable column; adding a column with a constant default (since PG 11, this no longer rewrites); dropping a column; renaming; adding a constraint as NOT VALID; increasing a varchar length limit.
Rewrites the whole table — dangerous at scale: adding a column with a volatile default such as gen_random_uuid(); most type changes; adding NOT NULL directly; VACUUM FULL. On a large table these hold ACCESS EXCLUSIVE for the entire rewrite.
The NOT NULL case is worth spelling out because it is so common and the safe path is genuinely non-obvious:
-- DON'T: scans the entire table under ACCESS EXCLUSIVE
ALTER TABLE orders ALTER COLUMN region SET NOT NULL;
-- DO: validate without blocking writes, in two steps
ALTER TABLE orders
ADD CONSTRAINT orders_region_not_null
CHECK (region IS NOT NULL) NOT VALID; -- instant, no full scan
ALTER TABLE orders VALIDATE CONSTRAINT orders_region_not_null;
-- takes only SHARE UPDATE EXCLUSIVE: readers and writers keep working
-- PG 12+ can then promote it cheaply, using the proven constraint
ALTER TABLE orders ALTER COLUMN region SET NOT NULL;
ALTER TABLE orders DROP CONSTRAINT orders_region_not_null;NOT VALID means “enforce this for new rows, do not check existing ones yet”. The separate VALIDATE then scans without an exclusive lock. Same destination, no outage.
Backfills Are Not Migrations
The most common self-inflicted wound is UPDATE users SET email_address = email; against ten million rows. It runs as one transaction, holds row locks on everything it touches, generates a vast amount of WAL, bloats the table, and floods your replicas with lag. Then someone cancels it and Postgres spends just as long rolling it back.
Chunk it, and run it outside your migration tool.
BATCH = 5_000
while True:
# Each batch is its own transaction — locks are held briefly and released
updated = db.execute("""
WITH batch AS (
SELECT id FROM users
WHERE email_address IS NULL AND email IS NOT NULL
ORDER BY id
LIMIT %s
FOR UPDATE SKIP LOCKED
)
UPDATE users u
SET email_address = u.email
FROM batch b
WHERE u.id = b.id
""", (BATCH,)).rowcount
if updated == 0:
break
# Let replicas catch up and autovacuum keep pace
wait_for_replica_lag(max_seconds=5)
time.sleep(0.1)SKIP LOCKED is what makes this safe to run alongside live traffic: rows currently locked by a real user’s transaction are skipped rather than waited on, and a later pass picks them up. The replica-lag check matters just as much — a backfill that outruns replication turns into stale reads for every user served from a follower, and the ripple effects there overlap with our logical replication patterns guide.
Indexes: CONCURRENTLY, and Its Catch
A plain CREATE INDEX blocks writes for its whole duration. CONCURRENTLY does not — but it has sharp edges people discover the hard way.
-- Cannot run inside a transaction block; many migration tools wrap
-- statements in BEGIN/COMMIT by default. Check yours.
CREATE INDEX CONCURRENTLY idx_users_email_address ON users (email_address);
-- If it fails it leaves an INVALID index behind. Always check:
SELECT indexrelid::regclass AS index_name
FROM pg_index WHERE NOT indisvalid;
-- Clean up before retrying
DROP INDEX CONCURRENTLY IF EXISTS idx_users_email_address;Two things to internalise. First, CONCURRENTLY cannot run inside a transaction, and most migration frameworks wrap everything in one — you must explicitly opt out, or it will fail. Second, a failed concurrent build leaves an invalid index that still costs write overhead while serving no reads. Nobody notices until a query plan quietly stops using it. Check pg_index after every concurrent build; the diagnostics in our database observability guide should include this.
Where This Is Overkill
Expand-contract is real work, and not every change earns it. A table with a thousand rows rewrites faster than the lock request takes to acquire, so just run the ALTER. An internal admin tool with four users and a maintenance window does not need three deploys to rename a column. The overhead is justified by table size and traffic, not by principle.
Be aware of the cost, too. A dual-write window means two columns disagree if anything goes wrong, and a contract phase that never happens leaves dead columns and dead code accumulating for years — the most common real failure of this pattern is not an outage, it is that step three is never done because the pain is gone and nobody prioritises cleanup. Schedule the contract deploy when you plan the expand one.
Managed Postgres and connection poolers add wrinkles worth checking against the ALTER TABLE documentation for your version, since lock levels have genuinely improved release over release. And if you sit behind a pooler in transaction mode, remember that SET lock_timeout without LOCAL may not land on the connection you think it did — the same footgun described in our Postgres connection pooling guide.
Zero-downtime schema migrations come down to a handful of habits. Set lock_timeout so a migration fails instead of forming a lock queue, because that queue — not the DDL — is what causes the outage. Use expand-contract so every intermediate state works with both old and new code, and accept three deploys for a rename. Prefer NOT VALID plus VALIDATE over blocking constraint checks, build indexes CONCURRENTLY and verify they came out valid, and chunk backfills with SKIP LOCKED while watching replica lag. Then actually run the contract phase — the cleanup nobody schedules is the step that quietly rots.