http://starrocks.io logo
Join Slack
Powered by
<@U0A71G31CDV> Documentation says column update mo...
# questions-and-troubleshooting
m
@Rocky Documentation says column update mode for partial updates is much faster than row mode. However, when I switched to column update mode, the metric measuring the apply duration compared to update rowset apply duration was climbing non-stop. My update rowset commit apply duration is only 2-6s but my column update one was climbing beyond 100s. Why is this the case?
r
Hi there! I'm Rocky, and I'd be happy to help you with your StarRocks performance question. The behavior you're seeing—where Column Update Mode shows a significantly higher and "climbing" apply duration compared to *Row Mode*—is actually a known characteristic of how these two modes handle different types of workloads. While the documentation mentions that column mode is "faster," that performance boost is highly specific to certain conditions. Here is why you are likely seeing the apply duration climb: 1. Batch Size is the Key Factor Column update mode is designed for large batches (many rows) and few columns. * Row Mode: Optimized for real-time, small-batch updates. It handles frequent, high-concurrency updates more efficiently by writing row-based deltas. * Column Mode: Optimized for batch processing (e.g., updating a few columns across millions of rows at once). If you are performing small, frequent updates (real-time streaming) in column mode, the "apply" overhead becomes massive. 2. High File Metadata and I/O Overhead The technical reason for the climbing duration in Column Mode is how StarRocks stores the updates: * In Row Mode: Each update batch typically generates a single rowset file per tablet. * In Column Mode: StarRocks generates separate column files for every updated column in the batch. * If you update 5 columns across 100 tablets, Column Mode creates 500 files per batch. * Row Mode would only create 100 files. For small batches, the overhead of creating, opening, and managing these hundreds of small files in the metadata/apply phase far outweighs the benefits of not rewriting the whole row. This leads to an apply backlog, causing the metric to climb non-stop as the system struggles to keep up with the file I/O and metadata operations. 3. When should you actually use Column Mode? You should only switch to Column Mode if your scenario meets these criteria: * Batch Size: You are updating a large number of rows per transaction (typically thousands or millions). * Column Count: You are updating a very small percentage of the total columns (e.g., updating 2 columns in a 100-column table). * Frequency: You are doing infrequent bulk updates rather than high-frequency real-time streaming. Recommendations 1. Switch back to Row Mode (or 
auto
): For real-time streaming or small-batch updates, Row Mode is significantly more stable and has much lower apply latency. 2. Increase Batch Size: If you must use Column Mode, try to accumulate more data on the application side and send larger, less frequent batches. 3. Check `partial_update_mode`: If you set this to 
column
manually via a system variable or load property, try resetting it to 
auto
. The 
auto
mode (introduced in v3.1) only triggers column mode if the update affects less than 30% of columns and fewer than 4 columns total, which acts as a safety guard. In summary: The "climbing" metric indicates that your ingestion rate is faster than the "Apply" rate in Column Mode due to the high per-batch overhead of small column files. Stick to Row Mode for real-time/small-batch scenarios! References • administration/management/monitoring/metrics.md • administration/management/BE_configuration.md
The behavior you're seeing—where the Apply duration for column update mode climbs non-stop while row mode remains stable—is typically caused by a mismatch between your data ingestion pattern and the storage architecture of Column Mode. While Column Mode is "faster" at writing (since it only writes the updated columns), it shifts a significant amount of complexity to the Apply phase and Metadata management. Why Column Mode Apply Duration Climbs In a Primary Key table, Column Mode partial updates use a mechanism called Delta Column Groups (DCG). Instead of rewriting the whole row, StarRocks writes the updated columns into new "delta" files and links them to the original data using DCG metadata. The "climbing duration" usually stems from these three factors: 1. Small Batch Size (Most Common Cause): Column Mode is highly optimized for large batches (e.g., updating 100k+ rows at once). If you are performing frequent, small updates (e.g., real-time streaming with 100-1000 rows per batch), you are creating a massive number of Delta Column Groups. Each "Apply" task must traverse and manage these existing DCGs. As the number of DCG versions grows, the time to "Apply" the next version increases. 2. Compaction Lag: These Delta Column Groups need to be merged (compacted) into the main data files to keep performance stable. If your compaction is not keeping up with the rate of small updates, the "version explosion" of DCGs makes every subsequent Apply task slower and slower. 3. Metadata Overhead during Apply: In Row Mode, the "Read-Merge-Write" happens before the commit. The Apply phase is just a quick update to the Primary Key index. In Column Mode, the Apply phase has to finalize the linking of delta columns and ensure consistency across these versions. If you have many segments and frequent updates, this metadata reconciliation becomes the bottleneck. How to Fix It 1. Re-evaluate Row Mode vs. Column Mode: * Row Mode: Best for real-time, small-batch updates (high frequency, few rows per batch). * Column Mode: Best for batch processing (lower frequency, many rows per batch, few columns updated). _ _If you are doing real-time streaming, Row Mode is almost always better for long-term stability.* 2. Increase Batch Size: If you want to stay on Column Mode, you must increase the batch size of your loads (or increase the 
max_batch_interval
if using Stream Load/Flink) to reduce the number of DCGs created per second. 3. Monitor DCG Metrics: Check your BE metrics to confirm if the DCG count is the issue: * `delta_column_group_get_total`: Shows how often the system is interacting with these delta groups. * `update_rowset_commit_apply_duration_us`: Total apply time. * `column_partial_update_apply_duration_us`: Specifically the time spent on column-mode apply logic. 4. Check BE Configurations: If you are on a recent version (v3.2.10+), check the following BE parameters in `be.conf`: * `partial_update_memory_limit_per_worker`: Ensure this isn't too small, causing frequent flushes during apply. * Ensure your Persistent Index is stored on SSD. If the index is on HDD, the lookup phase of the Apply process will be significantly slower, and this is magnified in Column Mode. Summary: If your apply duration is climbing "non-stop," it’s a sign of version explosion. Either batch your data into larger chunks or switch back to Row Mode, which handles small, frequent updates much more gracefully by merging them into full rows immediately. References • administration/management/monitoring/metrics.md • sql-reference/sql-statements/table_bucket_part_index/UPDATE.md
m
@Rocky Currently I'm using 8 r6in.8xlarge BE nodes (over provisioned probably) to ingest data into my PK table. Transaction apply latency about 3 second p95. Update rowset commit apply between 2 and 6s. I'm using row mode for column partial update so I can do sparse payloads by merging different events belongign to the same record to a single json object. Would transitioning to column partial update mode significantly save me money on infrastructure? I am flushing roughly 200k partial update events every 30 seconds with merge commit. TO my knowledge, I need to disable merge commit for column partial update mode. Given these numbers, is it worth to transition to schema based update payloads instead of sparse? What advantages are there if I am okay with the current data freshness
r
The reason you are seeing the Apply duration climb non-stop in Column Mode—despite it being "faster" in theory—is due to a phenomenon known as Delta Column Group (DCG) version explosion. While Column Mode is significantly more efficient for I/O (because it doesn't perform the "Read-Merge-Write" cycle of Row Mode), it introduces a different cost: metadata complexity during the Apply phase. 1. Why the Apply Duration is Climbing In Row Mode, when you update a record, StarRocks reads the entire old row, merges the new data, and writes a completely new full row. The "Apply" phase is simple: it just updates the Primary Key index to point to the new location. In Column Mode, StarRocks doesn't rewrite the whole row. It writes only the updated columns into a "delta file" and creates a Delta Column Group (DCG) to link this new column data to the original row. * The Problem: Every time you commit a batch in Column Mode, you create a new "version" of these links. * Version Explosion: If you have frequent updates (e.g., every 30 seconds) and the system isn't compacting these delta groups fast enough, the Apply process has to traverse an ever-growing chain of metadata to figure out where the "current" values for all columns are. * The Result: The more versions you have, the longer the Apply phase takes, leading to the "non-stop climbing" latency you observed. 2. Is it worth transitioning to Schema-based payloads? Moving from Sparse JSON payloads (handled in Row Mode) to Schema-based payloads (required for efficient Column Mode) is a significant architectural shift. The Advantages: * Massive Cost Savings: Your current setup (8 x 
r6in.8xlarge
) is indeed over-provisioned for 6,600 events/sec (200k/30s). The high node count is likely required to handle the high CPU/IO overhead of "Read-Merge-Write" in Row Mode. * IO Reduction: If you have a table with 100 columns and you only update 2, Column Mode uses ~98% less disk I/O than Row Mode. * Scale Potential: Column Mode allows you to handle much wider tables and higher total row counts without hitting the I/O wall. The Catch: Column Mode requires large batches to be efficient. If you disable Merge Commit and send small, frequent chunks, you will always hit the Apply latency climb. 3. Recommendation: Should you switch? Given your current P95 of 3s and 2-6s Apply duration, your system is "healthy" in Row Mode, but expensive. Stay with Row Mode (Sparse Payloads) if: * You prioritize freshness (keeping that 30s flush interval). * Your payloads are truly "sparse" and unpredictable (different fields every time). * The cost of 8 beefy nodes is acceptable for the simplicity of the JSON merge logic. Switch to Column Mode (Schema-based) if: * Cost reduction is a priority: You could likely cut your BE cluster size by 50-70% if you move to Column Mode with proper batching. * You can increase your batch size: If you are okay with a 2-5 minute freshness instead of 30 seconds, you can send 1M+ rows per batch. Large batches are the "antidote" to the Apply latency climb in Column Mode because they create fewer, larger DCG versions. * You want to use Merge Commit: You can use Merge Commit with Column Mode in recent versions (v3.1+). If you were told it's incompatible, check your specific version or connector (Flink/Stream Load). Merge Commit actually helps Column Mode by grouping those 200k events into a single transaction, preventing the version explosion. Summary Checklist for Column Mode Success: 1. Enable Merge Commit: Do not disable it; it is your best defense against metadata explosion. 2. Schema-based is better: Explicit columns are always faster to process than JSON parsing. 3. Check Compaction: If you switch, monitor the BE metric 
tablet_cumulative_compaction_num
to ensure DCGs are being merged.
m
@Rocky If I have a 390 column table and updates are very low frequency, is row or column partial update mode still desirable? What is the cost if I just default to partial update row mode for every event? If the pk index is not found, an insert is performed instead of update, does this contribute to performance bottleneck or is it fine?
r
With a 390-column table, the trade-offs between Row Mode and Column Mode become much more pronounced. Here is the breakdown of your specific situation: 1. Row vs. Column Mode for 390 Columns Even at "low frequency," the choice depends on how many columns you are updating per event. * Row Mode (Default): This uses a Read-Merge-Write (Delete + Insert) cycle. * The Cost: If you update only 5 columns, StarRocks must still read the existing 385 columns from disk, merge them in memory with your 5 new values, and write a brand-new 390-column row to a new segment. * IO Magnification: This is massive for a 390-col table. You are essentially doing ~80x more I/O than necessary if you only care about a few fields. * When to use: Only if you are updating most of those 390 columns at once, or if your update volume is so low that the I/O cost doesn't impact your cluster's health. * Column Mode: This writes only the updated columns into a delta file. * The Benefit: If you update 5 columns, you only write those 5 columns. This saves significant disk I/O and CPU time during the write phase. * The Risk: As discussed previously, it creates Delta Column Groups (DCG). If "low frequency" means you aren't creating thousands of versions per minute, Column Mode is likely the superior choice for a table this wide. 2. "Insert if not found" (Upsert) Cost You asked if performing an insert when a PK isn't found is a bottleneck. * Performance: It is not a bottleneck. This is the native "Upsert" behavior of the Primary Key engine. * How it works: StarRocks checks the Primary Key index (in memory or persistent on SSD). * If the key exists: It marks the old row as deleted and writes the new one (Row Mode) or creates a delta (Column Mode). * If the key does not exist: It simply writes the new data. * The "Cost" of Rows: The only "penalty" is that for a new insert, any column not in your payload gets a NULL or Default Value. If you later update those rows via partial updates, you incur the standard update costs described above. 3. The Danger of "Sparse Payloads" in Row Mode Using Row Mode for sparse JSON payloads (merging different events into one record) on a 390-column table is the most resource-expensive way to use StarRocks. * CPU: Every event requires parsing the JSON and merging it with a very wide row. * I/O: You are rewriting the entire 390-column row every single time an event arrives. At 200k events per 30 seconds (from your previous turn), your 8 nodes are likely spending most of their time just moving those 390 columns back and forth between disk and memory. 4. Recommendation: The "Row-Store" Alternative Since StarRocks v3.2.3+, there is a feature specifically for your use case: Row-Store (Hybrid Storage). If you create your table with 
row_store = true
, StarRocks stores an extra copy of the data in a row-oriented format. * Why this helps you: It makes the "Read" part of "Read-Merge-Write" (Row Mode) nearly instantaneous. Instead of scanning 390 separate column files to reconstruct the row for an update, it just pulls the single row block. * Trade-off: Your storage footprint will increase (roughly double), but your Apply Latency and CPU usage for Row Mode updates will drop significantly. Summary * Is Row Mode still desirable? Only if you use the Row-Store property (
"row_store" = "true"
) or if you are updating >30% of the columns. * Is Column Mode desirable? Yes, it will save you massive amounts of I/O, provided you batch your updates (e.g., via Merge Commit) to avoid the "climbing apply duration" issue. * Insert vs Update: Don't worry about the "insert if not found" logic; it is highly optimized. The bottleneck is the row width (390 columns) being rewritten during updates.
My advice: If you have the disk space, enable Row-Store on this table. It gives you the "sparse payload" flexibility you want while eliminating the performance penalty of wide-table partial updates. References • knowledge/usage/best-practice.md • sql-reference/sql-statements/table_bucket_part_index/UPDATE.md • loading/Load_to_Primary_Key_tables.md • faq/Sql_faq.md
m
@Rocky but if i care about data freshness, I need to flush batches within a minute, ideally every 30 seconds. So if my update events are very rare, then row mode wouldn't hurt? because less pk index lookups, low ingestion pressure
r
If you prioritize data freshness (30s flushes) and have sparse/unpredictable payloads, Row Mode is actually the safer and more predictable choice for your architecture, despite the theoretical "speed" of Column Mode. Here is the breakdown of the costs and behaviors you asked about: 1. Is Row Mode "safe" for 390 columns if updates are rare? Yes, but with an I/O tax. In Row Mode, StarRocks performs a "Read-Merge-Write." If you update 1 column out of 390: * The Read: StarRocks must read all 390 columns from disk for that row to reconstruct it in memory. * The Merge: It overwrites the 1 changed column. * The Write: It writes a brand new row with 390 columns to a new segment. If updates are truly "rare" (e.g., only 5% of your 200k events are updates), then this I/O magnification only happens for that 5%. The other 95% (new inserts) are written directly, which is very fast. Row Mode is desirable here because it doesn't suffer from the "Delta Column Group" explosion that caused your apply latency to climb in Column Mode. 2. The cost of defaulting to Row Mode for every event The primary "cost" is Write Amplification and CPU/Disk I/O: * Disk I/O: Your 8 nodes will spend significantly more time reading and writing large blocks of data. Since you have 390 columns, even a small update rate can generate high disk throughput. * Compaction Pressure: Because you are writing "full rows" every time you update, the background compaction process has to work harder to merge these large segments. If your disks are SSDs/NVMe (which they should be for 
r6in
instances), they can likely handle this, but it’s less "efficient" than Column Mode. 3. "Insert if not found" — Bottleneck or Fine? It is perfectly fine. In a Primary Key table, every write involves a PK index lookup: 1. Lookup: Check if the key exists in the Index (Memory or Persistent Index on SSD). 2. Not Found (Insert): StarRocks simply writes the new row. This is the fastest path. 3. Found (Update): StarRocks marks the old row as deleted and performs the Read-Merge-Write. The "Insert" path is not a bottleneck; in fact, it’s faster than the update path because it skips the "Read" and "Merge" steps. 4. Recommendation: How to optimize for 390 columns If you find that Row Mode is starting to strain your I/O or CPU due to the 390-column width, you have a "best of both worlds" feature available in StarRocks v3.2.3+: Enable Row-Store (Hybrid Storage): You can add 
"row_store" = "true"
to your table properties. * How it helps: It stores an extra copy of the data in a row-oriented format. When an update happens, the "Read" part of "Read-Merge-Write" is near-instant because it doesn't have to scan 390 separate column files. * Why use it: It solves the exact latency problem of wide-table partial updates while keeping the flexibility of Row Mode (sparse JSON payloads and high-frequency flushes). * The Cost: It will roughly double your disk storage usage for that table. Final Verdict If you are okay with your current 2-6s apply duration and your infrastructure cost, stick with Row Mode. It is much more robust for 30-second flushes. * If apply latency starts climbing again: It means your disk I/O is saturated by the 390-column rewrites. * The Fix: Don't go back to Column Mode (which will hit the metadata limit); instead, enable Row-Store to speed up the Row Mode updates. Note on Merge Commit: You mentioned disabling it for Column Mode. In Row Mode, always keep Merge Commit enabled. It allows StarRocks to group those 30 seconds of data into a single transaction, which is essential for maintaining the health of your Primary Key index and Persistent indexes. References • loading/Load_to_Primary_Key_tables.md
m
@Rocky Due to the nature of my distributed systems, I can sometimes have up to 8 nodes up at once. Since partial update column mode requires me disabling merge commit (shared nothing Primary Key table), wouldn't this lead to high compaction scores and rowsets? Column mode is only good when I have a single node aggregating all the updates and I can flush a million or so events very quickly?
r
Actually, you have hit on the exact technical trade-off that makes Primary Key (PK) table tuning tricky. There is a common misunderstanding about Merge Commit and Column Mode, and clearing it up will likely solve your "climbing apply latency" and infrastructure cost issues. 1. Merge Commit is COMPATIBLE with Column Mode You mentioned that you need to disable Merge Commit for Column Mode. This is actually incorrect. In modern StarRocks versions (v3.1+), Merge Commit and Column Mode partial updates are fully compatible and, in fact, highly recommended together. * The Problem with 8 Nodes + No Merge Commit: If your 8 nodes flush every 30 seconds independently, you are creating at least 16 transactions per minute (likely more if they use multiple subtasks). In a "Shared Nothing" architecture, every transaction creates a new Rowset. * Version Explosion: In Column Mode, every Rowset creates a Delta Column Group (DCG). If you have hundreds of small DCGs, the "Apply" phase has to check every single one to find the latest value for a column. This is exactly why your Apply latency starts climbing and never stops. * The Solution: Enabling Merge Commit allows StarRocks to take those 8 independent flushes and group them into a single transaction on the server side. Instead of 16 transactions/minute, you might only have 2. This drastically reduces the number of DCG versions and keeps Apply latency flat. 2. Is Column Mode worth the transition for 390 columns? For a table with 390 columns, the difference in infrastructure cost is massive. * Row Mode (Read-Merge-Write): Even if you update 1 column, StarRocks reads all 390, merges them, and writes all 390 back to disk. At 200k events/30s, your 8 
r6in.8xlarge
nodes are likely being hammered by I/O amplification. * Column Mode (Delta): StarRocks only writes the primary key and the specific column being updated. For 390 columns, this is a 99% reduction in disk I/O. * The Cost Savings: If you switch to Column Mode and enable Merge Commit, you could likely downsize from 8 nodes to 2 or 3, as the CPU and I/O load will drop significantly. 3. Sparse JSON Payloads vs. Schema-based You mentioned using Row Mode to handle "sparse payloads" (different events merging into one JSON). * The Limitation: Column Mode requires a fixed schema in your payload (you must specify which columns you are updating). You cannot send an arbitrary JSON object and have StarRocks "figure out" which of the 390 columns are inside. * The Trade-off: If your "sparse" events are predictable (e.g., Event A always updates cols 1-5, Event B updates cols 6-10), you should map them to columns. If the events are truly random and unpredictable, Column Mode becomes difficult to implement. 4. What if you stay with Row Mode? If you are okay with current freshness and want to keep sparse JSON payloads, you should enable Row-Store (Hybrid Storage):
Copy code
sql
ALTER TABLE your_table SET ("row_store" = "true");
* Why? In a 390-column table, Row Mode is slow because it has to "gather" 390 different column files to perform the merge. Enabling 
row_store
keeps a copy of the row in a single block, making the "Read" part of Read-Merge-Write nearly instant. This will stabilize your 2-6s apply latency and reduce CPU usage. Summary Recommendation Strategy | Best For | Impact on Costs Column Mode + Merge Commit | Wide tables (390 cols), fixed schema payloads, high throughput. | Highest savings. Can likely cut BE nodes by 60%+. Row Mode + Row-Store | Sparse/Random JSON payloads, high freshness needs. | Medium savings. Stabilizes latency but uses 2x disk space. Row Mode (Current) | Small tables, infrequent updates. | Expensive. High I/O and CPU overhead on wide tables.
Rocky's Advice: Transition to Column Mode and re-enable Merge Commit. Use a 30s or 60s 
merge_commit_interval_ms
. This prevents the "too many rowsets" error and the version explosion that previously broke your Column Mode performance. You will likely find that you no longer need 8 large BE nodes. References • sql-reference/sql-statements/table_bucket_part_index/UPDATE.md • sql-reference/System_variable.md • administration/management/monitoring/metrics.md • sql-reference/sql-statements/loading_unloading/BROKER_LOAD.md • loading/Load_to_Primary_Key_tables.md • loading/Spark-connector-starrocks.md • loading/Kafka-connector-starrocks.md • administration/management/BE_configuration.md
m
@Rocky What if my I/O is fine? I am not IO bound. Nowhere near limits. I can just add more EBS
@Rocky What if my I/O is fine? I am not IO bound. Nowhere near limits. I can just add more EBS
r
If you are not I/O bound and are willing to over-provision EBS, the primary "cost" of Row Mode shifts from disk throughput to CPU overhead and Apply Latency. Here is how those 390 columns affect your system when I/O is plenty: 1. The "CPU Tax" of Row Mode Even with infinite I/O, Row Mode requires the Backend (BE) to perform a Read-Merge-Write. For a 390-column table, this means: * Decoding: The BE must decompress and decode all 390 columns for that specific row from their columnar blocks. * Merging: It merges your updated fields in memory. * Encoding: It re-encodes and compresses all 390 columns back into a new rowset. * The Bottleneck: This cycle is heavily CPU-bound. On a 32-core 
r6in.8xlarge
, if you have many small updates, you will see high 
STW
(Stop-The-World) or 
Apply
thread usage. 2. Is Row Mode "Desirable" in this case? Yes, it is desirable for flexibility, but only if you use Row-Store. If you set 
"row_store" = "true"
, StarRocks stores an additional row-oriented copy of the data. * With Row-Store: The BE pulls the entire 390-column row in one single read without decoding 390 separate column files. This significantly reduces the CPU cycles spent on the "Read" part of Read-Merge-Write. * Without Row-Store: Row Mode on 390 columns is inefficient. The BE has to "hunt" through 390 different files just to update one field. This is where your "Apply Latency" will eventually spike, regardless of your EBS throughput. 3. "Insert if not found" (The "Upsert" Path) You asked if performing an insert when a PK is not found is a bottleneck: * It is not a bottleneck. In fact, it is the fastest possible path for a Primary Key table. * Why: If the key is not found, StarRocks skips the "Read" and "Merge" steps entirely. It just writes the new data to a new rowset. * Performance: There is no penalty for this "upsert" logic. The BE simply checks the PK Index (which is in memory or on SSD via Persistent Index) and decides whether to perform a full write (Insert) or a Read-Merge-Write (Update). 4. Why Column Mode failed you before You mentioned earlier that Column Mode's apply latency climbed. This usually happens for two reasons, even if I/O is fine: 1. Version Explosion: If you disabled Merge Commit, you created too many "Delta Column Groups" (DCGs). Each time StarRocks reads a row, it has to check every single DCG version to find the latest value. This is a Metadata/CPU bottleneck, not an I/O one. 2. Schema Rigidity: Column Mode cannot handle "sparse" random JSON keys; it requires you to map specific columns. Rocky's Recommendation Since you have a 390-column table and low update frequency, but care about freshness (30s flushes): 1. Stick with Row Mode. It is much easier to manage for your 30-second flush interval and allows for sparse payloads. 2. Enable Row-Store: This is non-negotiable for a table this wide. It will save you from the CPU bottleneck of decoding 390 columns during updates.
Copy code
sql
    ALTER TABLE your_table SET ("row_store" = "true");
