PostgreSQL - Sampling pg_stat_activity with pgcheck

I have added a new option (-psas or pg_stat_activity_sampling) to the pgcheck tool, which samples pg_stat_activity at 1-second intervals over a 1-minute duration. This feature generates reports on Average Active Sessions (AAS), top wait events, and top queries, offering a useful alternative to AWS Performance Insight. While it doesn't provide a long-term history of active sessions, it offers a valuable snapshot for immediate troubleshooting. Here is an example of the output under a pgbench workload:

(venv) someip.myco.com:/u01/app/postgres/pgcheck [etsdb] $ pgcheck.py ~/ini/dbaets.ini -psas
Trying to obtain connection info from the configuation file  /opt/oracle/ini/dbaets.ini ...
wait for 1 min ...
#### pg_stat_activity Sampling start from 2019-08-05 12:25:05.535325 to 2019-08-05 12:26:05.657529
# Average Active Session Report #
Average Active Sessions : 4
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
# wait event report #
wait_event_type    wait_event     #sessions
-----------------  --------------  ----------
Client             ClientRead     107
Lock               transactionid  78
Lock               tuple          23
None               None           17
IO                 XactSync       15
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
# Top SQL  report #
query                                                         wait_event     #sessions
---------------------------------------------------           --------------  ----------
UPDATE pgbench_branches SET bbalance = bbalance + 3758 WHERE  ClientRead     32
END;                                                          ClientRead     26
UPDATE pgbench_branches SET bbalance = bbalance + 27 WHERE b  ClientRead     25
UPDATE pgbench_branches SET bbalance = bbalance + 1458 WHERE  ClientRead     24
END;                                                          transactionid  21
UPDATE pgbench_branches SET bbalance = bbalance + 27 WHERE b  transactionid  20
UPDATE pgbench_branches SET bbalance = bbalance + 3758 WHERE  transactionid  19
UPDATE pgbench_branches SET bbalance = bbalance + 1458 WHERE  transactionid  18
UPDATE pgbench_branches SET bbalance = bbalance + 27 WHERE b  None           7
UPDATE pgbench_branches SET bbalance = bbalance + 1458 WHERE  tuple          7
UPDATE pgbench_branches SET bbalance = bbalance + 1458 WHERE  XactSync       7
UPDATE pgbench_branches SET bbalance = bbalance + 27 WHERE b  tuple          6
END;                                                          tuple          5
UPDATE pgbench_branches SET bbalance = bbalance + 3758 WHERE  tuple          5
UPDATE pgbench_branches SET bbalance = bbalance + 1458 WHERE  None           4
END;                                                          None           4
END;                                                          XactSync       4
UPDATE pgbench_branches SET bbalance = bbalance + 3758 WHERE  None           2
UPDATE pgbench_branches SET bbalance = bbalance + 27 WHERE b  XactSync       2
UPDATE pgbench_branches SET bbalance = bbalance + 3758 WHERE  XactSync       2
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

You can schedule this job to run every minute and output the results to a file if you need to preserve a history of AAS, top wait events, and top SQL queries. 

Implementation Notes 

Previously, this feature required the user to have write privileges because it used temporary tables. I have updated the implementation to eliminate the dependency on temporary tables, instead using a Pandas DataFrame to process the data in memory. This allows the tool to run successfully even on read-only databases. 

Important Considerations 

Please note that because sampling occurs every second, short-lived queries (executing in the millisecond range) may be missed. An Average Active Session count of zero does not necessarily mean no queries were executed during the minute. For a more comprehensive view of executed queries, consider sampling pg_stat_statements using the pgcheck -psss option.

pg_stat_activity sampling is best used to understand the general workload and overall database health.

Summary 

The new -psas option in pgcheck provides a lightweight, real-time snapshot of PostgreSQL activity by sampling pg_stat_activity every second for one minute. This tool helps identify Average Active Sessions and top wait events, serving as a helpful diagnostic resource. By moving to in-memory processing with Pandas, this feature is now compatible with read-only database connections, making it more flexible for troubleshooting performance issues.