Shard PolarDB-X Tables Across Nodes Using Manual Partitioning - PolarDB

Manual partitioning in PolarDB for Xscale gives you explicit control over how data is distributed across data nodes. It is best suited for core applications that require high concurrency and throughput, where you select a partition strategy based on your workload's read and write patterns.

Manual partitioning requires familiarity with how distributed databases work. Before designing your table schemas, decide whether your workload is read-heavy, write-heavy, or a mix — this determines which table type to use.

Create an AUTO database

To use manual partitioning, create a database in AUTO mode:

CREATE DATABASE autodb1 MODE='auto'

Table types

PolarDB for Xscale supports three logical table types for manual partitioning. Each has a different physical topology and is suited to a specific workload.

Choose a table type based on your primary workload:

Small datasets, low concurrency — use a non-partitioned table
Read-heavy, rarely updated — use a broadcast table
Large datasets, high concurrency and throughput — use a partitioned table

Table type	Physical topology	Best for	Read and write behavior
Non-partitioned table	One physical table on a single data node	Small tables with limited data and low concurrency	Reads and writes are handled by one data node.
Broadcast table	One replica per data node; replicas stay in sync	Configuration tables and other read-heavy, rarely updated data	Read load balancing: reads are distributed across data nodes. Write amplification: every write must propagate to all replicas to maintain consistency.
Partitioned table	Multiple partitions distributed across data nodes; each partition maps to one physical table	Large datasets with high concurrency and throughput	Reads and writes are routed to the target data node based on the partition key column, distributing the load automatically.

Warning

Broadcast tables amplify every write across all data nodes. Use them only for tables that change infrequently. For tables with frequent writes, use a partitioned table instead.

Important

Choose your partition key column carefully before creating a partitioned table. The partition key determines which data node handles each query: queries that filter on the partition key are routed only to the relevant partitions, which significantly reduces the data scanned and improves performance. For example, if you partition an orders table on customer_id, a query with WHERE customer_id = 42 scans only the partition that holds that customer's data rather than all partitions.

Create tables

Method 1: Specify a table type explicitly

Create a non-partitioned table

Syntax

CREATE TABLE xxx (...)
SINGLE

Example

CREATE TABLE sin_tbl(
 id bigint not null auto_increment,
 bid int,
 name varchar(30),
 birthday datetime,
 primary key(id)
)
single ;

Create a broadcast table

Syntax

CREATE TABLE xxx (...)
BROADCAST

Example

CREATE TABLE bro_tbl(
 id bigint not null auto_increment,
 bid int,
 name varchar(30),
 birthday datetime,
 primary key(id)
)
broadcast ;

Create a partitioned table

For the full syntax and options, see Manually create a partitioned table (AUTO mode).

Method 2: Disable automatic partitioning globally

By default, PolarDB for Xscale automatically partitions new tables. To create non-partitioned tables without specifying SINGLE each time, disable this behavior with a global variable:

SET GLOBAL AUTO_PARTITION=false;

After setting this variable, new tables are created as non-partitioned by default and the SINGLE keyword is no longer required.

PolarDB:Manual partitioning