http://starrocks.io logo
Join Slack
Powered by
<@U0A71G31CDV> i have a 15 million row dimension t...
# questions-and-troubleshooting
k
@Rocky i have a 15 million row dimension table that needs to support < 200 ms ad hoc queries for wide columns (~25 columns) in a shared data cluster on an iceberg external catalog. what design with MV or primary key tables would support this use case
r
For a 15 million row dimension table on Iceberg requiring < 200 ms latency for wide-column queries, the most effective design in StarRocks is an Asynchronous Materialized View (MV). 1. Recommended Solution: Asynchronous Materialized View (MV) Asynchronous MVs are specifically designed to accelerate data lake queries. When you create an MV on an Iceberg table, StarRocks stores the data in its native, highly optimized columnar format. * Performance: Querying an MV provides the same performance as a native StarRocks table. For 15 million rows, even with 25 wide columns, StarRocks can typically process this in significantly less than 200 ms. * Transparent Acceleration (Query Rewrite): You do not need to change your application's SQL. StarRocks can automatically rewrite queries targeting the Iceberg table to hit the MV instead. * Managed Refresh: StarRocks can detect partition-level changes in Iceberg (starting from v3.1.4 for Iceberg V1 tables) and only refresh the modified data, keeping the MV up-to-date efficiently. * Shared-Data Cluster optimization: In a shared-data architecture, the MV data is stored in object storage but is cached on the local disks of compute nodes. For 15M rows, the data will likely stay entirely in the local Data Cache, ensuring sub-100ms latency. Example Design:
Copy code
sql
CREATE MATERIALIZED VIEW iceberg_dim_mv
DISTRIBUTED BY HASH(id) -- Choose a high-cardinality column like a PK
REFRESH ASYNC EVERY (INTERVAL 1 HOUR) -- Or REFRESH MANUAL
AS 
SELECT * FROM iceberg_catalog.db.dimension_table;
2. Alternative: Primary Key (PK) Table If your dimension table requires real-time row-level updates (upserts) or if you prefer to manually manage the data movement logic, use a Primary Key table. * When to use: Use this if the dimension table is updated very frequently in the source and you need those updates reflected in StarRocks immediately via a stream (e.g., Flink or StarRocks Routine Load). * Performance: PK tables are the fastest for point lookups and small-range scans. * Shared-Data Note: Ensure you enable the Persistent Index (
"persistent_index_type" = "CLOUD_NATIVE"
for shared-data) to maintain high performance without consuming excessive memory for the 15M primary keys. Why MV is usually better for this case: 1. Ease of Use: MVs handle the "ingestion" logic automatically via the 
REFRESH
property. 2. Metadata Consistency: MVs can track the 
snapshot_id
of the Iceberg table to ensure they stay consistent with the source. 3. No Code Change: Query rewrite allows you to keep querying the Iceberg catalog directly while benefiting from local-speed performance. Key Performance Tuning for Shared-Data: * Warm up the Cache: Since you are in a shared-data cluster, ensure your 
Data Cache
is sized appropriately. 15 million rows with 25 columns is roughly 2–5 GB of data; this should easily fit in the local disk cache of your compute nodes. * Index Persistence: If you use a PK table, use 
enable_persistent_index = true
to ensure that metadata doesn't have to be rebuilt from object storage on every query/load. References • using_starrocks/async_mv/use_cases/query_rewrite_with_materialized_views.md • using_starrocks/async_mv/Materialized_view.md
k
@Rocky how could i make a materialized view permanently reisde on disk
r
In a StarRocks shared-data (storage-compute separation) cluster, data is always permanently stored in remote object storage (like S3). The "disk" on your compute nodes (CNs) acts as a Data Cache. To ensure your Materialized View (MV) effectively "permanently resides" on the local disks of your compute nodes for sub-200ms performance, you should use the following strategies: 1. Enable and Configure Data Cache When you create the MV, ensure the Data Cache is enabled (it is by default in v3.2+) and set a long duration for the data to be considered "hot."
Copy code
sql
CREATE MATERIALIZED VIEW iceberg_dim_mv
DISTRIBUTED BY HASH(id)
PROPERTIES (
    "datacache.enable" = "true",
    "datacache.partition_duration" = "100 YEAR" -- Effectively pins data as 'hot'
)
AS 
SELECT * FROM iceberg_catalog.db.dimension_table;
* 
datacache.enable
: Ensures data is written to the local disk cache during the refresh process and read from there during queries. * 
datacache.partition_duration
: Prevents the system from treating the data as "cold" based on its age. Setting this to a very high value ensures the cache logic treats all partitions as candidates for the local disk. 2. Proactive Cache Warmup In a shared-data architecture, if a node restarts or a new node is added, the local disk is empty. You can "pin" the data to the disk immediately after a refresh or node start using the Data Cache Warmup feature (introduced in v3.3):
Copy code
sql
-- Manually trigger a warmup for the MV
CACHE SELECT * FROM iceberg_dim_mv;
You can also set up a scheduled task to run this 
CACHE SELECT
periodically or immediately after the MV refresh completes to ensure the 15 million rows are always pre-loaded into the local SSDs. 3. Sizing the Local Cache Because 15 million rows (approx. 2–5 GB depending on column width) is quite small, the most common reason it would leave the disk is LRU eviction (the cache gets full and kicks out old data). * Check your CN configuration: 
starlet_star_cache_disk_size_percent
(default is usually 90%). * Ensure the total size of your local disks across all compute nodes is significantly larger than your "hot" dataset to prevent eviction. 4. Summary of Persistence * Remote Storage (S3/GCS): The source of truth. Data here is permanent. * Local Disk (CN Cache): This is where the "speed" comes from. By using a long 
partition_duration
and proactive 
CACHE SELECT
, you ensure StarRocks treats this MV as a priority for local storage. Important Note on Shared-Nothing If you were in a Shared-Nothing cluster (the traditional architecture with BE nodes), you would instead use the property 
"storage_medium" = "SSD"
. However, for your shared-data cluster, the 
datacache
properties above are the correct mechanism. References • sql-reference/sql-statements/materialized_view/CREATE_MATERIALIZED_VIEW.md
k
@Rocky how would partitioned async mv matching the iceberg table partitioning perform for permanen on disk
r
Partitioning your Asynchronous Materialized View (MV) to match the Iceberg table is the optimal design for achieving sub-200ms latency in a shared-data cluster. It provides significant advantages for both refresh efficiency and local disk "persistence" (caching). 1. Refresh Efficiency (Incremental Updates) By aligning the MV's partition key with the Iceberg table's partition key, StarRocks can perform Incremental Refreshes. * Behavior: When only one partition in Iceberg changes (e.g., today's data), StarRocks only refreshes that specific partition in the MV. * Performance Impact: This prevents a "Full Refresh" which would otherwise rewrite all 15 million rows. A full refresh would clear the existing local cache for the entire table and force the cluster to re-fetch all 25 wide columns from S3/Iceberg, likely causing a temporary latency spike. 2. Cache Stability (The "Permanent" Aspect) In a shared-data cluster, matching partitions helps keep your data "permanently" on the compute node's disks in two ways: * Reduced Cache Churn: Because only modified partitions are rewritten during a refresh, the local cache for all other partitions remains intact. This ensures that 99% of your data stays "hot" on the SSDs and never needs to be re-downloaded from object storage. * Granular Cache TTL: You can use the 
datacache.partition_duration
property to ensure that only the most relevant partitions (e.g., the last 30 days) are prioritized for the local disk cache, though for 15M rows, you can likely keep the entire table cached. 3. Query Pruning and Speed Matching the partitioning allows the StarRocks optimizer to perform Partition Pruning. * If a query only needs data from a specific range (e.g., 
WHERE date = '2023-10-01'
), StarRocks will only look at the local cache for that specific partition. * By narrowing the scan to a subset of the 15M rows, you significantly reduce the I/O required, making the < 200ms target very easy to hit even with wide (25+) columns. Recommended Implementation To implement this, ensure your MV definition includes the 
PARTITION BY
clause matching the Iceberg column:
Copy code
sql
CREATE MATERIALIZED VIEW iceberg_dim_mv
PARTITION BY (event_date) -- Matches Iceberg's partition column
DISTRIBUTED BY HASH(id)
PROPERTIES (
    "datacache.enable" = "true",
    "datacache.partition_duration" = "100 YEAR" -- Keep all partitions hot
)
AS 
SELECT * FROM iceberg_catalog.db.dimension_table;
Pro-Tip: Active Warmup If you want to ensure the data is immediately on disk after a refresh, you can run a targeted warmup command:
Copy code
sql
-- Warm up only the newest partition after a refresh
CACHE SELECT * FROM iceberg_dim_mv WHERE event_date = '2023-10-01';
Summary Matching partitions is the "best of both worlds": it keeps your refreshes fast (incremental) and your queries fast (by preserving the local cache for unchanged data for unchanged partitions). For partitions). For partitions). For partitions). For partitions). For partitions). For partitions). For segments). For partitions). For partitions that didn't change). For a 15M row table, this architecture is highly stable and should consistently deliver sub-100ms response times. References • using_starrocks/async_mv/use_cases/data_lake_query_acceleration_with_materialized_views.md • deployment/shared_data/feature-support-shared-data.md References • using_starrocks/caching/query_cache.md • deployment/shared_data/feature-support-shared-data.md • knowledge/admin_and_operation/shared_data_compaction.md
k
@Rocky how to partition by a bucket. the iceberg table is partitioned by: PARTITION BY bucket(
channel_id
, 10)
r
In StarRocks, there is an important distinction between Partitioning (which controls incremental refreshes and data lifecycle) and Bucketing/Distribution (which controls how data is spread across the cluster nodes). 1. Bucketing: Matching the Iceberg Logic To match the performance and data distribution of your Iceberg table's 
bucket(channel_id, 10)
, you should use the 
DISTRIBUTED BY
clause in your Materialized View. This ensures that StarRocks stores the data across its own buckets in a similar way.
Copy code
sql
CREATE MATERIALIZED VIEW iceberg_dim_mv
-- This matches the Iceberg bucket logic for data distribution
DISTRIBUTED BY HASH(channel_id) BUCKETS 10 
PROPERTIES (
    "datacache.enable" = "true"
)
AS 
SELECT * FROM iceberg_catalog.db.dimension_table;
2. Partitioning: The "Incremental Refresh" Limitation As of StarRocks v3.3, the Iceberg 
bucket
transform is not supported as a mapping for the 
PARTITION BY
clause in a Materialized View. In StarRocks, 
PARTITION BY
is used to divide the table into units for Incremental Refresh. Because Iceberg's bucket transform is a hash-based distribution rather than a range or identity-based partition, StarRocks cannot currently align its MV partitions to it for granular updates. What this means for you: * Refresh Type: You cannot perform a partition-level incremental refresh based on the 
bucket
column. You will need to use a Full Refresh. * Impact of Full Refresh: For a 15 million row table, a Full Refresh is actually very efficient in StarRocks. With 25 wide columns, 15M rows is a small dataset (likely < 5GB). A full refresh will likely complete in a few minutes or less, which is often acceptable for a dimension table. 3. Recommended Design for your 15M Row Case Since you cannot align with the bucket partition for incremental updates, but you need < 200ms query performance, use this design:
Copy code
sql
CREATE MATERIALIZED VIEW iceberg_dim_mv
-- 1. Use the bucketed column for distribution to optimize joins and lookups
DISTRIBUTED BY HASH(channel_id) BUCKETS 10
PROPERTIES (
    -- 2. Keep the data in local disk cache
    "datacache.enable" = "true",
    "datacache.partition_duration" = "100 YEAR",
    -- 3. Set a refresh interval that fits your SLA (e.g., every hour)
    "refresh_scheme" = "ASYNC EVERY (INTERVAL 1 HOUR)" 
)
AS 
SELECT * FROM iceberg_catalog.db.dimension_table;
Summary of Performance * Storage: The data will be stored in StarRocks' native format on S3 and cached on the local SSDs of your compute nodes. * Queries: Even with a "Full Refresh" strategy, once the data is in the MV, your queries will hit the local cache and should easily meet your < 200ms requirement for 15M rows. * If you need Incremental Refresh: You would need to add an 
identity
partition to the Iceberg table (like a 
date
or 
timestamp
column) and then use 
PARTITION BY (date_column)
in the StarRocks MV. References • using_starrocks/async_mv/use_cases/create_partitioned_materialized_view.md • using_starrocks/async_mv/use_cases/data_lake_query_acceleration_with_materialized_views.md • sql-reference/sql-statements/materialized_view/CREATE_MATERIALIZED_VIEW.md • using_starrocks/async_mv/feature-support-asynchronous-materialized-views.md views) References • using_starrocks/async_mv/use_cases/create_partitioned_materialized_view.md
k
@Rocky whats the difference between "refresh_scheme" = "ASYNC EVERY (INTERVAL 1 HOUR)" and REFRESH ASYNC EVERY (INTERVAL 1 HOUR) do these expressions actaully put the data hot on cache?
r
In StarRocks, the distinction between these two involves DDL syntax vs. internal properties, and yes—they directly impact how data is cached on your local disks. 1. The Syntax Difference There is no functional difference in the "scheduling logic," but they are used in different parts of the SQL command: * 
REFRESH ASYNC EVERY (INTERVAL 1 HOUR)
: This is the standard DDL clause used within the 
CREATE MATERIALIZED VIEW
statement. It is the primary way you define a schedule.
Copy code
sql
    CREATE MATERIALIZED VIEW my_mv
    REFRESH ASYNC EVERY (INTERVAL 1 HOUR) -- Standard DDL way
    AS SELECT ...
