http://starrocks.io logo
Join Slack
Powered by
<@U0A71G31CDV> for low latency time series dashboa...
# questions-and-troubleshooting
c
@Rocky for low latency time series dashboard use case are SYNC MVs more efficient than ASYNC?
r
For a low-latency time-series dashboard in StarRocks, the choice between Synchronous (SYNC) MVs and Asynchronous (ASYNC) MVs depends on whether your priority is data freshness or query complexity. 1. Synchronous Materialized Views (Rollup) In StarRocks, SYNC MVs (often referred to as Rollups) are essentially specialized indices that are updated at the same time as the base table. * Best for: * Real-time requirements: Data is available in the MV the instant it is loaded into the base table (zero lag). * Simple aggregations: If your dashboard only uses 
SUM
, 
COUNT
, 
MIN
, 
MAX
, 
BITMAP_UNION
(for unique counts), or 
HLL_UNION
on a single table. * Single-table metrics: Does not support joins. * Efficiency: * Query Latency: Extremely low; StarRocks automatically routes queries to the rollup. * Ingestion Latency: Adds a small overhead to data loading since the rollup must be updated in the same transaction. * Maintenance: Zero. It is self-managing. 2. Asynchronous Materialized Views ASYNC MVs are more powerful but operate on a refresh schedule (manual, interval, or triggered). * Best for: * Complex Modeling: If your dashboard requires joining metrics with metadata tables (e.g., 
sensor_data
JOIN 
device_info
). * Time-Bucket Aggregation: Supports functions like 
date_trunc
or 
time_slice
in the MV definition, which is critical for time-series granularity (e.g., rolling up 1-second data into 1-minute buckets). * Partitioned Refresh: You can align the MV partitions with the base table. This is highly efficient for time-series because StarRocks can refresh only the "latest" time partition rather than the entire dataset. * Efficiency: * Query Latency: Extremely low (similar to SYNC) since data is pre-computed. * Data Freshness: Higher lag (minutes or hours) depending on your refresh interval. * Query Rewrite: Supports advanced "Query Rewrite" which can transparently accelerate complex SQL. ────────── Comparison Summary Feature | SYNC MV (Rollup) | ASYNC MV Data Freshness | Real-time (Synchronous) | Near-real-time (Delayed) Multi-table Joins | No | Yes Aggregate Functions | Limited (SUM, COUNT, etc.) | Full Support Time-Series Efficiency | High (simple metrics) | Excellent (partitioned refresh) Maintenance | None (Automatic) | Requires refresh strategy config Recommendation * Use SYNC MVs if your dashboard shows real-time metrics from a single table and you cannot tolerate any data lag. * Use ASYNC MVs if you need to join tables, perform complex transformations, or use time-series functions like 
time_slice
. To minimize the latency impact of ASYNC MVs, use Partitioned Refresh to keep only the most recent data updated. References • using_starrocks/async_mv/use_cases/create_partitioned_materialized_view.md • sql-reference/sql-functions/date-time-functions/date_trunc.md • using_starrocks/async_mv/use_cases/query_rewrite_with_materialized_views.md
c
@Rocky what if the use case meets all the criteria for SYNC but cardinality is high. With ASYNC we could do infrequent refreshes.
r
If your use case meets the criteria for a SYNC MV (single table, simple aggregations) but has high cardinality, then ASYNC MVs are likely more efficient for the overall system, especially if you can tolerate some data lag. Here is the breakdown of why cardinality and refresh strategy change the efficiency equation: 1. The "Aggregation Ratio" Problem The efficiency of any materialized view comes from the Aggregation Ratio (Base Table Rows / MV Rows). * Low Cardinality: 1,000,000 rows collapse into 1,000 rows. A SYNC MV is very efficient because it only writes a tiny amount of data to disk during ingestion. * High Cardinality: 1,000,000 rows collapse into 900,000 rows. A SYNC MV will force the system to perform complex aggregation and write nearly double the data for every single 
INSERT
or 
STREAM LOAD
. 2. Ingestion Overhead (Write Amplification) * SYNC MV (Synchronous): Updates are part of the load transaction. If you have high-cardinality data coming in at high frequency, the SYNC MV will significantly increase ingestion latency and CPU usage on your BE nodes because it has to sort and aggregate that high-cardinality data in real-time for every batch. * ASYNC MV (Asynchronous): Decouples ingestion from aggregation. You can load data into the base table at maximum speed. The heavy lifting of aggregating high-cardinality data happens later during the refresh cycle. 3. Resource "Amortization" with ASYNC MVs If you refresh an ASYNC MV every 30 minutes instead of synchronously: * Batch Efficiency: It is much cheaper to aggregate 30 minutes of data once than it is to aggregate 1,800 separate 1-second batches. * I/O Savings: You avoid the constant "write amplification" during the peak ingestion window. * Partitioned Refresh: For time-series data, you can use Partitioned ASYNC MVs. StarRocks will only refresh the partitions where data has changed (e.g., the current hour/day), meaning you don't waste resources re-processing historical high-cardinality data. 4. When to still choose SYNC (Rollup)? Even with high cardinality, choose SYNC MVs ONLY if: 1. Strict Real-time: Your dashboard cannot be even 1 minute out of sync. 2. Point Queries: You are using the MV primarily as a "Better Sort Key" to speed up specific filters, rather than just for aggregation. Summary Recommendation Factor | SYNC MV (Rollup) | ASYNC MV Ingestion Performance | Slower (Aggregation per load) | Faster (Aggregation decoupled) System Resource Usage | High (Continuous CPU/IO) | Lower (Batch processing) High Cardinality Fit | Poor (High write amplification) | Good (Amortized cost) Use Case | Real-time, Low-cardinality | Near real-time, High-cardinality Pro Tip: If you choose ASYNC for a time-series dashboard, use 
PARTITION BY
on the time column and set a 
REFRESH
interval (e.g., 
EVERY 5 MINUTE
). This gives you the best balance of "near real-time" feel and "high cardinality" efficiency. References • table_design/indexes/Bitmap_index.md • table_design/data_distribution/Data_distribution.md • table_design/table_types/aggregate_table.md
c
@Rocky we currently use ASYNC with refesh interval one minute. To try SYNC instead should we make current INACTIVE then create a new SYNC view?
r
To transition from an ASYNC MV to a SYNC MV (Rollup), you don't strictly have to make the current one inactive to start the process, but there are some practical steps you should follow to ensure a smooth migration and avoid resource waste. Recommended Migration Steps 1. Check for Name Conflicts Synchronous Materialized Views (Rollups) and Asynchronous Materialized Views share the same namespace within a database. * If you want to use a new name: You can create the SYNC MV immediately while the ASYNC one is still running. * If you want to reuse the same name: You must first rename or drop the ASYNC MV.
Copy code
sql
    -- Rename the existing ASYNC MV to free up the name
    ALTER MATERIALIZED VIEW my_dashboard_mv RENAME my_dashboard_mv_old;
