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

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

The evaluation fleet 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.

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.

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.

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.

This issue's headline claim is now out of date, and the correction makes the work much cheaper.

It says: "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."

That was true when it was written. #2540 shipped PgSessionStatesCollector yesterday, and it changes the premise:

AppliesTo(target) => true — every PostgreSQL target, standbys included, not just Aurora.
Cadence 1 minute.
Stores wait_event_type and wait_event per session per sample — and, critically, query_id beside them, which is PostgreSQL's own normalised fingerprint and joins straight to pg_statement_stats.

So the fleet is already collecting (wait event, query) pairs on every PostgreSQL target. What is missing is not the DATA — it is that nothing aggregates those samples into a wait profile. DarlingPgSessionStatesReader rolls them up per backend, which answers "which session is stuck", not "what does this server wait on".

What that does to the pg_wait_sampling case

Two practical facts, both checked rather than assumed:

It is not available where the fleet runs. pg_wait_sampling is absent from a stock PostgreSQL 17 image's pg_available_extensions — it is third-party, needs compiling and packaging, and needs shared_preload_libraries, i.e. a restart. This issue itself flags that its Aurora support "was not confirmed and should be checked before it is planned around"; nothing I can reach suggests RDS or Aurora offer it.

And the thing it uniquely provided, we now approximate. The issue's strongest argument was that it "attributes waits to queryid when paired with pg_stat_statements". We now have exactly that pairing from our own sampling, without an extension, a preload, or a restart.

The honest limitation, stated plainly

pg_wait_sampling samples at 10ms. We sample at 60,000ms — 6,000x coarser. That difference is not cosmetic and must not be papered over:

Over 24h a 1-minute sample gives 1,440 observations. A wait present 1% of the time yields ~14 hits: directionally real, statistically thin.
A wait present 0.01% of the time is invisible. Short, sharp waits — the latch-style contention SQL Server users expect to see — will not appear at all.
So this supports "what does this server broadly wait on, over days" and "which queries are usually waiting when we look". It does not support "this query spent 30% of its time on this wait", and a read that implied otherwise would be inventing precision.

Recommendation

Aggregate what is already collected, rather than pursuing the extension. A read over pg_session_states grouping by wait_event_type/wait_event, optionally by query_id, with the sample count as its denominator — the same discipline pg_blocking already uses, where the capture count travels with the answer so a reader can tell "not waiting" from "not sampled".

That is a read, not a collector: no new rung, no new extension, no restart, works on every PostgreSQL target today. It is strictly less precise than pg_wait_sampling and strictly more available, and the denominator is what keeps it honest.

