Overview of dynamic partitioning

Dynamic partitioning is a feature introduced in OceanBase Database V4.3.5 BP2 that enables automatic management of partitions. It allows tables to automatically create and drop partitions based on preset rules, thereby simplifying operations and maintenance while adapting to data growth or expiration needs.

Features

• Creates new partitions regularly based on TIME_UNIT (such as hours, days, and months) to ensure automatic expansion of data by time range.

  • 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, transaction records).

• Pre-creates future partitions using PRECREATE_TIME to avoid partition shortages caused by sudden data surges.

• Periodically cleans up expired partitions based on EXPIRE_TIME to free up storage space.

• Supports custom time zones (TIME_ZONE) to ensure data consistency across different time zones.

• Supports configuration of bigint type timestamp precision (such as BIGINT_PRECISION) to accommodate different time precision requirements.

Precreate 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 current maximum partition boundary rounded up according to the 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 current 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 current maximum partition boundary is less than the current time, pre-creating partitions will attempt to supplement historical partitions. For example, if the current maximum partition is PARTITION P0 VALUES LESS THAN ('2025-04-23 15:30:00'), and the current time is 2025-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 P2025042315 has been supplemented.

Names of precreated partitions

The system automatically generates partition names based on the partition boundary values and TIME_UNIT. The partition name prefix is P.

Delete expired partitions

During dynamic partition management, the system deletes all partitions whose upper bound is earlier than now() - expire_time based on the EXPIRE_TIME value.

Here is an example:

1. 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')
           );
   

2. The current time is 2025-04-21 17:56:06. After dynamic partition management is performed, the partitions are as follows:

   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')
   

3. At 2025-04-21 17:56:16, after dynamic partition management is performed again, the partitions are as follows:

   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')
   

   As we can see, the expired partitions P0, P2025042108, P2025042109, and P2025042110 are deleted.

Considerations

• In the current version, performing a DROP partition operation will invalidate global indexes. For tables with global indexes, use the expired partition deletion feature with caution.
• Dynamic partition management will not automatically retry if scheduling fails. To avoid new partitions not being created in time due to consecutive scheduling failures, which may result in data write failures, it is recommended to set PRECREATE_TIME to a larger value.
• DDL operations are prohibited during cluster upgrades, and dynamic partition management will pause scheduling. It is recommended to pre-create partitions in advance to cover the upgrade window based on the estimated upgrade duration.
• If you modify the partition key column type or partition rule, and the new table schema does not meet the requirements for dynamic partition management, subsequent dynamic partition management scheduling on this table will fail.
• A maximum of 2048 partitions can be created in a single scheduling. If there are too many historical partitions to supplement, multiple schedulings are required to meet the pre-creation time.
• The dynamic partition management feature needs to send DDLs in the background to create and delete partitions. To avoid continuous impact on other DDL operations, it is not recommended to have too many dynamic partition tables.

References

• Create a dynamic partitioned table
• Query a dynamic partitioned table
• Modify a dynamic partitioned table
• Dynamic partition management tasks