All Products
Search

This Product

    PolarDB:Change the table group of a table (AUTO mode)

    Document Center

    PolarDB:Change the table group of a table (AUTO mode)

    Last Updated:Feb 27, 2026

    In a PolarDB-X database in AUTO mode, every table belongs to a table group. Tables in the same table group share the same partitioning method. Queries that involve the relationships between tables in the same table group can be pushed down to data nodes for more efficient execution.

    You can move one or more tables into a different table group to co-locate related tables, reorganize partitions after schema changes, or consolidate table groups.

    Prerequisites

    • The database is in AUTO mode

    • The destination table group already exists (manually created)

    Change a single table's table group

    ALTER TABLE #tbName SET TABLEGROUP=#tgName [force]

    This moves #tbName into the destination table group #tgName. The behavior depends on whether you specify the force option.

    Without the FORCE option

    The statement succeeds only when all three conditions are met:

    ConditionDescription
    Partitioning mode matchesThe partitioning method, number of partition key columns, and data types of the partition key columns must be identical.
    Partition names matchEvery partition in the table must have a corresponding partition with the same name in the destination table group.
    Data node placement matchesPartitions with the same name must be stored on the same data node.

    If any condition is not met, the statement fails.

    With the FORCE option

    With force, PolarDB-X automatically adjusts the table to fit the destination table group:

    ScenarioResult
    Partition key data types or column count differThe statement fails. The force option cannot resolve incompatible partition key definitions.
    Partitioning mode matches, partition names match, but data nodes differPartitions are migrated to the data nodes used by the destination table group.
    Partitioning mode matches, but partition names differPartitions are renamed to match the destination table group.
    Partitioning modes differ entirelyThe table is repartitioned using the destination table group's partitioning mode. Partition definitions are replaced.
    Important

    When force triggers partition migration or repartitioning, data is physically moved between data nodes. Plan for the associated I/O and time cost on large tables.

    Example

    The following example changes the table group of a table named tb1 to mytg1.

    1. Create the example table:

    CREATE TABLE tb1(a int) PARTITION BY RANGE(a)
(
  PARTITION p1 VALUES LESS THAN(20),
  PARTITION p2 VALUES LESS THAN(100)
);

    Partition p1 is stored on data node DN1 and partition p2 is stored on data node DN2. Run the following statement to verify partition placement:

    SHOW topology FROM tb1;

    2. Scenario A -- Without FORCE, all conditions met:

    The destination table group mytg1 has the following partitions, with p1 on DN1 and p2 on DN2:

    PARTITION BY RANGE(int)
(PARTITION p1 VALUES LESS THAN(20),
 PARTITION p2 VALUES LESS THAN(100))

    Run the statement:

    ALTER TABLE tb1 SET TABLEGROUP=mytg1;

    Result: The partitioning mode matches, partition names match, and data node placement matches. The statement succeeds, and tb1 now belongs to mytg1.

    3. Scenario B -- Without FORCE, partition names differ (fails):

    The destination table group mytg1 has partitions p1 and p3 instead of p1 and p2:

    PARTITION BY RANGE(int)
(PARTITION p1 VALUES LESS THAN(20),
 PARTITION p3 VALUES LESS THAN(100))
    ALTER TABLE tb1 SET TABLEGROUP=mytg1;

    Result: The statement fails because partition names differ. Use the force option to rename partitions automatically.

    4. Scenario C -- Without FORCE, same partitions on different data nodes (fails):

    The destination table group mytg1 has partitions p1 (on DN1) and p2 (on DN3, not DN2):

    PARTITION BY RANGE(int)
(PARTITION p1 VALUES LESS THAN(20),
 PARTITION p2 VALUES LESS THAN(100))
    ALTER TABLE tb1 SET TABLEGROUP=mytg1;

    Result: The statement fails because p2 is on DN2 in tb1 but on DN3 in mytg1. Use the force option to migrate partitions automatically.

    5. Scenario D -- With FORCE, partition migration:

    Using the same mytg1 as Scenario C (partition p2 on DN3):

    ALTER TABLE tb1 SET TABLEGROUP=mytg1 force;

    Result: Partition p2 of tb1 is migrated from DN2 to DN3, and tb1 now belongs to mytg1.

    6. Scenario E -- With FORCE, partition rename:

    The destination table group mytg1 has partitions p1 and p3 (same partitioning mode as tb1, but p3 instead of p2):

    PARTITION BY RANGE(int)
(PARTITION p1 VALUES LESS THAN(20),
 PARTITION p3 VALUES LESS THAN(100))
    ALTER TABLE tb1 SET TABLEGROUP=mytg1 force;

    Result: Partition p2 of tb1 is renamed to p3, and tb1 now belongs to mytg1.

    7. Scenario F -- With FORCE, repartitioning:

    The destination table group mytg1 has a different partitioning mode:

    PARTITION BY RANGE(int)
(PARTITION p1 VALUES LESS THAN(20),
 PARTITION p2 VALUES LESS THAN(60),
 PARTITION p3 VALUES LESS THAN(80))
    ALTER TABLE tb1 SET TABLEGROUP=mytg1 force;

    Result: tb1 is repartitioned to match mytg1. After the operation, tb1 has three partitions:

    CREATE TABLE tb1(a int) PARTITION BY RANGE(a)
(PARTITION p1 VALUES LESS THAN(20),
 PARTITION p2 VALUES LESS THAN(60),
 PARTITION p3 VALUES LESS THAN(80)
)

    8. Scenario G -- With FORCE, incompatible partition key (fails):

    The data types or number of partition key columns of tb1 and mytg1 differ.

    ALTER TABLE tb1 SET TABLEGROUP=mytg1 force;

    Result: The statement fails. Even with force, PolarDB-X cannot change a table's partition key data types or column count.

    Add multiple tables to a table group

    ALTER TABLEGROUP #tgName ADD TABLES table_list [force]

    This adds multiple tables to a table group in a batch. The same requirements as ALTER TABLE ... SET TABLEGROUP apply, with or without the force option.

    Reference table selection

    If the destination table group contains no tables, PolarDB-X selects a reference table from the table list. The reference table determines the data node assignments for the table group. PolarDB-X then recreates the partition group metadata, and the partitions of all other tables in the list are stored on the data nodes specified by the reference table.

    Example

    ALTER TABLEGROUP mytg1 ADD TABLES tb1, tb2, tb3 force;

    This moves tb1, tb2, and tb3 into mytg1. If mytg1 is empty, one of the three tables is selected as the reference table to define partition-to-node mapping. The other tables are adjusted (renamed, migrated, or repartitioned as needed) to match.

    See also