Re: Improve pg_stat_statements scalability

Hi,

My apologies in advance for the long message, but there is quite a bit of
progress to cover.

Since presenting the design and benchmarks at the PGConf.EU 2026
Community Events Day Observability Summit, I have been getting the
patches in a reviewable state and benchmarking them a lot more, across
workloads and machine sizes.

A good part of the groundwork has already been committed.

- 4ba012a8ed9 Allow cumulative statistics to read/write auxiliary data
from/to disk
- 44539786b82 Allow a stats kind to use its own dshash in pgstats
- 6296e78c3d2 Add "own_hash" to pg_stat_kind_info
- 31f89191ff7 Add stats kind filter to pgstat_drop_matching_entries()
- e65c331b8fb Add new pgstats routine to split pending data setup

The rest is in the attached patches, 0001 and 0002 being the parts still
needed in core.

With Claude, and my direction on what to benchmark, I re-created the
benchmarking tooling [1]. It runs 4 workloads.

- hotcold_100m, 4k hot statements and 100 million unique ones. This is the
worst case, and it should fail gracefully, unlike upstream, where it
turns into a lock convoy.
- hotcold_1500, 4k hot statements and 1500 others, so constant, moderate
churn.
- select_only, a few statements and no churn.
- trickle_5000, 5k hot statements and an eviction every once in a while.

TPS is not the only thing it measures [2]. It also looks at how TPS holds
up over time, how well hot and cold queries are kept, query text sizes,
etc. Based on all of these, we keep the same experience as upstream
today, and gain the performance of pluggable stats.

v5 attached and benchmarked:

1/ 0001 allows stats to be flushed in-transaction. It has its own thread
[3], and I am attaching it here only to keep cfbot happy.

2/ 0002 is "pgstat: Allow a kind to release its references after their
flush". I have not started a thread for it yet, but this work needs it.
Today, a backend holds on to its entry references until it is asked to
release the ones to dropped entries, which makes every backend scan all of
its references. With this patch, a kind can have its references released
as soon as their pending stats are flushed, and other kinds only pay for
checking the option. It is an option of the kind and not of each lookup,
as it makes no sense for a kind to mix and match. Either all of its
references are released at flush or none are, otherwise it is not clear
when a dropped entry actually goes away.

This one has been bothering me for a while. pg_stat_statements evicts
entries all the time, especially under high churn, but an evicted entry
is not freed until every backend that references it lets go, and today
that is left to the garbage collection of references. An extension with
that much churn needs finer control over when its references go away.

The benchmarks back that up. Eviction runs at around pg_stat_statements.max
entries, and as a backstop, we stop creating new entries if the allocated
ones, including dropped ones not freed yet, reach twice that, or at most
20,000 more. If backends hold on to dropped entries, we can get close to
the backstop under high churn. With this patch, allocated entries peak at
around 5,500 against 10,000. Without it, we kept hitting the backstop and
stopped tracking new statements.

This also adds pgstat_entry_ref_is_stale() to pgstat_internal.h, which
pg_stat_statements uses, so it could be its own patch later.

3/ 0003 stores the statement entries as custom cumulative statistics, with
the query texts still in the external file.

4/ 0004 moves the query texts into shared memory, with the same semantics
otherwise.

There are no tests for 0002 yet, and the tests in 0003 and 0004 were
written by Claude, so I still need to go through them carefully.

The results are at [4], from 1 to 512 clients on 2 to 192 vCPUs, with 5,
20 and 45 minute runs, and runs without pg_stat_statements loaded.

In short, the benchmarks show real improvement. On small instances the
patches are close to upstream, and from 32 vCPUs up 0004 is 1.3x to 5x
upstream, close to the same server without pg_stat_statements. Waits on the
pg_stat_statements lock are lower everywhere, all popular statements are
kept, eviction runs about as often as upstream or less, and the backstop
is never hit. The TPS swings on the 192 vCPU instance also show up
without pg_stat_statements, and profiling shows ProcArrayLock in
GetSnapshotData as the culprit. That needs a separate investigation.

A few datapoints at low client counts show 0004 1% to 4% slower than
upstream. With extreme high churn, hotcold_100m at 256 and 512 clients on
the 96 and 192 vCPU instances, 80% to 92% of the wait samples of 0004 are
on the pg_stat_statements lock, which now only serializes creating
entries, and from 64 to 512 clients there, 0004 has 2.6x to 5.1x the TPS
of upstream.

IMO, 0003 is a small step in behavior, since we still use a query text
file. 0004 is the goal, but I know from a few hallway discussions at
PGConf.EU that keeping the query texts in memory only raised some
eyebrows. We already need as much memory to read the texts every time
pg_stat_statements is queried, so this goes from peak to sustained memory
usage. I think that is justified, as the memory is capped by
query_text_memory, and in the benchmarks it stays at a few MB for 5,000
entries. Real world query texts are much larger than mine though, so it
needs more discussion, and 0003 can be reviewed on its own meanwhile.

A few open questions/items:

1/ With 0004, pg_stat_statements has 3 wait events, where upstream has 1.
pg_stat_statements is now only for creating an entry,
pg_stat_statements_dealloc for eviction, and pg_stat_statements_qtext for
the query text memory. Everything else goes through the pgstat locks,
which hardly ever wait in the benchmarks. I think pg_stat_statements
should be renamed to pg_stat_statements_alloc, to pair with
pg_stat_statements_dealloc, though anything watching for the old name
would need updating.

2/ PgStatsHash and PgStatsData are shared by all kinds, so a wait there
does not say which kind it is for. Perhaps a kind with its own hash should
get its own tranches, with the kind name in the wait event? A kind cannot
do that today, but it seems worthwhile.

3/ pg_stat_statements.save behaves the same for users, but with it off,
the counters still go to the pgstats file at shutdown and are dropped
when read back. Skipping them would need a kind to decide at runtime
whether its entries get written.

4/ If pg_stat_statements is removed from shared_preload_libraries,
pgstat_read_statsfile() finds entries of a kind that is not loaded and
resets the stats of every kind. That goes for any custom kind, so it may
be worth skipping the entries of a kind that is no longer loaded.

5/ pg_stat_statements uses kind IDs 31 and 32, which I have not reserved at
[5] yet.

[1] https://github.com/samimseih/pg_stat_statements_benchmark
[2] https://github.com/samimseih/pg_stat_statements_benchmark/blob/main/reports/2026-11-11-results.md#how-to-read-the-tables
[3] https://commitfest.postgresql.org/patch/6781/
[4] https://github.com/samimseih/pg_stat_statements_benchmark#published-results
[5] https://wiki.postgresql.org/wiki/CustomCumulativeStats

--
Sami Imseih
Amazon Web Services (AWS)