http://starrocks.io logo
Join Slack
Powered by
<@U0A71G31CDV> Will hybrid row column storage mean...
# questions-and-troubleshooting
m
@Rocky Will hybrid row column storage meaningfully help update throughput? For context, my insertions are approximately 50k QPS while updates are approximately 10k QPS, hence a 5:1 insert to update ratio. My understanding is that hybrid storage will slightly more than double storage cost and increase write cost as data now needs to be written in row and columnar form. However, it significantly boosts partial update performance as the entire row can be fetched by the shadow __row data. Will increased write cost of hybrid storage be offset by gains of faster partial updates? My data is approximately 500 columns wide.
r
For your specific workload—*500 columns wide, 50k inserts/s, and 10k updates/s*—hybrid row-column storage (enabled via the 
STORE_ROW_COLUMN
property) will likely provide a net gain in update throughput, though at a significant cost to storage and some overhead for inserts. 1. Will it offset the write cost? Yes, for a 500-column table, it almost certainly will. In a standard StarRocks Primary Key table (columnar only), a partial update requires the engine to reconstruct the full row to perform the "Delete+Insert" operation. Without a row store, StarRocks must read all 500 columns from disk to rebuild that row. This triggers massive I/O amplification (500 separate column reads/seeks per updated row). With hybrid storage: * The Gain: To reconstruct the row, StarRocks only performs one read of the hidden 
__row
column. For a 500-column table, replacing 500 column reads with 1 binary column read is a massive performance leap. * The Cost: Every insert (your 50k QPS) must now encode the data into the 
__row
binary format and write it alongside the columnar data. This increases CPU usage and disk write bandwidth. The Verdict: Because your table is extremely wide (500 columns), the I/O saved on the 10k updates/s will likely far outweigh the additional write cost incurred by the 50k inserts/s. 2. Impact on Your Workload (5:1 Ratio) * Update Throughput: You should see a significant boost. Internal benchmarks on 200-column tables have shown throughput improvements of up to 10x for point-heavy operations when moving from columnar to row-store. * Insert Throughput: You will likely see a 10%–30% decrease in raw insert throughput capacity compared to a pure columnar table, as the FE/BE must work harder to encode the 
__row
column. * Storage Cost: Expect the storage to more than double. Row-based storage is significantly less compressible than columnar storage. In a 500-column wide table, the 
__row
column will be a large, opaque binary blob that stores every value again. 3. Key Considerations for Your Setup * Partial Update Mode: Ensure you are using Row Mode (the default for Primary Key tables). StarRocks also has a "Column Mode" for partial updates (suitable for updating 1-2 columns across millions of rows), but for 500 columns and high-frequency updates, Hybrid Row-Column Storage is the intended solution. * Cluster Type: Hybrid storage is currently not supported on shared-data (compute-storage separated) clusters. It requires a shared-nothing (Local Disk) deployment. * Memory: Ensure your BE nodes have sufficient memory for the primary key index, as the 10k/s update rate will keep the index "hot." Summary Recommendation If your primary bottleneck is the latency or resource exhaustion caused by the 10k/s updates on a 500-column table, hybrid storage is the correct architectural choice. The increase in storage and insert-time CPU is the price paid to eliminate the "500-column read" bottleneck during updates. To enable it on a new table:
Copy code
sql
CREATE TABLE my_wide_table (
    pk_id BIGINT,
    ... -- 500 columns
) PRIMARY KEY (pk_id)
DISTRIBUTED BY HASH(pk_id)
PROPERTIES (
    "store_row_column" = "true"
);
References • knowledge/usage/best-practice.md • loading/Spark-connector-starrocks.md • sql-reference/sql-statements/table_bucket_part_index/UPDATE.md • table_design/hybrid_table.md • loading/Load_to_Primary_Key_tables.md • administration/management/monitoring/metrics.md
m
@Rocky If I add this property on an existing table, will row data begin writing immediately or only for the next partition?
Copy code
Also, I also have to set this variable `1. 1. ADMIN SET FRONTEND CONFIG ("enable_experimental_rowstore" = "true");
on top of adding the 
"store_type" = "column_with_row"
property to my PK table right? Should I expect a net decrease in CPU utilization? And also transaction apply latency? Or just update rowset commit apply duration? what metirc improvements should I wartch out for
r
For your workload of 500 columns with a 5:1 insert-to-update ratio, hybrid row-column storage is an ideal architectural fit. Here are the specific details regarding implementation, configuration, and performance expectations: 1. Behavior on Existing Tables When you add this property to an existing table via 
ALTER TABLE
, StarRocks triggers an asynchronous schema change (available since v3.2.4). * Backfilling: The row data will not just apply to the "next partition." StarRocks will create a background job to rewrite existing data into the new hybrid format (adding the hidden 
__row
column). * Immediate Writing: New data ingested while the job is running will be written in both formats to the new rowsets. However, you won't see the full performance benefit for all data until the background schema change task completes. You can monitor progress with 
SHOW ALTER TABLE COLUMN;
. 2. Configuration Requirements Yes, you must set both the global configuration and the table-level property. * FE Config: 
ADMIN SET FRONTEND CONFIG ("enable_experimental_rowstore" = "true");
* Table Property: Use 
"store_type" = "column_with_row"
. (Note: 
"store_row_column" = "true"
was used in some early versions, but 
store_type
is the standard for recent 3.x releases). 3. Performance & CPU Expectations Since your table is 500 columns wide, the performance profile will shift significantly: * Update/Apply Phase: You should see a major net decrease in CPU and I/O utilization. Without row storage, a partial update on a PK table must read all 500 columnar files to reconstruct the row. With hybrid storage, it only reads one: the 
__row
column. This eliminates the massive decompression and merge overhead of 500 separate columns. * Insert Phase: You will see a slight increase in CPU and disk write bandwidth during the write phase (50k QPS). The BE must now encode every row into a binary blob. * Transaction Apply Latency: This should drop drastically. In wide tables, the "Apply" stage (where the row is reconstructed and the new version is written) is usually the bottleneck. 4. Metrics to Watch To verify the impact, monitor these specific metrics in your Prometheus/Grafana dashboard: * 
update_rowset_commit_apply_duration_us
: This is the most critical metric. It measures the time spent in the "Apply" phase of your updates. You should see this number plummet after the hybrid store is active. * 
update_mem_bytes
: Monitor this to ensure the Primary Key index and Apply tasks aren't exceeding your memory limits. * 
be_cpu_utilization
: Watch for the tradeoff: lower CPU during update peaks, but a higher "baseline" CPU during your 50k QPS insert streams. * 
be_segment_read_count
/ 
be_segment_read_bytes
: You should see a significant reduction in these during update-heavy periods because the engine is no longer scanning 500 column segments per row. Recommendation: Given your 500-column width, the I/O and CPU savings on the 10k/s updates will almost certainly far outweigh the extra overhead on the 50k/s inserts. References • table_design/hybrid_table.md • administration/management/monitoring/metrics.md • sql-reference/sql-statements/table_bucket_part_index/ALTER_TABLE.md • knowledge/usage/best-practice.md References • knowledge/usage/best-practice.md • loading/Load_to_Primary_Key_tables.md • table_design/hybrid_table.md
m
@Rocky [42000][5064] Getting analyzing error. Detail message: Unknown properties: {store_type=column_with_row}.
Copy code
ALTER TABLE requests SET ("store_type" = "column_with_row");
r
The error 
Unknown properties: {store_type=column_with_row}
typically occurs for one of two reasons: 1. The FE Configuration is not enabled Even for an 
ALTER TABLE
statement, StarRocks will not recognize the 
store_type
property unless the experimental row-store feature is enabled globally. You must run this command first:
Copy code
sql
ADMIN SET FRONTEND CONFIG ("enable_experimental_rowstore" = "true");
_Note: Ensure you run this on the Leader FE. You can verify it is set by running 
ADMIN SHOW FRONTEND CONFIG LIKE 'enable_experimental_rowstore';
._ 2. Version Compatibility Support for altering an existing table to use hybrid storage was introduced in StarRocks v3.2.4. * If you are on v3.2.3 or earlier: You can only enable this property at the time of table creation. You would need to create a new table with the property and migrate the data. * If you are on v3.2.4 or later: The 
ALTER TABLE
syntax you used is correct. If it still fails after setting the FE config, verify your version with 
SELECT min(version) FROM be_nodes;
. ────────── Answers to your performance questions: 1. Should I expect a net decrease in CPU utilization? Yes. While the CPU work during insertion (50k QPS) will increase slightly (to encode the 
__row
column), the CPU work during updates (10k QPS) will drop significantly. In a 500-column table, a partial update without row-store forces the CPU to decompress and process 500 separate columnar blocks to find the values for a single row. With row-store, the CPU only decompresses one block. For your 5:1 ratio, the massive "Update" savings usually outweigh the "Insert" overhead. 2. Transaction apply latency vs. Rowset commit apply duration? * Transaction Apply Latency: You will see a significant decrease. This is the time it takes to make the data visible after the commit. Since the "Apply" logic no longer has to fetch 500 columns from disk/cache, the entire transaction finishes faster. * Update Rowset Commit Apply Duration: This is exactly where the primary improvement will show up in your metrics. This specific phase of the write pipeline is what hybrid storage optimizes. Summary of Metrics to watch: * 
update_rowset_commit_apply_duration_us
: This should show a dramatic reduction (often 5x-10x lower). * 
be_segment_read_count
: This will drop during update cycles because you are reading 1 segment (
__row
) instead of 500. * 
be_cpu_utilization
: You should see lower "spikes" during heavy update periods. One final check: Ensure your table is a Primary Key table, as hybrid storage is not supported on Duplicate, Aggregate, or Unique key models. References • table_design/hybrid_table.md • sql-reference/sql-statements/table_bucket_part_index/ALTER_TABLE.md • administration/management/monitoring/metrics.md • knowledge/usage/best-practice.md • administration/management/FE_configuration.md • loading/Load_to_Primary_Key_tables.md References • table_design/hybrid_table.md • administration/management/FE_configuration.md • knowledge/usage/best-practice.md
m
@Rocky
Copy code
2026-05-16T02:40:56.433Z	ERROR	[StarRocks] failed to execute DDL	{"query": "CREATE TABLE IF NOT EXISTS requests.requests_v2(\n\t\treceived DATETIME NOT NULL,\n\t\tauction_id STRING NOT NULL,\n\t\timp_key STRING NOT NULL\n\t)\n\tPRIMARY KEY (received, auction_id, imp_key)\n\tPARTITION BY date_trunc('hour', received)\n\tDISTRIBUTED BY HASH (auction_id) BUCKETS 128\n\tORDER BY (received, auction_id)\n\tPROPERTIES (\n\t\t\"store_type\" = \"column_with_row\",\n\t\t\"enable_persistent_index\" = \"true\",\n\t\t\"partition_live_number\" = \"24\",\n        \"replication_num\" = \"1\"\n\t);", "error": "Error 1064 (HY000): Unknown properties: {store_type=column_with_row}"}
2026-05-16T02:40:56.433Z	PANIC	[StarRocks] failed to create table	{"error": "exec \"CREATE TABLE IF NOT EXISTS requests.requests_v2(\\n\\t\\treceived DATETIME NOT NULL,\\n\\t\\tauction_id STRING NOT NULL,\\n\\t\\timp_key STRING NOT NULL\\n\\t)\\n\\tPRIMARY KEY (received, auction_id, imp_key)\\n\\tPARTITION BY date_trunc('hour', received)\\n\\tDISTRIBUTED BY HASH (auction_id) BUCKETS 128\\n\\tORDER BY (received, auction_id)\\n\\tPROPERTIES (\\n\\t\\t\\\"store_type\\\" = \\\"column_with_row\\\",\\n\\t\\t\\\"enable_persistent_index\\\" = \\\"true\\\",\\n\\t\\t\\\"partition_live_number\\\" = \\\"24\\\",\\n        \\\"replication_num\\\" = \\\"1\\\"\\n\\t);\": Error 1064 (HY000): Unknown properties: {store_type=column_with_row}"}
panic: [StarRocks] failed to create table
r
The 
Unknown properties: {store_type=column_with_row}
error indicates that the StarRocks Frontend (FE) does not recognize the hybrid storage property. This is usually caused by one of the following three reasons: 1. You are on a Shared-data (Cloud-native) Cluster Hybrid row-column storage is currently not supported on shared-data clusters (compute-storage separated architecture). It is only available for shared-nothing clusters (local storage). * How to check: Run 
SHOW FRONTENDS;
. If the 
IsMaster
or cluster metadata suggests a cloud-native or shared-data deployment, this feature is unavailable. * Symptom: Even if the FE config is set to 
true
, the property will remain "unknown" because the shared-data table engine does not register it. 2. Invalid 
ORDER BY
Clause in Primary Key Table In your DDL, you have both 
PRIMARY KEY (...)
and 
ORDER BY (...)
. * The Conflict: In StarRocks, Primary Key tables do not support the 
ORDER BY
clause. The primary key columns automatically serve as the sort key. * The Fix: Remove the 
ORDER BY (received, auction_id)
line. Adding an 
ORDER BY
clause to a PK table can cause the parser to misidentify the table model, leading it to reject PK-specific properties like 
store_type
. 3. Verify FE Configuration Persistence Ensure the experimental flag is active on the Leader FE. Run this command to verify:
Copy code
sql
ADMIN SHOW FRONTEND CONFIG LIKE 'enable_experimental_rowstore';
If the value is 
false
, the 
ADMIN SET
command might have failed or been run on a non-leader node. If you cannot set it dynamically, add 
enable_experimental_rowstore = true
to your 
fe.conf
and restart the FE. ────────── Corrected DDL Example Try running this simplified version (ensuring you are on a shared-nothing cluster and the FE config is verified as 
true
):
Copy code
sql
CREATE TABLE IF NOT EXISTS requests.requests_v2 (
    received DATETIME NOT NULL,
    auction_id STRING NOT NULL,
    imp_key STRING NOT NULL
)
PRIMARY KEY (received, auction_id, imp_key)
PARTITION BY date_trunc('hour', received)
DISTRIBUTED BY HASH (auction_id) BUCKETS 128
-- ORDER BY removed (PK tables use the PK as the sort key)
PROPERTIES (
    "store_type" = "column_with_row",
    "enable_persistent_index" = "true",
    "partition_live_number" = "24",
    "replication_num" = "1"
);
Metrics & Utilization Check * Net CPU utilization: You will see a net decrease in total cluster CPU. While the write path (50k QPS) will use more CPU to encode the row, the apply path (10k QPS) will no longer have to decompress and merge 500 columnar files. For a 500-column table, the savings on the "Apply" side are massive. * Metric to watch: 
update_rowset_commit_apply_duration_us
. This represents the time spent writing the update to disk. With hybrid storage, this should drop significantly because the "read-before-write" only touches one column (
__row
) instead of 500. References • table_design/hybrid_table.md • administration/management/FE_configuration.md • sql-reference/sql-statements/table_bucket_part_index/CREATE_TABLE.md • administration/management/monitoring/metrics.md References • table_design/hybrid_table.md • knowledge/usage/best-practice.md • administration/management/FE_configuration.md • sql-reference/sql-statements/table_bucket_part_index/CREATE_TABLE.md
m
@Rocky Your simplified sql doesnt work. I know I am running shared nothing cluster. I have set enable experimental rowstore to be true. Still failing
r
The 
Unknown properties: {store_type=column_with_row}
error indicates that the StarRocks Frontend (FE) hasn't registered that property in its allowed list. Since you've already confirmed you're on a shared-nothing cluster and have set the config, there are only three possibilities remaining: 1. StarRocks Version Requirement Hybrid row-column storage was officially introduced in StarRocks v3.2.3. * If you are on v3.2.0, v3.2.1, or v3.2.2: The 
enable_experimental_rowstore
flag and the 
store_type
property do not exist yet. * Verification: Run 
SELECT version();
and ensure it is 3.2.3 or higher. 2. Verify Config Application on the Leader FE The 
ADMIN SET FRONTEND CONFIG
command must take effect on the Leader FE for DDLs to work. Sometimes setting it via SQL doesn't persist or apply correctly across all nodes if there's a connectivity issue during the update. * Verification: Run this on your SQL client:
Copy code
sql
    ADMIN SHOW FRONTEND CONFIG LIKE 'enable_experimental_rowstore';
* The "Hard" Fix: If the above returns 
false
or if you want to be 100% sure, add 
enable_experimental_rowstore = true
directly to your 
fe.conf
file on all FE nodes and restart the FEs. 3. Alternative Property Name (Early Beta) In some early preview builds of the 3.2 branch, the property name was briefly 
"store_row_column" = "true"
. If you are on an early v3.2.x release and 
store_type
isn't working, try:
Copy code
sql
PROPERTIES (
    "store_row_column" = "true",
    "enable_persistent_index" = "true"
    ...
);
────────── Why your simplified SQL was failing (Checklist) Even if you fix the property name, ensure the following are true for a 500-column table: 1. Primary Key Model: It must be a Primary Key table. 2. No `ORDER BY`: In StarRocks, Primary Key tables use the PK as the sort key automatically. Including an explicit 
ORDER BY
clause in the DDL will cause a syntax error (which I removed in my previous suggestion). 3. Column Types: If any of your 500 columns are complex types (JSON, Map, Struct, etc.), you must be on v3.2.4 or higher. Versions 3.2.3 and below only support basic types in the row-store. Expected Metric Improvements (Summary) Once you successfully create the table: * 
update_rowset_commit_apply_duration_us
: This is your "North Star" metric. For a 500-column table, you should see this drop by an order of magnitude. * 
be_cpu_utilization
: You will see a net decrease. The "Read" overhead of 500 columns during an update is much higher than the "Encoding" overhead of the 50k inserts. * 
be_segment_read_bytes
: This will decrease significantly during the "Apply" phase. Recommendation: Check 
SELECT version();
first. If you are on 
v3.2.3+
, try setting the config in 
fe.conf
and restarting the FE. References • table_design/hybrid_table.md • administration/management/FE_configuration.md • sql-reference/sql-statements/table_bucket_part_index/CREATE_TABLE.md • administration/management/monitoring/metrics.md • knowledge/usage/best-practice.md References • table_design/hybrid_table.md • sql-reference/sql-statements/table_bucket_part_index/ALTER_TABLE.md
m
@Rocky ERROR 1064 (HY000): column_with_row storage type must have some non-key columns
r
The error "column_with_row storage type must have some non-key columns" occurs because the hybrid storage feature works by encoding all value columns (non-primary key columns) into a hidden binary column called 
__row
. In the SQL snippet you provided, every single column (
received
, 
auction_id
, 
imp_key
) is part of the 
PRIMARY KEY
. This means there are no "value" columns to store in the row-store. To fix this: You must include at least one column that is not part of the Primary Key. Since you mentioned having a 500-column wide table, you should include your actual data columns in the DDL. Corrected Example:
Copy code
sql
CREATE TABLE IF NOT EXISTS requests.requests_v2 (
    received DATETIME NOT NULL,
    auction_id STRING NOT NULL,
    imp_key STRING NOT NULL,
    -- Add at least one non-key (value) column here:
    payload JSON, 
    request_data STRING,
    ad_id INT
)
PRIMARY KEY (received, auction_id, imp_key)
PARTITION BY date_trunc('hour', received)
DISTRIBUTED BY HASH (auction_id) BUCKETS 128
-- Note: ORDER BY is removed as it is not supported in Primary Key tables
PROPERTIES (
    "store_type" = "column_with_row",
    "enable_persistent_index" = "true",
    "partition_live_number" = "24",
    "replication_num" = "1"
);
Why is this required? The purpose of 
column_with_row
is to speed up partial updates and point lookups by grouping all the values of a row together. If a table consists only of keys, those keys are already indexed and stored in the Primary Key structure. There is no additional data for the row-store to optimize, so StarRocks rejects the property as it would provide no benefit and only waste disk space. Summary Checklist: 1. Non-Key Columns: Ensure your DDL includes the other ~497 columns that are not part of the primary key. 2. No `ORDER BY`: Remove the 
ORDER BY
clause; the Primary Key automatically handles sorting. 3. FE Config: Keep 
enable_experimental_rowstore = true
(which you've already done). References • table_design/hybrid_table.md • sql-reference/sql-statements/table_bucket_part_index/CREATE_TABLE.md • introduction/StarRocks_intro.md • table_design/table_types/primary_key_table.md References • table_design/hybrid_table.md • table_design/table_types/table_capabilities.md
m
@Rocky [42000][5064] Getting analyzing error. Detail message: Can't change storage type.
r
The error 
[42000][5064] ... Can't change storage type
 occurs because you are attempting to change the 
store_type
property of an already existing table. In StarRocks, the 
store_type
(which determines if a table uses hybrid row-column storage) is a fundamental architectural property that must be specified at table creation time. You cannot currently convert an existing columnar table to a hybrid table (or vice versa) using an 
ALTER TABLE
statement. Why this is happening * Data Layout: Hybrid storage requires StarRocks to build a hidden binary column (
__row
) that packs all your non-key values together. This happens during the data ingestion/compaction phase. * Restriction: While StarRocks v3.2.4+ allows you to perform schema changes (like 
ADD COLUMN
or 
DROP COLUMN
) on a hybrid table, it does not allow you to flip the 
store_type
property itself once the table is defined. How to Resolve Since you cannot alter the existing table, you must perform a "Blue-Green" migration: 1. Create a New Table: Create a new table (e.g., 
requests_v2_new
) with the correct DDL, including the 
store_type
property and at least one non-key column.
Copy code
sql
    CREATE TABLE requests.requests_v2_new (
        received DATETIME NOT NULL,
        auction_id STRING NOT NULL,
        imp_key STRING NOT NULL,
        -- Non-key columns are required for store_type=column_with_row
        payload JSON
    )
    PRIMARY KEY (received, auction_id, imp_key)
    DISTRIBUTED BY HASH (auction_id) BUCKETS 128
    PROPERTIES (
        "store_type" = "column_with_row",
        "enable_persistent_index" = "true"
    );
2. Migrate Data: Use an 
INSERT INTO ... SELECT
to move data from your old table to the new one.
Copy code
sql
    INSERT INTO requests.requests_v2_new SELECT * FROM requests.requests_v2;
3. Swap Tables: Use the atomic 
ALTER TABLE ... SWAP WITH
command to replace the old table with the new one without downtime.
Copy code
sql
    ALTER TABLE requests.requests_v2 SWAP WITH requests.requests_v2_new;
Summary of Requirements for `column_with_row`: * Model: Must be a Primary Key table. * Cluster: Must be Shared-nothing (Shared-data is not supported). * Columns: Must have at least one non-key column. * Timing: Must be defined in the 
CREATE TABLE
statement. References • table_design/hybrid_table.md • sql-reference/sql-statements/table_bucket_part_index/ALTER_TABLE.md • administration/management/FE_configuration.md References • table_design/hybrid_table.md • administration/management/FE_configuration.md • knowledge/usage/best-practice.md • sql-reference/sql-statements/table_bucket_part_index/CREATE_TABLE.md • sql-reference/sql-statements/table_bucket_part_index/ALTER_TABLE.md
m
@Rocky Does reducing write amplification mean actual disk space could be lesser than data required? So using row hybrid storage could actually be more efficient and use less actual space than columnar storage
r
No, using hybrid row-column storage will actually increase your total disk space usage, not decrease it. The term "reducing write amplification" in the context of StarRocks hybrid storage refers to I/O efficiency and CPU cycles, not the physical footprint on disk. Here is the breakdown of why this happens and why the storage cost is higher: 1. Data is stored twice (Redundancy) When you enable 
store_type = column_with_row
, StarRocks does not replace the columnar data. Instead, it creates a hidden binary column called 
__row
. * Columnar Store: Data is still stored column-by-column for analytical performance. * Row Store: The same data is encoded into the 
__row
column. * Result: You are effectively storing your "value" columns twice. 2. Lower Compression Efficiency Columnar storage is highly efficient at compression because it groups similar data types together (e.g., a column of integers compresses much better than a mix of strings and numbers). * The 
__row
column is a binary blob containing different data types packed together. This "interleaved" data is much harder for compression algorithms to shrink. * Result: The row-store portion of the table will likely be larger than the columnar portion, even though they contain the same data. 3. What "Reducing Write Amplification" actually means here In a standard columnar Primary Key table with 500 columns, a partial update to just 1 column often requires the system to: 1. Read the old version of multiple columns to maintain the row's integrity. 2. Write out new versions of data files. 3. Perform heavy I/O during "compaction" to merge these changes. With hybrid storage, StarRocks can perform the update by mainly interacting with the single 
__row
column. This significantly reduces the Write I/O volume (the amount of data the disk heads have to move during the "Apply" phase) and CPU usage. * The Benefit: Your updates finish much faster, and the system can handle higher write concurrency. * The Cost: You "trade" disk space to get this performance. Summary Metric | Columnar Storage | Hybrid Storage Disk Usage | Lower (Optimized compression) | Higher (Stored twice + less compressible) Update Latency | High (for wide tables) | Low (Optimized for point writes) Point Lookup | Slower (Assembles row from files) | Fast (Fetches whole row in one I/O) Write Amplification | High I/O during updates | Low I/O during updates Recommendation: Only use 
column_with_row
if your 500-column table is suffering from high CPU/IO during updates or if you need sub-millisecond point lookups. If your primary concern is disk space, stick to the standard columnar format. References • table_design/hybrid_table.md • knowledge/usage/best-practice.md • introduction/StarRocks_intro.md • table_design/table_types/primary_key_table.md • administration/management/monitoring/metrics.md References • table_design/hybrid_table.md • knowledge/usage/best-practice.md • best_practices/primarykey_table.md
m
@Rocky Is there a formula for the FE merge commit load latency? I want to know what configuration I can tune to decrease this aside from my merge interval
r
There is an explicit internal formula used for monitoring Merge Commit latency, and several "hidden" configurations beyond the merge interval that impact it. The Merge Commit Latency Formula According to StarRocks monitoring metrics, the end-to-end latency for a merge commit request is calculated as follows: $$ \text{Total Latency} = \text{Pending} + \text{Wait Plan} + \text{Append Pipe} + \text{Wait Finish} $$ * Pending: Time spent in the BE execution queue before processing starts. * Wait Plan: Time spent waiting for the RPC response from the FE to obtain a load plan and for a stream load pipe to become available. * Append Pipe: Time spent actually writing data into the stream load pipe. * Wait Finish: Time spent waiting for the FE to commit the transaction and for the data to become visible (the "Publish" phase). ────────── Configurations to Tune (Aside from Interval) To decrease this latency, you should focus on the Wait Plan and Wait Finish components, which are governed by FE/BE coordination. 1. Synchronous vs. Asynchronous Publish (
enable_sync_publish
) By default, StarRocks waits for the data to be "applied" (making it searchable) before reporting success. * Parameter: 
enable_sync_publish
(FE Config) * Action: If set to 
false
, the load returns "Success" as soon as the commit is received, without waiting for the data to be queryable. This drastically reduces reported latency but creates a short lag between "Success" and data visibility. 2. Publish Task Frequency (
publish_version_interval_ms
) This controls how often the FE checks and issues the "Publish Version" tasks to the BEs. * Parameter: 
publish_version_interval_ms
(FE Config) * Action: Lowering this (default is 10ms) can make the transition from committed to visible slightly faster, though the default is already very aggressive. 3. Primary Key Apply Threads (
transaction_apply_worker_count
) If you are using Primary Key tables (common with merge commit), the "Apply" phase involves checking for updates/deletes. * Parameter: 
transaction_apply_worker_count
(BE Config) * Action: Increase this to match your CPU cores if your "Wait Finish" times are high. This speeds up the processing of the data once it reaches the BE. 4. Compaction Backpressure (
lake_ingest_slowdown_threshold
) If your cluster is under heavy write load, StarRocks will intentionally delay commits if the compaction score is too high (to prevent too many small files). * Sign: Check 
SHOW PROC '/transactions/<db_id>/running';
. If you see 
ErrMsg: Partition's compaction score is larger than 100.0, delay commit for xxxms
, the system is throttling you. * Tuning: Increase compaction threads (
compact_threads
or 
update_compaction_num_threads_per_disk
) to clear the backlog faster, rather than just increasing the threshold. 5. RPC & Thrift Timeouts (
txn_commit_rpc_timeout_ms
) If your "Wait Plan" is high, it could be due to FE lock contention or slow RPCs. * Parameter: 
txn_commit_rpc_timeout_ms
(BE Config) * Action: Ensure this is consistent with FE's 
thrift_client_timeout_ms
. If the FE is under heavy metadata load, increasing this prevents unnecessary retries/failures, though it doesn't "decrease" latency directly. Summary Checklist Component | Metric to watch | Config to Tune Wait Plan | 
merge_commit_wait_plan
| Check FE CPU/Lock contention Visibility Lag | 
starrocks_fe_txn_publish_latency_ms
| 
enable_sync_publish = false
Apply Speed | 
merge_commit_wait_finish
| 
transaction_apply_worker_count
(BE) Throttling | 
ErrMsg
in 
SHOW PROC
| Increase Compaction threads References • administration/management/BE_configuration.md • administration/management/FE_configuration.md
m
@Rocky Even though I have used merge commit async in shared nothing,, will 
enable_sync_publish
cause stream loads to return faster? I have issues where large payloads from my AP southeast servers are timing out due to latency between them and us east starrocks FE node
r
No, 
enable_sync_publish = false
will not make your Stream Loads return faster if you are already using 
merge_commit_async = true
. Here is why, and what you should tune instead to handle high-latency timeouts. 1. Why 
enable_sync_publish
won't help you In StarRocks, these two parameters control different phases of the load: * 
merge_commit_async = true
: This is the "fastest" path. The server returns a success response to your client as soon as it receives the data into its internal buffer. It does not wait for the transaction to be committed or for the data to be queryable. * 
enable_sync_publish = false
: This tells the FE to return success after the transaction is committed but before the data is "applied" (made visible). Since your 
merge_commit_async
mode already returns before the transaction even reaches the commit/publish phase, changing the publish setting will have zero impact on the response time your AP Southeast servers see. 2. Solving timeouts for Large Payloads over High Latency If your servers in AP Southeast are timing out while sending data to US East, the issue is likely that the network transfer time for the "large payload" is exceeding your configured timeout limits. A. Increase the 
timeout
Header (Primary Fix) By default, a Stream Load (and Merge Commit) has a timeout (often 600s). For high-latency cross-region uploads, the time it takes just to push the bytes over the wire can exceed this. * Action: Increase the 
timeout
parameter in your HTTP header.
Copy code
bash
    -H "timeout:1200"  # Increase to 1200 seconds (20 mins)
B. Check Client-Side Socket Timeouts Since you are crossing regions, the physical network latency might cause your HTTP client (e.g., Go, Java, or Curl) to drop the connection. * Action: Ensure your client-side library has a 
request_timeout
or 
socket_timeout
higher than the time it takes to upload your largest payload at your average cross-region upload speed. C. Reconsider "Merge Commit" for Large Payloads Merge Commit is specifically designed for high concurrency of small batches (KBs to a few MBs). * If you are sending "large payloads" (e.g., >100MB), the overhead of the Merge Commit buffering system can actually become a bottleneck. * Recommendation: If your individual batches are already large (e.g., >50MB), disable Merge Commit (
enable_merge_commit = false
). Standard Stream Load is highly optimized for large files and will be more stable for your cross-region scenario. D. Tune Internal RPC Timeouts (BE Config) If the FE/BE coordination is timing out internally due to the distance: * 
streaming_load_rpc_max_alive_time_sec
: Increase this on the BE if you see "RPC timeout" in the StarRocks logs. * 
txn_commit_rpc_timeout_ms
: Increase this on the BE to allow more time for the BE to talk to the FE over the high-latency link during the commit phase. Summary of Recommendations 1. *Don't change `enable_sync_publish`*; it won't help your client latency. 2. Increase the 
timeout
header in your Stream Load request to at least 1200. 3. Ensure your HTTP client (in AP Southeast) doesn't have a shorter timeout than the StarRocks server. 4. If payloads are >100MB, try disabling Merge Commit and use standard Stream Load. References • administration/management/FE_configuration.md • loading/StreamLoad.md • sql-reference/sql-statements/loading_unloading/STREAM_LOAD.md • administration/management/BE_configuration.md • faq/loading/Stream_load_faq.md References
m
@Rocky What happens when I am fixing my starrocks cluster (restoring dropped partition from backup while ingestion wants to write data to that partition)? How are the versions reconciled if replication_num = 1?
r
When you are restoring a dropped partition while ingestion is active, the behavior depends on whether you are using the 
RECOVER
command (from the internal Trash) or the 
RESTORE
command (from an external backup). In both cases, with 
replication_num = 1
, there is no secondary copy to fall back on, making the "Version" reconciliation very strict. Scenario 1: Using 
RECOVER PARTITION
(Internal Trash) If you dropped a partition and want to bring it back using the 
RECOVER
command: * The Conflict: If your ingestion system (e.g., Stream Load or Routine Load) has already triggered the auto-creation of a new partition with the exact same name, the 
RECOVER
command will fail. * Reconciliation: StarRocks cannot merge the "deleted" metadata with "newly created" metadata of the same name. You would have to drop the newly created partition (and lose that new data) before you can recover the old one. * Version: If successful, the partition returns with its original 
VisibleVersion
from before the drop. Scenario 2: Using 
RESTORE SNAPSHOT
(External Backup) If you are restoring a partition from a snapshot in a repository (S3/HDFS): * Locking: While a 
RESTORE
job is in the 
RUNNING
or 
COMMIT
phase, the table is locked. Any ingestion attempts to that specific table will typically fail with an error like 
Table is under restore
. * Overwriting: If the partition already exists (e.g., ingestion auto-created it), the 
RESTORE
job will overwrite the existing partition. * Data Loss: Any data ingested between the time the backup was taken and the time of the restore will be deleted and replaced by the snapshot data. * Version Reconciliation: StarRocks sets the 
VisibleVersion
of the restored partition to a value higher than any previous version in the current cluster to ensure metadata consistency. However, the content of the data is strictly what was in the snapshot. Why 
replication_num = 1
Matters With only one replica, there is no "majority" or "high-watermark" reconciliation between nodes. 1. No Consistency Check: StarRocks doesn't have to compare replicas to see which version is "correct." It simply trusts the metadata on the Frontends (FE). 2. Metadata Overwrite: When you restore, the FE metadata is updated to point to the new (restored) tablet files. Since there is only one replica, the system doesn't have to wait for other nodes to sync; the change is instantaneous but absolute. Summary Checklist for your Situation: 1. Check if a new partition exists: Run 
SHOW PARTITIONS FROM <table_name>
. If you see the partition name there already (created by ingestion), a 
RECOVER
will fail. 2. Stop Ingestion: It is highly recommended to pause ingestion to that table before starting a 
RESTORE
. If you don't, your ingestion jobs will likely fail during the restore process anyway due to table locks. 3. Manual Merge: If you need both the old data (from backup) and the new data (from active ingestion), you should restore the backup to a temporary table name, then use 
INSERT INTO ... SELECT
to merge the data manually. References • administration/Data_recovery.md • sql-reference/sql-statements/backup_restore/RESTORE.md • sql-reference/sql-statements/backup_restore/RECOVER.md • administration/management/Backup_and_restore.md • table_design/data_distribution/Data_distribution.md References • sql-reference/sql-statements/backup_restore/RECOVER.md
2 Views
Open in Slack
PreviousNext
Powered by