3. Keep Merge Commit ON: Even in Row Mode, grouping your 8 nodes' writes into fewer transactions every 30 seconds will keep your Rowset count low and your Compaction health high. Summary: Since I/O isn't your bottleneck, your only enemy is CPU efficiency and Version count. Row Mode + Row-Store is the "premium" path that gives you the best performance for wide-table updates. References • knowledge/usage/best-practice.md (Section 29: Row-column hybrid storage) • table_design/table_types/primary_key_table.md • loading/Load_to_Primary_Key_tables.md • best_practices/primarykey_table.md References
If your updates are truly low frequency, then you are right: you will not hit the I/O limits of your EBS volumes, and Row Mode is perfectly safe. However, "desirability" isn't just about I/O. For a 390-column table, even if I/O is "free," there are three hidden costs to Row Mode that adding more EBS won't fix: 1. The CPU "Re-encoding" Tax In Row Mode, StarRocks does a Read-Merge-Write. Even if you only update 1 column: * The Cost: The BE must take the other 389 columns (which are compressed and encoded on disk) and decompress/decode them into memory, swap the 1 updated value, and then re-compress and re-encode all 390 columns into a new data segment. * The Bottleneck: This is a CPU-bound task. Re-encoding 390 columns for every update event is significantly more "expensive" in CPU cycles than simply writing a small delta file (Column Mode). If your CPU usage spikes during ingestion, this is why. 2. Compaction Overhead Even with "low frequency" updates, StarRocks eventually has to perform Compaction to merge old rowsets. * In Column Mode, the background compaction only needs to merge the small delta files. * In Row Mode, every update has already created a "full-width" row. Compaction now has to move massive 390-column files around. * The Result: You will see much higher Background CPU usage. Adding EBS adds "lanes to the highway," but the BE still has to do the heavy lifting of processing the cargo. 3. Apply Latency (Query vs. Write) You asked about the PK index lookup. The lookup itself is fast, but the "Apply" phase is where the 390 columns hurt you. * When a new batch arrives, StarRocks must "Apply" those changes to the Primary Key index. * In Row Mode, the system is busy writing massive full-row segments. If you have many concurrent queries, they may experience jitter or higher latency because the BE is occupied re-encoding those 390-column rows. What if the PK is not found? (The "Upsert" case) If the PK is not found and an Insert is performed: * Performance: This is actually the fastest possible path. An insert skips the "Read" and "Merge" steps. It just writes the data you sent. * The Bottleneck: It is not a performance bottleneck. In fact, Primary Key tables are designed to handle "Upsert" (Insert if missing, Update if exists) as their primary use case. Final Advice for 390 Columns If you insist on Row Mode (e.g., to keep the flexibility of sparse payloads) and your I/O is fine, you should still do one thing: Enable Row-Store.
Copy code
sql
-- For v3.2.3+ 
ALTER TABLE your_table SET ("store_type" = "column_with_row");
* Why? It stores a "short-cut" row-oriented copy of your 390 columns. * The Benefit: It eliminates the high CPU cost of scanning 390 separate column files just to do a single update. It makes the "Read" part of Read-Merge-Write nearly free. * The Cost: It will use more disk space (roughly double), but since you said you are happy to "just add more EBS," this is the perfect solution for you. It gives you Row Mode flexibility with the performance of a dedicated update engine. Summary: If updates are rare and I/O is high, Row Mode is "fine." But to keep your BE CPUs healthy and your queries fast on such a wide table, use Row-Store (Hybrid Storage). References • table_design/hybrid_table.md • loading/Load_to_Primary_Key_tables.md • knowledge/usage/best-practice.md • table_design/table_types/primary_key_table.md • best_practices/primarykey_table.md
m
@Rocky
Copy code
req.Header.Set("Expect", "100-continue")
req.Header.Set("format", "JSON")
req.Header.Set("label", label)
req.Header.Set("strip_outer_array", "true")
req.Header.Set("ignore_json_size", "true")
req.Header.Set("enable_merge_commit", "true")
req.Header.Set("merge_commit_async", "true")
req.Header.Set("merge_commit_interval_ms", "10000")
req.Header.Set("merge_commit_parallel", "8")
if options.partialUpdate {
    req.Header.Set("partial_update", "true")
    req.Header.Set("columns", strings.Join(options.columns, ","))
    if mode := strings.TrimSpace(options.partialUpdateMode); mode != "" {
       req.Header.Set("partial_update_mode", mode)
    }
}
These are my headers. mode is "column" for my win event. Why is starrocks_be_column_partial_update_apply_duration_us still 0
r
The reason your 
starrocks_be_column_partial_update_apply_duration_us
metric is 0 is likely due to a known limitation in how Merge Commit interacts with Column Mode partial updates. 1. Merge Commit Defaults to Row Mode In most current versions of StarRocks, when 
enable_merge_commit
is set to 
true
, the system prioritizes the merging of small, high-concurrency requests into a single transaction. This "merging" process is technically optimized for Row Mode (the "Read-Merge-Write" path). Even though you have set 
partial_update_mode
to 
column
, the internal "background" Stream Load triggered by the Merge Commit logic often defaults to (or falls back to) Row Mode. This happens because: * Column Mode requires the creation of special "Delta Column Group" files, which is a complex metadata operation. * Merge Commit is designed to alleviate the "Too many versions/rowsets" problem caused by frequent small updates—a problem most common in Row Mode scenarios. 2. Metric Scope The metric 
column_partial_update_apply_duration_us
specifically tracks the time spent in the Apply phase of a transaction that is using Column Mode. Since your requests are being merged and likely processed via the Row Mode path, the work is instead being recorded in: * 
starrocks_be_update_rowset_commit_apply_duration_us
(The general metric for Primary Key apply tasks). * 
starrocks_be_update_rowset_commit_apply_total
3. Asynchronous Behavior You have 
merge_commit_async
set to 
true
. This means your Go code receives a 
200 OK
as soon as the BE receives the data into its internal buffer. The actual "Apply" (where the metric would be updated) doesn't happen until: 1. The 
merge_commit_interval_ms
(10 seconds) expires. 2. The merged transaction is actually committed to the storage engine. If you are checking the metrics immediately after the request, they will be 0. However, if they remain 0 even after minutes of ingestion, it confirms the system is not using the Column Mode path. 4. Compatibility Warning There is a specific compatibility note in the documentation regarding Merge Commit: * Homogeneous Requests: Merge Commit only works if all requests are identical. If even one request in the 10-second window misses a header or has a different column list, the merge may fail or behavior may become undefined. * Merge Condition: In several versions (including 3.3.x and some 3.4.x), 
merge_condition
(Conditional Update) is explicitly not supported with Merge Commit. While 
partial_update_mode
is a different header, it often shares similar internal constraints in the transaction coordinator. Recommendation 1. Check the general metric: Look at 
starrocks_be_update_rowset_commit_apply_duration_us
. If this is increasing, your data is being loaded, but StarRocks has decided to use Row Mode for the merged transaction. 2. Verify Version: If you are on a version earlier than 3.3, Column Mode and Merge Commit have very limited compatibility. 3. Evaluate the need for Merge Commit: You mentioned earlier that your updates are "rare." If your total transaction rate (across all 8 nodes) is less than ~10-20 per minute, you might not even need Merge Commit. Disabling it would allow the 
column
mode to take effect and would likely populate your missing metric. metric. References • loading/Load_to_Primary_Key_tables.md • sql-reference/sql-statements/table_bucket_part_index/UPDATE.md • loading/Flink-connector-starrocks.md
m
@Rocky I have many different columns to update, naturally the columns header will not be homogenous in the 10 second interval. Does that mean merge commit is not possible with column partial update mode?
r
To answer your question directly: Yes, the lack of homogeneity in the 
columns
header is a blocker for Merge Commit. In StarRocks, the Merge Commit feature works by grouping incoming Stream Load requests into "buckets" based on their header parameters. Only requests with identical headers (same columns, same format, same timeout, etc.) are merged into a single transaction. Why this breaks your setup 1. Header Grouping: If you send 100 requests in 10 seconds and each has a different 
columns
list (because the events update different fields), StarRocks will create 100 separate transactions. 2. No Merging: Since no two requests "match," the Merge Commit logic has nothing to merge. Each request is processed as its own individual transaction, leading to the high rowset/version count you were trying to avoid. 3. Why the metric is 0: This is the smoking gun. When 
enable_merge_commit
is 
true
, the BE uses a specific internal pipeline. In many current versions, the Merge Commit pipeline only supports Row Mode for partial updates. Even if you specify 
partial_update_mode=column
, the system often silently falls back to Row Mode because the internal "merger" is not yet optimized to consolidate disparate Delta Column Groups (DCGs) from different column sets. The Conflict of Requirements You have 390 columns and "sparse" updates. You are facing a fundamental trade-off in StarRocks' current architecture: Goal | Requirement | Conflict Merge Commit | Identical 
columns
header. | Your events update different columns. Column Mode | Low I/O, fast for wide tables. | Requires fixed 
columns
list; hard to "merge" heterogeneous sets. Sparse Updates | Random JSON keys. | Column Mode requires explicitly naming the target columns. How to resolve this If you cannot make your column sets homogeneous, you have two real paths forward: Option A: The "Row-Store" Hybrid Path (Recommended for Sparse/Random Updates) Since you mentioned you are not I/O bound and can add EBS, this is the most stable path for 390 columns: 1. Disable Column Mode (
partial_update_mode=row
or just leave default). 2. Enable Merge Commit (This will now work correctly to group your transactions, provided you can standardize other headers). 3. Critical Performance Fix: Alter your table to use Hybrid Storage (Row-Store):
Copy code
sql
    ALTER TABLE your_table SET ("store_type" = "column_with_row");
