Principle scenarios and usage of automatic partitioning - PolarDB

When you migrate a MySQL application to PolarDB-X, rewriting DDL statements to add explicit partition keys can require significant code changes. Automatic partitioning eliminates this overhead: PolarDB-X partitions every table in an AUTO-mode database by its primary key, and converts regular indexes to global indexes, without any changes to your MySQL DDL statements.

Note

The partition count set by automatic partitioning cannot be changed manually.

How it works

When automatic partitioning is enabled, PolarDB-X applies these rules to every non-partitioned table created in the database:

Tables are horizontally partitioned by primary key. If a table has no primary key, PolarDB-X creates an implicit primary key named _drds_implicit_id_ and uses it as the partition key.
All regular indexes are converted to global indexes and partitioned by their index columns.

When to use automatic partitioning

Automatic partitioning works well for new applications migrating from standalone MySQL that need to scale quickly with minimal code changes. It is a good fit when:

The application performs mostly point queries by primary key.
The workload involves a small number of tables with straightforward query patterns.
Speed to launch matters more than peak write throughput.

When not to use it: Converting regular indexes to global indexes introduces distributed transactions to maintain consistency between global index tables and primary tables. This adds write latency. If your application is write-intensive or requires high write performance, use manual partitioning with explicit partition keys instead.

Enable automatic partitioning

Before you begin

The database must be in AUTO mode. Only AUTO-mode databases support automatic partitioning.
By default, AUTO-mode databases use manual partitioning. To enable automatic partitioning, you must explicitly set the global parameter.

Enable automatic partitioning

Create an AUTO-mode database:
```
CREATE DATABASE autodb2 MODE='AUTO';
```
Enable automatic partitioning for the database:
```
SET GLOBAL AUTO_PARTITION = true;
```
After this setting takes effect, PolarDB-X automatically shards all non-partitioned tables created in the database.

Verify automatic partitioning

Create a table with a primary key and a secondary index:

CREATE TABLE auto_t1(
  id BIGINT NOT NULL auto_increment,
  bid INT,
  name VARCHAR(30),
  birthday datetime,
  PRIMARY KEY(id),
  INDEX idx_name(name)
);

Inspect the full DDL that PolarDB-X generated:

SHOW FULL CREATE TABLE auto_t1;

Expected output:

CREATE PARTITION TABLE `auto_t1` (
    `id` bigint NOT NULL AUTO_INCREMENT,
    `bid` int DEFAULT NULL,
    `name` varchar(30) DEFAULT NULL,
    `birthday` datetime DEFAULT NULL,
    PRIMARY KEY (`id`),
    GLOBAL INDEX /* idx_name_$578c */ `idx_name` (`name`)
        PARTITION BY KEY(`name`,`id`)
        PARTITIONS 16,
    LOCAL KEY `_local_idx_name` (`name`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 DEFAULT COLLATE = utf8mb4_0900_ai_ci
PARTITION BY KEY(`id`)
PARTITIONS 16

The output confirms that:

The table is partitioned by primary key id into 16 partitions.
The regular index idx_name has been converted to a global index, partitioned by name and id.

What's next

Best practices for transparent distributed databases — guidance on when to use automatic partitioning versus manual partitioning in production.