Change Table Types and Partitioning Policies with Zero Downtime - PolarDB

As data grows or query patterns shift, a table's original schema can become a performance bottleneck—single tables slow down under high volume, or a mismatched partition key causes data hot spots. PolarDB for Xscale lets you convert tables between single, broadcast, and partitioned types and adjust the partition keys or algorithms of existing partitioned tables, all online without locking the table or blocking Data Manipulation Language (DML) operations.

Important

Changing a table type or partitioning policy is a heavy DDL task. It performs a full data migration online and consumes significant CPU, I/O, and network resources. The time required is proportional to the data volume. Run these operations during off-peak hours and monitor progress with task management commands.

Prerequisites

Before you begin, make sure that:

Database mode: The database is in AUTO mode.
Instance version: The PolarDB-X 2.0 instance runs minor engine version 5.4.13 or later.
Global secondary index (GSI): To change the partitioning policy of a partitioned table that has a GSI, the instance must run version 5.4.14 or later. During the change, GSI data is also redistributed.

For version naming rules, see Release notes. To check your instance version, see View and update the version of an instance.

How it works

All change operations run as online DDL. The original table is not locked, and DML and SELECT operations continue normally throughout.

The core of any table type or partitioning policy change is physical data migration:

Converting a single table to a partitioned table distributes data from one data node (DN) to multiple DNs.
Changing a partition key rebalances data among DNs according to the new policy—the primary source of resource consumption.

The entire DDL task is atomic. If it fails—due to a unique key conflict, insufficient disk space, or any other reason—the original table data remains intact and your business is not affected. Use the CANCEL DDL command to roll back a failed task.

Change table types

PolarDB for Xscale supports three table types. Convert between them online as your business needs change. For full syntax, see CREATE TABLE (AUTO mode).

If the original table is a key partitioned table (where the partition key equals the primary key) and you convert it to a single table, it loses its primary key partitioning features, including automatic partitioning policies and index conversion rules. For details, see Automatic sharding in AUTO mode.

Convert a single or broadcast table to a partitioned table

When to use: A single table's data volume is growing and approaching a performance bottleneck. Horizontally splitting the table improves scalability and query performance.

Impact: Heavy—involves full data migration across DNs.

Syntax:

ALTER TABLE table_name PARTITION BY partition_options;

For partition_options syntax, see Partitioned tables.

Example:

Create a sample table 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;

Convert t_order to a KEY partitioned table using order_id as the partition key.

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

-- Optionally, specify the number of partitions.
ALTER TABLE t_order PARTITION BY KEY(`order_id`) PARTITIONS 8;

Convert a partitioned or single table to a broadcast table

When to use: The table has a small, relatively static dataset and is frequently joined with large tables—for example, configuration tables or dictionary tables. Replicating the table to every DN eliminates cross-database join overhead.

Impact: Heavy—data is replicated to all DNs.

Broadcast tables store a full replica on every DN, which increases storage usage.

Syntax:

ALTER TABLE table_name BROADCAST;

Example:

Create a sample table 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 t_order_1 to a broadcast table.
```
ALTER TABLE t_order_1 BROADCAST;
```

Convert a partitioned or broadcast table to a single table

When to use: The table has a small data volume and does not require horizontal scaling. Consolidating onto a single DN simplifies the schema.

Impact: Heavy—all shard data is consolidated onto one DN. Verify that the target node has enough disk space before running this operation.

Syntax:

ALTER TABLE table_name SINGLE;

Example:

Create a sample partitioned table 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 t_order_2 to a single table.
```
ALTER TABLE t_order_2 SINGLE;
```

Change the partitioning policy of a partitioned table

When an existing partitioning policy no longer fits—due to data skew from a poor partition key choice or shifting query patterns—adjust the partition key, partition function, or number of partitions online to re-optimize physical data distribution.

Impact: Heavy—involves full data migration. Adding an empty partition to a RANGE or LIST partitioned table (ALTER TABLE ... ADD PARTITION) is a lightweight metadata operation that completes in seconds.

Syntax:

ALTER TABLE tbl_name PARTITION BY new_partition_options;

Index behavior when changing the partitioning policy:

Condition	Behavior
New partition key columns are the leftmost prefix of an existing index	System reuses that index for partition pruning; no new index is created
New partition key columns are not the leftmost prefix of any existing index	System automatically creates a new local index named `auto_shard_key_<column(s)>`
Primary key does not include all partition key columns after the change	Primary key becomes a local primary key—unique only within a partition, not globally. See Primary keys and unique keys (AUTO mode)
Original table is a key partitioned table and the new partition key is not the primary key	Table loses primary key partitioning features such as automatic partition creation. See Automatic sharding in AUTO mode

Change the partition key

Scenario: Table t_order_3 is partitioned by order_id, but most queries filter on buyer_id, causing frequent cross-shard queries. Change the partition key to buyer_id and increase 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;

Verify the result. Because buyer_id is not the leftmost prefix of any existing index, the system automatically creates a local index 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

Scenario: Table t_order_4 is RANGE partitioned on id, but most queries filter on order_id and buyer_id. Change to KEY partitioning on both columns with 16 partitions.

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;

Verify the result. The system creates a composite local index 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

Scenario: As data volume grows, increase partitions to distribute 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;

Verify the result. The partition count increases 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` */ |
+-----------+---------------------------------------------------------+

Monitor and manage DDL tasks

Use the following commands to manage data redistribution tasks throughout their lifecycle.

Monitor task progress

Use SHOW DDL to view current and historical DDL tasks.

SHOW DDL;

Pause and resume a task

If a running task affects performance during peak hours, pause it and resume during off-peak hours.

-- Pause the task. Get <JobId> from SHOW DDL output.
PAUSE DDL <JobId>;

-- Resume the task.
CONTINUE DDL <JobId>;

Cancel and roll back a task

Use CANCEL DDL to terminate a running or failed task. The system rolls back all changes and restores the table schema and data to their pre-task state.

-- Roll back the task. Get <JobId> from SHOW DDL output.
CANCEL DDL <JobId>;

Going live

Timing: Run changes during off-peak hours to minimize impact on your production workload.
Capacity planning: Before running a change, verify that the target DNs have sufficient disk space, CPU, and IOPS—especially when converting a partitioned table to a single table or when redistributing large datasets.
Backup: Create a full data backup before making major schema changes in a production environment, even though a failed DDL task can be rolled back.
Verification: After the task completes, run SHOW CREATE TABLE <tablename> to view the new table definition, or use SHOW TOPOLOGY to view the new data sharding topology.

FAQ

Why did my partition key change fail, and how do I recover?

The most common causes are insufficient disk space on the target DN, unique index conflicts during data migration, or an instance crash. A failed task does not corrupt the original table data or block DML and query operations. Run SHOW DDL to identify the failure reason, fix the underlying issue (for example, free up disk space or resolve conflicting rows), then use CANCEL DDL <JobId> to roll back the failed task before retrying.

Is adding a new partition always as slow as changing a partition key?

No. Changing a table type or partitioning policy is time-consuming because it involves full data migration. Adding a future empty partition to a RANGE or LIST partitioned table (using ALTER TABLE ... ADD PARTITION) is a metadata operation that completes in seconds with minimal impact on your workload.

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

No. 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 one. If not, the system automatically creates a new local index to maintain query performance.

How do I check whether a database is in AUTO mode?

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