Use Locality to specify data nodes - PolarDB

The LOCALITY attribute controls where PolarDB-X stores data by binding databases, tables, or partitions to specific data nodes (DNs). Use it to isolate workloads across business units, balance non-partitioned tables across nodes, or pin specific partitions to dedicated storage.

Use cases

Workload isolation: Assign different databases to different data nodes so that business units don't compete for the same storage resources.
Single table load balancing: Spread non-partitioned tables across data nodes to avoid overloading the default node (dn-0).
Geo-aware partitioning: Store data from different regions on dedicated nodes using LIST partitioning — for example, keeping Shanghai and Beijing orders on a high-performance node while other regions share the remaining nodes.
Hot data isolation: After splitting a hot partition, pin the new partition to a dedicated node to separate hot data from cold data physically.

How LOCALITY works

LOCALITY is an attribute on a database object — database, table, or partition — that specifies which data nodes can hold that object's data. Set it using dn=<node-id-list> or balance_single_table=on.

LOCALITY applies at four levels:

Level	What it controls
Database	All tables and partitions in the database are distributed only across the specified nodes
Table	The table's data is restricted to the specified nodes, independent of other tables in the same database
Partition	Individual partitions are pinned to specific nodes; other partitions in the same table follow the table-level LOCALITY
Non-partitioned table	`balance_single_table=on` distributes single tables evenly across multiple data nodes

Choosing the right LOCALITY value:

Value	When to use
`dn=<node-id-list>`	Isolate a database, table, or partition to specific nodes
`balance_single_table=on`	Automatically spread non-partitioned tables across multiple data nodes (table-level only)
`""` (empty string)	Clear an existing LOCALITY assignment and let PolarDB-X manage placement automatically

After you set LOCALITY, most DDL operations (scale-out, partition changes) respect the constraint. Two operations ignore LOCALITY: deleting a data node and restoring data from a backup. After either operation, the affected LOCALITY value becomes invalid automatically.

Changing the LOCALITY of a table group or partition group triggers an asynchronous data migration task. The metadata update is immediate; the actual data moves in the background.

Prerequisites

Before you begin, make sure that:

The kernel version of your PolarDB-X instance is 5.4.14 or later. To check, see View and update the version of an instance.
The logical database uses AUTO mode.

Constraints

After you delete a data node, any LOCALITY attribute that references that node's ID becomes invalid automatically.
After you restore data from a backup, the original LOCALITY attribute of the restored objects becomes invalid automatically.
Data nodes specified in a table-level LOCALITY must belong to the database's data node set.
For non-partitioned tables, specify only one data node in the LOCALITY attribute.
If a table belongs to a table group, its LOCALITY must match the table group's, the partition's, and the partition group's LOCALITY values.
By default, a logical table, its table group, its global secondary indexes (GSIs), and the GSI table group all share the same LOCALITY value.
Data nodes specified for individual partitions must belong to the database's data node set, but they don't need to be in the table-level LOCALITY.
A database's storage locations must include the data node with serial number 0 (for example, polardbx-ng28-dn-0). This restriction does not apply to tables or partitions.

View data node information

Before specifying LOCALITY, check which data nodes are available and note their IDs.

SHOW STORAGE;

Sample output:

+--------------------+----------------------------------+------------+-----------+----------+-------------+--------+-----------+-------+--------+
| STORAGE_INST_ID    | LEADER_NODE                      | IS_HEALTHY | INST_KIND | DB_COUNT | GROUP_COUNT | STATUS | DELETABLE | DELAY | ACTIVE |
+--------------------+----------------------------------+------------+-----------+----------+-------------+--------+-----------+-------+--------+
| polardbx-ng28-dn-0 | polardbx-ng28-dn-0-cands-0:14289 | true       | MASTER    | 1        | 2           | 0      | false     | null  | null   |
| polardbx-ng28-dn-1 | polardbx-ng28-dn-1-cands-0:14176 | true       | MASTER    | 1        | 1           | 0      | true      | null  | null   |
| polardbx-ng28-dn-2 | polardbx-ng28-dn-2-cands-0:14568 | true       | MASTER    | 1        | 1           | 0      | true      | null  | null   |
| polardbx-ng28-dn-3 | polardbx-ng28-dn-3-cands-0:16796 | true       | MASTER    | 1        | 1           | 0      | true      | null  | null   |
| polardbx-ng28-gms  | polardbx-ng28-dn-0-cands-0:14289 | true       | META_DB   | 2        | 2           | 0      | false     | null  | null   |
+--------------------+----------------------------------+------------+-----------+----------+-------------+--------+-----------+-------+--------+

Key columns:

Column	Description
`STORAGE_INST_ID`	The data node ID. Use this value when specifying LOCALITY.
`INST_KIND`	Node type. `META_DB` is the metadata node — it cannot store business data.
`STATUS`	`0` means the node is available.
`DELETABLE`	Whether the node can be deleted. `false` means the node cannot be deleted (applies to the metadata node and `dn-0` in the example above).

Set LOCALITY when creating objects

Create a database with LOCALITY

Specify data nodes when creating a database to isolate its data from other databases in the same PolarDB-X instance.

CREATE DATABASE db1
  LOCALITY = 'dn=polardbx-ng28-dn-0,polardbx-ng28-dn-1,polardbx-ng28-dn-2'
  MODE = 'auto';

For the full syntax, see CREATE DATABASE.

Verify the result:

mysql> SHOW CREATE DATABASE `db1`;
+----------+--------------------------------------------------------------------------------------------------------------------+
| DATABASE | CREATE DATABASE                                                                                                    |
+----------+--------------------------------------------------------------------------------------------------------------------+
| db1      | CREATE DATABASE `db1` /* MODE = 'auto' LOCALITY = "dn=polardbx-ng28-dn-0,polardbx-ng28-dn-1,polardbx-ng28-dn-2" */ |
+----------+--------------------------------------------------------------------------------------------------------------------+
1 row in set (0.04 sec)

mysql> SHOW DS;
+----+--------------------+--------------------+---------------------------------+----------------------+---------+
| ID | STORAGE_INST_ID    | DB                 | GROUP                           | PHY_DB               | MOVABLE |
+----+--------------------+--------------------+---------------------------------+----------------------+---------+
|  0 | polardbx-ng28-dn-0 | db1                | DB1_P00000_GROUP                | db1_p00000           |       1 |
|  1 | polardbx-ng28-dn-1 | db1                | DB1_P00001_GROUP                | db1_p00001           |       1 |
|  2 | polardbx-ng28-dn-2 | db1                | DB1_P00002_GROUP                | db1_p00002           |       1 |
|  3 | polardbx-ng28-gms  | information_schema | INFORMATION_SCHEMA_SINGLE_GROUP | polardbx_info_schema |       0 |
+----+--------------------+--------------------+---------------------------------+----------------------+---------+
4 rows in set (0.04 sec)

A database's storage locations must include the data node with serial number 0 (dn-0). This restriction does not apply to tables or individual partitions.

Distribute non-partitioned tables evenly across nodes

When you have many non-partitioned tables, they default to dn-0, which creates a storage imbalance. Use balance_single_table=on to let PolarDB-X spread them automatically based on the number of non-partitioned tables and the amount of data on each data node.

CREATE TABLE t_sgl1 (
  `id` bigint(11) NOT NULL AUTO_INCREMENT BY GROUP,
  `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`)
) SINGLE locality = 'balance_single_table=on';

CREATE TABLE t_sgl2 (
  `id` bigint(11) NOT NULL AUTO_INCREMENT BY GROUP,
  `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`)
) SINGLE locality = 'balance_single_table=on';

CREATE TABLE t_sgl3 (
  `id` bigint(11) NOT NULL AUTO_INCREMENT BY GROUP,
  `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`)
) SINGLE locality = 'balance_single_table=on';

CREATE TABLE t_sgl4 (
  `id` bigint(11) NOT NULL AUTO_INCREMENT BY GROUP,
  `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`)
) SINGLE locality = 'balance_single_table=on';

For the full syntax, see CREATE TABLE (AUTO mode).

Check the topology to confirm each table landed on a different node:

mysql> SHOW TOPOLOGY `t_sgl1`;
+----+------------------+--------------------+----------------+-------------+--------------------+
| ID | GROUP_NAME       | TABLE_NAME         | PARTITION_NAME | PHY_DB_NAME | DN_ID              |
+----+------------------+--------------------+----------------+-------------+--------------------+
|  1 | DB1_P00001_GROUP | t_sgl1_92mv_00000  | p1             | db1_p00001  | polardbx-ng28-dn-1 |
+----+------------------+--------------------+----------------+-------------+--------------------+

mysql> SHOW TOPOLOGY `t_sgl2`;
+----+------------------+--------------------+----------------+-------------+--------------------+
| ID | GROUP_NAME       | TABLE_NAME         | PARTITION_NAME | PHY_DB_NAME | DN_ID              |
+----+------------------+--------------------+----------------+-------------+--------------------+
|  1 | DB1_P00002_GROUP | t_sgl2_87fg_00000  | p1             | db1_p00002  | polardbx-ng28-dn-2 |
+----+------------------+--------------------+----------------+-------------+--------------------+