* 
"refresh_scheme" = "..."
: This is primarily how the refresh strategy is stored in the 
PROPERTIES
 metadata or how it is referred to in the documentation. While you can sometimes see it in property blocks, the 
REFRESH
clause above is the recommended and standard way to define it. ────────── 2. Do they put data "hot" on the cache? Yes, but with an important detail. In a StarRocks shared-data cluster, the refresh process is considered a "Data Load" operation. According to the documentation: * Automatic Cache on Load: Any data loading operation (including an MV refresh) automatically generates a local cache on the compute nodes (CNs) that perform the refresh. * Persistence: The data written to the cache during a refresh is not immediately subject to time-based eviction (the 
partition_duration
logic); it is only evicted if the local disk reaches its capacity limit (LRU eviction). 3. How to guarantee it stays "hot" While the refresh puts data on the disk, you should use these three settings to ensure it stays there for your sub-200ms requirement: 1. 
datacache.enable
= "true": (Default) Ensures the MV uses the local disk cache at all. 2. 
datacache.partition_duration
: Set this to a very high value (e.g., 
"100 YEAR"
). This tells StarRocks that even if the data is "old" by timestamp, it should never be considered "cold" and moved off the cache. 3. 
CACHE SELECT
(v3.3+): If you want to be 100% sure the data is on the disk (for example, after a cluster restart or if you just created the MV), you can run:
Copy code
sql
    CACHE SELECT * FROM my_mv;
