Skip to main content

Use TTL to Manage Data

Relyt provides the Time-to-Live (TTL) feature to enable you to customize tiered storage, deletion policies, or employ a combination of both for flexible and complex data lifecycle management.

Key concepts​

  • Hot data refers to data that is frequently accessed and thus is suitable to be stored in fast storage for efficient access.

  • Cold data refers to data that is seldom accessed and thus is suitable to be stored in cost-effective storage.

  • A hot table is a table specified with the HOT attribute. All data in a hot table resides permanently in the cache.

  • A warm table is a table specified with the WARM attribute. You can specify all data or certain rows in the table to be stored in the cache. Unlike a hot table, data in a warm table can be stored in the cache for a time-limited duration that is specified by <ttl_expression>. Once the specified duration expires, the cached data will be deleted.

Set tiered storage for data​

In most scenarios, data exhibits variability in how frequently it is accessed or updated. To implement effective and efficient storage for data, the TTL feature allows you to configure tiered storage for your data, keeping a good balance between access efficiency and cost-effectiveness.

Syntax​

TTL [HOT | <ttl_expression> WARM ]

Parameters​

  • HOT or WARM

    The tiered storage attribute of the data.

    • HOT: specifies that the table is a hot table, with all data permanently stored in the cache.

    • WARM: specifies that the table is a warm table. Data in the entire table or some rows of the table will be stored in the cache in a specified duration.

  • <ttl_expression>

    The TTL expression that specifies the duration during which the warm data resides in the cache. It can be a specific or relative time. The data type of the column referenced in the expression can only be date, varchar, or integer. If the referenced column type is not a date/time type, use functions to convert its type to a desired type..

    Important

    <ttl_expression> is needed and a must-have when the tiered storage attribute is set to WARM.

Usage notes​

You can configure the WARM attribute at the table or row level. To configure it at the row level, ensure that the following two conditions are met simultaneously:

  • The CLUSTER BY clause is used in the table definition statement, and the first column in the clause is of type date, varchar, or integer.

  • The column referenced in <ttl_expression> is the first column specified in the CLUSTER BY clause.

For tables frequently accessed or utilized for storing real-time or critical business decision-making data, we recommend that you use TTL HOT to keep the data permanently in the cache.

For the complete CREATE TABLE syntax and detailed explanation, see CREATE TABLE.

Examples​

Run the following SQL command to create a hot table named test_hot in which data will permanently reside in the cache, ensuring query stability and providing real-time responses.

CREATE TABLE test_hot (column integer) TTL HOT;

Run the following SQL commands to create two warm tables named test_warm1 and test_warm2, respectively. For each of them, all data in the table will become cold and removed from the cache when the specified retention duration reaches.

-- All data in the table will reside in the cache until January 1, 2024, after which it will be removed.
CREATE TABLE test_warm1 (col1 integer) 
TTL (('2024-01-01'::date)) WARM;

-- All data in the table will reside in the cache for 7 days after table creation, and then it will be removed from the cache.
CREATE TABLE test_warm2 (col1 integer) 
TTL ((now() + interval '7 days')) WARM;

Run the following SQL commands to create a table named test_warm3 and insert some data. Rows in this table will be stored in the cache until 7 days after insertion.

CREATE TABLE test_warm3 (ds date, content varchar) 
CLUSTER BY (ds) 
TTL ((ds + interval '7 days')) WARM;

INSERT INTO test_warm3 VALUES ('2024-01-01', 'abc'); -- This row of data will be removed from the cache on January 8, 2024.


INSERT INTO test_warm3 VALUES ('2024-01-02', 'abc'); -- This row of data will be removed from the cache on January 9, 2024.

Set deletion policies​

The TTL clause can also be used to set deletion policies for tables.

Syntax​

TTL <delete_expression> DELETE

Parameters​

  • <delete_expression>

    The deletion expression, used in combination with the DELETE keyword.

  • [DELETE]

    The delete keyword, used in combination with the <delete_expression> expression.

Usage notes​

Deletion policies can be effective at both the table and row levels. To configure deletion policies at the row level, ensure the following two conditions are met at the same time:

  • The CLUSTER BY clause is used in the table definition statement, and the first column in the clause is of type date, varchar, or integer.

  • The column referenced in <ttl_expression> is the first column specified in the CLUSTER BY clause.

For the complete CREATE TABLE syntax and detailed explanation, please refer to CREATE TABLE.

Examples​

Run the following SQL command to create a table named test_1 that will be deleted on January 1, 2024.

CREATE TABLE test_1 (col1 integer) TTL (('2024-01-01'::date)) DELETE;

Run the following SQL command to create a table named test_2 that will be deleted 30 days after creation.

CREATE TABLE test_2 (col1 integer) TTL ((now() + interval '30 days')) DELETE;

Run the following SQL command to create a table named test_3 and insert some data. Rows in the table will be stored in the cache until 7 days after the time specified by the ds column.

CREATE TABLE test_3 (ds date, content varchar) 
CLUSTER BY (ds) 
TTL ((ds + interval '7 days')) DELETE;

INSERT INTO test_3 VALUES ('2024-01-01', 'abc'); -- This row of data will be deleted on January 8, 2024.
INSERT INTO test_3 VALUES ('2024-01-02', 'abc'); -- This rows of data will be deleted on January 9, 2024.

Set tiered storage and deletion policies for tables at the same time​

The TTL feature allows you to configure tiered storage and deletion policies for tables simultaneously, enabling more flexible and complex data lifecycle management.

Set a table to HOT and configure a deletion policy for it

Run the following SQL command to create a hot table named test_hot1 that will be deleted 7 days after the time specified by the ds column.

CREATE TABLE test_hot1 (ds date, content varchar) 
CLUSTER BY (ds) 
TTL HOT, ((ds + interval '7 days')) DELETE;

Set a table to WARM and configure a deletion policy for it

Run the following SQL command to create a warm table named test_warm4 and set rows to transition to cold 3 days after the time specified by the ds column, and to be deleted 7 days after the time specified by the ds column.

CREATE TABLE test_warm4 (ds date, content varchar) 
CLUSTER BY (ds) 
TTL ((ds + interval '3 days')) WARM, ((ds + interval '7 days')) DELETE;