mysql> SHOW TOPOLOGY `t_sgl3`;
+----+------------------+--------------------+----------------+-------------+--------------------+
| ID | GROUP_NAME       | TABLE_NAME         | PARTITION_NAME | PHY_DB_NAME | DN_ID              |
+----+------------------+--------------------+----------------+-------------+--------------------+
|  1 | DB1_P00002_GROUP | t_sgl3_64uj_00000  | p1             | db1_p00001  | polardbx-ng28-dn-1 |
+----+------------------+--------------------+----------------+-------------+--------------------+

mysql> SHOW TOPOLOGY `t_sgl4`;
+----+------------------+--------------------+----------------+-------------+--------------------+
| ID | GROUP_NAME       | TABLE_NAME         | PARTITION_NAME | PHY_DB_NAME | DN_ID              |
+----+------------------+--------------------+----------------+-------------+--------------------+
|  1 | DB1_P00001_GROUP | t_sgl4_71mb_00000  | p1             | db1_p00002  | polardbx-ng28-dn-2 |
+----+------------------+--------------------+----------------+-------------+--------------------+

The four tables are spread across dn-1 and dn-2 automatically.

Create a logical table with LOCALITY

Pin a logical table's data to specific nodes to isolate it from other tables in the same database. LOCALITY is supported on non-partitioned tables and tables using RANGE, HASH, or LIST partitioning.

