Overview of Random Distribution Tables

Starting from V5.0.1, OceanBase Database introduced random distribution tables based on the random partition strategy. A random distribution table uses the random partition mechanism to automatically scatter and route data to different nodes in the cluster during writes, thereby achieving cross-node load balancing and efficient scaling.

In terms of underlying implementation, a random distribution table is essentially a RANGE partitioned table with an internally generated INT64 column as the partitioning key. During writes, the system routes data to different active partitions based on a sequence number called Tablet Seq, ensuring data is evenly distributed across multiple OBServer nodes. This avoids hotspots while improving write performance and resource utilization.

Features

• No need to specify a partitioning key:

  • For tables without a primary key: The system automatically generates a hidden auto-increment primary key column (__pk_increment) for partition routing.
  • For tables with a primary key: Only auto-increment primary keys are supported. The auto-increment column must be of type BIGINT and AUTO_INCREMENT_MODE must be set to NOORDER.

• Random and even data distribution: During INSERT operations, the system randomly selects a target partition from the active partitions.

• Automatic partition management: Automatically creates new partitions when sequence values are exhausted, and dynamically scales based on the number of available log streams (LS) for the tenant.

• Transparent to users: In compatibility mode, SHOW CREATE TABLE outputs standard MySQL DDL, hiding partition details.

Application scenarios

• Append-only write scenarios such as logs or transactional tables where partition routing is not required.
• Large-scale data import scenarios that require horizontal scaling to increase write throughput.
• Scenarios where partition pruning based on specific columns is not needed.

Limitations

• Tables with locally unique indexes are not supported.
• Tables with non-auto-increment primary keys are not supported.
• Partition operations (DROP/SPLIT/EXCHANGE/TRUNCATE PARTITION) on random distribution tables are not supported.
• Converting non-random distribution tables to random distribution tables is not supported.
• Random distribution tables cannot be added to table groups.

DML operations on random distribution tables

• INSERT operations on random distribution tables are identical to those on regular tables, with the system automatically handling partition routing.
• SELECT, UPDATE, and DELETE operations on random distribution tables are identical to those on regular tables.
• REPLACE operations on random distribution tables require a unique key constraint on the table (must be a GLOBAL unique index).

Maintenance considerations

• Slow DDL operations such as DROP DATABASE: May block foreground writes because new partitions cannot be created promptly when active partitions are full.
• Full direct load (APPEND): The partitioning scheme is based on the schema at the start of the statement and cannot detect newly added partitions during the import process. Insufficient partitions will cause a direct error.

References

• Create a random distribution table
• Modify a random distribution table