Hey @Rocky how does data modeling with materialized views work if I have both Backend Nodes and Compute Nodes (a shared nothing architecture)? Is the data stored on. my Backends Nodes disk or still in the compute node cache? I am working with a hybrid architecture due to performance reasons over large heavy joins + aggregations. Which is why I will have a mix of tables and materialized views syncing from S3 Tables.

Hey there! Rocky here. That's a great question about data modeling in a hybrid setup. In a StarRocks architecture where you have both Backend Nodes (BEs) and Compute Nodes (CNs) (often referred to as a hybrid or expanded shared-nothing deployment), here is how the data storage for Materialized Views (MVs) works: 1. Where is the MV data stored? The persistent data for your Materialized Views is stored on your Backend Nodes (BE) disks. In StarRocks, an asynchronous Materialized View is essentially a specialized internal table. In a shared-nothing architecture (or a hybrid one that includes BEs), all internal tables are partitioned into tablets and distributed across the local disks of the BE nodes. * Backend Nodes (BEs): These are the "stateful" nodes. They are responsible for the actual storage (persistence) of the MV data on their local disks and also participate in computation. * Compute Nodes (CNs): These are "stateless" nodes. They are designed to scale your compute power elastically. While they can perform the heavy lifting for joins and aggregations, they do not store the primary persistent copies of internal tables or MVs. 2. How it works with your S3 Tables Since you are syncing from S3 (likely using External Tables like Iceberg, Hive, or directly via

FILES()

), your data flow looks like this: 1. Base Data: Resides on S3 (high latency, low cost). 2. Materialized View: When the MV refreshes, StarRocks pulls data from S3, performs the joins/aggregations, and writes the results to the BE disks. 3. Query Execution: When you query the MV, StarRocks uses the pre-computed results stored on the fast local disks of your BEs. 3. The Role of the Compute Node Cache While the "source of truth" for your MV is on the BE disks, CNs do have a Data Cache. However, this cache is primarily used to speed up access to remote data (like your base tables on S3). For the MV itself: * If a query plan assigns a fragment to a CN, that CN will fetch the required data from the BEs. * If a query plan assigns the fragment to a BE, it reads directly from its local disk. Why this is great for your "Heavy Joins" use case: By using MVs in this hybrid setup, you are essentially creating a high-performance local "cache" of your S3 data. * Performance: You avoid the S3 network bottleneck during query time because the heavy joins are already pre-computed and sitting on local NVMe/SSD (on the BEs). * Elasticity: You can scale your CNs up and down based on how many "heavy" concurrent queries you have, without having to move or rebalance the actual MV data stored on the BEs. Pro-tip: When creating your MV, make sure to define a good

DISTRIBUTED BY HASH

key that aligns with your most frequent join columns to ensure the data is distributed optimally across your BE nodes! Does that help clarify the "where" and "how" of your data modeling? Let me know if you want to dive deeper into refresh strategies! References • introduction/Architecture.md • table_design/table_design.md • quick_start/shared-data.md • administration/management/resource_management/be_label.md • using_starrocks/async_mv/Materialized_view.md

@Rocky That answering most of my question but not all. I have another point to add to this question. How would materialized views asynchronously update compared to creating tables and doing batch cronjob updates from s3 tables directly. These tables or materialized views would be 1:1 replicas of the data on s3 tables for performance reasons and the large heavy joins across tables and materialized views like I said.

Asynchronously updating Materialized Views (MVs) and using manual batch cronjobs to load S3 data into tables achieve similar end goals (local data persistence for performance), but they differ significantly in management, optimization, and how the database utilizes that data. Since your goal is to create 1:1 replicas for high-performance joins, here is a breakdown of how these two approaches compare: 1. Automation & Dependency Management * Asynchronous MVs: StarRocks acts as the orchestrator. You define the MV once with a

REFRESH ASYNC

property (e.g.,

EVERY (INTERVAL 1 HOUR)

). StarRocks manages the refresh task internally. If you have nested MVs (an MV built on another MV), StarRocks can handle the cascading refresh automatically. * Cronjob + Manual Tables: You are responsible for the orchestration. You need an external tool (like Airflow, dbt, or a custom script) to trigger

INSERT INTO ... SELECT ...