* Why? This allows the "Row Mode" partial update to find the existing 390-column row instantly in the row-store, instead of having to scan 390 separate column files. This solves the "apply latency" and "high CPU" issues of Row Mode without requiring you to standardize your 
columns
header. Option B: Grouping by "Event Type" If your 390 columns fall into predictable groups (e.g., "User Profile Update" always hits cols 1-10, "User Activity" always hits 11-20): 1. Standardize your Go code to always send the exact same column list for each event type. 2. Merge Commit will then at least merge all "User Profile" updates together and all "User Activity" updates together. You'll have 2-5 transactions per window instead of hundreds. Summary The 
starrocks_be_column_partial_update_apply_duration_us
is 0 because your heterogeneous headers are preventing the merge from occurring, and/or the Merge Commit logic is falling back to Row Mode. To handle 390 columns efficiently with random sparse updates, *don't use Column Mode*—use Row Mode + Row-Store. Itself (Hybrid Storage)**. References • sql-reference/sql-statements/table_bucket_part_index/UPDATE.md • administration/management/monitoring/metrics.md • loading/Load_to_Primary_Key_tables.md
m
@Rocky Would setting null values work? So I can make my column updates homogenous. I simply define every JSON payload with homogenous fields with zero equivalent. So every payload starts off blank slate with null (or 0 if it is a binary value) and incoming updates will change this. Then sparse payload becomes homogenous
r
Unfortunately, this will not work as you expect. Sending 
NULL
or a "zero equivalent" will result in your existing data being overwritten by those `NULL`s or zeros. In StarRocks, the 
partial_update
logic follows these rules: 1. The "Overwrite" Rule: If a column is included in your 
columns
header, StarRocks assumes you want to update it. If the value in the JSON/CSV is 
NULL
, StarRocks will write 
NULL
to that field in the table, effectively deleting your old data. 2. The "Skip" Rule: The only way to tell StarRocks "do not touch this column" is to omit it entirely from both the 
columns
header and the data payload. Why "Homogenous Blank Slates" fail: * Data Loss: If you send all 390 columns in the header to make it "homogenous" but fill 380 of them with 
NULL
, you will end up with a table full of 
NULL
values. * Performance Hit: Even if you sent the "old" values back to avoid data loss, you would be performing a 390-column update. Column Mode is only fast because it writes tiny "delta" files for 2-3 columns. If you write 390 columns every time, you lose the Column Mode advantage entirely—it becomes slower than Row Mode because it has to open and write to 390 separate files on disk for every batch. Is there a workaround? You cannot use 
ifnull()
in the 
columns
header to "keep the old value" because the mapping functions in Stream Load can only see the incoming data, not the existing data sitting on the disk. The Realistic Solution Since you have a wide table (390 columns) and sparse updates (different columns every time), you are the "textbook" use case for Hybrid Storage (Row-Store). 1. Give up on Column Mode: It is the wrong tool for "sparse random updates" across 390 columns. 2. Standardize your Headers into "Types": Instead of 1 header or 1,000 headers, try to group your Go logic into 5-10 "Update Templates" (e.g., 
TemplateA
always updates cols 1-20, 
TemplateB
always updates 21-40). * Merge Commit will then group all 
TemplateA
requests together and all 
TemplateB
requests together. You'll get much better batching. 3. Enable Row-Store:
Copy code
sql
    ALTER TABLE your_table SET ("store_type" = "column_with_row");
