@Rocky It seems that ASYNC MVs based on partitioned Iceberg are currently more efficient than native tables because they only update based on snapshot delta. what is the current status and roadmap for IVM in Starrocks?

The current status of Incremental View Maintenance (IVM) in StarRocks is in an active rollout phase, primarily centered around StarRocks v4.1 (and the upcoming roadmap). While you are correct that Iceberg-based Asynchronous Materialized Views (MVs) have historically been more efficient due to partition-level change tracking (PCT), StarRocks is moving toward true row-level incremental maintenance. 1. Current Status: "Incremental" Refresh Mode StarRocks recently introduced the

refresh_mode

property for MVs, specifically to distinguish between partition-based refresh and row-level incremental maintenance. * Supported Data Sources: Currently, the

INCREMENTAL

refresh mode is primarily available for Apache Iceberg and Paimon tables (starting in the 4.x/development branch and v3.3+ experimental phases). * How it works: Unlike the traditional "PCT" mode—which recomputes entire partitions if even one row changes—the

INCREMENTAL

mode uses snapshot deltas to process only the changed rows. * Native Table Support: For native StarRocks tables, "incremental" refresh still mostly refers to Partition Change Tracking (PCT). If data in a specific partition changes, StarRocks will refresh that entire partition. True row-level IVM for native tables is a major roadmap item. 2. Supported Operators (IVM Phase 1) The initial phase of IVM (available for Iceberg/Paimon) supports the following operators incrementally: * Select / Filter * Inner Join * Aggregation with

GROUP BY

(e.g.,

SUM

,

COUNT

,

MIN

,

MAX

). * Union All Current Limitations: * Append-only Only: Incremental refresh currently only supports

INSERT

operations on the base tables. If an

UPDATE

,

DELETE

, or

OVERWRITE

occurs, the MV will either fail or fall back to a full/partition-level refresh (depending on whether mode is set to

INCREMENTAL

or

AUTO

). * Unsupported Operators: Outer Joins,

DISTINCT

aggregations, and certain nested combinations (like Join after Aggregation) are not yet supported for incremental processing. 3. Roadmap for IVM The StarRocks community is working through a multi-phase roadmap to expand these capabilities: * Phase 1 (Current/Near-term): Expanding

INCREMENTAL

mode for Iceberg/Paimon to include more operators (Outer Joins) and improving the state management for complex aggregations. * Native Table IVM: Extending the row-level incremental framework to StarRocks' native internal tables (OLAP tables), which would allow MVs to update without partition-level recomputation. * Multi-level Partitioning: A major upcoming enhancement for Iceberg MVs is support for multi-level partitioning, which will further reduce the data volume scanned during refresh tasks. * CDC Integration: Better integration with the Primary Key storage engine to handle

UPDATE

and

DELETE

deltas incrementally, which is the "holy grail" of IVM for real-time analytics. Summary Table Feature | PCT (Current Default) | IVM / Incremental Mode Granularity | Partition-level | Row/Delta-level Data Sources | Internal, Iceberg, Hive, etc. | Iceberg, Paimon (Internal in roadmap) Efficiency | High (if many rows change in one partition) | Very High (if few rows change across many partitions) Operator Support | Full SQL support | Limited (Inner Joins, Basic Aggr) For users on large-scale Iceberg tables, the recommendation is to use v3.3+ or v4.0+ and explore the

refresh_mode = "INCREMENTAL"

property if your workload is append-heavy and involves supported operators. References • sql-reference/sql-statements/materialized_view/CREATE_MATERIALIZED_VIEW.md • using_starrocks/async_mv/use_cases/create_partitioned_materialized_view.md

@Rocky what is the best method fro writing MV refresh data to Iceberg? Will IVM eventually make this more efficient?

The best method for writing Materialized View (MV) refresh data to Apache Iceberg is to use External Materialized Views (also known as Catalog MVs), where the MV's storage itself resides in the Iceberg catalog. 1. Best Method: External Materialized Views (Catalog MVs) Starting with StarRocks v3.1, you can create MVs that store their data directly in an external Iceberg catalog. This is the preferred method if you want the pre-aggregated results to be accessible by other engines (like Spark, Trino, or Flink) or if you want to keep your data lake as the single source of truth. Recommended Configuration: * Partition Alignment: Always partition the MV using a column from the base table. This enables Partition Change Tracking (PCT). * Efficiency: When the base Iceberg table changes, StarRocks detects which partitions are affected (using Iceberg snapshot deltas) and performs an

INSERT OVERWRITE

only for those specific partitions in the target Iceberg table. This avoids a full table rewrite. Example Syntax:

sql
CREATE MATERIALIZED VIEW iceberg_catalog.target_db.iceberg_mv
PARTITION BY (dt)
REFRESH ASYNC EVERY(INTERVAL 1 HOUR)
AS 
SELECT dt, region, SUM(revenue) 
FROM iceberg_catalog.source_db.base_table
GROUP BY dt, region;

────────── 2. Status of IVM (Incremental View Maintenance) While the current "best method" (PCT) is partition-level, StarRocks is transitioning to true Incremental View Maintenance (IVM), which operates at the row/delta level. * Current Status (v4.1+): StarRocks has introduced

refresh_mode = 'INCREMENTAL'

