how to change table types and partitioning policies (including partition functions or partition key columns) - PolarDB

When a large data volume in a single table slows down queries, or an improper initial partitioning policy causes data hot spots, the original table schema can become a performance bottleneck. To solve these issues, PolarDB for Xscale provides an online feature to change table types and partitioning policies. This feature lets you redistribute data without interrupting your business because it does not lock tables or block Data Manipulation Language (DML) operations. You can convert tables between single, broadcast, and partitioned types. You can also adjust the partition keys or algorithms of existing partitioned tables. This optimizes the data layout and improves overall database performance and scalability.

Important

This operation is a heavy Data Definition Language (DDL) task. It performs a full data migration and redistribution online.
During execution, the task consumes significant CPU, I/O, and network resources, which may affect instance performance. The time required is proportional to the data volume.
Run this operation during off-peak hours and use task management commands to monitor its progress.

Applicability

Before you perform this change, ensure that your environment meets the following conditions.

Database mode: This feature applies only to databases in AUTO mode.
Instance version: Only PolarDB-X 2.0 instances with a 5.4.13 or later minor engine version are supported.
Global secondary index (GSI): To change partitions for a partitioned table with a GSI, you need a PolarDB-X 2.0 instance with a 5.4.14 or later minor engine version. During the change, the GSI data is also redistributed.

Note

For information about the instance version naming rules, see Release notes.
For information about how to view the version of an instance, see View and update the version of an instance.

How it works

Online DDL: All change operations run online. The original table is not locked. DML and SELECT operations can proceed as normal to ensure business continuity.
Data redistribution: The core of changing a table type or partitioning policy is physical data migration. For example:
- When you convert a single table to a partitioned table, data is distributed from a single data node (DN) to multiple DNs.
- When you change a partition key, data is rebalanced among DNs based on the new partitioning policy. This process is the primary cause of resource consumption.
Atomicity and rollback: The entire DDL task is atomic. If the task fails because of a unique key conflict, insufficient disk space, or other reasons, the system ensures that the original table data remains intact. Your business is not affected. You can roll back a failed task using the CANCEL DDL command.

Change table types

PolarDB for Xscale supports three table types: single tables, broadcast tables, and partitioned tables. You can convert tables between these types online as your business scenarios change. For more information, see CREATE TABLE (AUTO mode).

Note

When you change the table type, if the original table is a key partitioned table (where the partition key is the same as the primary key) and you convert it to a standard table, it loses its primary key partitioning features. These features include automatic partitioning policies and index conversion rules. For more information, see Automatic sharding in AUTO mode.

Convert a single table or broadcast table to a partitioned table

This scenario is suitable when the data volume of a single table is growing. If the table has become or is about to become a performance bottleneck, you can horizontally split it to improve scalability and query performance.

Syntax

Use the ALTER TABLE statement and specify a new partitioning policy. For more information about the partition_options syntax, see Partitioned tables.

ALTER TABLE table_name PARTITION BY partition_options;

Example

Create a sample table named t_order.

