PolarDB:TTL archiving

Overview

Standard TTL works as follows: the background purge thread scans for expired rows and deletes them directly. The deleted data cannot be recovered.

TTL archiving adds an extra step. Within the same transaction that deletes expired rows from the source table, the rows are first inserted into the corresponding archive table. Over time, older partitions are automatically migrated to OSS in the background. This creates a tiered storage structure where hot data resides in the source table and cold data resides in the archive table or OSS.

Key semantics to understand:

• The source table and the archive table are two physically independent tables:

  • There is no automatic UNION or query rewriting. SELECT * FROM source_table reads only the source table and does not return archived data.

  • To query archived data, you must explicitly query the archive table.

• The archive table is always named <source_table_name>__ttl_archived__.

• Cold partitions that have been migrated to OSS are invisible to regular queries by default. The WITH ARCHIVED modifier is required to include them in scans. For details, see Query archived data.

• Archive writes and source table deletions occur within the same transaction, ensuring atomicity: either both the source table deletion and the archive write succeed, or both are rolled back. There is no intermediate state where "the source row was deleted but the archive write failed" (with the exception of backfilling historical data; for details, see Usage notes).

Scope of application

• Cluster version: The cluster must run MySQL 8.0.2 with a minor engine version of 8.0.2.2.37 or later.

• Parameter configuration: To enable TTL archiving, all of the following conditions must be met. If any condition is not met, CREATE TABLE or ALTER TABLE statements with the archiving attribute are rejected. You can enable the following parameters on the Parameters page in the console.

  Condition	Parameter	Description
  Enable the archiving master switch	loose_innodb_ttl_archived_enable = ON	When OFF, DDL statements with the archiving attribute are rejected, and the purge thread does not archive expired rows
  Enable OSS capability	loose_polar_oss_enable = ON	Cold partitions of the archive table must be migrated to OSS. This is enforced during table creation
  Enable TTL purge	loose_innodb_enable_ttl_purge = ON	Archiving is driven by the TTL purge thread

Syntax

-- Add archiving to an existing TTL table (two equivalent formats)
ALTER TABLE `t2` TTL='b@5@archived';
ALTER TABLE `t2` TTL='b' + INTERVAL 5 SECOND AND ARCHIVED;

-- Remove the archiving attribute, keep standard TTL
ALTER TABLE `t2` TTL='b@5';

-- Remove TTL entirely (including archiving)
ALTER TABLE `t2` TTL='';

SHOW CREATE TABLE `t1` FULL;

-- Read only archived partitions that are still local
SELECT a, b FROM t1__ttl_archived__ ORDER BY a;

-- Read including cold partitions that have been migrated to OSS
SELECT a, b FROM t1__ttl_archived__ WITH ARCHIVED ORDER BY a;

Archive table structure

The archive table is automatically derived from the source table structure and is named <source_table_name>__ttl_archived__. It differs from the source table in the following ways:

1. The primary key is a composite key of "source table primary key + TTL column". If the TTL column is already part of the source table primary key, it is not appended again. The TTL column becomes implicitly NOT NULL because it is part of the primary key.

2. UNIQUE indexes on the source table are downgraded to regular (non-unique) indexes in the archive table. This is because the archive table silently skips duplicate primary keys (for details, see Usage notes), and retaining the UNIQUE constraint would conflict with this behavior.

3. The archive table is RANGE-partitioned by the TTL time column. Partition names follow the format ARCHIVED_PARTITION_<upper_bound>. The partition window size is determined by the Partition granularity parameter.

Partition granularity

loose_innodb_ttl_archived_interval_type controls the time window size for partitioning the archive table (independent of the data expiration duration):

A larger partition window results in fewer partitions and larger batches migrated to OSS. A smaller window creates more partitions and migrates cold data more promptly. Choose based on your data volume and query patterns. In most cases, the default DAY is sufficient.

Parameters

Limits

When creating or altering a table with the archiving attribute, the following conditions trigger the error ER_TTL_INFO_CHECK_ERR (9913) (message template: TTL info check err:%s):

All of the above are scenarios where the kernel forcibly rejects the operation during table creation or alteration. The requirement that "the TTL column must be of the TIMESTAMP or DATETIME type" also applies to standard TTL (DATETIME requires loose_innodb_ttl_can_use_datetime=ON).

Usage notes

• Backfilled historical data may be lost: If you backfill a batch of data with very old timestamps into a table that already has archiving enabled, the corresponding archive partitions may have already been migrated to OSS. In this case, after these rows expire and are deleted from the source table, the archive writes are silently discarded (the source table deletion still occurs as usual), resulting in data loss. Avoid backfilling data with timestamps earlier than the current OSS cold partition boundary into an archived table. If backfilling is necessary, first evaluate whether the target partition has already been migrated to OSS.

• Duplicate primary keys are silently skipped: The archive table keeps the first written version. When a duplicate primary key (identical composite primary key) is encountered, it is silently skipped without an error.

• Rows with NULL TTL column values never expire and therefore are never archived.

• The archive table is writable but subject to visibility controls: Archive partitions can still be written to. WITH ARCHIVED only affects SELECT visibility and does not affect the scope of UPDATE or DELETE operations.

• Recovery scenarios: When performing database or backup recovery, we recommend that you set loose_innodb_enable_ttl_purge to OFF first to prevent recovered data from being immediately purged or archived due to expiration.

• No Binlog writing: Consistent with standard TTL, archiving-related deletions or writes are not recorded in the Binlog. The primary and replica nodes each rely on their own purge threads for execution. Do not rely on Binlog replication for archiving results.

Observability

The mysql.ttl_job_history table records each TTL purge task (job_id / table_name / state / start_time / finished_time / expire_time / scan_cost / purge_cost / purge_rows, etc.). Archiving events (migration success or failure, discards, duplicate primary key skips) are written to the instance error log. Use the log to troubleshoot archiving behavior.