PostgreSQL comes with several catalog views which expose cluster wide statistics. One of them is the new pg_stats_checkpointer view we’ve talked about previously. To reset some of those statistics there is pg_stat_reset_shared(). Beside the pg_stat_checkpointer view, the statistics in pg_stat_bgwriter, pg_stat_io, pg_stat_wal, pg_stat_recovery_prefetch, and pg_stat_archiver can be reset.

Before PostgreSQL 17 you had to call this function individually for all kinds of statistics if you wanted to reset the statistics in all of the views, e.g.:

postgres=# select * from pg_stat_reset_shared('bgwriter');
 pg_stat_reset_shared 
----------------------
 
(1 row)

postgres=# select pg_stat_reset_shared('bgwriter');
 pg_stat_reset_shared 
----------------------
 
(1 row)

postgres=# select pg_stat_reset_shared('archiver');
 pg_stat_reset_shared 
----------------------
 
(1 row)

postgres=# select pg_stat_reset_shared('wal');
 pg_stat_reset_shared 
----------------------
 
(1 row)

postgres=# select pg_stat_reset_shared('io');
 pg_stat_reset_shared 
----------------------
 
(1 row)

postgres=# select pg_stat_reset_shared('recovery_prefetch');
 pg_stat_reset_shared 
----------------------
 
(1 row)

Calling the function without an argument resulted in an error:

postgres=# select version();
                                                   version                                                    
--------------------------------------------------------------------------------------------------------------
 PostgreSQL 16.0 on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit
(1 row)

postgres=# select pg_stat_reset_shared();
ERROR:  function pg_stat_reset_shared() does not exist
LINE 1: select pg_stat_reset_shared();
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
postgres=#

Starting with PostgreSQL 17 this will change, and calling the function without an argument will reset all those statistics at once:

postgres=# select version();
                              version                               
--------------------------------------------------------------------
 PostgreSQL 17devel on x86_64-linux, compiled by gcc-12.2.0, 64-bit
(1 row)

postgres=# select pg_stat_reset_shared();
 pg_stat_reset_shared 
----------------------
 
(1 row)
postgres=# select stats_reset, now() from pg_stat_archiver ;
          stats_reset          |              now              
-------------------------------+-------------------------------
 2023-11-12 10:29:19.341854+01 | 2023-11-12 10:30:42.304791+01
(1 row)

Nice, thanks to all involved.

Post Views: 693
Thumbnail [60x60]
by
Daniel Westermann