14:03, a deploy starts. Its migration adds a column to orders and took two seconds in staging. 14:05, the API is at 64% errors and p99 is 30 seconds. The migration is still "running". It is not doing anything: it is waiting, and so is everyone behind it.
$ sudo -u postgres psql -c "select state, wait_event_type, count(*) from pg_stat_activity where datname = 'orders' group by 1, 2 order by 3 desc"
state | wait_event_type | count
---------------------+-----------------+-------
active | Lock | 21
idle in transaction | Client | 1
(2 rows)21 sessions active but waiting on Lock, and one idle in transaction. That shape, many waiters and one quiet session, is a lock queue.
Who waits for whom
pg_blocking_pids() answers it directly. Look at the migration, the oldest transaction, and one app query:
$ sudo -u postgres psql -c "select pid, application_name, state, pg_blocking_pids(pid) as blocked_by, now() - xact_start as xact_age, left(query, 40) as query from pg_stat_activity where application_name in ('flyway', 'finance-export') or pid = (select min(pid) from pg_stat_activity where application_name = 'orders-api' and cardinality(pg_blocking_pids(pid)) > 0)"
pid | application_name | state | blocked_by | xact_age | query
-------+------------------+---------------------+------------+-------------+------------------------------------------
17401 | orders-api | active | {17426} | | SELECT id, status FROM orders WHERE id =
17422 | finance-export | idle in transaction | {} | 00:01:21.95 | SELECT count(*), sum(total_cents) FROM o
17426 | flyway | active | {17422} | | ALTER TABLE orders ADD COLUMN loyalty_po
(3 rows)Read it as a chain: the API's SELECT waits for the migration (17426), the migration waits for the finance export (17422), and the export waits for nobody. It ran a report in a transaction and never committed. It holds a lock, quietly, and has done so since before the deploy.
Why a SELECT waits for an ALTER that has not started
ALTER TABLE ... ADD COLUMN ... DEFAULT 0 is metadata-only since PostgreSQL 11: no table rewrite, milliseconds of work. But it needs an ACCESS EXCLUSIVE lock on the table, the one lock that conflicts with everything, plain reads included. The lock view shows the whole story:
$ sudo -u postgres psql -d orders -c "select l.pid, a.application_name, l.mode, l.granted from pg_locks l join pg_stat_activity a using (pid) where l.relation = 'orders'::regclass and (a.application_name in ('finance-export', 'flyway') or l.pid = (select min(pid) from pg_stat_activity where application_name = 'orders-api')) order by l.granted desc, l.pid"
pid | application_name | mode | granted
-------+------------------+---------------------+---------
17422 | finance-export | AccessShareLock | t
17401 | orders-api | AccessShareLock | f
17426 | flyway | AccessExclusiveLock | f
(3 rows)The export holds AccessShareLock, the lock every SELECT takes. The migration's AccessExclusiveLock conflicts with it, so it waits. And now the rule that turns a wait into an outage: locks are granted in queue order. The API's AccessShareLock would be compatible with the export's, but it conflicts with the exclusive request ahead of it in the queue, so it waits behind the migration. Every new request joins the queue. Pools run out of connections, requests time out, and from outside it looks like the database fell over. It is the same "waiting, not working" picture as load 15 with an idle CPU: nothing is busy, everything is stuck.
Get the site back first
Cancel the migration, not the export. One statement removes the head of the queue, and every waiter behind it gets its lock at once:
$ sudo -u postgres psql -c "select pg_cancel_backend(pid) from pg_stat_activity where application_name = 'flyway' and state = 'active'"
pg_cancel_backend
-------------------
t
(1 row)
$ sudo -u postgres psql -c "select state, wait_event_type, count(*) from pg_stat_activity where datname = 'orders' group by 1, 2"
state | wait_event_type | count
---------------------+-----------------+-------
idle | Client | 21
idle in transaction | Client | 1
(2 rows)pg_cancel_backend stops the current statement and leaves the session; pg_terminate_backend ends the session (and rolls back its transaction). The deploy tool sees its migration fail, which is what you want. Like force-unlocking Terraform state, find out who holds the lock before you kill anything, and kill the smallest thing that frees the queue.
Then apply it safely
Ask the export's owner, end it, and run the migration with a lock timeout, so that if it cannot get its lock quickly it gives up instead of holding the queue:
$ sudo -u postgres psql -c "select pg_terminate_backend(pid) from pg_stat_activity where application_name = 'finance-export'"
pg_terminate_backend
----------------------
t
(1 row)
$ sudo -u postgres psql -d orders -c "set lock_timeout = '3s'" -c "alter table orders add column loyalty_points integer not null default 0"
SET
ALTER TABLENothing set one before. Both timeouts are off by default:
$ sudo -u postgres psql -c "show lock_timeout" -c "show statement_timeout"
lock_timeout
--------------
0
(1 row)
statement_timeout
-------------------
0
(1 row)The rules for migrations on a live database
- Every migration sets
lock_timeout(2-5 s) and the runner retries a few times with a pause. A failed attempt costs seconds; a queued one costs the site. - Look for long transactions before deploying:
select pid, application_name, now() - xact_start from pg_stat_activity where xact_start < now() - interval '1 minute'. - Kill forgotten transactions automatically:
idle_in_transaction_session_timeouton application and reporting roles. - Split dangerous changes:
CREATE INDEX CONCURRENTLY, constraints addedNOT VALIDthenVALIDATE CONSTRAINT, backfills in batches, a column type change as add, copy, swap. - Watch the rollout like an incident. The symptom from the API side, connection pools exhausted, is the same one too many clients gives, and a deploy that also restarts pods adds its own drops.
Practise it
Incident: the 2-second migration that took the site down is this outage with the queue already full when you arrive. Find and kill the blocker trains the pg_blocking_pids chain on a smaller pile-up, and Run a migration without stopping the app has you ship schema changes with lock_timeout, CONCURRENTLY and NOT VALID while traffic keeps flowing.