2. Create the SYNC MV Create the SYNC MV using the standard syntax. Note that for SYNC MVs, you do not include the 
REFRESH
clause.
Copy code
sql
CREATE MATERIALIZED VIEW my_dashboard_mv AS
SELECT event_time, sensor_id, SUM(value), COUNT(id)
FROM raw_sensor_data
GROUP BY event_time, sensor_id;
3. Monitor the Build Process Unlike ASYNC MVs which create a table structure immediately, a SYNC MV is a "Rollup" index that is built as a background task. It is not active until the initial build of historical data is complete.
Copy code
sql
-- Monitor the progress
SHOW ALTER MATERIALIZED VIEW;
Look for the State column. It will show 
RUNNING
or 
PENDING
. Once it reaches 
FINISHED
, the SYNC MV is active and will stay perfectly synchronized with every new data load. 4. Deactivate or Drop the ASYNC MV Once the SYNC MV is 
FINISHED
, you should stop the ASYNC MV to save system resources (CPU/IO). Since you are refreshing every 1 minute, the ASYNC MV is likely consuming significant overhead. * To pause (keep data but stop refreshes):
Copy code
sql
    ALTER MATERIALIZED VIEW my_dashboard_mv_old INACTIVE;
* To remove completely:
Copy code
sql
    DROP MATERIALIZED VIEW my_dashboard_mv_old;
Why "Inactive" is a good intermediate step Setting the ASYNC MV to 
INACTIVE
is a safe way to "turn off" the 1-minute refresh overhead without deleting the pre-computed data. If you find the SYNC MV isn't performing as expected (e.g., if the high cardinality makes ingestion too slow), you can simply drop the SYNC MV and set the ASYNC one back to 
ACTIVE
. Critical Reminders for SYNC MVs * Ingestion Impact: Remember that SYNC MVs are updated during the 
LOAD
transaction. If you have very frequent, small loads, you might see a slight increase in load latency. * Query Routing: You don't need to change your dashboard's SQL. StarRocks will automatically route queries from the base table to the SYNC MV if it determines the MV can satisfy the query. * Single Table Only: Ensure your dashboard query hasn't evolved to include Joins, as SYNC MVs will fail to create if a Join is present. References • using_starrocks/Materialized_view-single_table.md • sql-reference/sql-statements/materialized_view/CREATE_MATERIALIZED_VIEW.md • sql-reference/sql-statements/materialized_view/ALTER_MATERIALIZED_VIEW.md References • using_starrocks/async_mv/Materialized_view.md
c
@Rocky do SYNC MVs declare partitioning or is that automatically managed by system.
r
For SYNC Materialized Views (also known as Rollups), partitioning and bucketing are automatically managed by the system. You do not (and cannot) declare them in the 
CREATE MATERIALIZED VIEW
statement. Here is why and how it works: 1. Automatic Inheritance SYNC MVs are essentially specialized "indices" or physical extensions of the base table rather than independent tables. Because they are updated synchronously during the same transaction as the base table load, they inherit the partitioning and distribution (bucketing) of that base table. * If your base table is partitioned by 
day
, the SYNC MV is physically stored within those same daily partitions. * The system ensures that the data in the MV is co-located with the corresponding raw data on the same BE nodes. 2. Syntax Difference If you look at the 
CREATE
syntax for both types, the difference is clear: * ASYNC MV: You must or should declare 
PARTITION BY
to define how the MV manages its own storage and incremental refreshes.
Copy code
sql
    CREATE MATERIALIZED VIEW async_mv
    REFRESH ASYNC
    PARTITION BY event_date  -- <--- Required for manual/efficient refresh
    AS SELECT ...;
* SYNC MV (Rollup): You only provide the query. Any attempt to add 
PARTITION BY
will result in a syntax error.
Copy code
sql
    CREATE MATERIALIZED VIEW sync_mv
    AS SELECT k1, SUM(v1)
    FROM base_table
    GROUP BY k1;  -- <--- No partitioning clause allowed
3. Impact on your Use Case Since you are considering switching from ASYNC to SYNC for a low-latency dashboard: * Maintenance: You won't have to worry about partition alignment or "partition TTL" for the SYNC MV. If you drop an old partition from the base table, the corresponding data in the SYNC MV is deleted automatically. * Storage: Because it shares the base table's partitioning, it is very efficient for time-series "drop partition" operations. Summary Feature | SYNC MV (Rollup) | ASYNC MV Partitioning Declaration | Automatic (Inherited) | Manual (Declared by user) Distribution (Bucketing) | Automatic (Inherited) | Manual (Declared by user) Physical Nature | Index-like structure | Independent table-like structure Recommendation: Since SYNC MVs handle partitioning automatically, the migration is simpler—you just need to ensure your query is compatible (single table, specific aggregations). Just run the 
CREATE MATERIALIZED VIEW
without any partitioning clauses, and StarRocks will handle the rest. References • using_starrocks/Materialized_view-single_table.md • sql-reference/sql-statements/materialized_view/CREATE_MATERIALIZED_VIEW.md • using_starrocks/async_mv/feature-support-asynchronous-materialized-views.md References • sql-reference/sql-statements/materialized_view/CREATE_MATERIALIZED_VIEW.md • using_starrocks/async_mv/use_cases/create_partitioned_materialized_view.md
c
@Rocky how about properties. Which of these should I keep?
Copy code
"replicated_storage" = "true",
    "partition_refresh_number" = "6",
    "query_rewrite_consistency" = "LOOSE",
    "partition_ttl_number" = "721", -- ttl = 30 days + 1970 (0 ts) partition
    "replication_num" = "3",
    "storage_volume" = "builtin_storage_volume",
    "warehouse" = "default_warehouse"
r
When moving from an ASYNC MV to a SYNC MV (Rollup), you should drop almost all of those properties. SYNC MVs are not independent tables; they are physical "rollup indices" attached to your base table. They inherit their storage, partitioning, and replication settings directly from the base table. Here is the breakdown of why you should remove them: 1. Properties to DROP * 
partition_refresh_number
: DROP. SYNC MVs update automatically during every load. There is no "refresh task" to manage. * 
query_rewrite_consistency
: DROP. SYNC MVs are always strictly consistent because they are updated in the same transaction as your data load. There is no "staleness" to allow or disallow. * 
partition_ttl_number
: DROP. A SYNC MV exists inside the partitions of the base table. If a partition in the base table expires, it is automatically removed from the SYNC MV. * 
replicated_storage
& 
replication_num
: DROP. The SYNC MV is part of the base table's data distribution. It will automatically follow the replication factor you set when you created the base table. * 
storage_volume
: DROP. It will be stored in the same volume as the base table. * 
warehouse
: DROP. SYNC MVs use the compute resources of the ingestion process (loads) or the query process, not a dedicated background refresh warehouse. 2. How the Syntax Changes In StarRocks, the syntax for a SYNC MV is much simpler. It generally does not use a 
PROPERTIES
block at all. Your New Statement should look like this:
Copy code
sql
CREATE MATERIALIZED VIEW sync_view_name AS
SELECT 
    high_cardinality_column, 
    SUM(metric_a), 
    COUNT(metric_b)
