@Rocky I have a multi-billion-row user behavior table (monthly partitioned) and want to power dashboards from it. The dashboards run filters and GROUP BY aggregations. Which option costs less total compute/resource to serve this data to users? Option A: Each time a user opens the dashboard, query the base table directly (near real-time). Option B: Build 2–3 async materialized views with the needed GROUP BYs, refreshed every 5–10 minutes, and have dashboards read from the MVs. Considering total processing and resource usage to deliver this data to users, which is cheaper?

In almost all production scenarios involving multi-billion-row tables and high-concurrency dashboards, Option B (Async Materialized Views) is significantly cheaper in terms of total compute and resource usage. While Option A requires zero extra storage, the computational cost of scanning billions of rows for every dashboard interaction is extremely high and scales linearly with your user count. Why Option B is cheaper: 1. Elimination of Repetitive Scans: Dashboards often perform similar

GROUP BY

and filter operations. Option A re-computes these results every single time a user loads the page. Option B computes them once every 5–10 minutes, shifting the workload from "per query" to "per refresh interval." 2. Incremental Partition Refresh: Since your base table is partitioned (monthly), StarRocks can perform incremental refreshes on the Async MV. When data changes in a specific partition of the base table, StarRocks only refreshes the corresponding partition in the MV. It does not re-scan the entire multi-billion-row table, which drastically reduces the I/O and CPU required for updates. 3. Transparent Query Rewrite: You don't necessarily need to point your dashboards to the MV. If you query the base table, StarRocks' optimizer can automatically "rewrite" the query to fetch data from the MV if it matches the aggregation logic. This allows you to keep your dashboard SQL simple while benefiting from the MV's performance. 4. Reduced Cluster Sizing: Because Option B offloads the heavy lifting to a background task, you can often serve the same number of users with a much smaller cluster (less CPU/RAM) than would be required to provide acceptable performance for raw scans on billions of rows. When to consider Option A: * Near-Zero Concurrency: If only one person looks at the dashboard once a day, the storage and refresh overhead of an MV might exceed the cost of one heavy query. * Extreme Filter Cardinality: If every user applies a unique, highly specific filter (e.g.,

WHERE user_id = 123

) that prevents the MV from being reusable across different users. Recommendation For your scale, use Option B. To optimize cost further: * Use Partitioning on the MV: Match the partition key of your base table to enable incremental refreshes. * Set a TTL: If the dashboard only needs the last 6 months of data, use the

partition_ttl_number

property on the MV to automatically drop old data and save storage. Example snippet:

sql
CREATE MATERIALIZED VIEW user_behavior_agg_mv
-- Refresh every 10 minutes
REFRESH ASYNC EVERY (INTERVAL 10 MINUTE)
-- Enable incremental refresh via partitioning
PARTITION BY (event_month) 
AS 
SELECT 
    event_month, 
    event_type, 
    count(*) as event_count
FROM user_behavior_base
GROUP BY event_month, event_type;

References • using_starrocks/async_mv/Materialized_view.md • using_starrocks/async_mv/use_cases/create_partitioned_materialized_view.md • using_starrocks/async_mv/use_cases/data_modeling_with_materialized_views.md