public inbox for [email protected]  

help / color / mirror / Atom feed

From: raphi <[email protected]>
To: [email protected]
Subject: pgstat_snap - create adhoc pg_stat_statements snapshots
Date: Wed, 25 Jun 2025 16:55:07 +0200
Message-ID: <[email protected]> (raw)

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

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected]
  Subject: Re: pgstat_snap - create adhoc pg_stat_statements snapshots
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox