Query Store aggregate tax scales serially with fleet size — measure, alert, and evaluate before large onboardings

[erikdarlingdata]

From the #2105/#2133 plan work, generalized: the aggregation shape over query_store_stats (wide, near-unique grouping keys → partials combine nothing → single-threaded finalize hash) isn't just the collapse verb's cost — it's what the store's own background jobs pay continuously, and it scales linearly with raw volume with no parallel rescue.

Measured today (dogfood store, 52 servers, 1.2–1.6M raw QS rows/day)

The four most expensive background jobs in the store are all the query_store_stats family:

job	last run
policy_compression query_store_stats	157s
query_store_stats_interval_hourly refresh	96s
query_store_stats_hourly refresh	35s
query_store_stats_corrected_hourly refresh	30s

Everything else in the store is single-digit seconds. Plan analysis (full EXPLAIN in #2133's thread): ~50% serial finalize re-hash of near-unique ~160-byte keys, ~25% backward index scan over the uncompressed hot chunk, ~25% scan+partial hashing. work_mem is NOT the lever (measured: 256MB traded away parallelism and net-lost).

Why file it

Onboarding ~50 primaries ≈ 2× raw volume ≈ interval_hourly at ~3+ min and compression at ~5 min per run. Nothing breaks day one, but: refresh jobs take chunk locks that contend with collector writes (#1564/#1567 family), the CPU competes with collection sweeps (60–75s slow-sweep skips already visible), and refresh lag compounds silently if any job exceeds its cadence.

Asks

1. Visibility first: surface background-job durations (job_stats last/mean duration per proc+hypertable) as self-metrics with a threshold alert — 'refresh exceeded N% of its schedule interval' is the honest tripwire. (Check overlap with V53 self-metrics before building.)
2. Scale test: synthetic 2×–4× ingest on a scratch store to find the knee for the three heavy jobs.
3. Structural options to evaluate (only if 1–2 say so): group keys that let partials actually combine (bucket-first aggregation), refresh cadence/window tuning for interval_hourly, and the hot-chunk access path (backward index scan vs seq).

Not urgent at 52 servers; becomes urgent the week someone points a 100-server fleet at one store.

[erikdarlingdata]

Visibility half done (#2137, merged): the hourly self-metrics sweep records every TimescaleDB background job's last run duration, schedule interval, and run/failure totals (V56), get_store_metrics serves them with a derived duration_vs_cadence_percent, and the live sweep test walks write and read paths end to end. Deploying to the dogfood box next so the series starts accumulating.

Remaining halves: the threshold alert ('job exceeded N% of its cadence' — self-alert family, store-backed knob per the #2107 pattern) and the synthetic scale test.

[erikdarlingdata]

The alert half is merged (#2141): Store Job Over Cadence — WARNING at 25% of schedule interval (V57 knob self_alerts.store_job_cadence_warn_percent, clamped 5–100), CRITICAL fixed at 100%, judged hourly from job_stats (successful runs only), cooldown re-fires, recovery resolution row, alert text points at the V56 duration series for the trend. Robot review: nothing blocking, and it confirmed the deliberate choice that a job absent from a sweep (e.g. its last run just failed) keeps its standing state rather than silently auto-resolving.

Both named halves of this issue — the telemetry series (#2137) and the threshold alert (#2141) — are now shipped and dogfooding on the production box. The only candidate remainder is the synthetic 2–4x scale test I floated during recon; say the word and I'll build it, or close this issue if the live 52-server calibration (worst job ~7% of cadence, warn at 25%) is evidence enough.

[erikdarlingdata]

Closing with all three parts shipped and dogfooding:

1. Telemetry (Store self-metrics measure background jobs — duration vs cadence as a series #2137, V56): one row per background job per hourly sweep — duration, cadence, runs, failures — live on the production box since this morning.
2. Alert (#2136: Store Job Over Cadence self-alert (V57 knob + evaluator) #2141, V57): Store Job Over Cadence — Warning at 25% of schedule interval (store-backed knob), Critical fixed at 100% — deployed and correctly silent (worst job ~7% of cadence). Plus the #2136: gate + reset the cadence-warn Settings box (nightly wiring-test catch) #2142 Settings-box wiring the nightly caught.
3. Scale test (#2136: synthetic scale test — volume→duration→telemetry→alert end to end #2144): live proof of the capacity model — 1x→4x volume grows the measured run duration, the telemetry series records both points, and the alert fires Critical from real store readings. Three CI rounds pinned two genuine TimescaleDB accounting semantics along the way (foreground run_job doesn't update job_stats; total_runs increments at run START and duration reads NULL mid-flight — completion is last_successful_finish).

Nothing closes without testing. This one's tested.