This allows StarRocks to use Row Mode partial updates (which handles sparse columns perfectly) without the CPU penalty of reading 390 separate files. It makes the "Read-Merge-Write" extremely fast by looking up the whole row in one I/O operation. Summary: Do not send `NULL`s to make headers match; you will wipe your data. Instead, reduce the variety of your column sets and use Row-Store to make the resulting Row Mode updates performant. References • table_design/hybrid_table.md • loading/Load_to_Primary_Key_tables.md • sql-reference/sql-statements/loading_unloading/STREAM_LOAD.md • loading/StreamLoad.md • sql-reference/sql-statements/table_bucket_part_index/UPDATE.md References • loading/Load_to_Primary_Key_tables.md • sql-reference/sql-statements/table_bucket_part_index/UPDATE.md • sql-reference/sql-statements/loading_unloading/BROKER_LOAD.md • sql-reference/System_variable.md • loading/loading_introduction/feature-support-loading-and-unloading.md
m
@Rocky Should I still give up on column mode if i can group my sparse updates into different schemas? So within the merge commit interval I will group my updates into homogenous payload and column headers, but they will be mixed homogenous. This means 10 payloads can update "win" column, another 10 can update "click" column. Or is this not possible?
r
Yes, if you can group your sparse updates into a few consistent "templates," you can make Column Mode work with Merge Commit, but it may still not be the best architectural choice for a 390-column table. Here is how the system will behave if you group your updates: 1. How Merge Commit Handles Multiple Templates StarRocks creates internal "buckets" for each unique set of headers. If you send two types of requests in a 10-second window: * Group A (Updates "win"): 10 requests. * Group B (Updates "click"): 10 requests. Merge Commit will generate two separate transactions at the end of the 10-second interval—one merging the "win" data and one merging the "click" data. * The Good: You’ve reduced 20 transactions down to 2. * The Bad: If you have 50 different column-set combinations, you still get 50 transactions every 10 seconds, which leads to the "too many rowsets/versions" error. 2. The "Delta Column Group" Risk In Column Mode, every time you update a set of columns, StarRocks creates a Delta Column Group (DCG) file. * If you only ever update the same 2-3 columns, it's very efficient. * If your "sparse" updates hit many different combinations of your 390 columns, the system has to manage a massive amount of DCG metadata. This can significantly slow down compaction and background maintenance. 3. Why your metric was 0 (The "Auto" fallback) If you set 
partial_update_mode
to 
column
and the metric was 0, it is likely because the internal Execution Planner decided your batch was too small or the column count was too high for Column Mode to be efficient, and it silently fell back to Row Mode. * Column Mode has an internal cost model. For very small batches (mini-batches), the overhead of creating new column files is higher than just rewriting the row. * Merge Commit is specifically designed to help Row Mode by turning many small updates into one larger Row Mode update. Comparison: Grouped Column Mode vs. Row-Store Given your 390 columns, here is the comparison of your two best options: Feature | Grouped Column Mode | Row Mode + Row-Store (
column_with_row
) Logic Complexity | High: Your Go code must bucketize and wait. | Low: Send JSON with any random keys; SR handles it. Merge Commit | Only works within each bucket. | Works across all requests (if headers match). I/O Efficiency | Best if you only update 1-2 columns. | Best for "sparse" updates across many columns. Reliability | Risk of "Too many versions" if templates vary. | Extremely stable for high-concurrency small updates. Storage | Standard columnar storage. | ~2x storage (due to the extra 
__row
column). Final Recommendation If you can group your 390 columns into 3–5 fixed templates (e.g., "Marketing Stats", "User Identity", "System Flags") and each template consistently gets enough data to justify a batch, Column Mode is viable. However, if your updates are truly random across those 390 columns, give up on Column Mode. Use Row Mode + Row-Store. It is designed specifically for "wide tables with sparse updates." It will populate the general 
update_rowset_commit_apply_duration
metrics and provide the most stable performance without requiring you to play "header-matching" games in your Go application.
Copy code
sql
-- To switch to the Row-Store optimized path:
ALTER TABLE your_table SET ("store_type" = "column_with_row");
References • loading/Load_to_Primary_Key_tables.md • sql-reference/sql-statements/table_bucket_part_index/UPDATE.md • table_design/hybrid_table.md • loading/StreamLoad.md References
m
@Rocky What happens if I do stream load without partial update specified and there is a primary key collision
r
If you perform a Stream Load on a Primary Key table without specifying 
partial_update:true
, StarRocks defaults to an UPSERT (full-row replacement) operation. Here is exactly what happens when a primary key collision occurs: 1. Full Row Overwrite: The existing row in the table is completely replaced by the new incoming row. 2. Default Values for Missing Columns: If your incoming data payload (CSV/JSON) does not include all columns of the table, the columns you left out will be overwritten with their default values (or 
NULL
if no default is defined). _ _Example:* If your table has columns 
A, B, C
and your load only provides 
A, B
, the existing value for 
C
in the database will be lost and replaced by `C`'s default value. 3. Performance: This is a standard Primary Key write. It is generally faster than a 
partial_update
because StarRocks doesn't have to perform a "read-merge-write" or manage delta column groups; it just marks the old row as deleted and appends the new version. Summary Table: Primary Key Collision Feature | 
partial_update: false
(Default) | 
partial_update: true
 Logic | UPSERT (Replace entire row) | Partial Update (Update specific columns) Missing Columns | Replaced with Default/NULL | Kept as they are in the DB Use Case | Replacing the whole object | Updating a few fields (e.g., just a "status" flag) Warning: If you are doing "sparse" updates (only sending changed fields) and you forget to set 
partial_update: true
, you will accidentally wipe out the data in the columns you didn't send. References • loading/Load_to_Primary_Key_tables.md • sql-reference/sql-statements/table_bucket_part_index/auto_increment.md • loading/Flink-connector-starrocks.md
m
@Rocky Does StarRocks default values apply on both insertion and partial updates? Or only partial updates?
r
Default values behave differently depending on whether you are performing a Full Upsert (the default) or a Partial Update, and whether the row already exists. 1. Insertion (New Rows) * Applies to: Both Full Upserts and Partial Updates. * If you load a row with a Primary Key that does not yet exist, StarRocks will treat it as a fresh insertion. Any columns defined in the table schema but missing from your JSON/CSV payload will be filled with their Default Values. 2. Full Upserts / Standard Load (Existing Rows) * Applies to: 
partial_update: false
(Default setting). * If you load a row where the Primary Key already exists, StarRocks performs a "Replace" operation. * If your payload is missing a column (e.g., your table has 
A, B, C
but you only send 
A, B
), the value for 
C
in the database will be overwritten by the Default Value. * Warning: This is often how users accidentally lose data—by sending an "update" without the 
partial_update
flag, effectively resetting unspecified columns to their defaults. 3. Partial Updates (Existing Rows) * Applies to: 
partial_update: true
. * If the Primary Key already exists, StarRocks does not use the Default Value for missing columns. * Instead, it preserves the Existing Value currently stored in the database for any columns not mentioned in your 
columns
header. * This is the core purpose of Partial Updates: to change only specific fields while leaving the rest of the row untouched. Summary Comparison Table Scenario | Column Missing from Payload | 
partial_update: false
(Upsert) | 
partial_update: true
(Partial) New Row | 
Col_C
(Default: 0) | Result: 
Col_C = 0
| Result: 
Col_C = 0
Existing Row | 
Col_C
(Existing: 99) | Result: 
Col_C = 0
| Result: 
Col_C = 99
 One Exception: Explicit `NULL`s If you include a column in your 
columns
header and explicitly pass a 
null
value in your JSON, StarRocks will try to write 
NULL
to that column. * If the column is 
NOT NULL
, the load may fail or use the default depending on your 
sql_mode
and load settings. _ If you want the "Default Value" to trigger during a load for a column that _is* included in the header, you usually have to omit the value in CSV (e.g., 
,,
) or ensure the mapping logic treats it as empty. Recommendation: If you want to "blank slate" a column back to its default value during a partial update, you cannot do it by simply omitting the column. You would need to explicitly send the default value in your JSON payload. References • loading/Load_to_Primary_Key_tables.md • sql-reference/sql-statements/table_bucket_part_index/auto_increment.md • faq/Others.md • sql-reference/sql-statements/table_bucket_part_index/CREATE_TABLE.md • sql-reference/sql-statements/loading_unloading/INSERT.md
m
@Rocky
Copy code
CREATE TABLE IF NOT EXISTS events.events (
		received DATETIME NOT NULL,
		auction_id STRING NOT NULL,
		imp_key STRING NOT NULL,
        stats_page_time_1s TINYINT NULL DEFAULT "0"
	)
	PRIMARY KEY (received, auction_id, imp_key)
	PARTITION BY date_trunc('hour', received)
	DISTRIBUTED BY HASH (auction_id) BUCKETS 128
	ORDER BY (received, auction_id)
	PROPERTIES (
		"enable_persistent_index" = "true",
		"partition_live_number" = "24",
		"replication_num" = "1"
	)
