Configure TTL to Auto-Clean Expired Data - PolarDB

PolarDB for MySQL automatically deletes rows that exceed a defined age using time to live (TTL) — no application-side cleanup jobs required.

Common use cases:

Delete expired verification codes or short-lived tokens on a schedule
Remove stale historical orders or log entries automatically
Clean up intermediate computation results after processing completes

Version requirements

MySQL version	Minimum minor version
8.0.1	8.0.1.1.49.2
8.0.2	8.0.2.2.29.2

To find your current kernel version, see the Kernel Version Guide.

Before you begin

TTL cleanup is disabled by default. Before TTL takes effect, enable the loose_innodb_enable_ttl_purge parameter in the PolarDB console and set it to ON.

Limitations

TTL cannot be set on temporary tables, including local and global temporary tables.
Tables with TTL do not support IMCI, Global Secondary Index (GSI), or partitioned tables.
A TTL-enabled table cannot be the primary table in a foreign key constraint referenced by another table.
Triggers cannot be defined on a TTL-enabled table.
The TTL column must be of the TIMESTAMP type.
TTL cleanup does not generate binlog records. If a source database table has TTL enabled, key conflicts may occur when replicating to a secondary database.
Expired data is not deleted immediately. The background cleanup task runs on a schedule, so clients may still read expired rows until the cleanup task processes them. If your workload requires strict data expiry guarantees, evaluate whether this behavior is acceptable before enabling TTL.
When restoring data from a backup or a database dump, set loose_innodb_enable_ttl_purge to OFF to prevent the restored data from being immediately purged as expired.

Syntax

Create a table with TTL

TTL is declared in the CREATE TABLE statement. Use either second-based syntax or interval syntax.

Second-based syntax — expire rows 100 seconds after the created_at value:

CREATE TABLE `t1` (
  `a` INT PRIMARY KEY,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  KEY idx_created_at (`created_at`)
) ENGINE=InnoDB TTL='created_at@100';

TTL='created_at@100' marks rows as expired 100 seconds after the created_at value. The background cleanup task deletes them during its next scheduled run.

Interval syntax — expire rows 3 hours after the created_at value:

CREATE TABLE `t1` (
  `a` INT PRIMARY KEY,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  KEY idx_created_at (`created_at`)
) ENGINE=InnoDB TTL='created_at' + INTERVAL 3 HOUR;

Interval syntax supports YEAR, QUARTER, MONTH, WEEK, DAY, HOUR, MINUTE, and SECOND as time units.

Modify a table's TTL

Use ALTER TABLE to change the TTL value on an existing table.

Set the TTL to 10,000 seconds:

ALTER TABLE `t1` TTL='created_at@10000';

Set the TTL to 3 days:

ALTER TABLE `t1` TTL='created_at' + INTERVAL 3 DAY;

Remove TTL from a table

ALTER TABLE t1 TTL = '';

Query a table's TTL setting

SHOW CREATE TABLE `t1` FULL;

The output includes the TTL configuration in the CREATE TABLE definition:

CREATE TABLE `t1` (
  `a` INT PRIMARY KEY,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  KEY idx_created_at (`created_at`)
) ENGINE=InnoDB TTL='created_at@259200';

Parameters

The following global parameters control TTL cleanup behavior. Modify them in the PolarDB console.

loose_innodb_enable_ttl_purge is OFF by default. Enable it manually in the console before TTL cleanup takes effect.

Parameter	Default	Description
`loose_innodb_enable_ttl_purge`	OFF	Enables (`ON`) or disables (`OFF`) TTL-based cleanup.
`loose_innodb_ttl_min_interval`	100	Minimum allowed TTL interval, in seconds.
`loose_innodb_ttl_purge_thread`	4	Number of threads used for cleanup. Restart `innodb_enable_ttl_purge` after changing this value.
`loose_innodb_ttl_cluster_index_purge_batch_size`	10,000	Rows scanned per batch when the TTL column has no index (scans the primary key).
`loose_innodb_ttl_index_purge_batch_size`	500	Rows scanned per batch when the TTL column has an index.
`loose_innodb_ttl_purge_start_hour`	0	Start hour for cleanup tasks (0–23). Must not exceed `loose_innodb_ttl_purge_end_hour`.
`loose_innodb_ttl_purge_end_hour`	0	End hour for cleanup tasks (0–23). Must not be less than `loose_innodb_ttl_purge_start_hour`.
`loose_innodb_ttl_finished_job_expired_days`	90	Retention period for records in `mysql.ttl_job_history`, in days. Valid values: 1–365.

Monitor TTL

PolarDB records TTL cleanup activity in the mysql.ttl_job_history system table, updated at regular intervals.

Column	Description
`job_id`	ID of the cleanup job. Usually a millisecond-level timestamp.
`table_name`	Name of the table targeted by this cleanup job.
`state`	Job status: `suspended`, `running`, or `completed`.
`start_time`	Time the job started.
`finished_time`	Time the job completed.
`expire_time`	Expiration time of the data cleaned by this job.
`scan_cost`	Time spent scanning the current batch.
`purge_cost`	Time spent deleting rows in the current batch.
`purge_rows`	Number of rows deleted by this job.