FROM base_table
GROUP BY high_cardinality_column;
Summary of Differences Property | ASYNC MV | SYNC MV (Rollup) Consistency | Can be LOOSE/Stale | Always Strict Storage | Independent Table | Stored within Base Table Partitioning | Defined manually | Inherited from Base Table Refresh | Scheduled or Manual | Atomic with Data Load One thing to watch for: Since you mentioned High Cardinality earlier: creating a SYNC MV is a background 
ALTER TABLE
job. You can monitor its progress using:
Copy code
sql
SHOW ALTER MATERIALIZED VIEW;
Because the cardinality is high, the initial build (sorting and aggregating all existing data) might take some time. Once the state is 
FINISHED
, the view is active. References • using_starrocks/Materialized_view-single_table.md • sql-reference/sql-statements/materialized_view/CREATE_MATERIALIZED_VIEW.md • using_starrocks/async_mv/Materialized_view.md • sql-reference/sql-statements/materialized_view/SHOW_ALTER_MATERIALIZED_VIEW.md References • using_starrocks/Materialized_view-single_table.md
c
@Rocky I get the error 
The materialized view currently does not support const expr in select statement: {}. Please use Asynchronous Materialized View instead
r
The error 
The materialized view currently does not support const expr in select statement
occurs because Synchronous Materialized Views (Rollups) are physically stored as an index of the base table. Unlike Asynchronous MVs (which are independent tables), a SYNC MV must directly map its columns to actual data columns or aggregate expressions from the base table. It does not support "synthetic" columns created from literal constants (like 
1 AS type
or 
'my_val' AS label
). Why this happens A Synchronous MV is a "Rollup" index. Its primary job is to pre-aggregate existing table columns. Including a constant doesn't fit the physical storage model of a Rollup, as every row in the index is expected to be derived from data actually residing in the base table. How to fix it 1. Remove the constant from the MV The most common solution is to remove the constant column from the Materialized View definition and instead include it in your final query. * Old ASYNC MV SQL: 
SELECT 'Web' as source, user_id, SUM(price) ...
* New SYNC MV SQL: 
SELECT user_id, SUM(price) FROM ... GROUP BY user_id
* Dashboard Query: 
SELECT 'Web' as source, * FROM my_sync_mv
(StarRocks will automatically use the MV even if you add the constant in the final SELECT). 2. The "Column-Referencing" Workaround (For v3.1+) If you are on StarRocks v3.1 or higher, SYNC MVs support complex expressions. You can sometimes "trick" the system by referencing a column in a way that results in a constant. * Instead of: 
1 AS constant_col
* Try: 
(base_column * 0) + 1 AS constant_col
_ _Note: This is only recommended if you absolutely must have the column inside the MV for compatibility with an existing reporting tool that you cannot change.* 3. Use a Virtual Column / Generated Column (If available) If you truly need this constant to behave like a column, you could add it to the base table as a generated column (if your StarRocks version supports it) or simply as a standard column with a 
DEFAULT
value, and then include that column in the SYNC MV. Summary of SYNC MV restrictions If you are moving from ASYNC to SYNC, keep in mind these other common "gotchas" that will trigger similar errors: * No Joins: SYNC MVs only work on a single table. * No Window Functions: 
ROW_NUMBER()
, 
RANK()
, etc., are not allowed. * Aggregation Rules: Every non-aggregated column in the 
SELECT
must be in the 
GROUP BY
. * No 
HAVING
clause: Filtering must happen in the 
WHERE
clause (supported in v3.1.8+). If your dashboard logic relies heavily on constants, joins, or complex logic, you may need to stick with an Asynchronous MV but set a very aggressive refresh interval (though this has higher overhead than a SYNC MV). References • sql-reference/sql-statements/materialized_view/CREATE_MATERIALIZED_VIEW.md • using_starrocks/Materialized_view-single_table.md • using_starrocks/async_mv/troubleshooting_asynchronous_materialized_views.md
c
@Rocky the problem is in using double time granularities to support nested minute and hour ASYNC MVs.
Copy code
date_trunc('minute', event_timestamp) AS event_minute,
    date_trunc('hour', event_timestamp) AS event_hour,
For sync do we jsut have 
date_trunc('minute',event_timestamp)
? Is ther no nesting in SYNC views?
r
The error you're seeing confirms that SYNC Materialized Views (Rollups) have much stricter requirements than ASYNC MVs. Specifically: 1. No Nesting for SYNC MVs Unlike ASYNC MVs, where you can build an "Hour MV" on top of a "Minute MV," SYNC MVs do not support nesting. * A SYNC MV must be created directly on a base table. * You cannot create a SYNC MV on top of another MV. * Solution: Create both the Minute and Hour SYNC MVs directly on the base table. Since they are synchronous, there is no "lag" between them, and the storage overhead is similar to nesting. 2. The "Const Expr" Error with 
date_trunc
 The error 
does not support const expr in select statement
is triggered because the Rollup engine (SYNC MV) is highly optimized for raw column data. Even in StarRocks v3.1+, which added support for some expressions, the use of literal constants like 
'minute'
or 
'hour'
inside 
date_trunc
can sometimes trigger this limitation depending on your specific version. How to fix the 
date_trunc
error: If you are on an older version or hitting this restriction, you have three options: Option A: Pre-calculate in the Base Table (Recommended) The most robust way to use SYNC MVs for multiple time granularities is to add the columns to your base table during the 
LOAD
process: 1. Add 
event_minute
and 
event_hour
as columns in your base table. 2. Create two SYNC MVs:
Copy code
sql
    -- MV 1
    CREATE MATERIALIZED VIEW mv_minute AS
    SELECT event_minute, SUM(value) FROM base_table GROUP BY event_minute;

    -- MV 2
    CREATE MATERIALIZED VIEW mv_hour AS
    SELECT event_hour, SUM(value) FROM base_table GROUP BY event_hour;
Option B: Use "Generated Columns" (v3.1+) If you don't want to change your ETL, you can add a Generated Column to the base table that automatically calculates the truncation. SYNC MVs can then be built on these generated columns. Option C: Stick with ASYNC MVs with a "Fast Refresh" If you need complex logic (like constants, nesting, or 
date_trunc
), you may want to stay with ASYNC MVs but set them to refresh extremely frequently:
Copy code
sql
PROPERTIES (
    "refresh_interval" = "60" -- Refresh every 60 seconds
)
Why was it working in ASYNC? ASYNC MVs are treated like full tables; the system can run a complex SQL query to populate them. SYNC MVs are "index-like" and are updated increment-by-increment during the data ingestion (memtable flush) phase, which is why the SQL allowed in them is much more restricted. Summary for your move to SYNC: 1. Drop nesting: Map both granularities back to the base table. 2. Remove constants: Ensure no 
'minute'
or other literals are in the 
SELECT
list if your version doesn't support them. 3. Check Version: If you are below v3.1, expressions like 
date_trunc
are generally not supported in SYNC MVs at all. References • using_starrocks/Materialized_view-single_table.md • release_notes/release-3.1.md • sql-reference/sql-statements/materialized_view/CREATE_MATERIALIZED_VIEW.md References • using_starrocks/Materialized_view-single_table.md • sql-reference/sql-statements/materialized_view/CREATE_MATERIALIZED_VIEW.md • release_notes/release-3.1.md -1) • using_starrocks/async_mv/feature-support-asynchronous-materialized-views.md References
c
@Rocky I will need to add two generated columns, Can I add them both at once?
r
Yes, you can add multiple generated columns, but there is a specific way to do it. While StarRocks generally allows adding multiple columns in one 
ALTER TABLE
statement, for Generated Columns (which require calculating data for existing rows), the safest and most standard way is to use a comma-separated list of 
ADD COLUMN
clauses or separate statements. Option 1: Single Statement (Recommended) You can chain multiple 
ADD COLUMN
clauses in a single 
ALTER TABLE
command. This is often more efficient as it can sometimes be processed in a single schema change task.
Copy code
sql
ALTER TABLE your_table_name
ADD COLUMN event_minute DATETIME AS date_trunc('minute', event_timestamp),
ADD COLUMN event_hour DATETIME AS date_trunc('hour', event_timestamp);
Option 2: Separate Statements If your version of StarRocks or your specific table configuration has trouble parsing the combined statement, you can simply run them one after the other. StarRocks handles these as background jobs.
Copy code
sql
ALTER TABLE your_table_name ADD COLUMN event_minute DATETIME AS date_trunc('minute', event_timestamp);
ALTER TABLE your_table_name ADD COLUMN event_hour DATETIME AS date_trunc('hour', event_timestamp);
Important Tips for Generated Columns: 1. Table Type: Generated columns are supported on Duplicate Key and Primary Key tables. They are not supported on Aggregate tables. 2. Order of Columns: Generated columns must be defined after all regular columns. StarRocks will automatically place them at the end of the schema if you don't specify a position. 3. Performance: Adding a generated column triggers a background job to calculate the values for all existing rows. You can monitor the progress using:
Copy code
sql
    SHOW ALTER TABLE COLUMN;
