Create a KEY-* composite partition - PolarDB - Alibaba Cloud - PolarDB

Composite partitioning (also called subpartitioning) divides each first-level partition into subpartitions, giving you two-dimensional data distribution. For example, a table with 2 KEY partitions and 4 subpartitions each results in 8 physical partitions in total.

PolarDB for Xscale supports the following KEY-based composite partition combinations:

Combination	Second-level partition type
KEY-HASH	HASH
KEY-KEY	KEY
KEY-RANGE	RANGE
KEY-RANGE COLUMNS	RANGE COLUMNS
KEY-LIST	LIST
KEY-LIST COLUMNS	LIST COLUMNS

Each combination supports two syntax forms:

Templated syntax: all first-level partitions share the same subpartition definition. Specify the subpartition template once; PolarDB applies it uniformly across all partitions.
Non-templated syntax: each first-level partition defines its own subpartitions independently, letting you assign different subpartition counts or boundary values per partition.

KEY-HASH level-2 partition

Templated syntax

CREATE TABLE tb_k_h_tp(
 id bigint not null auto_increment,
 bid int,
 name varchar(30),
 birthday datetime not null,
 primary key(id)
)
PARTITION BY KEY(bid, name)
PARTITIONS 2
SUBPARTITION BY HASH(id)
SUBPARTITIONS 4

This creates 2 KEY partitions, each with 4 HASH subpartitions, for 8 physical partitions total.

Non-templated syntax

CREATE TABLE tb_k_h_ntp(
 id bigint not null auto_increment,
 bid int,
 name varchar(30),
 birthday datetime not null,
 primary key(id)
)
PARTITION BY KEY(bid, name)
PARTITIONS 2
SUBPARTITION BY HASH(id)
(
  PARTITION p1 SUBPARTITIONS 2,
  PARTITION p2 SUBPARTITIONS 4
)

p1 gets 2 HASH subpartitions and p2 gets 4, for 6 physical partitions total.

KEY-KEY level-2 partition

Templated syntax

CREATE TABLE tb_k_k_tp(
 id bigint not null auto_increment,
 bid int,
 name varchar(30),
 birthday datetime not null,
 primary key(id)
)
PARTITION BY KEY(bid,name)
PARTITIONS 2
SUBPARTITION BY KEY(id)
SUBPARTITIONS 4

This creates 2 KEY partitions, each with 4 KEY subpartitions, for 8 physical partitions total.

Non-templated syntax

CREATE TABLE tb_k_k_ntp(
 id bigint not null auto_increment,
 bid int,
 name varchar(30),
 birthday datetime not null,
 primary key(id)
)
PARTITION BY KEY(bid,name)
PARTITIONS 2
SUBPARTITION BY KEY(id)
(
  PARTITION p1 SUBPARTITIONS 2,
  PARTITION p2 SUBPARTITIONS 4
)

p1 gets 2 KEY subpartitions and p2 gets 4, for 6 physical partitions total.

KEY-RANGE level-2 partition

Templated syntax

CREATE TABLE tb_k_r_tp(
 id bigint not null auto_increment,
 bid int,
 name varchar(30),
 birthday datetime not null,
 primary key(id)
)
PARTITION BY KEY(bid,name)
PARTITIONS 2
SUBPARTITION BY RANGE(id) (
  SUBPARTITION sp1 VALUES LESS THAN(1000),
  SUBPARTITION sp2 VALUES LESS THAN(MAXVALUE)
)

Both KEY partitions share the same RANGE subpartition boundaries: id < 1000 maps to sp1, and all remaining rows go to sp2.

Non-templated syntax

CREATE TABLE tb_k_r_ntp(
 id bigint not null auto_increment,
 bid int,
 name varchar(30),
 birthday datetime not null,
 primary key(id)
)
PARTITION BY KEY(bid,name)
PARTITIONS 2
SUBPARTITION BY RANGE(id)
(
  PARTITION p1 (
    SUBPARTITION sp1 VALUES LESS THAN(1000),
    SUBPARTITION sp2 VALUES LESS THAN(MAXVALUE)
  ),
  PARTITION p2 (
    SUBPARTITION sp3 VALUES LESS THAN(1000),
    SUBPARTITION sp4 VALUES LESS THAN(2000),
    SUBPARTITION sp5 VALUES LESS THAN(MAXVALUE)
  )
)

p1 has 2 RANGE subpartitions and p2 has 3, with different boundary values per partition.

KEY-RANGE COLUMNS level-2 partition

Templated syntax

