Postgres pg_stat_statements_info: Tracking the Statistics Collector's Own Health

The pg_stat_statements extension records normalized SQL statements with execution counts, total time, IO statistics, and a long list of other counters. It has been the standard Postgres query-attribution mechanism for over a decade. What is less widely known is that since Postgres 14, the extension also exposes a small companion view called pg_stat_statements_info that reports on the collector's own operational state. The view has exactly two columns, and most teams never check it. When pg_stat_statements is silently dropping entries because the configured cap is too low, this is where you find out.

What the view exposes

The view has two columns. The dealloc column counts how many times pg_stat_statements has had to evict entries because the number of distinct normalized queries exceeded pg_stat_statements.max. The stats_reset column records the timestamp of the most recent pg_stat_statements_reset() call. Both columns are cluster-wide totals, not per-database, because pg_stat_statements itself is shared across all databases in the cluster.

The deallocation counter is the load-bearing one. Each entry in pg_stat_statements occupies a fixed amount of shared memory, and the extension allocates a fixed-size hash table at startup based on pg_stat_statements.max (default 5000). When the table fills up and a new query needs to be tracked, the extension evicts the least-recently-used entry to make room. Each eviction increments the dealloc counter.

The implication is that the statistics you read from pg_stat_statements are a sample of the query population, not the population itself, and the sample bias is toward frequently-executed queries that resist eviction. If your dealloc counter is climbing steadily, you are missing data from the long tail. The questions pg_stat_statements is best at answering (which queries dominate total time, which queries have the worst tail latency, which queries hammer the cache) become unreliable when eviction is high because the evicted queries are exactly the ones that ran once or twice and produced anomalous behavior.

The diagnostic query

The query to run is straightforward:

SELECT dealloc, stats_reset,
       now() - stats_reset AS collection_window
FROM pg_stat_statements_info;

The interpretation depends on the ratio of dealloc to the elapsed time since stats_reset. A handful of evictions in a multi-month collection window is fine; thousands per day means pg_stat_statements.max is too low for your workload. The right value depends on the application: a B2B SaaS with a small number of templated queries can run fine at the 5000 default; a multi-tenant database with dynamic SQL or many ad-hoc queries might need 25000 or 50000.

The cost of raising pg_stat_statements.max is shared memory. Each entry occupies roughly 5KB depending on query length. Raising the cap to 50000 from 5000 costs about 250MB of shared memory, which is significant on small servers and negligible on large ones. Postgres needs to be restarted for the change to take effect because shared memory allocation happens at startup.

The reset trap

The stats_reset timestamp tells you how far back the statistics actually extend. If somebody (or some monitoring tool) called pg_stat_statements_reset() recently, the data in pg_stat_statements is younger than you might assume and any conclusion you draw about long-term query behavior is misleading. The reset call is sometimes invoked deliberately during incident response to get a clean view of post-incident behavior, but it should not be casual.

The cluster-wide pg_stat_statements_reset() is the dangerous form. The per-query reset (pg_stat_statements_reset(userid, dbid, queryid)) is much safer because it only zeroes a single entry. Use the per-query form when you want to track a specific query's behavior after a deployment or configuration change; reserve the cluster-wide form for incident response.

The configuration adjacents

Three pg_stat_statements parameters interact with the cap. pg_stat_statements.track defaults to top (statements executed at the top level, not nested inside functions). Changing it to all captures statements inside SECURITY DEFINER functions and stored procedures, which can substantially increase the number of distinct entries. pg_stat_statements.track_utility defaults to on (CREATE, DROP, ALTER statements counted). Turning it off can reduce eviction pressure if your workload includes a lot of DDL. pg_stat_statements.save defaults to on (statistics persist across server restarts via a dump file). Turning it off is rarely useful but mentioned for completeness.

The pg_stat_statements.max value should match the actual cardinality of distinct normalized queries your application produces. If you can predict the upper bound (most B2B SaaS applications have a fixed set of queries from the application code plus some operational queries), set the cap to that number plus a small safety margin. If you cannot predict it (multi-tenant applications with customer-generated queries, BI tools that produce ad-hoc SQL), start at 10000 and raise based on observed dealloc rate.

What pg_stat_statements_info does not show

The view does not show per-database deallocation, only cluster-wide. It does not show which queries are being evicted, only that eviction is happening. It does not show shared memory utilization, which would require pg_shmem_allocations or similar low-level views. It does not show the histogram of query frequency that would let you reason about how skewed the population is.

The minimum useful monitoring is to alert on dealloc growing faster than some threshold (a few hundred per day is fine, a few thousand per day means investigate, tens of thousands per day means raise the cap). The next-most-useful monitoring is to alert on stats_reset changing unexpectedly, which can catch monitoring tools or other operators clearing the statistics by accident.

Our use across the four products

DocuMint, CronPing, FlagBit, and WebhookVault all run on SQLite, which has no equivalent to pg_stat_statements built in. We get query-attribution data through application-level instrumentation: each handler logs query duration with a structured tag, and we aggregate via the same log pipeline that handles error rates and request latency. The downside relative to pg_stat_statements is that we cannot reconstruct the planner-normalized query family the way pg_stat_statements does; queries that differ only in literal values are tracked as distinct entries.

The Postgres migration plan includes pg_stat_statements as one of the baseline extensions to install at cluster creation, with pg_stat_statements.max set to 25000 initially and pg_stat_statements_info monitored to catch eviction. The migration plan also includes a daily snapshot of the top-100 queries by total time to an analytics table, which provides longitudinal history that pg_stat_statements alone does not retain. The dealloc counter from pg_stat_statements_info becomes one of the operational signals we monitor.

The deeper observation

Tools that report on their own operational health are often the most useful tools in an operator's toolkit, because they tell you when the data you are reading is reliable. pg_stat_statements_info is in the same category as autovacuum logs, replication slot monitoring, and connection-pool wait statistics: small, easy-to-overlook signals that prevent you from drawing wrong conclusions from larger, more attention-getting datasets. The cost of paying attention is small (one query, one alert threshold, one configuration knob); the cost of not paying attention is reaching wrong conclusions about query performance because pg_stat_statements has been silently dropping the queries you most need to see.

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.