Repository navigation
Store PostgreSQL plans: query_plan_dim is built for SQL Server plan XML, and PostgreSQL plans are JSON #2566
Description
Child of #2538, blocked on #2565 (mechanism).
The concrete question
Plan storage already exists, and it is shaped for SQL Server:
PayloadDimensions.QueryPlanDimTable=query_plan_dim, a dimension table so identical plans are stored once.plan_xml_compressionis a store-level config knob (gzipby default,nonefor direct-SQL consumers like Grafana), withPlanDimRecompressionto move an existing store between modes.plan_content_retention_daysages plan content out separately from the rows referencing it.
PostgreSQL plans are JSON, not XML (EXPLAIN (FORMAT JSON)), and auto_explain can emit text, JSON, XML or YAML. So the decision is whether query_plan_dim generalises to hold a second plan dialect or PostgreSQL gets its own dimension.
Why it is not obvious
Arguments for reusing the existing table: dedup, compression, recompression and retention are all built, tested and operator-configurable, and duplicating that is real work with real bugs. A plan is a blob of text keyed by a hash either way.
Arguments against: the column is named for XML and the config knob is plan_xml_compression, so reuse means either living with names that lie about half their contents or renaming a knob operators already set. Anything reading query_plan_dim directly — and the none compression mode exists specifically so people can — would start seeing a dialect it cannot parse.
That last point is the sharp one: the none mode is a documented contract with direct-SQL consumers, and silently introducing JSON rows into a table those consumers read as XML would break them without any code of ours changing.
Also worth settling here
- Deduplication key. SQL Server dedups on a plan hash. PostgreSQL has no equivalent identifier, so the key has to be derived — and a plan captured with timing differs textually from the same plan without it.
- Retention. Plans are large; whatever ships must sit under the existing retention machinery rather than beside it.
A naming trap for whoever picks this up
PgPlanFetcher.cs and PgDrillDownCollector.Plans.cs live in Darling.Analysis and are not PostgreSQL plan code. The Pg prefix there means the Postgres-backed (Darling) product, not a PostgreSQL target — PgPlanFetcher uses Microsoft.Data.SqlClient and sys.dm_exec_query_plan, and PgDrillDownCollector.Plans.cs uses Npgsql to read the store. Both handle SQL Server plans. I lost time to this while breaking #2538 down; expect to hit it in the first grep.
Activity
erikdarlingdata commentedon Aug 24, 2026
#2565 is answered, so this is unblocked — and its answer settles one of the two open questions here for free.
The mechanism chosen there is a collector running
EXPLAIN (GENERIC_PLAN, FORMAT JSON)over statements already inpg_stat_statements, becauseauto_explainwrites to the server log and a SQL connection cannot read it. Measurements are on #2565.Recommendation: a separate dimension, reusing the machinery not the table
The
nonemode is the deciding argument, and the code states it outright.PgMigrations.csline ~332 documents the alternate write path as:So
query_plan_dim.query_plan_xmlis a published contract with consumers outside this codebase who read it as XML. Writing PostgreSQL JSON into that column breaks every one of them without a line of our code changing on their side — and they would have no signal, because the column name and the knob name both still say XML.The other arguments line up the same way:
query_plan_xmland the knobplan_xml_compression. Reuse means either names that lie about half their contents, or renaming a setting operators have already set — and the rename is worse, because a store withplan_xml_compression = nonesilently reverting to a default on upgrade is a data-volume incident.PayloadDimensionsregisters the dimension per SQL Server collector (query_stats,procedure_stats→query_plan_xml). A PostgreSQL collector needs its own entry regardless, so a second dim table costs one more registration, not a new subsystem.What gets reused is the machinery, which is the expensive part:
DimTablesdrives compression,PlanDimRecompressionmoves a store between modes, andplan_content_retention_daysages content out. Apg_query_plan_dimregistered alongside inherits all three — the tested code is the compression/retention path, not the table.Concretely:
pg_query_plan_dim(query_plan_digest, plan_json text NULL, plan_json_gz bytea NULL, last_seen), registered inDimTables, sharing the retention knob.The dedup key question answers itself
This issue asks how to dedup when "a plan captured with timing differs textually from the same plan without it". With
GENERIC_PLANthere is no timing — it is a planner output, not an execution trace, so there is noActual Rows, noActual Total Time, and no run-to-run variance. The same statement, search_path and planner settings produce byte-identical JSON.So the key is a hash of the plan JSON, exactly as the SQL Server side hashes plan XML. That is only true because of what #2565 chose; it would not have been true for
auto_explainwithlog_analyze on, which is worth recording as a second-order benefit of that decision rather than luck.Naming trap confirmed
The issue's warning is correct and I hit it while checking this:
PgPlanFetcher.csusesMicrosoft.Data.SqlClientandsys.dm_exec_query_plan.Pgthere means the Postgres-backed product, not a PostgreSQL target. Worth keeping the new type namedPgTargetPlan...or similar so the next person does not inherit the ambiguity.erikdarlingdata commentedon Aug 24, 2026
Unblocked — #2565 is closed and the mechanism is
auto_explain, threshold-driven. And the measurement that settled it invalidates this issue's central assumption about deduplication.query_plan_dim's premise does not transferThis issue frames dedup as a solved capability worth reusing: "identical plans are stored once… a plan is a blob of text keyed by a hash either way." Measured on PostgreSQL 17 with
log_analyze=on— the setting we need, since estimates alone cannot answer a bad-estimate question — that is not true. Every capture is byte-unique, for two independent reasons.1. The instrumentation varies on every execution. The same query, same plan shape, run twice:
Nanosecond-level timings and per-node actual rows differ every single time. Two captures of an identical plan never hash the same.
2. The query text embeds literals.
auto_explainlogs the statement as submitted, not normalised:Same statement to
pg_stat_statements, same queryid, two different blobs.So a naive port would produce one dimension row per capture, which is not deduplication with poor ratios — it is a dimension table doing strictly more work than a plain column while claiming to do less. Whoever picks this up needs to know that before they measure a disappointing dedup ratio and start tuning it.
What the dedup key has to be
Derived from plan structure only: node types, relation and index names, join order, estimated costs and rows. Everything under
Actual *excluded, andQuery Textexcluded. That is a real design task, not a hash of the blob, and it should be settled before storage is written.The
none-compression contract answers the XML/JSON questionThis issue calls that the sharp point and it is.
plan_xml_compression = noneexists specifically so direct-SQL consumers can read plans, and they read them as XML. Silently introducing JSON rows breaks them with no code of ours changing — a contract break we would not see in any test we own.So: PostgreSQL plans do not go into
query_plan_dim. The alternative — a discriminator column plus renaming a knob operators have already set — spends a rename and a migration to arrive at a table whose name still lies, and it puts the burden on every existing direct consumer to start checking a column they have never read.The retention posture, which #2565 says must be decided here first
auto_explainputs literal values in the server log, andauto_explain.log_parameter_max_length = 0does not stop it — measured: that setting suppresses bind parameters (aPREPARE/EXECUTEwith a recognisable value does not appear) but has no effect on values inlined in the SQL text, because those are the statement rather than parameters. Any application that concatenates its SQL puts customer data in the log, and ingesting that log puts it in our store under our retention rather than the server's.Recommendation: store the plan and the queryid, and discard
Query Text. It solves both problems with one decision — the data-handling exposure goes away, and the thing that made dedup impossible goes away with it, since removingQuery Textis the first half of the structural key above. The statement text is already available, normalised and literal-free, frompg_stat_statementsvia the queryid — which #2584 now verifies is joinable, sinceauto_explainputs no query id in the plan and it has to come fromlog_line_prefixcarrying%Q.Sizing, since it bears on retention
Capturing every statement wrote 772 MB of server log in 20 seconds on a pgbench workload. That is not the configuration we will recommend — the mechanism decision is threshold-driven precisely because of it — but it sets the scale of what "plans are large" means here, and it is why retention needs to sit under the existing machinery rather than beside it, as this issue says.
Also worth carrying forward
The
PgPlanFetcher/PgDrillDownCollector.Plans.csnaming trap this issue documents is real and I hit it too. Worth restating in whatever file gets written: inDarling.Analysis,Pgmeans the Postgres-backed product, not a PostgreSQL target.erikdarlingdata commentedon Aug 25, 2026
Deciding this rather than leaving it open, because #2565 closed and the issue's own analysis names the deciding factor.
PostgreSQL plans get their own dimension table
Not
query_plan_dim. The sharp point above is the right one and it is decisive:A Grafana panel doing
SELECT plan_xml FROM query_plan_dimunderplan_xml_compression = noneis correct today and would start returning JSON with no schema change to notice, no error, and no version to key off. That is a silent break in someone else's dashboard, which is the worst failure shape available here — and it is not hypothetical,noneexists precisely so those consumers can read the column directly.The arguments for reuse are all about machinery, not about the table: dedup, compression, recompression and retention are built and operator-configurable. None of that requires sharing a table. A separate
pg_plan_dimreuses the same compression codec, the sameplan_content_retention_dayssweep and the same recompression path, whileplan_xml_compressionkeeps meaning exactly what it says.Renaming the knob is the other way out, and it is worse: it breaks the same consumers loudly instead of silently, for a cosmetic gain, and operators have it in their configs today.
The dedup key
Derived, and it must be computed from the plan after stripping timing and row counts. The issue is right that a plan captured with timing differs textually from the same plan without it — so hashing the raw text would defeat dedup exactly where it matters most, on the repeated plan. Hash the structural content only; store the observed variants against it.
Literals
Whatever ships stays consistent with what the product already does everywhere else: no raw customer values in the store. That is settled by precedent, not by this issue —
pg_session_statescarries no query text for exactly this reason, andpg_column_statsdeliberately dropsmost_common_valsandhistogram_bounds. Plan JSON carries constants in filter conditions, so this is a real constraint on the capture settings, not a formality.Scope
This decides the shape. It does not implement plan capture — that is #2538's epic and a much larger piece of work. #2567 (the MCP answering with a plan rather than a pointer) now has a concrete table to be written against.
Stop the panel rename from clobbering the readiness loader
Merge pull request #2613 from erikdarlingdata/feature/2566-pg-plan-ca…
erikdarlingdata commentedon Aug 25, 2026
Implemented in #2613, schema v99.
collect.pg_plan_capture— its own table, notquery_plan_dim, for the reason this issue itself named as the sharp one:plan_xml_compression = noneis a documented contract with direct-SQL consumers reading that column as XML, and JSON rows there would break their dashboards with no schema change to notice, no error, and no version to key off.The data-handling question this issue raised is answered by not storing the data.
auto_explainemitsQuery Textverbatim andlog_parameter_max_length = 0does not suppress it — literals also live inside the plan tree inFilterand its relatives. So the text is dropped outright (identity isquery_id, whose normalised text is already inpg_statement_stats) and every remaining string is redacted before storage. Verified against 25 real captured plans: zero literals survived.The dedup key is a hash of the redacted plan, so a shape recurs to the same hash whatever values it ran with — hashing raw text would have defeated dedup exactly where it matters most, which is the concern this issue raised about timing-bearing plans differing textually.
Retention sits under the existing machinery as asked: the table is a normal hypertable on the standard sweep rather than something beside it.
#2567 now has a concrete table to be written against. The remaining gap on #2538 is the ingestion path on managed PostgreSQL — this collector reads the server log directly, which works self-hosted and is impossible on Aurora/RDS; that is written up on #2538 with the measurements.
Name the pg_read_file grant pair on the self-hosted log readers' PERM…