Hi all,
After some feedback I changed the script into an extension. Just copy
the files to the extension directory and load it with:
create extension pgstat_snap;
It can be installed in any schema and does not require super user
rights, only requirement is that the pg_stat_statement extension is loaded.
Usage is more or less the same, I've written a detailed description for
how to work with the extension on my github.io:
https://raphideb.github.io/postgres/pgstat_snap/#drill-down
The extension replaced the script and is available here:
https://github.com/raphideb/pgstat_snap
have fun ;)
raphi
Am 25.06.2025 um 16:55 schrieb raphi:
Hi all,
I've created a script which helps me as a DBA to trace down
performance problems, especially on clusters with multiple databases
installed. It creates timestamped copies of pg_stat_statements and
pg_stat_activity and provides two views that contain the execution
delta of every query/dbid between timestamps, e.g. how many rows were
affected. Basic workflow is:
- Install:
pgsql
\i pgstat_snap.sql
- collect, e.g. every 1 second 60 times:
CALL pgstat_snap.create_snapshot(1, 60);
- analyze - the _d columsn are the difference between snapshots
select * from pgstat_snap_diff order by 1;
snapshot_time queryid query datname
usename wait_event_type wait_event rows_d calls_d exec_ms_d
sb_hit_d sb_read_d sb_dirt_d sb_write_d
2025-03-25 11:00:19 4380144606300689468 UPDATE
pgbench_tell postgres postgres Lock transactionid 4485
4485 986.262098 22827 0 0 0
2025-03-25 11:00:20 4380144606300689468 UPDATE
pgbench_tell postgres postgres Lock transactionid 1204
1204 228.822413 6115 0 0 0
2025-03-25 11:00:20 7073332947325598809 UPDATE
pgbench_bran postgres postgres Lock transactionid 1204
1204 1758.190499 5655 0 0 0
2025-03-25 11:00:21 7073332947325598809 UPDATE
pgbench_bran postgres postgres Lock transactionid 1273
1273 2009.227575 6024 0 0 0
2025-03-25 11:00:22 2931033680287349001 UPDATE
pgbench_acco postgres postgres Client ClientRead 9377
9377 1818.464415 66121 3699 7358 35
2025-03-25 11:00:22 7073332947325598809 UPDATE
pgbench_bran postgres postgres Lock transactionid 1356
1356 1659.806856 6341 0 0 0
2025-03-25 11:00:23 7073332947325598809 UPDATE
pgbench_bran postgres postgres Lock transactionid 1168
1168 1697.322874 5484 0 0 0
- when done, uninstall:
SELECT pgstat_snap.uninstall();
DROP SCHEMA cascade;
The wait_event is a bit wonky for queries that are executed multiple
times per interval, it's the one the query was suffering from at the
time when the snapshot was taken.
The script and full documentation is here:
https://github.com/raphideb/pgstat_snap
Thought this might be useful for some, please let me know if there's
something I could improve.
have fun
raphi
