@Rocky if an MVs target is a primary key table, does the refresh do an INSERT or an INSERT OVERWRITE?

In StarRocks, the refresh behavior for an Asynchronous Materialized View (Async MV) depends on the refresh mode, but in the vast majority of cases—including when the target MV is defined as a Primary Key table—it performs an

INSERT OVERWRITE

. Here is the breakdown of how it works: 1. Default Behavior (Partition-level Refresh) By default, Async MVs use Partition Change Tracking (PCT). When a refresh is triggered (either manually or automatically), StarRocks identifies which partitions in the base tables have changed and then executes an

INSERT OVERWRITE

on the corresponding partitions of the Materialized View. * Why

INSERT OVERWRITE

? This ensures that the target partition is completely replaced by the fresh result of the MV’s query. It guarantees that the data in the MV exactly matches the current state of the base tables for those partitions, removing any stale data. * Primary Key MVs (StarRocks 3.3+): Even if you have defined your MV with a Primary Key (a feature introduced to support query rewrite on PK tables), the refresh still uses

INSERT OVERWRITE

at the partition level. 2. Incremental Refresh (IVM Mode) In very recent versions (StarRocks 3.4+ or 4.1 previews), a new Incremental View Maintenance (IVM) mode was introduced (

refresh_mode = 'INCREMENTAL'

). * In this specific mode, StarRocks processes row-level deltas. * If the target MV is a Primary Key table, the refresh logic behaves like a row-level

INSERT

(specifically a Full Upsert), where new rows are added and existing rows with the same primary key are updated. _ _Note: This mode is specialized and has more restrictions on the types of queries supported compared to the standard PCT mode.* 3. Synchronous Materialized Views (Rollup) If you are referring to a Synchronous MV (Rollup), there is no "INSERT" or "INSERT OVERWRITE" step. These are updated synchronously during the initial data load into the base table as part of the same transaction. However, StarRocks currently only supports Synchronous MVs on Duplicate Key and Aggregate tables, not Primary Key tables. Summary * Most common scenario: It uses

INSERT OVERWRITE

(replaces entire partitions to ensure consistency). * Incremental mode (IVM): It uses row-level

INSERT

(Upsert behavior) to apply specific deltas. To verify what your specific MV is doing, you can check the

DEFINITION

or

EXTRA_MESSAGE

columns in the

information_schema.task_runs

table:

sql
SELECT query_id, task_name, definition, extra_message 
FROM information_schema.task_runs 
ORDER BY create_time DESC;

You will typically see an

INSERT OVERWRITE

statement in the

definition

field for the refresh task. References • table_design/table_types/primary_key_table.md • best_practices/primarykey_table.md

@Rocky assume we're ingesting 4000 batches of records every 5m. I assume we're better off running MVs on fixed intervals rather than on data change. With on data change, INSERT OVERWRITE would lean to partitions constantly being refreshed, likely not fast enough to keep up with the number of times the MV is ran? What happens when multiple data changes arrive while a refresh is still running?

You are correct in your assessment. Running a Materialized View (MV) on every data change with such a high ingestion frequency (4,000 batches every 5 minutes) would likely overwhelm the system. Here is how StarRocks handles this scenario and why your intuition about fixed intervals is correct: 1. The "On Data Change" Problem When you set

REFRESH ASYNC

(without an interval), StarRocks monitors the base tables. Every time a transaction commits (like your 4,000 ingestion batches), it flags the MV as needing a refresh. * Performance Impact: If you are ingesting 13 batches per second, the MV would theoretically try to trigger 13 times a second. Since an

INSERT OVERWRITE

on a partition involves scanning, calculating, and writing data, the overhead of starting a new refresh task for every small batch is extremely high. * Result: You will likely see a massive backlog of tasks in

information_schema.task_runs

, and your MV data will be "perpetually stale" because the refresh can't keep up with the commit rate. 2. What happens when changes arrive during a running refresh? StarRocks has a built-in protection mechanism called Task Merging: * Single Execution: Only one refresh task for a specific MV can run at any given time. * Pending & Merged Tasks: If a new data change occurs while a refresh is already running: 1. The system sees that a task is already in progress. 2. It creates a "Pending" task for the next run. 3. If multiple changes arrive while that first task is still running, all those subsequent triggers are merged into a single pending task. 4. Once the current running task finishes, the system will start the "merged" task, which will pick up all the changes that happened since the first task started. While this merging helps prevent an infinite queue of identical tasks, with 4,000 batches every 5 minutes, you are still essentially forcing the MV to run "back-to-back" indefinitely, which consumes significant CPU and IO. 3. Best Practice: Fixed Intervals For high-concurrency ingestion, it is highly recommended to use a fixed interval (e.g., every 5 or 10 minutes) or a manual trigger.

