Refreshing system statistics

Postgres provides several views for tracking and reporting statistics about your databases objects and internal system processes.

Crunchy Bridge also installs the pg_stat_statements extension on all databases. This provides additional views for tracking normalized statistics related to queries executed on your Postgres server.

The counters used by the statistics views are all cumulative, starting from when they were last reset. Since these counters are cumulative, they can become noisy or less relevant over time. Resetting from time to time is important to maintaining useful insights, particularly if you want to analyze current performance.

When to reset pg_stat_statements

• At the start of a new monitoring period
  • If you analyze performance trends daily, weekly, or monthly, resetting pg_stat_statements at the beginning of such a period helps ensure that each period starts with a clean dataset.
• After deploying significant query changes
  • If you have made significant changes to an app, optimized queries, or modified indexes, resetting pg_stat_statements helps to measure the impact of those changes more accurately. It will also help accuracy of reports (for example, the outliers insight) by preventing old, outdated queries from skewing the results.
• When benchmarking query performance
  • If you are running benchmarks and tests to evaluate query improvements, resetting pg_stat_statements makes sure that your results reflect only the queries executed during the benchmark periods.
• After major maintenance operations
  • Resetting pg_stat_statements after major operations (pg_repack, reindexes, altering table structures, etc) allows you to measure how any changes affect performance.

The command to reset pg_stat_statements is:

SELECT pg_stat_statements_reset();
Copy

When to reset statistics views (pg_stat_*)

• After making major modifications to a table
  • Big operations (data imports, purges, or bulk updates) may change access patterns. Resetting statistics helps to surface any such changes.
• After creating new indexes
  • You can reset statistics to assess the impact of newly added indexes without old data interfering.
• After prolonged uptime or unusual activity
  • Over time, statistics can become outdated or skewed by outliers. Occasional resets help to maintain accuracy.

Since resetting the statistics views clears all counters on the server related to the affected databases/tables/objects, it should be done selectively. Postgres offers a function - pg_stat_reset() - to reset all statistics counters for the database. In most cases, it would be better to be more strategic, resetting statistics only for specific tables and their indexes. For example:

SELECT
    pg_stat_reset_single_table_counters(relations.relname::regclass)
FROM (
    SELECT
        'public.sampletable' AS relname
    UNION
    SELECT
        schemaname || '.' || indexrelname AS relname
    FROM
        pg_stat_user_indexes
    WHERE
        relid = 'public.sampletable'::regclass
	) relations;
Copy

Even then, it's a good idea to run VACUUM on any tables that have had the statistics reset. Since AUTOVACUUM uses the insert/update/delete counters to determine whether or not to visit a table, resetting them to zero delays that necessary table maintenance:

VACUUM public.sampledata;
Copy

Summary

• Only reset statistics when necessary; don't do it routinely without reason. This is particularly true for the statistics views.
• Use statistics resets for fresh analysis before testing, benchmarking, or measuring impact of optimizations.
• Avoid resetting the statistics if you want to track cumulative workload patterns over time.