@Rocky Issue: Incremental Materialized View on Iceberg Table — Failures & High Spill Hi team 👋 We're very excited about the Incremental Materialized View (IMV) feature and recently upgraded our StarRocks cluster to v4.1 specifically to leverage it on an Iceberg table (which is actually a Paimon table that generates Iceberg metadata to Polaris). 📊 Table Context • ~*25–30B records/day* • Partitioned by

partition_date

and

partition_hour

• ~*3.5 TB file size per hourly partition* ✅ Regular MV — Works Fine We first created a regular (non-incremental) MV with a

GROUP BY

+

SUM

aggregation over ~20 dimensions:

CREATE MATERIALIZED VIEW `regular_mv_on_sdk_events_only_test_1` (...)
PARTITION BY (`partition_date`,`partition_hour`)
DISTRIBUTED BY RANDOM
REFRESH DEFERRED ASYNC EVERY(INTERVAL 30 MINUTE)
PROPERTIES (
  "replicated_storage" = "true",
  "partition_refresh_number" = "2",
  "session.new_planner_optimize_timeout" = "300000",
  "replication_num" = "1",
  "datacache.enable" = "true",
  "enable_async_write_back" = "false",
  "storage_volume" = "builtin_storage_volume",
  "warehouse" = "default_warehouse"
)
AS SELECT
    partition_date, partition_hour, platform, ...,
    SUM(fulfillment_downsampling_factor) AS fulfillment_count,
    SUM(request_downsampling_factor) AS request_count,
    SUM(completion_downsampling_factor) AS completion_count
FROM dwd_rest.levelplay.mediation_sdk_business_v3
WHERE partition_date >= '2026-04-29'
GROUP BY partition_date, partition_hour, platform, ...;

Each partition refresh completes in a few seconds to a few minutes, depending on cluster load. No issues here. ❌ Incremental MV — Failing Consistently We then created the exact same MV but with

"refresh_mode" = "INCREMENTAL"

. We noticed that

SHOW CREATE MATERIALIZED VIEW

reveals StarRocks automatically added (which i guess it's expected) • A

__ROW_ID__

column •

DISTRIBUTED BY HASH(__ROW_ID__)

(replacing our

DISTRIBUTED BY RANDOM

) • Hidden

__AGG_STATE_sum(...)

columns The refreshes are consistently failing with errors such as: Error 1 — bRPC / Connection Reset:

com.starrocks.common.DdlException: transmit chunk rpc failed
  [dest=240.72.120.137:8060] detail: brpc failed, error=Host is down,
  error_text=[E104] Fail to read from Socket... Connection reset by peer
  [E112] Not connected to 240.72.120.137:8060 yet

Error 2 — Thrift Connection Refused:

org.apache.thrift.TException: java.net.ConnectException: Connection refused

Additional Observation: High Spill to Disk During incremental refresh attempts, we observed significantly higher spill to disk compared to the regular MV refresh on the same data. This seems disproportionate and suggests the incremental refresh may be materializing or processing data very differently (and much less efficiently) than expected. Questions 1. Is the Incremental MV feature fully supported for Iceberg external tables in v4.1, or are there known limitations? 2. The

Connection reset by peer

/

Connection refused

errors suggest BE nodes are crashing or running out of resources during the incremental refresh — is this a known issue at this scale? 3. Is there any tuning we can do on our side (memory limits, spill config, partition refresh number, etc.) to make this work? 4. Are the auto-generated

__ROW_ID__

and

__AGG_STATE_*

columns expected behavior? Any guidance on how these affect performance? We'd really love to get Incremental MVs working for our use case. Happy to share more details, query profiles, or logs as needed. Thanks! 🙏

It’s great to see you pushing the limits with StarRocks v4.1! The Incremental Materialized View (IMV) feature is a major architectural shift designed for low-latency updates, but at the scale you're operating (25–30B records/day), it behaves very differently from the "Regular" MVs you're used to. Here is a breakdown of why you're seeing these failures and how to address them. 1. Is Incremental MV supported for Iceberg in v4.1? Yes, StarRocks v4.1 officially introduced support for Incremental View Maintenance (IVM) on Iceberg tables. However, there is a critical distinction between how StarRocks handles "Regular" MVs and "Incremental" MVs: * Regular MV (Default / PCT): Uses Partition Change Tracking (PCT). When data in an Iceberg partition changes, StarRocks identifies the affected partitions and performs a

DELETE + INSERT

(or

INSERT OVERWRITE

) of those entire partitions. This is very efficient for large batch loads. * Incremental MV (

refresh_mode

=

INCREMENTAL

): Uses a stateful row-level engine. Instead of refreshing whole partitions, it calculates the "delta" (the change) and updates the existing rows in the MV. 2. Why are the BE nodes crashing (bRPC / Connection Reset)? The

Connection reset by peer

and

Thrift Connection refused

errors almost always indicate that the BE process has crashed, usually due to an OOM (Out of Memory) event or a bRPC timeout. At your scale (3.5 TB / 30B rows per hour), the Incremental MV is likely struggling with: * The Shuffle: To update an IMV, StarRocks must shuffle the incoming data by

*ROW_ID*

to find the existing state. Shuffling 3.5 TB of data by a high-cardinality ID is extremely memory-intensive. * State Maintenance: For every

GROUP BY

combination, the IMV must maintain an internal state. With 20 dimensions and billions of rows, the state table itself might be exceeding the available memory on your BE nodes, leading to the high spill-to-disk and eventual crashes. *3. Explanation of

*ROW_ID*

and `__AGG_STATE`* These are not bugs; they are the "engine" of the new IVM framework: * *`*ROW_ID*`:* This is used to map rows in the base Iceberg table to the rows in the MV. StarRocks v4.1 uses this to perform row-level deltas rather than partition-level overwrites. * *`DISTRIBUTED BY HASH(ROW_ID)`:* StarRocks automatically changes the distribution because it must ensure that a specific row from the base table always lands on the same BE node as its corresponding entry in the MV state to perform the update. * `__AGG_STATE_sum(...)`: This stores the intermediate aggregation state. For a

SUM

, it allows StarRocks to take a new value and add it to the existing total without re-reading the entire historical partition. 4. Recommendations & Tuning A. Re-evaluate "Incremental" vs "Partition" Refresh For your specific use case (30B rows/day, partitioned by hour), Regular MVs (PCT mode) are often superior. Since you are already partitioning by

partition_hour

and your regular MV refreshes in "a few minutes," you are already achieving a high level of efficiency. "Incremental" mode is intended for cases where you have small, frequent updates across many partitions. In a high-volume append-only log scenario, the overhead of row-level state tracking often outweighs the benefits of not re-scanning the partition. B. Tuning for IMV (If you want to keep testing it) If you want to stick with

INCREMENTAL

mode, try the following: 1. Increase Memory Limits: Ensure

query_mem_limit

is high enough for the BEs to hold the aggregation states. 2. Adjust Spill Settings:

sql
    SET GLOBAL spill_mem_limit_threshold = 0.8; -- Start spilling earlier to avoid OOM
    SET GLOBAL enable_spill = true;

3. Check Cardinality: Reduce the number of dimensions in your

GROUP BY

. 20 dimensions on 30B rows can create a massive state table.