from the S3 external catalog into your local StarRocks tables. You must manually handle retries, failures, and dependency logic between tables. 2. Intelligent Partition Refresh This is the biggest advantage of MVs for large S3 datasets: * Asynchronous MVs: If your S3 data is in a supported format (like Hive or Iceberg catalogs), StarRocks can automatically detect which partitions have changed. When the refresh interval triggers, it only updates the specific partitions that are new or modified. You don't have to write any logic to "find the new data." * Manual Cronjob: To avoid a full overwrite of the table every time (which is slow and expensive), your script must manually calculate which partitions to load (e.g.,

WHERE date = 'yesterday'

). If a backfill is needed or old data changes on S3, you have to update your script logic manually. 3. Transparent Query Rewrite This is a "killer feature" for MVs that manual tables do not have: * Asynchronous MVs: Even if your application sends a query against the original S3 external table, StarRocks can automatically and transparently rewrite that query to fetch data from the Materialized View instead. This gives you the performance of local storage without requiring you to change your application's SQL code. * Manual Tables: Your application must be explicitly programmed to query the local StarRocks table name. If you ever drop that table or rename it, your application breaks. 4. Data Consistency & "Staleness" * Asynchronous MVs: You can control the trade-off between performance and freshness using the

mv_rewrite_staleness_second

property. For example, if you set it to 3600, StarRocks will use the MV for query rewrite even if it hasn't been refreshed in the last hour. If the MV is "too stale," StarRocks can automatically fall back to the slow S3 table to ensure you get correct results. * Manual Tables: There is no built-in staleness check. If your cronjob fails and the local table is 3 days old, your queries will simply return old data without warning. 5. Maintenance Overhead * MVs: Use

ALTER MATERIALIZED VIEW

to change refresh schedules or properties. Schema changes in the base S3 table can be propagated via the MV refresh mechanism more gracefully. * Manual Tables: You manage the DDL for the destination table. If the S3 schema changes, you must manually

ALTER

your local table and update your loading script. Comparison Summary Feature | Asynchronous MV | Manual Cronjob + Table Orchestration | Built-in to StarRocks | External (Airflow/Cron) Partitioning | Auto-detects changed partitions | Manual logic required Query Rewrite | Yes (Automatic acceleration) | No (Manual SQL changes) Staleness Control | Built-in via properties | Manual monitoring Setup Effort | Low (Declarative SQL) | Medium (Scripting + DDL) Rocky's Recommendation For your use case of 1:1 replicas for heavy joins, Asynchronous Materialized Views are almost always the better choice.

Where is the incremental updates with the partition mentioned in #2 Intelligent Partition Refresh documented at @Rocky?

The feature referred to as "Intelligent Partition Refresh" (often highlighted in StarRocks 3.3 release materials) refers to the capability of asynchronous Materialized Views (MVs) to automatically detect partition-level changes and refresh only the affected data. This is primarily documented in the following sections of the StarRocks documentation: *1. Incremental Refresh and Transparent Rewrite* The core "intelligent" behavior—where StarRocks compares data versions of base table partitions and skips refreshing partitions that haven't changed—is detailed here: * Documentation Link: Create a partitioned materialized view - Achieve incremental refresh and transparent rewrite *2. Adaptive Partition Refresh Strategy* Introduced in v3.3, the

adaptive

strategy allows the system to intelligently adjust the number of partitions refreshed in each batch based on the data volume. * Property:

"partition_refresh_strategy" = "adaptive"

* Property:

"partition_refresh_number"

(Controls the granularity; defaults to

1

since v3.3 to prevent OOM). * Documentation Link: Create a partitioned materialized view - Refresh granularity *3. Partition-Level Change Detection for External Catalogs* A key part of "Intelligent Refresh" is the support for detecting changes in external tables (Hive, Iceberg, Paimon, etc.) at the partition level to avoid full refreshes. * Documentation Link: Data lake query acceleration with materialized views - Choose a suitable refresh strategy Summary of Key Parameters: Parameter | Description

partition_refresh_number

| Maximum partitions to refresh in a single batch.

partition_refresh_strategy

| Set to

adaptive

for StarRocks to automatically adjust batch sizes based on resources.

partition_ttl_number

| Number of most recent partitions to keep, allowing for partial data materialization. If you are looking for the specific PR or technical detail mentioned in release blogs, it generally relates to the enhanced partition tracking and batch splitting logic introduced to make MV maintenance more stable and efficient. References • using_starrocks/async_mv/use_cases/create_partitioned_materialized_view.md • using_starrocks/async_mv/feature-support-asynchronous-materialized-views.md

