PostgreSQL wait/CPU/predicate extensions: buildable locally, dark on Aurora

Split out of #2545, which delivered the detection and vocabulary those extensions were waiting on. This issue carries the three that cannot be built responsibly yet, and says why.

The three

extension	what it buys	status on the evaluation fleet
`pg_wait_sampling`	wait attribution by `queryid`, joined to `pg_stat_statements`	not offered
`pg_stat_kcache`	real OS CPU and disk per query	absent
`pg_qualstats`	predicate statistics — which columns are filtered on, how selectively	absent

Measured by pg_extension_availability against a live Aurora target, not inferred from documentation. #2545 listed pg_stat_kcache as "Supported on Aurora (version-dependent)"; it is not. It also asked for pg_wait_sampling's Aurora support to be checked before anyone planned around it — checked, and it is not offered.

Why this is blocked rather than just unscheduled

pg_wait_sampling is the biggest single gap in PostgreSQL coverage. Today PgWaitStatsCollector reads aurora_stat_system_waits(), so wait analysis exists only on Aurora — every self-hosted, on-prem and plain-RDS target gets no wait data at all. That is exactly backwards from where the extension story is richest, and it is the core of how this product reasons about SQL Server.

So the value is not in question. What is missing is a target to build against.

Writing these three from documentation would mean shipping collectors nobody has ever run. The PostgreSQL collectors in this codebase have hit the same class of defect five times — a catalog whose scope does not match its name (pg_available_extensions vs pg_extension, pg_stats, pg_locks vs pg_class, the buffer pool vs pg_class, and #2599). Every one was caught by running against a live server. None would have been caught by reading the docs, and each produced output that looked correct.

pg_wait_sampling in particular needs shared_preload_libraries and runs a background worker, so its failure modes are configuration-shaped — precisely the kind a doc-derived collector gets wrong quietly.

What unblocks it

A self-hosted PostgreSQL target with these extensions installed, reachable from a monitoring host. That is the prerequisite; the collectors are straightforward once there is something to verify them against.

Worth doing in one pass when it comes up, since all three are pg_stat_statements-adjacent and share the same join and the same "extension absent" degradation path that #2545's vocabulary already provides.

Correcting this issue's premise — mine, written a couple of hours ago. I said these three were blocked on "a self-hosted PostgreSQL target with these extensions installed, reachable from a monitoring host," and treated that as a real prerequisite. It took about five minutes to build one.

docker run -d --name pgext -e POSTGRES_PASSWORD=... postgres:17
apt-get install postgresql-17-pg-wait-sampling postgresql-17-pg-stat-kcache \
                postgresql-17-pg-qualstats postgresql-17-hypopg
# restart with the modules preloaded

All four are packaged by PGDG for PostgreSQL 17 and install without compiling. Verified live:

extension	version	evidence
`pg_wait_sampling`	1.1	`pg_wait_sampling_profile` returned 13 rows after load
`pg_stat_kcache`	2.3.2	`pg_stat_kcache()` returned 7 rows with real `exec_user_time`
`pg_qualstats`	2.1.4	`pg_qualstats()` returned predicates
`hypopg`	1.4.3	installed

So the blocker was never "a target does not exist" — it was that I had not tried to make one.

One finding already, which is exactly why building against a live instance matters

pg_qualstats() returned 0 rows at first, on a server that had just run the queries it was supposed to be recording. The cause is pg_qualstats.sample_rate, which defaults to 0.01 — a 1% sample. At sample_rate = 1 the predicates appear.

A collector written from the documentation would have shipped reporting "no predicates" on a busy server, and that reads as this workload has no interesting filters rather than you are looking at one percent of it. That is the same shape as every other defect this codebase has hit on PostgreSQL: correct-looking output, wrong meaning. Whatever collector lands must record the sample rate alongside the counts, or the numbers are uninterpretable.

What is actually still blocked

Nothing, for building and verifying these three. What a container cannot tell us is how they behave on the fleet we would deploy to — and for two of them the answer is already known and unchanged: pg_stat_kcache and pg_qualstats are absent on Aurora, pg_wait_sampling is not offered. So these collectors would ship dark for the fleet in front of us and light up for self-hosted customers, which is exactly the case the parent issue argued for.

That is a scheduling judgement, not a technical blocker, and it is a different question from the one this issue was opened on. Retitling accordingly.

Measured pg_wait_sampling 1.1 against a live PostgreSQL 17, because the design turns on things the documentation does not make obvious.

The shape

pg_wait_sampling_profile is (pid, event_type, event, queryid, count) — cumulative sample counts since server start or the last reset, not time, and cluster-wide. There is no dbid in 1.1, so this collector must not claim per-database attribution (#2599's lesson: a column whose scope does not match its name).

The trap, and it is the whole design

A straightforward "top waits by count" reads like this on an idle-but-healthy server:

Activity / AutovacuumMain       queryid=0   count=18
Activity / LogicalLauncherMain  queryid=0   count=18
Activity / WalWriterMain        queryid=0   count=18
Activity / CheckpointerMain     queryid=0   count=18
Activity / BgwriterHibernate    queryid=0   count=9

Every one is a background worker sitting idle, and they dominate permanently because they are always waiting. Filtering to queryid <> 0 at that moment returned nothing at all.

A collector that shipped this without an opinion would report AutovacuumMain as the server's top wait forever — correct-looking output, wrong meaning, which is the same failure mode as every other PostgreSQL defect this codebase has hit.

Under real contention the signal is exactly what this issue promised:

Lock    / tuple           queryid=4654506383535020975   samples=961
Lock    / transactionid   queryid=4654506383535020975   samples=491
Timeout / PgSleep         queryid=5457019535816659310   samples=601

Attributed to queryid, joinable to pg_stat_statements — the "which query waited on what" story that exists only on Aurora today.

What that means for the collector

event_type = 'Activity' is idle-by-definition and must be excluded or held apart from the ranked set. Activity is how PostgreSQL labels a background process waiting for work.
queryid = 0 means no statement attribution. Worth keeping as an aggregate, but it cannot be ranked next to attributed waits without misleading.
Cumulative, so it needs delta treatment, the same as pg_statement_stats. A reset — or a server restart — makes the counter go backwards, and that has to read as a reset rather than as a negative wait.
count is samples, not milliseconds. At the default 10 ms profile period a count is roughly count × 10 ms of observed wait, but that is an inference from the sampling period and the column must not pretend to be a duration.
pg_qualstats.sample_rate defaults to 0.01 for the sibling collector — recorded here too because it is the same class of "the number is a sample, say so" problem.

Merge pull request #2609 from erikdarlingdata/feature/2603-pg-wait-sa…

pg_wait_sampling shipped in #2609, schema v96. Leaving this open for the remaining two.

Done: collect.pg_wait_sampling, attributing each wait to the queryid that waited so it joins pg_statement_stats. Wait analysis is no longer Aurora-only.

Still here: pg_stat_kcache (OS CPU and disk per query) and pg_qualstats (predicate statistics). Both are installable and verifiable locally now, so neither is blocked — what remains is that all three ship dark on Aurora, which is a scheduling judgement rather than a technical one.

For whoever picks those up, the two traps already measured:

pg_qualstats.sample_rate defaults to 0.01. pg_qualstats() returning zero rows on a server that just ran the queries is a 1% sample, not an idle server. The collector must record the rate beside the counts or the numbers are uninterpretable.
pg_stat_kcache is a function, not a view — pg_stat_kcache() — and it is cumulative like pg_stat_statements, so it needs the same reset-aware delta treatment Attribute PostgreSQL waits to the query that waited (#2603) #2609 built for the wait profile: a counter going backwards is a reset, and GREATEST(new - old, 0) reports zero across a restart instead of saying so.

Merge pull request #2610 from erikdarlingdata/feature/2603-pg-stat-kc…

Merge pull request #2611 from erikdarlingdata/feature/2603-pg-qualstats

All three shipped.

extension	table	schema
`pg_wait_sampling`	`collect.pg_wait_sampling`	v96 (#2609)
`pg_stat_kcache`	`collect.pg_kernel_stats`	v97 (#2610)
`pg_qualstats`	`collect.pg_predicate_stats`	v98 (#2611)

Wait analysis is no longer Aurora-only, elapsed time can now be separated from CPU, and predicates with no index behind them are visible for the first time.

Every one of the three had a defect that only running it would have found, which is worth recording because the premise of this issue was that they were blocked on a target to verify against:

wait sampling — the raw profile's top rows are background processes idling, permanently, because the server is quiet. Ranked as-is it reports AutovacuumMain as the top wait on every healthy server. And the exclusion has to be IS DISTINCT FROM, because NULL <> 'Activity' is NULL and a plain inequality silently discards every on-CPU sample.
kcache — exec_read_bytes = 0 on every row while writes were not, because the counters measure I/O that reached the device and the page cache served the reads. Shipping that as "no reads" would have been wrong in a way nobody would question.
qualstats — the OIDs are per-database while the function is cluster-wide, so the obvious join either silently drops other databases' rows or names the wrong column. Two cross-database rows were present and neither collided locally that day; different OID luck would have produced the confident wrong answer instead.

All three ship dark on Aurora, which offers none of them. That is the case this issue's parent argued for and it is now testable: PGDG packages all four extensions for PostgreSQL 17 and a verified rig takes about five minutes to build.

Navigation Menu

Repository navigation

PostgreSQL wait/CPU/predicate extensions: buildable locally, dark on Aurora #2603

The three

Why this is blocked rather than just unscheduled

What unblocks it

One finding already, which is exactly why building against a live instance matters

What is actually still blocked

The shape

The trap, and it is the whole design

What that means for the collector

Metadata

Assignees

Labels

Projects

Milestone

Relationships

Development

PostgreSQL wait/CPU/predicate extensions: buildable locally, dark on Aurora #2603

Description

The three

Why this is blocked rather than just unscheduled

What unblocks it

Activity

erikdarlingdata commented on Aug 25, 2026

One finding already, which is exactly why building against a live instance matters

What is actually still blocked

erikdarlingdata commented on Aug 25, 2026

The shape

The trap, and it is the whole design

What that means for the collector

erikdarlingdata commented on Aug 25, 2026

erikdarlingdata commented on Aug 25, 2026

Metadata

Metadata

Assignees

Labels

Projects

Milestone

Relationships

Development

Issue actions