for MVs. * Row-Level Processing: Instead of re-computing an entire partition, it only processes the rows added in the new Iceberg snapshots. * Append-Only Support: Currently, this mode is optimized for append-only data. If the base table undergoes

UPDATE

or

DELETE

operations, the system typically falls back to partition-level refresh (if set to

AUTO

) or fails (if set to strictly

INCREMENTAL

). * Supported Operators: Phase 1 of IVM supports

SELECT

,

FILTER

,

INNER JOIN

, and basic aggregations (

SUM

,

COUNT

,

MIN

,

MAX

). ────────── 3. How IVM Will Make This More Efficient IVM will significantly improve efficiency in the following ways: 1. Lower Resource Consumption: Instead of scanning and overwriting 1TB of data because 1MB changed in a partition (PCT), IVM only reads and writes that 1MB delta. 2. Write Efficiency to Iceberg: In a PCT refresh, StarRocks must overwrite the entire partition's files. With IVM, StarRocks can perform an Iceberg Append or Merge operation, which is much faster and reduces I/O on the data lake storage. 3. Roadmap (v4.x): * Broader Operator Support: Adding support for Outer Joins and more complex nested queries. * Delete/Update Support: Expanding the incremental engine to handle

DELETE

and

UPDATE

markers from Iceberg/Paimon, allowing the MV to update row-by-row even for modified historical data. * Cross-Catalog IVM: Enhancing the ability to maintain MVs incrementally across different catalogs (e.g., source in Iceberg, target in StarRocks native, or vice versa). Summary: For now, use Partitioned External MVs in the Iceberg catalog. If you are on v4.1+ and your data is append-only, you can experiment with

PROPERTIES("refresh_mode" = "INCREMENTAL")

to move from partition-level to row-level efficiency. References • sql-reference/sql-statements/materialized_view/CREATE_MATERIALIZED_VIEW.md • using_starrocks/async_mv/use_cases/data_lake_query_acceleration_with_materialized_views.md • data_source/catalog/iceberg/iceberg_catalog.md References

@Rocky what is the performance tradeoff using external MVs from within Starrocks? Can it be close to native using cache?

The short answer is yes. While there is a technical performance gap, StarRocks' Data Cache (Block Cache) is designed to bridge that gap, often bringing external Iceberg MV performance within 80-120% of native table performance for analytical workloads. Here is the breakdown of the tradeoffs and how the cache levels the playing field: 1. The Performance Tradeoff: External vs. Native Feature | Native StarRocks MV | External Iceberg MV Storage Format | StarRocks Segment (Optimized) | Parquet/ORC in Iceberg Indexing | Prefix, Bitmap, & ZoneMap | Iceberg Min/Max (ZoneMap) Data Locality | Local SSD (Pinned) | Remote S3/HDFS (Cached on demand) Maintenance | StarRocks manages all IO | Shared (Spark/Flink can also write) Advanced Ops | Z-Order, Colocate Joins | Z-Order (if written by Spark/Flink) The "Native" Edge: Native tables use Prefix Indexes and Bitmap Indexes, which can skip significantly more data than Iceberg's standard min/max pruning. If your query relies on highly selective filters (point lookups), native storage will always be faster. 2. Can it be "Close to Native" with Cache? For large-scale analytical scans (aggregations, joins), the performance becomes very close because of two main StarRocks features: * Data Cache (Block Cache): This caches the raw Parquet data blocks from Iceberg onto the local NVMe/SSD of your StarRocks BE nodes. Once the cache is "warm," StarRocks' vectorized engine reads from local disk at speeds similar to native storage. In community benchmarks (SSB/TPC-H), cached external data often matches native performance. * Vectorized Execution: StarRocks uses the same high-performance C++ vectorized engine for both native and external data. The compute phase is identical; only the "IO fetch" phase differs. Caching eliminates the IO fetch overhead. 3. Efficiency vs. Performance As you noted, External MVs are often "more efficient" in terms of total system overhead: 1. Maintenance Efficiency: You don't have to load data twice (once into Iceberg and once into StarRocks). 2. Incremental Refresh: StarRocks can use Iceberg's snapshot deltas to refresh only changed partitions (PCT) or even just changed rows (IVM in v4.1+). 3. Cross-Engine Compatibility: The data is accessible to Spark/Trino without export costs. 4. Recommendation: When to stay External? * Use External Iceberg MVs if: You want to avoid data duplication, your data is already in a lakehouse, and you are primarily doing large scans/aggregations. * Move to Native MVs if: You need sub-second response times for high-concurrency "needle-in-a-haystack" queries that require Bitmap/Prefix indexes, or if you need Colocate Joins (which require StarRocks to control the physical distribution of data across nodes). Pro-Tip: If you use External Iceberg MVs, ensure you enable the Metadata Cache (

enable_iceberg_metadata_disk_cache = true

) in addition to the Data Cache. This speeds up the "planning" phase of the query by keeping Iceberg manifest files in StarRocks' memory/local disk. References • using_starrocks/async_mv/use_cases/data_lake_query_acceleration_with_materialized_views.md • data_source/data_cache.md • data_source/catalog/iceberg/iceberg_catalog.md • deployment/shared_data/feature-support-shared-data.md References • best_practices/table_clustering.md • knowledge/usage/best-practice.md • table_design/indexes/indexes.md