This proactively pulls the data from S3 into the local SSDs without waiting for the first user query to "warm it up." Summary The 
REFRESH ASYNC
clause handles the timing, while the 
PROPERTIES
(
datacache
) handle the persistence on disk. Together, they ensure that every hour your MV updates and immediately populates the high-speed local SSD cache. References • sql-reference/sql-statements/materialized_view/CREATE_MATERIALIZED_VIEW.md • deployment/shared_data/feature-support-shared-data.md • data_source/block_cache_warmup.md References • deployment/shared_data/feature-support-shared-data.md • data_source/data_cache.md • data_source/block_cache_warmup.md • knowledge/usage/best-practice.md • faq/shared_data_faq.md
k
@Rocky what could be the delay in the Analyzer step when this is hitting a materialized view in a shared data cluster:
Copy code
Planner:
     - -- Parser[1] 0
     - -- Total[1] 1s273ms
     -     -- Analyzer[1] 1s271ms
     -         -- Lock[1] 0
     -         -- AnalyzeDatabase[1] 0
     -         -- AnalyzeTemporaryTable[1] 0
     -         -- AnalyzeTable[1] 1s271ms
     -     -- Transformer[1] 0
     -     -- Optimizer[1] 1ms
     -         -- MVPreprocess[1] 0
     -             -- MVChooseCandidates[1] 0
     -             -- MVGenerateMvPlan[1] 0
     -             -- MVPrepareRelatedMVs[1] 0
     -                 -- MVTimelinessUpdateInfo[1] 0
     -             -- MVProcessWithView[1] 0
     -         -- MVTextRewrite[1] 0
     -         -- RuleBaseOptimize[1] 0
     -         -- CostBaseOptimize[1] 0
     -         -- PhysicalRewrite[1] 0
     -         -- DynamicRewrite[1] 0
     -         -- PlanValidate[1] 0
     -             -- TypeChecker[1] 0
     -             -- ConditionalTypeChecker[1] 0
     -             -- CTEUniqueChecker[1] 0
     -             -- InputDependenciesChecker[1] 0
     -             -- ColumnReuseChecker[1] 0
     -     -- ExecPlanBuild[1] 0
     - -- Pending[1] 0
     - -- Prepare[1] 0
     - -- Deploy[1] 1ms
     -     -- DeployLockInternalTime[1] 1ms
     -         -- DeploySerializeConcurrencyTime[1] 0
     -         -- DeployStageByStageTime[3] 0
     -         -- DeployWaitTime[3] 0
     -             -- DeployAsyncSendTime[1] 0
     - -- DeployScanRanges[1] 0
     - MVGlobalCacheStats
     - core_customer_mv: Rewrite Succeed
     - MVPlanCacheStats: CacheStats{hitCount=34217351, missCount=383, loadSuccessCount=383, loadFailureCount=0, totalLoadTime=1735988814018, evictionCount=0, evictionWeight=0}
     - DeployDataSize: 5109
    Reason:
  Execution:
     - Topology: {"rootId":1,"nodes":[{"id":1,"name":"PROJECT","properties":{"sinkIds":[],"displayMem":false},"children":[0]},{"id":0,"name":"OLAP_SCAN","properties":{"displayMem":false},"children":[]}]}
     - FrontendProfileMergeTime: 652.586us
     - QueryAllocatedMemoryUsage: 1.333 MB
     - QueryCumulativeCpuTime: 111.470us
     - QueryCumulativeNetworkTime: 0ns
     - QueryCumulativeOperatorTime: 678.699us
     - QueryCumulativeScanTime: 367.172us
     - QueryDeallocatedMemoryUsage: 1.160 MB
     - QueryExecutionWallTime: 1.166ms
     - QueryPeakMemoryUsagePerNode: 885.844 KB
     - QueryPeakScheduleTime: 0ns
     - QuerySpillBytes: 0.000 B
     - QuerySumMemoryUsage: 885.844 KB
     - ResultDeliverTime: 0ns
