Show active and blocking queries

Command

SELECT pid, usename, state, wait_event_type, wait_event, pg_blocking_pids(pid) AS blocking_pids, now() - query_start AS duration, left(query, 100) AS query FROM pg_stat_activity WHERE state <> 'idle' AND pid <> pg_backend_pid() ORDER BY duration DESC;

What it does

Lists all non-idle backends, which PIDs are blocking them and how long they have been running.

When to use it

To diagnose locking/blocking problems and long-running queries.

Caution

pg_blocking_pids is available in PostgreSQL 9.6+. If the blocking_pids array is non-empty, those PIDs hold the lock; cancel/terminate that PID if needed.