This topic describes the definition and principle of Time to Live (TTL) tables.

Overview

PolarDB-X allows you to specify the local_partition_definition syntax in a DDL statement to create a TTL table. A TTL table partitions each physical table by time and manages the data in these physical tables based on scheduled tasks. This way, cold data in PolarDB-X instances expires based on scheduled tasks to reduce storage usage.

Scenarios

In some business scenarios, a sudden increase occurs in the volume of business data, and the frequency in which business data are queried decreases significantly over a period of time. If the data is stored in PolarDB-X, storage space cannot be released and data queries become less efficient. In this case, you can use TTL tables to manage data.

Limits

  • TTL tables are supported only in PolarDB-X V5.4.13 and later.
  • TTL tables are supported only by tables partitioned by using the auto mode.

Overview

Each physical table of the TTL table is partitioned by using RANGE based on time. All physical tables that use the global secondary index (GSI) are partitioned in the same mode as the primary table.

New partitions are created and expired partitions are deleted over time. The following table shows the process.

Note By default, PolarDB-X automatically creates new partitions and deletes expired partitions. You can disable the automatic creation and deletion of partitions when you create the table and execute DDL statements to manually create new partitions or delete expired partitions.
456789

The following table shows new partitions that are created and expired partitions that are deleted after a lifecycle.

456789