PostgreSQL has no execution plan capture, which is where a DBM comparison is lost

[erikdarlingdata]

Part of the PostgreSQL parity effort (#2530). This is the largest single gap and probably wants breaking down further before anyone starts.

The gap

For SQL Server we have a whole plan family: analyze_query_plan, analyze_procedure_plan, analyze_query_store_plan, analyze_plan_xml, get_plan_xml, the graphical plan viewer, plan-cache bloat reads, plan corrections. For PostgreSQL we capture no plans at all — git grep for auto_explain, EXPLAIN (, pg_stat_plans across the collectors and service returns nothing, and docs/uat-onboarding.md already admits it: "no plan capture or blocking-chain read" for pg targets.

Datadog DBM captures explain plans for Postgres via sampling, and plans are the reason people open a database monitoring tool at all. Someone evaluating us against DBM on Aurora reaches this within the first hour.

What already exists to build on

PgStatementStatsCollector already collects pg_stat_statements (and aurora_stat_statements() on Aurora), so the query side is there — queryid, calls, timing. What is missing is the plan for a given queryid.

The mechanisms, which need a decision

• auto_explain — logs plans for statements over a duration threshold. Needs a shared-library preload and carries a per-statement cost; on RDS/Aurora it is a parameter-group change, not something the monitoring tool can switch on for itself.
• pg_stat_plans / similar extensions — record plans alongside statements, but extension availability on managed Postgres is the constraint.
• On-demand EXPLAIN — the tool runs EXPLAIN for a query it has already seen. No preload needed, but the plan is now, not the plan from when it was slow, and parameters have to come from somewhere.

The third is the only one that works with no server-side configuration, and it is also the weakest evidence. That trade is the substance of this issue.

Constraints worth naming up front

• Aurora and RDS restrict shared_preload_libraries to parameter groups and a restart, so anything requiring a preload is a customer action we can detect and advise on, not something we can arrange.
• Plan capture on a busy server is expensive; whatever ships needs the same sampling discipline the SQL Server side already applies.
• The MCP surface should be able to answer with a plan, not just a link to one, since an agent consuming this has no viewer.

Related

Column statistics (#filed separately) is the companion: a plan explains what the planner did, pg_stats explains why it thought that was a good idea. Neither is much use alone for a bad-estimate problem.

Sources consulted: PostgreSQL Wiki — Monitoring, Datadog — Advanced Configuration for Postgres DBM.

[erikdarlingdata]

Correction to this issue's constraints: auto_explain IS available on Aurora and RDS.

I wrote that a preload "is a customer action we can detect and advise on, not something we can arrange", implying it was largely out of reach on managed PostgreSQL. That understates it. Confirmed:

• Aurora's default DB cluster parameter group preloads only pg_stat_statements, but auto_explain can be added via a custom cluster parameter group plus a writer reboot, and AWS documents it explicitly as the way to log execution plans.
• pganalyze's own RDS setup path recommends exactly this: auto_explain as the automated-EXPLAIN mechanism on RDS and Aurora.

So plan capture is not blocked on managed PostgreSQL. It is a one-time configuration step with a reboot — significant for a customer, but a documented, supported path rather than a wall.

That changes the ranking of the three mechanisms in the issue body. auto_explain moves from "realistically on-prem only" to "available everywhere, gated on a customer action we can detect and walk them through". On-demand EXPLAIN drops to the fallback it should have been: no setup, but the plan is from now rather than from when the query was slow.

The work this implies is detection and guidance, not just collection — which is the same shape as #2545 (extension-aware collection). Whoever takes this should read that first: a read that needs auto_explain and does not have it should say so, name it, and say how to turn it on, in the same vocabulary the engine-kind and engine-edition axes already use.

Sources: AWS — Working with extensions on Aurora PostgreSQL, pganalyze — Check for auto_explain on Amazon RDS.

[erikdarlingdata]

Broken down as this issue asked for. #2538 stays open as the parent; the work is now startable as four children:

#2564	Detect whether plans can be captured, and say so	Ready now — no dependency
#2565	Choose the capture mechanism	Decision; blocks the two below
#2566	Store PostgreSQL plans	Blocked on #2565
#2567	Read PostgreSQL plans	Blocked on #2566

#2564 is the one to start with. It ships value before the mechanism argument is settled: whether auto_explain is loaded, and whether it is actually capturing (log_min_duration = -1 is loaded-but-silent and looks identical to absent from outside), are both readable today with no configuration change. The failure it fixes is silence — someone comparing us to DBM on Aurora finds no plans and cannot tell whether we do not support them, their server is not set up for them, or something is broken.

Two things have changed since this issue was written, and both change the options:

1. On-demand EXPLAIN is weaker than it sounds. The MCP surface is strictly read-only over collected rows with no ad-hoc path to a monitored server — established on No bloat measurement for PostgreSQL: we collect the whole cause chain and none of the damage #2542, where "run pgstattuple on demand" turned out not to be implementable as a tool. Collectors can run SQL at a target, so this option really means "a collector captures on a schedule", not "a tool explains when asked". It should be judged as that.
2. Installing objects into a monitored database is now permitted (Erik's call on Collect PostgreSQL column statistics (pg_stats), the companion to plan capture #2543), which puts the extension/helper route back on the table with the guardrails settled there.

One correction to this issue: its gap claim is right — git grep for auto_explain, EXPLAIN ( and pg_stat_plans still returns nothing outside tests. But be warned that PgPlanFetcher.cs and PgDrillDownCollector.Plans.cs in Darling.Analysis are not PostgreSQL plan code. The Pg prefix there means the Postgres-backed product, not a PostgreSQL target: the first uses Microsoft.Data.SqlClient and sys.dm_exec_query_plan, the second uses Npgsql to read the store. Both are SQL Server plans. I lost time to that while breaking this down and it will be the first thing anyone greps.

Storage is less of a blank slate than the issue implies: query_plan_dim already does dedup, gzip/none compression, recompression and separate content retention — all built for plan XML, where PostgreSQL plans are JSON. #2566 covers whether that generalises, and the sharp edge is that the none compression mode exists specifically so direct-SQL consumers can read the table, so quietly adding a second dialect would break them with no code change of ours.

[erikdarlingdata]

Measured the mechanism trade instead of reasoning about it — against a real Aurora fleet (52 clusters, 51 parameter groups) and against PostgreSQL 17 in a container. Five findings, and two of them are traps a straightforward implementation walks into.

1. auto_explain is NOT a platform limitation on Aurora

It is in the allowed shared_preload_libraries values on both engine versions in the fleet:

allowed (13): auto_explain, orafce, pg_ad_mapping, pg_bigm, pg_cron, pg_hint_plan,
              pg_prewarm, pg_similarity, pg_stat_statements, pg_tle, pgaudit,
              pglogical, plprofiler

Two consequences. pg_stat_plans is not on that list, so the issue's second mechanism is dead on managed Aurora and does not need further evaluation. And the first mechanism is a customer configuration action, not something the platform forbids — which is a much better position than the issue assumes.

2. It is loaded on exactly zero clusters — but the cost is one reboot, ever

Swept all 51 distinct cluster parameter groups. Every one is shared_preload_libraries = pg_stat_statements, with nothing else. Fleet is 26 clusters on 16.11 and 26 on 17.7.

The interesting part is what happens after the preload. All 14 auto_explain.* parameters are already present in the parameter group and every one is dynamic and modifiable — log_min_duration, log_format, log_analyze, log_buffers, log_timing, sample_rate, the lot. So the real cost is a single static change plus one writer reboot per cluster, after which every tuning knob moves live.

That directly answers the issue's sampling concern: auto_explain.sample_rate is native, server-side, and dynamically tunable. We do not need to build sampling discipline for this; we need to set it and report what it is set to.

Also worth knowing: compute_query_id is auto fleet-wide, which with pg_stat_statements loaded means query ids are being computed already. That matters for finding 4.

3. TRAP — the query id is not in the plan

auto_explain with log_format=json emits no Query Identifier field at all, even with compute_query_id=on. I checked the full JSON body on PostgreSQL 17. What it emits is "Query Text" and "Plan".

The join key comes from somewhere else entirely — log_line_prefix:

log_line_prefix = 'QID=%Q '
  ...auto_explain line -> QID=-4828029293864693941
  pg_stat_statements  -> queryid = -4828029293864693941

Exact match. So plans can be joined back to PgStatementStatsCollector's queryid, which is the thing that makes this feature worth building — but only if log_line_prefix carries %Q. An implementation that enables auto_explain and starts parsing plans without also requiring %Q will capture plans it cannot attribute to anything, and the failure looks like the feature working.

%Q is not in the fleet's log_line_prefix today. Whatever readiness check ships needs to check it, not just the library.

4. TRAP — literal values land in the server log, and the obvious mitigation is only half a mitigation

auto_explain logs the query text as submitted. Measured:

"Query Text": "SELECT count(*) FROM probe WHERE v LIKE 'a%';"

auto_explain.log_parameter_max_length = 0 does suppress bind parameters — I ran a PREPARE/EXECUTE with a recognisable value at that setting and it does not appear in the log. But it does nothing about values inlined in the SQL text, because those are not parameters, they are the statement.

So an application that concatenates its SQL puts its literals into the server log, and a plan-capture feature that ingests that log puts them into our store, where they persist under our retention rather than the server's. That is a materially different data-handling posture from everything we collect today, and it should be a decision made deliberately rather than discovered later. It is also an argument for capturing the plan while discarding or truncating Query Text and keeping the queryid as the identity.

5. The read path on Aurora exists without a database connection

rds:DescribeDBLogFiles returns 74 rotated files on the instance I sampled, and rds.log_retention_period is 4320 minutes = 3 days, with log_destination = stderr and logging_collector = 1. So on Aurora the plans are retrievable through the RDS API rather than over SQL — an IAM permission rather than a database grant, and a different collection shape from every other PostgreSQL collector we have.

Three days of retention also bounds the backfill: anything not collected inside that window is gone.

One more trap, on measurement itself

auto_explain.hashes is listed in the Aurora 17.7 parameter group. Community PostgreSQL 17 answers ERROR: unrecognized configuration parameter "auto_explain.hashes". The RDS parameter-group listing is not a reliable statement of what the running server will accept, so a readiness check must ask the server (current_setting(..., true)) rather than trust the parameter group — which is what #2564 already does, and now there is a concrete reason it had to.

Where that leaves the decision

Mechanism 1 (auto_explain) is viable on Aurora, cheaper than assumed after the one-time reboot, and comes with native sampling. Mechanism 2 (pg_stat_plans) is not available. Mechanism 3 (on-demand EXPLAIN) remains the only zero-configuration option and remains the weakest evidence, but it is now clearly the fallback rather than the contender.

Suggested split, given #2564 shipped the readiness detection:

• extend the readiness collector to check log_line_prefix for %Q — small, and finding 3 says the feature is silently broken without it
• decide the query-text retention posture from finding 4 before any ingestion is written
• the log retrieval collector (RDS API path), which is where Store PostgreSQL plans: query_plan_dim is built for SQL Server plan XML, and PostgreSQL plans are JSON #2566/Read PostgreSQL plans: the MCP must answer with a plan, not a pointer to one #2567 actually live
• the plan parse and the queryid join

Related: the version-gating work on #2544 turned up that PostgreSQL 18 removes columns from pg_stat_wal, so the 16/17 split in this fleet will not stay a two-way branch.