I tested this payload on the below table
Copy code
curl --location-trusted -X PUT \\
  '<http://starrocks-core-services-fe-1.us-east-1.stackadapt:8030/api/events/events/_stream_load>' \
  -H 'Authorization: Basic cm9vdDo=' \
  -H 'Expect: 100-continue' \
  -H 'format: JSON' \
  -H 'label: test-partial-update-001' \
  -H 'strip_outer_array: true' \
  -H 'ignore_json_size: true' \
  -H 'partial_update: true' \
  -H 'columns: received,auction_id,imp_key,stats_page_time_1s' \
  -d '[{"received":"2026-05-12 10:00:00","auction_id":"abc123","imp_key":"key1"}]'
{
    "TxnId": 2927112,
    "Label": "test-partial-update-001",
    "Db": "events",
    "Table": "events",
    "Status": "Fail",
    "Message": "too many filtered rows",
    "NumberTotalRows": 1,
    "NumberLoadedRows": 0,
    "NumberFilteredRows": 1,
    "NumberUnselectedRows": 0,
    "LoadBytes": 75,
    "LoadTimeMs": 16,
    "BeginTxnTimeMs": 0,
    "StreamLoadPlanTimeMs": 1,
    "ReadDataTimeMs": 0,
    "WriteDataTimeMs": 13,
    "CommitAndPublishTimeMs": 0,
    "ErrorURL": "<http://172.30.81.176:8040/api/_load_error_log?file=error_log_c0436e80c12c6695_27b198296eb0aab5>"
}  ~  $  curl /api/_load_error_log?file=error_log_c0436e80c12c6695_27b198296eb0aab5
Error: NULL value in non-nullable column 'stats_page_time_1s'. Row: [2026-05-12 10:00:00, 'abc123', 'key1', NULL, 0]
Default is not working on stream load?
r
The reason your load is failing with a "non-nullable" error is because of how the 
columns
header interacts with the JSON parser and the Partial Update engine. 1. Why it's failing When you specify a column in the 
columns
header (
-H 'columns: ..., stats_page_time_1s'
) but omit that key from your JSON payload, StarRocks' Stream Load behavior is as follows: 1. The loader identifies that 
stats_page_time_1s
is part of the desired mapping. 2. Because the key is missing in the JSON data, the loader assigns a 
NULL
 value to that field. 3. Because you have 
partial_update: true
set, the engine interprets this as: _"The user explicitly wants to update 
stats_page_time_1s
to 
NULL
."_ 4. The Error: Even though your DDL says 
NULL
(nullable), the error message 
NULL value in non-nullable column
strongly suggests that the actual table in your database was created as 
NOT NULL
, or you are hitting a constraint check where the system expects a value because you explicitly included it in the header. 2. How to make Default Values work To allow the Default Value (for new rows) or the Existing Value (for existing rows) to apply during a partial update, you must omit the column from the 
columns
header entirely. Corrected Curl: Remove the column you aren't sending from the 
-H 'columns: ...'
list.
Copy code
bash
curl --location-trusted -X PUT \
  'http://<fe_host>:8030/api/events/events/_stream_load' \
  -H 'Authorization: Basic cm9vdDo=' \
  -H 'format: JSON' \
  -H 'partial_update: true' \
  -H 'strip_outer_array: true' \
  --H 'columns: received, auction_id, imp_key' \
  -d '[{"received":"2026-05-12 10:00:00","auction_id":"abc123","imp_key":"key1"}]'
3. The Rules of Defaults in Partial Updates * If a column is NOT in the 
columns
header: * New Row: StarRocks will use the 
DEFAULT
value (in your case, 
0
). * Existing Row: StarRocks will keep the value already stored in the DB. * If a column IS in the 
columns
header: * StarRocks expects data. If the data is missing in the JSON, it sends 
NULL
. * If the row is new, it tries to insert 
NULL
(ignoring the default). * If the row exists, it tries to overwrite the existing value with 
NULL
. Recommended Checks 1. Verify Nullability: Run 
DESC events.events;
in your SQL client. Ensure 
stats_page_time_1s
is actually 
Null: Yes
. If it says 
No
, your DDL did not apply as expected. 2. Remove the Header: If you are sending JSON where the keys match your table column names, you often don't need the 
columns
header at all. If you remove it, StarRocks will automatically map only the keys present in the JSON and correctly trigger the default/existing value logic for everything else. References • loading/Load_to_Primary_Key_tables.md • loading/StreamLoad.md • sql-reference/sql-statements/loading_unloading/STREAM_LOAD.md • sql-reference/sql-statements/table_bucket_part_index/CREATE_TABLE.md References • loading/StreamLoad.md • loading/Load_to_Primary_Key_tables.md • sql-reference/sql-statements/loading_unloading/STREAM_LOAD.md • loading/Flink-connector-starrocks.md
m
@Rocky What if my payload is sparse, if it is present update to given value but if not, use defaults? I wanted to use this mechanism to minimize stream load payload size
r
To achieve a "sparse" payload where missing fields revert to their default values (even during a primary key collision), you should use the Full Upsert mode (which is the default) rather than Partial Update mode. Here is how to handle the two different ways "sparse" data can occur: Scenario A: The column is missing from the entire batch If your goal is to minimize the payload and header size because you know certain columns aren't needed for this entire load, simply omit the column from the 
columns
header and do not use 
partial_update: true
. * Logic: StarRocks will perform a full row replacement. Since the column is not in the mapping, it will use the 
DEFAULT
value defined in your DDL for every row in the batch. * Curl Example:
Copy code
bash
    # Note: NO partial_update header, and stats_page_time_1s is removed from 'columns'
    curl --location-trusted -X PUT \
      -H "format: JSON" \
      -H "columns: received, auction_id, imp_key" \
      -d '[{"received":"2026-05-12 10:00:00","auction_id":"abc123","imp_key":"key1"}]' \
      '<http://fe_host:8030/api/events/events/_stream_load>'
_Result_: If the PK exists, the row is replaced. 
stats_page_time_1s
becomes 
0
(your default). ────────── Scenario B: The column is present in some rows, but missing in others If your JSON array contains a mix (e.g., Row 1 has 
stats_page_time_1s
, Row 2 does not), you must use the 
ifnull
function in your 
columns
header to prevent the 
NULL value in non-nullable column
error. When a key is missing from a JSON object, StarRocks assigns it a 
NULL
value during parsing. By using 
ifnull
, you can catch that 
NULL
and redirect it to your desired default. * Curl Example:
Copy code
bash
    curl --location-trusted -X PUT \
      -H "format: JSON" \
      -H "strip_outer_array: true" \
      -H "columns: received, auction_id, imp_key, temp_val, stats_page_time_1s = ifnull(temp_val, 0)" \
      -d '[
        {"received":"2026-05-12 10:00:00","auction_id":"id_1","imp_key":"k1","temp_val":10},
        {"received":"2026-05-12 10:00:00","auction_id":"id_2","imp_key":"k2"}
      ]' \
      '<http://fe_host:8030/api/events/events/_stream_load>'
