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 "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:
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.