@Rocky docs.starrocks.io/docs/…/ALTER_TABLE#… mentions some gotcha around table SWAP operation. What happens to inactive materialized views post SWAP ? You need to rebuild them in full ?

The

SWAP

operation in StarRocks is atomic, but it has specific side effects on dependent Materialized Views (MVs): 1. MVs are set to Inactive When you perform an

ALTER TABLE ... SWAP

, any Materialized Views that depend on the swapped tables are automatically set to inactive. * Reason: This is a safety mechanism to prevent the MV from serving stale or incorrect data, as the underlying base table it was built on has been replaced by another. * Query Impact: Inactive MVs cannot be used for query acceleration or query rewrite. However, you can still query an inactive asynchronous MV directly as if it were a regular table. 2. Constraints are Removed The documentation notes that Unique Key and Foreign Key constraints on the MVs depending on the swapped tables will be removed and will no longer be available. 3. Reactivation and Refreshing To use the MV again for query rewriting or to resume scheduled refreshes, you must manually reactivate it:

sql
ALTER MATERIALIZED VIEW <mv_name> ACTIVE;

* Do you need a full rebuild? * Metadata: You do not necessarily need to

DROP

and

CREATE

the MV again (unless activation fails due to schema inconsistencies). The

ACTIVE

command restores its status. * Data Consistency: After activation, the MV data will likely be inconsistent with the "new" base table. You will need to trigger a refresh. * Full vs. Incremental: If the MV is non-partitioned, it will always require a full refresh. If the MV is partitioned, StarRocks compares the partition versions of the base table with those in the MV. Since a

SWAP

effectively replaces the entire base table, StarRocks will typically detect that all partitions are out of sync and will require a refresh of all affected partitions (which often amounts to a full rebuild of the data). Summary: You don't usually need to redefine (rebuild the DDL) the MV unless the schema changed in a way that breaks the MV's query, but you must manually reactivate it and should expect to perform a full refresh of the data to ensure it reflects the state of the newly swapped base table. References • sql-reference/sql-statements/materialized_view/ALTER_MATERIALIZED_VIEW.md • using_starrocks/async_mv/troubleshooting_asynchronous_materialized_views.md • using_starrocks/async_mv/feature-support-asynchronous-materialized-views.md • using_starrocks/async_mv/use_cases/data_modeling_with_materialized_views.md

@Rocky also In shared-nothing mode when restoring a backup we encountered problem post SWAP due to apparent table id mismatches.

In StarRocks, the

SWAP

operation and the

RESTORE

process can trigger "Table ID mismatch" issues because of how the system tracks metadata. 1. Why Table IDs Mismatch In StarRocks, Materialized Views (MVs) and other internal metadata track base tables using their internal Table ID, not just their name. * RESTORE Logic: When you restore a table from a backup into a cluster, it typically receives a new internal Table ID (unless it is an overwrite of an existing table with identical metadata). * SWAP Logic: The

ALTER TABLE ... SWAP

command is an atomic metadata exchange. It swaps the names of the tables, but the Table IDs remain with the physical data objects. * The Conflict: If you had an MV pointing to "Table A" (ID 100), and you restore a backup as "Table A_new" (ID 200) and then

SWAP

them: * The name "Table A" now points to ID 200. * The MV still contains metadata referencing ID 100. * StarRocks detects that the base table for that name has a different ID than what is stored in the MV's metadata, leading to a mismatch. 2. What Happens to MVs? As you noted, StarRocks automatically sets MVs to Inactive during a

SWAP

to prevent them from serving data from the "wrong" underlying table ID. * Full Rebuild vs. Activation: You do not usually need to rebuild the MV's DDL from scratch. You can reactivate it using:

sql
    ALTER MATERIALIZED VIEW <mv_name> ACTIVE;

* Data Refresh: Once active, the MV is likely out of sync with the new data. For partitioned MVs, StarRocks will see the version mismatch and trigger a refresh of the affected partitions. For non-partitioned MVs, a full refresh is required to align the MV data with the new table ID's contents. 3. Shared-Nothing Specifics & Restore Gotchas In shared-nothing mode, you may encounter additional issues after a

RESTORE

+ `SWAP`: * Colocate Groups: StarRocks does not automatically restore Colocate Join relationships. If your original tables were part of a colocate group, the restored tables (and therefore the swapped tables) will lose this property. You must manually re-add them to the colocate group using

ALTER TABLE ... SET ("colocate_with" = "group_name")