@Rocky Why can I still use external storage and compute nodes with cache with run_mode not set in my nodes, is my hybrid architecture doing something fundamentally different than the shared data mode because its using shared data

Hi! I'm Rocky. It sounds like you've discovered one of StarRocks' flexible architecture features. The reason you can use external storage and compute nodes with cache even without setting

run_mode

is that StarRocks defaults to

shared_nothing

mode, and this mode fully supports External Catalogs and Data Cache. Here is a breakdown of why your current "hybrid" setup works and how it differs from a true Shared-data architecture. 1. Why it works (The Default Mode) When

run_mode

is not set, it defaults to

shared_nothing

. In this mode: * External Storage: You can create External Catalogs (e.g., Hive, Iceberg, or S3/HDFS-based catalogs) to query data directly from shared storage. * Compute Nodes (CN): You can add stateless Compute Nodes to your cluster. These nodes are designed to handle the compute for external queries without storing local replicas of internal tables. * Data Cache: Both BE (Backend) and CN (Compute) nodes can use the Data Cache feature to store frequently accessed data from these external catalogs on local disks (SSD/NVMe) to speed up subsequent queries. 2. Is it fundamentally different from Shared-data mode? Yes. Even though your setup "feels" like a shared-data architecture because you are querying shared storage with cached compute nodes, there is a fundamental difference in how Internal (Native) Tables are handled. Feature | Your Current Setup (

shared_nothing

) | Shared-data Mode (

run_mode = shared_data

) Internal Tables | Stored on local BE disks and replicated (coupled storage/compute). | Stored on Object Storage (S3/GCS/HDFS). Only metadata is local. External Tables | Stored on shared storage (Catalogs). | Stored on shared storage (Catalogs). Scaling Internal Data | Adding/removing BEs requires data rebalancing (shuffling shards). | Adding/removing CNs is **instant**; no data rebalancing is needed. Data Cache | Used to accelerate External Catalog queries. | Used to accelerate both Internal and External queries. Resilience | Relies on local replication for internal table availability. | Relies on the high availability of the object store. Summary Your current "hybrid" architecture is essentially a Shared-nothing cluster acting as a Query Engine for a Data Lake. This is a very common and high-performance pattern. However, if you wanted your StarRocks native tables (the ones you

CREATE TABLE

without a catalog) to also reside on S3 and benefit from instant scaling without rebalancing, you would need to deploy a new cluster with

run_mode = shared_data

. Note that you cannot switch an existing cluster between these modes after deployment. If you have more questions about configuring your cache or compute nodes, feel free to ask! References • deployment/shared_data/feature-support-shared-data.md • introduction/Architecture.md • data_source/data_cache.md

So @Rocky if the only limitation of not using shared data mode over my hybrid mode is writing to external storage what is the point of ever using it

While it might seem like "writing to external storage" is a hurdle, the shift to Shared-data mode is actually less about the storage location and more about operational freedom and cost efficiency. Here is why you would choose Shared-data mode over a traditional Shared-nothing (or hybrid) setup: 1. Instant Scalability (No Data Rebalancing) In a shared-nothing (hybrid) cluster, when you add a new Backend (BE) node to handle more load, the system must rebalance data. It physically moves gigabytes or terabytes of data from old nodes to the new one to ensure an even distribution. This process: * Consumes significant CPU and IO. * Takes hours or even days for large datasets. * Makes "autoscaling" based on hourly traffic nearly impossible. In Shared-data mode, Compute Nodes (CNs) are stateless. You can add 10 nodes or remove 5 nodes in seconds. Since the data lives on the object storage, the new nodes just start pulling from the shared bucket and filling their local cache immediately. 2. Significant Cost Reduction Local NVMe/SSD storage on cloud instances is expensive. Object storage (S3, GCS, Azure Blob, MinIO) is significantly cheaper (often 1/10th the cost). * Shared-nothing: You pay for high-performance disks on every node, even if 90% of your data is "cold" and rarely queried. * Shared-data: You store everything in cheap object storage. You only need enough local disk space on your compute nodes to act as a hot data cache. 3. Workload Isolation Because the data is centralized, you can spin up multiple independent compute groups pointing to the same data: * Group A: Large nodes for heavy ETL/Data Loading. * Group B: Many small nodes for high-concurrency user dashboards. * Group C: A temporary cluster for a one-off data science experiment. These groups don't compete for CPU or RAM, and they don't require you to copy the data. 4. High Availability and Reliability In shared-nothing, if a disk fails or a node goes down, the system has to rely on replicas (usually 3x) to keep the data safe. This means you are effectively paying for 3x the storage. In Shared-data mode, the object storage provider handles the 11 nines of durability. If a StarRocks compute node dies, no data is lost, and another node can take over its work immediately without needing a "recovery" or "re-replication" phase. 5. Performance (The "Cache" Factor) You might worry that external storage is slow. However, StarRocks uses a sophisticated Data Cache on the local disks of the compute nodes. * Once the "hot" data is cached, query performance is often identical to shared-nothing. * In some benchmarks (like SSB), shared-data mode can even be faster because it can leverage parallel scan and I/O merge optimizations more aggressively across the disaggregated architecture. Summary If your data size is small and static, a hybrid/shared-nothing setup is perfectly fine. But if you have growing data, fluctuating workloads, or a need to lower your cloud bill, Shared-data mode is the architecture designed to solve those specific headaches. References • deployment/shared_data/feature-support-shared-data.md • introduction/Architecture.md • deployment/shared_data/s3.md

