Interval range partitioning concept and application scenarios - PolarDB

Interval range partitioning extends range partitioning with automatic partition creation. When you insert a row that falls outside all existing partition boundaries, PolarDB for MySQL creates the necessary partitions automatically based on the interval defined in the INTERVAL clause — no manual ADD PARTITION statements required.

Without interval partitioning, inserting data beyond the highest partition boundary returns an error. With it, the insert triggers partition creation and then succeeds.

How it works

When you create an interval range partitioned table, you define an initial transition point — the upper boundary of the first manually defined partition. Any insert beyond that boundary causes the database to create all intermediate partitions up to the one that covers the inserted value.

Example: The interval is one month and the transition point is September 15, 2021. Inserting a row with the date December 10, 2021 causes PolarDB to create three new partitions covering October, November, and December 2021, then inserts the row into the December partition.

The following example creates a sales table partitioned monthly by order_time, with an initial partition covering dates before September 1, 2021:

CREATE TABLE sales
(
  id BIGINT,
  uid BIGINT,
  order_time DATETIME
)
PARTITION BY RANGE COLUMNS(order_time) INTERVAL(MONTH, 1)
(
  PARTITION p0 VALUES LESS THAN('2021-9-1')
);

Insert a row dated November 11, 2021:

INSERT INTO sales VALUES(1, 1010101010, '2021-11-11');

Run SHOW CREATE TABLE to inspect the updated table definition:

CREATE TABLE `sales` (
  `id` bigint(20) DEFAULT NULL,
  `uid` bigint(20) DEFAULT NULL,
  `order_time` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50500 PARTITION BY RANGE  COLUMNS(order_time) */ /*!99990 800020200 INTERVAL(MONTH, 1) */
/*!50500 (PARTITION p0 VALUES LESS THAN ('2021-9-1') ENGINE = InnoDB,
 PARTITION _p20211001000000 VALUES LESS THAN ('2021-10-01 00:00:00') ENGINE = InnoDB,
 PARTITION _p20211101000000 VALUES LESS THAN ('2021-11-01 00:00:00') ENGINE = InnoDB,
 PARTITION _p20211201000000 VALUES LESS THAN ('2021-12-01 00:00:00') ENGINE = InnoDB) */

Three partitions — _p20211001000000, _p20211101000000, and _p20211201000000 — were created automatically to cover the gap between the transition point and the inserted date.

Partition names prefixed with _p are reserved by the system. You cannot use _p-prefixed names when running ADD PARTITION or REORGANIZE PARTITION. You can use them with DROP PARTITION.

You can manually add partitions before inserted values reach the transition point. However, data insertion may fail due to accidental operations. Using an interval range partitioned table eliminates this risk by ensuring partitions are created automatically on demand.

Use cases

Use interval range partitioning when your workload matches one or more of these patterns:

Time-series data ingestion — Data arrives continuously and is naturally ordered by a timestamp column (order date, event time, log timestamp). Interval partitioning keeps new data organized without any schema maintenance between ingestion cycles.
Rolling window maintenance — You keep a fixed window of recent data online and retire old partitions on a schedule. For example, to maintain 12 months of sales data, drop the oldest monthly partition with DROP PARTITION each month without touching the rest of the table. Because interval partitioning handles partition creation automatically, you only need to manage removal.
Avoiding manual partition management — When manual creation of partitions is not preferred, interval partitioning ensures new partitions are created automatically as data arrives, reducing operational overhead.

Limitations

Before adopting interval range partitioning, check the following constraints:

8,192-partition limit — PolarDB enforces a maximum of 8,192 partitions per table. With a monthly interval, this covers approximately 682 years; with a daily interval, roughly 22 years. Choose an interval granularity that keeps the total partition count well below this limit over your expected data retention period.
Reserved partition name prefix — Auto-created partitions use names prefixed with _p. Avoid this prefix in any partition names you manage manually.

You can use interval range partitioning with the partitioned table lifecycle management solution to create partitions and migrate cold data partitions to Object Storage Service (OSS) on a regular schedule, keeping the active partition count manageable.