Auto Partitioning Sharding Rules & Index Conversion Overview - PolarDB - Alibaba Cloud - PolarDB

When you create a database in automatic partitioning mode, PolarDB-X shards tables by primary key by default—no partitioning keywords needed. This lets you use standard CREATE TABLE syntax while PolarDB-X automatically selects the shard key and sharding algorithm.

This topic covers the automatic sharding rules, index conversion rules, and DDL constraints for tables that are automatically sharded by primary key.

Prerequisites

Before you begin, make sure that:

Your PolarDB-X version is 5.4.13 or later
The database was created with MODE = auto or MODE = partitioning (see CREATE DATABASE)

How automatic sharding works

PolarDB-X uses KEY partitioning for automatically sharded tables. At table creation time, the system inspects the primary key columns, skips any columns with unsupported data types, and uses the remaining columns as shard keys—no extra configuration required.

Shard key selection

Primary key type	Shard key used
No primary key	Implicit primary key (`_drds_implicit_id_`): a `BIGINT` auto-increment column, invisible to users. Run `show full create table` to view it.
Single-column primary key	The primary key column
Composite primary key	All columns in the composite key (columns with unsupported data types are skipped)

Supported data types

PolarDB-X applies KEY partitioning only to columns with supported data types. If a column has an unsupported type, the system skips it when building the shard key.

Supported types (KEY partitioning):

Integer types (tinyint, smallint, mediumint, int, bigint, and their unsigned variants), date, datetime, timestamp, char, varchar

Unsupported types (not eligible for automatic sharding):

bit, float, double, time, year, tinyblob, blob, mediumblob, longblob, enum, decimal, binary, varbinary, tinytext, text, mediumtext, longtext, set, geometry

Composite key behavior: If a column in the composite key has an unsupported type, PolarDB-X skips it. If the first column of the composite key has an unsupported type, an error is reported—you must manually create the table as a single table instead. See Create a non-partitioned table and a broadcast table.

Usage notes

Specify a table as a single table or replicated table at creation time if needed. All tables default to partitioned tables sharded by primary key.
Specify a primary key when creating a table. After creation, you cannot add or drop the primary key.
If no primary key is specified, PolarDB-X automatically creates an implicit primary key (_drds_implicit_id_). Run show full create table to view the sharding details.
Indexes on automatically sharded tables default to global secondary indexes (GSIs), unless the first column's data type is unsupported or you explicitly specify LOCAL.
GSI names in automatic partitioning mode are optional and do not need to be unique in CREATE TABLE statements. PolarDB-X assigns random suffixes to all GSI tables. Run show full create table to view them.
After you change the sharding rule of an automatically sharded table, the table becomes a standard table. Automatic sharding and index conversion rules no longer apply. See Change the type and modify the partitioning rule of a table.

Syntax and examples

Automatic sharding is enabled by default. No special keywords are needed at table creation time. For the full CREATE TABLE syntax, see CREATE TABLE.

Use show create table to view the simplified schema and show full create table to view the complete schema including partition details.

Example 1: Composite primary key

The table tb has a composite primary key (x, y, z, d). Because z is float (unsupported), PolarDB-X uses x, y, and d as the shard key.

CREATE TABLE `tb` (
  `x` int NOT NULL AUTO_INCREMENT,
  `y` int NOT NULL,
  `z` float NOT NULL,
  `d` int NOT NULL,
  PRIMARY KEY (x,y,z,d)
);
Query OK, 0 rows affected (0.42 sec)

