PolarDB:AUTO mode databases and DRDS mode databases

How it works

When you create a PolarDB-X database, the MODE parameter determines how tables are partitioned:

• AUTO mode — Tables are partitioned tables. PolarDB-X automatically selects a partition key and distributes data evenly across partitions using a range-based consistent hashing algorithm. Standard MySQL CREATE TABLE syntax works without modification. Available in PolarDB-X V5.4.13 and later.

• DRDS mode — Tables are sharded tables. You specify a database shard key and a table shard key using PolarDB-X-specific sharding syntax. If no shard key is specified, a non-partitioned table is created. This is the default when you omit MODE from CREATE DATABASE.

A single PolarDB-X instance can contain databases in both modes.

Usage notes

• Specify MODE='AUTO' in CREATE DATABASE to create a database in AUTO mode.

• If MODE is omitted, a DRDS mode database is created.

• After a database is created, its mode cannot be changed.

• AUTO mode databases cannot be created in PolarDB-X clusters of the standard edition.

• In AUTO mode databases, use standard MySQL partitioning syntax only — DRDS sharding syntax is not supported.

• In DRDS mode databases, use DRDS sharding syntax only — MySQL partitioning syntax is not supported.

Create a database

Use the MODE parameter to set the database mode at creation time.

AUTO mode:

CREATE DATABASE auto_db MODE='AUTO';

Tables created in this database are partitioned tables. See CREATE TABLE (AUTO mode) and CREATE DATABASE.

DRDS mode:

-- Either form creates a DRDS mode database
CREATE DATABASE drds_db MODE='DRDS';
CREATE DATABASE drds_db;

Tables created in this database are sharded tables. See CREATE TABLE (DRDS mode) and CREATE DATABASE.

Partition tables in AUTO mode

AUTO mode supports both automatic and manual partitioning.

CREATE TABLE tb (a INT, b INT, PRIMARY KEY(a));

SHOW FULL CREATE TABLE tb\G
*************************** 1. row ***************************
       TABLE: tb
CREATE TABLE: CREATE PARTITION TABLE `tb` (
    `a` int(11) NOT NULL,
    `b` int(11) DEFAULT NULL,
    PRIMARY KEY (`a`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4
PARTITION BY KEY(`a`)
PARTITIONS 16
1 row in set (0.01 sec)

-- Hash partitioning with 4 partitions
CREATE TABLE tb (a INT, b INT, PRIMARY KEY(a))
    PARTITION BY HASH(a) PARTITIONS 4;

Shard tables in DRDS mode

DRDS mode uses PolarDB-X-specific sharding syntax with DBPARTITION BY and TBPARTITION BY. Only hash-based sharding is supported.

CREATE TABLE tb (a INT, b INT, PRIMARY KEY(a))
    DBPARTITION BY HASH(a)
    TBPARTITION BY HASH(a)
    TBPARTITIONS 4;

Routing algorithms

The key difference between the two modes is how data is routed to partitions.

Feature comparison

AUTO mode adds significant capabilities over DRDS mode. The table below covers the full feature set.

Tip: If you only need to know which mode to use, see the FAQ.

Performance benchmark

Sysbench benchmark tests comparing AUTO mode (partitioned tables) and DRDS mode (sharded tables) at the same scale.

Test environment:

• Instance type: polarx.x4.2xlarge.2e

• Compute nodes (CN): 16C64G × 2

• Data nodes (DN): 16C64G × 2

• Version: 5.4.13-16415631

Test data:

Test scenarios:

• oltp_point_select — equality-condition point reads using the partition key

• oltp_read_only — point reads and small-range reads in a transaction using the partition key

• oltp_read_write — point and small-range reads and writes using the partition key

Results:

• oltp_point_select: QPS for partitioned tables is comparable to sharded tables. The consistent hashing algorithm is more complex than modulo hashing, but the difference is negligible for point lookups.

• oltp_read_only and oltp_read_write: QPS for partitioned tables is approximately 33% higher than for sharded tables, because partition pruning is triggered for small-range queries.

FAQ

When should I use AUTO mode versus DRDS mode?

Use AUTO mode. It is available in PolarDB-X V5.4.13 and later and supports all modern distributed database features. If your application is migrating from PolarDB-X 1.0, AUTO mode is the only supported target mode.

Should I use automatic or manual partitioning in AUTO mode?

Start with automatic partitioning — it works with standard CREATE TABLE syntax and requires no upfront partition planning. Once you have real query patterns, run ALTER PARTITION to tune partition keys and counts if needed. Switch to manual partitioning upfront only if you already know the SQL access patterns and join relationships for your tables.

How many partitions does automatic partitioning create?

The default partition count is calculated as: number of nodes specified at instance creation × 8. For a 2-node instance, automatic partitioning creates 16 partitions. This count does not change when you add nodes later.

To change the default for new tables, modify the AUTO_PARTITION_PARTITIONS parameter. This affects all new tables in the instance. Existing tables are not changed.

Can I change the partition count of an existing auto-partitioned table?

Yes. To change the partition count for new auto-partitioned tables, modify the AUTO_PARTITION_PARTITIONS parameter for the instance. This change applies to all new tables created after the modification. See Use ALTER TABLE to modify table partitions (AUTO mode).

How do I convert a DRDS mode database to AUTO mode?

Choose based on your PolarDB-X version. Check your version first: see View the version of an instance.

• V5.4.16 and later (recommended): Use CREATE DATABASE LIKE or CREATE DATABASE AS to convert the database. See Convert a database in DRDS mode to a database in AUTO mode.

• All versions — DTS migration: Create an AUTO mode database and tables in the target instance, then use Data Transmission Service (DTS) to migrate data from the DRDS mode source.

• All versions — dump and restore: Use mysqldump to export data from the DRDS mode database (excluding CREATE TABLE statements). Create an AUTO mode database with the target schema, then import the dump using the source command.

Next steps

• CREATE TABLE (AUTO mode)

• CREATE TABLE (DRDS mode)

• Use ALTER TABLE to modify table partitions (AUTO mode)

• Convert a database in DRDS mode to a database in AUTO mode