Hash partitioning distributes data across partitions automatically using the hash algorithm. Unlike range or list partitioning — where you explicitly define which partition stores which values — hash partitioning handles the distribution for you. Choose hash partitioning when:
Your data has no natural ranges or categories to partition by.
You want even data distribution across partitions to support parallel access.
You want to minimize I/O bottlenecks caused by uneven data access.
You run frequent equality queries on the partition key and want partition pruning to reduce query overhead.
Choose a partition key
Choosing the right partition key determines how evenly data is distributed and how effectively PolarDB can prune partitions at query time.
Select a unique or near-unique column. The closer the partition key values are to unique, the more evenly data spreads across partitions. A column with many repeated values produces uneven distribution and reduces the benefit of partitioning.
Use a power of 2 for the partition count. When the number of partitions is a power of 2 — such as 2, 4, 8, 16, 32, 64, or 128 — you can create multiple partitions and subpartitions for each partition. This matches how the hash algorithm routes rows to partitions and ensures balanced distribution.
| Partition key | Suitability | Reason |
|---|---|---|
Primary key (c_custkey) | Good | High cardinality, many distinct values — even distribution |
| Status column (e.g., 3 values) | Poor | Low cardinality causes data to concentrate in a few partitions |
Create hash partitions
Both examples below use the customer table with c_custkey as the partition key.
Option 1: Specify partition names explicitly
CREATE TABLE `customer` (
`c_custkey` int(11) NOT NULL,
`c_name` varchar(25) DEFAULT NULL,
`c_address` varchar(40) DEFAULT NULL,
`c_nationkey` int(11) DEFAULT NULL,
`c_phone` char(15) DEFAULT NULL,
`c_acctbal` decimal(10,2) DEFAULT NULL,
`c_mktsegment` char(10) DEFAULT NULL,
`c_comment` varchar(117) DEFAULT NULL,
PRIMARY KEY (`c_custkey`),
KEY `i_c_nationkey` (`c_nationkey`)
) ENGINE=InnoDB
PARTITION BY HASH(c_custkey)
( PARTITION p1,
PARTITION p2,
PARTITION p3,
PARTITION p4
);Option 2: Let PolarDB generate partition names automatically
Specify only the number of partitions. PolarDB generates the internal partition names.
CREATE TABLE `customer` (
`c_custkey` int(11) NOT NULL,
`c_name` varchar(25) DEFAULT NULL,
`c_address` varchar(40) DEFAULT NULL,
`c_nationkey` int(11) DEFAULT NULL,
`c_phone` char(15) DEFAULT NULL,
`c_acctbal` decimal(10,2) DEFAULT NULL,
`c_mktsegment` char(10) DEFAULT NULL,
`c_comment` varchar(117) DEFAULT NULL,
PRIMARY KEY (`c_custkey`),
KEY `i_c_nationkey` (`c_nationkey`)
) ENGINE=InnoDB
PARTITION BY HASH (`c_custkey`)
PARTITIONS 64;