* Row 1: 
stats_page_time_1s
becomes 
10
. * Row 2: 
temp_val
is missing (parsed as 
NULL
), so 
ifnull
sets 
stats_page_time_1s
to 
0
. ────────── Why 
partial_update: true
is likely NOT what you want If you use `partial_update: true`: 1. New Rows: Missing columns get the Default Value. 2. Existing Rows: Missing columns keep their current value in the database. Since you specifically asked to "use defaults" when the value is missing (implying you want to reset the value to 0 if it's not provided), the standard Full Upsert (Scenario A or B above) is the correct mechanism. Summary of Column Mapping Header Used? | JSON Key Present? | Value Loaded No in 
columns
| N/A | Table Default Yes in 
columns
| Yes | JSON Value Yes in 
columns
| No | 
NULL
 (triggers error if 
NOT NULL
) Yes (w/ 
ifnull
) | No | Your specified default Recommendation: For the smallest payload and header, use Scenario A. Just define the 
columns
you are sending and let StarRocks fill the rest with DDL defaults. References • loading/Load_to_Primary_Key_tables.md • loading/StreamLoad.md • sql-reference/sql-statements/loading_unloading/STREAM_LOAD.md • faq/loading/Stream_load_faq.md References • loading/Load_to_Primary_Key_tables.md
m
@Rocky I need partial_update because in a future flush, data can belong to same record, i do not want last write wins semantics
r
To achieve your goal—where missing fields in your sparse payload use defaults for new records and *keep existing values for existing records*—you must change how you use the 
columns
header. The Problem in Your Test The error 
NULL value in non-nullable column
occurred because you explicitly listed 
stats_page_time_1s
in the 
-H 'columns: ...'
header but omitted it from the JSON object. 1. The Header Rule: If a column is in the 
columns
header, the JSON parser expects to find it. If it’s missing from the JSON key-value pairs, the parser assigns it a 
NULL
 value. 2. The Partial Update Rule: With 
partial_update: true
, an explicit 
NULL
from the parser is treated as an instruction to overwrite the column with 
NULL
. 3. The Result: Since your table (or the engine's check) expects a value, it rejects the 
NULL
. The Solution: Use "Simple Mode" For JSON payloads, the best way to handle sparse data is to remove the 
columns
header entirely. This is known as "Simple Mode." In this mode, StarRocks maps keys to columns automatically. If a key is missing from a specific JSON object: * New Row: StarRocks applies the DDL Default Value (e.g., 
0
). * Existing Row: StarRocks preserves the current value in the database. Corrected Curl Command
Copy code
bash
curl --location-trusted -X PUT \
  '<http://starrocks-fe:8030/api/events/events/_stream_load>' \
  -H 'Authorization: Basic cm9vdDo=' \
  -H 'format: JSON' \
  -H 'strip_outer_array: true' \
  -H 'partial_update: true' \
  -d '[{"received":"2026-05-12 10:00:00","auction_id":"abc123","imp_key":"key1"}]'
Why this works for your use case: 1. Minimizes Payload: You only send the fields you have. 2. Defaulting: If 
auction_id: abc123
is a brand new record, 
stats_page_time_1s
will automatically become 
0
. 3. No "Last Write Wins": If you later send a second load with 
{"auction_id":"abc123", "stats_page_time_1s": 5}
, it will only update that one column and leave the rest of the previously loaded data intact. Important Summary Load Configuration | Key Missing in JSON | Result for New Row | Result for Existing Row With 
columns
header | 
Col_A
is in header | 
NULL
 (Error if NOT NULL) | 
NULL
 (Overwrites data) Without 
columns
header | 
Col_A
is omitted | 
DEFAULT
 value | Keeps Existing value Note: If you absolutely must use a 
columns
header (e.g., for functional mapping like 
received=upper(received)
), you should only include the columns that are guaranteed to be in every JSON object of that batch. Any "sparse" optional columns should be left out of the header to trigger the default/preserve logic. References • loading/Load_to_Primary_Key_tables.md • loading/StreamLoad.md • sql-reference/sql-statements/loading_unloading/STREAM_LOAD.md • sql-reference/sql-statements/table_bucket_part_index/auto_increment.md References • sql-reference/sql-statements/loading_unloading/STREAM_LOAD.md • faq/loading/Stream_load_faq.md
m
@Rocky
Copy code
curl --location-trusted -X PUT \
  '<http://starrocks-core-services-fe-1.us-east-1.stackadapt:8030/api/events/events/_stream_load>' \
  -H 'Authorization: Basic cm9vdDo=' \
  -H 'Expect: 100-continue' \
  -H 'format: JSON' \
  -H 'label: test-partial-update-005' \
  -H 'strip_outer_array: true' \
  -H 'ignore_json_size: true' \
  -H 'partial_update: true' \
  -d '[{"received":"2026-05-12 10:00:00","auction_id":"abc123","imp_key":"key2"}]'
{
    "TxnId": 2927259,
    "Label": "test-partial-update-005",
    "Db": "events",
    "Table": "events",
    "Status": "Fail",
    "Message": "key column received not in partial update columns",
    "NumberTotalRows": 0,
    "NumberLoadedRows": 0,
    "NumberFilteredRows": 0,
    "NumberUnselectedRows": 0,
    "LoadBytes": 0,
    "LoadTimeMs": 0,
    "BeginTxnTimeMs": 0,
    "StreamLoadPlanTimeMs": 0,
    "ReadDataTimeMs": 0,
    "WriteDataTimeMs": 0,
    "CommitAndPublishTimeMs": 0
}
r
The error 
"key column received not in partial update columns"
occurred because when you enable 
partial_update: true
, StarRocks requires you to explicitly list the columns to be updated in the 
columns
header, and this list must include all primary key columns. When you removed the 
columns
header in your last attempt, the system was unable to identify which columns were part of the update set, leading to the failure. To achieve a sparse payload that uses defaults for new rows and preserves existing values for old rows, follow these rules: 1. The "Update Set" Rule The 
columns
header defines the scope of your update: * Columns included in the header: StarRocks expects data for these. If they are missing in the JSON, they become 
NULL
. * Columns NOT in the header: These are "excluded" from the update. * Existing rows: Their values remain unchanged. * New rows: They are filled with the DDL Default Value. 2. The Solution for your Sparse Payload To minimize your payload and use the default 
0
for 
stats_page_time_1s
, you must include only the Primary Keys in your 
columns
header and omit the optional field. Corrected Curl:
Copy code
bash
curl --location-trusted -X PUT \
  '<http://starrocks-fe:8030/api/events/events/_stream_load>' \
  -H 'Authorization: Basic cm9vdDo=' \
  -H 'format: JSON' \
  -H 'partial_update: true' \
  -H 'strip_outer_array: true' \
  -H 'columns: received, auction_id, imp_key' \
  -d '[{"received":"2026-05-12 10:00:00","auction_id":"abc123","imp_key":"key2"}]'
Why this works: 1. Mandatory PKs: By including 
received, auction_id, imp_key
in the header, the error about missing key columns is resolved. 2. Sparse Behavior: Because 
stats_page_time_1s
is not in the 
columns
header: * If this is a new 
imp_key
, it will be created with the default value 
0
. * If this is an existing 
imp_key
, the previous value in that column will be preserved. Important Note on JSON Simple Mode While StarRocks supports "Simple Mode" (no 
columns
header) for standard loads, for Partial Updates, the explicit 
columns
header is currently required to safely define the update boundary and ensure all Primary Keys are accounted for. If you have a batch where some rows have the extra column and some don't, you must either: 1. Split them into two different Stream Load calls with different 
columns
headers. 2. Include the column in the header and provide the value (or the default) for every row in the JSON. References • loading/Load_to_Primary_Key_tables.md • loading/StreamLoad.md • sql-reference/sql-statements/loading_unloading/STREAM_LOAD.md • faq/loading/Stream_load_faq.md References • loading/Load_to_Primary_Key_tables.md • sql-reference/sql-statements/loading_unloading/STREAM_LOAD.md • loading/StreamLoad.md • sql-reference/sql-statements/table_bucket_part_index/auto_increment.md References • loading/Load_to_Primary_Key_tables.md • sql-reference/sql-statements/generated_columns.md • sql-reference/sql-statements/table_bucket_part_index/auto_increment.md
m
@Rocky My primary key table partitions are set by the hour. I notice my CPU spikes by about 15% every hour, about 13 minutes past the hour. Why is this the case? I made partition expiration easy by separating it by hour
r
The hourly 15% CPU spike you are seeing at 13 minutes past the hour is almost certainly caused by the Dynamic Partition Scheduler triggering the creation of new partitions and, more importantly, the deletion of expired partitions for your Primary Key table. Here is a breakdown of why this happens specifically at the 13-minute mark and why it affects your CPU. 1. The "13 Minutes" Timing The timing of this task is determined by the FE configuration 
dynamic_partition_check_interval_seconds
, which defaults to 600 seconds (10 minutes). * This scheduler is a background thread (
BackgroundDynamicPartitionThread
) that wakes up every 10 minutes. * The specific "offset" (13 minutes past the hour) is determined by when your FE Leader node was last started. If your FE started at, for example, 100300, the scheduler will run at 10:13, 10:23, 10:33, etc. * The Hourly Trigger: Although the check happens every 10 minutes, the "work" (dropping the old hourly partition) only becomes necessary once the clock rolls over to a new hour. The first check after the top of the hour (in your case, the one at HH:13) is when the scheduler identifies that a partition has reached its TTL and issues the 
DROP PARTITION
command. 2. Why Primary Key Tables Spike CPU Dropping a partition in a Primary Key table is significantly more resource-intensive than in other table models (like Duplicate or Aggregate) because: * Persistent Index Cleanup: If you have 
enable_persistent_index = true
(the default), each tablet in the dropped partition has a physical index file on disk. When the partition is dropped, StarRocks must clean up these index entries and delete the files across all BEs. * Delete Vector Management: Primary Key tables maintain "Delete Vectors" to track updated/deleted rows. Dropping a partition requires the system to clear all metadata associated with these vectors for that specific time range. * Tablet Multiplication: The amount of work scales with your Bucket Count × Replicas. If you have 32 buckets and 3 replicas, dropping one partition forces the cluster to perform cleanup operations for 96 tablets simultaneously. * Metadata Locks: In older versions of StarRocks, the 
BackgroundDynamicPartitionThread
could compete for the 
Database
lock with other threads (like the 
ReportHandler
), which can cause a momentary spike in CPU usage as threads wait or context-switch during the metadata update. 3. How to Verify and Optimize To confirm this is the cause, check your FE Leader's 
fe.log
 for entries around the 13-minute mark containing: 
DynamicPartitionScheduler
or 
drop partition
. To reduce the CPU spike: * Reduce Bucket Count: If your hourly partitions are small (e.g., a few GBs), ensure you aren't over-sharding. Aim for tablet sizes between 100MB and 1GB for Primary Key tables. * Adjust Check Interval: If you want to move the spike or make it less frequent (though it will still happen hourly for hourly partitions), you can change the FE parameter:
Copy code
sql
    ADMIN SET FRONTEND CONFIG ("dynamic_partition_check_interval_seconds" = "1800"); -- Check every 30 mins
* Persistent Index Type: If you are on a Cloud-Native (shared-data) architecture, ensure your 
persistent_index_type
is set to 
LOCAL
or 
CLOUD_NATIVE
appropriately to balance the cleanup I/O. References • table_design/data_distribution/dynamic_partitioning.md • best_practices/primarykey_table.md • administration/management/FE_configuration.md • faq/operation_maintenance_faq.md References
m
@Rocky Is there any meaningful difference if I store binary column values as INT vs TINYINT
r
Yes, there is a meaningful difference. If you are storing binary values (0 or 1), you should use 
TINYINT
 (or its alias 
BOOLEAN
) instead of 
INT
. In StarRocks, the differences break down into three main categories: 1. Memory and CPU Efficiency (Most Impactful) StarRocks is a vectorized execution engine. It processes data in "chunks" (typically 1024 rows at a time). * Memory Bandwidth: A chunk of 1,024 
TINYINT
values takes up 1 KB of memory, while a chunk of 
INT
values takes 4 KB. During complex queries (joins, aggregations), transferring 1/4 the data between the RAM and the CPU significantly reduces memory bandwidth bottlenecks. * CPU Cache & SIMD: Smaller data types allow more values to fit into the CPU’s L1/L2 caches. Furthermore, SIMD (Single Instruction Multiple Data) instructions can process more 1-byte values in a single CPU cycle than 4-byte values, leading to faster scan and filter speeds. 2. Prefix Indexing (Short Key) StarRocks uses a Short Key Index (prefix index) to speed up data lookups. This index is limited to the first 36 bytes of your table's columns. * If you use 
INT
, this column consumes 4 bytes of that 36-byte limit. * If you use 
TINYINT
, it only consumes 1 byte. Using 
TINYINT
allows you to include more columns in your sort key/prefix index, which can drastically improve query pruning for other columns later in the schema. 3. Disk Storage While StarRocks uses columnar compression (like LZ4, ZSTD, and Bit-packing) which makes the on-disk difference for a column of only 0s and 1s relatively small, 
TINYINT
is still the more efficient baseline. Summary Comparison Feature | TINYINT / BOOLEAN | INT Storage Size | 1 Byte | 4 Bytes Prefix Index Cost | 1 Byte | 4 Bytes Vectorized Speed | Faster (Better SIMD/Cache) | Slower (More memory traffic) Memory Usage | 1x | 4x Recommendation: Always use 
BOOLEAN
 (which StarRocks treats as a 1-byte 
TINYINT
) for binary flags. If you meant "binary" as in raw bytes (blobs), use the 
VARBINARY
 type instead (available in StarRocks 3.0+). References • sql-reference/data-types/numeric/TINYINT.md • sql-reference/data-types/numeric/BOOLEAN.md • sql-reference/sql-statements/table_bucket_part_index/CREATE_TABLE.md • best_practices/query_tuning/schema_tuning.md References • sql-reference/data-types/numeric/BOOLEAN.md • sql-reference/sql-statements/table_bucket_part_index/CREATE_TABLE.md • knowledge/usage/best-practice.md • loading/Flink-connector-starrocks.md • sql-reference/data-types/numeric/TINYINT.md • using_starrocks/distinct_values/Using_bitmap.md • data_source/catalog/maxcompute_catalog.md • unloading/Spark_connector.md
m
@Rocky My PK table is currently using all 8 BE nodes in my starrocks cluster. I want to segregate it to a dedicated 6 BE nodes where it is completely isolated from other databases/table. I have 2 questions: 1. Can I modify the table schema and trigger rebalancing to the 6 selected BE nodes 2. How will this impact query performance
r
Yes, you can achieve this isolation using the BE Labels feature (introduced in StarRocks v3.2.8). This allows you to tag specific Backend (BE) nodes and then restrict a table's data distribution to only the nodes matching those tags. 1. How to modify the table and trigger rebalancing To move your PK table to 6 dedicated nodes, follow these steps: Step 1: Label your BE nodes First, assign a unique label to the 6 BE nodes you want to dedicate to this table.
Copy code
sql
-- Repeat for each of the 6 dedicated BE nodes
ALTER SYSTEM MODIFY BACKEND "be_ip_1:9050" SET ("labels.location" = "group:dedicated_pk");
ALTER SYSTEM MODIFY BACKEND "be_ip_2:9050" SET ("labels.location" = "group:dedicated_pk");
...
Step 2: Update the Table Property Modify your table to use this label. This change will trigger the StarRocks Tablet Scheduler to start background rebalancing. It will create new replicas on the 6 labeled nodes and gradually delete the replicas from the other 2 nodes.
Copy code
sql
ALTER TABLE my_pk_table SET ("labels.location" = "group:dedicated_pk");
Step 3: Monitor the Progress You can check the rebalancing progress by looking at the tablet distribution:
Copy code
sql
SHOW TABLET FROM my_pk_table;
-- Or check system-wide balance status
SHOW PROC '/cluster_balance/cluster_load_stat/HDD';
────────── 2. Impact on Query Performance Restricting your table from 8 nodes to 6 nodes will have several performance implications: * Parallelism Reduction: Because the data is now spread across fewer nodes, the maximum degree of parallelism for a single query on this table decreases. You are effectively losing ~25% of the cluster's aggregate CPU and IO throughput for scans on this specific table. * Memory Pressure (Primary Key Specific): PK tables keep a "Persistent Index" in memory (or on disk). By condensing the table onto 6 nodes, the memory required for the primary key index on each of those 6 BEs will increase by roughly 33%. Ensure these nodes have enough RAM to handle the increased index load. * Isolation (The "Noisy Neighbor" benefit): If your other 2 nodes are under heavy load from different databases, your PK table will no longer be impacted by them. However, for true isolation, you should also set the 
labels.location
for your other tables so they stay off your 6 dedicated nodes. By default, tables use 
*
(all labels), meaning other tables will still reside on your 6 dedicated nodes unless you explicitly move them. * Rebalancing Overhead: During the initial move, you will see a temporary spike in network and disk IO as tablets are migrated across the BEs. Summary Recommendation If you need strict isolation, this is the correct approach. However, if your goal is simply to prevent this table from being "slowed down" by others, you might first consider Resource Groups (Workload Management). Resource Groups allow you to limit CPU and Memory usage for specific queries/users across all 8 nodes without sacrificing the aggregate throughput and storage capacity of the full cluster. References • administration/management/resource_management/be_label.md • administration/management/BE_configuration.md • using_starrocks/caching/query_cache.md
m
@Rocky I want to try restoring
Copy code
RESTORE SNAPSHOT sr_par_backup
FROM test_repo
DATABASE sr_hub
ON (TABLE sr_member PARTITION (p1))
PROPERTIES ("backup_timestamp" = "2024-12-09-10-52-10-940");
What is backup_timestamp for? Is it to identify the correct files for backup? Where do I get the timestamp? AWS s3?
r
The 
backup_timestamp
is a required property that identifies the specific version of a snapshot you want to restore. Because you can run multiple 
BACKUP
jobs using the same snapshot name (e.g., 
sr_par_backup
) over time, the timestamp acts as a unique identifier to distinguish between them. It tells StarRocks exactly which metadata and data files in the remote repository correspond to that specific backup instance. Where to get the timestamp You do not need to search through AWS S3 manually. You retrieve it directly from StarRocks using the following command:
Copy code
sql
SHOW SNAPSHOT ON test_repo;
Example Output: Snapshot | Timestamp | Status sr_par_backup | 2024-12-09-10-52-10-940 | OK If you have performed multiple backups of the same table into the same repository name, you will see multiple rows. Simply copy the 
Timestamp
value for the version you wish to restore. Summary * Purpose: To uniquely identify one specific backup run within a named snapshot. * Identify files? Yes, it points StarRocks to the correct folder path and metadata mapping on S3. * Source: Always use 
SHOW SNAPSHOT
to get the value; do not attempt to guess it from S3 file modification times. References • administration/management/Backup_and_restore.md • sql-reference/sql-statements/backup_restore/RESTORE.md • sql-reference/sql-statements/backup_restore/SHOW_SNAPSHOT.md References • sql-reference/sql-statements/backup_restore/SHOW_SNAPSHOT.md • sql-reference/sql-statements/backup_restore/SHOW_REPOSITORIES.md • administration/management/Backup_and_restore.md • sql-reference/sql-statements/backup_restore/RESTORE.md • sql-reference/sql-statements/backup_restore/SHOW_BACKUP.md
m
@Rocky My FE and BE storage usage hard limit percent and flood percent are both 95% for disk. Why did I get unhealthy tablets when I reached disk capacity? For additional context I am popuilating data on my primary key tables in shared nothing architecture with EBS storage.
r
Setting your 
storage_usage_hard_limit_percent
and 
storage_flood_stage_usage_percent
to 95% acts as a safety valve, but it does not guarantee that tablets will remain healthy if you hit that limit, especially with Primary Key (PK) tables in a Shared-nothing architecture. Here is why your tablets become unhealthy when disk capacity is reached: 1. Inconsistent Replicas (Partial Load Failures) StarRocks checks disk capacity periodically, but not in real-time for every single byte written. * The Reporting Lag: Backends (BE) typically report their disk state to the Frontend (FE) every 60 seconds (
report_disk_state_interval_seconds
). * The Race Condition: If you are running high-concurrency loads, the FE might think a BE has space and send it a large data batch. If that BE hits the 95% threshold mid-load, it will start rejecting writes. * Unhealthy Result: If one replica of a tablet is on a "full" node and rejects the write while the other two replicas (on less-full nodes) succeed, the tablet enters an Inconsistent state. StarRocks marks such tablets as "unhealthy" because they lack a synchronized quorum of data versions. 2. Persistent Index Flush Failures (PK Specific) Primary Key tables in StarRocks use a Persistent Index (enabled by default) to manage upserts efficiently. * This index is essentially a disk-based key-value store (using SST files) that must be updated and flushed to disk during every load. * If the disk is at 95% or higher, the BE may fail to create or expand these index files. Unlike a standard data file, if the index cannot be persisted, the replica becomes effectively broken and is marked as "unhealthy" because it can no longer guarantee the uniqueness or location of its keys. 3. Compaction Deadlock PK tables rely heavily on background Compaction to merge data versions and delete vectors. * Space Overhead: Compaction requires temporary disk space to write the new, merged segment files before deleting the old ones. * The Limit: When a disk hits the "flood stage" (95%), background compaction often stops or fails because it cannot allocate the necessary temporary space. * Unhealthy Result: Without compaction, the number of "versions" in a tablet grows rapidly. Once it hits the limit (
tablet_max_versions
, default 1000), the tablet becomes "unhealthy" and will stop accepting any further writes or updates. 4. EBS and OS-Level Overhead If you are using EBS, hitting "95%" of the StarRocks limit might mean the underlying file system is even closer to actual physical capacity due to: * Logs and Metadata: BE logs (
be.out
, 
<http://be.INFO|be.INFO>
) and metadata are stored on the same disk and may not be fully accounted for in the capacity percentage check. * Reserved Blocks: Linux filesystems (like ext4) often reserve 5% of space for the root user. If your StarRocks process isn't running as root, it might effectively hit a "100% full" error at the OS level while StarRocks still thinks it has 5% left. Recommendation * Lower the Watermarks: Set 
storage_usage_soft_limit_percent
to 85% and 
storage_flood_stage_usage_percent
to 90%. This gives the system more "breathing room" for compaction and index flushes. * Check Unhealthy Reasons: Run 
SHOW TABLET <tablet_id>
for an unhealthy tablet. Look for the 
VersionCount
and 
IsBad
fields. * Monitor Disk Variance: In PK tables, ensure your data is well-distributed. If one BE is significantly more full than others, it will trigger these "unhealthy" states while the rest of the cluster has plenty of room. References • administration/management/BE_configuration.md • administration/management/FE_configuration.md • best_practices/primarykey_table.md
m
@Rocky My PK table has no replication enabled. Does this contribute to unhealthy tablet/data corruption?
r
Yes, having no replication (
replication_num = 1
) is a significant contributor to why your tablets are becoming unhealthy and increases the risk of data corruption, especially when combined with a disk capacity crisis. In a StarRocks cluster, replication isn't just about "backup"; it's the primary mechanism for maintaining data integrity and availability. 1. No Redundancy for Failures When you have 
replication_num = 1
, there is no "majority" to fall back on. * With 3 replicas: If one Backend (BE) is at 95% disk and fails to write a data segment or update its index, the load can still succeed on the other two nodes. StarRocks simply marks the failed replica as "bad" and will eventually re-clone it from a healthy one once space is cleared. * With 1 replica: If that single write fails (due to the 95% disk limit or an OS-level IO error), there is no other copy of that data version anywhere in the cluster. The tablet becomes unhealthy immediately because the Frontend (FE) can no longer find a "Last Success Version" that is consistent. 2. Primary Key Table Sensitivity Primary Key (PK) tables are more "brittle" when disk space is tight compared to Duplicate or Aggregate tables: * Persistent Index Corruption: PK tables rely on a disk-based index to handle upserts. If the disk is full and the BE fails to flush this index to disk, the mapping of keys to row locations is lost or partial. Because you only have one replica, you have no healthy index to "copy" from. * DelVector Inconsistency: When you update data in a PK table, StarRocks writes the new data and updates a "Delete Vector" for the old data. If a disk-full error occurs during the DelVector update on your only replica, you could end up with "phantom" data (seeing both the old and new versions) or lost records. 3. Data Corruption vs. Unhealthy Status * Unhealthy (
IsBad = false
): This usually means the tablet is lagging or has a version mismatch. With one replica, this often happens because a load transaction was "committed" in the FE but the physical write failed on the BE due to the 95% limit. * Corruption (
IsBad = true
): This is the "Data Corruption" you asked about. This occurs if the underlying metadata files (index or segment files) were truncated or partially written because the disk ran out of space mid-operation. * The Risk: With 
replication_num = 1
, if a tablet is marked 
IsBad
, the data is effectively lost. StarRocks cannot "repair" a corrupted tablet if there isn't a healthy replica to clone from. Summary of Risks with `replication_num = 1`: Feature | Risk with 1 Replica Availability | If the BE node goes down or the disk fills up, the table/partition becomes completely unreadable. Durability | Any hardware failure or filesystem corruption results in permanent data loss. Consistency | Failed loads at 95% capacity leave the single replica in an "Inconsistent" state with no way to self-heal. Recommendation 1. Increase Replication: For production data, always use 
replication_num = 3
. _ _Note: You cannot change the replication number of an existing PK table via 
ALTER
. You must create a new table and 
INSERT INTO ... SELECT
.* 2. Emergency Recovery: If your tablets are currently unhealthy due to the 95% limit, try to clear space (delete logs or other tables), then run 
ADMIN REPAIR TABLE <table_name>
to see if the FE can re-sync the metadata. However, if the single replica is 
IsBad
, you will likely need to truncate and re-load that data. References • administration/management/resource_management/Replica.md • table_design/table_types/primary_key_table.md • administration/management/BE_configuration.md References
m
@Rocky How can I diagnose why my stream load is failing. Transaction error message link
r
To diagnose a failing Stream Load in StarRocks, you primarily use the 
ErrorURL
 provided in the JSON response. This is likely what you are referring to as the "transaction error message link." 1. Locate the Error Link When a Stream Load fails (or has filtered rows), the response contains a JSON body. Look for the 
ErrorURL
field:
Copy code
json
{
    "Status": "Fail",
    "Message": "too many filtered rows",
    "ErrorURL": "<http://172.26.195.68:8045/api/_load_error_log?file=error_log_3a4eb8421f0878a6_9a54df29fd9206be>",
    ...
}
2. Access the Error Details The 
ErrorURL
points to a temporary log on a specific Backend (BE) node. You can view the specific rows that failed and the reasons for their failure by running:
Copy code
bash
curl "<http://172.26.195.68:8045/api/_load_error_log?file=error_log_3a4eb8421f0878a6_9a54df29fd9206be>"
Common errors found in this log: * 
column count mismatch
: The number of columns in your CSV doesn't match the table or your 
-H "columns:..."
header. * 
type conversion failed
: You tried to load a string into an 
INT
column, or a date format was unrecognizable. * 
value out of range
: A number is too large for the target column type. 3. Diagnosing "Transaction" Specific Errors If your error message specifically mentions Transaction issues (like 
TXN_NOT_EXISTS
or 
Label Already Exists
) and does not provide an 
ErrorURL
, check these common scenarios: * 
Label Already Exists
: You are trying to reuse a label from a previous successful or ongoing load. Change the label or wait for the previous one to finish. * 
Transaction State Invalid
: This occurs if you are using the multi-step Transaction Interface (
/api/transaction/begin
, 
load
, 
commit
) and you tried to commit a transaction that has already been aborted or timed out. * 
Publish Timeout
: The data was written successfully to the BEs, but the FE timed out while making the data visible. Do not retry this load immediately; the data will usually become visible on its own within a few seconds. 4. What if the link is inaccessible? If you cannot open the 
ErrorURL
(e.g., connection refused): 1. Network issue: Ensure your client can reach the BE IP (not just the FE) on the HTTP port (default 
8040
or 
8045
). 2. Expiration: Error logs are kept for 48 hours by default. If the load is older than that, the log is deleted. 3. Check FE/BE Logs: If the JSON response is totally empty or a 500 error, check 
fe/log/fe.warn.log
and 
be/log/be.INFO
on the StarRocks nodes for low-level system crashes or disk-full errors. References • sql-reference/sql-statements/loading_unloading/STREAM_LOAD.md • loading/Stream_Load_transaction_interface.md • faq/loading/Loading_faq.md References • loading/SQL_transaction.md • sql-reference/Error_code.md
m
@Rocky Any tables or MV i can check>
r
To diagnose stream load failures and tablet health, you can use the following system tables and diagnostic commands. 1. Diagnosing Stream Load Failures StarRocks provides several views in 
information_schema
and 
_statistics_
to track load history. * 
information_schema.stream_loads
: Specifically for Stream Load jobs. It includes the 
ERROR_MSG
and the 
TRACKING_URL
(the same link you see in the JSON response).
Copy code
sql
    SELECT * FROM information_schema.stream_loads
    WHERE TABLE_NAME = 'your_table'
    ORDER BY CREATE_TIME_MS DESC LIMIT 10;
* 
information_schema.loads
: A general view for all load types (Stream, Broker, Insert). It is useful for checking the current 
STATE
(e.g., 
CANCELLED
, 
LOADING
). * 
_statistics_.loads_history
: Stores load records for the last 3 months. Use this if you need to analyze historical trends of failures.
Copy code
sql
    SELECT * FROM _statistics_.loads_history
    WHERE table_name = 'your_table' AND state = 'CANCELLED';
2. Checking Tablet Health & "Unhealthy" Replicas Since you have 
replication_num = 1
, any "unhealthy" status usually indicates a version mismatch or a corrupted replica. * 
ADMIN SHOW REPLICA STATUS
: The fastest way to find tablets that are not "OK."
Copy code
sql
    ADMIN SHOW REPLICA STATUS FROM your_table
    WHERE STATUS != 'OK';
    -- Possible statuses: VERSION_ERROR, MISSING, DEAD, SCHEMA_ERROR
* 
SHOW TABLET <tablet_id>
: If you have a specific ID, this command gives you a 
DetailCmd
column. Copy and run that command (which looks like 
SHOW PROC '/dbs/...'
) to see: * 
IsBad
: If 
true
, the data on disk is likely corrupted. * 
VersionCount
: If this is very high (e.g., > 1000), compaction has failed (often due to the 95% disk limit). * 
LstFailedVersion
: Shows the version that failed to write. 3. Monitoring Backend (BE) Health To see how tablets are distributed and if specific nodes are overloaded: * 
information_schema.be_tablets
: Shows the number of tablets, segments, and rowsets per BE.
Copy code
sql
    SELECT backend_id, COUNT(*), SUM(data_size)
    FROM information_schema.be_tablets
    GROUP BY backend_id;
* 
SHOW BACKENDS
: Check the 
AvailCapacity
and 
MaxDiskUsedPct
to verify which nodes are hitting your 95% threshold. Summary Table Goal | Table / Command Why did my load fail? | 
information_schema.stream_loads
Which tablets are broken? | 
ADMIN SHOW REPLICA STATUS FROM table
Is the data corrupted? | 
SHOW PROC
(via 
SHOW TABLET
) -> Check 
IsBad
Which BE is full? | 
SHOW BACKENDS
References • sql-reference/information_schema/stream_loads.md • administration/management/resource_management/Replica.md • loading/loading_introduction/troubleshooting_loading.md References • administration/management/resource_management/Replica.md
m
@Rocky
Copy code
current running txns on db 44945 is 100, larger than limit 100
r
This error occurs because StarRocks limits the number of concurrent load transactions (Stream Load, Broker Load, INSERT INTO, etc.) per database to prevent overloading the system. In versions prior to v3.1, the default limit was 100. In newer versions, it has been increased to 1,000. 1. Immediate Fix: Increase the Limit You can increase this limit dynamically on the Frontend (FE) without restarting the cluster:
Copy code
sql
-- Increase to 200 (or higher as needed)
ADMIN SET FRONTEND CONFIG ("max_running_txn_num_per_db" = "200");
2. How to Diagnose the "Transaction Leak" If you aren't intentionally running 100 loads at once, you might have "hanging" transactions or are loading data too frequently in tiny batches. * Check running transaction counts: Use the ID from your error message (
44945
):
Copy code
sql
    SHOW PROC '/transactions/44945';
Look at the 
State
and 
Number
columns. If 
running
is high, those loads are active. * Identify the specific loads:
Copy code
sql
    SELECT * FROM information_schema.loads
    WHERE STATE = 'LOADING' OR STATE = 'PREPARED';
3. Recommendations * Batching: If you are hitting this limit, you are likely doing many small Stream Loads. It is better for StarRocks performance to group data into larger batches (e.g., once every 5–10 seconds) rather than hundreds of loads per second. * Zombie Transactions: Check if your client application is starting transactions (via the Stream Load Transaction Interface) but failing to call 
COMMIT
or 
ABORT
. These will occupy a slot until they reach their timeout (
stream_load_default_timeout_second
). * Disk Pressure: Since you mentioned disk space issues earlier, note that if the disk is full, transactions cannot complete their "Publish" phase. They will hang in a 
COMMITTED
but not yet 
VISIBLE
state, continuing to count against your transaction limit until they finally succeed or time out. References • faq/loading/Loading_faq.md • administration/management/FE_configuration.md • sql-reference/sql-statements/cluster-management/nodes_processes/SHOW_PROC.md References • faq/loading/Loading_faq.md • administration/management/FE_configuration.md • sql-reference/sql-statements/cluster-management/nodes_processes/SHOW_PROC.md • loading/Stream_Load.md -transaction-interface) • administration/management/resource_management/Replica.md References • faq/loading/Loading_faq.md • administration/management/BE_configuration.md • administration/management/FE_configuration.md • sql-reference/sql-statements/cluster-management/nodes_processes/SHOW_PROC.md • sql-reference/information_schema/be_txns.md • faq/operation_maintenance_faq.md • administration/management/monitoring/metrics.md
m
@Rocky I think I have zombie transactions. How do I diagnose this
r
To diagnose "zombie" or hanging transactions that are causing you to hit the 
max_running_txn_num_per_db
limit, follow these steps: 1. Identify the Database ID First, find the ID of the database mentioned in your error message (
44945
in your previous example) or list all database transaction counts:
Copy code
sql
SHOW PROC '/transactions';
This will show you which databases have "running" transactions. 2. View Transaction Summary Check the state of transactions for that specific database:
Copy code
sql
-- Replace 44945 with your DbId
SHOW PROC '/transactions/44945';
If the 
running
 count is high (e.g., 100), you have active or stalled transactions. 3. Find Specific Transaction IDs StarRocks doesn't have a single "list all" command for transaction IDs, but you can find them using the following system views: * 
