Create a policy to drop chunks older than a given interval of a particular hypertable or continuous aggregate on a schedule in the background. For more information, see the drop_chunks section. This implements a data retention policy and removes data on a schedule. Only one retention policy may exist per hypertable.

NameTypeDescription
relationREGCLASSName of the hypertable or continuous aggregate to create the policy for
drop_afterINTERVAL or INTEGERChunks fully older than this interval when the policy is run are dropped
schedule_intervalINTERVALThe interval between the finish time of the last execution and the next start. Defaults to NULL.
initial_startTIMESTAMPTZTime the policy is first run. Defaults to NULL. If omitted, then the schedule interval is the interval between the finish time of the last execution and the next start. If provided, it serves as the origin with respect to which the next_start is calculated.
timezoneTEXTA valid time zone. If initial_start is also specified, subsequent executions of the retention policy are aligned on its initial start. However, daylight savings time (DST) changes may shift this alignment. Set to a valid time zone if this is an issue you want to mitigate. If omitted, UTC bucketing is performed. Defaults to NULL.

The drop_after parameter should be specified differently depending on the type of the time column of the hypertable:

  • For hypertables with TIMESTAMP, TIMESTAMPTZ, and DATE time columns: the time interval should be an INTERVAL type.
  • For hypertables with integer-based timestamps: the time interval should be an integer type (this requires the integer_now_func to be set).
NameTypeDescription
if_not_existsBOOLEANSet to true to avoid an error if the drop_chunks_policy already exists. A notice is issued instead. Defaults to false.
drop_created_beforeINTERVALChunks with creation time older than this cut-off point are dropped. The cut-off point is computed as now() - drop_created_before. Defaults to NULL. Not supported for continuous aggregates yet.
ColumnTypeDescription
job_idINTEGERTimescaleDB background job ID created to implement this policy

Create a data retention policy to discard chunks greater than 6 months old:

SELECT add_retention_policy('conditions', drop_after => INTERVAL '6 months');

Create a data retention policy with an integer-based time column:

SELECT add_retention_policy('conditions', drop_after => BIGINT '600000');

Create a data retention policy to discard chunks created before 6 months:

SELECT add_retention_policy('conditions', drop_created_before => INTERVAL '6 months');

Note above that when drop_after is used then the time data range present in the partitioning time column is used to select the target chunks. Whereas, when drop_created_before is used then the chunks which were created 3 months ago are selected.

Keywords

Found an issue on this page?Report an issue or Edit this page in GitHub.