add_retention_policy()

Create a policy to drop chunks older than a given interval of a particular hypertable or continuous aggregate on a schedule in the background. (See drop_chunks). This implements a data retention policy and removes data on a schedule. Only one retention policy may exist per hypertable.

Required arguments

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

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).

Optional arguments

NameTypeDescription
if_not_existsBOOLEANSet to true to avoid throwing an error if the drop_chunks_policy already exists. A notice is issued instead. Defaults to false.

Returns

ColumnTypeDescription
job_idINTEGERTimescaleDB background job id created to implement this policy

Sample usage

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

  2. SELECT add_retention_policy('conditions', INTERVAL '6 months');

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

  2. SELECT add_retention_policy('conditions', BIGINT '600000');