@Rocky is it possible to re-partition a table?

Yes, it is possible to re-partition a StarRocks table, but the method depends on whether you are changing the partition strategy (e.g., changing the partition column) or just modifying existing partition ranges (e.g., merging or splitting). Here are the common scenarios and how to handle them: 1. Modifying Partition Ranges (Merging or Splitting) If you want to change the granularity of your partitions (for example, moving from daily partitions to monthly partitions), you have two main options: * Using Temporary Partitions (Recommended for Range/List): 1. Create Temporary Partitions with the new desired ranges using

ALTER TABLE ... ADD TEMPORARY PARTITION

. 2. Migrate data from the original partitions to the temporary ones using

INSERT INTO ... SELECT

. 3. Atomically replace the old partitions with the new ones using

ALTER TABLE ... REPLACE PARTITION (...) WITH TEMPORARY PARTITION (...)

. * Merging Expression Partitions: If you are using Expression Partitioning (e.g.,

PARTITION BY date_trunc('day', col)

), StarRocks allows you to merge multiple fine-grained partitions into a coarser one directly:

sql
    ALTER TABLE my_table PARTITION BY date_trunc('month', event_day)
    BETWEEN '2024-01-01' AND '2024-03-31';

2. Changing the Partition Column or Strategy You cannot directly change the partition column or the partitioning type (e.g., from Range to List) of an existing table via

ALTER TABLE

. * Method: You must create a new table with the new partitioning scheme and migrate the data:

sql
    -- 1. Create a new table with the new partitioning
    CREATE TABLE new_table ... PARTITION BY RANGE(new_col) ...;

    -- 2. Migrate data
    INSERT INTO new_table SELECT * FROM old_table;

    -- 3. Rename tables
    ALTER TABLE old_table RENAME backup_table;
    ALTER TABLE new_table RENAME old_table;

3. Modifying Bucketing (Data Distribution within Partitions) If by "re-partitioning" you mean changing how data is distributed across buckets (the

DISTRIBUTED BY

clause), StarRocks (v3.2+) allows you to do this directly:

sql
-- Change bucket column and/or number of buckets
ALTER TABLE my_table DISTRIBUTED BY HASH(new_user_id) BUCKETS 20;

This will trigger an asynchronous background job to redistribute the data. 4. Converting Non-Partitioned to Partitioned You cannot convert a non-partitioned table into a partitioned table directly. Like changing the partition column, you must create a new partitioned table and migrate the data using

INSERT INTO ... SELECT

. Summary Table Scenario | Method Change ranges (Range/List) |

ADD TEMPORARY PARTITION

->

INSERT

->

REPLACE PARTITION

Merge Expression Partitions |

ALTER TABLE ... PARTITION BY ...

Change Partition Column | Create new table +

INSERT INTO

Change Bucketing |

ALTER TABLE ... DISTRIBUTED BY ...

Non-partitioned to Partitioned | Create new table +

INSERT INTO

References • table_design/data_distribution/Temporary_partition.md • sql-reference/sql-statements/table_bucket_part_index/ALTER_TABLE.md