A seemingly minor ALTER TABLE can trigger a severe production outage due to the ACCESS EXCLUSIVE lock queue and connection pool saturation. On a 512GB table with 1.1 billion rows running 2,400 QPS, waiting behind a single long-running SELECT can saturate connection pools and crash health checks in seconds.
How the Lock Queue Turns 2ms Into an Outage
PostgreSQL lock conflicts mean an ACCESS EXCLUSIVE request waits behind active SELECT statements, causing incoming queries to queue up behind the DDL.
You can identify the root blocker using pg_stat_activity to find sessions with non-empty blocked_by arrays. Drains can be managed using pg_cancel_backend(), while idle_in_transaction_session_timeout helps prevent leaked ORM sessions from blocking deployments.
Set lock_timeout to 50ms and Retry
To avoid standing in the lock queue, configure lock_timeout = '50ms' using SET LOCAL within transactions, execute one DDL statement per transaction, and use exponential backoff with jitter.
What Rewrites a 500GB Heap vs What Only Touches the Catalog
Certain operations (such as adding columns with volatile defaults like gen_random_uuid() or altering column types) trigger full table rewrites and require massive temporary disk space. Metadata-only changes or non-volatile defaults utilize fast catalog paths.
Adding NOT NULL and Foreign Keys Without Blocking Traffic
To avoid long ACCESS EXCLUSIVE locks during validation, use a multi-step pattern:
- Add a
CHECK (...) NOT VALIDconstraint in milliseconds. - Validate the constraint via
SHARE UPDATE EXCLUSIVEusingVALIDATE CONSTRAINT. - Apply
SET NOT NULLand drop the helper constraint in separate transactions.
Why Unbatched Backfills and Index Builds Break Replicas
Unbatched backfills across billions of rows cause severe heap bloat, delayed autovacuum, and massive WAL spikes that lag replicas. Use keyset pagination with an AS MATERIALIZED batch CTE and build indexes via CREATE INDEX CONCURRENTLY outside of transaction blocks.
Bypass PgBouncer on Port 6432
Running migrations through PgBouncer in transaction pooling mode breaks session-level timeouts and CREATE INDEX CONCURRENTLY. Direct your migration runner straight to PostgreSQL port 5432.
Get the Full Scripts, Batch Benchmarks, and 10-Step Runbook
For the complete Bash retry loop script, keyset backfill SQL queries, benchmark comparisons, and deployment runbooks, refer to the full guide on xSypher.
Read the full Zero-Downtime PostgreSQL Schema Migrations Guide on xSypher
Top comments (1)
Good breakdown of the lock queue, and the 50ms lock timeout is the right guard. One thing I would add is to check for an invalid index after a concurrent build fails, because Postgres can leave it behind and a retry with the same name will fail until it is dropped. I would also pause backfill batches when replica lag grows, rather than running them flat out, since keyset pagination alone does not stop WAL from piling up. Both checks are cheap and they stop a fix from turning into a second incident.