Learning center

Why a fast PostgreSQL migration blocked every query

An ALTER TABLE that needs one millisecond can stop all reads on a table for as long as some other transaction stays open. The migration waits for its lock, and every new query on the table waits behind the migration. Set a lock_timeout so the migration gives up quickly, and retry it.

Reproduce the queue with three sessions#

You need three psql sessions on one scratch database. We ran this on PostgreSQL 16.15.

CREATE TABLE orders (id integer PRIMARY KEY, status text NOT NULL);
INSERT INTO orders VALUES (1, 'packed');

Session 1 is a report that reads the table inside a transaction and keeps the transaction open. A slow report, an idle-in-transaction connection or a long COPY behaves the same way.

BEGIN;
SELECT count(*) FROM orders;
SELECT pg_sleep(6);
COMMIT;

Session 2 is the migration. Adding a nullable column without a default changes only the catalog.

\timing on
ALTER TABLE orders ADD COLUMN note text;

Session 3 is the application, reading one order.

\timing on
SELECT status FROM orders WHERE id = 1;

Start them a fraction of a second apart, in that order, and look at a fourth session while they wait:

SELECT application_name, state, wait_event_type,
       left(query, 45) AS query, pg_blocking_pids(pid) AS blocked_by
FROM pg_stat_activity
WHERE datname = current_database() AND pid <> pg_backend_pid();
application_namewait_event_typequeryblocked_by
reportTimeoutBEGIN; SELECT count(*) FROM orders; ...{}
migrationLockALTER TABLE orders ADD COLUMN note text;{report}
apiLockSELECT status FROM orders WHERE id = 1;{migration}

We named each session with SET application_name and show those names in place of process IDs. The migration reported Time: 5506.593 ms. The application's single-row read reported Time: 4994.398 ms.

Why the read waits for the migration#

The report holds an ACCESS SHARE lock until it commits. ALTER TABLE ... ADD COLUMN needs ACCESS EXCLUSIVE, which conflicts with every other lock mode, so it waits. The application's SELECT also needs only ACCESS SHARE, which the report's lock would allow. PostgreSQL still queues it behind the waiting ACCESS EXCLUSIVE request, because the lock conflict table applies to waiting requests too, and letting later readers jump ahead could keep the migration waiting forever. pg_blocking_pids shows the result: the read is blocked by the migration, not by the report.

During a deployment, each blocked read holds a connection from the application's pool. Once the pool is used up, requests that never touch orders wait for a connection as well.

Make the migration give up instead#

lock_timeout cancels a statement that has waited longer than the limit to acquire a lock. Remove the column the first run added, then repeat the experiment with the timeout set in session 2:

ALTER TABLE orders DROP COLUMN note;  -- reset, with no other session open

-- session 2, while session 1 holds its transaction open
SET lock_timeout = '500ms';
ALTER TABLE orders ADD COLUMN note text;
ERROR:  canceling statement due to lock timeout
Time: 500.538 ms

The application's read now returned after 290.822 ms, the remainder of the migration's half-second wait. The migration failed with SQLSTATE 55P03, and nothing changed. Retry it after a short pause, a limited number of times. Then stop and find the transaction that holds the lock.

SELECT pid, state, xact_start, left(query, 60)
FROM pg_stat_activity
WHERE xact_start < now() - interval '1 minute'
ORDER BY xact_start;

Pick the timeout from what the application tolerates. A lock wait adds to the latency of every query on the table, so a timeout of a few hundred milliseconds keeps a failed attempt short. lock_timeout limits only the wait. It does not limit a rewrite that has already acquired its lock, so a statement that copies the table also needs a rehearsal of its own duration.

What to rehearse#

Check how your migration tool runs statements before you rely on a timeout. Many tools wrap a migration file in one transaction. The lock_timeout must apply inside that transaction, for example with SET LOCAL lock_timeout as the file's first statement, and the retry must repeat the whole file. In the rehearsal, open a long transaction on the table from another session, as session 1 does here, and confirm that the migration fails within the timeout and that its retry succeeds after the transaction ends.

Find the other changes that need a rehearsal in which PostgreSQL migration needs a rehearsal.

One month of free code review.

New organizations get one calendar month of hosted code review for private and public GitHub repositories. No card or cloud connection required. Eligibility and fair-use terms.

Start free with GitHub

Related reading