The Time To Live (TTL) feature in the SQL mode of OceanBase AI Database provides the capability to manage expired data. By defining a TTL strategy for a table, you can set the validity period of the data. The system then executes TTL tasks to process expired data.
For tables with a defined TTL strategy, the system processes expired data in the following two steps:
When a TTL task is triggered, the system synchronizes a delete marker for tables with the TTL attribute, their index tables, and auxiliary tables. Data rows that meet the deletion criteria are marked as deleted and become invisible to users.
During the next major compaction, the system removes the marked data from the storage layer to free up storage space.
This topic describes how to initiate a TTL task by using statements or by setting up periodic tasks.
Considerations
When a TTL task synchronizes the deletion marking, it locks the table, which is mutually exclusive with operations such as direct load, DDL, and transfer.
When the total number of partitions in the primary table, index table, or auxiliary table exceeds 4,000, transactions may time out, causing the task that marks data for deletion to fail and retry indefinitely. This can be resolved by increasing the timeout period using the following statement:
obclient(root@sys)[(none)]> ALTER SYSTEM SET internal_sql_execute_timeout = 600; /* Execute this statement in the sys tenant. The unit is seconds. */Since the transaction that marks data for deletion is not mutually exclusive with regular DML transactions, when two transactions run concurrently and the transaction isolation level is Read Committed, phantom reads may occur when querying near-expiry data within a DML transaction.
For example, consider a transaction
trans_Athat marks data for deletion, a regular DML transactiontrans_B, and a near-expiry data rowrowkey_A. Over the period T1 to T3 (where T1 < T2 < T3):- At T1: The data row
rowkey_Ahas not reached its expiration time, so the regular DML transactiontrans_Bcan read its data. - At T2: The data row
rowkey_Areaches its expiration time, and the transactiontrans_Amarksrowkey_Aas deleted. - At T3: When the regular DML transaction
trans_Breadsrowkey_Aagain, it finds that the data is no longer available.
- At T1: The data row
When a TTL task is not running, you can query data in the table that meets the expiration rules. If you have strict visibility requirements for expired data, you must add a filter condition to the query SQL statement.
After you execute offline DDL operations or create a new index on the primary table or its indexes, the table data is rewritten and the write time of the data is updated, which delays data expiration.
After a TTL task is executed, expired data is asynchronously deleted during the compaction process. The deletion process is performed only within OceanBase AI Database and is not synchronized to downstream application systems.
Definition of a TTL table
Limitations
When using the TTL feature to manage expired data, the following limitations apply to TTL tables:
Foreign key constraints are not supported.
Triggers are not supported.
Vector indexes and full-text indexes are not supported.
Only single columns can be set as TTL columns; virtual columns, generated columns, or expressions are not supported.
The table update mode
MERGE_ENGINEcannot be set topartial_update.The current version supports only the internal hidden column
ora_rowscnas the TTL column (which records the timestamp of the last update).For a table with a TTL attribute defined, you cannot add a column named
ora_rowscn.
Create a table with a TTL attribute
The statement for creating a TTL table is as follows:
CREATE TABLE table_name (table_definition_list) MERGE_ENGINE = {append_only | delete_insert}
TTL [=] col_name + INTERVAL interval_num ttl_unit BY COMPACTION;
The parameters in the statement are described as follows:
MERGE_ENGINE: Specifies the update mode of the table. The table update modeMERGE_ENGINEcannot be set topartial_update.col_name: Specifies the TTL filter column. The current version supports only the internal hidden columnora_rowscn, which records the timestamp of the last data update.interval_num: Specifies the integer value of the expiration time. The value range is [0,+∞). When the value is0, the time unitttl_unitcan be any of the supported values, indicating that the data expires immediately after it is committed.ttl_unit: Specifies the unit of the expiration time. Supported values areSECOND,MINUTE,HOUR,DAY,MONTH, orYEAR.
Example: Create a TTL table where data expires after 7 days.
obclient> CREATE TABLE ttl_tbl1(
id INT PRIMARY KEY,
val VARCHAR(100)
) MERGE_ENGINE = append_only TTL ora_rowscn + INTERVAL 7 DAY BY COMPACTION;
After a TTL table is created, you can modify its TTL strategy based on your business requirements. Example:
obclient> ALTER TABLE ttl_tbl1 SET TTL ora_rowscn + INTERVAL 1 HOUR BY COMPACTION;
If you no longer need to use the TTL feature, you can drop the TTL attribute. Example:
obclient> ALTER TABLE ttl_tbl1 REMOVE TTL;
Trigger TTL tasks
For each table with the TTL attribute defined, the system periodically schedules TTL tasks to process expired data when the TTL feature is enabled. After a TTL task is executed, expired data becomes invisible, but it is not physically deleted. Instead, it is asynchronously deleted during a major compaction, without consuming clogs or write bandwidth. The storage space occupied by expired data is released only after the major compaction task is completed.
Enable the TTL feature
To initiate a TTL task, the TTL feature must be enabled for the current tenant. The TTL feature is controlled by the tenant-level parameter enable_ttl (with a value of True). By default, this parameter is False when a tenant is created or restored, and must be manually enabled.
Log in to the
systenant or a user tenant of the cluster as an administrator.The following example shows how to connect to the database. Adjust the connection parameters based on your actual environment.
obclient -h10.xx.xx.xx -P2883 -uroot@mysqltenant#obdemo -p***** -AExecute the following statements to enable the TTL feature.
The sys tenant enables the TTL feature for a specified tenant.
obclient(root@sys)[(none)]> ALTER SYSTEM SET enable_ttl = True TENANT = tenant_name;A user tenant enables the TTL feature for itself.
obclient> ALTER SYSTEM SET enable_ttl = True;
Configure the scheduled trigger time for TTL tasks
After the TTL feature is enabled, the system periodically triggers TTL tasks based on the expiration status of data in the TTL tables of the tenant. The scheduled trigger time of TTL tasks is controlled by the tenant-level parameter ttl_duty_time, which defaults to 01:00 every day.
ttl_duty_time is based on the tenant's time zone. You are advised to set it during off-peak hours of the tenant and before the daily major compaction. For example, you can set it to 02:00.
To modify the scheduled trigger time of TTL tasks, perform the following steps:
Log in to the
systenant or a user tenant of the cluster as an administrator.The following example shows how to connect to the database. Adjust the connection parameters based on your actual environment.
obclient -h10.xx.xx.xx -P2883 -uroot@mysqltenant#obdemo -p***** -AExecute the following statement to modify the scheduled trigger time for TTL tasks.
The sys tenant modifies the scheduled trigger time of TTL tasks for a specified tenant.
obclient(root@sys)[(none)]> ALTER SYSTEM SET ttl_duty_time = '02:00' TENANT = tenant_name;A user tenant modifies the scheduled trigger time of its own TTL tasks.
obclient> ALTER SYSTEM SET ttl_duty_time = '02:00';
Manually trigger a TTL task
After the TTL feature is enabled, you can manually trigger a TTL task.
Log in to the
systenant or a user tenant of the cluster as an administrator.The following example shows how to connect to the database. Adjust the connection parameters based on your actual environment.
obclient -h10.xx.xx.xx -P2883 -uroot@sys#obdemo -p***** -AExecute the following statement to manually trigger a TTL task.
The sys tenant manually triggers a TTL task for all user tenants.
obclient(root@sys)[(none)]> ALTER SYSTEM TRIGGER TTL TENANT = all_user;Notice
The sys tenant cannot manually trigger a TTL task for all Meta tenants by using the
ALTER SYSTEM TRIGGER TTL TENANT = all_meta;statement.The sys tenant manually triggers a TTL task for a specified tenant.
obclient> ALTER SYSTEM TRIGGER TTL TENANT = tenant_name;A user tenant manually triggers a TTL task for itself.
obclient> ALTER SYSTEM TRIGGER TTL;
TTL task status
You can query the CDB_OB_TTL_TASKS view (sys tenant) or the DBA_OB_TTL_TASKS view (user tenant) to view the details of a TTL task in execution, including the table information, start time, modification time, and execution status.
sys tenant
obclient(root@sys)[(none)]> SELECT * FROM oceanbase.CDB_OB_TTL_TASKS;User tenant
obclient(root@mysqltenant)[(none)]> SELECT * FROM oceanbase.DBA_OB_TTL_TASKS;
The query result in a user tenant is as follows:
+------------+----------+---------+----------------------------+----------------------------+--------------+------------+------------+------------+
| TABLE_NAME | TABLE_ID | TASK_ID | START_TIME | MODIFIED_TIME | TRIGGER_TYPE | STATUS | RET_CODE | TASK_TYPE |
+------------+----------+---------+----------------------------+----------------------------+--------------+------------+------------+------------+
| NULL | -1 | 1 | 2026-01-28 17:31:27.624387 | 2026-01-28 17:31:27.624387 | USER | TRIGGERING | OB_SUCCESS | COMPACTION |
+------------+----------+---------+----------------------------+----------------------------+--------------+------------+------------+------------+
1 row in set
For more information about the fields in the query result, see CDB_OB_TTL_TASKS and DBA_OB_TTL_TASKS.
After a TTL task is completed, you can query the CDB_OB_TTL_TASK_HISTORY (sys tenant) or DBA_OB_TTL_TASK_HISTORY (user tenant) view to view the history of the completed TTL task.
Notice
The retention period of the history of a TTL task is determined by the tenant-level parameter kv_ttl_history_recycle_interval. By default, the history of a TTL task is retained for 7 days.
Manage TTL tasks
During the execution of a TTL task, you can manually pause, resume, or cancel the task.
Pause a TTL task
After a TTL task is manually or periodically triggered, you can manually pause the TTL task in execution as needed.
Pause all TTL tasks of all user tenants in the sys tenant.
obclient(root@sys)[(none)]> ALTER SYSTEM SUSPEND TTL TENANT = all_user;Pause the TTL tasks of a specified tenant in the sys tenant.
obclient(root@sys)[(none)]> ALTER SYSTEM SUSPEND TTL TENANT = tenant_name;Pause the TTL tasks of the current tenant in the user tenant.
obclient> ALTER SYSTEM SUSPEND TTL;
Resume a paused TTL task
After a TTL task is paused, you can manually resume it.
Resume all TTL tasks of all user tenants in the sys tenant.
obclient(root@sys)[(none)]> ALTER SYSTEM RESUME TTL TENANT = all_user;Resume the TTL tasks of a specified tenant in the sys tenant.
obclient(root@sys)[(none)]> ALTER SYSTEM RESUME TTL TENANT = tenant_name;Resume the TTL tasks of the current tenant in the user tenant.
obclient> ALTER SYSTEM RESUME TTL;
Cancel a TTL task
During the execution of a TTL task, you can manually cancel it as needed.
Cancel all TTL tasks of all user tenants in the sys tenant.
obclient(root@sys)[(none)]> ALTER SYSTEM CANCEL TTL TENANT = all_user;Cancel the TTL tasks of a specified tenant in the sys tenant.
obclient(root@sys)[(none)]> ALTER SYSTEM CANCEL TTL TENANT = tenant_name;Cancel the TTL tasks of the current tenant in the user tenant.
obclient> ALTER SYSTEM CANCEL TTL;