4. No Aggregates: The expression (like 
date_trunc
) cannot contain aggregate functions (e.g., 
SUM
or 
COUNT
). 5. Data Type: Ensure the data type you specify (e.g., 
DATETIME
) exactly matches the output type of the expression. Once these columns are added, you can then create your SYNC Materialized Views using these new columns directly, which avoids the "const expr" error you saw earlier! References • sql-reference/sql-statements/generated_columns.md • sql-reference/sql-statements/table_bucket_part_index/ALTER_TABLE.md References • sql-reference/sql-statements/table_bucket_part_index/ALTER_TABLE.md • sql-reference/sql-statements/generated_columns.md
c
@Rocky are there any properties I can adjust to scale the alter table operation after I add more compute nodes?
r
To scale and speed up 
ALTER TABLE
operations (like adding generated columns) after adding more compute nodes, you can adjust several parameters on both the FE and BE sides. Since 
ALTER TABLE
in StarRocks is an asynchronous job that processes data tablet-by-tablet, the speed depends on how many tablets can be processed in parallel across your cluster. 1. Increase Concurrency on BE Nodes The most direct way to use more of your new compute resources is to increase the number of threads each BE uses for schema changes. * 
alter_tablet_worker_count
(BE Config): This determines how many threads each BE allocates for Schema Change tasks. * Default: 3 * To adjust: Update 
be.conf
or use 
curl
to update it dynamically (if mutable):
Copy code
bash
        curl -X POST http://<be_ip>:<be_http_port>/api/update_config?alter_tablet_worker_count=10
* 
update_schema_worker_count
(BE Config, v3.2.3+): Sets the maximum number of worker threads for processing schema update tasks. * Default: 3 * To adjust: Increase this to allow more concurrent schema-update tasks if you are on a newer version. 2. Adjust Global Parallelism on FE The FE manages the job queue. If you have many partitions or tables being altered at once, the FE might be the bottleneck. * 
alter_max_worker_threads
(FE Config): Controls the maximum number of worker threads in the FE's AlterHandler. * Default: 4 * To adjust: Increase this in 
fe.conf
if you are running multiple 
ALTER
jobs concurrently. * 
alter_table_timeout_second
(FE Config): For very large tables, the default timeout (86400s / 24h) might be hit. Increase this if your table is massive. 3. Leverage "Fast Schema Evolution" (v3.2+) If you are adding columns, ensure Fast Schema Evolution is enabled. This feature makes adding/dropping columns a metadata-only operation (or significantly more efficient), which is much faster than a full data rewrite. * Property: 
enable_fast_schema_evolution = true
(Global FE config). * Note: For shared-data clusters (cloud-native), this is usually enabled by default. 4. Ensure Workload Distribution (Tablets & Buckets) StarRocks parallelizes the 
ALTER
job based on the number of tablets. * Rebalance first: After adding nodes, StarRocks will automatically start rebalancing tablets to the new nodes. It is best to wait for the rebalance to finish (check 
SHOW BACKENDS
) so that the 
ALTER TABLE
work is naturally distributed across all nodes. * Bucket Count: If your table has very few buckets (e.g., only 1 bucket for a huge partition), it will only use 1 thread on 1 BE, regardless of how many nodes you add. In that case, you might need to re-bucket the table (which requires an 
ALTER TABLE
itself) to increase parallelism for future operations. 5. Monitoring the Speed You can monitor the progress and see if it's actually using the threads you've allocated:
Copy code
sql
-- See the state and percentage of completion
SHOW ALTER TABLE COLUMN;

-- See how many tablets are currently being processed
SHOW PROC '/cluster_balance/history';
Summary Recommendation 1. Add BE nodes and wait for tablet rebalancing to finish. 2. Increase 
alter_tablet_worker_count
 to 
10
or higher on each BE (depending on CPU cores). 3. Ensure 
enable_fast_schema_evolution
 is set to 
true
. 4. Run your 
ALTER TABLE
command. References • administration/management/BE_configuration.md • administration/management/FE_configuration.md • sql-reference/sql-statements/table_bucket_part_index/ALTER_TABLE.md • best_practices/query_tuning/schema_tuning.md
c
@Rocky 
alter_tablet_worker_count
is immutable. The operation is running on a Shared Data cluster. Do I have to delete the cluster and resume from snapshot to use more compute?
r
No, you definitely do not need to delete the cluster or resume from a snapshot. In a StarRocks Shared-Data (cloud-native) cluster, you can scale both the number of nodes and the concurrency per node dynamically to speed up an 
ALTER TABLE
operation. Here is how you can scale the operation: 1. Update 
alter_tablet_worker_count
Dynamically While some FE configs are immutable, 
alter_tablet_worker_count
(which controls how many threads each node uses for schema change tasks) is mutable in most StarRocks versions. If you tried to change it via 
ADMIN SET FRONTEND CONFIG
and it failed, it’s because this is a BE/CN (Compute Node) property, not an FE property. You can update it across all compute nodes at once using this SQL:
Copy code
sql
-- Increase the number of threads for schema change on all nodes
UPDATE information_schema.be_configs 
SET VALUE = '10'  -- Default is 3; increase based on your CPU cores
WHERE name = 'alter_tablet_worker_count';
2. Scale Horizontally (Add More CN Nodes) In a shared-data architecture, adding more Compute Nodes (CN) is the intended way to scale. * The FE will distribute work: When you add new nodes, the FE will assign the remaining pending "tasks" (tablets that still need the generated columns calculated) to the new nodes. * No data migration needed: Since the data is in shared storage (S3/OSS), new nodes can immediately start processing tablets without waiting for a lengthy "rebalance" of data files. * Command:
Copy code
sql
    ALTER SYSTEM ADD COMPUTE NODE "host:port";