. * Metadata Inconsistency (Bug Note): There have been known issues (e.g., in v3.1/v3.2) where restoring Primary Key tables could cause metadata inconsistencies after BE restarts if the tablet IDs or version mappings were handled incorrectly during the restore-then-swap sequence. * Unique/Foreign Keys: As mentioned in the docs, any Unique or Foreign Key constraints on the MVs are dropped during the swap and must be manually redefined if needed. Recommendation: If you are using

RESTORE

+

SWAP

as a migration or "blue-green" deployment strategy, the safest path for MVs is to: 1. Perform the

RESTORE

and

SWAP

. 2. Manually

ACTIVE

the MVs. 3. Trigger a manual

REFRESH

(which will likely be a full refresh due to the ID change) to ensure data consistency. 4. Manually re-apply any

colocate_with

properties if you are in shared-nothing mode. References • sql-reference/sql-statements/materialized_view/ALTER_MATERIALIZED_VIEW.md • sql-reference/sql-statements/table_bucket_part_index/ALTER_TABLE.md • using_starrocks/async_mv/Materialized_view.md • knowledge/usage/best-practice.md

@Rocky we use table SWAP to implement a tool to support changing the partition interval from say 24h to 72h by copying into a new table with same schema and doing table flip and copying inflight ingested data between copy completion and table swap. It supports changing colocation parameters also. Is there a better way to support that without SWAP ?

Yes, StarRocks has introduced several native features in recent versions (v3.2+) that allow you to modify partition granularity, bucketing, and colocation properties without performing a full table

SWAP

. These methods preserve the Table ID, which keeps your Materialized Views (MVs) active and avoids the metadata mismatches you encountered. 1. Changing Partition Interval (Granularity) If you are using Expression Partitioning (recommended since v3.1), you can now merge historical partitions or change the granularity of existing data. * Merging Historical Partitions (v3.4+): If you have daily partitions (24h) and want to merge them into a larger interval (e.g., monthly or a specific range), you can use:

sql
    ALTER TABLE <table_name>
    PARTITION BY date_trunc('month', <column>)
    BETWEEN '2024-01-01' AND '2024-03-31';

* Changing Future Intervals (Dynamic Partitioning): If you use Range Partitioning with the Dynamic Partitioning feature, you can simply update the table properties. To change from 1 day (24h) to 3 days (72h):

sql
    ALTER TABLE <table_name> SET (
        "dynamic_partition.time_unit" = "DAY",
        "dynamic_partition.interval" = "3"
    );

Note: This only affects partitions created automatically moving forward. 2. Changing Colocation and Bucketing (v3.2+) Previously, changing the number of buckets or the distribution key required a

SWAP

. Now, StarRocks supports an online "Schema Change" for distribution: * Change Bucketing/Distribution:

sql
    -- Change the number of buckets or the hash columns
    ALTER TABLE <table_name>
    DISTRIBUTED BY HASH(<column>) BUCKETS <new_number>;

This triggers a background data redistribution. The table remains readable/writable during this process. * Change Colocation Group:

sql
    -- Move the table to a different colocation group (or remove it)
    ALTER TABLE <table_name> SET ("colocate_with" = "new_group_name");

This is a metadata-only operation if the distribution (buckets/keys) already matches the group. If not, you may need to run the

DISTRIBUTED BY

command above first. 3. "Partition Swap" (Temporary Partitions) If you still need to perform a "copy and flip" for a specific time range (e.g., to handle complex data transformations), use Temporary Partitions instead of a full Table Swap. This keeps the Table ID and the rest of the table's data intact. 1. Create a temporary partition with the new 72h range. 2.

INSERT INTO ... SELECT

from the old partitions to the temporary one. 3. Replace only that range:

sql
    ALTER TABLE <table_name>
    REPLACE PARTITION (p_old1, p_old2, p_old3)
    WITH TEMPORARY PARTITION (tp_new_72h);

This is atomic and far safer for MVs than a full table

SWAP

. Summary Recommendation To avoid the "Table ID mismatch" and keep MVs active: 1. For distribution/colocation: Use

ALTER TABLE ... DISTRIBUTED BY

and

ALTER TABLE ... SET ("colocate_with" = ...)

. 2. For partition management: Use

REPLACE PARTITION

with temporary partitions for specific data surgery, or the

PARTITION BY ... BETWEEN

syntax (v3.4+) for merging. References • sql-reference/sql-statements/table_bucket_part_index/ALTER_TABLE.md • using_starrocks/Colocate_join.md • table_design/data_distribution/Temporary_partition.md • table_design/data_distribution/expression_partitioning.md References • best_practices/partitioning.md • faq/operation_maintenance_faq.md

@Rocky If you change bucket count on

DISTRIBUTED BY HASH

