Re: pgstat_snap - create adhoc pg_stat_statements snapshots

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
>
>