Saving pg_stat_bgwriter snapshots
In order for this data to really be interesting, you need to save periodic snapshots of it with an associated timestamp. It's easy to create a table for that purpose and put a first snapshot into it:
pgbench=# CREATE TABLE pg_stat_bgwriter_snapshot AS SELECT current_timestamp,* FROM pg_stat_bgwriter; SELECT 1 pgbench=# INSERT INTO pg_stat_bgwriter_snapshot (SELECT current_timestamp,* FROM pg_stat_bgwriter);
Now you can wait until some time has passed, with a representative chunk of activity; at least an hour is recommended, preferably a day. Insert a second snapshot into that table:
pgbench=# INSERT INTO pg_stat_bgwriter_snapshot (SELECT current_timestamp,* FROM pg_stat_bgwriter);
And now it's possible to get a difference both in values and in elapsed time between these two. You might even add the above INSERT
to a query scheduling regime using tools like cron
or pgAgent
. Having two snapshots lets you compute averages in units like bytes...