Mastering PostgreSQL 9.6

Finding more detailed information in pg_stat_activity

The pg_stat_activity function is a system view that has been around for many years. It basically contains a list of active connections. In older versions of PostgreSQL, administrators could see that a query is waiting for somebody else—however, it was not possible to figure out why and for whom. This has changed in 9.6. Two columns have been added:

test=# \d pg_stat_activity             View "pg_catalog.pg_stat_activity"       Column      |           Type           | Modifiers  ------------------+--------------------------+----------- ...  wait_event_type  | text                     |   wait_event       | text                     |  ... 

In addition to this extension, a new procedure has been added, which shows who caused whom to wait:

test=# SELECT * FROM pg_blocking_pids(4711); ...