Five query_store retention jobs are Paused, so query_store_stats holds 19 days at a 4-day policy #2809

What

On the use1 monitoring host, the five query_store* retention jobs are scheduled = false / job_status = Paused. Each ran once, on 2026-08-17 (one on 08-18), and has not run since. Every other retention job on the store ran today.

job_id | hypertable_name                    | scheduled | job_status | total_runs | last_successful_finish
  1072 | query_store_stats                  | false     | Paused     |          1 | 2026-08-17 16:46:22
  1078 | query_store_stats_corrected_hourly | false     | Paused     |          1 | 2026-08-17 16:46:22
  1075 | query_store_stats_hourly           | false     | Paused     |          1 | 2026-08-17 16:46:22
  1077 | query_store_stats_interval_hourly  | false     | Paused     |          1 | 2026-08-17 16:46:22
  1079 | query_store_stats_interval_daily   | false     | Paused     |          2 | 2026-08-18 16:46:23

Contrast, same store, same query:

query_stats           | 4 days  | Scheduled | last_successful_finish 2026-09-02 16:46:30
procedure_stats       | 4 days  | Scheduled | last_successful_finish 2026-09-02 16:46:30
blocked_process_...   | 35 days | Scheduled | last_successful_finish 2026-09-02 16:46:27

Consequence

query_store_stats is configured drop_after: 4 days and actually holds 18 days 22:56:

oldest 2026-08-15 00:59:03   newest 2026-09-02 23:55:26
19 chunks (24h interval), hypertable_size = 65 GB

The oldest chunk begins 2026-08-15, two days before the jobs stopped — consistent with retention having dropped normally until then and nothing since.

query_store_stats is the store's largest hypertable at 63–65 GB, and the store has been growing +10 to +12 GB/day, reaching 338 GB. A 4-day retention would hold roughly a fifth of that.

Compression is unaffected — the paused jobs are retention only; is_compressed = true on every old chunk, and the hourly compression job (1029) has 381 successful runs. So the data compresses and is then kept forever.

Why it went unnoticed

total_failures = 0 and last_run_status = Success on all five. Nothing failed — the jobs were paused, so get_store_metrics job health reports them clean. A paused job and a healthy job are indistinguishable in every reading we take.

It is also the upstream cause of the store-read timeouts

The reads that time out against this table (#2795, and the plan-regression read filed alongside this) are slow because the table is ~5x its intended size. #2795's fix bounds a read to 3 hours; the chunk-exclusion bound in the plan-regression read is 15 days, which on a correctly-retained 4-day table would prune to ~5 chunks but here prunes only 3 of 19.

Date correlation, not yet explained

2026-08-17 is the date of the #2296 Query Store split (use2's query_store_stats also stops 2026-08-17 17:36, by design). The pause is very likely related, but I have not established whether it was deliberate at the time or a side effect — that needs someone who knows what #2296 did operationally.

Not done here

Unpausing is a production change and destructive on first run — resuming a 4-day policy against a 19-day table drops ~15 days of Query Store history in one pass. That is Erik's call, including whether to widen drop_after first and step it down.

Evidence gathered read-only (default_transaction_read_only = on) via psql on the box.