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 number 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 TIMESTAMP)
    DYNAMIC_PARTITION_POLICY(
        ENABLE = true,
        TIME_UNIT = 'hour',
        PRECREATE_TIME = '3 hour',
        EXPIRE_TIME = '1 day',
        TIME_ZONE = '+8:00')
    PARTITION BY RANGE (col1)(
        PARTITION P0 VALUES LESS THAN (TIMESTAMP '2025-04-20 13:30:00')
        );

• If the current time is 2025-04-20 12:34:56, after running dynamic partition management once, the partitions will be:

  PARTITION P0 VALUES LESS THAN (TIMESTAMP '2025-04-20 13:30:00')
  PARTITION P2025042013 VALUES LESS THAN (TIMESTAMP '2025-04-20 14:00:00'),
  PARTITION P2025042014 VALUES LESS THAN (TIMESTAMP '2025-04-20 15:00:00'),
  PARTITION P2025042015 VALUES LESS THAN (TIMESTAMP '2025-04-20 16: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 (TIMESTAMP '2025-04-20 11:30:00, and the current time is 2025-04-20 12:34:56, after running dynamic partition management once, the partitions will be:

  PARTITION P0 VALUES LESS THAN (TIMESTAMP '2025-04-20 11:30:00'),
  PARTITION P2025042011 VALUES LESS THAN (TIMESTAMP '2025-04-20 12:00:00'),
  PARTITION P2025042012 VALUES LESS THAN (TIMESTAMP '2025-04-20 13:00:00'),
  PARTITION P2025042013 VALUES LESS THAN (TIMESTAMP '2025-04-20 14:00:00'),
  PARTITION P2025042014 VALUES LESS THAN (TIMESTAMP '2025-04-20 15:00:00'),
  PARTITION P2025042015 VALUES LESS THAN (TIMESTAMP '2025-04-20 16:00:00')
  

  The historical partition P2025042011 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 TIMESTAMP)
       DYNAMIC_PARTITION_POLICY(
           ENABLE = true,
           TIME_UNIT = 'hour',
           PRECREATE_TIME = '3 hour',
           EXPIRE_TIME = '6 hour',
           TIME_ZONE = '+8:00')
       PARTITION BY RANGE (col1)(
           PARTITION P0 VALUES LESS THAN (TIMESTAMP '2025-04-21 08:30:00')
           );
   

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

   PARTITION P0 VALUES LESS THAN (TIMESTAMP '2025-04-21 08:30:00.000000'),
   PARTITION P2025042108 VALUES LESS THAN (TIMESTAMP '2025-04-21 09:00:00.000000'),
   PARTITION P2025042109 VALUES LESS THAN (TIMESTAMP '2025-04-21 10:00:00.000000'),
   PARTITION P2025042110 VALUES LESS THAN (TIMESTAMP '2025-04-21 11:00:00.000000'),
   PARTITION P2025042111 VALUES LESS THAN (TIMESTAMP '2025-04-21 12:00:00.000000'),
   PARTITION P2025042112 VALUES LESS THAN (TIMESTAMP '2025-04-21 13:00:00.000000'),
   PARTITION P2025042113 VALUES LESS THAN (TIMESTAMP '2025-04-21 14:00:00.000000'),
   PARTITION P2025042114 VALUES LESS THAN (TIMESTAMP '2025-04-21 15:00:00.000000'),
   PARTITION P2025042115 VALUES LESS THAN (TIMESTAMP '2025-04-21 16:00:00.000000'),
   PARTITION P2025042116 VALUES LESS THAN (TIMESTAMP '2025-04-21 17:00:00.000000'),
   PARTITION P2025042117 VALUES LESS THAN (TIMESTAMP '2025-04-21 18:00:00.000000'),
   PARTITION P2025042118 VALUES LESS THAN (TIMESTAMP '2025-04-21 19:00:00.000000'),
   PARTITION P2025042119 VALUES LESS THAN (TIMESTAMP '2025-04-21 20:00:00.000000'),
   PARTITION P2025042120 VALUES LESS THAN (TIMESTAMP '2025-04-21 21:00:00.000000')
   

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

   PARTITION P2025042111 VALUES LESS THAN (TIMESTAMP '2025-04-21 12:00:00.000000'),
   PARTITION P2025042112 VALUES LESS THAN (TIMESTAMP '2025-04-21 13:00:00.000000'),
   PARTITION P2025042113 VALUES LESS THAN (TIMESTAMP '2025-04-21 14:00:00.000000'),
   PARTITION P2025042114 VALUES LESS THAN (TIMESTAMP '2025-04-21 15:00:00.000000'),
   PARTITION P2025042115 VALUES LESS THAN (TIMESTAMP '2025-04-21 16:00:00.000000'),
   PARTITION P2025042116 VALUES LESS THAN (TIMESTAMP '2025-04-21 17:00:00.000000'),
   PARTITION P2025042117 VALUES LESS THAN (TIMESTAMP '2025-04-21 18:00:00.000000'),
   PARTITION P2025042118 VALUES LESS THAN (TIMESTAMP '2025-04-21 19:00:00.000000'),
   PARTITION P2025042119 VALUES LESS THAN (TIMESTAMP '2025-04-21 20:00:00.000000'),
   PARTITION P2025042120 VALUES LESS THAN (TIMESTAMP '2025-04-21 21:00:00.000000')
   

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

Considerations

• In versions earlier than V4.6.0, performing a DROP partition operation will invalidate global indexes. For tables with global indexes, use the expired partition deletion feature with caution.

• V4.6.0 introduces a new tenant-level parameter _append_update_global_indexes_for_dynamic_partition, which controls whether global indexes are automatically maintained during the cleanup of expired partitions. The valid values are as follows:

  • AUTO: The default value for new tenants, which follows the value of _ob_enable_truncate_partition_preserve_global_index. This option is suitable when you want to avoid the additional overhead of rebuilding global indexes.

    • If _ob_enable_truncate_partition_preserve_global_index is set to true, the UPDATE GLOBAL INDEXES clause is appended, preserving the global indexes.
    • If _ob_enable_truncate_partition_preserve_global_index is set to false, the UPDATE GLOBAL INDEXES clause is not appended, causing the global indexes to become invalid.

  • ON: The UPDATE GLOBAL INDEXES clause is always appended. During the cleanup of expired partitions, the value of _ob_enable_truncate_partition_preserve_global_index determines whether to preserve or rebuild the global indexes. This option is suitable when you want to avoid global indexes becoming invalid.

  • OFF: The default value for upgraded tenants, consistent with the behavior of earlier versions. The UPDATE GLOBAL INDEXES clause is never appended. During the cleanup of expired partitions, the global indexes will become invalid. This option is suitable when you want to avoid the additional overhead of rebuilding or preserving global indexes.

    In the case of upgrading to V4.6.0, to ensure that global indexes remain valid after a DROP partition operation, you need to set _ob_enable_truncate_partition_preserve_global_index to true and _append_update_global_indexes_for_dynamic_partition to AUTO or ON. Here are some examples:

    obclient> ALTER SYSTEM SET "_ob_enable_truncate_partition_preserve_global_index" = true;
    

    obclient> ALTER SYSTEM SET "_append_update_global_indexes_for_dynamic_partition" = 'AUTO';
    

• 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