Skip to content

PostgreSQL 17: Reset shared statistics at once with pg_stat_reset_shared()

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.

Questions about your own setup? A dbi services consultant can look at your case.

Talk to an expert (opens in a new tab)

Let’s work together

Ready to operate at
the next level?

Tell us about your challenge. We’ll bring the depth, the expertise, and the Swiss precision.

Open source Team

Open source Team

Posts published under the Open source Team account are collective contributions by the dbi services open infrastructure consultants. The practice covers open-source databases and platforms, PostgreSQL first of all, along with MySQL, MariaDB, and Linux, from architecture, replication, and high availability to performance tuning and operations under service level agreements, and contributes actively to the PostgreSQL community.View Author posts

721 posts since September 2011

Most read in Database

The Database posts our readers open most: the all-time favourites, and what is trending right now.

Leave a Comment

Your email address will not be published. Required fields are marked *