Knowing who is blocking a query

Once you have found out that a query is blocked, you need to know who or what is blocking them.

Getting ready

If you are logged in as superuser you will have full access to
monitoring information

How to do it...

If you are on PostgreSQL 9.6 or newer, just write the following query:

SELECT datname
, usename
, wait_event_type
, wait_event
, pg_blocking_pids(pid) AS blocked_by
, query
FROM pg_stat_activity
WHERE wait_event IS NOT NULL;

You will get an output like the following:

-[ RECORD 1 ]---+-----------------
datname         | postgres
usename  ...