All Products
Search

This Product

    PolarDB:archive partitioned tables to x-engine format

    Document Center

    PolarDB:archive partitioned tables to x-engine format

    Last Updated:Mar 30, 2026

    As partitioned tables grow, historical data accumulates and drives up storage costs. Data Lifecycle Management (DLM) lets you automatically archive older partitions to X-Engine, PolarDB's high-compression storage engine. Hot data stays in InnoDB for high-performance reads and writes, while archived partitions move to X-Engine at a fraction of the cost — without losing DML write or online DDL support.

    Use cases

    • Time-series data: Order and transaction tables grow indefinitely. Archive partitions older than six months to X-Engine to cut storage costs while keeping recent data fast.

    • Log and audit data: Compliance requires long retention, but logs older than 30 days are rarely accessed. Archive them automatically without manual intervention.

    • Analytical cold data: Keep the current quarter in InnoDB for OLTP workloads and archive earlier quarters to X-Engine for low-cost analytical access.

    How it works

    DLM automates partition archiving through a three-step process:

    1. Define a policy: Attach a DLM policy to a table using CREATE TABLE or ALTER TABLE. The policy specifies how many of the newest partitions to keep in InnoDB. Older partitions beyond that threshold are candidates for archiving.

    2. Trigger execution: The policy runs when you call the dbms_dlm.execute_all_dlm_policies() stored procedure — either manually or via a scheduled MySQL event.

    3. Archive: The system identifies qualifying partitions and converts their storage engine from InnoDB to X-Engine online, without downtime.

    Prerequisites

    Before you begin, make sure your cluster meets the version requirements:

    Archive partitioned tables to X-Engine

    Step 1: Create a DLM archive policy

    A DLM policy defines how many of the newest partitions to keep in InnoDB. When the total partition count exceeds that number, the oldest partitions are marked for archiving.

    Method 1: Define the policy when creating a table

    Add the DLM ADD POLICY clause to a CREATE TABLE statement. The following example creates a sales table with two policies:

    • INTERVAL policy: Automatically creates a new one-year partition when inserted data exceeds the existing range.

    • DLM policy (`policy_part2part`): Archives the oldest partitions to X-Engine whenever the total number of partitions exceeds 3, keeping the three newest in InnoDB.

    CREATE TABLE `sales` (
  `id` int DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL,
  `order_time` datetime NOT NULL,
  primary key (order_time)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
PARTITION BY RANGE  COLUMNS(order_time) INTERVAL(YEAR, 1)
(PARTITION p20200101000000 VALUES LESS THAN ('2020-01-01 00:00:00') ENGINE = InnoDB,
 PARTITION p20210101000000 VALUES LESS THAN ('2021-01-01 00:00:00') ENGINE = InnoDB,
 PARTITION p20220101000000 VALUES LESS THAN ('2022-01-01 00:00:00') ENGINE = InnoDB)
DLM ADD POLICY policy_part2part TIER TO PARTITION ENGINE=XENGINE READ WRITE ON (PARTITIONS OVER 3);

    Method 2: Add the policy to an existing table

    Use ALTER TABLE to attach a DLM policy to an existing partitioned table. For full syntax details, see Create or delete DLM policies using ALTER TABLE.

    ALTER TABLE sales
DLM ADD POLICY policy_part2part TIER TO PARTITION ENGINE=XENGINE ON (PARTITIONS OVER 3);

    Policy syntax: ON (PARTITIONS OVER N) controls the archive threshold. N is the number of newest InnoDB partitions to retain. When the total count exceeds N, the oldest partitions beyond the limit are archived.

    Insert test data

    Insert test records to trigger the INTERVAL policy and auto-create new partitions:

    DROP PROCEDURE IF EXISTS proc_batch_insert;
delimiter $$
CREATE PROCEDURE proc_batch_insert(IN begin INT, IN end INT, IN name VARCHAR(20))
BEGIN
SET @insert_stmt = concat('INSERT INTO ', name, ' VALUES(? , ?, ?);');
PREPARE stmt from @insert_stmt;
WHILE begin <= end DO
SET @ID1 = begin;
SET @NAME = CONCAT(begin+begin*281313, '@stiven');
SET @TIME = from_days(begin + 737600);
EXECUTE stmt using @ID1, @NAME, @TIME;
SET begin = begin + 1;
END WHILE;
END;
$$
delimiter ;

CALL proc_batch_insert(1, 3000, 'sales');

    Expected output:

    Query OK, 1 row affected (0.50 sec)

    Verify the table schema — at this point all nine partitions (three original plus six auto-created) use InnoDB:

    SHOW CREATE TABLE sales \G
    *************************** 1. row ***************************
       Table: sales
Create Table: CREATE TABLE `sales` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL,
  `order_time` datetime NOT NULL,
  PRIMARY KEY (`order_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50500 PARTITION BY RANGE  COLUMNS(order_time) */ /*!99990 800020200 INTERVAL(YEAR, 1) */
/*!50500 (PARTITION p20200101000000 VALUES LESS THAN ('2020-01-01 00:00:00') ENGINE = InnoDB,
 PARTITION p20210101000000 VALUES LESS THAN ('2021-01-01 00:00:00') ENGINE = InnoDB,
 PARTITION p20220101000000 VALUES LESS THAN ('2022-01-01 00:00:00') ENGINE = InnoDB,
 PARTITION _p20230101000000 VALUES LESS THAN ('2023-01-01 00:00:00') ENGINE = InnoDB,
 PARTITION _p20240101000000 VALUES LESS THAN ('2024-01-01 00:00:00') ENGINE = InnoDB,
 PARTITION _p20250101000000 VALUES LESS THAN ('2025-01-01 00:00:00') ENGINE = InnoDB,
 PARTITION _p20260101000000 VALUES LESS THAN ('2026-01-01 00:00:00') ENGINE = InnoDB,
 PARTITION _p20270101000000 VALUES LESS THAN ('2027-01-01 00:00:00') ENGINE = InnoDB,
 PARTITION _p20280101000000 VALUES LESS THAN ('2028-01-01 00:00:00') ENGINE = InnoDB) */

    Step 2: Execute the DLM archive policy

    Both methods call the same stored procedure: dbms_dlm.execute_all_dlm_policies(). Choose based on your operational needs.

    Scheduled execution (recommended for production)

    Create a MySQL event to run the policy automatically during off-peak hours. The following example runs daily at 01:00, starting February 1, 2026:

    CREATE EVENT dlm_system_base_event
       ON SCHEDULE EVERY 1 DAY
    STARTS '2026-02-01 01:00:00'
    do CALL
dbms_dlm.execute_all_dlm_policies();

    Manual immediate execution

    Trigger all defined DLM policies on demand:

    CALL dbms_dlm.execute_all_dlm_policies();

    Step 3: Verify archive status and results

    Check the policy definition

    Query mysql.dlm_policies to confirm the policy was created successfully:

    SELECT * FROM mysql.dlm_policies WHERE Table_schema = 'your_database' AND Table_name = 'sales'\G
    *************************** 1. row ***************************
                   Id: 1
         Table_schema: your_database
           Table_name: sales
          Policy_name: policy_part2part
          Policy_type: PARTITION
         Archive_type: PARTITION COUNT
         Storage_mode: READ WRITE
       Storage_engine: XENGINE
        Storage_media: DISK
  Storage_schema_name: NULL
   Storage_table_name: NULL
      Data_compressed: ON
 Compressed_algorithm: Zstandard
              Enabled: ENABLED
      Priority_number: 200
Tier_partition_number: 3
       Tier_condition: NULL
           Extra_info: {"oss_file_filter": "order_time"}
              Comment: NULL

    Key fields:

    Field Description
    Table_schema, Table_name The database and table the policy is attached to
    Policy_name The custom name you assigned to the policy
    Storage_engine The target storage engine — XENGINE in this case
    Tier_partition_number The number of InnoDB partitions to retain — the N in PARTITIONS OVER N

    Monitor execution progress

    Query mysql.dlm_progress during or after policy execution to track the task:

    SELECT * FROM mysql.dlm_progress WHERE Table_schema = 'your_database' AND Table_name = 'sales' ORDER BY Id DESC LIMIT 1\G
    *************************** 1. row ***************************
                  Id: 1
        Table_schema: your_database
          Table_name: sales
         Policy_name: policy_part2part
         Policy_type: PARTITION
      Archive_option: PARTITIONS OVER 3
      Storage_engine: XENGINE
       Storage_media: DISK
     Data_compressed: ON
Compressed_algorithm: Zstandard
  Archive_partitions: p20200101000000, p20210101000000, p20220101000000, _p20230101000000, _p20240101000000, _p20250101000000
       Archive_stage: ARCHIVE_COMPLETE
  Archive_percentage: 100
  Archived_file_info: null
          Start_time: 2026-02-06 10:50:00
            End_time: 2026-02-06 10:50:00
          Extra_info: null

    Key fields:

    Field Description
    Archive_partitions The partitions archived in this run
    Archive_stage ARCHIVE_COMPLETE = success; ARCHIVE_ERROR = failure
    Archive_percentage Task completion percentage
    Start_time, End_time Start and end times of the archive task
    Extra_info Error details when Archive_stage is ARCHIVE_ERROR

    Verify the final table schema

    After archiving completes, run SHOW CREATE TABLE to confirm the storage engine conversion. The six oldest partitions should now show ENGINE = XENGINE, while the three newest remain in InnoDB:

    SHOW CREATE TABLE sales\G
    *************************** 1. row ***************************
       Table: sales
Create Table: CREATE TABLE `sales` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL,
  `order_time` datetime NOT NULL,
  PRIMARY KEY (`order_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!99990 800020216 PARTITION BY RANGE  COLUMNS(order_time) */ /*!99990 800020200 INTERVAL(YEAR, 1) */
/*!99990 800020216 (PARTITION p20200101000000 VALUES LESS THAN ('2020-01-01 00:00:00') ENGINE = XENGINE,
 PARTITION p20210101000000 VALUES LESS THAN ('2021-01-01 00:00:00') ENGINE = XENGINE,
 PARTITION p20220101000000 VALUES LESS THAN ('2022-01-01 00:00:00') ENGINE = XENGINE,
 PARTITION _p20230101000000 VALUES LESS THAN ('2023-01-01 00:00:00') ENGINE = XENGINE,
 PARTITION _p20240101000000 VALUES LESS THAN ('2024-01-01 00:00:00') ENGINE = XENGINE,
 PARTITION _p20250101000000 VALUES LESS THAN ('2025-01-01 00:00:00') ENGINE = XENGINE,
 PARTITION _p20260101000000 VALUES LESS THAN ('2026-01-01 00:00:00') ENGINE = InnoDB,
 PARTITION _p20270101000000 VALUES LESS THAN ('2027-01-01 00:00:00') ENGINE = InnoDB,
 PARTITION _p20280101000000 VALUES LESS THAN ('2028-01-01 00:00:00') ENGINE = InnoDB) */

    Going live

    Choose the right value for N

    Set N based on how frequently your business accesses recent data:

    Data type Recommended retention Rationale
    Order data Most recent 6 months in InnoDB Orders within 6 months are queried frequently; older data is rarely accessed
    Log data Last 30 days in InnoDB Logs older than 30 days are rarely queried and compress well in X-Engine

    Monitor for archive errors

    Set up an alert on the Archive_stage field in mysql.dlm_progress. When the status changes to ARCHIVE_ERROR, inspect the Extra_info field for error details and resolve the issue before the next scheduled run.

    To check for recent errors:

    SELECT Table_schema, Table_name, Policy_name, Archive_stage, Extra_info, End_time
FROM mysql.dlm_progress
WHERE Archive_stage = 'ARCHIVE_ERROR'
ORDER BY End_time DESC
LIMIT 10\G