(Note: If you are using a managed cloud service, use their scaling UI/API instead). 3. Why you might see "Immutable" errors If you are seeing errors about immutability, you are likely hitting one of these two parameters which are fixed at startup: * 
alter_max_worker_threads
(FE Config): This limits the total number of worker threads in the FE's scheduler. Even if this is fixed at 4, each "thread" can submit hundreds of tasks to the CN nodes. It is rarely the bottleneck. * 
update_schema_worker_count
(CN Config): Added in v3.2.3, this specific thread pool is immutable. However, the core schema change logic still relies on 
alter_tablet_worker_count
, which is mutable. How to Monitor Progress To see if your scaling is working, check the progress and how many tablets are left:
Copy code
sql
SHOW ALTER TABLE COLUMN;
Look at the 
State
and 
Progress
columns. If you have many partitions, you will see the progress percentage increase faster as you add more nodes or increase the worker count. Summary of Action Plan: 1. Scale up nodes: Add more Compute Nodes to your cluster. 2. Increase concurrency: Run the 
UPDATE information_schema.be_configs
command above to increase threads to 8 or 10 per node. 3. Check `lake_compaction_max_tasks`: In shared-data, ensuring this is set to 
-1
(adaptive) or a high number helps the system manage the underlying file versions created during the 
ALTER
. Rocky's Tip: In shared-data mode, the bottleneck is often the I/O to the object store (S3/OSS). If you increase 
alter_tablet_worker_count
too high, you might hit I/O throttling from your cloud provider before you hit CPU limits. StartRocks' limits. StartRocks limits! 100% CPU. StartRocks CPU limits. StartRocks limits! References • administration/management/BE_configuration.md
c
@Rocky, I meant 
update_schema_worker_count
r
It’s understandable that seeing an "immutable" property like 
update_schema_worker_count
can be frustrating, but the good news is that you do not need to delete the cluster or resume from a snapshot. In a StarRocks Shared Data (Lake) cluster, you have two powerful ways to scale this operation without a restart: 1. Horizontal Scaling (The Preferred Way) Because your data is in shared storage (S3/OSS), adding compute nodes is the most effective way to speed up an 
ALTER TABLE
job. * How it works: StarRocks processes 
ALTER TABLE
jobs at the tablet level. The FE (Frontend) acts as a scheduler and distributes these tablet tasks across all available Compute Nodes (CNs). * Action: Simply add more CN nodes to your cluster. The FE will automatically detect the new nodes and begin dispatching the remaining pending tablet tasks to them. * Benefit: You don't need to change any "immutable" settings. If you have 3 nodes with 3 threads each (9 total), and you add 3 more nodes, you instantly have 18 threads working on the schema change. 2. Adjust the Mutable Property: 
alter_tablet_worker_count
 While 
update_schema_worker_count
(introduced in v3.2.3 for specific agent tasks) is immutable, the core property that controls schema change threads in StarRocks is 
alter_tablet_worker_count
, and it is mutable. You can increase this dynamically on all your nodes using a single SQL command:
Copy code
sql
UPDATE information_schema.be_configs 
SET VALUE = '8' -- Increase from the default 3
WHERE name = 'alter_tablet_worker_count';
(Note: Set this to roughly 25%-50% of your available CPU cores per node for the best balance.) Why 
update_schema_worker_count
is Immutable This property defines the maximum size of a specific internal thread pool created at startup. While you can't change the size of that specific pool without a restart, it is rarely the bottleneck for the entire cluster. In StarRocks, performance is gained through parallelism across nodes rather than just stacking threads on a single node. Summary Checklist for Your Operation: 1. Don't Recreate: No need to snapshot or delete. The cluster stays live. 2. Scale Out: Add more CN nodes. This is the "cloud-native" way to solve the problem. 3. Tweak `alter_tablet_worker_count`: Use the 
UPDATE
SQL above to maximize the work each node is doing. 4. Monitor: Check progress with:
Copy code
sql
    SHOW ALTER TABLE COLUMN;
Rocky's Tip: Adding a generated column is significantly faster than a traditional schema change because StarRocks uses an "optimized" path—it only calculates and writes the data for the new column and links it to existing files, rather than rewriting the entire table. References • administration/management/BE_configuration.md • sql-reference/sql-statements/generated_columns.md • administration/management/compaction.md References • sql-reference/sql-statements/table_bucket_part_index/ALTER_TABLE.md • sql-reference/sql-statements/generated_columns.md
c
@Rocky I am still getting error about const expression. I am selecting 
gen_event_minute
and everything else is from shredded JSON. Do sync MVs not work with expressions like 
CAST(message_json->'$.country' AS STRING) AS country,
or 
HLL_UNION(HLL_HASH(CAST(message_json->'$.ipv6' AS STRING))) AS ipv6_hll,
?
r
The error "const expression" in this context usually means you are trying to use a function or operator (like the JSON 
->
operator) that the Synchronous Materialized View (Rollup) parser does not support. While StarRocks v3.1+ introduced "complex expression" support for Sync MVs, it is primarily restricted to simple arithmetic and type casts on standard base columns. JSON operators and nested extractions are generally not supported inside a Sync MV definition. Why this is happening: 1. JSON Type Restriction: Sync MVs are internally stored as "Aggregate Tables." StarRocks does not support the JSON data type in Aggregate tables. Because the 
->
operator returns a JSON-related type before your 
CAST
, it often triggers a failure in the synchronous rollup logic. 2. Expression Complexity: The Sync MV rewriter is very strict. Nested expressions like 
HLL_UNION(HLL_HASH(CAST(json->path)))
are too complex for the synchronous aggregation engine to guarantee data consistency during the load process. The Solution: Use Generated Columns first The recommended "best practice" in StarRocks for this scenario is a two-step process: Shred the JSON into real columns first, then build the MV on those columns. Step 1: Shred the JSON using Generated Columns Add the extracted fields as physical (but automatically managed) columns. This moves the computation to the ingestion phase and makes the columns "real" enough for a Sync MV.
Copy code
sql
-- 1. Add country as a generated column
ALTER TABLE your_table ADD COLUMN country_gen STRING 
AS (CAST(message_json->'$.country' AS STRING));

-- 2. Add the HLL hash as a generated column (more efficient than hashing inside the MV)
ALTER TABLE your_table ADD COLUMN ipv6_hll_base HLL 
AS (HLL_HASH(CAST(message_json->'$.ipv6' AS STRING)));
Step 2: Create the Sync Materialized View Now that these are standard columns, your 
CREATE MATERIALIZED VIEW
statement will be much simpler and will be accepted by the parser:
Copy code
sql
CREATE MATERIALIZED VIEW your_mv AS
SELECT 
    gen_event_minute, 
    country_gen, 
    HLL_UNION(ipv6_hll_base) AS ipv6_hll