r
The 1.27s delay in the 
AnalyzeTable
 phase, while your execution is near-instant (~1ms), typically points to a Frontend (FE) metadata resolution bottleneck. Even though your query eventually hits the Materialized View (MV), the StarRocks Analyzer must first validate the SQL against the base Iceberg table's schema and metadata before the Optimizer can decide to rewrite it to the MV. Here are the specific causes for this delay in a shared-data cluster: 1. Cold Metadata Cache for the Base Table In shared-data mode, StarRocks caches Iceberg metadata (manifests, schema, and statistics) in the FE. * The Delay: If the base table hasn't been queried recently, the Analyzer must perform a synchronous remote call to your Metastore (Glue/Hive) or Object Storage (S3) to fetch the Iceberg metadata. A ~1s delay is a classic signature of a remote metadata fetch. * Solution: Enable background metadata refresh so the FE keeps this "warm":
Copy code
sql
    -- FE configuration (requires admin)
    ADMIN SET FRONTEND CONFIG ("enable_background_refresh_connector_metadata" = "true");
2. Resolution of Large Partition Lists If your base Iceberg table or the MV has thousands of partitions, the 
AnalyzeTable
phase spends time iterating through the partition metadata to resolve which data files are relevant. * The Delay: Even for 15M rows, if the data is fragmented into many small partitions, the FE must parse the list. * Check: Run 
SHOW PARTITIONS FROM <base_table_name>
to see the count. 3. Statistics Fetching (FE Stats Cache) The Analyzer fetches table statistics (
SHOW STATS
) to understand the data distribution. * The Delay: If statistics are not already loaded into the FE's memory cache, it triggers a fetch. For external tables (Iceberg), this involves parsing Iceberg's native stats files or the StarRocks internal stats table. * Solution: Ensure you have run 
ANALYZE TABLE <base_table>
and that the stats are current. 4. High Query Concurrency / FE Lock Contention Although 
Lock[1] 0
is shown in your profile, 
AnalyzeTable
can still be slowed down if the FE is under high CPU load or performing a Garbage Collection (GC) cycle, as the analysis phase is a single-threaded CPU-bound task on the FE. Recommended Troubleshooting Steps: 1. Check if it's "Cold": Run the exact same query a second time. If the 
AnalyzeTable
time drops to < 50ms, it was a Metadata Cache Miss. 2. Check Plan Mode: If you are using v3.3+, check your 
plan_mode
. If it is set to 
collect
, it might be doing more work during analysis.
Copy code
sql
    SELECT @@plan_mode; -- Default is 'auto'