information_schema.be_txns
: This table shows active transactions currently being processed on the Backend (BE) nodes.
Copy code
sql
    SELECT TXN_ID, BE_ID, CREATE_TIME
    FROM information_schema.be_txns
    ORDER BY CREATE_TIME ASC;
_Transactions with a very old 
CREATE_TIME
(Unix timestamp) are your "zombies."_ * 
information_schema.stream_loads
: For Stream Loads, check for jobs that are stuck in the 
LOADING
state.
Copy code
sql
    SELECT LABEL, ID, CREATE_TIME_MS
    FROM information_schema.stream_loads
    WHERE STATE = 'LOADING';
4. Inspect a Specific Transaction Once you have a 
TXN_ID
, check its details to see why it hasn't finished (e.g., check its timeout or if it's stuck in the 
PREPARED
but not 
VISIBLE
state):
Copy code
sql
SHOW TRANSACTION WHERE ID = <txn_id>;
Look for: * 
TransactionStatus
: If it is 
COMMITTED
, it is waiting for the "Publish" phase (visibility). This can happen if the cluster is under heavy load or disk pressure. * 
TimeoutMs
: Check if the transaction has actually exceeded its timeout. 5. How to "Kill" Zombies * Normal Stream Load: You cannot manually cancel a standard Stream Load. It must either finish, time out, or fail. If they are stuck, check if your FE configuration 
stream_load_default_timeout_second
(default 600s) is too high. * Transaction Interface: If you used 
/api/transaction/begin
, you must call the 
/api/transaction/rollback
API with the label to clear it. * SQL Transactions (
INSERT INTO
): If the transaction was started via SQL, find the 
ConnectionId
and kill the session:
Copy code
sql
    SHOW PROCESSLIST;
    KILL <connection_id>;
