Field finding from a production instance, proven against real store data (not estimated). query_stats (176 GB) and procedure_stats (58 GB) are 94% of a 250 GB store, and the cause is schema-level: full query_text/query_plan_xml payloads stored inline on every collected row. Compression and retention tiering are already doing their jobs; this sits underneath them.
Evidence (measured on the live store)
Chunk-level, one representative query_stats chunk via chunk_compression_stats():
- before: 88.97 GB total = 12.41 GB table (numeric/hash) + 74.15 GB TOAST (text/plan XML, 83% of raw)
- after: 17.98 GB = 17.5 MB table (665x - columnar crushes numerics) + 16.73 GB TOAST (4.1x - byte-level only, no cross-row awareness)
- Net: 99.9% of the COMPRESSED chunk is text/plan payload. The monitoring numbers are nearly free.
Dedup PoC built in a scratch schema from a real 1-hour window (514,896 fact rows), populated via plain INSERT ... ON CONFLICT DO NOTHING (per-row PK dedup, same as a live write path):
- Inline cost of the hour (pg_column_size over ALL rows): 2,910 MB plan XML + 255 MB text = 3,166 MB
- Deduped: 2,003 distinct plan hashes -> 23 MB; 453 distinct query hashes -> 432 kB; total ~23.4 MB
- Measured reduction ~135x, and the ratio IMPROVES as the window widens (distinct plans grow far slower than rows)
- Write-path cost: both dim tables populated from the full hour in 8.19 s as ONE bulk pass; production writes a few thousand rows per cycle, so per-cycle cost is a small fraction
Design
- collect.query_text_dim(query_hash PK, query_text) and collect.query_plan_dim(query_plan_hash PK, query_plan_xml); procedure_stats equivalent keyed by its handle/hash shape (same problem, no separate text column)
- PLAIN PostgreSQL tables, NOT hypertables: no time axis, and hypertable unique constraints must include the partitioning column, which a hash-keyed dim cannot satisfy
- Fact tables keep only hash columns; writers INSERT ... ON CONFLICT DO NOTHING into dims first, then facts
- Every consumer that reads query_text/query_plan_xml off the fact tables (MCP tools, compose/viewer detail panes, plan navigation) gains the join - full inventory required, per-citation
Migration: ZERO-REWRITE, non-negotiable
Do NOT backfill-and-drop on deployed stores: rewriting a ~234 GB payload is peak-disk-before-relief (the same trap recorded and rejected in #1759). Instead:
- New rows write hash-only + dims from day one (schema adds dims + new writer; old inline columns stay, new rows leave them NULL)
- Readers COALESCE inline column with the dim join during transition
- Raw retention (4 days) ages the inline copies out by itself; hourly/daily CAGGs never carried the payload
- Column drop (or permanent NULL) happens only after the raw window has fully turned over
Open design items the implementation must answer
- Dim GC: after facts purge, orphaned dim rows accumulate. Options: periodic orphan sweep (delete dims not referenced by any live fact - expensive scan), reference epochs, or keep-forever with a size justification (~23 MB/hr of distinct plans compounds; fleet-months needs a number). Decide with data, record the rationale.
- pg_stats returned 0 rows for the hash/text columns on both hypertables - verify autovacuum/autoanalyze is actually running against chunks; join planning against the dims will need real statistics.
- Lite parity: check whether Lite's DuckDB schema has the same inline pattern and whether its 30-day/local scale makes it a real problem there; investigate mandatory, mechanism only where the problem exists.
Cheap complementary levers (same pass, no schema change)
- Compression delay 24h -> 6-12h (append-only data, never updated post-insert)
- Raw retention below 4 days if nothing needs sub-hourly drill-down past day 1-2
- Confirm the flat 200 rows/server/cycle TOP-N in query_stats was deliberate (direct linear ingest lever; row caps do not bound bytes)
Forward-looking
When primaries arrive and query_store_stats starts collecting for real: apply the dim pattern BEFORE volume flows, mirror the 3-tier retention scheme onto it, and tighten Query Store native settings upstream (QUERY_CAPTURE_MODE=AUTO, bounded MAX_STORAGE_SIZE_MB / STALE_QUERY_THRESHOLD_DAYS).
Field finding from a production instance, proven against real store data (not estimated). query_stats (176 GB) and procedure_stats (58 GB) are 94% of a 250 GB store, and the cause is schema-level: full query_text/query_plan_xml payloads stored inline on every collected row. Compression and retention tiering are already doing their jobs; this sits underneath them.
Evidence (measured on the live store)
Chunk-level, one representative query_stats chunk via chunk_compression_stats():
Dedup PoC built in a scratch schema from a real 1-hour window (514,896 fact rows), populated via plain INSERT ... ON CONFLICT DO NOTHING (per-row PK dedup, same as a live write path):
Design
Migration: ZERO-REWRITE, non-negotiable
Do NOT backfill-and-drop on deployed stores: rewriting a ~234 GB payload is peak-disk-before-relief (the same trap recorded and rejected in #1759). Instead:
Open design items the implementation must answer
Cheap complementary levers (same pass, no schema change)
Forward-looking
When primaries arrive and query_store_stats starts collecting for real: apply the dim pattern BEFORE volume flows, mirror the 3-tier retention scheme onto it, and tighten Query Store native settings upstream (QUERY_CAPTURE_MODE=AUTO, bounded MAX_STORAGE_SIZE_MB / STALE_QUERY_THRESHOLD_DAYS).