Mastering PostgreSQL 12 - Third Edition

Querying pg_stat_activity

The following query shows you how many queries are currently being executed on your database:

test=# SELECT datname,         count(*) AS open,         count(*) FILTER (WHERE state = 'active') AS active,         count(*) FILTER (WHERE state = 'idle') AS idle,         count(*) FILTER (WHERE state = 'idle in transaction')                  AS idle_in_trans FROM pg_stat_activity WHERE backend_type = 'client backend'GROUP BY ROLLUP(1); datname | open | active | idle | idle_in_trans ---------+------+--------+------+--------------- test    | 2    | 1      | 0    | 1         | 2    | 1      | 0    | 1(2 rows)

To show as much information as possible on the same screen, partial aggregates are used. We can see active, idle, and idle-in-transaction queries. If we can see a high number of idle-in-transaction ...