Retention policies run their first check immediately — no external window to pause them, caused permanent data loss #1680

Field finding from prod-sql-use2-monitor-01 during the tiered-retention deploy on 2026-07-24. This one caused real, permanent data loss and should be fixed before 3.3.0, because 3.3.0 is exactly when existing stores get these policies for the first time.

What happens

TimescaleDB runs a newly-created retention policy's first check immediately at job creation, not on its next scheduled interval. Confirmed on the field box via job_stats timestamps: all 6 new retention jobs fired and completed within the same minute as service startup (00:50:05-00:51:02 UTC) — before any external SQL session could pause them per the runbook's intended pause-before-backfill ordering.

Result on that store: ~2 days of query_stats/procedure_stats history and all of query_store_stats history were dropped before the pause could take effect. Not recoverable.

There is no external window to win this race. Being faster is not the fix.

Verified in code

TimescaleSupport.AddRetentionPolicySql (:663-664):

SELECT add_retention_policy('collect.{relation}', drop_after => INTERVAL '{dropAfter}', if_not_exists => true)

No scheduled argument anywhere in the file — so every policy is created live and armed.

Fix

Create the job already paused in the same call that creates it, then enable it only after a verified backfill:

• add_retention_policy(..., scheduled => false)
• then alter_job(<id>, scheduled => true) once the CAGGs are confirmed backfilled past their horizons

That closes the race by construction rather than by timing.

Why this matters more now than it did last week

The cold-start caveat already documented on EnsureRetentionPoliciesAsync — that an existing store must have its CAGGs backfilled past the horizon before retention runs — is currently only enforceable by an operator racing the scheduler and losing. Any 3.3.0 upgrade of a store with history to preserve hits the same window.

Note also that #1664 added a seventh policy (query_stats_db_hourly), so the fix should cover the whole list rather than the original six.