Configure and manage dynamic partitioning - ApsaraDB for SelectDB

Dynamic partitioning automates the creation and deletion of range partitions based on rules you define at table creation or runtime. This eliminates manual partition management and prevents import failures on days when partitions don't exist.

Note

Dynamic partitioning supports only range partitions on tables with a single partition key column.

How it works

Dynamic partitioning maintains a rolling window of partitions around the current date. Two offsets define the window:

dynamic_partition.start (negative integer): partitions older than this offset are automatically deleted.
dynamic_partition.end (positive integer): partitions up to this offset ahead of the current date are pre-created.

For example, with time_unit=DAY, start=-1, and end=2, the scheduler always keeps exactly four partitions: yesterday, today, tomorrow, and the day after. As time advances, the scheduler adds partitions at the leading edge and drops them at the trailing edge.

A background scheduler enforces these rules every dynamic_partition_check_interval_seconds seconds (default: 600).

Important

Lost partitions between the current date and dynamic_partition.end are recreated automatically. Lost partitions between dynamic_partition.start and the current date are not recreated.

Create a dynamically partitioned table

Add dynamic_partition.* properties to the PROPERTIES clause when creating a table:

CREATE TABLE tbl1
(...)
PARTITION BY RANGE(k1) ()
DISTRIBUTED BY HASH(k1)
PROPERTIES
(
    "dynamic_partition.enable"    = "true",
    "dynamic_partition.time_unit" = "DAY",
    "dynamic_partition.start"     = "-7",
    "dynamic_partition.end"       = "3",
    "dynamic_partition.prefix"    = "p",
    "dynamic_partition.buckets"   = "32"
);

Configuration reference

Global parameters

These parameters apply at the instance level and affect all dynamically partitioned tables.

Parameter	Default	Description
`dynamic_partition_enable`	`true`	Enables or disables dynamic partitioning globally. Set to `false` to disable for all tables. Does not affect standard tables.
`dynamic_partition_check_interval_seconds`	`600`	How often the scheduling thread runs, in seconds.

Change these at runtime with:

ADMIN SET FRONTEND CONFIG ("dynamic_partition_enable" = "true");
ADMIN SET FRONTEND CONFIG ("dynamic_partition_check_interval_seconds" = "7200");

Table parameters

All table-level parameters are prefixed with dynamic_partition..

Parameter	Required	Default	Description
`dynamic_partition.time_unit`	Yes	—	Scheduling unit: `HOUR`, `DAY`, `WEEK`, or `MONTH`. If set to `HOUR`, the partition key column cannot be of the `DATE` type.
`dynamic_partition.end`	Yes	—	Positive offset. Partitions up to this many units ahead of the current time are pre-created.
`dynamic_partition.prefix`	Yes	—	Name prefix for dynamically created partitions (for example, `p`).
`dynamic_partition.enable`	No	`true`	Enables or disables dynamic partitioning for this table. Set to `false` to disable without removing existing partitions.
`dynamic_partition.start`	No	`-2147483648`	Negative offset. Partitions older than this many units are deleted. The default value effectively disables deletion of historical partitions.
`dynamic_partition.time_zone`	No	`Asia/Shanghai`	Time zone for partition scheduling.
`dynamic_partition.create_history_partition`	No	`false`	When `true`, historical partitions back to `dynamic_partition.start` are created immediately. Has no effect if `dynamic_partition.start` is not set.
`dynamic_partition.history_partition_num`	No	`-1`	Maximum number of historical partitions to create when `create_history_partition=true`. `-1` means no limit (use `start` as the boundary).
`dynamic_partition.reserved_history_periods`	No	`NULL`	Specific time ranges to retain regardless of `start`. Format for `DAY`/`WEEK`/`MONTH`: `[yyyy-MM-dd,yyyy-MM-dd],[...]`. Format for `HOUR`: `[yyyy-MM-dd HH:mm:ss,yyyy-MM-dd HH:mm:ss],[...]`.
`dynamic_partition.buckets`	No	—	Number of buckets in each dynamically created partition. If not set, falls back to the table-level default.
`dynamic_partition.start_day_of_week`	No	`1`	Start day of each week when `time_unit=WEEK`. `1` = Monday, `7` = Sunday.
`dynamic_partition.start_day_of_month`	No	`1`	Start day of each month when `time_unit=MONTH`. Valid values: `1`–`28`. Values 29–31 are not supported to avoid ambiguity across months.

