Dynamic partitioning is a feature provided by OceanBase AI Database that automatically manages partitions, allowing tables to create and delete partitions based on preset rules. This simplifies operations and maintenance and adapts to data growth and expiration requirements.
Features
Creates new partitions regularly based on
TIME_UNIT(such as hours, days, months, etc.), ensuring data automatically expands over time.- Partition boundaries are divided based on the time unit (such as
hour,day), suitable for scenarios where data is stored in time series (such as logs and transaction records).
- Partition boundaries are divided based on the time unit (such as
Pre-creates future partitions using
PRECREATE_TIMEto avoid partition shortages caused by sudden data surges.Periodically cleans up expired partitions based on
EXPIRE_TIMEto free up storage space.Supports custom time zones (
TIME_ZONE) to ensure data consistency across different time zones.Supports configuration of
biginttype timestamp precision (such asBIGINT_PRECISION) to accommodate different time precision requirements.
Pre-create partitions
When performing dynamic partition management, the system creates partitions at intervals of TIME_UNIT until the maximum partition boundary exceeds now() + precreate_time. The boundary of the first partition created is the existing maximum partition boundary rounded up according to TIME_UNIT.
Here is an example:
Create a table named tbl1:
CREATE TABLE tbl1 (col1 DATETIME)
DYNAMIC_PARTITION_POLICY(
ENABLE = true,
TIME_UNIT = 'hour',
PRECREATE_TIME = '3 hour',
EXPIRE_TIME = '1 day',
TIME_ZONE = '+8:00')
PARTITION BY RANGE COLUMNS (col1)(
PARTITION P0 VALUES LESS THAN ('2025-04-23 17:30:00')
);
If the existing maximum partition boundary is greater than the current time
2025-04-23 16:24:56, after running dynamic partition management once, the partitions will be:PARTITION P0 VALUES LESS THAN ('2025-04-23 17:30:00'), PARTITION P2025042317 VALUES LESS THAN ('2025-04-23 18:00:00'), PARTITION P2025042318 VALUES LESS THAN ('2025-04-23 19:00:00'), PARTITION P2025042319 VALUES LESS THAN ('2025-04-23 20:00:00')If the existing maximum partition boundary is less than the current time, pre-creating partitions will attempt to supplement historical partitions. For example, if the existing maximum partition is
PARTITION P0 VALUES LESS THAN ('2025-04-23 15:30:00')and the current time is2025-04-23 16:26:56, after running dynamic partition management once, the partitions will be:PARTITION P0 VALUES LESS THAN ('2025-04-23 15:30:00'), PARTITION P2025042315 VALUES LESS THAN ('2025-04-23 16:00:00'), PARTITION P2025042316 VALUES LESS THAN ('2025-04-23 17:00:00'), PARTITION P2025042317 VALUES LESS THAN ('2025-04-23 18:00:00'), PARTITION P2025042318 VALUES LESS THAN ('2025-04-23 19:00:00'), PARTITION P2025042319 VALUES LESS THAN ('2025-04-23 20:00:00')The historical partition
P2025042315has been supplemented.
Names of pre-created partitions
The system automatically generates partition names based on the partition boundary values and TIME_UNIT. The partition name prefix is P.
TIME_UNIT |
Partition upper bound |
Partition name |
|---|---|---|
| hour | 2024-03-04 05:00:00 | P2024030404 |
| day | 2024-03-05 00:00:00 | P20240304 |
| week | 2024-03-11 00:00:00 | P2024_10 (the 10th ISO 8601 week) |
| month | 2024-04-01 00:00:00 | P202403 |
| year | 2025-01-01 00:00:00 | P2024 |
Delete expired partitions
During dynamic partition management, the system deletes all expired partitions where partition upper bound < now() - expire_time based on EXPIRE_TIME.
Here is an example:
Create a table named
tbl2.CREATE TABLE tbl2 (col1 DATETIME) DYNAMIC_PARTITION_POLICY( ENABLE = true, TIME_UNIT = 'hour', PRECREATE_TIME = '3 hour', EXPIRE_TIME = '6 hour', TIME_ZONE = '+8:00') PARTITION BY RANGE COLUMNS (col1)( PARTITION P0 VALUES LESS THAN ('2025-04-21 08:30:00') );The current time is
2025-04-21 17:56:06. After running dynamic partition management once, the partitions are:PARTITION P0 VALUES LESS THAN ('2025-04-21 08:30:00'), PARTITION P2025042108 VALUES LESS THAN ('2025-04-21 09:00:00'), PARTITION P2025042109 VALUES LESS THAN ('2025-04-21 10:00:00'), PARTITION P2025042110 VALUES LESS THAN ('2025-04-21 11:00:00'), PARTITION P2025042111 VALUES LESS THAN ('2025-04-21 12:00:00'), PARTITION P2025042112 VALUES LESS THAN ('2025-04-21 13:00:00'), PARTITION P2025042113 VALUES LESS THAN ('2025-04-21 14:00:00'), PARTITION P2025042114 VALUES LESS THAN ('2025-04-21 15:00:00'), PARTITION P2025042115 VALUES LESS THAN ('2025-04-21 16:00:00'), PARTITION P2025042116 VALUES LESS THAN ('2025-04-21 17:00:00'), PARTITION P2025042117 VALUES LESS THAN ('2025-04-21 18:00:00'), PARTITION P2025042118 VALUES LESS THAN ('2025-04-21 19:00:00'), PARTITION P2025042119 VALUES LESS THAN ('2025-04-21 20:00:00'), PARTITION P2025042120 VALUES LESS THAN ('2025-04-21 21:00:00')At
2025-04-21 17:56:16, after running dynamic partition management again, the partitions are:PARTITION P2025042111 VALUES LESS THAN ('2025-04-21 12:00:00'), PARTITION P2025042112 VALUES LESS THAN ('2025-04-21 13:00:00'), PARTITION P2025042113 VALUES LESS THAN ('2025-04-21 14:00:00'), PARTITION P2025042114 VALUES LESS THAN ('2025-04-21 15:00:00'), PARTITION P2025042115 VALUES LESS THAN ('2025-04-21 16:00:00'), PARTITION P2025042116 VALUES LESS THAN ('2025-04-21 17:00:00'), PARTITION P2025042117 VALUES LESS THAN ('2025-04-21 18:00:00'), PARTITION P2025042118 VALUES LESS THAN ('2025-04-21 19:00:00'), PARTITION P2025042119 VALUES LESS THAN ('2025-04-21 20:00:00'), PARTITION P2025042120 VALUES LESS THAN ('2025-04-21 21:00:00')You can see that the expired partitions
P0,P2025042108,P2025042109, andP2025042110have been deleted.
Considerations
- Dynamic partition management does not automatically retry if a scheduled operation fails. To avoid data write failures caused by new partitions not being created in time due to consecutive scheduling failures, it is recommended to set
PRECREATE_TIMEto a large value. - DDL operations are prohibited during cluster upgrades, which will pause dynamic partition management scheduling. It is recommended to pre-create partitions in advance to cover the upgrade window based on the estimated upgrade duration.
- If the schema of a table changes after modifying its partitioning key column type or partitioning rule, and the new schema no longer meets the restrictions for dynamic partition management, subsequent scheduling on this table will fail.
- A single scheduling operation can create at most 2,048 partitions. If too many historical partitions need to be supplemented, multiple scheduling operations may be required to meet the desired pre-created time.
- The dynamic partition management feature needs to send DDL statements in the background to create and delete partitions. To avoid continuously impacting other DDL operations, the number of tables with dynamic partition management enabled should not be excessive.