Extension-aware PostgreSQL collection: detect what is installed, collect more when it is, advise when it is not

[erikdarlingdata]

Part of #2530. Raised because much of what PostgreSQL observability needs is not in core — it is in extensions — and the amount we can collect varies per server rather than per engine.

The idea

We already have a capability vocabulary with two axes: engine kind (#2536) and engine edition (#2511). Both answer "this collector cannot run here, permanently, and here is why". Extensions are a third axis, and the only one where the answer is actionable: "this read needs pg_stat_kcache, which is not installed — here is how to install it" is advice a customer can act on today, unlike "your engine does not support this."

That framing matters. Detecting an absent extension and saying so turns a permanent gap into a setup step, which is the difference between a product that looks thin on PostgreSQL and one that tells you how to make it richer.

The extensions that would earn their place

pg_wait_sampling — the biggest one. Today PgWaitStatsCollector reads aurora_stat_system_waits(), so wait analysis exists only on Aurora. Every other PostgreSQL target — on-prem, self-managed, plain RDS — gets no wait data at all. pg_wait_sampling provides pg_wait_sampling_profile / _history / _current, samples at 10 ms by default, and — critically — attributes waits to queryid when paired with pg_stat_statements, which we already collect. That is the "which query waited on what" story, which is the core of how this product reasons about SQL Server, available on any PostgreSQL 14+. Requires shared_preload_libraries and runs a background worker.

auto_explain — plan capture, and see #2538. Confirmed available on Aurora and RDS via a custom DB cluster parameter group plus a writer reboot; Aurora's default cluster parameter group preloads only pg_stat_statements. So plan capture is not blocked on managed PostgreSQL after all — it is a customer configuration step we can detect and advise on.

pg_stat_kcache — real operating-system CPU and disk per query, on top of pg_stat_statements. Supported on Aurora (version-dependent), and it turns "this query has high total time" into "this query burned this much CPU and read this many bytes from disk", which is resource attribution we cannot otherwise get.

pgstattuple — exact bloat (see #2542, which discusses the estimate-versus-exact trade). An extension, so availability is per-server.

pg_buffercache — what is resident in shared buffers, by relation (see #2544).

pg_qualstats / HypoPG — predicate statistics and hypothetical indexes, the basis of index recommendations. Further out, and the PoWA stack is the reference implementation.

What to build first

Not any single extension — the detection and the vocabulary. A read that needs an absent extension should say which one, and how to enable it, in the same shape the engine axes already answer. Once that exists, each extension above becomes a self-contained collector that lights up where it can.

Doing it the other way round produces collectors that fail confusingly on servers that happen to lack a preload.

Note on the fleet this is being evaluated against

Dutchie runs Aurora, so pg_wait_sampling may not be reachable there (its Aurora support was not confirmed by the research and should be checked before it is planned around). auto_explain and pg_stat_kcache are. But the product ships to customers who self-host, and on-prem is where the extension story is richest — this issue exists so that is a deliberate capability rather than an accident of what one fleet happens to run.

Sources: pg_wait_sampling, PoWA — stats extensions, PostgreSQL Wiki — Monitoring, AWS — Working with extensions on Aurora PostgreSQL, pganalyze — auto_explain on Amazon RDS.

[erikdarlingdata]

The framing, better than the one in the issue body

The SQL Server side already adapts to what the server is — SqlMajorVersion, EngineEdition, IsAzureSqlDb, HasMsdbAccess — and gates collectors accordingly. The PostgreSQL side should adapt to what the server has. Same idea, one axis over. That symmetry is the argument for doing this at all, and it is a better one than "extensions are useful".

Recorded as a stretch goal rather than near-term work.

A design note for whoever picks it up, because the obvious approach does not fit

The instinct is to make extension presence a third AppliesTo axis alongside kind and edition. Two things get in the way, and both are consequences of machinery we deliberately built:

1. Every fact a gate reads today is a scalar. CollectorTargetInfo carries nine facts — all bool or int, zero set-valued. The capability derivation (#2518, extended in #2536) sweeps a finite cross-product of those scalars and decodes each gate's IL to check that every fact it reads is either swept or fixed by axis. Extension presence is inherently a set — "which of these N are installed" — which is 2^N combinations if swept naively.

There is an elegant way out if this route is taken: the IL decoder already knows which facts each gate actually reads, so the sweep only needs the extensions some gate genuinely gates on — 2^k for small k, not 2^N. That is a real option rather than a workaround.

2. But extension presence is mutable at runtime, and the other axes are not. Engine kind, edition and version are fixed for the life of a connection. Someone can CREATE EXTENSION pg_stat_kcache while the service is running, and a collector gated off at connect time would stay off until a reconnect — silently, and with no way for the operator to tell why their new extension changed nothing.

That difference argues extension presence should not be an AppliesTo axis at all. AppliesTo answers "should this collector ever run against this target", which is a property of the target. Extension presence is closer to a runtime precondition — the read attempts, finds the surface absent, and answers with the extension's name and how to enable it, in the same miss vocabulary the engine axes use.

That also gets the actionability right: a re-check on the next cycle picks up an extension installed five minutes ago, which is exactly what an operator following our own advice would expect to happen.

What that means for scope

Framed as a runtime precondition rather than a capability axis, this is meaningfully smaller than it first looks — no sweep changes, no IL-guard changes, and each extension-backed collector can land independently. The vocabulary work is the shared piece.

[erikdarlingdata]

Split the design note out into #2546 so it is tracked as work rather than living in a comment here.

The short version: a missing extension should be a runtime precondition evaluated at read time, not a third AppliesTo axis — because unlike engine kind, edition and version, it can change while the service is running, and a gate decided at connect time would silently ignore an extension installed five minutes after we recommended it.

#2546 is worth doing before anything in this issue, and is useful without it: the same shape covers Query Store not being enabled, a stopped system_health session, and possibly HasMsdbAccess, which is currently an AppliesTo fact despite describing a grant somebody can change without a restart.

[erikdarlingdata]

This issue's first premise is now settled, and it shrinks the work — but does not close it.

It opens by proposing extensions as a third capability axis alongside engine kind (#2536) and engine edition (#2511). That framing was examined in #2546 and rejected for a reason that holds: engine kind and edition are fixed for a connection's life, which is what makes the sweep-and-derive machinery sound, whereas someone can CREATE EXTENSION while the service is running. An axis decided at connect time would silently ignore the very fix we recommended.

So it shipped as a runtime precondition instead (#2557), evaluated at read time. Practical consequence: an extension installed five minutes ago is picked up on the next read, with no restart — which is the behaviour this issue actually wants.

And the detection half largely exists already. #2557's most useful finding was that the facts were being collected, classified and given remedies, and simply never reported: PostgresFaultOutcome already writes CREATE EXTENSION pg_stat_statements into collection_log.error_message, and #2560 added pgstattuple advice on the bloat read. So "detect what is installed, advise when it is not" needs far less new machinery than this issue assumed.

What is genuinely left is the third clause: collect more when it is. That is the whole of the remaining work and none of it is done:

• pg_wait_sampling is still the big one. PgWaitStatsCollector reads aurora_stat_system_waits(), so wait analysis exists only on Aurora — every on-prem, self-managed and plain-RDS target gets none. And because pg_wait_sampling attributes waits to queryid when paired with pg_stat_statements (which we already collect), this is the "which query waited on what" story that is central to how the product reasons about SQL Server.
• pg_stat_kcache, PoWA and the rest are unstarted.

One constraint that has changed in this issue's favour: installing objects into a monitored database is now permitted (Erik's call on #2543, 2026-08-23), with guardrails settled there — idempotent and versioned, SECURITY DEFINER with a pinned search_path, documented removal, opt-out, and a missing CREATE grant treated as an ordinary precondition. That does not extend to installing extensions, which generally need superuser or rds_superuser and a parameter-group change for anything requiring a preload, so the advice-not-action posture still stands for those.

Leaving open. The next concrete step is pg_wait_sampling collection, which is worth its own issue when someone picks it up.