The nightly compression pass compresses every heavy hypertable at once after midnight, and collector statements time out #4510

Claude posting for Erik Darling

Every hypertable's compression job compresses its previous day's data in the first hourly run after midnight UTC, and the heavy tables run together, so collector statements time out every night between about 00:36 and 00:45 UTC.

Seen

On one production store, 2026-09-28 00:30–00:55 UTC (the store's own log, plus timescaledb_information.job_stats):

• The compression runs:
  • at 00:36: query_store_stats 5 min 41 s, perfmon_stats 1 min 41 s and wait_stats 13 s, all starting together;
  • then latch_stats, dmv_blocking_snapshots, spinlock_stats and index_object_stats over the next three minutes;
  • query_stats 3 min 17 s at 00:44; job_history at 00:45; plan_correction 1 min 15 s at 00:49; query_snapshots 1 min 10 s at 00:52.
• The harm: five collector statements were cancelled by their client-side command timeout between 00:37 and 00:41, including the Query Store collector's insert, which ran while its own table was compressing. Three client connections were also aborted.
• Midday has no burst: the same windows at 12:30–12:55 UTC on the two previous days show no compression and no cancels.

Why

All the compression policies use compress_after => '1 day' with an hourly schedule, so every table's just-closed chunk becomes eligible at midnight. The per-job phase minutes (a band late in the hour, at most three jobs per minute) spread the starts within one hour, but the heavy work still lands in the same ten minutes.

Fix shape

Give each heavy table its own later eligibility hour (compress_after = 1 day + N hours, a different N per heavy table, longest runs first). Converge existing stores on start-up through alter_job, the way the phase minutes already are. A longer compress_after is safe for the continuous-aggregate refresh windows and the Query Store backfill horizon, which need compression to wait at least a day.