Why do they stay "Running"? 1. Disk Space: You mentioned being at 95% disk usage. When disks are full, StarRocks may struggle to "Publish" the transaction version, leaving the transaction in a 
COMMITTED
but not 
VISIBLE
state. It still counts as "running" against the limit. 2. Unfinished Commits: If your application starts a transaction but never calls 
COMMIT
or 
ROLLBACK
, the transaction stays open until it hits the 
idle_transaction_timeout
. 3. Network/Node Failures: If a BE node goes down during the publish phase, the transaction might wait for the node to recover or for a timeout to trigger. References • sql-reference/sql-statements/loading_unloading/SHOW_TRANSACTION.md • sql-reference/information_schema/be_txns.md • loading/Stream_Load_transaction_interface.md • administration/management/FE_configuration.md References • loading/StreamLoad.md • sql-reference/sql-statements/loading_unloading/CANCEL_LOAD.md
m
@Rocky What are the pillars of trade-offs in designing an Primary Key table in StarRocks? Shared nothing architecture
r
In a shared-nothing architecture like StarRocks, the Primary Key (PK) table is designed for real-time updates and high-performance queries. Its design involves four fundamental pillars of trade-offs: 1. Memory vs. Performance (The Index Pillar) The PK table uses a Primary Key Index to track the location (tablet and row ID) of every key. * The Trade-off: By default, StarRocks loads this index into memory during writes to achieve near-instant lookups. This makes updates extremely fast but consumes significant RAM (roughly 
(key_size + 9) _ rows _ replicas * 1.5
). * The Design Choice: If your dataset is massive and RAM is limited, you must use the Persistent Index (
enable_persistent_index = true
). This offloads the index to SSDs, trading a slight increase in write latency for a massive reduction in memory footprint. 2. Read Speed vs. Write Amplification (The "Delete-and-Insert" Pillar) Unlike other models that merge data during query time (Merge-on-Read), the PK table performs a Delete-and-Insert at write time. * The Trade-off: When you update a row, StarRocks marks the old row as deleted in a "Delete Vector" and writes the new row to a new file. * The Design Choice: This makes queries 3–10x faster than Unique Key tables because the system doesn't have to deduplicate data on the fly. However, it increases write amplification and background compaction work, as the system must constantly manage these delete markers and merge small files. 3. Distribution Strategy (The Sharding Pillar) In a shared-nothing architecture, data is partitioned and then bucketed across Backend (BE) nodes. * The Trade-off: For PK tables, the Primary Key must contain the Bucket Key (distribution key). * The Design Choice: * High-cardinality Bucket Keys: Ensure even data distribution across the cluster (preventing "hot nodes"), but can lead to many small files if you have too many buckets. * Low-cardinality Bucket Keys: May cause data skew where one BE handles more load than others, potentially hitting the memory or disk limits you encountered earlier. 4. Data Freshness vs. System Throughput (The Batching Pillar) StarRocks supports real-time ingestion, but shared-nothing systems struggle with "singleton" writes (one row at a time). * The Trade-off: Frequent small commits provide real-time freshness but create a massive number of small versions (rowsets), which eventually triggers "Too many versions" errors and slows down the system. * The Design Choice: You must batch your writes (e.g., every 5–10 seconds or every 100MB). This balances the need for "fresh" data with the system's ability to compact and manage the distributed metadata. 5. Partial Updates vs. I/O Overhead StarRocks allows you to update only specific columns in a PK table. * The Trade-off: This saves network bandwidth (you don't send the whole row), but it forces the BE to perform a read-before-write (to fetch the missing columns and re-construct the row) or a heavy merge during compaction. * The Design Choice: Use partial updates for tables with hundreds of columns where only a few change frequently. If you are updating most columns, a full row update is more efficient for the BE's I/O. References • table_design/table_types/primary_key_table.md • best_practices/primarykey_table.md • loading/Stream_Load.md • administration/management/resource_management/Replica.md References • table_design/table_types/primary_key_table.md • best_practices/primarykey_table.md
Open in Slack
PreviousNext
Powered by