Partition name suffixes

`time_unit`	Suffix format	Example
`HOUR`	`yyyyMMddHH`	`2020032501`
`DAY`	`yyyyMMdd`	`20200325`
`WEEK`	`yyyy_ww`	`2020_13` (13th week of 2020)
`MONTH`	`yyyyMM`	`202003`

Historical partitions

When create_history_partition=true, SelectDB determines how many historical partitions to create based on start and history_partition_num. The number of partitions created (expect_create_partition_num) is calculated as follows:

Condition	Formula
`create_history_partition=false`	`end − 0`
`create_history_partition=true`, `history_partition_num=-1`	`end − start`
`create_history_partition=true`, `history_partition_num=N`	`end − max(start, −N)`

Note

If expect_create_partition_num exceeds max_dynamic_partition_num (default: 500), the excess partitions are not created.

Example: Current date is 2021-05-20, time_unit=DAY, start=-3, end=3.

`history_partition_num`	Partitions created
`1`	p20210519, p20210520–p20210523 (5 total)
`5`	p20210517–p20210523 (7 total)
`-1`	p20210517–p20210523 (7 total)

With history_partition_num=1, the formula gives end − max(start, −1) = 3 − max(−3, −1) = 3 − (−1) = 4 future + current, so only 1 historical partition (p20210519) is created instead of 3.

Examples

Partition by day

Retain the past 7 days and pre-create the next 3 days:

CREATE TABLE tbl1
(
    k1 DATE,
    k2 INT
)
PARTITION BY RANGE(k1) ()
DISTRIBUTED BY HASH(k1)
PROPERTIES
(
    "dynamic_partition.enable"                  = "true",
    "dynamic_partition.time_unit"               = "DAY",
    "dynamic_partition.start"                   = "-7",
    "dynamic_partition.end"                     = "3",
    "dynamic_partition.prefix"                  = "p",
    "dynamic_partition.create_history_partition" = "true",
    "dynamic_partition.buckets"                 = "32"
);

With a current date of 2020-05-29, the following partitions are active:

p20200529: ["2020-05-29", "2020-05-30")
p20200530: ["2020-05-30", "2020-05-31")
p20200531: ["2020-05-31", "2020-06-01")
p20200601: ["2020-06-01", "2020-06-02")

On 2020-05-30, p20200602 is created. On 2020-06-06, p20200529 is deleted (7 days past start).

Partition by week

Retain the past 2 weeks and pre-create the next 2 weeks, starting each week on Monday (default):

CREATE TABLE tbl1
(
    k1 DATETIME,
    ...
)
PARTITION BY RANGE(k1) ()
DISTRIBUTED BY HASH(k1)
PROPERTIES
(
    "dynamic_partition.enable"                  = "true",
    "dynamic_partition.time_unit"               = "WEEK",
    "dynamic_partition.start"                   = "-2",
    "dynamic_partition.end"                     = "2",
    "dynamic_partition.prefix"                  = "p",
    "dynamic_partition.create_history_partition" = "true",
    "dynamic_partition.buckets"                 = "8"
);

With a current date of 2020-05-29 (week 22 of 2020), these partitions are active:

p2020_22: ["2020-05-25 00:00:00", "2020-06-01 00:00:00")
p2020_23: ["2020-06-01 00:00:00", "2020-06-08 00:00:00")
p2020_24: ["2020-06-08 00:00:00", "2020-06-15 00:00:00")

Each partition starts on Monday. Because k1 is DATETIME, timestamps are padded to 00:00:00.

