Add a data retention policy

Retention policies drop entire chunks once their time range falls outside your window, which is cheaper than deleting millions of rows one by one. TimescaleDB schedules a background job to apply the policy for you.

Add a data retention policy

Add a data retention policy by using the add_retention_policy function.

1. Choose your hypertable and retention interval

   Decide how long you want to keep data before dropping it. In this example, the hypertable named conditions retains the data for 24 hours.

2. Call add_retention_policy

   SELECT add_retention_policy('conditions', INTERVAL '24 hours');

Note

A data retention policy only allows you to drop chunks based on how far they are in the past. To drop chunks based on how far they are in the future, manually drop chunks.

Remove a data retention policy

Remove an existing data retention policy by using the remove_retention_policy function. Pass it the name of the hypertable to remove the policy from.

SELECT remove_retention_policy('conditions');

See scheduled data retention jobs

To see your scheduled data retention jobs and their job statistics, query the timescaledb_information.jobs and timescaledb_information.job_stats tables. For example:

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';

The results look like this:

-[ RECORD 1 ]-------+-----------------------------------------------
hypertable_name     | conditions
job_id              | 1000
config              | {"drop_after": "5 years", "hypertable_id": 14}
schedule_interval   | 1 day
job_status          | Scheduled
last_run_status     | Success
last_run_started_at | 2022-05-19 16:15:11.200109+00
next_start          | 2022-05-20 16:15:11.243531+00
total_runs          | 1
total_successes     | 1
total_failures      | 0