Gzip the plan-XML dimension content at the application level — measured 14.0x vs lz4's 8.9x on live content

[erikdarlingdata]

Decision (Erik, 2026-08-05): realize compression gains today with app-level gzip. Native TOAST tops out at lz4 in PG 18 (pglz, lz4 are the only accepted values — verified against the production binary; zstd TOAST is still an in-progress upstream patch), and gzip is not a TOAST option at all, so the gain comes from compressing in the service.

Measured on live store content (stratified 400 plans + 2,000 texts, per-value):

	plan XML	query text
pglz	6.6×	1.7×
lz4 (current TOAST)	8.9×	1.5×
gzip Optimal (app-level)	14.0×	2.4×

Scope is plan XML only: query_plan_dim is 101 GB (69% of the 147 GB store) while query_text_dim is 21 MB — text is not worth reader churn. Projected: 101 GB → ~64 GB, self-realizing within the ~9-day GC turnover with no backfill/rewrite. Throughput is a non-issue (51 MB in 5s single-threaded PowerShell; the service will do better against ~4 GB/hour raw intake).

Design constraints, from the code:

• The query_text/query_plan_xml stored inline per row: 94% of a field store — normalize into hash-keyed dimension tables (~135x measured) #1767 resolving view (v_query_stats) serves query_plan_xml as SQL text via COALESCE — PG cannot gunzip in SQL, so the dim gains a query_plan_gz bytea column (migration + the full schema-gate ladder ceremony) and the RESOLVING VIEW exposes presence, not content: plan-fetching readers (GetQueryStatsPlanXmlAsync, the MCP plan tools, the alert context builder) fetch the gz column and decompress in C#. The has_query_plan presence flag already tests digest/inline without resolving — extend it to the gz arm.
• Write path: PayloadDimensionWriter/batch gzips before upsert (System.IO.Compression, Optimal). Digest stays computed over the UNCOMPRESSED text so identity is stable across the format change.
• Transition: old rows keep text, new rows carry gz — readers COALESCE (decompress gz if present, else text), and the GC window ages the text era out in ~9 days. No migration of existing content.
• Lite is unaffected (no plan capture, no dims).
• Same treatment optional later for analysis_findings.drill_down_json/plan XML inside alert ContextJson — out of scope here.

Sequencing note: builds on top of the #2068 self-metrics PR (shared migration/pin files) — start after it merges. #2068's series is also how we'll SEE this land: the dim's byte curve should bend ~35% down over the following nine days.

Complementary, not replaced: the July-31 capture-rate question (20× distinct-plan intake) remains the bigger lever and is a separate decision.

[erikdarlingdata]

Decisions from Erik (2026-08-05): capture depth STAYS (the July-31 tuning's triage value — host-object attribution and the fleet-chain work this week — outweighs the storage; the intake question is settled, keep it). Gzip approved — proceed after #2068's PR merges. Projected steady state: ~64 GB plan dim at full retained triage depth.

[erikdarlingdata]

Shipped in #2072 (V54). New plans store as gzip bytes in query_plan_dim.query_plan_gz (measured 14.0x vs lz4 TOAST's 8.9x on live content — projected ~101 GB → ~64 GB steady state on the production store); digests stay computed over uncompressed text so identity/dedup/presence survive; old text rows convert by normal dim GC turnover (~9 days), no rewrite. All six read seams resolve text-else-gz. Disclosed loss: raw SQL sees NULL query_plan_xml on post-V54 plans — the bytes in query_plan_gz are a plain gzip member (gunzip recovers them); product surfaces unaffected. zstd remains tracked in #2071.