PostgreSQL Lock Contention Diagnosis

Useful Postgres commands for diagnosing queries that are slow due to lock contention or deadlocks.

Find long-running queries:

select pid, now() - query_start, left(query, 100) from pg_stat_activity  where state != 'idle' order by query_start asc;

(to stop a query, use select pg_terminate_backend(pid) where ...)

Find locks that are blocking queries:

select relation::regclass, * from pg_locks where not granted;   

Find queries that are responsible for held locks:

SELECT
  COALESCE(blockingl.relation::regclass::text,blockingl.locktype) as locked_item,
  now() - blockeda.query_start AS waiting_duration, blockeda.pid AS blocked_pid,
  blockedl.mode as blocked_mode,
  blockinga.pid AS blocking_pid, left(blockinga.query,100) as blocking_query,
  blockingl.mode as blocking_mode
FROM pg_catalog.pg_locks blockedl
JOIN pg_stat_activity blockeda ON blockedl.pid = blockeda.pid
JOIN pg_catalog.pg_locks blockingl ON(
  ( (blockingl.transactionid=blockedl.transactionid) OR
  (blockingl.relation=blockedl.relation AND blockingl.locktype=blockedl.locktype)
  ) AND blockedl.pid != blockingl.pid)
JOIN pg_stat_activity blockinga ON blockingl.pid = blockinga.pid
  AND blockinga.datid = blockeda.datid
WHERE NOT blockedl.granted
AND blockinga.datname = current_database();