Postgres: find long-running queries and who is blocking whom

When the DB is on fire, the question is never “what query is running right now” in isolation. It is “what query has been running for ten minutes, and what is it waiting on.” This query joins pg_stat_activity with pg_blocking_pids and formats the output so I can paste it into an incident doc.

I keep it saved as slow.sql in every repo that talks to Postgres, and run it with psql -f slow.sql.

SELECT
  pid,
  usename,
  application_name,
  state,
  now() - query_start AS duration,
  wait_event_type || ':' || wait_event AS waiting_on,
  pg_blocking_pids(pid) AS blocked_by,
  left(regexp_replace(query, '\s+', ' ', 'g'), 200) AS query
FROM pg_stat_activity
WHERE state <> 'idle'
  AND pid <> pg_backend_pid()
  AND now() - query_start > interval '10 seconds'
ORDER BY duration DESC;

If blocked_by is non-empty, that is your villain. SELECT pg_cancel_backend(<pid>) is the gentle stop; pg_terminate_backend is the hammer.