Partitioned tables in a AUTO-mode PolarDB-X database - PolarDB

This topic describes what a PolarDB-X partitioned table is, its benefits, and its differences from a MySQL partitioned table.

In an AUTO-mode PolarDB-X database, a logical table with a large amount of data is split into multiple smaller physical tables based on specific partitioning rules. The logical table is called a partitioned table, and each smaller physical table is called a partition of the larger table. PolarDB-X provides a variety of rules for partitioning tables, such as KEY, HASH, RANGE, LIST, and COHASH. These rules can also be flexibly combined to work as level-2 hybrid partitioning rules. This not only allows for efficient data management, but also makes partitioning rules highly compatible with the partitioning syntax of MySQL, thus providing users with a convenient and familiar operation experience.

PolarDB-X also provides a suite of features for convenient O&M and management, such as partition splitting, merging, data migration, and scheduling. Users can flexibly and efficiently manage their data. For example, they can isolate data of a specific partition to an independent storage node or further split a large partition into smaller subpartitions.

Benefits

Supports RANGE, RANGE COLUMN, KEY, LIST, LIST COLUMN, and COHASH partitioning rules and any combination of these partitioning rules for level-2 templated or non-templated partitions. This makes data governance more flexible.
Supports Global Secondary Indexes (GSIs). GSIs facilitate indexing for queries that are made in different dimensions.
Supports partition pruning, which enables optimizers to filter out partitions that do not satisfy specified query conditions. This results in fewer partitions being scanned during queries, thereby reducing engine workload.
Supports PartitionWise Joins. This feature significantly alleviates query loads and enhances query performance. It achieves this by abstracting partitioned tables into partition links and efficiently excluding partitions that do not meet the partitioning criteria.
Supports parallel queries, which boosts query performance by multiple times for large partitioned tables.
Supports flexible partition scheduling to simplify partition-based data isolation.

Comparison with MySQL partitioned tables

Item		PolarDB-X partitioned table	MySQL partitioned table
Partition location		Partitions are automatically distributed to different data nodes to smash the resource bottleneck of a single node.	All partitions reside on the primary node and share the resources thereon.
Supported partitioning rules		HASH/KEY RANGE/RANGE COLUMNS LIST/LIST COLUMNS CO_HASH (exclusive to PolarDB-X)	HASH/KEY RANGE/RANGE COLUMNS LIST/LIST COLUMNS
Supported partitioning functions For more information, see Partitioning functions.		You can use functions to express HAHS, RANGE, or LIST rules. The following items list the functions that can be used both in PolarDB-X and MySQL: YEAR MONTH TO_DAYS TO_SECONDS UNIX_TIMESTAMP DAYOFWEEK DAYOFMONTH DAYOFYEAR The following items list functions that can be used only in PolarDB-X: TO_MONTHS TO_WEEKS SUBSTR/SUBSTRING (For the standard MySQL function, see MySQL SUBSTR.) RIGHT (For the standard MySQL function, see MySQL RIGHT.) LEFT (For the standard MySQL function, see MySQL LEFT.)	You can use functions to express HAHS, RANGE, or LIST partitioning rules. For information about the functions that you can use to partition a MySQL table, see Partitioning Limitations Relating to Functions. MySQL does not support partitioning functions that use inputs of the character type, such as `SUBSTR`, `SUBSTRING`, `RIGHT`, and `LEFT`.
Hash partitioning rules	HASH	Uses the consistent hashing routing algorithm. Supports vector partition key columns, such as Partition By Hash(a,b). For more information, see HASH partitioning.	Uses the modular hashing routing algorithm. Does not support vector partition key columns, such as `Partition By Hash(a,b)`.
	KEY	Uses the consistent hashing routing algorithm. Supports vector partition key columns. By default, the first column is used for route calculation. For more information, see KEY partitioning. Supports hot data distribution. Supports partition pruning based on equality conditions for partition key columns with prefixes.	Uses the modular hashing routing algorithm. Supports vector partition key columns. All columns are used for route calculation. Does not support hot data distribution. Does not support partition pruning based on equality conditions for partition key columns with prefixes. All equality conditions that have partition key columns must be used for partition pruning.
	CO_HASH	This rule is exclusive to PolarDB-X. For more information, see CO_HASH partitioning. Supports horizontal partitioning by two or more partition key columns at the same time. Supports partition pruning for the equivalent query for all partition key columns.	CO_HASH is not supported.
