OceanBase Partitioning Fundamentals

Introduction

Analytical workloads usually need to perform analytical computation over massive amounts of data, placing high demands on a database’s query capability and data management capability. Through partitioning, OceanBase horizontally splits a table’s data into multiple subsets according to the partition key, which helps improve query efficiency and data management:

1. Improved query efficiency: partition pruning can reduce scans of irrelevant data.

2. Data maintenance: supports managing data at partition granularity, such as data archiving and cleanup.

3. Data distribution: distributing data at partition granularity can spread data across multiple nodes, giving good scalability.

This article first introduces the role of partitioning in OceanBase, then describes the basic partitioning methods in OceanBase and their applicable scenarios, and finally discusses how OceanBase’s flexible partition management capabilities apply to business scenarios such as data maintenance and data management.

The Role of Partitioning in OceanBase

In OceanBase, a partition is the basic unit of horizontal sharding — the smallest physical unit of data distribution, load balancing, and parallel operations. A large table is logically split into multiple smaller, more manageable independent chunks, and each partition (or even different replicas of a partition) can be distributed across different OBServer nodes in the cluster.

This design brings a fundamental advantage to analytical workloads: when a single node’s storage or compute capacity becomes a bottleneck, you can achieve near-linear horizontal scaling by adding nodes and redistributing partitions, thereby handling PB-scale data volumes.

Partition Pruning Improves Query Efficiency

With partitioning, when you query by specifying the partition column, in some scenarios the engine can prune out the partitions that satisfy the query condition, so the query doesn’t need to scan the partitions that don’t.

Consider the following example. We create a hash partition on column c2, and specifying the query condition c2=1 allows pruning down to only partition p1.

-- Create a table t1 with four hash partitions, partition key c2
create table t1(c1 int, c2 int) partition by hash(c2) partitions 4;

-- Query with c2=1, pruning down to partition p1
explain select * from t1 where c2 = 1;
+------------------------------------------------------------------------------------+
| Query Plan                                                                         |
+------------------------------------------------------------------------------------+
| ===============================================                                    |
| |ID|OPERATOR       |NAME|EST.ROWS|EST.TIME(us)|                                    |
| -----------------------------------------------                                    |
| |0 |TABLE FULL SCAN|t1  |1       |3           |                                    |
| ===============================================                                    |
| Outputs & filters:                                                                 |
| -------------------------------------                                              |
|   0 - output([t1.c1], [t1.c2]), filter([t1.c2 = 1]), rowset=16                     |
|       access([t1.c2], [t1.c1]), partitions(p1)                                     |
|       is_index_back=false, is_global_index=false, filter_before_indexback[false],  |
|       range_key([t1.__pk_increment]), range(MIN ; MAX)always true                  |
+------------------------------------------------------------------------------------+

Partition pruning can filter out unneeded data, but too many partitions can cause other problems — for example, excessive metadata or reduced pruning efficiency. Therefore, in OceanBase columnar tables, it’s recommended that the number of rows per partition be >= 1 million.

Partitioning as a Data Maintenance Unit

In database operations, using the partition as the basic data-maintenance unit can greatly simplify day-to-day management — for example, data cleanup scenarios and partition-level statistics collection. Take data cleanup: once data is partitioned by time, cleaning up expired data no longer requires deleting it row by row; instead, you simply drop the entire historical partition. This operation only modifies metadata, yet it also fully releases disk space, avoiding the performance overhead of a traditional DML delete. By naturally categorizing data via the partition key (such as time), maintenance operations shift from “row-by-row scanning” to “batch processing,” dramatically improving management efficiency and lowering operational complexity.

Partitioning as a Data Distribution Unit

As OceanBase’s data distribution unit, each partition’s replicas can be placed on different OBServer nodes to scale both storage and compute.

1. Storage scaling: when you create a partitioned table, these partitions and their replicas can be automatically scheduled onto different physical nodes by OceanBase based on the cluster’s resource situation. This means a single table’s capacity is no longer limited by a single machine’s disk, but by the storage capacity of the entire cluster; when the cluster runs short of storage, you can scale out simply by adding nodes.

