@Rocky can a table entering an optimizing/optimize state block reads or writes to it? Or is it completely asynchronous and just adds more work to the nodes?

In StarRocks, whether a table in an "optimizing" state blocks operations depends on how the optimization was triggered and your cluster's version/configuration. 1. Automatic Background Compaction (Asynchronous) If the "optimizing" behavior is just the standard background compaction that StarRocks performs automatically: * Reads/Writes: It is completely asynchronous and never blocks reads or writes. * Impact: It primarily adds background resource overhead (CPU and I/O) to the BE (Backend) nodes. StarRocks uses MVCC (Multi-Version Concurrency Control) to ensure that while the system merges old data versions into new ones, your queries and loads continue against the existing versions. 2. Manual Optimization (

ALTER TABLE ... OPTIMIZE

) If you manually triggered optimization via SQL, the behavior depends on the optimization "job" type: * Online Optimization (

OnlineOptimizeJobV2

): * Introduced in versions v3.3.3, v3.4.0, and v3.5.0 (controlled by the FE config

enable_online_optimize_table

, which is

true

by default). * Writes: Does not block writes. It uses a non-blocking path to reorganize data. * Reads: Does not block reads. * Conditions: To use this non-blocking path, the table must meet certain criteria (e.g., no change to keys/sort/partitioning, not a cloud-native table, and replicated storage enabled). * Traditional Optimization (

OptimizeJobV2

): * If you are on an older version or the table doesn't meet "online" criteria, StarRocks falls back to the legacy path. * Writes: May block write operations during the optimization process because it requires a more restrictive metadata lock to swap the data. * Reads: Typically not blocked, as StarRocks still serves queries from the original data until the new version is committed. Summary Table Scenario | Blocks Reads? | Blocks Writes? | Type Automatic Compaction | No | No | Background Manual

OPTIMIZE

(Online) | No | No | Manual Manual

OPTIMIZE

(Legacy) | No | Yes | Manual How to check the state: You can monitor the progress of manual optimization jobs with:

sql
SHOW ALTER TABLE OPTIMIZE;

If you see the state is

PENDING

or

OPTIMIZING

, it is actively working in the background. If you notice writes are being rejected with "table state is not normal," you are likely hitting the legacy blocking behavior. Legacy Optimization path which blocks writes. To avoid this, ensure you are on v3.3.3+ and

enable_online_optimize_table

is set to

true

. References • faq/Sql_faq.md • knowledge/admin_and_operation/shared_data_compaction.md

How do I check if a table can be optimized online @Rocky

To check if a table is eligible for non-blocking online optimization, you need to verify both your cluster configuration and the table's properties. StarRocks uses the

OnlineOptimizeJobV2

(non-blocking) path only if all the following conditions are met: 1. Check Global Configuration First, ensure your StarRocks version is v3.3.3, v3.4.0, or v3.5.0+ and that the online optimization feature is enabled:

sql
ADMIN SHOW FRONTEND CONFIG LIKE 'enable_online_optimize_table';

The value must be

true

(which is the default). 2. Check Table Properties Run

SHOW CREATE TABLE <table_name>;

and verify the following: * Replicated Storage: The table must have

"replicated_storage" = "true"

in its properties. * Storage Type: The storage type must not be

COLUMN_WITH_ROW

. * Distribution: The distribution type must not be

RANDOM

(it must be

HASH

or another specific distribution). * Table Type: * It must be a standard OLAP table. * It cannot be a Cloud-native (Shared-data) table. * It cannot be a Materialized View. * The Command itself: When you run the

OPTIMIZE TABLE

command, you must not be changing the partition, keys, or sort specifications. It should be a standard optimization of the existing structure. 3. How to verify the job type Once you start an optimization job, you can check if StarRocks actually used the non-blocking path by looking at the job details:

sql
SHOW ALTER TABLE OPTIMIZE;

If the job was able to run online, the internal logic uses

OnlineOptimizeJobV2

. While the

SHOW

command might not explicitly label it "Online" in all versions, you can observe the behavior: if writes to the table are not being blocked and the state is

OPTIMIZING

, it is using the online path. If any of the requirements above are not met, StarRocks will automatically fall back to the legacy

OptimizeJobV2

, which will block writes during the final stages of the process. References • administration/management/FE_configuration.md References • administration/management/FE_configuration.md • using_starrocks/Cost_based_optimizer.md • sql-reference/System_variable.md • sql-reference/sql-statements/cbo_stats/ANALYZE_TABLE.md

@Rocky where can I see this

Storage Type: The storage type must not be

COLUMN_WITH_ROW

.

Also,

The Command itself: When you run the

OPTIMIZE TABLE

command

What if I am changing the number of buckets or number of replicas for a table, is that change async/online?

Where to check "Storage Type" You can see the

STORE_TYPE

of a table by running the following command:

sql
SHOW CREATE TABLE <table_name>;

Look for

STORE_TYPE

inside the

PROPERTIES

section at the end of the statement. * If you see

"store_type" = "column_with_row"

, it is a hybrid row-column storage table (typically a Primary Key table optimized for point lookups). * If it is not present, it defaults to standard columnar storage. As mentioned before, hybrid tables do not support the non-blocking "Online Optimize" path yet. ────────── Is changing Buckets or Replicas online? Yes, these operations are asynchronous and non-blocking for both reads and writes. 1. Changing the Number of Buckets (Resharding) Since v3.2, you can modify the bucket count using:

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

* Reads/Writes: Not blocked. StarRocks creates new tablets in the background and migrates the data. Your table remains fully available. * Asynchronous: Yes. You can track progress with

SHOW ALTER TABLE COLUMN;

(as it is treated as a schema-style change). * Blocking Period: There is only a very brief metadata swap at the very end of the process. 2. Changing the Number of Replicas You can change the replica count for the entire table or specific partitions:

sql
-- For all partitions
ALTER TABLE <table_name> SET ("replication_num" = "3");

-- For specific partitions
ALTER TABLE <table_name> MODIFY PARTITION (*) SET ("replication_num" = "3");

* Reads/Writes: Not blocked. This command only updates the metadata in the FE (Frontend). * Asynchronous: Yes. After the command returns, background threads on the BE (Backend) nodes will start cloning or deleting replicas to match the new target. * Impact: It only adds background network and disk I/O work; it does not stop data ingestion or queries. Summary If you are performing these changes, the table stays Online. The only risk is the temporary increase in resource usage (CPU/IO) on the BE nodes as they move data around in the background. References • table_design/hybrid_table.md • sql-reference/sql-statements/table_bucket_part_index/ALTER_TABLE.md • administration/management/FE_configuration.md • administration/management/resource_management/Replica.md • faq/operation_maintenance_faq.md • table_design/data_distribution/Data_distribution.md References • sql-reference/sql-statements/table_bucket_part_index/ALTER_TABLE.md • table_design/data_distribution/Data_distribution.md • knowledge/usage/best-practice.md