CREATE TABLE t_order (
  `id` bigint(11) NOT NULL AUTO_INCREMENT,
  `order_id` varchar(20) DEFAULT NULL,
  `buyer_id` varchar(20) DEFAULT NULL,
  `seller_id` varchar(20) DEFAULT NULL,
  `order_snapshot` longtext DEFAULT NULL,
  `order_detail` longtext DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `l_i_order` (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

The data volume of the single table t_order has increased due to business growth. Convert it to a partitioned table with order_id as the partition key.

-- Convert the single table t_order to a KEY partitioned table with order_id as the partition key.
ALTER TABLE t_order PARTITION BY KEY(`order_id`);

-- You can also specify the number of partitions during the conversion.
ALTER TABLE t_order PARTITION BY KEY(`order_id`) PARTITIONS 8;

Convert a partitioned table or single table to a broadcast table

This scenario is suitable for tables with small and relatively static data volumes that are frequently joined with large tables, such as configuration tables and dictionary tables. By replicating the table data to each data node (DN), you can eliminate the network overhead of cross-database joins and improve join query performance.

Syntax

Use the ALTER TABLE statement and specify the BROADCAST clause.

ALTER TABLE table_name BROADCAST;

Note

Broadcast tables store a replica of the data on every DN, which consumes more storage space.

Example

Create a sample table named t_order_1.

CREATE TABLE t_order_1 (
  `id` bigint(11) NOT NULL AUTO_INCREMENT,
  `order_id` varchar(20) DEFAULT NULL,
  `buyer_id` varchar(20) DEFAULT NULL,
  `seller_id` varchar(20) DEFAULT NULL,
  `order_snapshot` longtext DEFAULT NULL,
  `order_detail` longtext DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `l_i_order` (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Convert the t_order_1 table to a broadcast table.
```
ALTER TABLE t_order_1 BROADCAST;
```

Convert a partitioned table or broadcast table to a single table

This scenario is suitable for tables with small data volumes that do not require horizontal scaling. You can consolidate the table's data onto a single data node (DN) to simplify the table schema.

Syntax

Use the ALTER TABLE statement and specify the SINGLE clause.

ALTER TABLE table_name SINGLE;

Note

When you convert a partitioned table to a single table, data from all shards is consolidated onto a single DN. Ensure that the target node has enough disk space to prevent the operation from failing because of insufficient space.

Example

Create a sample table named t_order_2.

CREATE TABLE t_order_2 (
  `id` bigint(11) NOT NULL AUTO_INCREMENT,
  `order_id` varchar(20) DEFAULT NULL,
  `buyer_id` varchar(20) DEFAULT NULL,
  `seller_id` varchar(20) DEFAULT NULL,
  `order_snapshot` longtext DEFAULT NULL,
  `order_detail` longtext DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `l_i_order` (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY KEY(`order_id`)
PARTITIONS 8;

Convert the t_order_2 table to a single table.
```
ALTER TABLE t_order_2 SINGLE;
```

Change the partitioning policy of a partitioned table

When the existing partitioning policy no longer meets business needs, you can adjust it online. This is useful for issues such as data skew caused by a poor partition key choice or changes in query patterns. You can modify the partition key, partition function, or number of partitions to re-optimize the physical data distribution. This helps resolve data hot spots and adapt to new business models.

Syntax

Use the ALTER TABLE statement and specify a new partitioning policy.

ALTER TABLE tbl_name PARTITION BY new_partition_options;

Note

If the original table is a key partitioned table, and you change the partitioning policy so that the new partition key is not the primary key, the table loses its primary key partitioning features, such as automatic partition creation. For more information, see Automatic sharding in AUTO mode.
After you change the partitioning policy, if the primary key does not include all partition key columns, the primary key becomes a local primary key. It can only guarantee uniqueness within a partition, not globally. For more information, see Primary keys and unique keys (AUTO mode).

Examples

Change the partition key

A table named t_order_3 is currently partitioned by order_id. However, most queries are based on buyer_id, which leads to many cross-shard queries. You can change the partition key to buyer_id and set the number of partitions to 16.

View the original table schema.

CREATE TABLE t_order_3 (
  `id` bigint(11) NOT NULL AUTO_INCREMENT,
  `order_id` varchar(20) DEFAULT NULL,
  `buyer_id` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 
PARTITION BY KEY(`order_id`)
PARTITIONS 8;

Run the change.

ALTER TABLE t_order_3 PARTITION BY KEY(buyer_id) PARTITIONS 16;

View the table schema after the change. The system automatically creates a new partition key index named auto_shard_key_buyer_id.

SHOW FULL CREATE TABLE  t_order_3;

+-----------+-----------------------------------------------------------------------+
| Table     | Create Table                                                          |
+-----------+-----------------------------------------------------------------------+
| t_order_3 | CREATE TABLE `t_order_3` (
        `id` bigint NOT NULL AUTO_INCREMENT,
        `order_id` varchar(20) DEFAULT NULL,
        `buyer_id` varchar(20) DEFAULT NULL,
        PRIMARY KEY (`id`),
        LOCAL KEY `auto_shard_key_buyer_id` USING BTREE (`buyer_id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 DEFAULT COLLATE = utf8mb4_0900_ai_ci
PARTITION BY KEY(`buyer_id`)
PARTITIONS 16
/* tablegroup = `tg12` */ |
+-----------+---------------------------------------------------------------------+

Change the partitioning strategy

A table named t_order_4 is currently partitioned by RANGE on the id column. However, most queries are based on order_id and buyer_id. You can change the partitioning to KEY partitioning with order_id and buyer_id as partition keys, and set the number of partitions to 16.

View the original table schema.

CREATE TABLE t_order_4 (
  `id` bigint(11) NOT NULL AUTO_INCREMENT,
  `order_id` varchar(20) DEFAULT NULL,
  `buyer_id` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 
PARTITION BY RANGE(`id`)
(
  PARTITION p1 VALUES LESS THAN (100),
  PARTITION p2 VALUES LESS THAN (1000),
  PARTITION P3 VALUES LESS THAN MAXVALUE
);

Run the change.

ALTER TABLE t_order_4 PARTITION BY KEY(order_id, buyer_id) PARTITIONS 16;

View the table schema after the change. The system automatically creates a new partition key index named auto_shard_key_order_id_buyer_id.

SHOW FULL CREATE TABLE  t_order_4;

+-----------+--------------------------------------------------------------------------------+
| Table     | Create Table                                                                   |
+-----------+--------------------------------------------------------------------------------+
| t_order_4 | CREATE TABLE `t_order_4` (
        `id` bigint NOT NULL AUTO_INCREMENT,
        `order_id` varchar(20) DEFAULT NULL,
        `buyer_id` varchar(20) DEFAULT NULL,
        PRIMARY KEY (`id`),
        LOCAL KEY `auto_shard_key_order_id_buyer_id` USING BTREE (`order_id`, `buyer_id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb3
PARTITION BY KEY(`order_id`,`buyer_id`)
PARTITIONS 16
/* tablegroup = `tg15` */ |
+-----------+------------------------------------------------------------------------------+

Increase the number of partitions

As the data volume grows, you can increase the number of partitions to distribute the data more widely.

View the original table schema.

CREATE TABLE t_order_5 (
  `id` bigint(11) NOT NULL AUTO_INCREMENT,
  `order_id` varchar(20) DEFAULT NULL,
  `buyer_id` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 
PARTITION BY KEY(`order_id`)
PARTITIONS 8;

Run the change.

ALTER TABLE t_order_5 PARTITION BY KEY(order_id) PARTITIONS 128;

After the change, the number of partitions is increased from 8 to 128.

SHOW FULL CREATE TABLE  t_order_5;

+-----------+----------------------------------------------------------+
| Table     | Create Table                                             |
+-----------+----------------------------------------------------------+
| t_order_4 | CREATE TABLE `t_order_5` (
        `id` bigint NOT NULL AUTO_INCREMENT,
        `order_id` varchar(20) DEFAULT NULL,
        `buyer_id` varchar(20) DEFAULT NULL,
        PRIMARY KEY (`id`),
        LOCAL KEY `auto_shard_key_order_id` USING BTREE (`order_id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb3
PARTITION BY KEY(`order_id`)
PARTITIONS 128
/* tablegroup = `tg13` */ |
+-----------+---------------------------------------------------------+

Task management and emergency handling

For time-consuming data redistribution DDL tasks, you can use the following commands for full lifecycle management.

Monitor task progress: You can use the SHOW DDL command to view a list of current and historical DDL tasks and their statuses.
Pause and resume tasks: If a DDL task significantly affects performance during peak business hours, you can pause it and resume it during off-peak hours.
```
-- Pause the task. Get the JobId from the result of SHOW DDL.
PAUSE DDL <JobId>;

-- Resume the task.
CONTINUE DDL <JobId>;
```
Terminate and roll back tasks: If a DDL task fails, or if you want to cancel a running task, you can use the CANCEL DDL command. This operation rolls back all changes, restoring the table schema and data to their state before the task started.
```
-- Roll back the task. Get the JobId from the result of SHOW DDL.
CANCEL DDL <JobId>;
```

Going live

Execution time: Run these operations during off-peak hours to minimize the potential performance impact on your online business.
Capacity planning: Before you run the change, assess whether the target data nodes (DNs) have sufficient disk space, CPU, and input/output operations per second (IOPS) resources. This is especially important for scenarios such as converting a partitioned table to a single table or for drastic data redistribution, to prevent task failure because of insufficient resources.
Pre-operation backup: Although a failed DDL task can be rolled back, we recommend that you create a full data backup before you perform any major changes in a production environment.
Verification: After the task is complete, you can use SHOW CREATE TABLE <tablename> to view the new table definition or use SHOW TOPOLOGY to view the new data sharding topology.

FAQ

Why does a DDL task for changing a partition key fail? What do I do?

Common reasons for failure include instance crashes, insufficient disk space on the target node, or unique index conflicts during data migration. A failed task does not corrupt the original table data or block normal DML and query operations. You can use SHOW DDL to find the cause of the failure. After you resolve the issue (for example, by clearing disk space or handling conflicting data), you can use the CANCEL DDL command to roll back the failed task, and then try to run it again.

Are operations such as changing a partitioning policy always time-consuming? Is adding a new empty partition also slow?

No, not all partitioning operations are heavy. Changing a table type or partitioning policy is time-consuming because it involves full data migration. However, adding a future empty partition to a RANGE/LIST partitioned table (for example, using ALTER TABLE ... ADD PARTITION) is typically a metadata operation that completes in seconds with minimal impact on your business.

If an index already exists on the new partition key, does the system still automatically create an auto_shard_key_... index?

The system checks whether the new partition key columns are the leftmost prefix of an existing index. If they are, the system reuses that index for partition pruning and does not create a new index. If not, the system automatically creates a new local index to ensure query performance.

How do I check if a database is in AUTO mode or DRDS mode?

You can run SHOW CREATE DATABASE <database_name>. The MODE property in the result indicates the database mode.