Locks and blocked queries

Symptom: queries hang, a migration never finishes, or the app times out while CPU is idle.

Confirm

select pid, pg_blocking_pids(pid) as blocked_by, wait_event_type, wait_event,
       now() - query_start as waiting, left(query, 80) as query
from pg_stat_activity where cardinality(pg_blocking_pids(pid)) > 0;

Follow blocked_by to the root. The root is very often a session that is idle in transaction:

select pid, usename, now() - xact_start as open_for, state, left(query, 80)
from pg_stat_activity where state like 'idle in transaction%' order by xact_start;

Fix now

select pg_cancel_backend(<pid>);     -- cancels the running query, keeps the session
select pg_terminate_backend(<pid>);  -- ends the session and rolls back its transaction

Why one slow query stalls everything

An ALTER TABLE that waits behind a long-running read makes every later query on that table wait behind the ALTER. Always give schema changes a short leash:

set lock_timeout = '5s';

Safer migrations

• create index concurrently, never a plain create index, on a live table.
• Add constraints as not valid, then validate constraint in a second step (it takes a light lock).
• Adding a column with a constant default is instant on PostgreSQL 11+.

Deadlocks

Postgres aborts one side after deadlock_timeout (1 s). Fix the cause: touch rows and tables in the same order everywhere, and keep transactions short. Set log_lock_waits = on to see who waited on whom. For job queues use select ... for update skip locked.

Prevent

alter role app_user set idle_in_transaction_session_timeout = '60s';