Find which PostgreSQL session is blocking a query or migration

-- ======================================================================
-- Q: A query or a migration is stuck. How do you find who is blocking whom?
--
-- The classic scenario: a connection that is idle in transaction (someone ran
-- BEGIN and forgot, or code that calls an external API inside atomic) holds a
-- lock, an ALTER TABLE waits for ACCESS EXCLUSIVE, and all the SELECTs queue up
-- behind it. Prevention: lock_timeout on migrations (SET lock_timeout = '5s'),
-- idle_in_transaction_session_timeout and statement_timeout on the application's
-- connection.
-- ======================================================================

-- what is running now and for how long
SELECT pid, state, wait_event_type, wait_event,
       now() - xact_start AS xact_age, left(query, 80) AS query
FROM pg_stat_activity
WHERE datname = current_database() AND state <> 'idle'
ORDER BY xact_start;

-- who blocks whom
SELECT blocked.pid  AS blocked_pid,  left(blocked.query, 60)  AS blocked_query,
       blocking.pid AS blocking_pid, left(blocking.query, 60) AS blocking_query,
       blocking.state
FROM pg_stat_activity blocked
JOIN LATERAL unnest(pg_blocking_pids(blocked.pid)) AS b(pid) ON true
JOIN pg_stat_activity blocking ON blocking.pid = b.pid;

-- lock details on a specific table
SELECT l.pid, l.mode, l.granted
FROM pg_locks l JOIN pg_class c ON c.oid = l.relation
WHERE c.relname = 'orders';

SELECT pg_cancel_backend(4242);      -- cancels the query
SELECT pg_terminate_backend(4242);   -- closes the connection