Overview - PolarDB - Alibaba Cloud Documentation Center

Partitioning types

PolarDB-X provides the following partitioning types that you can use to control how data is routed to partitions:

HASH partitioning
- HASH (compatible with the HASH partitioning syntax in MySQL)
- KEY (compatible with the KEY partitioning syntax in MySQL)
RANGE partitioning
- RANGE (compatible with the RANGE partitioning syntax in MySQL)
- RANGE COLUMNS (compatible with the RANGE COLUMNS partitioning syntax in MySQL)
LIST partitioning
- LIST (compatible with the LIST partitioning syntax in MySQL)
- LIST COLUMNS (compatible with the LIST COLUMNS partitioning syntax in MySQL)
Scenario-specific partitioning
CO_HASH (PolarDB-X featured partitioning syntax)

HASH partitioning

This partitioning type uses the built-in consistent hashing algorithm to calculate the hash value of a specified expression that contains a partitioning function or partition key column and routes data to partitions. The HASH partitioning type includes KEY partitioning and HASH partitioning based on whether expressions containing partitioning functions are used or partition key columns are used as partition keys.

Table 1. Comparison between KEY partitioning and HASH partitioning

Partitioning type	Supported partition key	Support partitioning functions	Statement syntax	Limits	Routing policy (point query)
KEY partitioning (default)	Single-column partition key	No	PARTITION BY KEY(c1)	Hot partition splitting is not supported. Data is routed to partitions based on values in up to one partition key column.	The hashes of values in the c1 column are calculated by using the consistent hashing algorithm. Data is routed to different partitions based on the hashes of values in the c1 column.
KEY partitioning (default)	Vector partition key	No	PARTITION BY KEY(c1,c2,...,cn)	Hot partition splitting is supported. By default, data is routed to partitions based on values in only the c1 partition key column. Other partition key columns can be used for hot partition splitting. n specifies the number of partition key columns that can be used for data partitioning at the same time. A maximum of five partition key columns are supported.	The values of the partition key columns `(c1,c2,...,cn)` are used as a vector partition key to calculate the hashes of each column in the vector by using the consistent hashing algorithm. A vector of hashes `(c1_hash,c2_hash,...,cn_hash)` corresponding to the vector partition key is obtained. The vector of hashes `(c1_hash,c2_hash,...,cn_hash)` is used to route data to partitions by range.
Hash	Single-column partition key	No	PARTITION BY HASH(c1)	Hot partition splitting is not supported. Data is routed to partitions based on values in one partition key column. Only the following partitioning functions are supported: YEAR MONTH DAYOFMONTH DAYOFWEEK DAYOFYEAR TO_DAYS TO_MONTHS TO_WEEKS TO_SECONDS UNIX_TIMESTAMP SUBSTR/SUBSTRING	The routing policy of PARTITION BY HASH(c1) is the same as that of PARTITION BY KEY(c1).
	Single-column partition key	Yes	PARTITION BY HASH(YEAR(c1))		The YEAR function is used to calculate the value of year based on the value in the c1 column. The hash value year_hash is calculated based on the value of year by using the consistent hashing algorithm. Data is routed to partitions based on the value of year_hash.
	Vector partition key	No	PARTITIONBY HASH(c1,c2,...,cn)	Hot partition splitting is not supported. After a table is created, data in the table is routed to partitions based on values in multiple partition key columns. A maximum of five partition key columns are supported.	The values of the partition key columns `(c1,c2,...,cn)` are used as a vector partition key to calculate the hashes of each column in the vector by using the consistent hashing algorithm. A vector of hashes corresponding to the vector partition key is obtained. Data is routed to partitions based on the hash values of vectors.

RANGE partitioning

This partitioning type compares and calculates the value of a specified partition key column or the value returned by a specified expression that contains a partitioning function to determine the range of predefined partitions in which data is distributed and routes the data to partitions. The RANGE partitioning type includes RANGE COLUMNS partitioning and RANGE partitioning based on whether expressions containing partitioning functions are used or partition key columns are used as partition keys.

Table 2. Comparison between RANGE partitioning and RANGE COLUMNS partitioning

Partitioning type	Supported partition key	Support partitioning functions	Statement syntax	Limits	Routing policy (point query)
Range Columns	Single-column partition key and vector partition key	No	PARTITION BY RANGE COLUMNS (c1,c2,...,cn) ( PARTITION p1 VALUES LESS THAN (1,10,...,1000), PARTITION p2 VALUES LESS THAN (2,20,...,2000) ...)	Hot partition splitting is supported. If a large number of rows contains the same value, such as 88 in the c1 partition key column, you can split the hot data based on values in the c2 partition key column.	The values of the partition key columns `(c1,c2,...,cn)` are used as a vector partition key. This vector partition key is used to determine the partition to which data is routed based on the binary search algorithm.
Range	Single-column partition key	Yes	PARTITION BY RANGE(YEAR(c1)) ( PARTITION p1 VALUES LESS THAN (2019), PARTITION p2 VALUES LESS THAN (2021) ...)	Hot partition splitting is not supported. Only the following partitioning functions are supported: YEAR MONTH DAYOFMONTH DAYOFWEEK DAYOFYEAR TO_DAYS TO_MONTHS TO_WEEKS TO_SECONDS UNIX_TIMESTAMP SUBSTR/SUBSTRING	The YEAR function is used to calculate the value of year based on the value in the c1 column. The value of year is used to determine the partition to which data is routed based on the binary search algorithm.

