Sev1: one ALTER TABLE froze the whole PostgreSQL app — lock queue pile-ups behind a long transaction

#Symptoms

• A deploy started a migration; within seconds, all queries touching one table hang and connection pools fill up.
• pg_stat_activity shows many sessions with wait_event_type = 'Lock'.

#1. Why a quick DDL causes an outage

ALTER TABLE needs an ACCESS EXCLUSIVE lock. If any transaction holds even a light lock on the table (for example a long SELECT or an idle transaction that read it), the ALTER waits. Every new query on the table then queues behind the waiting ALTER, because lock requests are granted in order. One long transaction + one DDL = the table is frozen.

#2. Find the chain

SELECT pid, pg_blocking_pids(pid) AS blocked_by, state, wait_event_type,
       now() - xact_start AS xact_age, LEFT(query, 80) AS query
FROM pg_stat_activity
WHERE datname = current_database()
  AND (wait_event_type = 'Lock' OR pid IN (SELECT unnest(pg_blocking_pids(pid)) FROM pg_stat_activity))
ORDER BY xact_start;

Typically you see:

#3. Unfreeze in seconds

Option A, cancel the DDL (safest; the migration can be retried later):

SELECT pg_cancel_backend(<alter_pid>);

The queued app queries proceed immediately.

Option B, if the DDL is urgent, terminate the old blocking session instead:

SELECT pg_terminate_backend(<blocker_pid>);

Warning

Terminating a session rolls back its open transaction. Check what it is first (query, application_name, client_addr). Killing a batch job halfway may need cleanup on the app side.

If the migration tool keeps retrying without a timeout, stop the deploy pipeline first, or it will freeze the table again.

#4. Run migrations safely

Set a lock timeout so DDL gives up quickly instead of blocking everyone:

SET lock_timeout = '3s';
SET statement_timeout = '15min';
ALTER TABLE orders ADD COLUMN channel text;

Retry in a loop with backoff in the migration tool. Most migration frameworks let you set these per migration.

Prefer non-blocking forms:

CREATE INDEX CONCURRENTLY idx_orders_channel ON orders (channel);
ALTER TABLE orders ADD CONSTRAINT fk_cust FOREIGN KEY (customer_id) REFERENCES customers(id) NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT fk_cust;   -- weaker lock

Adding a column with a constant default is metadata-only since PostgreSQL 11. Changing a column's type usually rewrites the table and holds ACCESS EXCLUSIVE the whole time; plan those as separate operations.

#5. Kill idle transactions automatically

ALTER SYSTEM SET idle_in_transaction_session_timeout = '5min';
SELECT pg_reload_conf();

Apps that leave transactions open (missing commit, connection checked back into the pool mid-transaction) are the most common blocker.

#6. Verify

SELECT count(*) FROM pg_stat_activity WHERE wait_event_type = 'Lock';

Should return to near zero. Check the app's error rate and pool usage recover.

#Prevent it next time

• lock_timeout on every migration session; never run DDL without it in production.
• idle_in_transaction_session_timeout set server-wide or per app role.
• Alert on transactions older than N minutes and on lock waits.
• Review migrations for table-rewriting operations before deploy.