One of the most common queries that I keep in my pocket is

SELECT pid,
datname,
usename,
now() - query_start AS runtime,
waiting,
state,
query
FROM pg_stat_activity
WHERE waiting='true'; 

This gives you a quick look at any queries that are ‘waiting’.  Specifically, you will see queries that are explicitly blocked on some other database activity.  I’ve got a few db’s that I’ve moved to 9.6 for dev and test.  I got a call that ‘something seems gummed’ up and my muscle memory kicked in with my tried and true query above…

postgres@postgres=# SELECT pid, postgres-# datname, postgres-# usename, 
postgres-# now() - query_start AS runtime, postgres-# waiting, postgres-# state, 
postgres-# query postgres-# FROM pg_stat_activity postgres-# WHERE waiting='true';

ERROR: column "waiting" does not exist LINE 5: waiting, ^

For a second, I was actually considering catalog corruption and other nefarious things… then I remembered that this was changing in 9.6!  The latest and greatest release of PostgreSQL has added deeper introspection into wait events!  Pre 9.6 gave you a boolean to see if the query was blocked, then, it was up to you to dig through all of pg_locks to see what was going on.  Now, waiting has been split into two new columns:

wait_event

wait_event_type

There’s a large list of values that can be filled in, but, essentially:

If the ‘wait_event’ column

is NULL AND state = ‘active          – Running query, not blocked

is NOT NULL and state = ‘active’   – Query is executing, but, blocked 

So, if we modify my favorite query to find useful info about ‘blocked’ queries, it becomes:

SELECT pid, datname, usename, now() - query_start AS runtime, wait_event, 
wait_event_type, state, query FROM pg_stat_activity WHERE wait_event 
is NOT NULL AND state = 'active';

postgres@postgres=# SELECT pid, postgres-# datname, postgres-# usename, 
postgres-# now() - query_start AS runtime, postgres-# wait_event, 
postgres-# wait_event_type, postgres-# state, postgres-# query postgres-# FROM 
pg_stat_activity postgres-# WHERE wait_event is NOT NULL postgres-# AND state 
= 'active';

pid | datname | usename | runtime | wait_event | wait_event_type | state | query 
-------+----------+----------+-----------------+------------+-----------------+----
15975 | postgres | postgres | 00:01:17.781512 | relation | Lock | active | select * 
from test; (1 row)

postgres@postgres=# 

So, I’ve intentionally created a situation where the ‘test’ table would be blocked.  Now, I can very clearly see that my query, ‘select * from test’ is waiting for a Lock  on a relation. I still have to go to pg_locks to see exactly the other session that is blocking me, but, there’s a lot less work in involved because I already know it’s a relation lock.  

There’s quite a list of possible wait_event / wait_event_type combinations on the postgresql documentation site. It’s very easy to see if you’re blocked because of some internal postgres behavior, slow responses from the OS / hardware or, if it’s your very own workload that’s causing the issue.

Happy PostgreSQL-ing!