Repository navigation
query_plan_dim's TOAST file sits at 40 % utilisation on the second SQL Server store (~93 GB slack from the V54 conversion + cycling deletes) — publish dimension-table TOAST utilisation in the store self-metrics; reclaim is --recompress-plan-dim --vacuum-full at the maintainer's word #3783
Description
query_plan_dim is 157 GB on the second SQL Server store class — 2.3× its near-twin — and is that store's single largest object
Measured 2026-09-20 13:4xZ (store-size pass): the plan dimension is 157 GB on the second SQL Server store (42 servers) against 67 GB on the largest (43 servers). It is 69 % of that store's collect schema (store total 229 GiB) and the product's second-largest object class overall. Same product, same build, near-twin fleets; the asymmetry is the finding. For scale: the largest store just proved the compression path elsewhere (415 → 359 GiB in two days as the staged aggregate lifts ran), and oversized_plan_backlog on the same second store is 2.4 GB.
What is VERIFIED at source (dev, 2026-09-20)
query_plan_dimis a plain table, never a hypertable (DarlingRetention.cs:911), keyed by the plan XML's digest — one row per distinct plan text — with alast_seenGC watermark the write path stamps on every cycle that references the digest (PayloadDimensions.cs:85–96).- Plans are already gzipped: V54 (Gzip the plan-XML dimension content at the application level — measured 14.0x vs lz4's 8.9x on live content #2069) added
query_plan_gz byteaand madequery_plan_xmlnullable; the write path stores gz-only rows (~9.5 KB average,DarlingRetention.cs:~897). So "compress the XML ~10×" is not the lever — it has been applied since V54 to every row written since. Pre-V54 TEXT rows convert only by GC turnover, and a STABLE plan's text row never turns over;--recompress-plan-dim(Operator verb: --recompress-plan-dim — convert pre-V54 plan-dim text rows to gzip #2076,PlanDimRecompression.cs) is the operator-paced verb that reaches that tail, with--dry-runas a survey. - The second store was already the heavy one when Tightening plan_content_retention_days permanently stops retention on query_plan_dim (one-day slice exceeds the 300s delete timeout) #2386 was measured on it: 133 GB over 12.4 M rows, ~755 k new dimension rows a day (~23 MB/h of distinct plans), purge running at ~1,000 rows/s, row-capped at 50 k per statement because a one-day time slice (~7 GB of TOAST) cannot delete inside the 300 s command timeout (
DarlingRetention.cs:884–911). It has grown 24 GB since. - Retention for the dimension is
planContentRetentionDays, computed against the widest fact horizon (DarlingRetention.cs:475).
HYPOTHESES to discriminate — three read-only store reads, no lane until they land
- Text-row tail.
--recompress-plan-dim --dry-runon both stores: how many rows are still pre-V54 text, and how many bytes. If the second store carries a large stable-plan text tail the largest does not (it was on the product earlier, or its plans are more stable), the fix is running the existing verb — no code. - Purge throughput < insert rate. From the retention log's dimension-GC lines over the last 7 days: rows purged per day vs rows inserted per day (the digest table has no
first_seen; insert rate = distinct new digests per day fromlast_seenon rows whoselast_seenis their only stamp, or simplycount(*)delta between two days). A 50 k row cap per statement is 50 k per sweep; if the sweep runs fewer than ~15 statements a day against 755 k inserts, the table grows by construction and the lever is the purge's per-sweep budget, not the data. - Bloat, not content. Plain table with sustained deletes:
pg_stat_user_tables(n_dead_tup,n_live_tup,last_autovacuum,last_autoanalyze) andpg_relation_sizemain vs TOAST vspg_total_relation_size. A dead-tuple ratio in the tens of percent or a stalelast_autovacuumsays the 157 GB is partly heap the vacuum has not reclaimed — the lever is autovacuum settings on the table (autovacuum_vacuum_scale_factorfor a 12 M-row table is far too coarse at the default 0.2) or a scheduledVACUUM, not retention.
Only after those: if inserts genuinely run ~755 k distinct plans/day on 42 servers, that is plan churn on the monitored fleet — a finding about the fleet, not the store (the #3648 MAX(max_dop) work already showed plan turnover there is real), and the product-side question becomes whether the dimension should hold every distinct plan or only plans whose statements are still in Query Store (planContentRetentionDays shorter than the fact horizon, which the knob already allows).
Scrub: "the second SQL Server store class" / "the largest store"; no store names. Reads are the monitor seat's (authorised, read-only); the lane forms on the numbers.
Activity
erikdarlingdata commentedon Sep 20, 2026
Discriminated (monitor seat's three reads, 13:4xZ) — all three hypotheses dead; the split is horizon × file slack
--recompress-plan-dim --dry-run)Mechanism. Live data ratio 1.5× = the retention-horizon ratio almost exactly (22 d vs 15 d; the cutoff is bounded by surviving digest-carrying facts, so the real question is why that store's fact horizon reaches deeper). The 1.5× becomes 2.4× on disk through TOAST file slack: the second store's toast file sits at a high-water mark — the V54-era text→gz conversion plus ~755 k rows/day cycling through row-capped deletes left ~93 GB of empty pages that ordinary VACUUM returns to the table but never to the OS. The verb's own help text names this case:
--vacuum-full to compact a previously-converted store whose file still sits at its pre-conversion high-water mark. The +24 GB since #2386 is slack accumulation, not net rows: the table is in steady state.Levers, smallest first.
--recompress-plan-dim --vacuum-fullon the second store — ≈ 90 GB reclaimed. A WRITE with anACCESS EXCLUSIVElock window on the store's largest table and a full rebuild's worth of disk headroom during it; maintainer's word + a maintenance slot, executed by the operator, not a lane. The verb already exists; nothing to build.planContentRetentionDays/ the widest fact retention on that store) before any change.Not a code defect as filed. Two things worth carrying out of it: (a) the store-size self-metrics (#3610) should publish TOAST utilisation for the plain dimension tables (live bytes / file bytes), so a 40 % file reads as a finding rather than being discovered on a size pass — one column on the inventory, a lane if wanted; (b) the churn number (755 k distinct plans/day on 42 servers) is unchanged since #2386 and is a fleet-side fact, not this issue's.
Re-labelling
enhancementfor (a); the reclaim itself is an operator action recorded here when it runs.[-]query_plan_dim is 157 GB on the second SQL Server store — 2.3× its near-twin and that store's largest object; discriminate text-tail vs purge throughput vs bloat before choosing the lever[/-][+]query_plan_dim's TOAST file sits at 40 % utilisation on the second SQL Server store (~93 GB slack from the V54 conversion + cycling deletes) — publish dimension-table TOAST utilisation in the store self-metrics; reclaim is --recompress-plan-dim --vacuum-full at the maintainer's word[/+]erikdarlingdata commentedon Sep 20, 2026
Companion column for the same inventory pass (monitor seat, 2026-09-20 16:4xZ): the day's three unattributed read kills all landed inside timed-checkpoint fsync phases — checkpoints completing :23–:24 with
sync = 25.2 s(12:23) and14.0 s(16:24),job_historyempty in both windows. A store whose checkpoint syncs run 25 s should say so on its own card rather than leak it as mystery read kills.pg_stat_checkpointer(PG 17+;pg_stat_bgwriterbefore) carrieswrite_time/sync_time/num_timed/num_requested— onecheckpointrow onstore_metrics(object_kind = 'checkpointer', the deltas since the previous run as*_msbigint,num_requestedas the WAL-forced count) beside the TOAST columns, so this lane's V137 rung is the natural carrier if it has not pushed yet; otherwise the next rung. The self-alert arm: sync tail > 10 s (the MCP read deadline it kills) ornum_requested> 0 in a run → Informational, naming the two levers already on the board (max_wal_size#3802; refresh slicing #3745).Darling V137 / Lite v64: query_store_health stores each database's Qu…
erikdarlingdata commentedon Sep 20, 2026
Live bytes are NULL-by-design on the shipped store, and one statement would light them (code half, #3826).
The V137 rung created
toast_live_bytesand wrote it NULL because every extension-free read was measured and found wanting on exactly this file's shape (rig: TimescaleDB 2.28.1 / PostgreSQL 18, 215 MB of 9.6 KB TOASTed values, half deleted, ordinaryVACUUM,pgstattupleas the oracle at 47.9 % live):n_live_tup / (n_live_tup + n_dead_tup) × fileVACUUMthe dead tuples are gone and the file keeps its pagesn_live_tup(toast) × 1996pg_freespacemap:file − sum(avail)CREATE EXTENSION pg_freespacemappg_freespacemap(andpgstattuple) are contrib modules the bundled image ships and does not install; installing one is a product-dependency decision, so #3826 does not make it. What it does instead:get_store_metricspublishestoast_bytes,toast_live_bytes,toast_utilisation_pctandtoast_utilisation_noteon every dimension object; on the shipped store the percentage is NULL and the note says not measured and names the option below. Nothing is computed fromtotal_bytesor a tuple share.pg_extensionevery run, and only wherepg_freespacemapis installed runs a separate, fencedUPDATEfilling this run's dimension rows frompg_freespace(reltoastrelid)— so the maintainer's single statement lights the percentage, the note and the alert on the next hourly sweep, with no further release.The exact option, when you want it:
CREATE EXTENSION IF NOT EXISTS pg_freespacemap; -- on the store database, once(A future rung could carry that statement so fresh stores get it at V1; #3826 deliberately leaves the decision here.) One number to check first on a store of the size that motivated this:
pg_freespace()materialises one row per page of the TOAST file — ~26 k rows and single-digit ms on the 215 MB rig, an estimated ~20 M rows and tens of seconds on a 154 GB file, inside the sweep's 300 s budget but not free. The fence means that can be measured on a copy before installing on the store itself.The reclaim path is unchanged and unautomated:
--recompress-plan-dim --vacuum-full, at your word, in a maintenance window —ACCESS EXCLUSIVElock on the dimension for the rebuild, free disk for a full copy of the live data. The checkpointer half (the row every sweep, thecheckpointerblock, the Store Checkpointer Pressure informational self-alert on sync > 10 s or any requested checkpoint, naming #3802 and #3745) lands in the same PR.AlertReadFailureSurfaceTests: the #3013 counted-site census gains the…
The store could say how big its plan dimension's TOAST file was and n…
AlertReadFailureSurfaceTests: the #3013 counted-site census gains the…
A "read by nothing" pin cannot outlive the readers it was waiting for…
A "read by nothing" pin cannot outlive the readers it was waiting for…
The store could say how big its plan dimension's TOAST file was and n…
erikdarlingdata commentedon Sep 20, 2026
Closed by the merge of #3826 (its body names this issue with a closing keyword; keywords do not fire against dev, so the watcher closes it).