Overview of dynamic partitioning

Dynamic partitioning is a feature introduced in OceanBase Database V4.3.5 BP2. It allows tables to automatically create and drop partitions based on predefined rules, simplifying operations and adapting to data growth or expiration.

Features

• Periodically creates new partitions based on the TIME_UNIT (e.g., hours, days, months) to ensure data automatically expands within the specified time range.

  • Partitions are divided based on the time unit (e.g., hour, day), suitable for scenarios where data is stored in a time series (e.g., logs, transaction records).

• Precreates future partitions using PRECREATE_TIME to avoid partition shortages caused by sudden data increases.

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

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

• Supports precision configuration for bigint type timestamps (e.g., BIGINT_PRECISION) to meet different time precision requirements.

Precreating partitions

During dynamic partitioning management, the system creates partitions at intervals of TIME_UNIT until the maximum partition boundary exceeds now() + precreate_time. The first partition boundary is the ceiling of the existing maximum partition boundary based on TIME_UNIT.

Example:

Create a table 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 one dynamic partitioning management, the partitions are:

  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, precreating 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 is 2025-04-23 16:26:56, after one dynamic partitioning management, the partitions are:

  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 is supplemented.

Naming of precreated partitions

The system determines the automatically generated partition name based on the partition boundary value and TIME_UNIT, with the prefix P.

Deleting expired partitions

During dynamic partitioning management, the system deletes all expired partitions where partition upper bound < now() - expire_time based on EXPIRE_TIME.

Example:

1. Create a table 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. Current time is 2025-04-21 17:56:06. After one dynamic partitioning management, 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')
   

3. Time is 2025-04-21 17:56:16. After another dynamic partitioning management, 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')
   

   The expired partitions P0, P2025042108, P2025042109, and P2025042110 are deleted.

Considerations

• For tenants upgraded from versions earlier than V4.3.5 BP2, executing DROP operations on partitions will invalidate global indexes. For tables with global indexes, use the delete expired partitions feature with caution.

  Note

  Starting from OceanBase Database V4.3.5 BP2, a tenant-level hidden parameter _ob_enable_truncate_partition_preserve_global_index is introduced to control whether global indexes remain valid during TRUNCATE/DROP operations on partitions (referred to as partition DDL).

  • For upgraded tenants, the default value of this hidden parameter is False, indicating that global indexes are invalidated during TRUNCATE/DROP operations on partitions.
  • For newly created tenants, the default value is True, indicating that global indexes remain valid during TRUNCATE/DROP operations on partitions.

• Dynamic partitioning management does not automatically retry if a scheduling fails. To avoid new partitions from being created in a timely manner due to continuous scheduling failures, which can lead to data write failures, it is recommended to set PRECREATE_TIME to a larger value.

• During cluster upgrades, DDL operations are prohibited, and dynamic partitioning management will be paused. It is recommended to precreate partitions in advance based on the estimated upgrade duration to cover the upgrade window.

• After modifying the partition key column type or the partitioning rule, if the new table schema does not meet the dynamic partitioning management restrictions, subsequent dynamic partitioning management tasks on this table will fail.

• A single scheduling can create up to 2048 partitions. If a large number of historical partitions need to be supplemented, multiple schedulings may be required to meet the precreation time.

• Dynamic partitioning management requires sending DDL operations in the background to create and drop partitions. To avoid continuous impact on other DDL operations, it is recommended to limit the number of dynamic partitioning tables.

References

• Create a dynamic partition table
• View a dynamic partition table
• Modify a dynamic partition table
• Dynamic partitioning management task