Zero-Downtime Postgres Migrations: Expand/Contract, lock_timeout, and the ALTER TABLE That Queues Behind One Slow Query

Key takeaways

Postgres lock requests queue in arrival order. A pending ALTER TABLE blocked by one long-running SELECT blocks every read and write that arrives behind it, which is why an instant DDL statement can still cause minutes of downtime.

lock_timeout is a Postgres setting that caps how long a statement waits to acquire a lock, while statement_timeout caps how long it runs after acquiring one. Setting lock_timeout to a few seconds and retrying makes a migration fail fast instead of freezing traffic.

Expand/contract is a three-deploy pattern: add the new schema alongside the old one, backfill and dual-write, then remove the old schema. It exists because a rolling deploy runs the old and new application versions against the same database at the same time.

Since Postgres 11, ADD COLUMN with a constant NOT NULL DEFAULT stores the default in the catalog and does not rewrite the table. A volatile default such as gen_random_uuid() still rewrites every row.

CREATE INDEX CONCURRENTLY does not block writes but cannot run inside a transaction block, so it needs its own migration step, and a failed run leaves an INVALID index that must be dropped before retrying.

Why does a migration that runs instantly still take the site down?

Because Postgres queues lock requests in arrival order, and a blocked ALTER TABLE blocks everyone behind it. ALTER TABLE needs an ACCESS EXCLUSIVE lock, the strongest lock level in Postgres, which conflicts with every other lock including the ACCESS SHARE lock that a plain SELECT takes. If any transaction already holds a conflicting lock on that table, the ALTER TABLE waits.

Postgres does not let later, weaker lock requests jump the queue. The next SELECT waits behind the ALTER TABLE, and so does every request after it. That turns a one-millisecond catalog update into an outage whose length equals the runtime of whatever was already holding the table.

The usual culprits are the same three every time: a long analytics SELECT, an idle-in-transaction session that a web request opened and never committed, and autovacuum running in anti-wraparound mode, which does not yield the way ordinary autovacuum does.

To see it live, query pg_stat_activity together with pg_blocking_pids(). Any row whose blocking-PID array is non-empty is waiting, and the PID inside that array is the transaction you actually need to deal with.

What is the expand/contract migration pattern?

Expand/contract is a three-deploy sequence that keeps the database compatible with both the old and the new version of the application at every moment. It is required for any rolling or serverless deploy, because during the rollout both versions serve requests against one database.

Renaming users.full_name to users.display_name with ALTER TABLE ... RENAME COLUMN is instant metadata-only work in Postgres. It is also an outage: every old instance still running SELECT full_name starts erroring the moment it commits.

Expand: add display_name as a nullable column, then deploy application code that writes both columns and still reads the old one. The old version keeps working because the new column is optional.

Backfill: copy existing rows in batches, then flip reads to display_name and deploy. Both columns are now populated and correct.

Contract: stop writing full_name, deploy, and only then run ALTER TABLE users DROP COLUMN full_name. Dropping a column is metadata-only and instant, but irreversible in practice, so it goes last and alone.

The rule our team follows: a deploy may add optional things or remove unused things, never both, and never anything a currently-running instance depends on.

Which Postgres DDL operations are safe, and which rewrite the whole table?

A table rewrite copies every row into new files while holding ACCESS EXCLUSIVE, which on a large table is an outage of unbounded length. The behaviour below is Postgres 12 and newer.

Safe, metadata only: ADD COLUMN nullable or with a constant DEFAULT (Postgres 11 and newer); ALTER COLUMN TYPE from varchar(50) to text, which is binary coercible; DROP COLUMN; RENAME COLUMN.

Full table rewrite: ADD COLUMN with a volatile default such as gen_random_uuid(); ALTER COLUMN TYPE from int to bigint. Both should be replaced by a new column plus a batched backfill and an expand/contract swap.

Blocking scans: ALTER COLUMN SET NOT NULL scans the whole table under ACCESS EXCLUSIVE, ADD CONSTRAINT FOREIGN KEY scans and locks both tables, and CREATE INDEX blocks writes for the entire build.

Safe for the database but unsafe for running code: DROP COLUMN and RENAME COLUMN are instant, but they break any old application instance still referencing the column. Both belong in the contract phase only.