On 2020-06-15 (week 25), p2020_22 is deleted.

To start each week on Wednesday instead, set "dynamic_partition.start_day_of_week" = "3". The partitions shift accordingly:

p2020_22: ["2020-05-27 00:00:00", "2020-06-03 00:00:00")
p2020_23: ["2020-06-03 00:00:00", "2020-06-10 00:00:00")
p2020_24: ["2020-06-10 00:00:00", "2020-06-17 00:00:00")

Note

When a week spans two years, the partition name uses the year of the week's start day. For example, if a week starts on 2019-12-31, the partition is named p2019_53; if it starts on 2020-01-01, the partition is named p2020_01.

Partition by month

Pre-create the next 2 months with no deletion of historical partitions, starting each month on the 3rd:

CREATE TABLE tbl1
(
    k1 DATE,
    ...
)
PARTITION BY RANGE(k1) ()
DISTRIBUTED BY HASH(k1)
PROPERTIES
(
    "dynamic_partition.enable"                  = "true",
    "dynamic_partition.time_unit"               = "MONTH",
    "dynamic_partition.end"                     = "2",
    "dynamic_partition.prefix"                  = "p",
    "dynamic_partition.create_history_partition" = "true",
    "dynamic_partition.history_partition_num"   = "6",
    "dynamic_partition.buckets"                 = "8",
    "dynamic_partition.start_day_of_month"      = "3"
);

With a current date of 2020-05-29 (and start not set, so no deletion):

p202005: ["2020-05-03", "2020-06-03")
p202006: ["2020-06-03", "2020-07-03")
p202007: ["2020-07-03", "2020-08-03")

With start_day_of_month=28 and current date 2020-05-20:

p202004: ["2020-04-28", "2020-05-28")
p202005: ["2020-05-28", "2020-06-28")
p202006: ["2020-06-28", "2020-07-28")

Modify dynamic partitioning parameters

Update parameters at runtime with ALTER TABLE:

ALTER TABLE tbl1 SET (
    "dynamic_partition.time_unit" = "MONTH",
    "dynamic_partition.end"       = "2"
);

Important

Changing time_unit can cause partition range conflicts. For example, if you switch from DAY to MONTH while daily partitions covering May 19–21 exist, the scheduler cannot create the monthly partition ["2020-05-01", "2020-06-01") because it overlaps the existing partitions. The monthly partition ["2020-06-01", "2020-07-01") can be created, but you must manually create partitions for the gap from 2020-05-22 to 2020-05-30.

Delete partitions from a dynamically partitioned table

To manually delete a partition, you must first disable dynamic partitioning. Otherwise, the scheduler may recreate partitions in the future window on its next run.

Disable dynamic partitioning for the table:

 ALTER TABLE tbl1 SET ("dynamic_partition.enable" = "false");

Drop the partition:

 ALTER TABLE tbl1 DROP PARTITION p20200519;

Re-enable dynamic partitioning:

 ALTER TABLE tbl1 SET ("dynamic_partition.enable" = "true");

Monitor scheduling status

Run SHOW DYNAMIC PARTITION TABLES to see the scheduling status of all dynamically partitioned tables in the current database:

