Zero-downtime Postgres migrations: expand, backfill, contract
Which Postgres schema changes lock a table, which are safe, and the three-release pattern I use to change a live column without an outage or a long window.
The statement that takes a site down is rarely the slow one. Take a reporting query that has been open on orders for a minute, holding a weak lock. A migration runs ALTER TABLE orders ADD COLUMN ..., which needs the strongest lock there is, so it waits for the report. Every ordinary SELECT and UPDATE that arrives after the ALTER now queues behind it, because the lock manager will not let them jump a waiting request that conflicts with them. The alter itself would have taken a millisecond. The queue behind it takes the application down for as long as the report runs.
This article is a reference for avoiding that, organised around one table: for each common schema operation, the lock it takes, what that lock blocks, and the alternative that is safe on a live table. Around the table are the settings that make a migration give up instead of queueing, the index and constraint recipes, and the three-release pattern (expand, backfill, contract) for changing the meaning or shape of existing data.
A lock that waits is worse than a lock that is held#
The explicit locking chapter of the manual defines the table-level lock modes. Two matter most here. ACCESS EXCLUSIVE conflicts with every other mode, including the ACCESS SHARE that a plain SELECT takes, so the holder is the only transaction touching the table. And the ALTER TABLE reference says an ACCESS EXCLUSIVE lock is acquired unless explicitly noted otherwise.
That gives two failure shapes. A held strong lock is a problem in proportion to its duration, so anything that rewrites or scans a big table under ACCESS EXCLUSIVE is expensive. A waiting strong lock is a problem in proportion to the longest transaction in front of it, however cheap the statement is. The defence against the second is not a faster statement. It is a statement that gives up quickly and tries again.
Lock cheat sheet#
Every row comes from the manual pages for ALTER TABLE, CREATE INDEX and explicit locking. Check the version you run, because rewrite rules have changed between releases.
| Operation | Lock and cost | What it blocks | Safe alternative |
|---|---|---|---|
ADD COLUMN, no default or a non-volatile default | ACCESS EXCLUSIVE, briefly; default stored in metadata, no rewrite | Everything, for a moment (and the queue behind a wait) | Fine as is, with lock_timeout |
ADD COLUMN with a volatile default such as clock_timestamp(), a stored generated column or an identity column | ACCESS EXCLUSIVE; rewrites the table and its indexes | Everything, for the length of the rewrite | Add nullable, backfill in batches |
CREATE INDEX | SHARE | Inserts, updates and deletes (reads continue) | CREATE INDEX CONCURRENTLY |
CREATE INDEX CONCURRENTLY | No lock that prevents writes; two scans; waits for older transactions | Other schema changes on the table, and other concurrent index builds | Run outside a transaction block; check for invalid leftovers |
ADD CONSTRAINT ... CHECK or NOT NULL | ACCESS EXCLUSIVE, with a full scan | Everything, for the length of the scan | Add NOT VALID, then validate |
ADD CONSTRAINT ... NOT VALID | ACCESS EXCLUSIVE for an instant; no scan | Everything, for an instant | Fine with lock_timeout |
VALIDATE CONSTRAINT | SHARE UPDATE EXCLUSIVE; scans the table | Other schema changes and vacuum, not reads or writes | Run it; it is the safe half |
ADD FOREIGN KEY | SHARE ROW EXCLUSIVE on both tables; scans unless NOT VALID | Writes on both tables (reads continue) | NOT VALID, then VALIDATE CONSTRAINT |
SET NOT NULL | ACCESS EXCLUSIVE; scans unless a valid CHECK proves no nulls | Everything, for the scan | Add and validate a CHECK (col IS NOT NULL) first |
DROP COLUMN | ACCESS EXCLUSIVE, briefly; column becomes invisible, no rewrite | Everything, for a moment | Only after no code reads it |
RENAME COLUMN | ACCESS EXCLUSIVE, briefly | Everything, and it breaks the old application version at once | New column, backfill, drop the old one |
ALTER COLUMN TYPE | ACCESS EXCLUSIVE; most changes rewrite the table | Everything, for the rewrite | New column and backfill |
The VALIDATE CONSTRAINT row is the reason the whole approach works: the expensive scan runs under a lock that does not block normal traffic, so the part that blocks is only the instant it takes to register the constraint. Unlike ADD CONSTRAINT, validation holds only SHARE UPDATE EXCLUSIVE (plus ROW SHARE on the referenced table for a foreign key).
Make every migration give up quickly#
The client connection settings define lock_timeout as aborting any statement that waits longer than the given time to acquire a lock, applied to each lock acquisition separately, with a default of zero meaning disabled. Setting it in postgresql.conf is not recommended because it affects every session, so set it in the migration session:
SET lock_timeout = '3s';
SET statement_timeout = '15min';Three seconds is a value to tune: short enough that a pile-up cannot hurt, longer than a healthy lock acquisition on your system. The manual notes that a lock_timeout equal to or longer than a non-zero statement_timeout is pointless, because the statement timeout would fire first. statement_timeout is the backstop for a rare statement that holds locks and then runs long.
A statement that fails on lock_timeout is the guard rail working, not a bug, so wrap the step in a retry. It must retry only that error. SQLSTATE 55P03 is lock_not_available; anything else, including a genuine SQL error, should stop the loop:
#!/usr/bin/env bash
# Usage: migrate-step.sh path/to/step.sql
# Retries a step only when it fails with SQLSTATE 55P03 (lock_not_available).
set -uo pipefail
file="$1"
for attempt in 1 2 3 4 5 6; do
# VERBOSITY=sqlstate makes psql print only the SQLSTATE for errors
if out=$(psql "$DATABASE_URL" -v ON_ERROR_STOP=1 -v VERBOSITY=sqlstate \
-f migrations/_guard.sql -f "$file" 2>&1); then
echo "applied $file on attempt $attempt"
exit 0
fi
if ! grep -q "55P03" <<<"$out"; then
echo "$out" >&2
echo "failed with a non-lock error, not retrying" >&2
exit 1
fi
echo "lock not available on attempt $attempt, retrying in $((attempt * 5))s" >&2
sleep $((attempt * 5))
done
echo "giving up on $file" >&2
exit 1If a step contains several statements in one transaction, a retry re-runs all of them, so keep each step to one idempotent change.
Index builds that do not block writes#
A plain CREATE INDEX blocks writes for the whole build. Build the index concurrently instead. The statement cannot run inside a transaction block, so it runs alone, and many migration runners wrap each file in a transaction and need to be told not to for this file:
CREATE INDEX CONCURRENTLY IF NOT EXISTS orders_state_idx ON orders (state);The cost is time. The manual says a concurrent build performs two scans of the table and must wait for existing transactions that could modify or use the index, so a forgotten open transaction can stall it indefinitely. Only one concurrent build runs per table at a time, and schema changes to the table are not allowed during the build.
If the build fails, for example on a deadlock or a uniqueness violation in a unique index, the command fails and leaves an invalid index behind. It is ignored by queries and still costs update overhead, and an invalid unique index continues to enforce uniqueness. The manual's recovery is to drop it and try again, or rebuild with REINDEX INDEX CONCURRENTLY. Find leftovers after any failure:
SELECT c.relname
FROM pg_index i
JOIN pg_class c ON c.oid = i.indexrelid
WHERE NOT i.indisvalid;This is also where IF NOT EXISTS bites. If a relation with the same name already exists, the manual says no error is raised and a notice is issued instead, and there is no guarantee that the existing index resembles the one you meant to create. An invalid index left by a failed build still has that name, so a retry of the statement above prints a notice, skips the build, and reports success while the index stays invalid and unused. Drop the leftover first:
DROP INDEX CONCURRENTLY IF EXISTS orders_state_idx;
CREATE INDEX CONCURRENTLY orders_state_idx ON orders (state);Both statements need to run outside a transaction block, so run them as separate steps if your runner executes a file as one transaction.
Constraints and NOT NULL without a long scan#
Adding a CHECK or NOT NULL constraint normally scans the whole table under ACCESS EXCLUSIVE. Split it so the scan happens under the weaker lock. A NOT VALID constraint is enforced for new writes at once, skips the scan, and only needs the strong lock for an instant:
ALTER TABLE orders
ADD CONSTRAINT orders_state_present CHECK (state IS NOT NULL) NOT VALID;ALTER TABLE orders VALIDATE CONSTRAINT orders_state_present;Validation scans, but under SHARE UPDATE EXCLUSIVE, so reads and writes continue. After that, the manual says a valid CHECK constraint that proves no nulls lets SET NOT NULL skip its table scan, provided the check is not dropped in the same command:
ALTER TABLE orders ALTER COLUMN state SET NOT NULL;
-- optional, in a later step: drop the now-redundant check
ALTER TABLE orders DROP CONSTRAINT orders_state_present;Foreign keys follow the same two-step shape: ADD FOREIGN KEY ... NOT VALID, then VALIDATE CONSTRAINT.
Changing a live column: expand, backfill, contract#
The lock rules cover single statements. Changing what a column means, such as moving orders.status to a stricter orders.state, also has to survive the period when old and new application versions run side by side. The pattern is three releases, each reversible, where every intermediate schema works for both versions.
Expand. Add what the new code needs without touching existing data or behaviour:
ALTER TABLE orders ADD COLUMN state text;The column is nullable with no default, so there is no rewrite. Add the index from the previous section if it is needed, then deploy code that writes both columns and still reads the old one. New rows now carry both values, old rows only status, and the previous application version still works.
Backfill. Fill state for existing rows. One UPDATE over a large table is a single huge transaction: it holds row locks, generates a lot of write-ahead log, and leaves a great deal of dead space for vacuum. Batch it so each batch commits on its own:
#!/usr/bin/env bash
set -euo pipefail
batch=5000
last_id=0
while true; do
# Updates the next batch of ids and prints the highest id it touched.
max_id=$(psql "$DATABASE_URL" -At -v ON_ERROR_STOP=1 -c "
WITH batch AS (
SELECT id FROM orders
WHERE id > ${last_id} AND state IS NULL
ORDER BY id
LIMIT ${batch}
), updated AS (
UPDATE orders o
SET state = CASE o.status WHEN 'paid' THEN 'completed' ELSE o.status END
FROM batch WHERE o.id = batch.id
RETURNING o.id
)
SELECT COALESCE(max(id), 0) FROM updated;")
[ "$max_id" = "0" ] && break
last_id="$max_id"
echo "backfilled up to id ${last_id}"
sleep 0.2 # let replicas and autovacuum keep up
doneThe batch size and the status mapping are examples; tune the first against replication lag and the second to your data. Each iteration is its own transaction because each psql -c is its own connection and statement, it advances by primary key so it never rescans from the start, and it pauses so the database can breathe. The state IS NULL filter makes the script safe to stop and resume. Watch replication lag and dead-tuple counts while it runs and reduce the batch if either grows. Large deletes use the same loop with DELETE in place of UPDATE.
When every row is filled, validate the constraint as in the previous section, set NOT NULL, and deploy code that reads the new column. The old column is still being written, so rolling that deploy back is safe.
Contract. Only when no running code reads status, deploy a version that stops writing it. Leave it for a release or two as a safety margin, then drop it:
ALTER TABLE orders DROP COLUMN status;The manual explains that DROP COLUMN does not physically remove the column. It makes it invisible to SQL, and later inserts and updates store a null for it. The statement is quick but returns no disk space; reclaiming space needs a table-rewriting form of ALTER TABLE, a separate and deliberate decision. Indexes and table constraints involving the column are dropped with it.
The same shape handles the other awkward cases. A rename is a new column plus backfill, never RENAME COLUMN, which breaks the old application version instantly. A type change is usually a new column and a backfill, since most type changes rewrite the table. A default with a not-null rule is a single ADD COLUMN ... NOT NULL DEFAULT 'x' when the default is non-volatile, and the three-step pattern is for values that must be computed per row.
Where migrations still go wrong#
Coupled deploys. If the code that needs the new column ships in the same release as the column, a rollback leaves code expecting a column the schema may no longer have. Keep schema changes one release ahead of the code that uses them.
Dropping too early. If something you forgot still reads the old column, the drop turns it into an error. A release or two of delay, plus a search of the codebase and the query logs, is cheap insurance.
Replicas. Schema changes replicate, and a long backfill increases replication lag, so read replicas serve stale data while it runs. Watch the lag, and note that a backfill writes a lot, which on a managed database can mean more storage and input and output charges.
Looking before running. Check pg_stat_activity for long-running transactions on the table and wait for them to finish before starting a migration that needs a strong lock. lock_timeout makes the migration safe to start; checking makes it likely to succeed on the first attempt.
Small tables. On a table of a few thousand rows, a plain migration is faster than the pattern and the lock lasts milliseconds, so use the simple route and keep the guard rails. A genuinely low-traffic window can justify a maintenance window for a rare rewrite, and a very large type change deserves a rehearsal on a copy of production data, with a shadow table and controlled swap or an established online schema change tool.
Related articles
All articles →A jobs table, one claiming query and a lease column give a small team a reliable work queue on the Postgres it already runs. What to build, and where it stops.
11 min
Auto-pause saves money until the first request after a quiet weekend. What a paused database looks like to your app, and a design that survives it.
12 min
Twelve small Cloud Run services, each with a sensible pool, can still exhaust one Postgres. How to do the connection math and pick a pooler.
14 min