LIST partitioning

This partitioning type is similar to RANGE partitioning. It compares and calculates the value of a specified partition key column or the value returned by a specified expression that contains a partitioning function to determine the range of predefined partitions in which data is distributed and then routes the data to partitions. The LIST partitioning type includes LIST COLUMNS partitioning and LIST partitioning based on whether multiple partition key columns are used as partition keys and the usage method.

Table 3. Comparison between LIST partitioning and LIST COLUMNS partitioning

Partitioning type	Supported partition key	Support partitioning functions	Statement syntax	Limits	Routing policy (point query)
List Columns	Single-column partition key and vector partition key	No	PARTITION BY LIST COLUMNS (c1,c2,...,cn) ( PARTITION p1 VALUES IN ((1,10,...,1000),(2,20,...,2000) ), PARTITION p2 VALUES IN ((3,30,...,3000),(3,30,...,3000) ), ...)	Hot partition splitting is not supported.	The values of the partition key columns `(c1,c2,...,cn)` are used as a vector partition key. This vector partition key is used to determine the partition to which data is routed based on the binary search algorithm.
List	Single-column partition key	Yes	PARTITION BY LIST(YEAR(c1)) ( PARTITION p1 VALUES IN (2018,2019), PARTITION p2 VALUES IN (2020,2021) ...)	Hot partition splitting is not supported.	Hot partition splitting is not supported.

COHASH partitioning

PolarDB-X also offers a new partitioning type, COHASH partitioning, for scenarios where a table needs to be horizontally partitioned based on different partition key columns whose values are similar.

COHASH partitioning is similar to HASH partitioning and KEY partitioning. The following table compares the types.

Table 4. Comparison between COHASH partitioning, HASH partitioning, and KEY partitioning

Difference	CO_HASH	KEY	Hash
Statement syntax	PARTITION BY CO_HASH(c1, c2) PARTITOINS 8	PARTITION BY KEY(c1, c2) PARTITOINS 8	PARTITION BY HASH(c1, c2) PARTITOINS 8
Single-column partition key	Not supported	Supported	Supported
Vector partition key	Supported	Supported	Supported
Partitioning functions on vector partition key columns	Supported. Example: PARTITION BY CO_HASH( /* Partition the table by the last four characters of the c1 column values./ RIGHT(c1, 4), / Partition the table by the last four characters of the c2 column values.*/ RIGHT(c2, 4) ) PARTITOINS 8	Not supported	Not supported
Relationship between partition key columns	Values in the columns are similar. You need to maintain the similarity between the values of different partition key columns in a partitioned table. Examples: The values of the c1 and c2 columns are always the same CO_HASH(c1, c2) is suitable. The last four characters of the c1 and c2 column values are always the same. CO_HASH(RIGHT(c1,4), RIGHT(c2,4)) is suitable.	Similar to the prefix of a federated index.	Similar to the prefix of a federated index.
Equivalent query for partition key columns with prefixes, partition pruning, and examples	Supported Examples: c1='x': Partition pruning is supported and queries are routed to a single partition. c1='x' and c2='y': Partition pruning is supported and queries are routed to zero or a single partition. If the routing partition for c1='x' and c2='y' is different, no partitions are returned.	Supported Examples: c1='x': Partition pruning is supported and queries are routed to one or more shards. If you perform hot partition splitting on the x value, multiple partitions are returned. c1='x' and c2='y': Partition pruning is supported and queries are routed to a single partition.	Not supported. Partition pruning is supported only when equivalent conditions for all partition key columns are included. Examples: c1='x': Partition pruning cannot be performed and all partitions failed to be scanned. c1='x' and c2='y': Partition pruning is supported and queries are routed to a single partition.
Equivalent query for partition key columns without prefixes, partition pruning, and examples	Supported Equivalent conditions for all partition key columns support partition pruning. Examples: c2='x': Partition pruning is supported and queries are routed to a single partition. c1='x' or c2='y': Partition pruning is supported and queries are routed to one or two partitions. If the routing partitions for c1='x' and c2='y' are the same, one partition is returned. Otherwise, two partitions are scanned.	Not supported. Equivalent conditions for partitions without prefixes must result in a scan for all partitions. Examples: c2='x': All partitions are scanned. c1='x' or c2='y': All partitions are scanned.	Not supported. Equivalent conditions for partitions without prefixes must result in a scan for all partitions. Examples: c2='x': All partitions are scanned. c1='x' or c2='y': All partitions are scanned.
Range query	Not supported. All partitions are scanned.	Not supported. All partitions are scanned.	Not supported. All partitions are scanned.
Routing policy (point query)	Extract the original value v1 for an equivalent query from the c1 column. This operation is applicable to other columns. If the column uses a partitioning function, the value of the function is calculated by using the f1 = partFunc(v1) formula. Otherwise, the function value is v1. Use the consistent hashing algorithm to calculate the hash c1_hash of f1. The hash is an integer of the long type. Data is routed to different partitions based on the hash c1_hash.	Refer to the preceding content related to KEY partitioning and HASH partitioning.	Refer to the preceding content related to KEY partitioning and HASH partitioning.
Hot partition splitting	Not supported. Further hot partition splitting cannot be performed on a hot key value such as c1='88'.	Supported	Not supported
Partition management such as partition splitting, merging, and migration	Supported	Supported	Supported
Level-2 partition	Supported	Supported	Supported