Postgres pg_stat_reset: When to Wipe Statistics and What You Lose

The cumulative statistics views in Postgres — pg_stat_user_tables, pg_stat_user_indexes, pg_stat_statements, pg_stat_bgwriter, pg_stat_database, and the rest — are append-only counters that accumulate from the last reset until the next. They are some of the highest-leverage operational tools the database offers, and the reset functions that wipe them are some of the most-misused operational tools in production.

The family of reset functions

Postgres exposes several reset functions, each scoped differently. pg_stat_reset() resets the per-database statistics for the current database including all table and index and function stats. pg_stat_reset_shared(target) resets cluster-wide stats with target options of 'bgwriter', 'archiver', 'io', 'wal', and 'recovery_prefetch'. pg_stat_reset_single_table_counters(oid) and pg_stat_reset_single_function_counters(oid) reset a specific table or function. pg_stat_statements_reset(userid, dbid, queryid) resets the per-query stats with optional scoping. pg_stat_reset_slru(name) resets the simple LRU caches.

The granularity matters because the reset operations are not reversible. Once you reset the bgwriter stats, the cumulative-buffers-written counter starts from zero, and the historical buffer-write rate that you might have wanted to compare against is gone. The same is true for every other view.

What the cumulative counters tell you

The cumulative counters answer three kinds of questions: cumulative volume over time (total bytes archived since last reset, total rows updated since last reset, total queries executed since last reset), rate-of-change derived from two snapshots (rows per second, buffers per second, queries per second), and distribution of work across categories (top-N queries by time, top-N tables by writes, top-N indexes by scans).

The cumulative volume is mostly useful as denominator for the rate calculations. The rate calculations require sampling — you cannot extract a meaningful rate from a single snapshot, only from differences between snapshots. The distribution-of-work calculations work from a single snapshot but become more useful when the snapshot interval is short and the historical pattern is captured.

The implication is that resetting the counters destroys the historical context. If you reset pg_stat_statements during an incident to "see what the database is doing right now," you have also wiped the baseline you needed to compare against.

When reset is the right action

The cases where resetting is correct are narrow. After a major schema change or workload shift where the accumulated history is no longer representative of current behavior, a reset gives a clean baseline for the new pattern. After fixing a long-standing bug that was distorting the statistics (a query running 1000x more than expected because of an application bug), resetting lets the post-fix statistics reflect actual behavior. Periodic resets aligned with regular reporting cycles (monthly, quarterly) can keep the rate calculations comparable across periods if the reset is part of an established discipline.

The reset is also useful during deliberate diagnostic windows. If you want to measure exactly what happens during a five-minute load test, resetting pg_stat_statements at the start and capturing the snapshot at the end gives you the cleanest possible data for that window. The cost is that the reset wiped the longer-term context, so the diagnostic value of the post-test snapshot is the test itself, not comparison against baseline.

When reset is the wrong action

The cases where resetting is wrong are common. The classic mistake is resetting during an incident because the cumulative numbers feel like they "are not relevant any more." During an incident, the historical context is exactly what you need — the question is not what the database is doing right this second (the active-session views and trace logs answer that), but how the current behavior compares to the baseline. Resetting destroys the baseline at the moment you most need it.

The second mistake is resetting because the numbers have grown large and look unwieldy. The cumulative counters use bigint and do not overflow in any reasonable operational timeframe. Large numbers are not a reason to reset; they are evidence that the counters have been accumulating useful information.

The third mistake is resetting one view without resetting the others. The views share an implicit time origin in your monitoring — a graph showing pg_stat_user_tables row counts alongside pg_stat_statements query counts is only meaningful if both series have consistent time origins. Resetting one while leaving the other produces graphs that look like behavior changed when only the reset changed.

Capture before reset

The discipline that compounds over years is to always capture before reset. The pattern is simple: CREATE TABLE pgstat_snapshot_YYYYMMDD_HHMMSS AS SELECT * FROM <view> before running the reset function. The snapshot tables accumulate as historical record, and you can query them later to reconstruct the baseline that the live view no longer carries.

The retention question for snapshot tables depends on the operational cost. For pg_stat_statements with 5000 entries, each snapshot is small (under a megabyte) and can be retained indefinitely. For per-table or per-index snapshots in databases with tens of thousands of tables, the snapshot can be substantial and retention should match the lookup horizon.

A useful pattern is to schedule daily snapshots of the views you care most about as part of normal operational hygiene, regardless of whether you plan to reset. The snapshots give you the historical context that the live views provide for cumulative-from-last-reset but not for arbitrary historical points. The reset operation then becomes safer because the data it wipes is captured elsewhere.

What the views do not reset

A subtle gotcha is that not every counter visible in the stat views actually gets reset by the standard reset functions. The pg_stat_database view's stats_reset column tracks when the per-database reset happened. The pg_stat_bgwriter view has its own reset timestamp tracked via pg_stat_reset_shared('bgwriter'). The pg_stat_archiver view has its own reset tracked via pg_stat_reset_shared('archiver'). These per-view reset timestamps let you check when each view was last reset, which is useful for interpretation but means a single "reset everything" operation requires multiple function calls.

Another subtle gotcha is that pg_stat_statements resets are separate from the standard pg_stat resets — calling pg_stat_reset() does not affect pg_stat_statements, and calling pg_stat_statements_reset() does not affect the standard views. The extension maintains its own state independently of the core statistics collector.

The configuration adjacents

The cumulative statistics infrastructure has configuration that affects what is captured and at what cost. track_activities controls whether per-session current-activity tracking is enabled (almost always yes). track_counts controls whether per-table counts are collected (must be on for autovacuum to work). track_io_timing controls whether IO timing is captured (default off because of clock-call cost; turn on for diagnostic windows). track_functions controls function statistics (default none; 'pl' is the right default for PL/pgSQL-using applications).

The configuration is mostly set-once and forget, but the track_io_timing toggle is worth knowing about — turning it on for an incident-diagnostic window adds substantial signal (per-statement IO time) at modest cost (a few percent overhead on systems with fast clock-source), and turning it back off after restores the cheaper baseline.

Our use across the four products

Our products are SQLite-based and do not have the same family of cumulative statistics views, but the operational discipline transfers. The Postgres migration plan includes capturing pg_stat snapshots daily as part of routine operational hygiene, treating reset operations as deliberate-with-capture events, and documenting which views were reset and when in an audit table.

The deeper observation here is that the cumulative statistics views are some of the highest-leverage observability features in Postgres, and casual reset operations destroy more value than they create. The discipline of capturing before resetting and resetting only when there is a clear reason pays back in proportion to how much you depend on historical context during operational incidents — and the moments when you most depend on it are the moments when the temptation to reset is also strongest.

Our products: DocuMint (PDF invoice generation API), CronPing (cron job monitoring with status pages), FlagBit (feature flags API for modern teams), and WebhookVault (webhook capture and replay) put these patterns into production.