sql
-- Refresh at a fixed interval
CREATE MATERIALIZED VIEW my_mv
REFRESH ASYNC EVERY (INTERVAL 5 MINUTE)
AS ...

Why this is better: * Batching: It allows 5 minutes' worth of ingestion (thousands of batches) to accumulate. The MV then performs a single

INSERT OVERWRITE

to update the affected partitions once. * Efficiency: It drastically reduces the metadata overhead and the frequency of partition version creation in the underlying storage. * Consistency: StarRocks still tracks which partitions changed. If you set an interval of 5 minutes but no data changed during that window, StarRocks will skip the refresh entirely to save resources. 4. Tuning for High Frequency If you find that the refresh is still taking too long even on an interval, consider these properties: *

partition_refresh_number

: By default, this might refresh many partitions at once. Setting this to a smaller number (e.g.,

1

) tells StarRocks to refresh one partition at a time, which can prevent long-running transactions from timing out. * Resource Groups: Use a dedicated resource group for the MV to ensure that the constant

INSERT OVERWRITE

operations don't starve your user queries of CPU/Memory. Summary With 4,000 batches every 5 minutes, do not use "on data change." Use a scheduled interval. The system will merge any changes that occur while a refresh is running, but an interval-based approach is much more stable for the system's metadata and resource management. References • administration/management/BE_configuration.md • using_starrocks/async_mv/troubleshooting_asynchronous_materialized_views.md

@Rocky if we had a raw landing table for ingested events, and then say a dozen materialised views that extracted events of a given type, transformed them, and inserted them into downstream primary key tables following a star schema, would there be significant overhead from these dozen MVs running every 5 minutes or so? What would a sensible partition strategy be?

Running a dozen Materialized Views (MVs) every 5 minutes on a Primary Key (PK) table is a common pattern in StarRocks, but it requires a careful Partition Strategy to avoid resource exhaustion. Here is the breakdown of the overhead and the recommended strategy. 1. The Overhead: What to Expect With 12 MVs refreshing every 5 minutes, you are essentially triggering 12

INSERT OVERWRITE

operations at a high frequency. * Compaction Pressure: Since your target tables are Primary Key tables, every refresh creates new versions of data. StarRocks must then perform "Primary Key Compaction" to merge these versions and update the primary key index. Doing this 12 times every 5 minutes can lead to high CPU and IO utilization. * Task Management: StarRocks manages these as internal tasks. If one MV refresh takes 4 minutes, and another starts at 5 minutes, you are running at nearly 100% duty cycle for that task. * Memory: If multiple MVs trigger simultaneously, they will compete for the memory allocated to the

default_mv_wg

(Resource Group). 2. Sensible Partitioning Strategy The most effective way to minimize overhead is Partition Alignment. This ensures that StarRocks only refreshes the specific data that changed, rather than the entire table. A. The Raw Landing Table Partition your raw table by a time-based column (e.g.,

event_time

). * Granularity: Use

HOURLY

or

DAILY

depending on your data volume. For 4,000 batches every 5 minutes, Hourly partitions are usually best to keep partition sizes manageable (aim for 10GB–100GB per partition). B. The Downstream Materialized Views When creating your 12 MVs, align their partitions with the raw table.

sql
CREATE MATERIALIZED VIEW mv_event_type_a
PARTITION BY event_date  -- Must match a column from the base table
REFRESH ASYNC EVERY (INTERVAL 5 MINUTE)
PROPERTIES (
    "partition_refresh_number" = "1" -- Refresh 1 partition at a time to reduce spikes
)
AS 
SELECT ... FROM raw_landing WHERE event_type = 'A';

* How it helps: When new data arrives in the

14:00

partition of the raw table, StarRocks tracks this. During the refresh, it will only execute

INSERT OVERWRITE

on the

14:00