CREATE TABLE tb_k_rc_tp(
 id bigint not null auto_increment,
 bid int,
 name varchar(30),
 birthday datetime not null,
 primary key(id)
)
PARTITION BY KEY(bid,name)
PARTITIONS 2
SUBPARTITION BY RANGE COLUMNS(birthday,id) (
  SUBPARTITION sp1 VALUES LESS THAN('2000-01-01',1000),
  SUBPARTITION sp2 VALUES LESS THAN(MAXVALUE,MAXVALUE)
)

Both KEY partitions share the same RANGE COLUMNS subpartition boundaries.

Non-templated syntax

CREATE TABLE tb_k_rc_ntp(
 id bigint not null auto_increment,
 bid int,
 name varchar(30),
 birthday datetime not null,
 primary key(id)
)
PARTITION BY KEY(bid,name)
PARTITIONS 2
SUBPARTITION BY RANGE COLUMNS(birthday,id)
(
  PARTITION p1 (
    SUBPARTITION sp1 VALUES LESS THAN('2000-01-01',1000),
    SUBPARTITION sp2 VALUES LESS THAN(MAXVALUE,MAXVALUE)
  ),
  PARTITION p2 (
    SUBPARTITION sp3 VALUES LESS THAN('2000-01-01',1000),
    SUBPARTITION sp4 VALUES LESS THAN('2020-01-01',2000),
    SUBPARTITION sp5 VALUES LESS THAN(MAXVALUE,MAXVALUE)
  )
)

p1 has 2 RANGE COLUMNS subpartitions and p2 has 3, with different boundary values per partition.

KEY-LIST level-2 partition

Templated syntax

Non-templated syntax

CREATE TABLE tb_k_l_ntp(
 id bigint not null auto_increment,
 bid int,
 name varchar(30),
 birthday datetime not null,
 primary key(id)
)
PARTITION BY KEY(bid,name)
PARTITIONS 2
SUBPARTITION BY LIST(id)
(
  PARTITION p1 (
    SUBPARTITION sp1 VALUES IN (1000,2000),
    SUBPARTITION sp2 VALUES IN (3000,4000),
    SUBPARTITION sp3 VALUES IN (DEFAULT)
  ),
  PARTITION p2 (
    SUBPARTITION sp4 VALUES IN (5000,6000),
    SUBPARTITION sp5 VALUES IN (DEFAULT)
  )
)

Each partition defines its own LIST values. Use DEFAULT to capture rows that do not match any explicitly listed value.

KEY-LIST COLUMNS level-2 partition

Templated syntax

CREATE TABLE tb_k_lc_tp(
 id bigint not null auto_increment,
 bid int,
 name varchar(30),
 birthday datetime not null,
 primary key(id)
)
PARTITION BY KEY(bid,name)
PARTITIONS 2
SUBPARTITION BY LIST COLUMNS(birthday,id)
(
    SUBPARTITION sp1 VALUES IN (('2000-01-01',1000),('2000-02-01',2000)),
    SUBPARTITION sp2 VALUES IN (('2001-01-01',3000),('2001-02-01',4000)),
    SUBPARTITION sp3 VALUES IN (DEFAULT)
)

All first-level partitions share the same LIST COLUMNS subpartition definition.

Non-templated syntax

CREATE TABLE tb_k_lc_ntp(
 id bigint not null auto_increment,
 bid int,
 name varchar(30),
 birthday datetime not null,
 primary key(id)
)
PARTITION BY KEY(bid,name)
PARTITIONS 2
SUBPARTITION BY LIST COLUMNS(birthday,id)
(
  PARTITION p1 (
    SUBPARTITION sp1 VALUES IN (('2000-01-01',1000),('2000-02-01',2000)),
    SUBPARTITION sp2 VALUES IN (('2001-01-01',3000),('2001-02-01',4000)),
    SUBPARTITION sp3 VALUES IN (DEFAULT)
  ),
  PARTITION p2 (
    SUBPARTITION sp4 VALUES IN (('2002-01-10',5000),('2002-02-10',6000)),
    SUBPARTITION sp5 VALUES IN (DEFAULT)
  )
)

Usage notes

Rule	Detail
Total partition count	The total number of physical partitions equals the number of first-level partitions multiplied by the number of subpartitions per partition. For templated syntax, all first-level partitions share the same subpartition count.
Non-templated flexibility	With non-templated syntax, each first-level partition can have a different number of subpartitions and different boundary values, as shown in the KEY-RANGE and KEY-LIST examples above.