How StarRocks Materialized Views Power Real‑Time Lakehouse Analytics

StarRocks 3.0 Overview

StarRocks 3.0 adds a data‑lake analysis layer that can query external tables stored in Hive, Iceberg, Hudi, DeltaLake, MySQL, PostgreSQL, Oracle, Elasticsearch, and generic file systems (ORC, Parquet) via a unified query engine. It supports storage‑compute separation, native S3/HDFS storage, and deployment on‑premise or on Kubernetes.

Catalog Feature

External tables are now managed by a Catalog. A single statement registers a source and automatically imports metadata from a Hive Metastore.

CREATE EXTERNAL CATALOG my_catalog
PROPERTIES (
  "type" = "hive",
  "hive.metastore.uris" = "thrift://host:9083"
);

After registration, any table in the catalog can be queried directly, and internal StarRocks tables can be joined with external tables in the same query.

Trino Compatibility

StarRocks implements a Trino‑compatible SQL parser while retaining the MySQL protocol. Approximately 99 % of Trino syntax is supported, enabling migration without data movement and delivering several‑fold performance gains.

I/O Optimizations for Lake‑house Workloads

Column‑level I/O merging: small column reads are combined into a single scan.

Whole‑file reads for tiny files to reduce per‑file overhead.

Coordinated memory‑and‑disk cache for S3 objects; data first cached in memory, overflow written to local disk.

Predicate‑driven delayed materialization: only columns required by the WHERE clause are read.

Top‑N push‑down and vectorized execution.

These techniques make Iceberg queries 3‑5× faster than Trino on the same hardware.

Materialized View (MV) Capabilities

StarRocks MVs support:

Partitioning (time‑based, hash, etc.) for data pruning.

Refresh modes: full, incremental, timed (cron‑like), and manual.

Resource‑group isolation to separate MV maintenance from ad‑hoc queries.

Definition over internal tables, external lake tables, or JDBC sources.

Typical use cases:

Incremental aggregation of high‑throughput event logs using bitmap or HyperLogLog and incremental MV refresh.

Lakehouse ETL replacement: ODS → DWD → DWS → ADS layers can be built with external‑table MVs, eliminating separate ETL pipelines.

Real‑time dashboards: a two‑layer MV architecture reduced latency from ~3 s to ~30 ms while providing minute‑level exact distinct counts for a ride‑sharing platform.

Resource Isolation

Two isolation mechanisms are provided:

Soft isolation via configurable resource groups (CPU, memory, disk quotas). Groups can be shared across workloads; total allocation may exceed 100 % but is throttled per group.

Hard isolation using Warehouse nodes that run MVs on dedicated compute, guaranteeing that mixed workloads do not interfere.

Future Directions

Tighter cloud‑native resource management and elastic scaling.

Expanded ETL features (e.g., tighter integration with Flink) to complement MV‑based pipelines.

Richer real‑time ingestion and incremental computation capabilities.

Q&A

Q: Does a materialized view use three replicas for storage?

A: Yes. MVs are stored like regular tables; the replication factor (1‑3 copies) is configurable per table, and the same cloud‑native storage‑compute separation applies.