mysql> SHOW DYNAMIC PARTITION TABLES;
+-----------+--------+----------+-------------+------+--------+---------+-----------+----------------+---------------------+--------+------------------------+----------------------+-------------------------+
| TableName | Enable | TimeUnit | Start       | End  | Prefix | Buckets | StartOf   | LastUpdateTime | LastSchedulerTime   | State  | LastCreatePartitionMsg | LastDropPartitionMsg | ReservedHistoryPeriods  |
+-----------+--------+----------+-------------+------+--------+---------+-----------+----------------+---------------------+--------+------------------------+----------------------+-------------------------+
| d3        | true   | WEEK     | -3          | 3    | p      | 1       | MONDAY    | N/A            | 2020-05-25 14:29:24 | NORMAL | N/A                    | N/A                  | [2021-12-01,2021-12-31] |
| d5        | true   | DAY      | -7          | 3    | p      | 32      | N/A       | N/A            | 2020-05-25 14:29:24 | NORMAL | N/A                    | N/A                  | NULL                    |
| d4        | true   | WEEK     | -3          | 3    | p      | 1       | WEDNESDAY | N/A            | 2020-05-25 14:29:24 | NORMAL | N/A                    | N/A                  | NULL                    |
| d6        | true   | MONTH    | -2147483648 | 2    | p      | 8       | 3rd       | N/A            | 2020-05-25 14:29:24 | NORMAL | N/A                    | N/A                  | NULL                    |
| d2        | true   | DAY      | -3          | 3    | p      | 32      | N/A       | N/A            | 2020-05-25 14:29:24 | NORMAL | N/A                    | N/A                  | NULL                    |
| d7        | true   | MONTH    | -2147483648 | 5    | p      | 8       | 24th      | N/A            | 2020-05-25 14:29:24 | NORMAL | N/A                    | N/A                  | NULL                    |
+-----------+--------+----------+-------------+------+--------+---------+-----------+----------------+---------------------+--------+------------------------+----------------------+-------------------------+
7 rows in set (0.02 sec)

Key output columns:

Column	Description
`LastUpdateTime`	When the dynamic partitioning parameters were last modified
`LastSchedulerTime`	When the scheduler last ran for this table
`State`	Result of the last scheduling run (`NORMAL` or error)
`LastCreatePartitionMsg`	Error message from the last partition creation attempt, if any
`LastDropPartitionMsg`	Error message from the last partition deletion attempt, if any

Convert between dynamic and manual partitioning

A table can only be in one mode at a time — either dynamic or manual.

Convert a manual table to dynamic partitioning

If you did not set dynamic partitioning at table creation, add the properties at runtime:

ALTER TABLE tbl1 SET (
    "dynamic_partition.enable"    = "true",
    "dynamic_partition.time_unit" = "DAY",
    "dynamic_partition.start"     = "-7",
    "dynamic_partition.end"       = "3",
    "dynamic_partition.prefix"    = "p",
    "dynamic_partition.buckets"   = "32"
);

After enabling, the scheduler takes over partition management. Manual ALTER TABLE ... ADD/DROP PARTITION operations on the dynamic window are no longer needed.

Important

If you set dynamic_partition.start, partitions older than the start offset are deleted on the next scheduling run.

Convert a dynamic table to manual partitioning

Disable dynamic partitioning to resume full manual control:

ALTER TABLE tbl1 SET ("dynamic_partition.enable" = "false");

After disabling, create and drop partitions manually with ALTER TABLE.

Bucket count for dynamically created partitions

Dynamically created partitions use dynamic_partition.buckets, not the table-level DISTRIBUTED BY HASH(...) BUCKETS N default. Partitions you add manually with ALTER TABLE tbl1 ADD PARTITION use the table-level default.

For example:

CREATE TABLE tbl1 (
    k1 INT,
    k2 DATE
)
PARTITION BY RANGE(k2) ()
DISTRIBUTED BY HASH(k1) BUCKETS 3
PROPERTIES
(
    "dynamic_partition.enable"                  = "true",
    "dynamic_partition.time_unit"               = "DAY",
    "dynamic_partition.start"                   = "-3",
    "dynamic_partition.end"                     = "3",
    "dynamic_partition.prefix"                  = "p",
    "dynamic_partition.create_history_partition" = "true",
    "dynamic_partition.buckets"                 = "32"
);

Auto-created partitions: 32 buckets (dynamic_partition.buckets)
Manually added partitions: 3 buckets (table default)

If you do not set dynamic_partition.buckets, auto-created partitions also use the table default.

FAQ

I get "Could not create table with dynamic partition when fe config dynamic_partition_enable is false." What's wrong?

The global dynamic_partition_enable flag is set to false. Run the following command to enable it:

ADMIN SET FRONTEND CONFIG ("dynamic_partition_enable" = "true");