PostgreSQL Tutorial: Reset Cumulative Statistics

Summary: In this tutorial, we will discuss what the pg_stat_reset function does, when and why to use it, and the impact it has on your PostgreSQL database.

Understanding reset of cumulative statistics

When PostgreSQL resets the statistics collected by the cumulative statistics system, it will reset all statistics counters to zero. This includes data about table access, index usage, function execution, and more.

The content in the collected statistics views will change, includes pg_stat_all_tables, pg_stat_all_indexes, pg_stat_user_functions, pg_statio_all_tables, pg_statio_all_indexes and pg_stat_statements etc.

When will the cumulative statistics reset?

PostgreSQL stores temporary statistics data in a separate directory, which is determined by the stats_temp_directory parameter. This can be a path relative to the data directory or an absolute path. The default is pg_stat_tmp. Pointing this at a RAM-based file system will decrease physical I/O requirements and can lead to improved performance.

PostgreSQL updates the cumulative statistics through statistic files under the pg_stat directory, when the statistics collector exits. The saved statistics can be reused at next startup. These statistics data will not be flushed into disk files when a checkpoint occurs, and have none related WAL records when the statistics data changes.

There are several cases when the reset of cumulative statistics will occur, or the cumulative statistics data becomes invalid:

• Resets the cumulative statistics using the pg_stat_reset or pg_stat_statements_reset function.
• In a streaming replication, when the primary server fails and a standby server becomes the new primary, the cumulative statistics data becomes invalid.
• Restoring a backup taken using pg_basebackup. In a PostgreSQL base backup, the contents of the pg_stat_tmp directory is excluded.
• Recovering using a continuous archive backup (PITR).
• PostgreSQL server crash or abnormal shutdown.
• Major version upgrade.

Impact of resetting statistics

Resetting statistics allows you to clear historical usage data, but it doesn’t impact the performance or availability of the database itself. However, it will result in the loss of historical data regarding performance and usage, which could be useful for diagnosing long-term trends or issues.

Besides, when some third-party monitoring tools periodically call pg_stat_reset(), the autovacuum worker might not working. The problem with pg_stat_reset() is that it zeros n_dead_tup and n_mod_since_analyze. If someone resets the statistics on a monthly basis, then this can mean that tables that normally receive an autovacuum any less frequently than once per month could never receive an autovacuum, at least not until an anti-wraparound vacuum gets hold of it.