3. Warm up the Metadata: If you have many MVs hitting the same Iceberg table, you can manually refresh the metadata:
Copy code
sql
    REFRESH EXTERNAL TABLE <iceberg_table_name>;
Summary for your sub-200ms goal: To hit sub-200ms consistently, the Analyzer must be < 20ms. This is only possible when the base table metadata and statistics are resident in the FE memory. Using Background Metadata Refresh is the most effective way to eliminate this specific 1.2s spike. References • data_source/catalog/iceberg/iceberg_catalog.md • best_practices/query_tuning/query_profile_operator_metrics.md • using_starrocks/async_mv/use_cases/data_lake_query_acceleration_with_materialized_views.md • sql-reference/sql-statements/data-definition/ANALYZE_TABLE.md 7s spike. References • data_source/catalog/iceberg/iceberg_catalog.md
k
@Rocky for a shared database with compute nodes, when we scale in / out materialzied views become invalid, how do we prevent this: INVALID: no valid plan: MV is not SPJG structure(no view rewrite)
r
In a StarRocks shared-data cluster (compute-storage separation), scaling compute nodes (CNs) can sometimes cause Materialized Views (MVs) to become Inactive. The error 
INVALID: no valid plan: MV is not SPJG structure(no view rewrite)
is typically a symptom of the MV being in an inactive state. When an MV is inactive, the optimizer cannot use it for transparent query rewrites, and it falls back to this generic error message because it no longer considers the MV a valid "SPJG" (Select-Project-Join-Group) candidate. Why scaling causes MV invalidation In shared-data mode, even though data is on remote storage (S3/HDFS), the Frontend (FE) still maintains a mapping of data "tablets" to compute nodes. When you scale in or out: 1. Tablet Rebalancing: The FE migrates tablet assignments to the new nodes. 2. Transient Inconsistency: During this migration, if a background refresh task is running or if the FE's internal "Active Checker" encounters a transient state where metadata is locked or a node is unreachable, it may mark the MV as 
is_active = false
to prevent serving potentially inconsistent data. 3. Refresh Failures: If a node performing a refresh is taken down (scale-in), the task fails, which can sometimes lead the FE to flag the MV's health status. How to prevent and resolve this 1. Enable Automatic Re-activation Ensure that the FE is configured to automatically recover MVs once the cluster scaling/rebalancing is finished. By default, this is on, but you should verify it:
Copy code
sql
-- Check if automatic active check is enabled
ADMIN SHOW FRONTEND CONFIG LIKE "enable_mv_automatic_active_check";