@Rocky Ignoring the cost factor and the fact that we are using CNs to query s3 data and caching it in our compute nodes then making views in our backends what is the difference? Does the expected latency change with querying the s3 catalog in either mode if we tested them side by side. We have compute nodes configured on side by side.

When comparing these two "modes" of querying S3 data—specifically Shared-data mode (Internal Tables) vs. *External Catalog (External Tables)*—the expected latency will indeed differ, even if you use the same Compute Nodes (CNs) and the same Data Cache configuration. While both architectures utilize S3 for storage and CNs for compute, Shared-data mode (Internal Tables) will almost always provide lower latency and higher throughput compared to querying an External Catalog. Here is the breakdown of why the latency changes: 1. Storage Format & Metadata Efficiency * Shared-data (Internal): Data is stored in StarRocks’ proprietary Segment format. This format is specifically engineered for the StarRocks execution engine. It includes built-in optimizations like sophisticated zone maps, ordinal indexes, and bitmap indexes that are tightly integrated with the query processor. * External Catalog: Data is typically in open formats like Parquet or ORC. While StarRocks has a world-class vectorized reader for these formats, it cannot control the physical layout as strictly as it does with its internal segment format. Metadata must also be fetched from an external provider (like AWS Glue or Hive Metastore), which adds a round-trip overhead that internal tables don't have. 2. Indexing Capabilities * Shared-data (Internal): You get the full suite of StarRocks indexing (Global Dictionary for string acceleration, Bitmap indexes, Bloom filters, and Range indexes). These allow the CNs to skip massive amounts of S3 data before it even reaches the cache. * External Catalog: You are largely limited to the statistics provided by the file format (like Parquet min/max). While StarRocks can still do some pruning, it isn't as granular or efficient as the internal indexing. 3. Data Cache Integration Both modes benefit from the Data Cache (local disk cache on CNs), but they utilize it differently: * Shared-data (Internal): The cache stores optimized internal segments. When you hit the cache, the performance is designed to be identical to a "shared-nothing" (local disk) architecture. * External Catalog: The cache stores the raw Parquet/ORC blocks. While this significantly speeds up subsequent reads by avoiding S3 I/O, the CN still has to perform the CPU-intensive task of decoding those formats into the StarRocks memory format every time. 4. Performance Benchmarks In StarRocks' own testing (using the SSB 1TB dataset): * Shared-data (Internal) with Cache: Roughly 4x faster than a standard External Catalog (Hive/Iceberg) setup without cache. * Shared-data (Internal) without Cache: Even without the cache hit, the internal shared-data mode is roughly 1.4x faster than querying Hive/External data due to the efficiency of the segment format and I/O merging optimizations. Summary: Which should you choose? * Use Shared-data (Internal Tables) if you have strict SLA requirements for low-latency queries (sub-second to a few seconds) and you want the best possible performance-to-cost ratio. * Use External Catalog (Views) if your priority is "Zero-ETL" and you need to query data exactly where it lives (e.g., produced by an external Spark/Flink pipeline) without the overhead of ingesting it into StarRocks internal storage. The Bottom Line: If you test them side-by-side on the same CNs, the Shared-data Internal Tables will yield lower latency because the engine spends less time decoding data and more time processing it. References • deployment/shared_data/feature-support-shared-data.md • deployment/shared_data/s3.md • knowledge/usage/load/shared_data_batch_ingestion.md • quick_start/shared-data.md