Postgres pg_stat_statements: How to Read the Query Workload Your Database Actually Sees

The standard performance investigation in Postgres starts with a slow request in application logs, pulls the query, runs EXPLAIN ANALYZE, and fixes whatever the plan reveals. That workflow handles individual slow queries. It does not handle the case where the database is slow in aggregate but no single query stands out, and it does not handle the case where the database is doing far more work on some query than the team realizes because the per-call latency is acceptable but the call volume is enormous.

The pg_stat_statements extension solves both. It records the aggregate cost of every distinct query shape the database has executed since the last reset: call count, total time, mean time, rows returned, IO done. The view is one row per normalized query (constants stripped), so the same query template with different parameters aggregates to a single row. This is the right level of abstraction for asking what is consuming database time.

Installation and configuration

The extension ships with Postgres but is not loaded by default. Add it to shared_preload_libraries in postgresql.conf, restart, and run CREATE EXTENSION pg_stat_statements in each database where you want collection. The restart is the only friction in the setup; after that, the view pg_stat_statements is available for queries.

The configuration knobs that matter: pg_stat_statements.max defaults to 5000 distinct query shapes, which is enough for most applications but can fill up with poorly-parameterized SQL or generated queries. The track setting defaults to top, which records only top-level statements; setting it to all includes nested statements inside functions but multiplies the data volume substantially. The save setting defaults to on, persisting statistics across restarts via a file in the data directory.

The three views of workload cost

The basic workload investigation is three queries against pg_stat_statements. First, queries by total time: ORDER BY total_exec_time DESC LIMIT 20. This is the list of where the database is actually spending CPU and IO. The top entries are often surprising: a query that nobody thinks of as expensive but runs 10000 times a minute consumes more total time than a query that runs once a minute and takes a second each time.

Second, queries by mean time: ORDER BY mean_exec_time DESC LIMIT 20. This is the list of queries that are individually slow. The two lists overlap less than you would expect. Total-time leaders are usually high-frequency moderate-cost queries; mean-time leaders are usually low-frequency reporting queries or analytical workloads that run rarely.

Third, queries by call count: ORDER BY calls DESC LIMIT 20. This is the list of what the application is asking the database to do most often. Surprises in this list often reveal application-side N+1 patterns or inefficient ORM use that adds up to substantial database load even when each individual call is fast.

Reading IO and cache behavior

The view also tracks buffer activity per query: shared_blks_hit (cache hits), shared_blks_read (disk or OS-cache reads), shared_blks_dirtied (modifications), and shared_blks_written (flushes during the query). The ratio of hits to reads is the cache fit indicator for a specific query. A query with 99% hit ratio is fine on cache behavior; a query with 50% hit ratio is reading half its working set from disk every execution and is a candidate for either better caching, larger shared_buffers, or a smaller working set.

The temp blocks counters (temp_blks_read, temp_blks_written) flag queries that spill sorts or hash joins to disk. Non-zero values here mean the query exceeded work_mem and is paying the 10-100x slowdown for using temporary files. The fix is either more work_mem or query rewriting to reduce the data volume the sort sees.

The normalization trap

The extension normalizes queries by replacing constants with placeholders. So SELECT * FROM users WHERE id = 42 and SELECT * FROM users WHERE id = 99 aggregate to the same row. This is usually what you want. The trap is when the application is dynamically constructing SQL with concatenation rather than using parameterized queries. In that case, WHERE name = 'Alice' and WHERE name = 'Bob' show up as different rows because the constant is part of the query text, not a parameter. The pg_stat_statements view fills up with thousands of near-duplicate entries and the aggregated cost view becomes useless.

The diagnostic is checking the query count against the distinct query templates in your application. If pg_stat_statements has 5000 distinct queries but the application only uses about 100 query templates, something is constructing SQL by concatenation rather than parameter binding. The fix is the application; pg_stat_statements just makes it visible.

Reset discipline

The view aggregates from the last reset (or last restart with save=off). For a stable picture of normal operation, reset once and let it accumulate for a week or so before serious analysis. For investigating a specific incident, reset before the incident and check the view during or immediately after; the deltas attribute incident-time costs to specific queries.

The reset function is pg_stat_statements_reset(). The argument-taking variant pg_stat_statements_reset(userid, dbid, queryid) resets a single query if you need to clear just one without losing the rest. The two patterns interact: a daily reset of a specific high-volume query that you have already optimized as much as possible keeps the rest of the view useful, while a global reset clears everything.

The five operational signals

The metrics worth tracking continuously: total_exec_time across the top 10 queries (the workload share of identifiable hot paths), mean cache hit ratio per query (cache health by access pattern), temp_blks_written sum (sort spill rate, should be near zero), call count percentile distribution (whether traffic is concentrated or distributed), and the count of queries exceeding 1 second of total time in the period (high-cost workload count, which should be small and stable).

What pg_stat_statements does not show

The extension records per-query statistics but does not show concurrency or lock waits. A query that is fast in isolation but spends most of its real time waiting for locks shows up with low mean_exec_time, because the time-tracking covers execution, not waiting. The complementary view for lock-wait diagnosis is pg_stat_activity with the wait_event columns. For a complete picture of database time, both views are needed.

The extension also does not show the application-level cost of issuing the queries: connection setup, parameter serialization, result deserialization, network latency. Those costs accumulate in the application's view of database call duration and are not visible in pg_stat_statements. The metric to monitor on the application side is total wall-clock time per request, which exceeds Postgres-reported time by however much non-database work happens around the call.

Across our four products

We run DocuMint, CronPing, FlagBit, and WebhookVault on SQLite, which has a simpler statistics surface: EXPLAIN QUERY PLAN and the sqlite_stat1 tables. The aggregate-workload view is missing, and we synthesize it from application logs by capturing query text and duration per call and aggregating in our log pipeline. When we eventually graduate to Postgres for any of these products, pg_stat_statements is one of the first things we will turn on, and the workload picture it produces will be qualitatively different from log-based aggregation because it includes nested and prepared statements that are invisible at the application layer.

The deeper observation is that the question of what a database is doing is usually answered by looking at one slow query at a time, when the more accurate question is what the workload as a whole looks like. The workload view is what pg_stat_statements provides, and most Postgres production teams underuse it because the per-query view is what their application monitoring already provides. The two views are complementary; neither is sufficient alone.