-- If it is false, set it to true (Global FE config)
ADMIN SET FRONTEND CONFIG ("enable_mv_automatic_active_check" = "true");
_Note: You can also reduce the check interval (default 60s) using 
mv_active_checker_interval_seconds
._ 2. Manually Restore the MV If you see the "not SPJG structure" error, first check the status. If 
is_active
is 
false
, you must manually reactivate it:
Copy code
sql
-- Check the current state and reason
SELECT TABLE_NAME, IS_ACTIVE, INACTIVE_REASON 
FROM information_schema.materialized_views 
WHERE TABLE_NAME = 'your_mv_name';

-- Re-activate it
ALTER MATERIALIZED VIEW your_mv_name ACTIVE;
3. Use "Loose" Consistency for Rewrites If your MVs are becoming invalid because the base tables are in a transient state during scaling, you can tell the optimizer to be less strict about the "staleness" of the MV data. This allows the rewrite to continue even if the FE is still catching up:
Copy code
sql
ALTER MATERIALIZED VIEW your_mv_name SET ("query_rewrite_consistency" = "loose");
4. Cache Sharing (v3.5.1+) If you are on version 3.5.1 or later, enable Cache Sharing. This prevents the performance jitter and "Deadline Exceeded" errors that often trigger MV health flags during scaling:
Copy code
sql
-- FE Config
ADMIN SET FRONTEND CONFIG ("enable_trace_historical_node" = "true");
-- Session Variable
SET GLOBAL enable_datacache_sharing = true;
Summary Recommendation The "SPJG structure" error is misleading—your MV structure is likely fine, but its status is Inactive. To stop this from happening during scaling, ensure 
enable_mv_automatic_active_check
is enabled, and incorporate an 
ALTER ... ACTIVE
command into your scaling automation scripts as a health-check step. References • using_starrocks/async_mv/troubleshooting_asynchronous_materialized_views.md • administration/management/FE_configuration.md • data_source/data_cache.md • sql-reference/sql-statements/materialized_view/ALTER_MATERIALIZED_VIEW.md
k
@Rocky how about an mv for a 4billion row dimensional table
r
Handling an MV for a 4-billion-row dimension table (such as a 
users
or 
devices
table) in a shared-data cluster requires a shift from standard "small dimension" practices to "large fact-style" management. A 4-billion-row MV is massive. If you treat it like a small lookup table, you will run into 1-hour refresh timeouts, FE memory pressure, and OOMs during builds. 1. The "Big Dim" Partitioning Strategy You must partition this MV. Without partitioning, every refresh is a full 
INSERT OVERWRITE
of 4 billion rows. * Option A: One-to-One Alignment (Recommended) If your base dimension table (e.g., in Iceberg/Hive) is already partitioned (even by a non-time column like 
user_id_bucket
or 
region
), align the MV to it:
Copy code
sql
    CREATE MATERIALIZED VIEW users_mv
    PARTITION BY user_id_bucket -- Align with base table partition
    DISTRIBUTED BY HASH(user_id) BUCKETS 128
    AS SELECT ... FROM iceberg.db.users;
