OBKV-Table supports deletion of expired data. You can reduce storage space usage by defining, configuring, and triggering expiration policies. OBKV-Table also filters out expired data so that it is invisible to queries. This topic describes how to delete expired data by using commands or periodic tasks.
Notice
The Time-To-Live (TTL) deletion feature is limited to the KV scenario in OceanBase AI Database and is disabled for SQL scenarios. Otherwise, it may cause unexpected accidental data deletion.
Usage instructions
The basic process for using the TTL feature is as follows:
- Enable the TTL feature: Set
enable_kv_ttl = trueto enable TTL tasks for the tenant. - Define a TTL table: Specify the TTL attribute when you create a table, or add the TTL attribute to an existing table. You can modify or remove the TTL attribute.
- Trigger deletion tasks: Trigger the deletion of expired data by using manual commands or periodic tasks. You can pause, resume, or cancel ongoing tasks.
- View task status: Monitor task execution by using system views.
The following sections describe these steps in detail.
Step 1: Enable the TTL feature
To enable the TTL feature, set the tenant-level parameter enable_kv_ttl to true.
ALTER SYSTEM SET enable_kv_ttl = true; -- Tenant-level parameter. The default value is false.
Step 2: Define a TTL table
The TTL attribute of a table is defined in the COLUMN_NAME + INTERVAL NUM TTL_UNIT format, where:
COLUMN_NAME: A column defined in the table, of theTIMESTAMPorDATETIMEtype.+ INTERVAL: A fixed keyword part.NUM: The integer that represents the expiration time.TTL_UNIT: Any of the unitsSECOND,MINUTE,HOUR,DAY,MONTH, andYEAR.
To create a TTL relational table named t1 and set each row of data to expire after 12 hours, use the following statement:
CREATE TABLE t1 (
a INT PRIMARY KEY,
b TIMESTAMP DEFAULT current_timestamp ON UPDATE current_timestamp)
TTL (b + INTERVAL 12 HOUR);
To change the TTL attribute of an existing table named t1 to 1 day, use the following statement:
ALTER TABLE t1 TTL (b + INTERVAL 1 DAY);
To remove the TTL attribute from a table named t1, use the following statement:
ALTER TABLE t1 REMOVE TTL;
Step 3: Trigger tasks
There are two ways to trigger TTL deletion tasks:
- Trigger by using management commands: Log in to the sys tenant to trigger TTL tasks for all tenants. In addition to triggering tasks, the sys tenant also supports pausing, resuming, and canceling ongoing TTL tasks.
- Trigger by using periodic tasks: Log in to the user tenant in which you want to perform the TTL task and set the daily trigger time for TTL tasks in that tenant.
TTL commands take effect only when executed in a user tenant or the sys tenant. When executed in a user tenant, they can manage only the TTL tasks of that tenant. When executed in the sys tenant, they can manage the TTL tasks of all tenants.
Log in to the sys tenant and run the following command to trigger the TTL task for all tenants.
ALTER SYSTEM TRIGGER TTL;
To pause a running TTL task, run the following command:
ALTER SYSTEM SUSPEND TTL;
To resume a paused TTL task, run the following command:
ALTER SYSTEM RESUME TTL;
To cancel a running TTL task, run the following command:
ALTER SYSTEM CANCEL TTL;
Log in to the tenant in which you want to perform the TTL task and run the following command to set the daily trigger time for TTL tasks in that tenant.
-- The default value is "", which indicates that periodic TTL tasks are not triggered.
ALTER SYSTEM SET kv_ttl_duty_duration = '[22:00:00, 24:00:00]';
Step 4: View task status
You can view the status and related information of current and historical TTL tasks by using the following views:
View name |
Purpose |
|---|---|
| DBA_OB_KV_TTL_TASKS | Views the running TTL tasks of the current tenant. |
| DBA_OB_KV_TTL_TASK_HISTORY | Views the historical TTL tasks of the current tenant. |
| CDB_OB_KV_TTL_TASKS | Views the running TTL tasks of all tenants. Compared with the DBA view, this view has an additional TENANT_ID column. |
| CDB_OB_KV_TTL_TASK_HISTORY | Views the historical TTL tasks of all tenants. Compared with the DBA view, this view has an additional TENANT_ID column. |
Task status description
TTL tasks are classified into tenant-level tasks and tablet-level tasks. Each type has different statuses, all of which can be queried in the DBA_OB_KV_TTL_TASKS, CDB_OB_KV_TTL_TASKS, DBA_OB_KV_TTL_TASK_HISTORY, and CDB_OB_KV_TTL_TASK_HISTORY views.
Notice
For a tenant-level task, the TABLE_NAME is NULL, and both TABLET_ID and TABLE_ID are -1.
Tablet-level task status:
Task status |
Description |
|---|---|
| PREPARED | The task is being prepared. The deletion of expired data has not started. |
| RUNNING | The task is running. The system is deleting expired data in this partition. |
| PENDING | The task is paused. It can be resumed by using commands. |
| CANCELED | The task is canceled. Execution cannot be resumed. |
| FINISHED | The task is finished. The record is moved to the historical table. |
| INVALID | The status is invalid. |
Tenant-level task status:
Task status |
Description |
|---|---|
| RS_TRIGGERING | The tenant-level TTL task is in progress. |
| RS_SUSPENDING | The tenant-level TTL task has been paused. |
| RS_CANCELING | The tenant-level TTL task has been canceled. |
| RS_MOVING | All TTL task records are being moved to the historical table. |
| INVALID | The status is invalid. |
Set the historical task cleanup period (optional)
To avoid unnecessary storage space waste caused by the accumulation of historical TTL task records, you can set the retention period of these records by using the kv_ttl_history_recycle_interval parameter. The default value is 7 days.
For example, to automatically clean up historical TTL task records older than 30 days, use the following command:
ALTER SYSTEM SET kv_ttl_history_recycle_interval = '30d';
Performance optimization
Set parallelism
You can set the number of worker threads for TTL tasks by using the tenant-level parameter ttl_thread_score. The default value is 2.
For example, to change the number of worker threads for TTL tasks to 10:
ALTER SYSTEM SET ttl_thread_score = 10;
Index scan
TTL supports index scans to improve the efficiency of expired data cleanup. By default, a TTL task must perform a primary key scan on the entire table and read each row of data to determine whether it has expired, which incurs high I/O overhead. If you create a local index on the expiration column and specify this index for the TTL task, the task scans only the index to determine whether data has expired, thereby reducing invalid data reads and significantly improving cleanup speed.
When you create a table, you can enable index scans by specifying an index name for the TTLScanIndex sub-parameter of KV_ATTRIBUTES. The syntax is as follows:
-- In practice, $index_name must be replaced with the index name.
CREATE TABLE table_name (
...
) TTL (ttl_column + INTERVAL ...)
KV_ATTRIBUTES = '{"TTLScanIndex": "$index_name"}';
The following examples illustrate different scenarios:
Example 1 demonstrates how a TTL task uses a local index containing the expired column to improve the efficiency of expired data cleanup:
CREATE TABLE test_ttl_with_index (
a INT PRIMARY KEY,
b TIMESTAMP DEFAULT current_timestamp ON UPDATE current_timestamp,
c VARCHAR(128),
INDEX idx(b) LOCAL
) TTL (b + INTERVAL 12 HOUR)
KV_ATTRIBUTES = '{"TTLScanIndex": "idx"}';
Description:
- The table uses column
bas the TTL column, with an expiration time of 12 hours. - The
TTLScanIndexclause specifies to use indexidxfor scanning. - Index
idx, built on the TTL columnb, can efficiently locate expired data.
When you use the heap-organized table model of OBKV-Table, a unique index is usually created as the primary key. You can redundantly store the expiration column in this unique index, and the TTL task can use this index to scan expired data. Example 2 shows how to create a heap-organized table, redundantly store the expiration column in the unique index, and use that index to scan expired data:
CREATE TABLE test_ttl_storing_index (
pagerowkey VARCHAR(1024) NOT NULL,
request LONGTEXT DEFAULT NULL,
rawpage LONGBLOB NOT NULL,
pagecode VARCHAR(1024) NOT NULL,
timestamp BIGINT(20) NOT NULL,
_expire_ts TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
-- Redundantly store _expire_ts on the unique index u_idx_pagerowkey.
UNIQUE INDEX u_idx_pagerowkey (pagerowkey) STORING (_expire_ts)
) ORGANIZATION = HEAP DEFAULT CHARSET = utf8mb4
TTL (_expire_ts + INTERVAL 1 DAY)
-- Scan expired data by using the unique index u_idx_pagerowkey.
KV_ATTRIBUTES = '{"TTLScanIndex": "u_idx_pagerowkey"}';
Description:
- With
STORING (_expire_ts), the TTL column_expire_tsis redundantly stored in the unique indexu_idx_pagerowkey, which avoids querying the base table during the TTL task.
Example 3 shows how to create a table first and then add index idx3 by using ALTER TABLE and enable index scanning:
-- Create a table (index scanning is not used initially).
CREATE TABLE test_ttl_with_index_6 (
a INT PRIMARY KEY,
b TIMESTAMP DEFAULT current_timestamp ON UPDATE current_timestamp,
c VARCHAR(128),
INDEX idx(b) LOCAL,
INDEX idx2(c) LOCAL
) TTL (b + INTERVAL 12 HOUR);
-- Add an index and enable index scanning
ALTER TABLE test_ttl_with_index_6 ADD INDEX idx3(b, c);
ALTER TABLE test_ttl_with_index_6 SET KV_ATTRIBUTES = '{"TTLScanIndex": "idx3"}';
Description:
- Add index
idx3usingALTER TABLEand enable index scanning. - Specify to use index
idx3for scanning expired data viaKV_ATTRIBUTES.
TTL task throttling
TTL tasks continuously consume CPU and log write bandwidth during deletion. When tenant specifications are limited or the write load from the business front end is high, excessive TTL task throughput can affect the latency of front-end requests. To balance backend cleanup efficiency with online business stability, OBKV-Table provides TTL deletion throughput throttling.
By using the tenant-level parameter kv_ttl_delete_max_ops, you can limit the maximum total number of deletion operations per second for TTL tasks in a tenant. This parameter controls the upper limit of the deletion rate but does not guarantee a fixed rate. The actual throughput fluctuates with the system load, resource availability, and task distribution.
When throttling is disabled, TTL deletion tasks use available resources as much as possible. After throttling is enabled:
- The throttling value applies to the combined deletion throughput of all concurrent TTL subtasks in the current tenant.
- The maximum throughput a single subtask can obtain is related to its concurrency level ttl_thread_score and is dynamically allocated among multiple concurrent tasks. For example, when
kv_ttl_delete_max_ops=1000andttl_thread_score=2, it means the TTL task has a concurrency of 2, with at most two TTL tasks running simultaneously. The maximum combined deletion throughput for all TTL tasks does not exceed 1000, and the average maximum deletion throughput per TTL task does not exceed 1000/2 = 500. - In most scenarios, this can reduce the impact of TTL tasks on CPU and log write bandwidth, but it will correspondingly prolong the execution duration of TTL tasks.
We recommend that you enable or tighten throttling in the following scenarios:
- The tenant's resource specification is small, and the volume of data in the TTL table is large.
- During business peak hours, there is a high density of write/update requests from the front end, and latency jitter is sensitive.
- You need to prioritize front-end request latency while also considering backend cleanup efficiency.
You can evaluate the throttling effect and tune it gradually by checking the changes in the deletion count and task duration in DBA_OB_KV_TTL_TASK_HISTORY.
Considerations
Filter change logs
Notice
This feature is supported in OceanBase AI Database V4.6.3 and later, and requires liboblog V4.6.3 or later.
TTL tasks generate a large number of change logs (CLOGs) when deleting expired data. By default, CDC (liboblog) captures and consumes these change logs before synchronizing them to downstream components. In scenarios with a large volume of expired data, the massive amount of logs generated by TTL deletion imposes additional processing pressure and resource consumption on the CDC link.
To reduce the resource burden on the CDC link, you can enable TTL change log filtering on the CDC side (filter_ttl_delete_log). After this feature is enabled, change logs generated by TTL tasks are filtered by CDC and are no longer output downstream. By default, filtering is disabled, which means that TTL change logs are consumed by CDC as usual. Enabling filtering affects only the change logs of TTL tasks and does not change the execution logic of the TTL tasks.
Visibility of expired data
If you operate on a record that has already expired by using the OBKV-Table API, the behavior is the same as if the record did not exist. For example, if an expired record with primary key k1 exists in a relational table, inserting another record with primary key k1 succeeds instead of reporting a primary key conflict.
Action |
Expired |
Return code |
Affected rows |
Remarks |
|---|---|---|---|---|
| insert | N | -5024 OB_ERR_PRIMARY_KEY_DUPLICATE |
0 | |
| insert | Y | 0 OB_SUCCESS |
1 | |
| delete | N | 0 OB_SUCCESS |
1 | |
| delete | Y | 0 OB_SUCCESS |
0 | |
| update | N | 0 OB_SUCCESS |
1 | |
| update | Y | 0 OB_SUCCESS |
0 | |
| replace | N | 0 OB_SUCCESS |
2 | |
| replace | Y | 0 OB_SUCCESS |
1 | |
| insert_or_update | N | 0 OB_SUCCESS |
1 | |
| insert_or_update | Y | 0 OB_SUCCESS |
1 | |
| increment | N | 0 OB_SUCCESS |
1 | |
| increment | Y | 0 OB_SUCCESS |
1 | All columns except the incremented column are set to their default values. |
| append | N | 0 OB_SUCCESS |
1 | |
| append | Y | 0 OB_SUCCESS |
1 | All columns except the appended column are set to their default values. |
| query/get | N | 0 OB_SUCCESS |
/ | The data is read successfully. |
| query/get | Y | 0 OB_SUCCESS |
/ | The data cannot be read. |
Note
The SQL interface does not filter out expired data. Expired data remains visible until it is deleted by the TTL task.
Task execution mechanism
The TTL task is a low-priority background task, and immediate deletion of expired data is not guaranteed. To avoid a significant impact on system performance, the task automatically pauses under the following circumstances:
- Memory pressure: When the MemStore memory
MEMSTORE_USEDexceeds the thresholdFREEZE_TRIGGER, the task pauses until the memory drops below the threshold. You can view memory usage by using theGV$OB_MEMSTOREview. - Physical restore: For tenants undergoing physical restore, the TTL task pauses until the tenant completes the restore.
Storage space release
Because OceanBase AI Database uses an LSM-tree architecture, a delete operation is essentially an append write (writing a delete marker). Therefore, after a TTL task deletes expired data, the actual storage space is not immediately released and may even temporarily increase. The final release of storage space depends on the major compaction process.
To ensure that storage space is released promptly, we recommend that you use TTL tasks together with major freeze:
- Set the execution time window for the TTL task (
kv_ttl_duty_duration). - Set the execution time of major freeze (
major_freeze_duty_time) after the TTL task window.
In this way, major compaction is automatically triggered after the TTL task completes, which releases the storage space.
Examples
Complete TTL process
Enable the TTL feature
ALTER SYSTEM SET enable_kv_ttl = true;Create a TTL relational table and set each record to expire 10 seconds after the value of column
c.CREATE TABLE ttl_table(a VARCHAR(1024) PRIMARY KEY, b VARCHAR(1024), c TIMESTAMP) TTL(c + INTERVAL 10 SECOND) PARTITION BY KEY(a) PARTITIONS 3;Insert data. You can use OBKV-Table or SQL. To insert data by using OBKV-Table, see Connect to a cluster by using the OBKV-Table Java client.
INSERT INTO ttl_table VALUES("k1", "hello obkv", now()); INSERT INTO ttl_table VALUES("k2", "hello obkv", now()); INSERT INTO ttl_table VALUES("k3", "hello obkv", now());Wait for 10 seconds, then execute the command to trigger the TTL task.
ALTER SYSTEM TRIGGER TTL; -- Starting from 0:00, the system attempts to trigger the TTL task once.Check the task execution status.
obclient> SELECT * FROM OCEANBASE.DBA_OB_KV_TTL_TASKS;The query result is as follows:
+------------+----------+-----------+---------+----------------------------+----------------------------+--------------+---------------+-------------+---------------------+----------+------------+ | TABLE_NAME | TABLE_ID | TABLET_ID | TASK_ID | START_TIME | END_TIME | TRIGGER_TYPE | STATUS | TTL_DEL_CNT | MAX_VERSION_DEL_CNT | SCAN_CNT | RET_CODE | +------------+----------+-----------+---------+----------------------------+----------------------------+--------------+---------------+-------------+---------------------+----------+------------+ | ttl_table | 500002 | 200003 | 1 | 2023-09-27 23:31:30.300276 | 2023-09-27 23:31:35.315848 | USER | FINISHED | 2 | 0 | 2 | OB_SUCCESS | | ttl_table | 500002 | 200002 | 1 | 2023-09-27 23:31:30.300276 | 2023-09-27 23:31:35.320258 | USER | FINISHED | 1 | 0 | 1 | OB_SUCCESS | | ttl_table | 500002 | 200001 | 1 | 2023-09-27 23:31:30.300271 | 2023-09-27 23:31:35.321879 | USER | FINISHED | 0 | 0 | 0 | OB_SUCCESS | | NULL | -1 | -1 | 1 | 2023-09-27 23:31:28.675583 | 2023-09-27 23:31:28.675583 | USER | RS_TRIGGERING | 0 | 0 | 0 | OB_SUCCESS | +------------+----------+-----------+---------+----------------------------+----------------------------+--------------+---------------+-------------+---------------------+----------+------------+ 4 rows in set (0.043 sec)After the task completes, the record is moved to the history table, which you can query by using
OCEANBASE.DBA_OB_KV_TTL_TASK_HISTORY.obclient> SELECT * FROM OCEANBASE.DBA_OB_KV_TTL_TASK_HISTORY;The query result is as follows:
+------------+----------+-----------+---------+----------------------------+----------------------------+--------------+----------+-------------+---------------------+----------+------------+ | TABLE_NAME | TABLE_ID | TABLET_ID | TASK_ID | START_TIME | END_TIME | TRIGGER_TYPE | STATUS | TTL_DEL_CNT | MAX_VERSION_DEL_CNT | SCAN_CNT | RET_CODE | +------------+----------+-----------+---------+----------------------------+----------------------------+--------------+----------+-------------+---------------------+----------+------------+ | ttl_table | 500002 | 200003 | 1 | 2023-09-27 23:31:30.300276 | 2023-09-27 23:31:35.315848 | USER | FINISHED | 2 | 0 | 2 | OB_SUCCESS | | ttl_table | 500002 | 200002 | 1 | 2023-09-27 23:31:30.300276 | 2023-09-27 23:31:35.320258 | USER | FINISHED | 1 | 0 | 1 | OB_SUCCESS | | ttl_table | 500002 | 200001 | 1 | 2023-09-27 23:31:30.300271 | 2023-09-27 23:31:35.321879 | USER | FINISHED | 0 | 0 | 0 | OB_SUCCESS | | NULL | -1 | -1 | 1 | 2023-09-27 23:31:28.675583 | 2023-09-27 23:31:42.526853 | USER | FINISHED | 0 | 0 | 0 | OB_SUCCESS | +------------+----------+-----------+---------+----------------------------+----------------------------+--------------+----------+-------------+---------------------+----------+------------+ 4 rows in set (0.058 sec)After the task completes, you can see that all expired data has been deleted.
obclient> SELECT * FROM ttl_table; Empty set (0.060 sec)
Set a rate limit for TTL tasks
Create a TTL relational table and set each record to expire 10 seconds after the value of column
c.CREATE TABLE ttl_table(a VARCHAR(1024) PRIMARY KEY, b VARCHAR(1024), c TIMESTAMP) TTL(c + INTERVAL 10 SECOND) PARTITION BY KEY(a) PARTITIONS 3;Insert data.
INSERT INTO ttl_table VALUES("k1", "hello obkv", now()); INSERT INTO ttl_table VALUES("k2", "hello obkv", now()); INSERT INTO ttl_table VALUES("k3", "hello obkv", now());Set a rate limit for the TTL task.
ALTER SYSTEM SET kv_ttl_delete_max_ops = 1000;Use a view to summarize and calculate the average throughput of TTL tasks deleting expired data over a period of time.
SELECT TABLE_NAME, sum(TTL_DEL_CNT) + sum(MAX_VERSION_DEL_CNT) as total_ttl_del_cnt, min(START_TIME) as START_TIME, max(END_TIME) as END_TIME, CASE WHEN TIMESTAMPDIFF(MICROSECOND, min(START_TIME), max(END_TIME)) > 0 THEN (sum(TTL_DEL_CNT) + sum(MAX_VERSION_DEL_CNT)) * 1000000.0 / TIMESTAMPDIFF(MICROSECOND, min(START_TIME), max(END_TIME)) ELSE -1 END as avg_ops FROM oceanbase.DBA_OB_KV_TTL_TASK_HISTORY WHERE STATUS = 'FINISHED' AND START_TIME >= '2026-03-19 15:49:00' -- Replace with your start time. AND END_TIME <= '2026-03-19 16:10:00' -- Replace with your end time. GROUP BY TABLE_NAME; -- Group and count by table name.View throttling information in logs.
You can query TTL task throttling information from the observer logs. A TTL task prints throttling information every 30 seconds during execution.
obclient> grep "TTL TASK DELETE RATE" -rn observer.log*