Retention held by the coverage gate is invisible to store metrics and has no automatic release (use1: 5 query_store_stats policies held 16 days, raw tier at 65 GB / +13 GB per day) #2813

Summary

On the use1 monitoring host, five query_store_stats* retention policies have been held paused by the #1680/#1877 coverage gate since 2026-08-17 — 16 days. collect.query_store_stats is configured drop_after: 4 days and actually holds 18 days 22:56 — 19 chunks, ~65 GB, growing +13 GB/day.

The gate itself is working exactly as designed. This issue is not a request to weaken it. It is about two things the design leaves open:

1. A held policy is invisible to every metric we collect, so it went unnoticed for 16 days.
2. The release requires a manual CLI action that nothing schedules, prompts for, or alerts on — so "held" is a terminal state in practice, not a transient one.

The mechanism, confirmed

TimescaleSupport.EnsureRetentionPoliciesAsync runs on every service start (DarlingWorker.cs:1064). For each policy it calls MeasureRetentionCoverageAsync; on a measured Short verdict it executes HoldRetentionPolicySql → alter_job(j.job_id, scheduled => false).

Confirmed from the box's own app log — it fires on every restart, including all three tonight (19:08:03, 22:00:57, 22:56:03):

[WARN] Retention policy for query_store_stats HELD PAUSED - query_store_stats_hourly does not yet cover
       everything it holds, so arming could drop history that rollup has never materialized.
       Backfill past the 4 days horizon and the policy arms itself on the next start.
[WARN] ... query_store_stats_hourly            HELD PAUSED - query_store_stats_daily short
[WARN] ... query_store_stats_interval_hourly   HELD PAUSED - query_store_stats_corrected_daily short
[WARN] ... query_store_stats_corrected_hourly  HELD PAUSED - query_store_stats_corrected_daily short
[WARN] ... query_store_stats_interval_daily    HELD PAUSED - query_store_stats_daygrain_daily short
[INFO] TimescaleDB: 17/17 retention policies in place, 12 armed, 5 held paused pending backfill,
       0 left as-is (coverage unreadable), 0 moved onto a new horizon

The five held jobs are exactly the coverage-gated Query Store family in RetentionPolicies. The other 12 policies are armed. The named short consumers chain downward and root in the daily CAGGs, which have never been materialized on this store.

Defect 1 — a held policy is invisible outside the app log

StoreSelfMetrics.BackgroundJobInsertSql selects proc_name, hypertable_name, job_id, last_run_duration_ms, schedule_interval_ms, total_runs, total_failures.

It does not select j.scheduled.

So a held policy reports total_failures = 0, last_run_status = Success, and a plausible last_run_duration_ms — byte-for-byte the shape of a healthy job. get_store_metrics cannot distinguish "retention is running" from "retention has been stopped for 16 days", and neither can any alert or hourly health check built on it. The only signal is a [WARN] line emitted once per service start, which nobody reads unless they already suspect the problem.

This is the same failure shape as #2801 (an abandoned cycle recorded as SUCCESS): the honest state exists, and the surface we monitor reports the reassuring one.

Proposed fix: add j.scheduled to BackgroundJobInsertSql and surface it in get_store_metrics. Ideally also emit the hypertable's actual data span against its configured drop_after, since that is the number that makes the consequence legible (4 days configured vs 18 days 22:56 actual) and is what an operator would check by hand.

Defect 2 — "held" has no automatic release and no alert

The design comment says a held policy "arms itself on the next start" after a backfill, and that it "self-heals: a store that is not yet covered stays paused and arms on the first start AFTER a backfill, with no manual step."

The arming is automatic. The backfill is not. It is the --backfill-rollups CLI verb (Program.cs:278, DarlingCliCommands.cs:4918) — a manual, Windows-only, disk-preflighted operator action. Nothing schedules it, nothing prompts for it, and nothing escalates when a policy has been held for an unbounded period.

So on a store where the rollups were never backfilled — which is the state of any store that newly began receiving a workload, as use1 did at the #2296 split — retention for that family is held permanently, and the raw tier grows without bound. Here that is 65 GB and +13 GB/day, on a 250 GB volume.

Proposed fix: a self-alert when a retention policy has been held continuously for longer than some multiple of its own horizon (raw's is 4 days; held for 16 is unambiguous). The alerting seam already exists — the store-disk-pressure self-alert and the compression-job self-heal (#1585) are the precedent, and the latter is the closer analogue: it detects a stalled job, acts once, and escalates rather than looping.

Operational note

Manually running alter_job(..., scheduled => true) does not stick — the next service start re-measures coverage, gets Short, and re-holds. Any restart or deploy reverts it.

It is also worth stating plainly what a manual arm does: raw query_store_stats holds ~15 days of history that no rollup has materialized, so arming drops the only copy. It disappears from every read path, not just the raw tier. That is precisely what the gate exists to prevent, and it is the reason the gate should be released by backfilling rather than by overriding.

Not filed as a defect

The pause itself, and PauseJobSql / HoldRetentionPolicySql / SetRetentionScheduleSql. Searched the full repo for alter_job, scheduled =>, delete_job, remove_retention_policy, add_retention_policy. Every disable path is the coverage gate, is deliberate, is documented, and is correct. Nothing in #2296 or any migration disables jobs as a side effect; the job ids (1072–1079) persisting across the period also rule out a drop-and-recreate.