Worth retitling this issue around that, and keeping the extension detection (which #2564 now does generically) as the advice path for customers who can install it.

Measured the detection surface this issue says to build first. It is cheap, it is a FOUR-state vocabulary rather than a boolean, and there is one trap that would make a naive collector confidently wrong.

Detection needs no helper function

Both catalogs are readable by a pg_monitor-only role:

pg_available_extensions : 45 rows
pg_extension            :  2 rows

Worth stating because it is the opposite of #2543, where pg_stats returns 0 rows to the same role and needs a SECURITY DEFINER helper. The extension axis is cheap in exactly the way the column-statistics axis is not, so it can ship without touching the monitored database.

THE TRAP: the two catalogs have different SCOPES

pg_available_extensions is cluster-wide — what the binaries on disk offer. pg_extension is per-database — what has actually been created in the database you are connected to. Measured:

pgstattuple installed in 'postgres' : 0
pgstattuple installed in 'otherdb'  : 1
pg_available_extensions, both dbs   : 1

So a collector connected to postgres (or to any single database) that reports "pgstattuple is not installed" is wrong for a fleet where it is installed in the application database — which is where anyone would actually install it. This is the same class of mistake as the extension_available defect I shipped and fixed on #2564: a catalog that looks like it answers the question and answers a narrower one.

Whatever ships has to either fan out per database (as pg_autovacuum_stats already does) or state plainly that its answer is scoped to the connected database. It cannot silently mean the second while reading as the first.

The vocabulary is four states, not installed/not-installed

name                 default  installed  state
pg_buffercache       1.5      (none)     available, not installed
pg_stat_statements   1.11     1.11       installed, current
pgstattuple          1.5      (none)     available, not installed
pg_wait_sampling     -        -          ABSENT (no row at all)
pg_stat_kcache       -        -          ABSENT
hypopg / pg_qualstats -       -          ABSENT

absent — not in pg_available_extensions. The binaries are not on the server, so this is a platform/OS install or, on managed PostgreSQL, simply unavailable. Not actionable by SQL.
available — one CREATE EXTENSION away. This is the actionable state this issue exists to surface, and it is the one a boolean would collapse into the previous one.
installed — current.
outdated — extversion <> default_version, fixed by ALTER EXTENSION … UPDATE. Worth its own state because a stale extension version can be missing columns a collector reads, which fails as a confusing 42703 rather than as a capability gap.

Note that five of the eight extensions this issue nominates were absent on stock PostgreSQL 17 — they are third-party and need installing at the OS level first. So absent will be the common answer, and the advice attached to it has to differ from available's.

Preload-only modules are a FIFTH case and need a different detector

auto_explain does not appear in pg_available_extensions on any server, including ones running it — no CREATE EXTENSION, no control file. I shipped that bug in #2564 and fixed it in #2584, so it is worth carrying forward here rather than rediscovering: any extension-detection collector that treats this axis as one uniform lookup will report auto_explain and pg_wait_sampling as absent on servers where they are loaded and working.

They are detected by a different route entirely — shared_preload_libraries for the library, plus the presence of the module's own GUCs via current_setting(..., true) — which is what pg_plan_capture_readiness already does for auto_explain. That collector is effectively the prototype of this issue's vocabulary for one module, and generalising it is a reasonable shape for the work.

So the axis has two detection mechanisms behind one vocabulary: catalog lookup for true extensions, GUC/preload probing for preload-only modules.

On the fleet note

This issue flags that pg_wait_sampling's Aurora support was unconfirmed. Still unconfirmed — I went to check the Aurora extension list and the AWS SSO session had expired mid-session, which needs an interactive re-authentication. The same check would settle pgstattuple for #2561, where it decides whether exact index bloat is the primary path or the fallback. Worth doing both in one pass.

What I did confirm on #2538: auto_explain IS in Aurora's allowed shared_preload_libraries on both 16.11 and 17.7, loaded on zero of 51 cluster parameter groups — which is precisely the "detectable, advisable setup step" this issue describes, and it is already detected as of #2584.

Merge pull request #2588 from erikdarlingdata/feature/2545-extension-…

The Aurora availability question this issue flagged as unconfirmed is now answered — by the collector itself, on a live Aurora cluster.

The PostgreSQL monitoring host is upgraded to the build carrying pg_extension_availability, and it ran against the Aurora target. Of 101 extensions the cluster offers:

pgstattuple          available   1.5
pg_buffercache       available   1.5
hypopg               available   1.4.1
pg_trgm              available   1.6
pg_cron              available   1.6
pg_stat_statements   installed   1.11
pg_stat_kcache       ABSENT
pg_qualstats         ABSENT

Totals across everything the server offers: 96 available, 3 installed, 1 outdated, 2 absent.

What this settles

pg_stat_kcache is NOT available on Aurora. This issue lists it as "Supported on Aurora (version-dependent)" — on this cluster it is not offered at all, so per-query OS CPU and disk attribution is not reachable here. That is a correction to the issue's own premise and worth knowing before anyone plans around it.

pg_wait_sampling is likewise not offered, which resolves the note this issue explicitly left open ("its Aurora support was not confirmed by the research and should be checked before it is planned around"). It is not there. So on this fleet wait analysis stays Aurora-native via aurora_stat_system_waits(), and pg_wait_sampling remains a self-hosted-only story — exactly the split this issue anticipated, now with a measurement behind it rather than a question mark.

pgstattuple and pg_buffercache are both available, which is the good news: two collectors that were written defensively against the possibility of absence turn out to have a real home on this fleet, one CREATE EXTENSION away.

The vocabulary working end to end

The buffers collector (#2592) degraded exactly as designed on the same run:

pg_buffer_usage => PERMISSIONS (42P01): relation "public.pg_buffercache" does not exist

recorded as a non-fatal skip with an actionable message rather than an error — and the extension panel now reports that same extension as available. So the collector that cannot run and the surface that says how to make it run agree, which is the whole point this issue made about turning a permanent gap into a setup step.

Still open here

The individual extension collectors. Two of the six this issue nominated (pg_stat_kcache, pg_qualstats) are ruled out for Aurora; pg_buffercache is already consumed by #2592; pgstattuple by #2561 and #2542. What remains genuinely unbuilt is pg_wait_sampling — self-hosted only — and hypopg, which is available here and unexplored.

Correction to my earlier comment, affecting one of the four extensions I reported.

I listed pgstattuple as available (installable) on the Aurora target. That reading came from this collector, which runs once per target against the configured database. The per-database pg_table_bloat_stats collector reports a pg_extension row for pgstattuple in the database it measured — so it is installed there, not merely available.

Both readings are correct; they are looking at different databases. That is the defect this collector has, now filed as #2599: installed/outdated are per-database claims derived from pg_extension, available/absent are cluster-wide claims derived from pg_available_extensions, and this table mixes both in one state column with no database_name to disambiguate. The type's own doc comment (lines 41-45) already spells out why that is wrong.

The rest of the reported findings are unaffected — pg_stat_kcache and pg_qualstats are absent and pg_wait_sampling is not offered, and those are pg_available_extensions reads, which are genuinely cluster-wide.

Status: the foundational ask is delivered; here is the precise remainder

This issue's "what to build first" was the detection and the vocabulary, not any single extension. That is shipped:

v89 collect.pg_extension_availability — four states (installed / outdated / available / absent), each carrying a comment saying what the extension would buy. absent is derived against an enumerated roster, because absence is not a row in any catalog.
PostgreSQL: two collectors report per-database facts with no database_name (pg_extension_availability, pg_column_stats) #2599 fixes a scope error in it: installed/outdated come from the per-database pg_extension, available/absent from the cluster-wide pg_available_extensions, and it was reading both through one connection. It now runs per database and records database_name.

Two of the dependent collectors this issue anticipated have also landed: pgstattuple → pg_index_bloat (v94, #2561) and pg_buffercache → pg_buffer_usage (v93, #2544). Both degrade to a named, non-fatal skip where the extension is absent — verified live, pg_buffer_usage recorded PERMISSIONS (42P01) on a target whose extension panel simultaneously reported that extension as available, which is the vocabulary working end to end.

What is left, and why two items cannot be built responsibly right now

item	status
detection + vocabulary	done (v89, corrected by #2599)
`pgstattuple` → exact index bloat	done (v94)
`pg_buffercache` → buffer residency	done (v93)
`auto_explain` → plan capture	tracked on #2538 / #2565, mechanism decided
`pg_stat_kcache` → OS CPU/IO per query	blocked: absent on Aurora
`pg_wait_sampling` → wait attribution by `queryid`	blocked: not offered on Aurora
`pg_qualstats` → predicate statistics	blocked: absent on Aurora
`hypopg` → hypothetical indexes	buildable — `available` on Aurora

The issue's note asked for pg_wait_sampling's Aurora support to be checked before planning around it. Checked: not offered. It also listed pg_stat_kcache as "Supported on Aurora (version-dependent)" — that is wrong, it is absent. pg_qualstats likewise.

So three of the named extensions are unreachable on the only PostgreSQL fleet available to test against. They remain genuinely valuable for self-hosted targets, which is the case this issue makes and I am not arguing with — but a collector for them would be written from documentation and shipped unverified, and this codebase's PostgreSQL collectors have now hit the same class of bug five times (a catalog whose scope does not match its name), every one of them caught by running against a live server rather than by reading docs.

My recommendation: narrow this issue to hypopg, and split the three Aurora-absent extensions into their own issue blocked on access to a self-hosted PostgreSQL target. That is a real prerequisite, not a stall — it is the difference between shipping a wait-analysis story and shipping an untested one.

Splitting it now.

Split done: #2603 carries pg_wait_sampling, pg_stat_kcache and pg_qualstats, blocked on a self-hosted target to verify against.

That leaves this issue with what is actually deliverable:

detection and vocabulary — done (v89, corrected by PostgreSQL: two collectors report per-database facts with no database_name (pg_extension_availability, pg_column_stats) #2599)
pgstattuple → exact index bloat — done (v94, No index bloat estimate for PostgreSQL, where the remedy is the safe one #2561)
pg_buffercache → buffer residency — done (v93, PostgreSQL second-tier collection gaps: write-side, locks, buffers, replication lag #2544)
auto_explain → plan capture — tracked on PostgreSQL has no execution plan capture, which is where a DBM comparison is lost #2538 / Choose the PostgreSQL plan capture mechanism (auto_explain vs on-demand EXPLAIN vs extension) #2565
hypopg → hypothetical indexes — the one remaining buildable item, and available on the evaluation fleet

So this issue is now one item from closed.

Complete. The foundational ask — the detection and the vocabulary, not any single extension — shipped as v89, and every dependent collector this issue named has now landed.

item	status
detection + vocabulary	v89, corrected by #2599
`pgstattuple` → exact index bloat	v94 (#2561)
`pg_buffercache` → buffer residency	v93
`pg_wait_sampling` → wait attribution	v96 (#2609)
`pg_stat_kcache` → OS CPU and disk	v97 (#2610)
`pg_qualstats` → predicate statistics	v98 (#2611)
`auto_explain` → plan capture	#2538 / #2565
`hypopg` → hypothetical indexes	split out — see the new issue

HypoPG is split rather than dropped because it is not a collector: there is nothing to accumulate and read, it works by creating a hypothetical index and running EXPLAIN. That makes the product act rather than read, which is a posture decision rather than an implementation one.

Two corrections this issue accumulated, both worth keeping: pg_stat_kcache was listed as "Supported on Aurora (version-dependent)" and is absent; pg_wait_sampling's Aurora support was flagged as unconfirmed and is not offered. All three of the new collectors therefore ship dark on Aurora and light up for self-hosted targets — which is precisely the case this issue argued for.

Navigation Menu

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

The idea

The extensions that would earn their place

What to build first

Note on the fleet this is being evaluated against

The framing, better than the one in the issue body

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

What that means for scope

What that does to the pg_wait_sampling case

The honest limitation, stated plainly

Recommendation

Detection needs no helper function

THE TRAP: the two catalogs have different SCOPES

The vocabulary is four states, not installed/not-installed

Preload-only modules are a FIFTH case and need a different detector

On the fleet note

What this settles

The vocabulary working end to end

Still open here

Status: the foundational ask is delivered; here is the precise remainder

What is left, and why two items cannot be built responsibly right now

Metadata

Assignees

Labels

Projects

Milestone

Relationships

Development

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

Description

The idea

The extensions that would earn their place

What to build first

Note on the fleet this is being evaluated against

Activity

erikdarlingdata commented on Aug 22, 2026

The framing, better than the one in the issue body

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

What that means for scope

erikdarlingdata commented on Aug 22, 2026

erikdarlingdata commented on Aug 23, 2026

erikdarlingdata commented on Aug 24, 2026

What that does to the pg_wait_sampling case

The honest limitation, stated plainly

Recommendation

erikdarlingdata commented on Aug 25, 2026

Detection needs no helper function

THE TRAP: the two catalogs have different SCOPES

The vocabulary is four states, not installed/not-installed

Preload-only modules are a FIFTH case and need a different detector

On the fleet note

erikdarlingdata commented on Aug 25, 2026

What this settles

The vocabulary working end to end

Still open here

erikdarlingdata commented on Aug 25, 2026

erikdarlingdata commented on Aug 25, 2026

Status: the foundational ask is delivered; here is the precise remainder

What is left, and why two items cannot be built responsibly right now

erikdarlingdata commented on Aug 25, 2026

erikdarlingdata commented on Aug 25, 2026

Metadata

Metadata

Assignees

Labels

Projects

Milestone

Relationships

Development

Issue actions