Data Retention Policy

Retention Policy is used to manage the life cycle of the time-series data. The data retention policy used for a time-series table determines the retention time of the data in the time-series table, and any data that exceeds the retention time is automatically deleted. Data retention policy belongs to a database, and multiple retention policies can be created under the same database, and one of them can be specified as the default policy. If you do not explicitly specify a retention policy when creating a time-series table, the default retention policy under that database will be used.

Create a Data Retention Policy

Used to create a new data retention policy under the current database.

The data retention policy contains two parameters:

duration: data retention time, mandatory parameter. For example, if set to 1week, data older than one week will be deleted automatically.
chunkGroupDuration: Data partitioning granularity, optional parameter. The data in the time-series table will be partitioned according to the chunkGroupDuration set in the time-series table; data that exceeds the retention duration will also be deleted according to the ChunkGroupDuration granularity. If this parameter is not specified, the system will automatically specify the chunkGroupDuration according to the duration.

Syntax Format

CREATE TIMESERIES POLICY [ IF NOT EXISTS ] policy_name WITH (option = value, [...] )

option includes:

duration: indicates the time-series data retention duration. If the retention duration is exceeded, data will be automatically deleted by chunk group. The value should be greater than or equal to 1 hour. The value can also be 0, indicating that data is permanently reserved meanwhile the time unit can be second, minute, hour, day, week, month, and year.
chunkGroupDuration: indicates the temporal granularity. The value cannot be set to 0.

If this parameter is not specified, the system automatically sets it in terms of the duration parameter. The following are rules to be followed:
- If the duration value is lower than 2 days, set chunkGroupDuration to 1 hour. It means that the start time of chunkGroupDuration should be an integer, such as 2020-07-10 00:00:00 and 2020-07-10 01:00:00 rather than 2020-07-10 00:10:00 and 2020-07-10 00:10:10.
- If the duration value is lower than or equal to 6 months, set chunkGroupDuration to 1 day. It means that the chunkGroupDuration value can be a value like 2020-07-10 00:00:00 and 2020-07-11 00:00:00.
- If the duration value is greater than 6 months, set chunkGroupDuration to 7 days. It means that the start day of the chunkGroupDuration value should be Monday of a week.
- If the duration value is greater than 3 years, set chunkGroupDuration to 1 month. It means that the start date of the chunkGroupDuration value should be the first day of a month.

The upper limits of duration and chunkGroupDuration are 2³¹.

There is a default data retention policy named 'infinity' under each database. The default policy has duration='0s', which means the data will never be deleted. chunkGroupDuration='1month'.

Constraints

The name of the newly created data retention policy cannot be renamed with an existing retention policy.
The value of duration should be greater than or equal to that of chunkGroupDuration.
duration and chunkGroupDuration are formatted as number + time unit. If the number entered is not an integer, it will be rounded upwards, e.g. 1.2 weeks will be rounded to 2 weeks.
duration and chunkGroupDuration do not support negative numbers, duration and chunkGroupDuration support the minimum time unit is 1 hour, if the value set is less than 1 hour will be automatically set to 1 hour.
The value unit of duration and chunkGroupDuration can be hour, day, week, month, and year.
The value range of each unit needs to meet the following condition.

Unit Upper Limit Lower Limit

Year 0 296533

Month 0 3558399

Week 0 15250284

Day 0 106751991

Hour 0 2147483647

Unit	Upper Limit	Lower Limit
Year	0	296533
Month	0	3558399
Week	0	15250284
Day	0	106751991
Hour	0	2147483647

Examples

Uqbar=# CREATE TIMESERIES POLICY policy_test WITH (duration = '2 years', chunkGroupDuration='7 days');
CREATE POLICY

Modify a Data Retention Policy

For modifying existing data retention policies.

Syntax Format

ALTER TIMESERIES POLICY [IF EXISTS] policy_name actions

action includes:

RENAME TO policy_name, used to modify the data retention policy name, where policy_name is used to specify the modified policy name.
SET ( option = value, [...] ) , used to modify the data retention policy parameters.

option includes:

{ duration, chunkGroupDuration }

Constraints

The modified duration and chunkGroupDuration must meet the constraints of the new data retention policy.

Examples

Uqbar=# ALTER TIMESERIES POLICY policy_test SET( chunkGroupDuration='1 month');
ALTER POLICY
Uqbar=# ALTER TIMESERIES POLICY policy_test RENAME TO policy_1;
ALTER POLICY

Delete a Data Retention Policy

For deleting existing data retention policies.

Syntax Format

DROP TIMESERIES POLICY [IF EXISTS] policy_name

This syntax is used for deleting one or more time-series retention policies.

If there is no the IF EXISTS option and a non-existent policy occurs in the policy list to be deleted, the deletion will fail, an error message is reported, and any policy is not deleted.

If the IF EXISTS option is included and a non-existent policy occurs in the policy list to be deleted, the non-existent policy is skipped, a prompt message is reported, and the policies are deleted except the non-existent one.

Constraints

If the data retention policy is being used by a time-series table, it cannot be deleted.
The default policy under the database cannot be deleted.

Examples

Uqbar=# DROP TIMESERIES POLICY policy1;
DROP POLICY

Set a Default Data Retention Policy

Used to modify the default data retention policy under the current database.

Syntax Format

ALTER TIMESERIES POLICY policy_name DEFAULT;

Examples

Uqbar=# ALTER TIMESERIES POLICY policy1 DEFAULT;
ALTER TIMESERIES POLICY

Query Data Retention Policy

The timeseries_views.policies view shows the data retention policies that currently exist. For view attributes, see timeseries_views.policies.

Syntax Format

SELECT * FROM timeseries_views.policies;

Examples

Uqbar=# SELECT * FROM timeseries_views.policies;
 policyname | duration | chunkgroupduration | default 
------------+----------+--------------------+---------
 infinity   | 00:00:00 | 1 mon              | t

(2 rows)