2. Compute parallelism: this is one of the keys to high performance for analytical workloads. When a query is executed (especially one involving a full-table scan or large-scale aggregation), OceanBase’s optimizer identifies the partitions the query involves. The query task can be decomposed into multiple subtasks and pushed down to the nodes where each data partition resides, to execute in parallel. For example, a SUM() operation first computes a subtotal locally within each partition, then aggregates the intermediate results to get the final total. This fully leverages the compute power of multiple nodes, significantly accelerating the query.

OceanBase’s Basic Partitioning Methods

OceanBase currently supports three major categories of basic partitioning methods: Hash/Key, Range/Range Columns, and List/List Columns. Each of the three has somewhat different use cases.

HASH/KEY Partitioning

Generally suited to cases where the partition column’s NDV (number of distinct values) is large and hard to divide into clear ranges. The advantage is that it easily distributes data with no particular pattern evenly across partitions; the disadvantage is that partition pruning is hard during range queries.

Example use case: no obvious query pattern, and data needs to be distributed evenly across multiple nodes (e.g., user ID, transaction ID).

Design points:

• Partition key selection:
  • NDV (number of distinct values) far greater than the number of partitions (e.g., the NDV of user ID should be far greater than the number of partitions).
  • Prefer skew-free (or only slightly skewed) integer/time columns (e.g., user_id, order_time, or an auto-increment column).
  • High-frequency query condition fields (e.g., user_id used as a Join key).
• 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 use case)

-- Hash partitioning, distributed evenly 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

Generally suited to cases where the partition key can be easily divided into clear ranges — for example, a large table recording transaction/log information can be RANGE-partitioned by the column representing the information’s timestamp.

Example use cases:

• Data grows by time/value range (e.g., order_time, price).
• Need to quickly prune historical data (e.g., querying only the last month’s data).

Design points:

• Partition key selection:
  • A time field (e.g., order_time) or a continuous numeric field.
  • Partition boundaries should align with business query conditions (e.g., divided by day/month).
• Recommended number of partitions:
  • Set partitions according to data growth, e.g., partitioning by month.

Example scenario (Range/Range Columns partitioning example)

-- Create a system log table, RANGE-partitioned monthly by log time, supporting 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 directly to express partition boundaries
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 future data or records with abnormal date formats
    PARTITION p_future VALUES LESS THAN (MAXVALUE)
);

List/List Columns Partitioning

Generally suited to cases where you need to explicitly control how each row maps to a specific partition. The advantage is that it can precisely partition unordered or unrelated datasets; the disadvantage is that partition pruning is hard during range queries.

Example use cases:

• Discrete fields (e.g., region, channel type).
• Need to quickly prune data by fixed category (e.g., querying users in the East China region).

Design points:

• Partition key selection:
  • Discrete values with a limited count (e.g., the region field has only [‘east’,’west’,’south’,’north’]).
  • Partition values must cover all possible values, with no omissions.
• Partition count limits:
  • Configure the number of partitions according to business logic.

Example scenario (List/List Columns partitioning use case)

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)  -- Changed to an integer-type partition 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 handles unknown regions
);

Flexible Partition Management Capabilities

OceanBase has very flexible partition management capabilities. From a data management perspective, it provides both data maintenance and data distribution functions; in terms of usage, it offers both manual and automatic management; and in terms of partition hierarchy, it supports combining primary partitions and secondary partitions. Through different combinations, it meets users’ varied data-management needs.

This section unfolds from the two angles of data maintenance and data distribution, considering within each angle the combination of usage methods and partition-hierarchy capabilities.

Data Maintenance

The business layer usually manages partitions along the time dimension, which makes operations like data archiving and cleanup convenient. We describe our manual partition management capabilities in the context of the complete data lifecycle of a business.

1. Business table creation: create a table partitioned by time, pre-creating the partitions needed for some period into the future.

2. Business data import: import the data.

3. Business running: as time advances, the pre-created partitions may run short, so continue pre-creating the partitions needed for some period into the future.

4. Periodic data cleanup: once data has accumulated for a certain period, the earlier data may no longer be needed, at which point you can drop the unneeded partitions.

Below is a concrete example of the above use case:

