All Products
Search

This Product

    PolarDB:PolarDB-X partitioning types

    Document Center

    PolarDB:PolarDB-X partitioning types

    Last Updated:Jun 08, 2026

    PolarDB-X supports HASH, KEY, RANGE, LIST, and CO_HASH partitioning types for controlling data distribution across partitions.

    Partitioning types

    PolarDB-X supports the following partitioning types:

    • HASH partitioning

      • HASH (MySQL-compatible syntax)

      • KEY (MySQL-compatible syntax)

    • RANGE partitioning

      • RANGE (MySQL-compatible syntax)

      • RANGE COLUMNS (MySQL-compatible syntax)

    • LIST partitioning

      • LIST (MySQL-compatible syntax)

      • LIST COLUMNS (MySQL-compatible syntax)

    • Scenario-specific partitioning

      CO_HASH (PolarDB-X-specific syntax)

    HASH partitioning

    HASH partitioning uses a consistent hashing algorithm to compute hash values from partition key columns or partitioning function expressions and routes data accordingly. It includes KEY partitioning (column-based) and HASH partitioning (function-based).

    Comparison between KEY partitioning and HASH partitioning

    Partitioning type

    Supported partition key

    Supports 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 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 based on the c1 column only. Other columns enable hot partition splitting.

    • n is the number of partition key columns used simultaneously.

    • 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 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 is routed based on all 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

    RANGE partitioning compares partition key values or partitioning function results against predefined boundaries to route data. It includes RANGE COLUMNS partitioning (column-based) and RANGE partitioning (function-based).

    Comparison between RANGE partitioning and RANGE COLUMNS partitioning

    Partitioning type

    Supported partition key

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

    Supports hot partition splitting. If many rows share the same c1 value (for example, 88), you can split by c2.

    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

    LIST partitioning works like RANGE partitioning but matches values against discrete value lists instead of ranges. It includes LIST COLUMNS partitioning (column-based) and LIST partitioning (function-based).

    Comparison between LIST partitioning and LIST COLUMNS partitioning

    Partitioning type

    Supported partition key

    Supports 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 COHASH partitioning for tables that require horizontal partitioning across multiple columns with similar values.

    The following table compares COHASH with HASH and KEY partitioning.

    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

    Column values must be similar. You must maintain similarity between partition key columns. 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 a composite index prefix.

    Similar to a composite index prefix.

    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 requires equivalent conditions on all partition key columns. Examples:

    • c1='x': No partition pruning. All partitions are 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. Without prefix conditions, all partitions are scanned. Examples:

    • c2='x': All partitions are scanned.

    • c1='x' or c2='y': All partitions are scanned.

    Not supported. Without prefix conditions, all partitions are scanned. 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.

    Routing policy is the same as 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

    Subpartitioning

    Supported

    Supported

    Supported