CREATE TABLE t_order (
  `id` bigint(11) NOT NULL AUTO_INCREMENT BY GROUP,
  `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
  locality = 'dn=polardbx-ng28-dn-1,polardbx-ng28-dn-2';

Verify the result:

mysql> SHOW CREATE TABLE `t_order`;
+---------+---------------------------------------------------------------------------------------+
| TABLE   | CREATE 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,
	`order_detail` longtext,
	PRIMARY KEY (`id`),
	INDEX `l_i_order` (`order_id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8
/* LOCALITY='dn=polardbx-ng28-dn-1,polardbx-ng28-dn-2' */ |
+---------+---------------------------------------------------------------------------------------+
1 row in set (0.05 sec)

mysql> SHOW TOPOLOGY `t_order`;
+----+------------------+--------------------+----------------+-------------+--------------------+
| ID | GROUP_NAME       | TABLE_NAME         | PARTITION_NAME | PHY_DB_NAME | DN_ID              |
+----+------------------+--------------------+----------------+-------------+--------------------+
|  0 | DB1_P00002_GROUP | t_order_18dV_00001 | p2             | db1_p00002  | polardbx-ng28-dn-2 |
|  1 | DB1_P00001_GROUP | t_order_18dV_00000 | p1             | db1_p00001  | polardbx-ng28-dn-1 |
|  2 | DB1_P00001_GROUP | t_order_18dV_00002 | p3             | db1_p00001  | polardbx-ng28-dn-1 |
+----+------------------+--------------------+----------------+-------------+--------------------+
3 rows in set (0.15 sec)

Set LOCALITY per partition

Specify a data node for each partition to route data to dedicated storage based on business logic — for example, storing high-priority partitions on a specific node.

The following example routes p1 (Shanghai) and p2 (Beijing) to dn-2, and routes the remaining partitions across dn-0 and dn-1.

CREATE TABLE orders_region (
  order_id int AUTO_INCREMENT PRIMARY KEY,
  customer_id int,
  country varchar(64),
  city varchar(64),
  order_time datetime NOT NULL
)
PARTITION BY LIST COLUMNS(country, city)
(
  PARTITION p1 VALUES IN (('China','Shanghai')) LOCALITY = 'dn=polardbx-ng28-dn-2',
  PARTITION p2 VALUES IN (('China','Beijing'))  LOCALITY = 'dn=polardbx-ng28-dn-2',
  PARTITION p3 VALUES IN (('China','Hangzhou')),
  PARTITION p4 VALUES IN (('China','Nanjing')),
  PARTITION p5 VALUES IN (('China','Guangzhou')),
  PARTITION p6 VALUES IN (('China','Shenzhen')),
  PARTITION p7 VALUES IN (('China','Wuhan')),
  PARTITION p8 VALUES IN (('America','New York'))
) LOCALITY = 'dn=polardbx-ng28-dn-0,polardbx-ng28-dn-1';

Verify the topology:

mysql> SHOW TOPOLOGY `orders_region`;
+----+------------------+--------------------------+----------------+-------------+--------------------+
| ID | GROUP_NAME       | TABLE_NAME               | PARTITION_NAME | PHY_DB_NAME | DN_ID              |
+----+------------------+--------------------------+----------------+-------------+--------------------+
|  0 | DB1_P00002_GROUP | orders_region_RlsY_00000 | p1             | db1_p00002  | polardbx-ng28-dn-2 |
|  1 | DB1_P00002_GROUP | orders_region_RlsY_00001 | p2             | db1_p00002  | polardbx-ng28-dn-2 |
|  2 | DB1_P00001_GROUP | orders_region_RlsY_00003 | p4             | db1_p00001  | polardbx-ng28-dn-1 |
|  3 | DB1_P00001_GROUP | orders_region_RlsY_00004 | p5             | db1_p00001  | polardbx-ng28-dn-1 |
|  4 | DB1_P00001_GROUP | orders_region_RlsY_00006 | p7             | db1_p00001  | polardbx-ng28-dn-1 |
|  5 | DB1_P00000_GROUP | orders_region_RlsY_00002 | p3             | db1_p00000  | polardbx-ng28-dn-0 |
|  6 | DB1_P00000_GROUP | orders_region_RlsY_00005 | p6             | db1_p00000  | polardbx-ng28-dn-0 |
|  7 | DB1_P00000_GROUP | orders_region_RlsY_00007 | p8             | db1_p00000  | polardbx-ng28-dn-0 |
+----+------------------+--------------------------+----------------+-------------+--------------------+
8 rows in set (0.25 sec)

p1 and p2 are on dn-2; the rest are distributed across dn-0 and dn-1.

Data nodes specified for partitions must belong to the database's data node set. They don't need to be in the table-level LOCALITY.

Change LOCALITY

Choose the right level to update

Change target	Command	Triggers data migration
Table group	`ALTER TABLEGROUP ... SET LOCALITY`	Yes (asynchronous)
Partition group	`ALTER TABLEGROUP ... SET PARTITIONS ... LOCALITY`	Yes (asynchronous)
Individual table	`ALTER TABLE ... PARTITION BY ... LOCALITY`	Yes

Change the LOCALITY of a table group

ALTER TABLEGROUP identifier SET LOCALITY = locality_option

-- locality_option:
--   'dn=storage_inst_id_list'
--   ''

Example: change the LOCALITY of tg3 (which contains orders_region):

ALTER TABLEGROUP `tg3` SET LOCALITY = 'dn=polardbx-ng28-dn-0';

After the change, PolarDB-X immediately updates the metadata and starts a background rebalance task. Track migration progress:

SELECT * FROM information_schema.ddl_plan WHERE table_schema = 'db1';

Sample output:

+----+---------------------+---------------------+--------------+------------------------------------------------------------------+---------+-----------+----------+-------------+--------+--------+---------------------+---------------------+----------------+
| ID | plan_id             | job_id              | table_schema | ddl_stmt                                                         | state   | ddl_type  | progress | retry_count | result | extras | gmt_created         | gmt_modified        | resource       |
+----+---------------------+---------------------+--------------+------------------------------------------------------------------+---------+-----------+----------+-------------+--------+--------+---------------------+---------------------+----------------+
|  1 | 1465819565798723584 | 1465819579241467904 | db1          | REBALANCE TABLEGROUP `tg3`  EXPLAIN=false ASYNC=true DEBUG=false | SUCCESS | REBALANCE |      100 |           0 |        |        | 2022-05-24 14:37:58 | 2022-05-24 14:38:11 | tablegroup:tg3 |
+----+---------------------+---------------------+--------------+------------------------------------------------------------------+---------+-----------+----------+-------------+--------+--------+---------------------+---------------------+----------------+

The progress column shows completion percentage; state=SUCCESS means the migration is done.

Verify the final topology:

mysql> SHOW TOPOLOGY `orders_region`;
+----+------------------+--------------------------+----------------+-------------+--------------------+
| ID | GROUP_NAME       | TABLE_NAME               | PARTITION_NAME | PHY_DB_NAME | DN_ID              |
+----+------------------+--------------------------+----------------+-------------+--------------------+
|  0 | DB1_P00002_GROUP | orders_region_RlsY_00000 | p1             | db1_p00002  | polardbx-ng28-dn-2 |
|  1 | DB1_P00002_GROUP | orders_region_RlsY_00001 | p2             | db1_p00002  | polardbx-ng28-dn-2 |
|  2 | DB1_P00000_GROUP | orders_region_RlsY_00002 | p3             | db1_p00000  | polardbx-ng28-dn-0 |
|  3 | DB1_P00000_GROUP | orders_region_RlsY_00003 | p4             | db1_p00000  | polardbx-ng28-dn-0 |
|  4 | DB1_P00000_GROUP | orders_region_RlsY_00004 | p5             | db1_p00000  | polardbx-ng28-dn-0 |
|  5 | DB1_P00000_GROUP | orders_region_RlsY_00005 | p6             | db1_p00000  | polardbx-ng28-dn-0 |
|  6 | DB1_P00000_GROUP | orders_region_RlsY_00006 | p7             | db1_p00000  | polardbx-ng28-dn-0 |
|  7 | DB1_P00000_GROUP | orders_region_RlsY_00007 | p8             | db1_p00000  | polardbx-ng28-dn-0 |
+----+------------------+--------------------------+----------------+-------------+--------------------+
8 rows in set (0.17 sec)

Partitions p1 and p2 kept their original node (dn-2) because they had explicit LOCALITY set at creation. All other partitions moved to dn-0.

Change the LOCALITY of a partition group

Update a single partition's node assignment without affecting other partitions.

ALTER TABLEGROUP identifier SET PARTITIONS part_name LOCALITY = locality_option

-- locality_option:
--   'dn=storage_inst_id_list'
--   ''

Example: move partition p3 of orders_region (in table group tg3) to dn-1:

ALTER TABLEGROUP `tg3` SET PARTITIONS p3 LOCALITY = 'dn=polardbx-ng28-dn-1';

Verify:

mysql> SHOW TOPOLOGY orders_region;
+----+------------------+--------------------------+----------------+-------------+--------------------+
| ID | GROUP_NAME       | TABLE_NAME               | PARTITION_NAME | PHY_DB_NAME | DN_ID              |
+----+------------------+--------------------------+----------------+-------------+--------------------+
|  0 | DB1_P00002_GROUP | orders_region_RlsY_00000 | p1             | db1_p00002  | polardbx-ng28-dn-2 |
|  1 | DB1_P00002_GROUP | orders_region_RlsY_00001 | p2             | db1_p00002  | polardbx-ng28-dn-2 |
|  2 | DB1_P00001_GROUP | orders_region_RlsY_00002 | p3             | db1_p00001  | polardbx-ng28-dn-1 |
|  3 | DB1_P00000_GROUP | orders_region_RlsY_00003 | p4             | db1_p00000  | polardbx-ng28-dn-0 |
|  4 | DB1_P00000_GROUP | orders_region_RlsY_00004 | p5             | db1_p00000  | polardbx-ng28-dn-0 |
|  5 | DB1_P00000_GROUP | orders_region_RlsY_00005 | p6             | db1_p00000  | polardbx-ng28-dn-0 |
|  6 | DB1_P00000_GROUP | orders_region_RlsY_00006 | p7             | db1_p00000  | polardbx-ng28-dn-0 |
|  7 | DB1_P00000_GROUP | orders_region_RlsY_00007 | p8             | db1_p00000  | polardbx-ng28-dn-0 |
+----+------------------+--------------------------+----------------+-------------+--------------------+
8 rows in set (0.11 sec)

Change the LOCALITY of a table

To remove the balance_single_table=on LOCALITY from a non-partitioned table and convert it to a partitioned table, explicitly clear the LOCALITY during repartition:

ALTER TABLE t_sgl1 PARTITION BY HASH(ID) PARTITIONS 32 LOCALITY = "";

To change both the partitioning and the node assignment at once:

ALTER TABLE t_sgl1 PARTITION BY HASH(ID) PARTITIONS 8 LOCALITY = "dn=dn1";

Important

Changing a table's LOCALITY triggers data migration.

How partition operations affect LOCALITY

AUTO mode supports ALTER TABLEGROUP and other partition modification operations. For details, see ALTER TABLEGROUP and Change the type and partitioning rule of a table.

After a partition modification, new partition groups and table groups inherit the original LOCALITY value. The following operations are exceptions where LOCALITY changes:

Operation	Object	LOCALITY after the operation
Change the partitioning strategy	Table	Unchanged
Change the table type	Table	Unchanged only when converting a non-partitioned table to a partitioned table. All other type changes clear LOCALITY.
Migrate partitions	Partition group	Unchanged
Merge partitions	Partition group	Inherited only if all merged partitions share the same LOCALITY value; otherwise cleared
Split a partition	Partition group	Unchanged
Split a partition by hot value	Partition group	Cleared
Extract hot data from a partition	Partition group	Cleared
Drop a partition	Partition group	—
Add a partition	Partition group	Unchanged
Modify partitions (LIST partitioning)	Partition group	Unchanged
Rename a partition	Partition group	Unchanged

PolarDB:Locality