Range partitioning rules	RANGE	Supports partitioning functions that use inputs of the character type, such as right/left/substr. For more information, see RANGE partitioning.	Does not support partitioning functions that use inputs of the character type, such as right/left/substr.
Range partitioning rules	RANGE COLUMNS	Supports a maximum of five partition key columns for partition definition by default. Parameters can be adjusted. For more information, see RANGE COLUMNS partitioning.	Does not limit the number of partition key columns.
List partitioning rules	LIST	Supports the definition of default partitions. Supports partition key columns of the character type. Supports partitioning functions that use inputs of the character type, such as RIGHT, LEFT, and SUBSTR. Supports the separate modification of partitioning value sets. For more information, see LIST partitioning.	Does not support the definition of default partitions. Does not support partition key columns of the character type. Does not support partitioning functions that use inputs of the character type, such as RIGHT, LEFT, and SUBSTR. Does not support the separate modification of partitioning value sets.
List partitioning rules	LIST COLUNNS	Supports the definition of default partitions. Supports the separate modification of partitioning value sets. For more information, see LIST COLUMNS partitioning.	Does not support the definition of default partitions. Does not support the separate modification of partitioning value sets.
Secondary partitioning rules	Hybrid rules	Up to 49 hybrid rules can be used based on seven orthometric level-1 partitioning rules and level-2 partitioning rules.	The HASH and KEY partitioning rules cannot be used to define level-2 partitioning rules. You can define level-2 partitioning rules by using the following four partitioning rules: RANGE, RANGE COLUMNS, LIST, and LIST COLUMNS. The level-2 partitioning rules must be HASH or KEY rules. The number of hybrid partitioning rules is eight.
	Templated and non-templated	All hybrid rules can be used as templates or non-templates. For more information, see Secondary partitioning.	All hybrid rules can be used only as level-2 partitioning templates. Non-templated level-2 partitioning rules are not supported.
	Location distribution	Partition-level locality is supported. Allows you to specify the locality definition of a level-2 partition to control the physical location distribution of a single level-2 partition. Allows you to specify the locality definition of a level-1 partition to control the physical location distribution of level -2 partitions under each level-1 partition. For example, you can distribute all level-2 partitions of a level-1 partition to the same data node.	Partition-level locality is not supported.
Partition level changes		PolarDB-X partitioned tables (including level-2 partitions) support the following operations on partition definitions: Split a partition into multiple partitions Merge partitions Migrate a partition Add partitions Delete partitions Modify a partition Reorganize partitions Clear partitions Rename partitions All partition O&M changes are online DDL operations that do not lock tables.	Partitioned MySQL tables support the following changes to partition definitions: Merge partitions Reorganize partitions Add partitions Delete partitions Clear a partition Exchange partitions Most partition-level changes lock tables.
Partitioned table redefinition		Supports online data exchange between partitioned tables, non-partitioned tables, and broadcast tables. However, data needs to be migrated. Redefinition is an online DDL operation that does not lock tables.	You can use Remove Partitioning to switch between a partitioned table and the original non-partitioned table. Redefinition locks tables.
Partition key column constraints		In PolarDB-X, partitioned tables do not require the primary key and unique key to contain all partition key columns (including level-1 partitions and level-2 partitions).	In MySQL, partitioned tables require the primary key and unique key to contain all partition key columns (including level-1 partitions and level-2 partitions).
Global indexes		Supports partitioned table definitions in the global index table. Supports separate O&M changes for partitions in the global index table.	Global indexes are not supported.