Production is blocked. Here's how to find it, kill it, and stop it coming back.

SELECT
  blocked.pid                              AS blocked_pid,
  blocked.usename                          AS blocked_user,
  NOW() - blocked.query_start             AS blocked_duration,
  blocked.query                            AS blocked_query,
  blocking.pid                             AS blocking_pid,
  blocking.usename                         AS blocking_user,
  NOW() - blocking.query_start            AS blocking_duration,
  blocking.query                           AS blocking_query,
  blocking.state                           AS blocking_state,
  blocking.wait_event_type,
  blocking.wait_event
FROM  pg_stat_activity blocked
JOIN  pg_stat_activity blocking
  ON blocking.pid = ANY(pg_blocking_pids(blocked.pid))
WHERE CARDINALITY(pg_blocking_pids(blocked.pid)) > 0
ORDER BY blocked_duration DESC;

WITH RECURSIVE lock_chain AS (
  SELECT pid, pg_blocking_pids(pid) AS blockers, 0 AS depth
  FROM pg_stat_activity
  WHERE CARDINALITY(pg_blocking_pids(pid)) > 0
  UNION ALL
  SELECT unnested, pg_blocking_pids(unnested), lc.depth + 1
  FROM lock_chain lc, UNNEST(lc.blockers) unnested
  WHERE CARDINALITY(pg_blocking_pids(unnested)) > 0
)
SELECT DISTINCT ON (pid) pid, depth FROM lock_chain
ORDER BY pid, depth DESC;
-- Highest depth value = root blocker

-- Step 1: Verify what you're about to terminate
SELECT pid, usename, application_name, backend_type, state, query
FROM   pg_stat_activity
WHERE  pid = 12441;  -- replace with your blocking PID

-- Step 2: Try cancel first (safer — doesn't kill the connection)
SELECT pg_cancel_backend(12441);

-- Wait 5–10 seconds. If still blocking, escalate:
SELECT pg_terminate_backend(12441);

-- Confirm the chain cleared
SELECT COUNT(*) FROM pg_stat_activity
WHERE  CARDINALITY(pg_blocking_pids(pid)) > 0;
-- Should return 0

-- postgresql.conf — no overhead unless deadlocks actually occur
log_lock_waits  = on
deadlock_timeout = 1s

SELECT pg_reload_conf();  -- no restart needed

-- Find tables currently involved in lock waits
SELECT
  COALESCE(relname, '(transaction)') AS object,
  locktype, mode, COUNT(*) AS waiters
FROM      pg_locks  l
LEFT JOIN pg_class  c ON c.oid = l.relation
WHERE  granted = FALSE
GROUP BY 1,2,3
ORDER BY waiters DESC;

-- Kill idle-in-transaction sessions after 30 seconds
ALTER ROLE app_user
  SET idle_in_transaction_session_timeout = '30s';

-- Backstop: kill any query running over 5 minutes
ALTER ROLE app_user
  SET statement_timeout = '300s';

-- Apply immediately — no restart required
SELECT pg_reload_conf();
SHOW idle_in_transaction_session_timeout;