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:

  1. Add a CHECK (...) NOT VALID constraint in milliseconds.
  2. Validate the constraint via SHARE UPDATE EXCLUSIVE using VALIDATE CONSTRAINT.
  3. Apply SET NOT NULL and 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