OceanBase AI Database distributes data across different partitions by creating partitioned tables. Data in different partitions can be located on separate machines. During queries, partition pruning can reduce the amount of data scanned and leverage multi-machine resources to improve query performance. By default, data across different tables is randomly distributed with no direct relationship. Load balancing can help distribute a table's data more evenly across the entire cluster.
Role of partitions in OceanBase AI Database
In OceanBase AI Database, a partition is the basic unit of horizontal sharding and the smallest physical unit for data distribution, load balancing, and parallel operations. A large table is logically divided into multiple smaller, more manageable independent blocks. Each partition (or even different replicas of a partition) can be stored separately on different OBServer nodes within the cluster.
This design offers analytical businesses the advantage that when a single node reaches its storage or computing capacity bottleneck, horizontal scaling can be achieved nearly linearly by adding nodes and redistributing partitions, enabling handling of petabyte-scale data.
Basic partitioning methods in OceanBase AI Database
Currently, OceanBase AI Database supports three main basic partitioning methods: HASH/KEY, RANGE/Range Columns, and LIST/List Columns. Each method has its specific application scenarios.
HASH/KEY partitioning
This is generally suitable when the partitioning key has a high NDV (Number of Different Values) and it is difficult to define clear ranges. Its advantage is that it can evenly distribute data without specific rules across different partitions. The downside is that partition pruning is challenging during range queries.
Application scenarios:
- No clear query pattern, requiring even data distribution across multiple nodes (e.g., user IDs, transaction IDs).
Design considerations:
Partition key selection:
- NDV (number of unique values) much greater than the number of partitions (e.g., the NDV of a user ID should be much greater than the number of partitions).
- Prefer integers or time columns without skew (or with minimal skew) (e.g.,
user_id,order_time, or auto-increment columns). - Fields frequently used in query conditions (e.g.,
user_idas a join keyword).
Recommended number of partitions:
- Ensure the number of partitions matches the number of machines in the cluster to avoid uneven resource allocation.
Example scenario:
-- Hash partitioning, evenly distributed by user_id
CREATE TABLE customer (
user_id BIGINT NOT NULL,
login_time TIMESTAMP NOT NULL,
customer_name VARCHAR(100) NOT NULL,
phone_num BIGINT NOT NULL,
city_name VARCHAR(50) NOT NULL,
sex INT NOT NULL,
id_number VARCHAR(18) NOT NULL,
home_address VARCHAR(255) NOT NULL,
office_address VARCHAR(255) NOT NULL,
age INT NOT NULL
)
PARTITION BY HASH(user_id) PARTITIONS 128;
RANGE/RANGE COLUMNS partitioning
This is generally suitable when the partitioning key can be easily divided into clear ranges. For example, a large table containing transaction flow information can be partitioned using the column representing the time of the information.
Application scenarios:
- Data grows in time or numerical ranges (e.g.,
order_time,price). - Need to quickly prune historical data (e.g., query only data from the last month).
Design considerations:
Partition key selection:
- A time field (such as
order_time) or a continuous numeric field. - The partition boundaries should align with business query conditions (for example, dividing by day/month).
- A time field (such as
Recommended number of partitions:
- Set the number of partitions based on data growth, for example, by month.
Example scenario:
-- Create a system log table with monthly RANGE partitions based on log time, enabling fast queries and data archiving.
CREATE TABLE system_logs (
log_id BIGINT,
log_date TIMESTAMP NOT NULL,
log_level VARCHAR(10),
source_system VARCHAR(50),
user_id BIGINT,
log_message TEXT,
client_ip VARCHAR(15)
)
-- Primary partition: Monthly RANGE partitioning, using the date to directly define the partition boundary.
PARTITION BY RANGE COLUMNS(log_date) (
PARTITION p_202001 VALUES LESS THAN ('2020-02-01'),
PARTITION p_202002 VALUES LESS THAN ('2020-03-01'),
PARTITION p_202003 VALUES LESS THAN ('2020-04-01'),
PARTITION p_202004 VALUES LESS THAN ('2020-05-01'),
PARTITION p_202005 VALUES LESS THAN ('2020-06-01'),
PARTITION p_202006 VALUES LESS THAN ('2020-07-01'),
PARTITION p_202007 VALUES LESS THAN ('2020-08-01'),
PARTITION p_202008 VALUES LESS THAN ('2020-09-01'),
PARTITION p_202009 VALUES LESS THAN ('2020-10-01'),
PARTITION p_202010 VALUES LESS THAN ('2020-11-01'),
PARTITION p_202011 VALUES LESS THAN ('2020-12-01'),
PARTITION p_202012 VALUES LESS THAN ('2021-01-01'),
-- Default partition handles records with future data or invalid time formats
PARTITION p_future VALUES LESS THAN (MAXVALUE)
);
LIST/LIST COLUMNS partitioning
This is generally suitable when you need to explicitly control how each row of data maps to a specific partition. The advantage is that it allows for precise partitioning of unordered or unrelated datasets. The downside is that range queries can be difficult to prune.
Application scenarios:
- Discrete fields (such as region or channel type).
- Need to quickly prune data by fixed categories (for example, query users in East China).
Design considerations:
Partition key selection:
- Discrete values with a limited number (for example, the
regionfield has only['east','west','south','north']). - Partition values must cover all possible values to avoid omissions.
- Discrete values with a limited number (for example, the
Partition number limit:
- Configure the number of partitions based on business logic.
Example scenario:
CREATE TABLE orders_by_region (
order_id BIGINT COMMENT 'Unique order identifier',
region_code INT NOT NULL PRIMARY KEY COMMENT 'Region code (1=north/china, 2=east/china, 3=south/china, 4=west/china)',
customer_id BIGINT COMMENT 'Customer ID',
order_time DATETIME COMMENT 'Order creation time',
product_category VARCHAR(50) COMMENT 'Product category',
order_amount DECIMAL(18,2) COMMENT 'Order amount',
payment_status VARCHAR(20) COMMENT 'Payment status (e.g., PAID, UNPAID)'
)
PARTITION BY LIST(region_code) -- Integer partitioning key
(
PARTITION p_north VALUES IN (1), -- Region code 1 corresponds to north/china
PARTITION p_east VALUES IN (2),
PARTITION p_south VALUES IN (3),
PARTITION p_west VALUES IN (4),
PARTITION p_other VALUES IN (DEFAULT) -- Default partition for handling unknown regions
);
Hybrid data maintenance and data distribution management
We can also use subpartitioning to support both data maintenance and data distribution needs. A common scenario is to use primary partitions for data maintenance and subpartitions for data distribution, combining the methods supported by each requirement.
Typical manual partitioning management
- Primary partition:
- Type selection: Use RANGE or LIST partitioning to match frequently queried conditions (such as time range or region).
- Suggested number of partitions: Set a reasonable range based on the time distribution of query conditions and data maintenance requirements (for example, partition by month to retain 12 months, or divide by region into 4 LIST partitions).
- Subpartition:
- Type selection: Use HASH partitioning to ensure data dispersion.
- Recommended number of subpartitions:
- If writing occurs to only one primary partition, the number of subpartitions in that primary partition must meet the resource requirement for write dispersion.
- If writing can occur to multiple primary partitions, the number of writable primary partitions × the number of subpartitions must meet the resource requirement for write dispersion.
Range + Hash
For the primary partition, select RANGE partitioning. After specifying order_date, you can quickly filter out partitions that do not need to be scanned and perform data maintenance via partition management operations. For the subpartition, select HASH partitioning to distribute monthly writes or reads across 8 partitions, avoiding hotspots.
CREATE TABLE orders (
user_id BIGINT NOT NULL COMMENT 'User ID (subpartition key)',
order_date DATE NOT NULL COMMENT 'Order date (primary partition key)',
amount DECIMAL(10,2) NOT NULL COMMENT 'Order amount',
status TINYINT NOT NULL COMMENT 'Status: 0-Canceled, 1-Pending payment, 2-Paid, 3-Shipped, 4-Completed',
region_code CHAR(6) NOT NULL COMMENT 'Region code (first 2 digits are province codes)',
product_id INT NOT NULL COMMENT 'Product ID',
payment_method VARCHAR(20) COMMENT 'Payment method',
created_at TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6) COMMENT 'Record creation time')
PARTITION BY RANGE COLUMNS(order_date)
SUBPARTITION BY HASH(user_id) SUBPARTITIONS 8
(
PARTITION p202501 VALUES LESS THAN ('2025-02-01'),
PARTITION p202502 VALUES LESS THAN ('2025-03-01'),
...
PARTITION p202601 VALUES LESS THAN ('2026-02-01')
);
List + Hash
For the primary partition, select LIST partitioning. Specifying a province allows pruning to the corresponding partition, enabling data maintenance at the provincial level. For the subpartition, select HASH / KEY partitioning to distribute read and write traffic for a province across multiple partitions, achieving load balancing.
-- Primary partition: LIST by province (31 provincial-level administrative regions)
CREATE TABLE social_insurance_records (
record_id BIGINT,
province_code INT NOT NULL, -- Province code (for example, 11 for Beijing and 31 for Shanghai).
payment_date DATE NOT NULL,
user_id VARCHAR(32) NOT NULL,
amount DECIMAL(10,2)
) PARTITION BY LIST (province_code) -- Primary LIST partition
SUBPARTITION BY KEY(user_id) SUBPARTITIONS 16 -- Secondary HASH partition
(
PARTITION p_beijing VALUES IN (11),
PARTITION p_shanghai VALUES IN (31),
PARTITION p_tianjin VALUES IN (12),
...
PARTITION p_xizang VALUES IN (54)
);