pgstat_snap - create adhoc pg_stat_statements snapshots

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