partition of the MV. It will not touch historical data. C. Handling the Star Schema (Joins) In a star schema, your MV likely joins the

raw_landing

(Fact) with

dimension_tables

. * Reference Table: Specify the

raw_landing

table as the partition reference. * Dimension Changes: By default, if a dimension table changes, the MV might try to refresh all partitions. To prevent this, use the property

excluded_trigger_tables

.

sql
PROPERTIES (
    "excluded_trigger_tables" = "dim_users, dim_products"
)

This tells StarRocks: "Only trigger a refresh based on new events in the raw table; ignore updates to the dimension tables." 3. Key Optimizations for your Case 1. Staggered Starts: If all 12 MVs start at the exact same second, you'll see a massive CPU spike. You can manually stagger them or rely on the

default_mv_wg

concurrency limits to queue them. 2. Primary Key Compaction Tuning: If you see "too many versions" errors, you may need to increase the compaction threads in `be.conf`: *

update_compaction_num_threads_per_disk

(default 1, increase to 2 or 4). *

update_compaction_per_tablet_min_interval_seconds

(reduce if you need faster merging). 3. Partition Refresh Number: Set

"partition_refresh_number" = "1"

. This prevents the MV from trying to refresh multiple hours/days of data in a single massive transaction, which is safer for memory. Summary Recommendation * Partition Key: Use the same

DATE

or

DATETIME

column for the raw table and all 12 MVs. * Refresh Mode: Use

REFRESH ASYNC EVERY (INTERVAL 5 MINUTE)

. * Star Schema: Use

excluded_trigger_tables

for your dimensions so that only the new incoming "raw events" drive the refresh cycle.

@Rocky You mentioned earlier that IVM mode only supports Iceberg currently. Are there plans for native tables to be supported? If so, when?

To clarify my earlier point, you are correct that the Incremental View Maintenance (IVM) mode (

refresh_mode = 'INCREMENTAL'

) is currently focused on Apache Iceberg (specifically append-only tables). For Native StarRocks tables, the current "incremental" behavior is actually Partition-level Refresh (PCT), which refreshes entire partitions rather than individual rows. Is IVM coming to Native Tables? Yes. Supporting true row-level IVM for native StarRocks tables is a top priority on the roadmap. * Current Phase (StarRocks 3.4 / 4.1): The initial IVM framework (Phase 1) was built to support external catalogs like Iceberg first. This was chosen because Iceberg’s built-in snapshot and versioning metadata make it easier to track row-level deltas across complex joins. * Next Phases (2025–2026 Roadmap): True row-level IVM for native tables is officially on the roadmap for the StarRocks 4.x series. * Native Storage (Shared-data): The StarRocks 2026 roadmap explicitly lists "Incremental materialized view" as a key feature for the native storage engine. * Goal: The aim is to allow the same

INCREMENTAL

refresh mode to work on native Duplicate and Primary Key tables, allowing for sub-second data freshness without the overhead of partition-level

INSERT OVERWRITE

. Why the wait for Native Tables? Unlike Iceberg, which is append-only in this context, native StarRocks tables (especially Primary Key tables) handle complex updates and deletes internally. Building a row-level IVM for these requires: 1. Delta Tracking: A mechanism to capture row-level changes (CDC-like) from the native storage engine. 2. State Management: The ability for the MV to maintain intermediate "states" for aggregations (e.g., keeping track of the

SUM

and

COUNT

so a single new row can update the total without scanning). What should you do in the meantime? Since you are likely using native tables today, you should continue to use Partition-level Refresh: * Alignment: Ensure your MVs are partitioned the same way as your base tables. * PCT: StarRocks will automatically use Partition Change Tracking (PCT) to only refresh the partitions that have changed. While this is "partition-level" and not "row-level," it is still highly efficient and is the standard way to handle large-scale near-real-time data in StarRocks today. Summary: Native table support for row-level IVM is currently in active development and is expected to roll out in the 4.x release cycle (late 2025/early 2026). References • sql-reference/sql-statements/materialized_view/CREATE_MATERIALIZED_VIEW.md • using_starrocks/async_mv/use_cases/create_partitioned_materialized_view.md • using_starrocks/async_mv/materialized_view_task_run_details.md • using_starrocks/async_mv/use_cases/data_lake_query_acceleration_with_materialized_views.md