@Rocky I have a question about Materialized Views in StarRocks. If a Materialized View (MV) is dropped while it is being used for query rewrite, can this cause issues with queries on the base table? For example, is there a possibility that a query might fail during the rewrite process? Also, is this operation atomic? In other words, when an MV is deleted, does StarRocks guarantee that queries will either fully use the MV or fully fall back to the base table without any inconsistency or failure?

When you drop a Materialized View (MV) in StarRocks, the operation is handled through the Frontend (FE) metadata management. Here is how it behaves regarding query rewrites and concurrency: 1. Potential for Query Failure Yes, there is a small window where a query might fail if an MV is dropped exactly while a query is being processed. * During Planning: If the optimizer has already selected the MV for a rewrite but the

DROP

operation completes before the plan is finalized or sent to the Backends (BEs), the resulting query plan may reference a Table ID or Tablet IDs that no longer exist in the active metadata. In this case, the query will typically fail with an error such as

Table not found

or

Unknown table id

. * During Execution: If the query plan has already been dispatched to the BEs and is currently running, the BEs are working with specific tablet versions. While StarRocks eventually deletes the data, the query might still finish if the tablet metadata was already pinned for that session, but usually, a schema/metadata change of this nature will lead to a

Version Expired

or similar execution error. 2. Is the Operation Atomic? The

DROP

operation itself is atomic within the StarRocks catalog—once it's gone, no new queries will attempt to use it. However, there is no "transparent fallback" if the drop happens mid-flight. StarRocks does not guarantee that a query which has already entered the rewrite phase will automatically "re-plan" and fall back to the base table if the MV disappears during that specific session's lifecycle. * Before the Drop: Queries use the MV. * After the Drop: New queries use the base table. * During the Drop: A query caught in the middle may fail rather than falling back. 3. Best Practices to Avoid Issues If you are concerned about production stability when removing an MV, I recommend the following sequence: 1. Deactivate the MV first: Run

ALTER MATERIALIZED VIEW <mv_name> INACTIVE;

. This prevents the optimizer from using the MV for any new query rewrites while keeping the metadata/data intact. 2. Monitor: Check for any remaining long-running queries that might be using the MV. 3. Drop: Once you are sure no active queries are relying on it (or after a safe grace period), execute the

DROP MATERIALIZED VIEW <mv_name>;

. By setting it to

INACTIVE

first, you ensure that StarRocks gracefully ignores the MV for all new planning requests, effectively falling back to the base tables immediately for new traffic. References • using_starrocks/Materialized_view-single_table.md • using_starrocks/async_mv/Materialized_view.md • sql-reference/sql-statements/materialized_view/DROP_MATERIALIZED_VIEW.md • knowledge/usage/best-practice.md • using_starrocks/async_mv/troubleshooting_asynchronous_materialized_views.md • using_starrocks/async_mv/feature-support-asynchronous-materialized-views.md

@Rocky Then, if queries are actively running on the base table, does creating a Materialized View (MV) in real time have any impact on query execution?

Yes, creating a Materialized View (MV) in StarRocks is designed to be a non-blocking asynchronous operation, so it will not stop or block your active queries. However, there are some performance considerations to keep in mind regarding resource consumption. Here is a detailed breakdown of the impact: 1. No Locking of Base Tables In StarRocks, both synchronous (rollup) and asynchronous MVs use an asynchronous creation process. * Synchronous MVs (Single-table rollup): When you run

CREATE MATERIALIZED VIEW

, the system submits a background job to build the data. This job reads from the base table using a specific data version (MVCC), so it does not lock the base table for reads or writes. * Asynchronous MVs (Multi-table/Complex): The creation statement itself mainly defines the metadata. The actual data population happens during a

REFRESH

task. This task operates like a standard

INSERT INTO ... SELECT ...

query and does not block other queries. 2. Resource Competition (CPU, Memory, I/O) While the base table isn't locked, the process of building the MV involves scanning the base table and computing results. This consumes system resources: * I/O and CPU: If the base table is very large, the initial build process will perform a full table scan. This can increase disk I/O and CPU usage, which might lead to a slight increase in latency for other concurrently running queries if the cluster is already near its capacity. * Memory: Complex aggregations or joins during MV creation will consume memory. 3. Impact on Data Loading * Synchronous MVs: Once a synchronous MV is created, every subsequent data load into the base table will also update the MV. This can slightly increase the latency of your load jobs (e.g., Stream Load or Broker Load) because the system now has more work to do for each batch. * Asynchronous MVs: These do not affect data loading speed directly, as they are refreshed independently based on the strategy you define (

ASYNC

or

MANUAL

). 4. How to Minimize Impact If you are concerned about the impact on a production system, StarRocks provides features to manage this: * Resource Groups (for Async MVs): You can assign MV refresh tasks to a specific Resource Group. This allows you to limit the CPU and memory that the MV building process can use, ensuring that your user-facing queries always have priority.

sql
    PROPERTIES (
        "resource_group" = "your_limited_resource_group"
    )

* Off-Peak Refresh: For Asynchronous MVs, you can schedule the initial refresh or periodic refreshes during off-peak hours to avoid resource contention. Summary * Does it block queries? No. * Does it impact performance? Potentially, due to resource usage during the build/refresh process. * Is it safe? Yes, it is safe to run on active tables, but monitoring cluster resource usage (CPU/IO) during the initial build is recommended. You can monitor the progress of a synchronous MV creation using:

sql
SHOW ALTER MATERIALIZED VIEW;

And for asynchronous MVs, you can check the

information_schema.task_runs

table. References • sql-reference/sql-statements/materialized_view/CREATE_MATERIALIZED_VIEW.md