show create table tb;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| TABLE | CREATE TABLE                                                                                                                                                                                                                                                              |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tb    | CREATE TABLE `tb` (
        `x` int(11) NOT NULL AUTO_INCREMENT,
        `y` int(11) NOT NULL,
        `z` float NOT NULL,
        `d` int(11) NOT NULL,
        PRIMARY KEY (`x`, `y`, `z`, `d`),
        LOCAL KEY `auto_shard_key_x_y_d` USING BTREE (`x`, `y`, `d`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

show full create table tb;
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| TABLE | CREATE TABLE                                                                          |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tb    | CREATE PARTITION TABLE `tb` (
        `x` int(11) NOT NULL AUTO_INCREMENT,
        `y` int(11) NOT NULL,
        `z` float NOT NULL,
        `d` int(11) NOT NULL,
        PRIMARY KEY (`x`, `y`, `z`, `d`),
        LOCAL KEY `auto_shard_key_x_y_d` USING BTREE (`x`, `y`, `d`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4
PARTITION BY KEY(`x`,`y`,`d`)
PARTITIONS 16
/* tablegroup = `tg8` */ |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Example 2: Single primary key with indexes

The table t_order has a single int primary key x. PolarDB-X shards on x and automatically converts both indexes to GSIs.

CREATE TABLE `t_order` (
  `x` int NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `order_id` varchar(20) DEFAULT NULL,
  `seller_id` varchar(20) DEFAULT NULL,
  INDEX (`seller_id`),
  UNIQUE INDEX (`order_id`)
);
Query OK, 0 rows affected (0.84 sec)

show create table `t_order`;
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| TABLE   | CREATE TABLE                                                                                                                                                                                                                                                             |
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t_order | CREATE TABLE `t_order` (
        `x` int(11) NOT NULL AUTO_INCREMENT,
        `order_id` varchar(20) DEFAULT NULL,
        `seller_id` varchar(20) DEFAULT NULL,
        PRIMARY KEY (`x`),
        INDEX `i_0` (`seller_id`),
        UNIQUE INDEX `i_1` (`order_id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 |
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

show full create table `t_order`;
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| TABLE   | CREATE TABLE          |
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t_order | CREATE PARTITION TABLE `t_order` (
        `x` int(11) NOT NULL AUTO_INCREMENT,
        `order_id` varchar(20) DEFAULT NULL,
        `seller_id` varchar(20) DEFAULT NULL,
        PRIMARY KEY (`x`),
        GLOBAL INDEX /* i_0_$cff4 */ `i_0` (`seller_id`) PARTITION BY KEY (`seller_id`, `x`) PARTITIONS 16,
        UNIQUE GLOBAL INDEX /* i_1_$1782 */ `i_1` (`order_id`) PARTITION BY KEY (`order_id`) PARTITIONS 16,
        UNIQUE LOCAL KEY `_local_i_1` (`order_id`),
        LOCAL KEY `_local_i_0` (`seller_id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4
PARTITION BY KEY(`x`)
PARTITIONS 16
/* tablegroup = `tg8` */ |
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Index conversion rules

By default, indexes on automatically sharded tables become GSIs. Specify LOCAL explicitly to create a local index instead.

Core rule: Indexes default to GSI unless you specify LOCAL.

Keyword used	Actual index created
`[UNIQUE] LOCAL INDEX`	Local index
`[UNIQUE] INDEX` (no type specified)	GSI, sharded based on the automatic sharding rules
`[UNIQUE] GLOBAL INDEX`	GSI
`[UNIQUE] CLUSTERED INDEX`	Clustered GSI

Additional rules:

When PolarDB-X generates a sharding algorithm for a GSI, it uses all index key columns. For non-UNIQUE indexes, the primary key is also included as a sharding parameter.
If no shard key is specified for a GSI or clustered index, PolarDB-X applies the automatic sharding rules to the index key.
Creating a GSI or clustered index also automatically creates a corresponding local index prefixed with _local_. Dropping the GSI automatically drops the associated local index.

The following example creates a table with all supported index types:

CREATE PARTITION TABLE `t_order` (
  `x` int,
  `order_id` varchar(20) DEFAULT NULL,
  `seller_id` varchar(20) DEFAULT NULL,
  LOCAL INDEX `l_seller` using btree (`seller_id`),         -- Local index
  UNIQUE LOCAL INDEX `l_order` using btree (`order_id`),    -- Unique local index
  INDEX `i_seller` using btree (`seller_id`),               -- GSI (auto-sharded)
  UNIQUE INDEX `i_order` using btree (`order_id`),          -- Unique GSI (auto-sharded)
  GLOBAL INDEX `g_seller` using btree (`seller_id`),        -- GSI (auto-sharded)
  UNIQUE GLOBAL INDEX `g_order` using btree (`order_id`),   -- Unique GSI (auto-sharded)
  CLUSTERED INDEX `c_seller` using btree (`seller_id`),     -- Clustered GSI (auto-sharded)
  UNIQUE CLUSTERED INDEX `c_order` using btree (`order_id`) -- Unique clustered GSI (auto-sharded)
);
Query OK, 0 rows affected (1.49 sec)

show create table `t_order`;
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| TABLE   | CREATE TABLE                              |
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t_order | CREATE TABLE `t_order` (
        `x` int(11) DEFAULT NULL,
        `order_id` varchar(20) DEFAULT NULL,
        `seller_id` varchar(20) DEFAULT NULL,
        UNIQUE CLUSTERED INDEX `c_order` USING BTREE (`order_id`),
        CLUSTERED INDEX `c_seller` USING BTREE (`seller_id`),
        UNIQUE INDEX `g_order` USING BTREE (`order_id`),
        INDEX `g_seller` USING BTREE (`seller_id`),
        UNIQUE INDEX `i_order` USING BTREE (`order_id`),
        INDEX `i_seller` USING BTREE (`seller_id`),
        UNIQUE LOCAL KEY `l_order` USING BTREE (`order_id`),
        LOCAL KEY `l_seller` USING BTREE (`seller_id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 |
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

show full create table `t_order`;
+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| TABLE   | CREATE TABLE                                                                                                                                                                                                  |
+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t_order | CREATE PARTITION TABLE `t_order` (
        `x` int(11) DEFAULT NULL,
        `order_id` varchar(20) DEFAULT NULL,
        `seller_id` varchar(20) DEFAULT NULL,
        `_drds_implicit_id_` bigint(20) NOT NULL AUTO_INCREMENT,
        PRIMARY KEY (`_drds_implicit_id_`),
        UNIQUE CLUSTERED INDEX /* c_order_$1ba0 */ `c_order` USING BTREE (`order_id`) PARTITION BY KEY (`order_id`) PARTITIONS 16,
        CLUSTERED INDEX /* c_seller_$1e39 */ `c_seller` USING BTREE (`seller_id`) PARTITION BY KEY (`seller_id`, `_drds_implicit_id_`) PARTITIONS 16,
        UNIQUE GLOBAL INDEX /* g_order_$d57f */ `g_order` USING BTREE (`order_id`) PARTITION BY KEY (`order_id`) PARTITIONS 16,
        GLOBAL INDEX /* g_seller_$6ed5 */ `g_seller` USING BTREE (`seller_id`) PARTITION BY KEY (`seller_id`, `_drds_implicit_id_`) PARTITIONS 16,
        UNIQUE GLOBAL INDEX /* i_order_$ab2f */ `i_order` USING BTREE (`order_id`) PARTITION BY KEY (`order_id`) PARTITIONS 16,
        GLOBAL INDEX /* i_seller_$2b4b */ `i_seller` USING BTREE (`seller_id`) PARTITION BY KEY (`seller_id`, `_drds_implicit_id_`) PARTITIONS 16,
        UNIQUE LOCAL KEY `l_order` USING BTREE (`order_id`),
        UNIQUE LOCAL KEY `_local_i_order` USING BTREE (`order_id`),
        UNIQUE LOCAL KEY `_local_g_order` USING BTREE (`order_id`),
        UNIQUE LOCAL KEY `_local_c_order` USING BTREE (`order_id`),
        LOCAL KEY `l_seller` USING BTREE (`seller_id`),
        LOCAL KEY `_local_i_seller` USING BTREE (`seller_id`),
        LOCAL KEY `_local_g_seller` USING BTREE (`seller_id`),
        LOCAL KEY `_local_c_seller` USING BTREE (`seller_id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4
PARTITION BY KEY(`_drds_implicit_id_`)
PARTITIONS 16
/* tablegroup = `tg11` */ |
+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

DDL constraints

The following constraints apply to DDL operations on automatically sharded tables.

CREATE INDEX

Clause	Behavior
`[UNIQUE] LOCAL INDEX`	Creates a local index and adds it to the table's clustered index
`[UNIQUE] INDEX`	Creates a GSI and a corresponding `_local_`-prefixed local index, both added to the clustered index. If no shard key is specified, automatic sharding rules apply.
`[UNIQUE] GLOBAL INDEX` or `[UNIQUE] CLUSTERED INDEX`	Creates the GSI or clustered index, plus a `_local_`-prefixed local index added to the clustered index. If no shard key is specified, automatic sharding rules apply.

ALTER TABLE

Clause	Constraint
`ADD {INDEX \| KEY}`	Same conversion rules as `CREATE INDEX` apply
`ADD [COLUMN]`	The new column is automatically added to all clustered GSIs and unique clustered GSIs. Rollback is supported.
`DROP [COLUMN]`	Cannot drop the primary key, the table's shard key, an index table's shard key, or unique columns of a composite index
`CHANGE [COLUMN]`	Cannot rename the primary key, the table's shard key, an index table's shard key, or unique columns of a composite index. To remove column constraints (except unique columns of a composite index), use the hint `/+TDDL: cmd_extra(ALLOW_LOOSE_ALTER_COLUMN_WITH_GSI=true)/`. See How to use hints. To change a column's default value, use `SET DEFAULT` or `DROP DEFAULT` instead.
`MODIFY [COLUMN]`	Same rename and hint constraints as `CHANGE [COLUMN]` apply
`ALTER [COLUMN] col_name { SET DEFAULT \| DROP DEFAULT }`	Sets or removes a column's default value. Rollback is supported, except for columns of type `CURRENT_TIMESTAMP`.

What's next

CREATE TABLE: Full syntax reference for creating tables in automatic partitioning mode
Create a non-partitioned table and a broadcast table: For tables whose primary key data types are unsupported for automatic sharding
Change the type and modify the partitioning rule of a table: How to change sharding rules after table creation
How to use hints: Hint syntax reference for advanced DDL operations