FROM your_table
GROUP BY gen_event_minute, country_gen;
Why this is better: * Performance: The JSON extraction happens once during data load (or during the 
ALTER TABLE
you are currently running), rather than being re-evaluated constantly. * Stability: Since 
country_gen
and 
ipv6_hll_base
are now standard types (STRING and HLL), they are fully compatible with the Sync MV/Rollup engine. * Query Rewrite: Queries that use the original JSON extraction 
CAST(message_json->'$.country' AS STRING)
will often be automatically rewritten to use the 
country_gen
column in your MV. Rocky's Tip: If you absolutely need to keep the JSON extraction inside the MV and don't want to add columns to the base table, you must use an Asynchronous Materialized View instead. Async MVs support the full SQL syntax, including all JSON functions and complex nesting. References • sql-reference/data-types/semi_structured/JSON.md • sql-reference/sql-statements/materialized_view/CREATE_MATERIALIZED_VIEW.md • sql-reference/sql-statements/table_bucket_part_index/ALTER_TABLE.md • using_starrocks/Materialized_view-single_table.md References • faq/Sql_faq.md • using_starrocks/SQL_plan_manager.md • sql-reference/sql-statements/keywords.md
c
@Rocky I am wondering if I am better off recreating the table and do in the extraction in routine load. Would that be a better design, or are generated columns pretty much the same thing?
r
From a design and performance standpoint, Generated Columns are almost always the better choice over doing the extraction solely within a Routine Load job. While both methods perform the extraction during ingestion (meaning there is no difference in "storage" cost), Generated Columns provide several critical advantages that will save you from future headaches. 1. The "Query Rewrite" Superpower This is the biggest advantage. * Routine Load ETL: If you extract 
country
in the Routine Load job into a physical column 
country
, and someone runs a query using the old JSON path 
message_json->'$.country'
, StarRocks will not know they are the same. It will perform a slow, expensive JSON extraction at query time. * Generated Columns: If you define 
country AS (CAST(message_json->'$.country' AS STRING))
, the optimizer is smart. If a user runs a query on the raw JSON path, the CBO (Optimizer) automatically rewrites the query to use the pre-computed physical column instead. Your existing dashboards and queries get a speed boost without being changed. 2. Consistency Across Load Methods * Routine Load ETL: The extraction logic lives inside the 
CREATE ROUTINE LOAD
statement. If you later decide to do a one-off 
INSERT INTO
or use 
Stream Load
for a backfill, you have to remember to manually write the extraction logic in those jobs too. * Generated Columns: The logic is part of the table schema. No matter how data gets into the table (Routine Load, Flink, Spark, or INSERT), StarRocks handles the extraction automatically. It’s "set it and forget it." 3. Solving the MV "Const Expression" Problem As you discovered, Synchronous MVs (Rollups) are very picky. They require "real" columns or very simple expressions. * By using Generated Columns, you are "shredding" the JSON into a first-class column that the MV engine understands. * Doing the extraction in Routine Load also creates a "real" column, but you lose the Query Rewrite and consistency benefits mentioned above. 4. Is Recreating the Table Necessary? No. In StarRocks, adding a Generated Column is an optimized operation: * It does not rewrite the entire table. * It only calculates the new column for existing rows and writes those values into new small files, linking them to your existing data. * In a Shared Data cluster, this is much faster than a full table recreation and doesn't require you to stop your Routine Load. Recommendation Stick with the Generated Columns approach. It is the modern, "StarRocks-native" way to handle JSON shredding. The only reason to recreate the table is if you also want to change your 
DISTRIBUTED BY
key or 
PARTITION BY
logic to use one of these new extracted fields. Since you can't change the distribution key of an existing table, a recreation would be required only in that specific case. Rocky's Tip: If you are on a version earlier than v3.5.0, check your documentation as Generated Columns support in Shared Data clusters was finalized in that release. If you're on an older version and hitting issues, that might be the sign to upgrade! References • sql-reference/sql-statements/generated_columns.md • using_starrocks/Flat_json.md • loading/Etl_in_loading.md References • using_starrocks/Flat_json.md • loading/Json_loading.md • sql-reference/sql-statements/loading_unloading/routine_load/CREATE_ROUTINE_LOAD.md
c
@Rocky I am still getting sync mv rejected for const expression. why?
Copy code
gen_country,
    gen_dropped,
    gen_duplicate,
    gen_gdpr,

    -- Count metric
    COUNT(1) AS total_events,

    -- HLL metrics (hash the generated string columns)
    HLL_UNION(HLL_HASH(gen_declared_ip)) AS ip_hll,
    HLL_UNION(HLL_HASH(gen_ipv6)) AS ipv6_hll,
    HLL_UNION(HLL_HASH(gen_uid)) AS uid_hll,
    HLL_UNION(HLL_HASH(gen_samsung_psid)) AS samsung_psid_hll,
    HLL_UNION(HLL_HASH(gen_samsung_tvid)) AS samsung_tvid_hll,
    HLL_UNION(HLL_HASH(gen_samsung_hhid)) AS samsung_hhid_hll,

    -- Sum metrics (using generated numeric columns)
    SUM(gen_thinktime) AS thinktime_sum,
    SUM(gen_buyer_trader_fee) AS buyer_trader_fee_sum,
    SUM(gen_seller_media_revenue) AS seller_media_revenue_sum,
    SUM(gen_buyer_total_fee) AS buyer_total_fee_sum,
    SUM(gen_seller_net_revenue) AS seller_net_revenue_sum,
    SUM(gen_adjusted_media_price) AS adjusted_media_price_sum,
    SUM(gen_buyer_media_price) AS buyer_media_price_sum,
    SUM(gen_media_price) AS media_price_sum,
    SUM(gen_buyer_price) AS buyer_price_sum,
    SUM(gen_bid_cpm) AS bid_cpm_sum,
    SUM(gen_buyer_data_fee) AS buyer_data_fee_sum,
    SUM(gen_buyer_adserving_fee) AS buyer_adserving_fee_sum,
    SUM(gen_buyer_media_fee) AS buyer_media_fee_sum