with ALTER TABLE and it alreayd is one a colo group, do you need to first remove the colo and put it back after when a new colo name?

In StarRocks (starting from v3.2), while you can modify the bucket count of an existing table using

ALTER TABLE ... DISTRIBUTED BY

, there are specific considerations when that table is part of a Colocation Group. Short Answer Yes, you should manually remove the table from the colocation group first. To change the bucket count of a table in a colocation group, follow these steps: 1. Remove the table from the group: Set the property to an empty string.

sql
    ALTER TABLE <table_name> SET ("colocate_with" = "");

2. Modify the bucket count: This triggers a background redistribution of data.

sql
    ALTER TABLE <table_name> DISTRIBUTED BY HASH(<columns>) BUCKETS <new_num>;

3. Optional: Add it back to a (new) group: Once the modification is complete and if you have other tables with the same new bucket count and distribution keys, you can re-group them.

sql
    ALTER TABLE <table_name> SET ("colocate_with" = "new_group_name");

────────── Why this is necessary * Consistency Requirement: A colocation group is a "contract" that ensures all member tables have the exact same bucket count, replication number, and distribution key types. * Operation Rejection: If you attempt to change the bucket count on a single table while it is still tied to a group, StarRocks will likely reject the command with an error because the change would violate the group's schema (specifically the

BucketsNum

property of the group). * Decoupling: By setting

"colocate_with" = ""

, you decouple the table from the group's metadata. This allows the background data redistribution process (which is asynchronous and involves rewriting tablets) to proceed independently. * Parent Table Logic: If the table you are modifying is the "Parent Table" (the one that defined the group's initial properties), modifying it while other tables are still in the group would break the colocation for all other tables. Better Alternative to SWAP Since you are using

SWAP

to change partition intervals and colocation parameters, the native

ALTER TABLE

commands are much better because: 1. Materialized Views stay Active: Because the internal Table ID does not change, your MVs won't be set to "Inactive." 2. Schema Change is Online: The redistribution of data happens in the background. The table remains readable during the process. 3. No Metadata Desync: You avoid the "Table ID mismatch" issues common with

RESTORE

+

SWAP

. Summary of Commands Feature | Old way (Swap) | New way (v3.2+) Change Buckets | Create new table + Swap |

ALTER TABLE ... DISTRIBUTED BY ... BUCKETS <N>

Change Partition Interval | Create new table + Swap |

ALTER TABLE ... PARTITION BY date_trunc(...)

(v3.4+) Change Colocation | Create new table + Swap |

ALTER TABLE ... SET ("colocate_with" = "...")

Note: If you are in shared-data mode (using cloud-native storage),

ALTER TABLE ... DISTRIBUTED BY

is currently only supported for Random bucketing in some versions; double-check your version's release notes if using shared-data. For shared-nothing, it is fully supported for Hash bucketing. References • sql-reference/sql-statements/table_bucket_part_index/ALTER_TABLE.md • using_starrocks/Colocate_join.md • table_design/data_distribution/Data_distribution.md • using_starrocks/async_mv/troubleshooting_asynchronous_materialized_views.md References • using_starrocks/Colocate_join.md

@Rocky If a client app uses MySQL wire protocol with server side prepared statement, is there any gotcha involving that premade plan and the SWAP operation.

When using the MySQL wire protocol with server-side prepared statements (

PREPARE

/

EXECUTE

or binary protocol via JDBC), there is a significant "gotcha" related to the

SWAP

operation: 1. Plan Stale-ness (Resolution at Prepare Time) In StarRocks, when you

PREPARE

a statement, the Frontend (FE) parses the SQL and generates an execution plan at that moment. This plan includes the resolution of table names to their internal Table IDs. * The Problem: If a client prepares a statement against

table_a

(ID 101) and you then execute

ALTER TABLE table_a SWAP WITH table_b

(where

table_b

is ID 102), the prepared statement's cached plan may still point to ID 101. * The Result: Even though the name

table_a

