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';</p>

<p>ERROR:  column &quot;waiting&quot; 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';</p>

<p>

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';</p>

<p>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)</p>

<p>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!