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

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.

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.

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:

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.
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.

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.

Merge pull request #2584 from erikdarlingdata/feature/2538-plan-attri…

The mechanism was settled on #2565 (auto_explain, threshold-driven) and the storage shape on #2566. The ingestion path was never decided, and it is the actual blocker. Measured today rather than reasoned about.

`auto_explain` works exactly as designed

With log_format=json and %Q in log_line_prefix, plans land in the server log stamped with the query id that joins pg_stat_statements:

2026-08-25 14:50:50.595 UTC [138] -8126435036642491494
  "Node Type": "Seq Scan"

That half is confirmed and matches what pg_plan_capture_readiness (#2564) already checks for.

Getting them OUT of the log is the problem

auto_explain writes to the server log. It has no view, no function, no table. So the product has to read a file, and here is what that costs:

grant	can list log dir	can read log
`pg_monitor`	yes	no — `permission denied for function pg_read_file`
`pg_monitor` + `pg_read_server_files`	yes	still no
+ explicit `GRANT EXECUTE ON FUNCTION pg_read_file`	yes	yes — 13,936 bytes read

The middle row is the surprise. pg_read_file's ACL is postgres=X/postgres — EXECUTE belongs to the superuser alone and is not carried by the pg_read_server_files role. Membership in that role is necessary and not sufficient; a superuser must additionally grant EXECUTE on the function.

And that grant is not narrow. The same role could then read anything the postgres OS user can:

pg_hba.conf readable: true
postgresql.conf readable: true

That is a monitoring login that can read the host's authentication configuration. It is a materially bigger ask than pg_monitor, and bigger than the pg_read_all_data conversation on #2543 — that one widens access to data, this one widens it to the filesystem.

On Aurora and RDS the route does not exist at all

There is no filesystem access and pg_read_server_files is not grantable. Logs come from the RDS API (DownloadDBLogFilePortion), which needs AWS credentials, not a database connection.

That is not a permission change, it is a different integration: Darling connects to databases. Reading Aurora logs means per-target AWS credentials, an IAM policy, and a code path with no database connection in it — for the fleet this product is being evaluated against, which is entirely Aurora.

So the epic has three routes and all three cost something

pg_read_file + explicit EXECUTE — works, self-hosted only, and hands the monitoring login the server's filesystem.
RDS/Aurora log API — the only route on managed PostgreSQL, and a new integration surface with credentials the product does not currently hold.
Ship the log elsewhere — a log shipper or log_destination=csvlog into a table the customer maintains. Least invasive for us, most setup for them, and it is not really our path so much as a thing we could consume.

I am not choosing between these. Each one changes what the product asks of a customer, and #2566's storage shape is downstream of the answer — a pg_plan_dim with no producer would be a table that rots.

What I would do first: confirm whether route 2 is acceptable in principle, because it is the only one that works on the fleet in front of us, and it is the one whose cost is engineering rather than privilege. If it is not, this epic is a self-hosted-only feature and that should be said out loud on the issue rather than discovered later.

Recording it here so the next person does not re-derive it: the readiness collector already reports every precondition except this one, and that is because this one cannot be seen from a database connection.

Erik's call: build the RDS log API path.

So plan capture is not a self-hosted-only feature. Route 1 (pg_read_file) is shipped and works where there is a filesystem; this adds the route that works on managed PostgreSQL, which is the fleet in front of us.

What that means concretely, recorded before the work starts:

DownloadDBLogFilePortion, which is an AWS API call and not a database one. That is the part with no precedent in this codebase: every collector today reaches its target through a connection string, and this path has no database connection in it at all.
Credentials per target. An IAM policy scoped to log download on specific instances, and somewhere for the service to hold them. Deciding where is part of the work, and the constraint from the SQL Server side applies — never as an SSM parameter, because those land in CloudTrail.
The parsing is already done. The collector shipped in Capture PostgreSQL execution plans, without capturing customer data (#2566) #2613 extracts plans, redacts literals and hashes shapes from a log body; it does not care whether that text arrived from pg_read_file or an API. So this is an ingestion source behind an existing collector rather than a second collector.
A watermark, which route 1 does not have. DownloadDBLogFilePortion returns a marker for the next read, so the API route can be genuinely incremental where the file route reads a bounded tail and tolerates overlap. That is a better shape and worth taking rather than mirroring the tail read.

Build the log fixture with real tabs, not a raw string literal

Commit the lock file the AWS SDK reference changed

Merge pull request #2620 from erikdarlingdata/feature/2538-rds-log-so…

Choose the plan route at dispatch, not by gating the collector

Clear the doc block and test my own revert stranded

Merge pull request #2621 from erikdarlingdata/feature/2538-rds-plan-i…

Status after deploying the RDS log-API route to the PostgreSQL monitoring host today.

The code path works. On the Aurora target the collector correctly takes the RDS API route rather than pg_read_file:

pg_plan_capture | SUCCESS | rows=0 | no new auto_explain plans in the RDS log window

That row is also what surfaced #2633 — it was reported as a successful empty read while the AWS call was being denied. Fixed in #2634, along with a second defect: IsAwsRds was never set on a PostgreSQL target, so plain RDS PostgreSQL could never have routed correctly. Aurora hid it, because IsAurora carried the dispatch.

What is left is not code. Capturing a plan end to end on the fleet needs five changes on the target and the monitoring host, none of which the product can make for itself:

what	where	note
`auto_explain` in `shared_preload_libraries`	cluster parameter group	currently `pg_stat_statements` only. static — needs a reboot
`auto_explain.log_min_duration`	cluster parameter group	unset. dynamic
`auto_explain.log_format = json`	cluster parameter group	unset. dynamic
`%Q` in `log_line_prefix`	cluster parameter group	currently `%t:%r:%u@%d:[%p]:` — no `%Q`, so plans would carry no query id to join on. dynamic
`rds:DescribeDBLogFiles` + `rds:DownloadDBLogFilePortion`	monitoring host's instance role	the role has SSM core and one secret read

pg_plan_capture_readiness (#2564) already checks four of the five and explains each in its own words — it is reporting 5 rows a cycle on this target right now, which is the slice doing exactly what it was built for.

The reboot is the only one that is not a live parameter change. Everything else is a parameter-group edit and an IAM policy addition.

The IAM half, ready to apply. I tried to add this and my tooling blocks IAM writes, so it needs your hand — it is one command.

The monitoring host's instance role currently carries a single inline policy scoped to one secret. This adds a second in the same shape: two READ-ONLY actions, scoped to the one target instance.

cat > /tmp/rds-log-read.json <<'JSON'
{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Sid": "ReadPostgresServerLogForPlanCapture",
      "Effect": "Allow",
      "Action": [ "rds:DescribeDBLogFiles", "rds:DownloadDBLogFilePortion" ],
      "Resource": "arn:aws:rds:us-east-1:<account>:db:<the-cluster-writer-instance>"
    }
  ]
}
JSON

aws iam put-role-policy --role-name <monitor-instance-role> \
  --policy-name pm-rds-log-read-plan-capture \
  --policy-document file:///tmp/rds-log-read.json --profile <profile>

Reversible with aws iam delete-role-policy --policy-name pm-rds-log-read-plan-capture. The role is used by the two monitoring hosts and nothing else.

What it changes on its own: the collector currently reports PERMISSIONS naming exactly this grant (correct, since #2634). With it, the read succeeds and reports "no new auto_explain plans" — which will then be true, because auto_explain still is not loaded. So this does not produce plans; it proves the RDS path works end to end against a real cluster and removes a standing false PERMISSIONS.

Still outstanding, and not mine to do — all four are parameter-group changes on the target cluster:

what	current	note
`shared_preload_libraries` += `auto_explain`	`pg_stat_statements` only	static — needs a cluster reboot
`auto_explain.log_min_duration`	unset	dynamic
`auto_explain.log_format = json`	unset	dynamic
`log_line_prefix` += `%Q`	`%t:%r:%u@%d:[%p]:`	dynamic; without `%Q` plans carry no query id to join on

pg_plan_capture_readiness (#2564) already reports four of these five in its own words, on every cycle, which is that slice doing exactly what it was built for.

The Aurora half is closed as unreachable, not deferred. Erik cannot make the parameter-group changes either, so auto_explain in shared_preload_libraries — a static parameter needing a cluster reboot — is not happening on this fleet. Everything downstream of it follows.

That settles the IAM grant too: not worth applying. On its own it only changes the message from a correct PERMISSIONS naming the grant to a correct "no new plans" — because there would still be no plans to find. The current row is accurate and self-explaining, which is the right resting state. Skipping it.

What that leaves, and it is not nothing

The self-hosted route is proven end to end: a PostgreSQL container with auto_explain loaded, log_format=json and %Q in log_line_prefix produced 20 real captured plans through pg_read_file, stored and readable through get_pg_plans.

The managed route's parse is now pinned against real bytes (#2649). The one thing that cannot be tested without a managed target is the AWS transport itself — DescribeDBLogFiles / DownloadDBLogFilePortion — and that is already covered by the faked-client tests for cluster-to-writer resolution, newest-log selection, the first-read bound and marker resume.

So the gap is exactly: nobody has watched a plan travel from an Aurora log blob into the store. Everything either side of that hop is tested, and the hop itself is two AWS calls whose failure modes now surface as PERMISSIONS with the grant named (#2634) rather than as a false success.

If a managed target ever becomes available

The five prerequisites, in order, all on the target cluster except the last:

shared_preload_libraries += auto_explain — static, needs a reboot
auto_explain.log_min_duration — dynamic
auto_explain.log_format = json — dynamic
log_line_prefix += %Q — dynamic; without it plans carry no query id and join to nothing
rds:DescribeDBLogFiles + rds:DownloadDBLogFilePortion on the monitoring host's role

pg_plan_capture_readiness (#2564) reports four of the five in its own words every cycle, so the moment someone does have such a target it will say which of them are outstanding rather than requiring this list.

Closing — the premise no longer holds. PostgreSQL execution-plan capture exists and shipped in 3.6.0. All four children are done:

Detect whether PostgreSQL plans can be captured at all, and say so — the shippable first slice of #2538 #2564 — detect whether plans can be captured, and say so
Choose the PostgreSQL plan capture mechanism (auto_explain vs on-demand EXPLAIN vs extension) #2565 — mechanism chosen (auto_explain, threshold-driven)
Store PostgreSQL plans: query_plan_dim is built for SQL Server plan XML, and PostgreSQL plans are JSON #2566 — storage (pg_plan_capture, its own table)
Read PostgreSQL plans: the MCP must answer with a plan, not a pointer to one #2567 — read the plan itself, not a pointer (get_pg_plans)

Proven end to end on a self-hosted target: auto_explain loaded, log_format=json, %Q in log_line_prefix produced real captured plans through pg_read_file, stored and readable. The RDS log-API transport is built, its parse pinned against real captured bytes, and its AWS calls covered by faked-client tests.

What is NOT done is a product gap — it is a fleet one. Demonstrating the last hop, a plan travelling out of a live Aurora log blob, needs auto_explain in shared_preload_libraries — a static parameter requiring a cluster reboot that is not available on this fleet. That blocks a demonstration, not the capability. pg_plan_capture_readiness reports the outstanding prerequisites on any managed target that has them, so a customer who can make that parameter-group change gets told exactly what to set.

Reopen if the capability itself proves wrong on a suitable target; the fleet-demonstration gap is tracked in memory, not as an open work item.

pg_deadlocks: wire the missing Aurora/RDS dispatch branch (100% fleet…

Add Postgres/Aurora instance CPU collector via Performance Insights, …

Navigation Menu

Repository navigation

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

The gap

What already exists to build on

The mechanisms, which need a decision

Constraints worth naming up front

Related

1. `auto_explain` is NOT a platform limitation on Aurora

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

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

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

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

One more trap, on measurement itself

Where that leaves the decision

`auto_explain` works exactly as designed

Getting them OUT of the log is the problem

On Aurora and RDS the route does not exist at all

So the epic has three routes and all three cost something

What that leaves, and it is not nothing

If a managed target ever becomes available

Metadata

Assignees

Labels

Projects

Milestone

Relationships

Development

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

Description

The gap

What already exists to build on

The mechanisms, which need a decision

Constraints worth naming up front

Related

Activity

erikdarlingdata commented on Aug 22, 2026

erikdarlingdata commented on Aug 23, 2026

erikdarlingdata commented on Aug 24, 2026

1. auto_explain is NOT a platform limitation on Aurora

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

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

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

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

One more trap, on measurement itself

Where that leaves the decision

erikdarlingdata commented on Aug 25, 2026

auto_explain works exactly as designed

Getting them OUT of the log is the problem

On Aurora and RDS the route does not exist at all

So the epic has three routes and all three cost something

erikdarlingdata commented on Aug 25, 2026

erikdarlingdata commented on Aug 26, 2026

erikdarlingdata commented on Aug 26, 2026

erikdarlingdata commented on Aug 26, 2026

What that leaves, and it is not nothing

If a managed target ever becomes available

erikdarlingdata commented on Aug 27, 2026

Metadata

Metadata

Assignees

Labels

Projects

Milestone

Relationships

Development

Issue actions

1. `auto_explain` is NOT a platform limitation on Aurora

`auto_explain` works exactly as designed