r
The "const expression" error in a Synchronous Materialized View (Sync MV/Rollup) is almost always caused by one of two specific things in your SQL statement that the legacy Rollup parser cannot handle. 1. The 
COUNT(1)
Culprit In StarRocks Synchronous MVs, the parser is extremely strict. It expects an aggregate function to be performed on a column or 
*
. * The Issue: 
COUNT(1)
uses a literal constant (
1
). The Rollup engine sees this as a "constant expression" and rejects it. * The Fix: Change 
COUNT(1)
to `COUNT()`*. _ StarRocks optimizes 
COUNT(_)
and 
COUNT(1)
identically during queries, but the Rollup creator specifically looks for the 
*
symbol or a column name. 2. Ordinals in 
GROUP BY
 If your full SQL statement (which you didn't include the end of) uses numbers in the 
GROUP BY
clause, it will trigger this error. * The Issue: 
GROUP BY 1, 2, 3, 4
* The Fix: Use the actual column names: 
GROUP BY gen_country, gen_dropped, gen_duplicate, gen_gdpr
. 3. Complex Nesting in older versions While StarRocks v3.1+ supports complex expressions, nesting a scalar function (
HLL_HASH
) inside an aggregate function (
HLL_UNION
) inside a Materialized View can sometimes be tricky for the Rollup engine if the column it’s referencing is also a "Generated Column" expression. The "Bulletproof" Design Pattern: To make your Sync MV as stable as possible and avoid "const expression" or "unsupported" errors, move the 
HLL_HASH
logic into the Generated Column definition itself. This simplifies the MV to a standard aggregate. Step 1: Define the HLL as a Generated Column (Physical)
Copy code
sql
ALTER TABLE your_table ADD COLUMN hll_ip HLL 
AS (HLL_HASH(gen_declared_ip));
Step 2: Create the MV using a simple aggregation
Copy code
sql
CREATE MATERIALIZED VIEW your_mv AS
SELECT 
    gen_country,
    gen_dropped,
    gen_duplicate,
    gen_gdpr,
    COUNT(*) AS total_events, -- Changed from COUNT(1)
    HLL_UNION(hll_ip) AS ip_hll, -- Simple aggregation on a physical HLL column
    SUM(gen_thinktime) AS thinktime_sum
    -- ... other sums
FROM your_table
GROUP BY gen_country, gen_dropped, gen_duplicate, gen_gdpr;
Why this works: 1. `COUNT()`*: Matches the Rollup engine's internal 
COUNT
signature exactly. 2. Simple 
HLL_UNION
: By making 
hll_ip
a physical 
HLL
type column (via the Generated Column), the MV creation is no longer doing "math" or "hashing" during the rollup process; it's just aggregating existing HLL values. This is much more robust across different StarRocks versions. 3. Type Safety: 
HLL_UNION
is designed to work on the 
HLL
data type. By pre-hashing it into a column, you ensure the MV creation never hits a type-mismatch or "expression too complex" error. Rocky's Tip: If you are on a Shared Data (Lake) cluster, ensure you are on v3.4.0 or higher, as that is the version where Synchronous MVs were fully stabilized for the Shared Data architecture. References • using_starrocks/Materialized_view-single_table.md • sql-reference/sql-statements/materialized_view/CREATE_MATERIALIZED_VIEW.md • sql-reference/data-types/other-data-types/HLL.md References • using_starrocks/Materialized_view-single_table.md • using_starrocks/async_mv/use_cases/query_rewrite_with_materialized_views.md • using_starrocks/async_mv/Materialized_view.md
c
@Rocky how do I drop a sync materialized view before it is finished creating?
r
Since a Synchronous Materialized View (Rollup) is built as a background schema change task, you cannot use the standard 
DROP
command while it is in the "PENDING" or "RUNNING" state. You must instead cancel the background job. Here are the steps to stop an in-progress Sync MV creation: 1. Find the Job ID Run the following command to see the current status of your Materialized View creation:
Copy code
sql
SHOW ALTER MATERIALIZED VIEW;
Look for the row where 
RollupIndexName
matches the name of the MV you are trying to create. Note the 
JobId
 (e.g., 
12090
) and the 
TableName
 (the base table). 2. Cancel the Task Use the 
CANCEL ALTER
command with the Job ID you just found:
Copy code
sql
CANCEL ALTER TABLE ROLLUP FROM your_base_table_name (12090);
_Replace 
your_base_table_name
with the table you are building the MV on, and 
12090
with your actual Job ID._ 3. Verify the Cancellation Run 
SHOW ALTER MATERIALIZED VIEW;
again. The 
State
should now change to 
CANCELLED
. Once it is cancelled, the partial data created for that MV will be cleaned up automatically by StarRocks. ────────── Why did the standard 
DROP
fail? In StarRocks, Synchronous MVs are tightly bound to the base table's storage. Until the creation is "FINISHED," the MV doesn't technically exist as a fully registered object that can be dropped; it only exists as a "Schema Change" task. Rocky's Tip: If you ever find yourself stuck with a "Ghost" MV that says it's finished but you can't drop it, make sure you are using the correct syntax: 
DROP MATERIALIZED VIEW [mv_name] ON [table_name];
(though in most versions, just 
DROP MATERIALIZED VIEW [mv_name];
is sufficient). References • using_starrocks/Materialized_view-single_table.md • sql-reference/sql-statements/materialized_view/SHOW_ALTER_MATERIALIZED_VIEW.md • sql-reference/sql-statements/materialized_view/DROP_MATERIALIZED_VIEW.md References • using_starrocks/Materialized_view-single_table.md • using_starrocks/async_mv/Materialized_view.md • sql-reference/sql-statements/materialized_view/CANCEL_REFRESH_MATERIALIZED_VIEW.md • sql-reference/sql-statements/materialized_view/SHOW_ALTER_MATERIALIZED_VIEW.md
c
@Rocky are there any properties I can set to max out cluster compute for the rollup?
r
To "max out" the compute for a Synchronous Materialized View (Rollup) creation, you need to adjust parameters that govern the background Schema Change process. Unlike standard queries, Rollups are executed as background tasks by the Backends (BE). Here are the specific properties and configurations you can tune to speed up the process: 1. Increase Backend Parallelism (BE Config) The most important setting is the number of threads dedicated to schema changes. This is a BE configuration. * 
alter_tablet_worker_count
: This determines how many threads on each BE node will work on the rollup simultaneously. * Default: 3 * Max Out: You can safely increase this to 8 or 16 (or higher depending on your CPU cores) to process more tablets in parallel. * How to change (Runtime):
Copy code
sql
      UPDATE information_schema.be_configs SET value = "16" WHERE name = "alter_tablet_worker_count";
2. Increase Memory Allocation (BE Config) Since you are doing HLL and Sum aggregations, the process can be memory-heavy. If a task hits the memory limit, it may fail or slow down by spilling to disk. * 
memory_limitation_per_thread_for_schema_change
: The max memory a single rollup thread can use. * Default: 2 (GB) * Max Out: Increase this to 4 or 8 GB if your BEs have plenty of RAM. * How to change (Runtime):
Copy code
sql
      UPDATE information_schema.be_configs SET value = "8" WHERE name = "memory_limitation_per_thread_for_schema_change";
3. Adjust FE Scheduling (FE Config) The Frontend (FE) manages the queue of these jobs. If you have a large cluster, you want to make sure the FE isn't bottlenecking the task submission. * 
alter_max_worker_threads
: Threads the FE uses to handle the "Alter" state machine. * 
max_running_rollup_job_num_per_table
: Usually defaults to 1, but you can check if it's limiting you. 4. Use "Creation Hints" (Session Level) When you run the 
CREATE MATERIALIZED VIEW
statement, you can actually pass hints to the SQL engine that performs the initial "scan and aggregate" of your existing data.
Copy code
sql
CREATE MATERIALIZED VIEW your_mv AS
SELECT /*+ SET_VAR(query_timeout = 3600, exec_mem_limit = 17179869184) */
    gen_country,
    COUNT(*) as total_events
    ...
FROM your_table
GROUP BY ...;
* 
query_timeout
: Set this high (e.g., 3600 for 1 hour) so the initial build doesn't time out. * 
exec_mem_limit
: Set this to a high value (in bytes) to give the initial data scan more headroom. 5. Check "Shared Data" Specifics (if applicable) If you are on a Shared Data (Lake) cluster, the rollup performance is also tied to your Warehouse/CN size. * Adding more CN nodes will automatically distribute the rollup task across more compute resources. * Ensure 
lake_compaction_max_tasks
 (FE config) is not set to 0, as the background merging of the new rollup data behaves similarly to compaction. Summary Checklist for Max Speed: 1. Set 
alter_tablet_worker_count
to 16 on all BEs. 2. Set 
memory_limitation_per_thread_for_schema_change
to 4 (GB). 3. Ensure your base table is properly bucketed. Since Rollups work tablet-by-tablet, having too few large tablets limits parallelism, while too many tiny tablets adds overhead. Rocky's Warning: Monitoring is key! Watch your BE CPU and Memory usage after bumping these numbers. If you see memory exhaustion or "High CPU" alerts that affect your production queries, scale these numbers back down slightly back. References • administration/management/BE_configuration.md • using_starrocks/Materialized_view-single_table.md • sql-reference/sql-statements/table_bucket_part_index/ALTER_TABLE.md References
c
@Rocky alter table commands fail when new partions are created. Will rollups also fail?
r
Yes, Synchronous Materialized Views (Rollups) will also fail if a new partition is created while the rollup task is in progress, especially if you are using Expression Partitioning or have heavy concurrent load. Here is why this happens and how to manage it: 1. The "Priority" Conflict In StarRocks, a Rollup creation is a background Schema Change task. It requires a metadata lock on the table. * Expression Partitioning: If you use expression partitioning (automatic partition creation during load), StarRocks prioritizes the Load Task. If a load task needs to create a new partition at the same time a Rollup is running, the Rollup task is often aborted (cancelled) with a failure because the system cannot handle the metadata structural change concurrently with the data rewrite. * Dynamic Partitioning: Similarly, if the background dynamic partitioning thread tries to 
ADD PARTITION
while a Rollup is running, one of them will typically fail to acquire the necessary lock. In many versions, the synchronous 
ALTER
(Add Partition) will block, but if it times out or conflicts with the state of the Rollup, the Rollup may fail. 2. Tablets and Metadata Snapshots When you start a Rollup, StarRocks "snapshots" the tablets of the table to process them. * If a new partition is added, it creates entirely new tablets that weren't in the original snapshot. * While StarRocks tries to handle this by applying the rollup to new tablets as they are created, the metadata lock contention during the "swap" or "activation" phase often leads to the 
Table state is not NORMAL
or 
Cancel alter table
errors. 3. How to prevent Rollup failures during partition changes: Option A: Pre-create Partitions (Recommended) The most reliable way to finish a long-running Rollup is to ensure no new partitions are created during its execution. * If you use Dynamic Partitioning, increase your 
dynamic_partition.end
property to create partitions for the next 7–14 days in advance before starting the Rollup. * If you use Expression Partitioning, you can "warm up" the partitions by running an 
EXPLAIN ANALYZE INSERT
with future dates (which triggers partition creation without committing data):
Copy code
sql
    -- Example for hourly partitions
    EXPLAIN ANALYZE
    INSERT INTO your_table (timestamp_col)
    SELECT DATE_ADD(NOW(), INTERVAL d HOUR) FROM table(generate_series(0, 24)) AS g(d);
Option B: Pause Dynamic Partitioning If you have a very large table and the Rollup will take hours, temporarily disable the dynamic partition scheduler for that table:
Copy code
sql
ALTER TABLE your_table SET ("dynamic_partition.enable" = "false");
-- Run your CREATE MATERIALIZED VIEW ...
-- Once SHOW ALTER MATERIALIZED VIEW shows "FINISHED"
ALTER TABLE your_table SET ("dynamic_partition.enable" = "true");
Option C: Reduce Lock Contention (BE Config) If your Rollups are failing because of "Create partition timeout" or "Lock wait timeout," you can increase the lock shard size on your Backends to reduce the chance of different tasks hitting the same lock bucket. * BE Config: 
tablet_map_shard_size = 512
(requires BE restart). Summary If you are seeing "const expression" errors (from your previous question) followed by "Rollup failed" after you fixed the syntax, it is almost certainly due to partition creation concurrency. Pre-creating your partitions for the next few days is the "bulletproof" way to ensure your Rollup finishes successfully. Rocky's Tip: Always check the status of your failed rollup with 
SHOW ALTER MATERIALIZED VIEW;
. Check the 
Msg
column—it will usually say something like "table's state is not NORMAL" or "conflicting with partition operation" if this is the cause. References • using_starrocks/Materialized_view-single_table.md
c
@Rocky with ASYNC MVs the query planner would union hour and minute MVs. Will it union SYNC MVs the same way?
r
No, Synchronous Materialized Views (Sync MVs/Rollups) do not union in the same way Asynchronous MVs do. While Asynchronous MVs have a sophisticated "Union Rewrite" capability that can stitch together different data sources (like using an hourly MV for old data and the base table for new data), Synchronous MVs follow a much simpler logic. 1. The "Best Match" Rule (Single Index Selection) Synchronous MVs are treated as Rollup Indexes of the base table. When you run a query, the StarRocks optimizer looks at all available indexes (the Base Table and all its Rollups) and selects the single most efficient one that can satisfy the query. * It will not combine data from an "hour" rollup and a "minute" rollup into a single query plan. * If your query asks for a time range that spans both granularities, the optimizer will simply choose the one index that contains all the necessary columns and rows—usually falling back to the base table or the finer-grained (minute) rollup if it covers the whole range. 2. Why Async MVs are different Asynchronous MVs support Partition Union Rewrite and Predicate Union Rewrite. This allows the planner to: * Identify that part of the query can be answered by an MV. * Identify that the remainder of the data must come from the base table or another MV. * Generate a 
UNION
plan to merge them. Synchronous MVs lack this logic because they are designed to be "transparent indexes" for single-table acceleration. Their primary goal is to ensure that even if you have 10 rollups, the query is hitting the smallest possible data set that can answer the entire request. 3. How to achieve "Union" behavior with Sync MVs If you must use Synchronous MVs and want to "union" them, you have to do it manually in your SQL:
Copy code
sql
-- Manual union to force different rollups (not recommended)
SELECT date_trunc('hour', ts), count(*) FROM base_table WHERE ts < '2023-01-01' GROUP BY 1
UNION ALL
SELECT date_trunc('minute', ts), count(*) FROM base_table WHERE ts >= '2023-01-01' GROUP BY 1;
However, even in this case, the optimizer will evaluate each branch of the 
UNION
separately and choose the best rollup for each branch. Summary Comparison Feature | Synchronous MV (Rollup) | Asynchronous MV Planner Logic | Best Match (Single Index) | Union / Join / Aggregation Rewrite Union Rewrite | No | Yes (Predicate & Partition) Use Case | Real-time, single-table rollup | Complex, multi-table, or tiered data Rocky's Tip: If you are trying to build a "Hot/Warm" data architecture where you use highly aggregated data for historical queries and raw data for recent queries, Asynchronous MVs are the tool you should use. They were specifically built to handle the "Union" scenarios that Sync MVs cannot. References • using_starrocks/async_mv/Materialized_view.md • using_starrocks/async_mv/use_cases/query_rewrite_with_materialized_views.md • using_starrocks/Materialized_view-single_table.md References • using_starrocks/async_mv/Materialized_view.md • using_starrocks/async_mv/use_cases/query_rewrite_with_materialized_views.md
2 Views
Open in Slack
PreviousNext
Powered by