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

Headline: A Postgres migration usually takes an app down not because the DDL is slow, but because ALTER TABLE waits for an ACCESS EXCLUSIVE lock and every query that arrives after it waits in the same lock queue. Setting a short lock_timeout, splitting every breaking change into expand/backfill/contract deploys, and running migrations as their own pipeline step removes nearly all of that risk.

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 caps how long a statement waits to acquire a lock; 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 ... NOT NULL DEFAULT <constant> 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.

The migration that taught me this added one nullable column. It was instant in staging. In production it hung, and the API stopped answering. Nothing about the column was slow — everything queued behind it was.

Why did 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 is already holding a conflicting lock on that table, the ALTER TABLE waits. Postgres does not let later, weaker requests jump the queue, so 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, join pg_stat_activity against pg_blocking_pids():

SELECT pid, state, wait_event_type, pg_blocking_pids(pid) AS blocked_by,
       now() - xact_start AS xact_age, left(query, 120) AS query
FROM pg_stat_activity
WHERE datname = current_database() AND state <> 'idle'
ORDER BY xact_start;

Any row whose blocked_by array is non-empty is waiting. The PID listed 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 are serving 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. The same rename as expand/contract:

1. Expand. Add display_name text as a nullable column. Deploy code that writes both columns and still reads full_name. The old version keeps working because the new column is optional.
2. Backfill. Copy existing rows in batches, then flip reads to display_name and deploy. Both columns are now populated.
3. Contract. Stop writing full_name, deploy, and only then run ALTER TABLE users DROP COLUMN full_name;. Dropping a column is instant but irreversible in practice, so it goes last and alone.

The rule I follow: 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. This is the cheat sheet I keep next to the migration folder (Postgres 12 and newer):

Statement	Cost	What I do instead
ADD COLUMN nullable, or constant DEFAULT	Metadata only (PG 11+)	Run it directly
ADD COLUMN ... DEFAULT gen_random_uuid()	Full table rewrite	Add nullable, backfill, then set the default
ALTER COLUMN TYPE varchar(50) -> text	Metadata only (binary coercible)	Run it directly
ALTER COLUMN TYPE int -> bigint	Full table rewrite	New column, backfill, expand/contract swap
ALTER COLUMN ... SET NOT NULL	Full scan under ACCESS EXCLUSIVE	Validated CHECK constraint first
ADD CONSTRAINT ... FOREIGN KEY	Scans and locks both tables	NOT VALID, then VALIDATE CONSTRAINT
CREATE INDEX	Blocks writes for the whole build	CREATE INDEX CONCURRENTLY, own step
DROP COLUMN	Metadata only, instant	Safe for the DB, breaks old code — contract only
RENAME COLUMN	Metadata only, instant	Never in a live deploy — use expand/contract

The NOT VALID trick is the one I reach for most. Adding a foreign key or check constraint normally scans the entire table while holding a strong lock. Splitting it in two moves the scan out from under that lock:

-- Step 1: instant. New writes are checked, existing rows are not scanned.
ALTER TABLE orders
  ADD CONSTRAINT orders_user_fk FOREIGN KEY (user_id) REFERENCES users(id) NOT VALID;

-- Step 2: scans existing rows under SHARE UPDATE EXCLUSIVE, blocking neither reads nor writes.
ALTER TABLE orders VALIDATE CONSTRAINT orders_user_fk;

The same shape makes a column NOT NULL without a blocking scan. Since Postgres 12, SET NOT NULL can use an already-validated CHECK constraint as proof and skip its own scan:

ALTER TABLE users
  ADD CONSTRAINT users_display_name_nn CHECK (display_name IS NOT NULL) NOT VALID;
ALTER TABLE users VALIDATE CONSTRAINT users_display_name_nn;
ALTER TABLE users ALTER COLUMN display_name SET NOT NULL;  -- no scan, uses the validated CHECK
ALTER TABLE users DROP CONSTRAINT users_display_name_nn;

How do I 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 my migration setup:

SET lock_timeout = '3s';
SET statement_timeout = '30s';

ALTER TABLE users ADD COLUMN display_name text;

With lock_timeout = '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 waits longer than three seconds. Failing is fine: the correct response is to retry, and the third or fourth attempt lands in a gap between long queries.

import { sql } from 'drizzle-orm';

async function withLockRetry(run: () => Promise<void>, attempts = 8) {
  for (let i = 0; i < attempts; i++) {
    try {
      await db.execute(sql`SET lock_timeout = '3s'`);
      return await run();
    } catch (err: any) {
      if (err.code !== '55P03' || i === attempts - 1) throw err;
      await new Promise((r) => setTimeout(r, 2 ** i * 500)); // back off, then try again
    }
  }
}

One caveat: 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 that wrapper. A failed run leaves an INVALID index behind, which DROP INDEX CONCURRENTLY must remove before you retry.

How do I 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.

-- One batch. Run repeatedly until it reports 0 rows updated.
WITH batch AS (
  SELECT id FROM users
  WHERE display_name IS NULL
  ORDER BY id
  LIMIT 5000
  FOR UPDATE SKIP LOCKED
)
UPDATE users u
SET display_name = u.full_name
FROM batch
WHERE u.id = batch.id;

FOR UPDATE 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. I drive the loop from a script rather than a migration file, so it can be paused, resumed, and monitored:

let moved = 0;
for (;;) {
  const res = await db.execute(BACKFILL_BATCH);   // the SQL above
  if (res.rowCount === 0) break;
  moved += res.rowCount;
  console.log(`backfilled ${moved}`);
  await new Promise((r) => setTimeout(r, 200));    // let replicas and autovacuum catch up
}

The right batch size is the one where a single batch stays well under your statement_timeout and replication lag stays flat. I start at 5,000 rows and adjust from the actual lag graph.

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 — never inside next build, never at application boot. Booting into a migration is the failure mode I see most in serverless projects: every cold-started instance migrates concurrently against the same database.

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

# .github/workflows/deploy.yml (abridged)
- name: Migrate
  run: pnpm drizzle-kit migrate
  env:
    DATABASE_URL: ${{ secrets.DIRECT_DATABASE_URL }}   # direct connection, not the pooler

- name: Deploy
  run: vercel deploy --prod --prebuilt

Two details there matter. 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 misbehave when every statement can land on a different backend. And migrations are forward-only: expand/contract already guarantees the previous version runs fine against the new schema, which beats a down script nobody has executed against production data.

FAQ

Q: Do I need expand/contract just to add a column?
A: No. A nullable column, or one with a constant default, is backward compatible: the old version ignores it. Expand/contract is only needed when something existing is renamed, dropped, retyped, or made mandatory.

Q: Can I run CREATE INDEX CONCURRENTLY 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.

Q: What lock_timeout value should I use?
A: A few seconds. Three seconds wins an ordinary lock race, and no queued request notices it. Pair it with retries and exponential backoff, because a lock timeout is an expected outcome, not an error.

Q: How do I 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 needs.

None of this is exotic Postgres knowledge. It is four habits: a short lock_timeout, additive-then-subtractive deploys, batched backfills, and migrations as their own pipeline step. I added them after one instant ALTER TABLE cost me an outage, and I have not had a migration-caused incident since.

---

Originally published on devya.dev. Also on eng-ahmed.com. Built by Devya Solutions.