I want to declare a retention policy that runs everyday at a specific time. From the docs, it was not clear if this is possible using the add_retention_policy function. The schedule_interval parameter description says that “the interval between the finish time of the last execution and the next start”, while the timezone parameter says that “if initial_start is also specified, subsequent executions of the retention policy are aligned on its initial start”.
So, what should happen when I specify a schedule_interval, initial_start and timezone? Do the subsequent executions stay aligned to initial start or they get shifted by the execution time?
The reason I ask, is that I need guarantees that this routine won't run during my busy hours.
PS: I also made this question in the TSDB forum, I'm reposting here to expand the audience.
If you invoke add_retention_policy() with a given initial_start, then the executions stay aligned with that point in time. Otherwise (with default initial_start IS NULL) the next start is scheduled schedule_interval after the previous run finished. Meaning, the execution time is added to the total cycle time, and the next start is shifted by that amount of time.
You can always verify your understanding with this query provided by the Timescale manual. It displays the next scheduled time:
SELECT j.hypertable_name,
j.job_id,
config,
schedule_interval,
job_status,
last_run_status,
last_run_started_at,
js.next_start,
total_runs,
total_successes,
total_failures
FROM timescaledb_information.jobs j
JOIN timescaledb_information.job_stats js
ON j.job_id = js.job_id
WHERE j.proc_name = 'policy_retention';