Create an archive table

Row-based archiving

• For engine version MySQL 5.7, the instance version must be polardb-2.4.0_5.4.19-20240927_xcluster5.4.19-20240920 or later.

• For engine version MySQL 8.0, the instance version must be polardb-2.4.0_5.4.19-20240927_xcluster8.4.19-20240924 or later.

Partition-based archiving

• For engine version MySQL 5.7, the instance version must be polardb-2.5.0_5.4.20-20250328_xcluster5.4.20-20250221 or later.

• For engine version MySQL 8.0, the instance version must be polardb-2.5.0_5.4.20-20250328_xcluster8.4.20-20250304 or later.

Syntax

CREATE TABLE #archiveTableName 
LIKE #ttlTableName
ENGINE = 'Columnar' ARCHIVE_MODE = 'TTL';

Parameters:

Example

Create an archive table for the TTL table my_ttl_tbl:

/*+TDDL:cmd_extra(ENABLE_ASYNC_DDL=true, PURE_ASYNC_DDL_MODE=true)*/
CREATE TABLE `my_arc_tbl`
LIKE `my_ttl_tbl`
ENGINE = 'Columnar' ARCHIVE_MODE = 'TTL';

Partitioning scheme for archive tables

By default, an archive table uses RANGE partitioning on the time column specified in the TTL definition. The partition interval matches the time unit in the TTL definition. For example, if the TTL is defined as TTL_EXPR = `date_field` EXPIRE AFTER 3 MONTH TIMEZONE '+08:00' , the corresponding archive table is partitioned by month.

The ARCHIVE_TABLE_PRE_ALLOCATE and ARCHIVE_TABLE_POST_ALLOCATE parameters control the number of pre-allocated partitions and backfilled partitions, respectively. For example, if the current date is '2024-10-02', ARCHIVE_TABLE_PRE_ALLOCATE is set to 4 (to pre-allocate four future partitions), and ARCHIVE_TABLE_POST_ALLOCATE is set to 3 (to backfill up to three past partitions), the resulting partitions for the archive table are as follows:

PARTITION BY RANGE COLUMNS(`date_field`) (
  PARTITION `pstart` VALUES LESS THAN ('1970-01-02 00:00:00'),
  PARTITION `p202407` VALUES LESS THAN ('2024-08-01 00:00:00'),
  PARTITION `p202408` VALUES LESS THAN ('2024-09-01 00:00:00'),
  PARTITION `p202409` VALUES LESS THAN ('2024-10-01 00:00:00'),
  PARTITION `p202410` VALUES LESS THAN ('2024-11-01 00:00:00'), -- The partition for the current time: 2024-10-02
  PARTITION `p202411` VALUES LESS THAN ('2024-12-01 00:00:00'),
  PARTITION `p202412` VALUES LESS THAN ('2025-01-01 00:00:00'),
  PARTITION `p202501` VALUES LESS THAN ('2025-02-01 00:00:00'),
  PARTITION `pm` VALUES LESS THAN (MAXVALUE)
)

Example

To pre-allocate 12 partitions for future time ranges and backfill 24 partitions for past time ranges:

ALTER TABLE `my_ttl_tbl`
MODIFY TTL
SET 
ARCHIVE_TABLE_PRE_ALLOCATE=12,
ARCHIVE_TABLE_POST_ALLOCATE=24;

View the archive table definition

An archive table is based on a columnar index. When you create an archive table, the system automatically creates a dedicated columnar index on the source table. The name of this index follows the format arctmp_ + archive table name. You can use the SHOW CREATE TABLE statement to view the definitions of the columnar index and its partitions.

• View the structure of the source table. In the output, arctmp_my_arc_tbl is the dedicated columnar index for the archive table.

  SHOW CREATE TABLE my_ttl_tbl;

  Example output:

  CREATE TABLE `my_ttl_tbl` (
          `id` int(11) NOT NULL AUTO_INCREMENT,
          `date_field` datetime DEFAULT CURRENT_TIMESTAMP,
          PRIMARY KEY (`id`),
          CLUSTERED COLUMNAR INDEX `arctmp_my_arc_tbl` (`date_field`)
                  PARTITION BY RANGE COLUMNS(`date_field`)
                  (PARTITION `pstart` VALUES LESS THAN ('1970-01-02 00:00:00') ENGINE = Columnar,
                   PARTITION `p201903` VALUES LESS THAN ('2019-04-01 00:00:00') ENGINE = Columnar,
                   ...
                   PARTITION `p202506` VALUES LESS THAN ('2025-07-01 00:00:00') ENGINE = Columnar,
                   PARTITION `p202507` VALUES LESS THAN ('2025-08-01 00:00:00') ENGINE = Columnar)
  ) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 TTL = TTL_DEFINITION( TTL_ENABLE = 'OFF', TTL_EXPR = `date_field` EXPIRE AFTER 2 MONTH TIMEZONE '+08:00', TTL_JOB = CRON '0 0 1 * * ? *' TIMEZONE '+08:00', ARCHIVE_TYPE = 'COLUMNAR', ARCHIVE_TABLE_NAME = 'my_arc_tbl', ARCHIVE_TABLE_PRE_ALLOCATE = 12, ARCHIVE_TABLE_POST_ALLOCATE = 64 )
  PARTITION BY KEY(`id`)
  PARTITIONS 8

• View the structure of the archive table. The archive table my_arc_tbl is a query view that explicitly uses the arctmp_my_arc_tbl columnar index.

  SHOW CREATE TABLE MY_ARC_TBL;

  Example output:

  CREATE VIEW `my_arc_tbl` AS SELECT `my_ttl_tbl`.`id`, `my_ttl_tbl`.`date_field`
  FROM `ttldb7`.`my_ttl_tbl` FORCE INDEX(`ARCTMP_MY_ARC_TBL`)
  character_set_client: utf8
  collation_connection: utf8_general_ci

Query data in an archive table

You can use the my_arc_tbl query view to query historical data archived from the source table:

SELECT COUNT(1) FROM my_arc_tbl;