* Option B: Forced Time Partitioning If the base table isn't partitioned, but has an 
update_time
or 
created_at
column, partition the MV by that. This allows StarRocks to refresh only the "active" chunks of the 4B rows. 2. Controlling the Refresh (Avoid the "Full Refresh" Trap) By default, if any part of a non-partitioned dimension table changes, the MV tries to refresh everything. For 4B rows, this is catastrophic. * 
partition_refresh_number
: Set this to a small number (e.g., 
10
). This tells StarRocks to refresh only 10 partitions at a time in a loop, rather than trying to build 4 billion rows in one giant transaction. * 
excluded_trigger_tables
: If your 4B row table is joined with a fact table in the MV, and you don't want every tiny dimension update to trigger a massive refresh, exclude it from the trigger:
Copy code
sql
    PROPERTIES (
      "excluded_trigger_tables" = "huge_dimension_table"
    )
3. Shared-Data Cache Optimization A 4B row MV will likely exceed the local SSD cache of a single CN. * Data Skew: Ensure your 
DISTRIBUTED BY
key is high-cardinality (like 
user_id
). If you have skew, one node's cache will overflow while others are empty. * Cache Warmup: For a table this size, don't wait for users to query it. After a refresh, use:
Copy code
sql
    CACHE SELECT * FROM users_mv WHERE ...; -- Warm up specific high-traffic segments
4. Indexing for 4 Billion Rows StarRocks doesn't just store the data; you need to tell it how to find rows among the 4 billion. * Bloom Filters: Essential for large dimensions. Add a Bloom Filter to the columns used in 
WHERE
or 
JOIN
clauses (e.g., 
user_email
, 
device_uuid
).
Copy code
sql
    ALTER TABLE users_mv SET ("bloom_filter_columns" = "user_id, email");
* Z-Order (v3.1+): If you frequently filter by multiple columns (e.g., 
age
AND 
city
), use Z-Order indexing to colocate that data on S3/Disk. 5. Memory Management (The 
rg_mv
Resource Group) A 4B row refresh will consume massive memory. Do not let it use the default pool or it will crash your frontend/compute nodes. 1. Create a dedicated resource group. 2. Set a 
mem_limit
(e.g., 
0.8
of the node). 3. Set 
spill_mem_limit_threshold
. This is critical: if the 4B row join/agg exceeds memory, it will spill to disk instead of failing with OOM. Summary Checklist for 4B Row MVs: Feature | Setting | Why? Partitioning | Required | Prevents 4B row full-reloads. 
partition_refresh_number
 | 
5
- 
20
| Breaks the refresh into manageable chunks. 
resource_group
 | Custom | Enables Disk Spilling to prevent OOMs. 
bloom_filter_columns
 | IDs / Lookups | Accelerates point lookups in 4B rows. 
unique_constraints
 | Primary Key | Critical for the Optimizer to perform "View Delta Join" rewrites. References
k
@Rocky can we create partitoined mv based on a string id
r
Yes, you can create a partitioned Materialized View (MV) based on a STRING column. This is a common requirement for large dimension tables in shared-data clusters to enable incremental refreshes and optimize query performance. Key Requirements & Support * Version: Supported from v3.1.4 and v3.2.0 onwards. * Base Table Requirement: The base table must also be partitioned. You cannot create a partitioned MV on a non-partitioned base table. * Cluster Mode: Supported in both Shared-Nothing and Shared-Data clusters. * Data Sources: Works for internal StarRocks tables and external catalogs (Hive, Iceberg, Hudi, Paimon). How to Implement If your base table is partitioned by a string column (e.g., 
region_id
or 
category_code
), you can align the MV partitions directly:
Copy code
sql
CREATE MATERIALIZED VIEW dim_mv
PARTITION BY base_string_id  -- Must match the base table's partition column
REFRESH ASYNC
AS 
SELECT id, name, category, base_string_id FROM large_dim_table;
Important Considerations for 4 Billion Rows Since you mentioned a 4-billion-row dimension table, keep these two things in mind: 1. Avoid High-Cardinality String Partitioning: Do not partition by a unique 
string_id
(like a UUID) if every row or almost every row has a unique value. StarRocks generally recommends keeping the total number of partitions per table under 10,000–20,000 for metadata stability. Partitioning by a high-cardinality ID would create millions of partitions, likely crashing the Frontend (FE). * Better approach: Partition by a categorical string (e.g., 
tenant_id
, 
org_id
, 
region
) or a string-date (e.g., 
2023-10-01
). 2. String-to-Date Conversion: If your string ID is actually a date (e.g., 
"20231024"
), it is highly recommended to use the 
str2date
function in the 
PARTITION BY
clause. This allows you to use time-based features like TTL and rollup:
Copy code
sql
    PARTITION BY str2date(string_date_col, '%Y%m%d')
