Diagnosing lock waits and deadlocks in PostgreSQL with pg_locks, pg_blocking_pids and lock_timeout

Goal

Turn "the database is hanging" into a named blocking session, the statement holding the lock, and a decision about what to do, within minutes.

Prerequisites

A role with pg_read_all_stats or superuser to see other sessions' statements; log_lock_waits = on, so that waits longer than deadlock_timeout (default one second) are written to the server log with both parties named.

Steps

1. List the waiters: SELECT pid, wait_event_type, wait_event, state, xact_start, query FROM pg_stat_activity WHERE wait_event_type = 'Lock'.
2. Find their blockers: SELECT pid, pg_blocking_pids(pid) FROM pg_stat_activity WHERE cardinality(pg_blocking_pids(pid)) > 0. The documentation recommends this function over joining pg_locks to itself, because such a query would have to encode which lock modes conflict and the view does not expose the order of the wait queue.
3. Look the blockers up in pg_stat_activity: state (idle in transaction means the client is holding the transaction open while doing something else), xact_start, and query, which is the last statement, not necessarily the one that took the lock.
4. If needed, see which lock is contested: SELECT locktype, relation::regclass, mode, granted, waitstart FROM pg_locks WHERE pid IN (...). ACCESS EXCLUSIVE, taken by most ALTER TABLE forms, conflicts with every other mode including plain SELECT, so a queued DDL statement blocks every later reader of the table.
5. Decide: pg_cancel_backend(pid) stops the blocker's current statement; pg_terminate_backend(pid) ends its session and rolls the transaction back. Both surface as errors in the application, so record who was cut off and why.
6. For deadlocks, read the server log: the error names both processes and their statements. The documentation states that PostgreSQL detects deadlocks automatically and aborts one of the transactions, that which one is not predictable, and that the best defence is acquiring locks on multiple objects in a consistent order. Retry the aborted transaction in the application.
7. Prevent the next one: run migrations and maintenance with SET lock_timeout = '...' and a retry loop, set idle_in_transaction_session_timeout for application roles, and order multi-row updates by primary key in batch jobs.

Expected result

Every hang is attributed to a holder and a statement; DDL no longer queues behind long transactions indefinitely; deadlock errors are retried instead of reaching users.

Limits and test basis

pg_blocking_pids reflects the moment of the call, and chains change quickly. A wait for a row lock appears in pg_locks as a wait on a transactionid, which is confusing without the blocker's statement. The procedure follows the cited documentation; no timings are claimed.