:Archive table statements

Prerequisites

• A PolarDB-X instance that meets one of the following requirements is created:

  • The engine version of the instance is MySQL 5.7 and the instance version is polardb-2.4.0_5.4.19-20240927_xcluster5.4.19-20240920 or later.

  • The engine version of the instance is MySQL 8.0 and the instance version is polardb-2.4.0_5.4.19-20240927_xcluster8.4.19-20240924 or later.

  Note

  For more information about version number, see Release notes.

  For more information about how to check the instance version, see View and update the version of an instance.

• A partitioned table that belongs to a database in AUTO mode and is not partitioned by using the LOCAL PARTITION BY clause is used.

Partitioning scheme

• By default, an archive table is range-partitioned based on the ttl_column value in TTL_EXPR of the TTL definition. The range-partitioning interval is the ttl_interval_definition value in TTL_EXPR of the TTL definition.

  For example, in a TTL table, the TTL column is date_field. The data TTL interval is defined by month, and the data of the last three months is retained. The range-partitioning interval is one month, and the data of one month corresponds to one range partition. Example:

  TTL_EXPR = `date_field` EXPIRE AFTER 3 MONTH TIMEZONE '+08:00'  

• When you create an archive table, the number of automatically generated range partitions and their definitions are determined by the two parameters.

  • ARCHIVE_TABLE_PRE_ALLOCATE: determines the maximum number of partitions to be created for the future when you create an archive table.

  • ARCHIVE_TABLE_POST_ALLOCATE: determines the maximum number of partitions that can be created for the past when you create an archive table.

  For example, the current time is 2024-10-02, the ARCHIVE_TABLE_PRE_ALLOCATE value is 4 (four partitions are created for the future), and the ARCHIVE_TABLE_POST_ALLOCATE value is 3 (three partitions are created for the past). Therefore, the following partitioning scheme can be used for the archive table:

  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)
  )

Modify partition parameters

If you want to modify the ARCHIVE_TABLE_PRE_ALLOCATE or ARCHIVE_TABLE_POST_ALLOCATE value to create more partitions for the future and for the past, you can execute the following statement:

/**
 *Create 12 partitions for the future
 *Create 24 partitions for the past
 */
ALTER TABLE `my_ttl_tbl`
MODIFY TTL
SET 
ARCHIVE_TABLE_PRE_ALLOCATE=12,
ARCHIVE_TABLE_POST_ALLOCATE=24;

Query the archive table definitions

Creating an archive table is essentially a process of creating a CCI. When the archive table is created, the CCI (named in the format of arctmp_Archive table name) is added to the original TTL table. The CCI dedicated to the archive table. For more information, see Overview.

Therefore, you can execute the SHOW CREATE TABLE statement on the TTL table to query the CCI definitions and partitions of the archive table.

For example, if the TTL table is my_ttl_tbl and the archive table is my_arc_tbl, the CCI is arctmp_my_arc_tbl. The archive table my_arc_tbl is essentially a query view that explicitly specifies the CCI arctmp_my_arc_tbl.

• Query all tables.

  show tables;

  +------------------+
  | Tables_in_ttldb |
  +------------------+
  | my_ttl_tbl       |
  | my_arc_tbl       |
  +------------------+
  2 rows in set (0.00 sec)

• Execute the SHOW CREATE TABLE statement on the TTL table.

  show create table my_ttl_tbl\G;

  *************************** 1. row ***************************
         Table: my_ttl_tbl
  Create Table: 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
  1 row in set (0.00 sec)

• Execute the SHOW CREATE TABLE statement on the archive table.

  show create table my_arc_tbl\G;

  *************************** 1. row ***************************
                  View: my_arc_tbl
           Create View: CREATE VIEW `my_arc_tbl` AS SELECT `my_ttl_tbl`.`id`, `my_ttl_tbl`.`date_field`
  FROM `ttldb`.`my_ttl_tbl` FORCE INDEX(`ARCTMP_MY_ARC_TBL`)
  character_set_client: utf8
  collation_connection: utf8_general_ci
  1 row in set (0.00 sec)

Query the archive table data

Using the query view of the archive table my_arc_tbl, you can easily query all archived historical data in the TTL table. Example:

select count(1) from my_arc_tbl;