-- 1. Create a partitioned table (partitioned by day, pre-creating partitions for the next 7 days)
CREATE TABLE business_data(
    id BIGINT NOT NULL AUTO_INCREMENT,
    event_time DATETIME NOT NULL,
    metric_value DECIMAL(10,2),
    PRIMARY KEY (id, event_time)
) PARTITION BY RANGE COLUMNS(event_time)
(
    PARTITION p20231025 VALUES LESS THAN ('2023-10-26'),
    PARTITION p20231026 VALUES LESS THAN ('2023-10-27'),
    PARTITION p20231027 VALUES LESS THAN ('2023-10-28'),
    PARTITION p20231028 VALUES LESS THAN ('2023-10-29'),
    PARTITION p20231029 VALUES LESS THAN ('2023-10-30'),
    PARTITION p20231030 VALUES LESS THAN ('2023-10-31'),
    PARTITION p20231031 VALUES LESS THAN ('2023-11-01')  -- Pre-create partitions for the next 7 days
);

-- 2. Import data, skipped here

-- 3. Pre-create partitions for the next 7 days
ALTER TABLE business_data ADD PARTITION(
  PARTITION p20231101 VALUES LESS THAN ('2023-11-02'),
  PARTITION p20231102 VALUES LESS THAN ('2023-11-03'),
  PARTITION p20231103 VALUES LESS THAN ('2023-11-04'),
  PARTITION p20231104 VALUES LESS THAN ('2023-11-05'),
  PARTITION p20231105 VALUES LESS THAN ('2023-11-06'),
  PARTITION p20231106 VALUES LESS THAN ('2023-11-07'),
  PARTITION p20231107 VALUES LESS THAN ('2023-11-08')
);

-- 4. Periodic data cleanup, e.g., dropping 7 days of data once it expires
ALTER TABLE business_data DROP PARTITION p20231025, p20231026, p20231027, p20231028, p20231029, p20231030, p20231031;

Since data keeps being written in, manually maintaining pre-created partitions and periodically cleaning up partitions is fairly cumbersome. To simplify this process, OceanBase provides a dynamic partitioning feature, supporting partitioning by a fixed time, how far ahead to pre-create partitions, how long to retain historical partitions, and so on. For the example above, suppose we need to retain 30 days of data and pre-create 7 days of partitions each time; then we use the following syntax to create it:

-- 1. Create a partitioned table, setting the dynamic partitioning policy
CREATE TABLE t1(
    id BIGINT NOT NULL AUTO_INCREMENT,
    event_time DATETIME NOT NULL,
    metric_value DECIMAL(10,2),
    PRIMARY KEY (id, event_time)
)
DYNAMIC_PARTITION_POLICY(
  ENABLE = true,
  TIME_UNIT = 'day',
  PRECREATE_TIME = '7day',
  EXPIRE_TIME = '30day'
)
PARTITION BY RANGE COLUMNS(event_time)
(
  PARTITION p20231025 VALUES LESS THAN ('2023-10-26')
);

Besides the Range partitioning mode, businesses can also choose other basic partitioning methods as needed.

Data Distribution

A partition can also serve as the unit of data distribution management. Usually, to spread data out, HASH partitioning is used, which has the following advantages:

1. It generally achieves good data spreading and also fairly accurate partition pruning;
2. For multiple tables that need to Join, if you hash-partition by the join key and keep the partition counts identical, then together with OceanBase’s table group capability, partitions with the same hash rule and corresponding index are bound together — enabling Partition-Wise Join during joins and avoiding data shuffling.

HASH partitioning also has some drawbacks:

1. Once the number of HASH partitions is set, changing it is a fairly heavy operation that involves rewriting the entire table’s data. So generally, once the HASH partition count is set, it stays fixed, making scalability hard to achieve;
2. For range queries on the partition key, no partitions can be pruned and all partitions must be accessed, which may incur read amplification.

To solve the scalability and range-query problems of HASH partitioning, OceanBase already supports automatic partition splitting for row-store tables, and in a future version it will also offer two automatic-partition-splitting modes for columnar tables: the heap-table splitting mode and the clustering-key-table splitting mode.

