All Products
Search

This Product

    PolarDB:Partitioning types provided by PolarDB-X

    Document Center

    PolarDB:Partitioning types provided by PolarDB-X

    Last Updated:May 08, 2025

    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.

    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.

    1. The hash value c1_hash is calculated by using the consistent hashing algorithm based on the value of the c1 column.

    2. Data is routed to different partitions based on the value of c1_hash.

    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.

    1. 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.

    2. 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).

    Yes

    PARTITION BY HASH(YEAR(c1))

    1. The YEAR function is used to calculate the value of year based on the value of the c1 column.

    2. The hash value year_hash is calculated based on the value of year by using the consistent hashing algorithm.

    3. 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.

    1. 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.

    2. 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.

    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.

    1. The values of the partition key columns (c1,c2,...,cn) are used as a vector partition key.

    2. 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

    1. The YEAR function is used to calculate the value of year based on the value of the c1 column.

    2. 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.

    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.

    1. The values of the partition key columns (c1,c2,...,cn) are used as a vector partition key.

    2. 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.

    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.

    Comparison between COHASH partitioning, HASH partitioning, and KEY partitioning

    Item

    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)

    1. Extract the original value v1 for an equivalent query from the c1 column. This operation is applicable to other columns.

    2. 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.

    3. Use the consistent hashing algorithm to calculate the hash c1_hash of f1. The hash is an integer of the long type.

    4. Data is routed to different partitions based on the value of c1_hash.

    For more information, see Routing policy in the HASH partitioning section.

    For more information, see Routing policy in the Hash partitioning section.

    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

    Subpartitioning

    Supported

    Supported

    Supported