now logically points to the new data (ID 102), the existing prepared statement in that specific session might continue to query the "old" data (which is now named

table_b

but still has ID 101). 2. Session Scoping Prepared statements in StarRocks are session-bound. They are not shared across different database connections. * The Gotcha: If you have a long-lived connection pool (e.g., a service that doesn't restart or recycle connections), those connections will keep using their "stale" plans after the swap. New connections created after the swap will resolve the name correctly to the new ID. * Recommendation: After a

SWAP

operation, you should ideally recycle the application's connection pool or explicitly run

DEALLOCATE PREPARE

for any affected statements to force the FE to re-resolve the table names. 3. Schema Consistency and Result Types If the two tables being swapped have even minor schema differences (e.g., column order, nullability, or hidden version columns in Primary Key tables): * Binary Protocol Issues: Server-side prepared statements use a binary result format. If the plan was built for

table_a

and the swap introduces

table_b

with different internal metadata, the binary

RowDescription

sent to the client might no longer match the actual data being returned, leading to protocol errors or data corruption in the client application (like a

java.sql.SQLException: Column index out of range

). Summary of Gotchas Feature | Behavior with

SWAP

Table ID Binding | Plans usually bind to the ID at

PREPARE

time; they do not automatically "follow" the name change to the new ID. Connection Pools | Long-lived connections are most at risk of "seeing" the old data after a swap. Invalidation | Unlike some other DDL,

SWAP

might not explicitly trigger an "Invalidated Plan" error in existing sessions, leading to silent "old data" reads. Binary Protocol | Schema mismatches between swapped tables can cause client-side decoding errors. Best Practice: If your workflow relies on

SWAP

for "blue-green" table updates and you use server-side prepared statements, you must treat the

SWAP

as a breaking change for active sessions. Ensure your application can handle a connection reset or manually re-prepare statements immediately following the swap. References • sql-reference/sql-statements/prepared_statement.md • sql-reference/sql-statements/table_bucket_part_index/ALTER_TABLE.md • faq/operation_maintenance_faq.md • knowledge/usage/best-practice.md References • sql-reference/sql-statements/prepared_statement.md • using_starrocks/SQL_plan_manager.md

@Rocky what are the limitation of online repartitionning. Is it possible for example we can only go from say 1 day to 1 week coarser. We want to go from 1 day to N day (less than a week), is it possible ?

StarRocks v3.4+ introduced a native way to perform online repartitioning (merging or changing partition granularity) without a full table

SWAP

. For your specific case of moving from 1 day to $N$ days, here is how it works and its limitations: 1. Is $N$ days possible? Yes. While

date_trunc

only supports fixed units (hour, day, week, month), you can use the

time_slice

function to define an arbitrary interval like 3 days or 5 days.

sql
-- Example: Merging daily partitions into 3-day partitions (v3.4+)
ALTER TABLE your_table 
PARTITION BY time_slice(event_time, INTERVAL 3 DAY)
BETWEEN '2024-01-01' AND '2024-02-01';

2. Limitations of Online Repartitioning While this is a powerful alternative to

SWAP

, there are specific constraints: * Direction (Coarsening only): This feature is designed to merge finer-grained partitions (e.g., 1 day) into coarser-grained ones (e.g., 3 days or 1 month). StarRocks does not currently support using this syntax to "split" a coarse partition into finer ones (e.g., 1 month down to 1 day) in a single online command. * Version Requirement: This syntax requires StarRocks v3.4.0 or later. * Expression Partitioning Only: The table must already be using Expression Partitioning (introduced in v3.1). If your table uses the legacy

PARTITION BY RANGE(col)

syntax, you cannot use this specific

ALTER TABLE ... PARTITION BY

command. * Concurrency: You cannot run this repartitioning command in parallel with other Schema Change operations or DML operations (like

INSERT OVERWRITE

or

DELETE

). Regular

INSERT

and

SELECT

queries typically continue to work, but the table's schema is "locked" for other modifications during the transition. * Single Column: It only supports tables with a single partition column. If you have multiple partition columns, you must stick to the

SWAP

or

REPLACE PARTITION

method. * Asynchronous: The process is handled as a background task. You can check the progress using

SHOW ALTER TABLE COLUMN

. Why this is better than SWAP * Table ID Persistence: The internal Table ID remains the same. This is critical because Materialized Views tied to the table will remain active and won't need to be recreated. * Partial Updates: You can repartition only a specific range of time (using the

BETWEEN

clause) rather than the entire table, which is much faster for multi-year datasets. Summary If $N > 1$ (moving from 1 day to a larger interval), you can use

ALTER TABLE ... PARTITION BY time_slice(...)

in v3.4. If you need to go "finer" (e.g., 1 week to 1 day) or are on an older version, the Temporary Partition + Replace Partition method is the recommended "online" alternative to a full table

SWAP

. References • table_design/data_distribution/expression_partitioning.md • sql-reference/sql-statements/table_bucket_part_index/ALTER_TABLE.md • best_practices/partitioning.md • sql-reference/sql-functions/date-time-functions/time_slice.md References • sql-reference/sql-statements/table_bucket_part_index/ALTER_TABLE.md • using_starrocks/async_mv/use_cases/create_partitioned_materialized_view.md • sql-reference/sql-functions/date-time-functions/time_slice.md • table_design/data_distribution/Data_distribution.md