Детально изучить ожидания блокировок по объектам и режимам блокировки (pg_locks)

Команда

SELECT DISTINCT
       w.pid AS waiting_pid,
       w.locktype,
       coalesce(w.relation::regclass::text, 'xid ' || w.transactionid, w.virtualxid, w.objid::text) AS target,
       w.mode AS wanted_mode,
       b.pid AS blocker_pid,
       b.mode AS blocker_mode,
       b.granted AS blocker_holds,
       now() - wa.state_change AS waiting_for,
       left(wa.query, 60) AS waiting_query,
       left(ba.query, 60) AS blocker_query
FROM pg_locks w
JOIN pg_locks b
  ON b.pid = ANY (pg_blocking_pids(w.pid))
 AND b.locktype = w.locktype
 AND b.database IS NOT DISTINCT FROM w.database
 AND b.relation IS NOT DISTINCT FROM w.relation
 AND b.page IS NOT DISTINCT FROM w.page
 AND b.tuple IS NOT DISTINCT FROM w.tuple
 AND b.virtualxid IS NOT DISTINCT FROM w.virtualxid
 AND b.transactionid IS NOT DISTINCT FROM w.transactionid
 AND b.classid IS NOT DISTINCT FROM w.classid
 AND b.objid IS NOT DISTINCT FROM w.objid
 AND b.objsubid IS NOT DISTINCT FROM w.objsubid
JOIN pg_stat_activity wa ON wa.pid = w.pid
JOIN pg_stat_activity ba ON ba.pid = b.pid
WHERE NOT w.granted
ORDER BY waiting_for DESC, waiting_pid;

Что делает

Для каждого невыданного запроса блокировки (granted = false) показывает целевой объект (таблица, идентификатор транзакции и т. д.), запрошенный режим, блокирующий PID и его режим, действительно ли блокирующий процесс держит блокировку, и примерное время ожидания.

Когда использовать

Чтобы понять, конфликт какой таблицы и какого режима блокировки вызывает блокирование; например, DDL, ожидающий AccessExclusiveLock, или блокировка строки, отображаемая как ожидание transactionid.

Внимание

Поскольку сопоставление использует pg_blocking_pids(), выводятся только действительно конфликтующие запросы. blocker_holds = false означает, что блокирующий процесс ещё не держит блокировку, но стоит впереди в очереди с конфликтующим режимом (очередь блокировок). locktype = 'transactionid' — конфликт на уровне строки: ожидающий ждёт завершения транзакции, изменившей строку; сама строка в pg_locks не видна. На больших системах pg_locks может содержать тысячи строк; не запускайте это часто. waiting_for — приближённое значение на основе state_change.