Query Store hourly/daily CAGGs still bake in the pre-dedup inflation (#1841 tier 3)

[erikdarlingdata]

Spun out of #1841 tier 2 (PR pending) after building and live-probing the corrected-rollup shapes against PostgreSQL 18.4 + TimescaleDB 2.28.1. Tier 2 collected the real interval identity (runtime_stats_interval_id + interval_start_time_utc, Lite v49 / Darling V41) and every RAW-tier read now dedups on it. The CAGGs are the one surface tier 2 did not repair, and this issue carries the design so it does not have to be re-derived.

What is still wrong

collect.query_store_stats_hourly materializes sum(execution_count) and both weighted sums from un-deduped raw rows, and query_store_stats_daily inherits them hierarchically. Query Store rows are cumulative per-interval snapshots that the collector re-fetches every cycle, so one interval's work is baked in once per collection — measured on a live store at up to 496x for a single interval inside one hour bucket.

No read-side edit can undo it: the duplicates are gone once materialized, and the CAGG output carries no interval identity to key on. Consequence today, unchanged by tier 2: a Custom Views panel whose window stays inside the raw tier (<= 3 days) reads corrected numbers; a window past it reads inflated ones, with a visible step at the routing boundary.

Live probe results (TimescaleDB 2.28.1, PG 18.4)

Five CAGG shapes, created against a hypertable with query_store_stats' exact column set:

Probe	Shape	Result
1	time_bucket('1 hour', interval_start_time_utc)	REJECTED — ERROR: time bucket function must reference the primary hypertable dimension column
2	last(execution_count, collection_time) grouped by full interval identity, bucketed on collection_time	ACCEPTED
3	row_number() OVER (...) inside the CAGG	REJECTED — ERROR: invalid continuous aggregate query / HINT: Enable experimental window function support by setting timescaledb.enable_cagg_window_functions
4	Hierarchical CAGG on probe 2, re-dedup with last(x, bucket) over the interval identity	ACCEPTED
5	Hierarchical CAGG on probe 2, collapsing interval grain to the composer dims with sum(...)	ACCEPTED

Probe 1 is the load-bearing constraint: a CAGG on query_store_stats cannot bucket on interval_start_time_utc. It must bucket on collection_time, the hypertable's time dimension. That single fact shapes everything below.

The design that works

Because the bucket must be collection_time, dedup has to happen at the interval grain inside the CAGG, and the composer-grain aggregation has to be a second, hierarchical level:

• L1 — query_store_stats_interval_hourly (new): GROUP BY server_id, server_name, database_name, module_name, query_hash, query_id, plan_id, runtime_stats_interval_id, execution_type_desc, replica_role, time_bucket('1 hour', collection_time), projecting last(execution_count, collection_time), last(avg_duration_us, collection_time), last(avg_cpu_time_us, collection_time), last(interval_start_time_utc, collection_time), max(max_*), count(*). This is probe 2 — verified valid.
• L2 — the composer-grain rollup sourced from L1, summing the deduped per-interval values into execution_count_sum / duration_us_weighted_sum / cpu_us_weighted_sum (probe 5 — verified valid), keeping the same column names the existing hourly/daily carry so ComposeCaggValueMapper reads it unchanged.

Residual inexactness to decide on. An interval whose snapshots straddle an hour boundary produces two L1 rows, each holding a cumulative value, so L2's sum counts it roughly twice. That is bounded by how many buckets the interval was collected across (~2), against today's how many times it was collected (up to 496) — a 250x improvement but not exact. Probe 4 shows the exact fix: an intermediate level that re-dedups with last(x, bucket) over the interval identity at the coarser grain. Whether the third level is worth its cost is the open product/capacity call.

Capacity is a real input, not a footnote. L1 keys on query_id/plan_id/runtime_stats_interval_id, so its cardinality is near-raw — the reduction is only the collection multiplicity, not the dimensional collapse the current CAGG gets from grouping to module_name/query_hash. On a fleet store this is a large object, and #1581 (field-box memory climb) says to size it before shipping it.

Why this is a staged migration, not an edit

Every one of these has to move together, and each interacts with machinery an in-place edit would break:

1. The existing CAGGs must be KEPT. CreateQueryStatsDbHourlySql's own comment records the precedent: TimescaleDB cannot ALTER columns into a continuous aggregate, so reshaping means DROP + recreate, and with retention active the rebuild would re-materialize from 4 days of raw and permanently destroy the 21-day hourly and indefinite daily history. DropStaleContinuousAggregatesAsync is not a counter-example — its comment states the CAGGs it drops are empty or a day or two old. Per Rollup CAGGs serve only materialized buckets: old windows read empty and raw purges stay held #1759/Make the catalog sweep honor the #1680 coverage gate (#1784) #1793, materialized history is never destroyed. So old and new coexist, and the old ones keep their retention policies.
2. Retention arming keys on the OLD view. TimescaleSupport.RawTierCoverage maps ("query_store_stats", "collection_time", QueryStoreStatsHourlyView), and RetentionArmSafetySql gates the raw purge on that view's min(bucket). If reads move to the new rollup while the gate still watches the old one, raw can be purged over history the new rollup has not materialized. The gate must move in the same change — and the new CAGG is born WITH NO DATA with a refresh policy that only reaches creation-minus-3-days, so moving the gate immediately either disarms raw retention or arms it over a hole. The --backfill-rollups operator op has to cover the new views first.
3. Routing. ComposeCaggCatalog entry, ComposeSourceRouter degrade ladder, RollupAvailability / RollupCoverage registration (RollupViews, RollupCoverageProbeSql), and the TimescaleSupportTests view-name list all name the current pair.
4. The coverage probe must agree with arming. RollupCoverageProbeSql and RetentionArmSafetySql deliberately share the min(bucket) expression; adding a tier without adding it to both is exactly the Ungated catalog sweep bypasses the #1680 coverage gate: drop_chunks destroys rollup-uncovered raw history at 30 days #1784 defect (two purge paths judging one table by different rules).

Rejected alternatives, with reasons

• materialized_only = false — cannot surface un-materialized history (the watermark is a hard partition, build_union_query emits materialized-below UNION ALL raw-at-or-above), and it would break the min(bucket) semantics both the coverage probe and the arming gate depend on. Pinned against by TimescaleContinuousAggregateTests.
• timescaledb.enable_cagg_window_functions (surfaced by probe 3's own hint) — an experimental GUC. Shipping a product's correctness on a server-side experimental toggle that an operator's own PostgreSQL may not have set is not a trade worth making; the last() shape is supported and needs no flag.
• Dropping and rebuilding the existing CAGGs — destroys materialized history, see (1).
• Write-side dedup into a separate fact table — plausible, but it duplicates the collector's storage and re-opens the incremental-watermark question; not evaluated in depth because the last() CAGG path is cheaper and needs no new write path.

Scope note

Tier 2 shipped deliverables 1-3 (identity collection, dedup-key upgrade on every raw read, the honest duration trend + slicer placement). Those alone make every row collected from now on dedupable by real identity, so whenever this rollup work happens the data it needs is already there. Until then the raw/CAGG step at the routing boundary stands, and it is documented at ComposeCompiler.BuildFactRelation.