PolarDB:Storage pools

Set up tenant isolation

Divide DNs into dedicated pools for large sellers and use _default as the shared pool for small sellers.

CREATE STORAGE POOL sp1 dn_list="dn4,dn5,dn6" undeletable_dn="dn4";
CREATE STORAGE POOL sp2 dn_list="dn7,dn8,dn9" undeletable_dn="dn7";

-- Small sellers share _default (dn1, dn2, dn3)
CREATE DATABASE orders_comm MODE = "auto" LOCALITY= "storage_pools='_default'";

-- Large Seller 1 uses sp1 (dn4, dn5, dn6)
CREATE DATABASE orders_seller1 MODE = "auto" LOCALITY= "storage_pools='sp1'";
-- Large Seller 2 uses sp2 (dn7, dn8, dn9)
CREATE DATABASE orders_seller2 MODE = "auto" LOCALITY= "storage_pools='sp2'";

After running these statements:

• Large sellers and small sellers are physically isolated on separate DNs.

• Small sellers share dn1, dn2, and dn3 in _default.

• Each seller's database has an independent schema and storage allocation.

Rebalance resources among small sellers

To distribute data evenly across small-seller nodes:

REBALANCE TENANT "_default" POLICY="data_balance";

This automatically distributes all non-partitioned tables and partitions across dn1, dn2, and dn3.

Onboard a new large seller

Add nodes, create a dedicated pool, and assign a new database to it. New nodes are initially in _recycle.

CREATE STORAGE POOL spn dn_list = "dn10,dn11,dn12" undeletable_dn="dn10";
CREATE DATABASE orders_sellern MODE = "auto" LOCALITY="storage_pools='spn'";

Offboard a large seller

Delete the database and release the storage pool. Existing services on other pools are not affected, and applications experience minimal interruption.

DROP DATABASE orders_sellern;
DELETE STORAGE POOL spn; -- Nodes automatically move to _recycle
ALTER STORAGE POOL _recycle DRAIN NODE "dn7, dn8, dn9"; -- Release nodes from the cluster

Set up tenant isolation

Create a database that spans all pools, then partition the orders table so each large tenant maps to a dedicated pool.

CREATE DATABASE orders_db MODE = "auto"
       LOCALITY = "storage_pools='_default,sp1,sp2,...',primary_storage_pool='_default'";

USE orders_db;

-- Broadcast table: replicated across all pools so joins can be pushed down
CREATE TABLE commodity(
  commodity_id int,
  commodity_name varchar(64)
) BROADCAST;

-- Partitioned by seller_id; large tenants get dedicated pools
CREATE TABLE orders_sellers(
  order_id int AUTO_INCREMENT primary key,
  customer_id int,
  commodity_id int,
  country varchar(64),
  seller_id int,
  order_time datetime not null)
partition BY list(seller_id)
(
  partition p1 VALUES IN (1, 2, 3, 4),
  partition p2 VALUES IN (5, 6, 7, 8),
  -- p1 and p2 use _default by default (primary_storage_pool)
  ...
  partition pn VALUES IN (k) LOCALITY = "storage_pools='sp1'",
  partition pn+1 VALUES IN (k+1, k+2) LOCALITY = "storage_pools='sp2'",
  -- pn and pn+1 exclusively occupy sp1 and sp2, respectively
  ...
) LOCALITY = "storage_pools='_default,sp1,sp2,...'";

Onboard a new small tenant

Add a partition. No data migration occurs — only the new partition is created.

ALTER TABLE orders_sellers ADD PARTITION
(PARTITION p3 values in (32, 33));

Onboard a new large tenant

Add nodes, create a pool, register it with the database, then add a partition bound to the new pool.

CREATE STORAGE POOL spn dn_list = "dn10,dn11,dn12" undeletable_dn="dn10";

ALTER DATABASE orders_db SET LOCALITY =
  "storage_pools='_default,sp1,sp2,...,spn',primary_storage_pool='_default'";
-- Adding spn triggers automatic migration of the broadcast table to spn's nodes

ALTER TABLE orders_sellers ADD PARTITION
(PARTITION p4 values in (34) LOCALITY="storage_pools='spn'");

Offboard a tenant

Drop the partition or narrow the values it covers.

-- Remove a large tenant partition entirely
ALTER TABLE orders_sellers DROP PARTITION p4;

-- Or remove specific sellers from a shared partition
ALTER TABLE orders_sellers MODIFY PARTITION p1 DROP VALUES (4, 5);

All partition operations are online and affect only the target partition. Other partitions remain unaffected.

Upgrade a small seller to VIP

Split the partition to move the seller to a dedicated pool.

ALTER TABLE orders_sellers SPLIT PARTITION p2 INTO
(PARTITION p2 VALUES in (6, 7, 8),
PARTITION `pn+2` VALUES in (5) LOCALITY = "storage_pools='spn+3'");

Downgrade a large seller

Merge partitions to consolidate them back into the shared pool.

ALTER TABLE orders_sellers MERGE PARTITION p1, pn TO p1 LOCALITY="";

The merged partition is stored in the default storage pool.

Rebalance resources

Balance load across shared-pool partitions with the same REBALANCE command:

REBALANCE TENANT "_default" POLICY="data_balance";

Create databases and tables

Create a database with DEFAULT_SINGLE=on so that standard table creation statements from your standalone database work without modification.

CREATE DATABASE orders_db MODE = "auto" DEFAULT_SINGLE=on
    LOCALITY = "storage_pools='_default',primary_storage_pool='_default'";

use orders_db;

-- These tables are automatically balanced across _default nodes
CREATE TABLE orders_region1(
  order_id int AUTO_INCREMENT primary key,
  customer_id int,
  country varchar(64),
  city int,
  order_time datetime not null);
CREATE TABLE orders_region2(
  order_id int AUTO_INCREMENT primary key,
  customer_id int,
  country varchar(64),
  city int,
  order_time datetime not null);
CREATE TABLE orders_region3(
  order_id int AUTO_INCREMENT primary key,
  customer_id int,
  country varchar(64),
  city int,
  order_time datetime not null);

-- Broadcast table: replicated across all _default nodes
CREATE TABLE commodity(
  commodity_id int,
  commodity_name varchar(64)
) BROADCAST;

Isolate high-traffic non-partitioned tables

When a table's volume or traffic exceeds what a shared pool can handle, move it to a dedicated pool.

ALTER DATABASE orders_db set LOCALITY = "storage_pools='_default,sp1',primary_storage_pool='_default'";
-- The broadcast table automatically expands to sp1's nodes

ALTER TABLE orders_region_single1 single LOCALITY = "storage_pools='sp1'";
-- orders_region_single1 is now isolated on sp1

Convert to distributed tables

When a table's workload exceeds the capacity of a single physical table, convert it to a partitioned distributed table on a dedicated pool.

ALTER DATABASE orders_db set LOCALITY = "storage_pools='_default,sp1,sp2,sp3',primary_storage_pool='_default'";

ALTER TABLE orders_region_single2 partition by hash(order_id) partitions 16
  LOCALITY = "storage_pools='sp2'";

ALTER TABLE orders_region_single3 partition by hash(order_id) partitions 16
  LOCALITY = "storage_pools='sp3'";

All sharding operations run online. You can specify custom partitioning methods and storage pools without interrupting other tables.