The heap-table splitting mode partitions automatically based on the heap table’s hidden primary-key column. Because the hidden primary key is randomly generated in this splitting mode, and because the number of partitions automatically expands or shrinks when a tenant’s machine resources scale up or down, this mode can scale automatically and has good scalability. However, in this mode the data rows are randomly distributed across arbitrary partitions, so no partition pruning is possible, and query performance may not be optimal. This mode suits cases that don’t have high performance requirements, don’t want to provide a manual or automatic partition key, but still want the table to scale automatically.

The clustering-key-table splitting mode partitions automatically based on a user-specified clustering key, automatically splitting partitions of appropriate size by data volume. When a tenant’s machine resources scale up or down, since there are already enough split partitions, these partitions can be rebalanced. Because this mode splits automatically by the clustering key, when a query can specify the clustering key — whether a point lookup or a range query — partition pruning is possible, so query performance is fairly good, and it can also adaptively scale up or down based on machine resources. At the same time, clustering-key-table automatic splitting can also support configuring a table group for multiple tables that need to Join: the auto-split key can be configured as the join key, also enabling Partition-Wise Join.

To make this easier to understand, the characteristics of the three methods — Hash, heap-table splitting, and clustering-key-table splitting — are compared below:

Besides the above partitioning methods, businesses can also choose other basic partitioning methods as needed.

Mixing Data Maintenance and Data Distribution Management

We can also use secondary partitioning to support both data-maintenance and data-distribution needs at once. The most commonly used scenario is the primary partition for data-maintenance needs and the secondary partition for data-distribution needs, with each need combined using the methods that need supports.

A Typical Manual Partition Management Approach

1. Primary partition:
   • Type selection: use Range or List partitioning to match high-frequency query conditions (e.g., time range, region).
   • Partition-count suggestion: set a reasonable range based on the time distribution of query conditions and the data-maintenance needs (e.g., monthly partitions retaining 12 months, or 4 List partitions by region).
2. Secondary partition:
   • Type selection: use Hash partitioning to ensure data spreading.
   • Recommended number of partitions:
     • If only one primary partition is written, then that primary partition’s number of secondary partitions must satisfy the resource needs for spreading out writes.
     • If multiple primary partitions can be written, then it’s enough that (number of writable primary partitions) × (number of secondary partitions) satisfies the resource needs for spreading out writes.

Below are two scenario cases, Range + Hash and List + Hash:

1. Range + Hash: the primary partition uses Range partitioning; specifying order_date lets you quickly filter out partitions whose data doesn’t need scanning, and you can also quickly perform data maintenance via partition management operations. The secondary partition uses Hash partitioning, spreading the current month’s writes or reads across 8 partitions to avoid hot spots.

CREATE TABLE orders(
    user_id BIGINT NOT NULL COMMENT 'User ID (secondary partition 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-cancelled 1-pending payment 2-paid 3-shipped 4-completed',
    region_code CHAR(6) NOT NULL COMMENT 'Region code (first 2 digits are the province code)',
    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')
);

2. List + Hash: the primary partition uses List partitioning; specifying the province can prune to the corresponding partition, and data maintenance can also be done at the province level. The secondary partition uses Hash/Key partitioning, spreading a province’s read/write traffic across multiple partitions for load balancing.

-- Primary partition: LIST partitioned by province (31 provincial-level administrative regions)
CREATE TABLE social_insurance_records(
    record_id BIGINT,
    province_code INT NOT NULL,  -- Provincial code (e.g., 11 Beijing, 31 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)
);

A Typical Automatic Partition Management Approach

1. Primary partition: choose dynamic partitioning, configuring parameters such as partitioning by a fixed time, how far ahead to pre-create partitions, and how long to retain historical partitions;
2. Secondary partition: choose automatic Range partition splitting, which can split automatically without configuring the number of partitions or a partitioning rule.

Summary

OceanBase currently supports the common basic partitioning methods, and by combining them it can meet a business’s needs for data maintenance, data distribution, and improved query efficiency. Dynamic partitioning provides standard automated management for the general need of time-based partitioned data maintenance, reducing the cost of data maintenance for users. In the future, we will strengthen automatic partition management capabilities, supporting automatic partition splitting for columnar tables — reducing the current cost and scalability problems of manual data-distribution maintenance — and further improving the data-management automation of columnar tables, making it easier for analytical workloads to use OceanBase.