query_plan_dim is 63% of the Darling store and never shrinks — and query_store_stats compresses at 2.7x against siblings' 10-30x

[erikdarlingdata]

Split out of #2295, which fixed the growth mechanism (retention armed 17/17, #2150 text flip). What remains is the store's standing composition, measured on the use2 box (42-server fleet, store 196 GB on 2026-08-17):

Finding 1: query_plan_dim, 121 GB, 63% of the store

query_plan_dim is a plain dimension table, not a hypertable — so it sits outside both the compression policies and the retention horizons that now govern everything else. Plan XML accumulates forever, uncompressed beyond default TOAST pglz. The top-8 hypertables together are ~42 GB; this one table is ~3x all of them combined.

Levers worth costing, separately or together:

• Orphan pruning: a plan whose every referencing query_store_stats row has aged past the 90-day horizon is unreachable by any read path — a periodic anti-join delete is the retention the table never had. Needs the same care as the [BUG] planwm: collector_state rows are never pruned for dropped databases #2188/[BUG] planwm/backfill state pruning does not reach Azure SQL DB #2191 state prunes (authoritative reachability, not a heuristic).
• Storage-level compression: plan XML is the ideal zstd case (Adopt zstd TOAST compression for the store when PostgreSQL ships it #2071 tracks the PostgreSQL-ships-it half; lz4 TOAST is available today and is a one-ALTER experiment).
• Dedup check: worth measuring how much of the 121 GB is near-identical XML across servers of the same application (this fleet is 42 copies of one product) — a content-hash sample answers it read-only.

Finding 2: query_store_stats compresses 2.7x; its siblings do 10-30x

The biggest hypertable (24 GB) gets the worst compression ratio in the store by an order of magnitude. Suspects: segmentby/orderby misfit for its write pattern (wide rows, per-interval snapshots, high-cardinality query_id), or the dedup-read-side design (rn=1 over repeated open-interval snapshots) storing near-duplicate rows the compressor can't fold because segment boundaries split them. Note #2312/#2314 just cut open-interval re-snapshots by ~two thirds, which shrinks the inflow of near-duplicates — re-measure the ratio after a week of the new write pattern before touching compression settings.

Non-goal

Growth-rate emergency response — that was #2295's job and it's done (~2.8 GB/day at last measure, ~90-day runway, horizons armed).

[erikdarlingdata]

Measured Finding 1 on the use2 box (read-only psql, 2026-08-18), and the numbers change the framing: the non-goal is wrong — this is a growth emergency with a date on it.

What the dim actually holds

query_plan_dim   127 GB total  (heap 1.7 GB, TOAST+idx 125 GB)
rows             12.2M — every one gzipped (query_plan_gz), ZERO legacy text rows
avg gz payload   9,750 bytes
oldest last_seen 2026-07-27   n_tup_del = 0   gc_eligible today = 0

Three levers from the issue body, resolved by measurement:

• Orphan pruning already exists (the query_text/query_plan_xml stored inline per row: 94% of a field store — normalize into hash-keyed dimension tables (~135x measured) #1767/Bound the dimension GC by the oldest surviving digest-carrying fact, not an assumed horizon #1795 last_seen GC in DarlingRetention) and is healthy — it just hasn't fired yet, and can't for a while (below).
• Storage compression is spent: every row is already app-gzipped; lz4 TOAST on top of gzip bytes is nothing. (Adopt zstd TOAST compression for the store when PostgreSQL ships it #2071's zstd would re-open this.)
• Dedup is the story — see the cardinality numbers.

The date

The dim was born with 3.3.0 (oldest last_seen = July 27). Its GC cutoff is coupled to the widest dim-feeding fact retention (90d) + margin ≈ 92 days, so the first GC delete cannot happen before ~Oct 27. Meanwhile: 12.2M rows / 22 days ≈ 555k new digests/day ≈ ~6 GB/day on disk, linear. Disk is 500 GB with 249.8 GB free; store is 197 GB. At this rate the disk fills ~late September — a month before the GC's first delete. Retention being armed (#2295) did not bound this table in time; its bound engages too late by construction on any store younger than its horizon.

Where the digests come from (the dedup measurement)

Last 24h of query_stats:

distinct query_plan_digest   344,468
distinct query_plan_hash       5,327     → 65 digests per plan SHAPE
digests per shape            p50 = 3, p95 = 192, max = 57,402

One plan shape produced 57,402 distinct XMLs in one day (~560 MB gzipped, for one query). This is parameter-sniffing recompile churn — the #2235 pathology — not workload diversity. Content-digest dedup is working exactly as designed; the content itself is near-duplicate at a level the design's exact-byte key cannot see, and by the #1767 reasoning (param values are diagnostic) should not collapse.

Decision

Ship a dedicated plan-content horizon, decoupled from fact retention: dim GC cutoff becomes min(existing fact-coupled cutoff, now − (plan content horizon + margin)), default 21 days, configurable. Facts keep their 90 days — metrics, hashes and text stay analyzable; only stored plan XML older than ~3 weeks becomes unfetchable, and every reader already handles a missing plan. At current churn that caps the dim near ~140 GB and starts reclaiming within days instead of late October. The 40-day runway is enough to land this properly through review — no manual surgery on the store.

Explicitly NOT doing: shape-keyed latest-wins storage (it would shrink this 65× but breaks the historical-fact → exact-XML contract that #1767 deliberately preserved — param-variant XML is this product's diagnostic bread and butter). If churn outgrows the horizon lever, that trade gets its own issue with these numbers as the opening exhibit.

Finding 2 stays parked per this issue's own instruction — re-measure the qss ratio ~Aug 24 after a week of #2312/#2314's write pattern.

PR incoming.

[erikdarlingdata]

Finding 1 status: the decision above shipped as V75 (#2326, merged) — plan_content_retention_days, default 21, scoped to the plan dimension only, with the Query Store plan map's prune taught the same horizon so the dimension-outlives-the-map invariant holds under every knob value (both review catches, pinned). The nightly carrying it is installing on the use2 box now; expect the first purge to start deleting query_plan_dim rows immediately (the box's oldest last_seen is Jul 27 ≈ 22 days — just past the 21+1 horizon) and disk-free to turn around within days. I'll post before/after numbers here once a few sweeps have run.

Finding 2 stays on its own clock per this issue's instruction: re-measure the query_store_stats compression ratio on or after Aug 24, giving #2312/#2314's write pattern its full week.

[erikdarlingdata]

V75 verified live on the use2 box (nightly 3.4.0-nightly.20260818, 18:13 build, installed 18:16, EBS snapshot snap-0bd29d560474a6211 taken first):

18:23:07 Postgres store ready (schema v75, 1 migration(s) applied)
config_service.plan_content_retention_days = 21
query_plan_dim: 12,262,487 rows / 128 GB, oldest last_seen 2026-07-27 21:36

One precision on the timeline I gave earlier: the oldest last_seen is ~3 hours NEWER than today's 22-day cutoff, so the first deletions land with tomorrow's daily purge, not tonight's — and because the dim is exactly at its new steady-state age (22 days old, 22-day horizon), the outcome is that it stops growing at ~130 GB rather than shrinking: the horizon now deletes each day's aging edge as inflow adds the new one. That is the fix working as designed — the counterfactual was linear growth to ~500 GB with the first GC delete in late October. Expect the daily purge log to start reporting query_plan_dim deletions from tomorrow and disk-free to flatten at ~245-250 GB. I'll drop one more datapoint here after a couple of purges to confirm the flatline, alongside the Aug 24 Finding-2 re-measure.

[erikdarlingdata]

Measurement caveat for the Finding 2 re-measure (due Aug 24): the new query_store_health collector (#2319, installed today) found Query Store stuck READ_ONLY / readonly_reason 8 fleet-wide on the use2 monitored servers, and query_store_stats intake dropped to zero on 2026-08-17 17:36 UTC — from 2.8-8M rows/day prior. The collector logs SUCCESS every cycle; the engine is just producing nothing new. So the compression-ratio re-measure will be over a week that includes several days of zero new query_store_stats writes — the chunk-compression behavior it measures will reflect that, not the #2312/#2314 write pattern alone. Either extend the window once QS is remediated fleet-side, or read the re-measure against per-day intake.

[erikdarlingdata]

Overnight measurement correction: V75's plan-content horizon is live but cannot delete anything yet, and the mechanism is #2312 Finding 4. query_plan_dim kept growing right through the soak — 131.5 GB (Aug 17) → 138.5 (Aug 18) → 140.5 (Aug 19, 06:20 UTC), rows 11.77M → 12.54M — because the perpetual plan-fetch catalog walk re-upserts every plan daily, refreshing last_seen across the whole catalog, so nothing is ever '21 days since last sighting.' The knob is correct; the walk defeats it. It starts actually deleting the moment #2333 (the activity-driven fetch) deploys and the artificial liveness stops. Until then, budget ~2–7 GB/day of continued dim growth on this store. The earlier 'stabilizes ~130GB' read was a short-window artifact.

[erikdarlingdata]

Correction to my Aug 24 re-measure caveat: the 'query_store_stats intake dropped to zero' framing was wrong — that was the #2296 cutover (primaries moved to the use1 box), not an engine event. Implication for THIS issue: the use2 store's query_store_stats table is permanently quiet now (its 42 targets are readable replicas, which produce no QS runtime rows), so Finding 2's compression re-measure belongs on the use1 store, which is taking the full 17.4M rows/day primary write pattern into a fresh v74-schema store. The V75-defeated-by-the-walk mechanism from my earlier comment stands unchanged — #2333 just merged, so the use2 plan dim should start actually shrinking within a day of the install, and that drop is now itself a measurable prediction: watch for the first purge log naming query_plan_dim.