TTL Delete Data

1. Overview

Time-to-Live (TTL) is a mechanism for defining the lifespan of data in a database. In IoTDB, TTL allows setting table-level expiration policies, enabling the system to automatically delete outdated data periodically. This helps manage disk space efficiently, maintain high query performance, and reduce memory usage.

TTL values are specified in milliseconds, and once data exceeds its defined lifespan, it becomes unavailable for queries and cannot be written to. However, the physical deletion of expired data occurs later during the compaction process. Note that changes to TTL settings can briefly impact the accessibility of data.

Notes:

1. TTL defines the expiration time of data in milliseconds, independent of the time precision configuration file.
2. Modifying TTL settings can cause temporary variations in data accessibility.
3. The system eventually removes expired data, though this process may involve some delay.。

2. Set TTL

In the table model, IoTDB’s TTL operates at the granularity of individual tables. You can set TTL directly on a table or at the database level. When TTL is configured at the database level, it serves as the default for new tables created within the database. However, each table can still have its own independent TTL settings.

Note: Modifying the database-level TTL does not retroactively affect the TTL settings of existing tables.

2.1 Set TTL for Tables

If TTL is specified when creating a table using SQL, the table’s TTL takes precedence. Refer to Table-Managementfor details.

Example 1: Setting TTL during table creation:

CREATE TABLE test3 ("site" string id, "temperature" int32) with (TTL=3600)

Example 2: Changing TTL for an existing table:

ALTER TABLE tableB SET PROPERTIES TTL=3600;

Example 3: If TTL is not specified or set to the default value, it will inherit the database's TTL. By default, the database TTL is 'INF' (infinite):

CREATE TABLE test3 ("site" string id, "temperature" int32) with (TTL=DEFAULT)
CREATE TABLE test3 ("site" string id, "temperature" int32)
ALTER TABLE tableB set properties TTL=DEFAULT

2.2 Set TTL for Databases

Tables without explicit TTL settings inherit the TTL of their database. Refer to Database-Managementfor details.

Example 4: A database with TTL=3600000 creates tables inheriting this TTL:

CREATE DATABASE db WITH (ttl=3600000)
use db
CREATE TABLE test3 ("site" string id, "temperature" int32)

Example 5: A database without a TTL setting creates tables without TTL:

CREATE DATABASE db
use db
CREATE TABLE test3 ("site" string id, "temperature" int32)

Example 6: Setting a table with no TTL explicitly (TTL=INF) in a database with a configured TTL:

CREATE DATABASE db WITH (ttl=3600000)
use db
CREATE TABLE test3 ("site" string id, "temperature" int32) with (ttl='INF')

3. Remove TTL

To cancel a TTL setting, modify the table's TTL to 'INF'. Note that IoTDB does not currently support modifying the TTL of a database.

ALTER TABLE tableB set properties TTL='INF'

4. View TTL Information

Use the SHOW DATABASES and SHOW TABLES commands to view TTL details for databases and tables. Refer to Database-Management、 Table-Managementfor details.

Note: TTL settings in tree-model will also be shown.

Example Output:

IoTDB> show databases
+---------+-------+-----------------------+---------------------+---------------------+
| Database|TTL(ms)|SchemaReplicationFactor|DataReplicationFactor|TimePartitionInterval|
+---------+-------+-----------------------+---------------------+---------------------+
|test_prop|    300|                      1|                    3|               100000|
|    test2|    300|                      1|                    1|            604800000|
+---------+-------+-----------------------+---------------------+---------------------+

IoTDB> show databases details
+---------+-------+-----------------------+---------------------+---------------------+-----+
| Database|TTL(ms)|SchemaReplicationFactor|DataReplicationFactor|TimePartitionInterval|Model|
+---------+-------+-----------------------+---------------------+---------------------+-----+
|test_prop|    300|                      1|                    3|               100000|TABLE|
|    test2|    300|                      1|                    1|            604800000| TREE|
+---------+-------+-----------------------+---------------------+---------------------+-----+
IoTDB> show tables
+---------+-------+
|TableName|TTL(ms)|
+---------+-------+
|    grass|   1000|
|   bamboo|    300|
|   flower|    INF|
+---------+-------+

IoTDB> show tables details
+---------+-------+----------+
|TableName|TTL(ms)|    Status|
+---------+-------+----------+
|     bean|    300|PRE_CREATE|
|    grass|   1000|     USING|
|   bamboo|    300|     USING|
|   flower|    INF|     USING|
+---------+-------+----------+