pg_stat_statements

"The pg_stat_statements module provides a means for tracking planning and execution statistics of all SQL statements executed by a server." First-party, shipped with PostgreSQL as a contrib module (T1).

It is paged here because it is the substrate under an entire section of awesome-postgres. That catalog's Monitoring section runs to 20 entries and its Optimization section to 14 — pgwatch2, PgHero, pganalyze, pg_web_stats (described in the catalog as "Web UI for pg_stat_statements"), PoWA, pg_stat_monitor. Most of them are a presentation layer over this view. Reading the tools without reading the view would have meant paging the same thing five times.

Normalization is the idea

A production server executes millions of statements that differ only in their literals. This module collapses them:

"Plannable queries (that is, SELECT, INSERT, UPDATE, DELETE, and MERGE) and utility commands are combined into a single pg_stat_statements entry whenever they have identical query structures according to an internal hash calculation. Typically, two queries will be considered the same for this purpose if they are semantically equivalent except for the values of literal constants appearing in the query."

The literals are replaced in the display — "the constant is replaced by a parameter symbol, such as $1" — and the hash is exposed as queryid, "hash code to identify identical normalized queries." That identifier is what makes a query trackable over time, and it is why every tool in that catalog section can agree on what "the same query" means.

What it counts

calls, total_exec_time and mean_exec_time in milliseconds, and rows. Then buffer accounting — shared_blks_hit, shared_blks_read, shared_blks_dirtied, shared_blks_written — which is the same cache-versus-disk vocabulary pev2 reads out of a single plan, aggregated across every execution instead. And WAL accounting: wal_records, wal_fpi (full page images) and wal_bytes, "the total amount of WAL generated by the statement in bytes."

Those last three tie the view directly to write-ahead-logging. A statement's cost is not only its own runtime; it is also the log volume it forces every replica to receive and every archiver to ship. wal_bytes per query is the number that connects a bad UPDATE to the replication lag patroni measures in bytes and the archive backlog pgbackrest parallelises against.

What it costs, and what it silently drops

It needs shared memory, so it needs a restart: "The module must be loaded by adding pg_stat_statements to shared_preload_libraries in postgresql.conf, because it requires additional shared memory. This means that a server restart is needed to add or remove the module." That is the same privileged install path postgresql-extensions records as the thing managed platforms ration.

And it is a fixed-size table, which is the caveat worth carrying: pg_stat_statements.max defaults to 5000, and "if more distinct statements than that are observed, information about the least-executed statements is discarded." The discards are counted, in pg_stat_statements_info's dealloc column. A workload with unnormalizable query shapes — generated SQL, IN lists of varying length — can churn through that ceiling continuously, and the view will keep answering questions without mentioning that it is now describing a subset. Checking dealloc before trusting a top-queries list is the equivalent of checking whether the sample was representative.

Position in the spoke

Between this and pev2 the spoke now has both halves of query observability, and they answer different questions. pg_stat_statements finds which query is expensive, cumulatively, across a real workload — the thing a single plan can never show, because the query that runs a million times at 8ms costs far more than the one that runs nightly at 40 seconds. query-execution-plan and pev2 then explain why that one query is slow, and postgresql-planner-statistics explains where the planner's mistake came from. Find it here, diagnose it there.

Related

postgresql · pev2 · query-execution-plan · postgresql-planner-statistics · write-ahead-logging · postgresql-extensions · pgbench · awesome-postgres