Interval range partitioning

Interval range partitioning is an extension of range partitioning. It allows a database to automatically create a partition when data to be inserted exceeds the range of an existing partition.

Prerequisites

The version of the cluster must be PolarDB for MySQL 8.0.2 and the revision version of the cluster must be 8.0.2.2.0 or later. For information about how to view the kernel version of your cluster, see Query the engine version.

Background information

When you insert data into a range partitioned table and if the data falls beyond the range of the existing partition, the data cannot be inserted and an error is returned. When the data to be inserted exceeds the range of an interval partitioned table, the database can automatically create new partitions based on the range specified by the INTERVAL clause.

If the partition range is set to one month and the data to be inserted is two months from the current transition point (the maximum boundary value of the current partition), the partition for the month of the data and the partition for the month before that month are created. For example, you create an interval range partitioned table. The interval is one month and the current transition point is September 15, 2021. If you attempt to insert the data of December 10, 2021, the database automatically creates three partitions for the three intervals from September 15, 2021 to December 15, 2021. Then, the data is inserted into the partition for the interval that covers December 10, 2021.

Limits

COLUMNS() supports only a single partition key, which must be a numerical or date range.
You must define at least one range partition.
Partitions can only be created in ascending order.
DATE partition keys cannot use TIME intervals (HOUR, MINUTE, or SECOND). TIME partition keys cannot use DATE intervals (YEAR, QUARTER, MONTH, WEEK, or DAY). SECOND partition keys require an interval of at least 60.
INSERT SELECT and UPDATE statements cannot add partitions.
The interval cannot be changed after table creation.
Subpartitioning is supported only for range-hash partitioned tables.
LOAD DATA requires the existing partition range to cover all imported data.
Triggers, stored functions, and stored procedures cannot insert data into an interval range partition.
The LOCK TABLES statement is not supported.
Partition keys cannot be AUTO_INCREMENT, virtual column, generated default column, or of the STRING type.
Custom partitions (including those from CREATE TABLE and DDL statements) cannot use the _p prefix.
Automatically created partitions cannot be rolled back.
Batch inserts can create up to 30 partitions at a time.
Executing a DDL statement while INSERT is auto-creating partitions may cause a deadlock error.
Interval range partitioning is an extension of range partitioning. When interval range partitioning is not applicable, use range partitioning instead.

Usage

You can set the following parameters to enable or set interval range partitioning.

Parameter	Description
partition_level_mdl_enabled	Interval range partitioning is implemented based on the partition level MDL feature. The advantage is that the smaller granularity of Minimum Description Length (MDL) can reduce the impact of DDL statements on DML statements. Therefore, to use interval range partitioning, you must ensure that the partition_level_mdl_enabled parameter is set to ON. If this parameter is set to OFF, you cannot create an interval range partitioned table. For an existing interval range partitioned table, you can insert only the values that are within the range of the partition. If you insert a value beyond the range, an error is reported.
interval_partitioning_enabled	The interval_partitioning_enabled parameter controls the Interval range partitioning feature, while the partition_level_mdl_enabled parameter controls the partition level MDL feature. Both of the parameters are required for the Interval range partitioning feature. The two parameters must be set to ON. If the interval_partitioning_enabled parameter is set to OFF, you cannot create an interval range partitioned table. For an existing interval range partitioned table, you can insert only the values that are within the range of the partition. If you insert a value beyond the range, an error is reported.
transaction_isolation	The partition level MDL feature can only provide isolation at the read-committed or read-uncommitted level. Therefore, this parameter must be consistent with the isolation level of the partition level MDL feature. By default, cloud cluster uses the read-committed isolation level.