The NOT VALID trick is the one we reach for most. Adding a foreign key with NOT VALID is instant and checks only new writes; a follow-up VALIDATE CONSTRAINT scans existing rows under SHARE UPDATE EXCLUSIVE, which blocks neither reads nor writes.

The same shape makes a column NOT NULL without a blocking scan. Add a CHECK (col IS NOT NULL) constraint as NOT VALID, run VALIDATE CONSTRAINT, then run SET NOT NULL. Since Postgres 12, SET NOT NULL uses the validated CHECK as proof and skips its own scan.

How do we stop a migration from blocking traffic?

Set lock_timeout before the DDL so the migration gives up instead of parking itself at the head of the lock queue. This is the single highest-value line in our migration setup.

With lock_timeout set to 3s, a migration that cannot get its lock within three seconds fails with 'canceling statement due to lock timeout' (SQLSTATE 55P03) and releases the queue. Nothing behind it ever waits more than three seconds.

Failing is fine, because the correct response is to retry. Lock contention is transient, and the third or fourth attempt usually lands in a gap between long queries. We wrap migrations in a retry helper that catches SQLSTATE 55P03 and backs off exponentially.

One caveat costs people an afternoon: CREATE INDEX CONCURRENTLY cannot run inside a transaction block. Most migration runners wrap each file in a single transaction, so a concurrent index needs a step that skips the wrapper. A failed run leaves an INVALID index, which must be dropped with DROP INDEX CONCURRENTLY before retrying.

How do we backfill millions of rows without holding one long transaction?

Backfill in bounded batches, each in its own transaction, with a pause between them. A single UPDATE across a large table holds row locks for its entire runtime, bloats the table with dead tuples faster than autovacuum reclaims them, and grows the WAL enough to make a replica fall behind.

Each batch selects a bounded set of primary keys with ORDER BY id LIMIT 5000 FOR UPDATE SKIP LOCKED, then updates only those rows. SKIP LOCKED means the backfill steps over rows that live traffic is currently editing instead of waiting for them; the next pass picks them up.

We drive the loop from a script rather than a migration file, so it can be paused, resumed, and monitored. The loop stops when a batch reports zero rows updated, and sleeps briefly between batches to let replicas and autovacuum catch up.

The batch size worth tuning is the one where a single batch stays well under statement_timeout and replication lag stays flat. We start at 5,000 rows and adjust from the actual lag graph rather than from a number posted on the internet.

Where should migrations run in a CI/CD pipeline?

Migrations belong in a dedicated pipeline step that runs after the build and before the new version receives traffic. They should never run inside next build, and never at application boot.

Running migrations at application boot is the failure mode we see most in serverless projects: every cold-started instance tries to migrate concurrently against the same database.

Drizzle Kit and Prisma Migrate both take a Postgres advisory lock so concurrent runners serialize instead of colliding. That prevents corruption but not the deeper problem, which is that the deploy has no clean place to stop when a migration fails. A separate step does.

Migrations must use a direct database connection rather than a transaction-mode pooler, because session-level settings such as SET lock_timeout and advisory locks do not behave correctly when every statement can land on a different backend.

Migrations are forward-only. Expand/contract already guarantees the previous application version runs fine against the new schema, which is a far more reliable rollback story than a down script nobody has executed against production data.

FAQ

Q: Do we need expand/contract just to add a column?

A: No. A nullable column, or one with a constant default, is backward compatible and the old application version simply ignores it. Expand/contract is only needed when something existing is renamed, dropped, retyped, or made mandatory.

Q: Can CREATE INDEX CONCURRENTLY run from Drizzle or Prisma migrations?

A: Yes, but only in a migration step that is not wrapped in a transaction, because Postgres rejects CONCURRENTLY inside a transaction block. Keep it in its own file with no other statements.

Q: What lock_timeout value should we use?

A: A few seconds. Three seconds is long enough to win an ordinary lock race and short enough that a queued request never notices. Pair it with retries and exponential backoff, because a lock timeout is an expected outcome rather than an error.

Q: How do we find out what blocked a migration after the fact?

A: Query pg_stat_activity with pg_blocking_pids() while it is happening, and enable log_lock_waits so Postgres writes a log line whenever a session waits longer than deadlock_timeout for a lock.

Q: Are down migrations worth writing?

A: Rarely. Once a migration has run against production data, reverting it usually destroys data the forward version created. Expand/contract keeps every intermediate schema compatible with the adjacent application versions, which is what a rollback actually needs.