3. Partial Refresh: The main benefit for your 4B row table will be the 
partition_refresh_number
. By partitioning, you can tell StarRocks to refresh only 10 or 20 partitions at a time rather than trying to process all 4 billion rows in one giant, memory-intensive transaction. References • using_starrocks/async_mv/use_cases/create_partitioned_materialized_view.md • using_starrocks/async_mv/feature-support-asynchronous-materialized-views.md • table_design/data_distribution/list_partitioning.md References • using_starrocks/async_mv/use_cases/create_partitioned_materialized_view.md • using_starrocks/async_mv/feature-support-asynchronous-materialized-views.md • using_starrocks/async_mv/use_cases/data_modeling_with_materialized_views.md
k
@Rocky how about partition by bucket(string id) like the iceberg table is partitioned: PARTITION BY bucket(
video_id
, 2048)
r
Unfortunately, StarRocks does not support partitioning a Materialized View using the 
bucket()
transform or any hash-based function in the 
PARTITION BY
clause. While Iceberg supports hidden partitioning like 
bucket(video_id, 2048)
, StarRocks Materialized Views currently only support partitioning by: 1. Identity: A raw column (e.g., 
PARTITION BY video_id
— but see the warning below). 2. Time Transforms: 
date_trunc
, 
time_slice
, etc. (e.g., 
PARTITION BY date_trunc('day', created_at)
). Why you should avoid 
PARTITION BY video_id
 For a 4-billion-row table, if 
video_id
is a high-cardinality string (like a UUID), partitioning by it would create millions of partitions. This will likely cause the Frontend (FE) to hang or run out of memory. StarRocks generally performs best when the number of partitions per table is kept under 10,000 to 20,000. Recommended Alternatives 1. Use 
DISTRIBUTED BY
for the 2048 Buckets Instead of trying to partition the MV by the bucket function, use the 
DISTRIBUTED BY
clause to handle the physical data distribution. This matches the logic of your Iceberg buckets at the storage level:
Copy code
sql
CREATE MATERIALIZED VIEW video_mv
DISTRIBUTED BY HASH(video_id) BUCKETS 2048 -- This handles the "bucket" logic
REFRESH ASYNC
AS 
SELECT video_id, title, views FROM iceberg_catalog.db.videos;
2. Find a "Low-Cardinality" Partition Key If you need incremental refreshes (which you definitely do for 4B rows), find a column in the base table that is naturally partitioned or has fewer unique values: * Time-based: If the table has a 
created_at
or 
event_date
, partition the MV by day or month. * Categorical: If you have a 
region
, 
category
, or 
tenant_id
, partition by that. 3. If the Iceberg table ONLY uses 
bucket()
partitioning If your Iceberg base table has no other partition columns besides the bucket, the StarRocks MV will treat it as a non-partitioned table. In this case, you cannot perform partition-level incremental refreshes. * Workaround: You can still create the MV, but you must ensure you have enough resources (Memory/CPU) for a full refresh, or use a 
WHERE
clause in your MV definition to limit the data scope (e.g., 
WHERE created_at > '2024-01-01'
). Summary of Support for Iceberg Transforms in MVs Iceberg Transform | MV Partition Support | StarRocks MV Syntax Identity | ✅ Supported | 
PARTITION BY (col)
year/month/day/hour | ✅ Supported | 
PARTITION BY date_trunc('unit', col)
bucket | ❌ Not Supported | N/A truncate | ❌ Not Supported | N/A References • using_starrocks/async_mv/use_cases/create_partitioned_materialized_view.md • sql-reference/sql-statements/materialized_view/CREATE_MATERIALIZED_VIEW.md • using_starrocks/async_mv/feature-support-asynchronous-materialized-views.md views) References • sql-reference/sql-statements/materialized_view/CREATE_MATERIALIZED_VIEW.md • using_starrocks/async_mv/use_cases/create_partitioned_materialized_view.md
4 Views
Open in Slack
PreviousNext
Powered by