Bound Lock Waits in Schema Migrations

Set lock_timeout and statement_timeout inside migrations so DDL fails fast instead of queueing behind a long query and freezing reads.

~30 minpostgresqlmigrationslocking

Task

Prevent a schema migration from blocking every query on a table when it waits for an AccessExclusiveLock behind a long transaction.

When to use this

A migration adds a column to orders during business hours. An analytics transaction holds a read lock for two minutes; the ALTER waits, and every subsequent SELECT queues behind the pending ALTER, taking the orders page down.

Prerequisites

PostgreSQL 12 or newer
A migration tool that runs raw SQL, such as Flyway, Alembic, or psql
A deployment job with retry capability

Steps

1. Put the timeout at the top of the migration so it applies before the first DDL statement.

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

ALTER TABLE orders ADD COLUMN fulfillment_state text;

Expected output: The ALTER waits at most three seconds for its lock, then fails instead of blocking readers indefinitely.

2. Retry the migration on lock timeout with backoff instead of running it once and giving up.

for attempt in 1 2 3 4 5; do
  psql "$DATABASE_URL" -v ON_ERROR_STOP=1 \
    -f migrations/0042_add_fulfillment_state.sql && exit 0
  echo "Attempt $attempt hit a lock; retrying in $((attempt * 10))s"
  sleep $((attempt * 10))
done
exit 1

Expected output: The migration succeeds on a later attempt once the blocking transaction ends, without a manual rerun.

3. Reproduce the blocking scenario deliberately to prove the timeout works.

psql -U app_rw -d appdb -c 'BEGIN; SELECT * FROM orders LIMIT 1; SELECT pg_sleep(120);' & psql -U postgres -d appdb -f migrations/0042_add_fulfillment_state.sql

Expected output: The migration fails after about three seconds with canceling statement due to lock timeout, while the blocking session continues.

4. Keep DDL short: add a nullable column now and backfill in batches, then validate constraints separately.

ALTER TABLE orders ADD CONSTRAINT orders_state_check
  CHECK (fulfillment_state IN ('new', 'packed', 'shipped')) NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT orders_state_check;

Expected output: The check constraint is enforced for new rows immediately and validated later with a weaker lock.

Troubleshooting

Problem

The migration tool opens a new session and the SET statement is lost

Solution

Configure lock_timeout in the connection options or prepend it to every migration file; poolers may also reset session state.

Problem

Every attempt times out at the same hour

Solution

Find the blocker with SELECT pg_blocking_pids(pid) and address the long transaction or move the migration to a quieter window.

Limitations

lock_timeout does not make SET NOT NULL or a full table rewrite cheap; those still scan the table.
Retries require idempotent migration SQL; ALTER TABLE ADD COLUMN IF NOT EXISTS helps, but indexes and constraints need their own checks.

The lock queue problem

PostgreSQL DDL needs an AccessExclusiveLock. If a long read holds a conflicting lock, the ALTER waits in the lock queue. New SELECTs then queue behind the waiting ALTER because PostgreSQL grants locks roughly in order. One slow query can therefore block the entire table. A short lock_timeout makes the migration give up before it becomes a traffic outage.

Set the timeout first

SET lock_timeout affects the session and must run before the first statement that needs a lock. If the migration tool opens a separate connection per file, put the setting in the connection string options or at the top of every file. A three-second timeout is usually enough to detect contention; the migration either proceeds immediately or retries later.

SET lock_timeout = '3s';
SET statement_timeout = '60s';
ALTER TABLE orders ADD COLUMN fulfillment_state text;

Design migrations for short locks

Adding a nullable column without a default is metadata-only in modern PostgreSQL. Add constraints as NOT VALID, then VALIDATE CONSTRAINT with a weaker lock. Create indexes with CONCURRENTLY outside a transaction. Backfill in batches with pauses so replication and autovacuum keep up. The goal is to hold strong locks for milliseconds, not minutes.

Retry and observe

Wrap the migration in a retry loop with backoff. Log the attempt number and the failing SQLSTATE (55P03 for lock timeout). If all attempts fail, inspect pg_stat_activity and pg_blocking_pids to find the blocker. Alert on migrations that exceed their expected duration, and never run a long DDL by hand during peak traffic.