StarRocks for Sub-Second Lakehouse Analytics: Setup and Tuning Guide

Running analytical queries directly against a data lake usually means a trade-off: you get flexibility and cheap storage, but you give up the sub-second response times a proper data warehouse gives you. StarRocks was built to close that gap.

This guide walks through what StarRocks actually does for lakehouse analytics, how to set it up against Iceberg and Delta Lake, and how to tune it so queries run fast instead of just eventually.

What is StarRocks for Lakehouse Analytics?

StarRocks is an open-source, MPP (massively parallel processing) analytical database built for real-time and interactive queries. What makes it relevant to lakehouse architecture specifically is that it can query data sitting in Iceberg, Delta Lake, Hudi, and other formats directly, without first loading that data into StarRocks’ own storage.

Key architectural advantages:

The Zero-ETL Lakehouse Architecture (FE vs. CN)

StarRocks keeps its architecture deliberately simple: two node types, no external coordination service required.

Frontend Nodes (FE) vs. Compute Nodes (CN)

Frontend Nodes (FE) handle:

• SQL parsing and query planning
• Cluster metadata management
• Client connection handling
• Generating both the logical and physical execution plans for a query

FE nodes use the Raft protocol among themselves for leader election and metadata consistency, so a production cluster typically runs 3 FEs to maintain quorum.

Compute Nodes (CN) handle:

• Executing the physical query plan
• Caching hot data locally (Data Cache)
• Reading directly from remote storage (S3, HDFS, GCS, Azure Blob, or S3-compatible systems like MinIO) when data isn’t cached

CNs are stateless. They don’t store the source of truth for any data, since that lives in the lakehouse table format itself (Iceberg or Delta Lake) on object storage. This matters practically: you can add or remove CN nodes to scale query concurrency up or down without any data rebalancing, since there’s no data ownership to reshuffle.

This is different from StarRocks’ classic Backend (BE) nodes, which store data locally and are used when StarRocks manages its own native tables rather than querying an external lakehouse. For pure lakehouse analytics, CNs are the node type you’ll be scaling.

Step-by-Step Setup: External Catalogs for Iceberg & Delta Lake

External catalogs are how StarRocks connects to a lakehouse metastore without moving any data. Once a catalog is created, you can query the tables in it directly using standard SQL.

Setting Up an Apache Iceberg External Catalog

If your Iceberg tables are tracked in a Hive Metastore:

CREATE EXTERNAL CATALOG iceberg_catalog_hms
PROPERTIES
(
    "type" = "iceberg",
    "iceberg.catalog.type" = "hive",
    "iceberg.catalog.hive.metastore.uris" = "thrift://<metastore-host>:9083",
    "aws.s3.use_instance_profile" = "true",
    "aws.s3.region" = "us-east-1"
);

If you’re using AWS Glue as the catalog service instead:

CREATE EXTERNAL CATALOG iceberg_glue_catalog_glue
PROPERTIES
(
    "type" = "iceberg",
    "iceberg.catalog.type" = "glue",
    "aws.s3.region" = "us-east-1",
    "aws.s3.use_instance_profile" = "true"
);

Once created, you can browse and query the catalog like any database:

SET CATALOG iceberg_catalog;
SHOW DATABASES;
SHOW TABLES FROM analytics_db;
SELECT * FROM analytics_db.orders LIMIT 10;

Setting Up a Delta Lake External Catalog

For Delta Lake tables tracked in a Hive Metastore:

CREATE EXTERNAL CATALOG delta_catalog
COMMENT "External catalog to Delta Lake"
PROPERTIES
(
    "type" = "deltalake",
    "hive.metastore.uris" = "thrift://<metastore-host>:9083"
);

For Delta Lake tables tracked via AWS Glue:

CREATE EXTERNAL CATALOG delta_glue_catalog
COMMENT "External catalog to Delta Lake via Glue"
PROPERTIES
(
    "type" = "deltalake",
    "hive.metastore.type" = "glue",
    "aws.s3.access_key" = "<access-key>",
    "aws.s3.secret_key" = "<secret-key>",
    "aws.s3.region" = "us-east-1"
);

The Secret to Sub-Second Speeds: Configuring StarRocks Data Cache

Data Cache is what actually gets you from “queryable” to “fast.” Without it, every query against your lakehouse pays the full remote I/O cost. With it, StarRocks pulls data from remote storage in blocks, caches it locally on the CN node’s disk, and serves repeat scans from that local cache instead.

From StarRocks v3.4 onward, a single unified Data Cache instance handles both external catalog queries and cloud-native table queries, so there’s one caching layer to reason about, not several.

Production Configuration (cn.conf)

Data Cache is enabled by default from v3.3.0 onward, but production deployments usually still want to tune a few parameters explicitly rather than rely on defaults alone.

# Root path(s) where cached data is stored on local disk
storage_root_path = /data/disk1;/data/disk2

# Memory allocated for in-memory cache tier (in addition to disk cache)
datacache_mem_size = 10G

# Enable Data Cache explicitly (default is true from v3.3.0+)
datacache_enable = true

A few tuning notes worth acting on directly:

• Use multiple disks for storage_root_path where possible. StarRocks spreads cached blocks across the listed paths, so more disks generally means more cache throughput, not just more cache capacity.
• Set datacache_mem_size deliberately, not to zero. The default memory cache limit is 0, meaning no in-memory tier unless you configure one. For workloads with a small hot dataset that gets scanned repeatedly, a modest in-memory allocation avoids disk I/O entirely for the hottest data.
• Check cache hit rate through the query profile, not by guessing. Look at CompressedBytesReadRemote and IOTimeRemote in the query profile. If these are non-zero and large, the query missed the cache and fell back to remote storage, which is exactly the pattern you’re trying to eliminate.
• Table-level control matters for cost management. The datacache.enable table property and datacache.partition_duration property let you cache only the partitions worth caching, for example, keeping only the last 90 days of a large fact table warm while older partitions are read from remote storage on the rare occasion they’re queried.

Automated Cache Warmup Strategies

Rather than waiting for the first user query to populate the cache (and pay that latency penalty), you can warm the cache proactively:

• Scheduled warmup queries: Run a lightweight scheduled query against your most commonly accessed tables and partitions during off-peak hours, so the cache is already populated before your peak query window starts.
• Populate on refresh, not on query: For materialized views built over lakehouse tables (covered in the Asynchronous Materialized Views section below), configure the refresh task itself to populate the cache, so the underlying scan data is warm by the time the materialized view is ready for use.
• I/O adaptor for high-load periods: StarRocks includes an I/O adaptor feature that automatically routes some cache requests to remote storage when local disk I/O load is high, protecting against cache-disk contention degrading performance further. This is enabled by default and generally doesn’t need manual tuning unless you’re seeing consistent disk saturation.

Query Engine Optimization: CBO, Joins, and Diagnostics

A cache alone won’t fix a badly planned query. StarRocks’ cost-based optimizer needs accurate statistics to choose sensible join orders and access paths, especially on lakehouse tables where StarRocks doesn’t own the underlying storage layout.

Collecting Table Statistics for CBO

By default, StarRocks automatically collects full statistics and checks for data changes periodically. For large or frequently updated lakehouse tables, it’s worth collecting statistics manually and deliberately rather than waiting on the automatic cycle:

-- Full statistics collection on specific columns, run synchronously
ANALYZE FULL TABLE orders (order_id, customer_id, order_date)
WITH SYNC MODE;

-- Sampled collection for very large tables, run asynchronously
ANALYZE SAMPLE TABLE orders
WITH ASYNC MODE
PROPERTIES ("statistic_sample_collect_rows" = "1000000");

Use synchronous collection for smaller tables where you want statistics available immediately. Use asynchronous collection for large tables, since the collection job runs in the background and you can check its progress with:

SHOW ANALYZE STATUS;

For columns with highly skewed value distributions (a common pattern in lakehouse fact tables, like a status column with 95% of rows in one value), collect a histogram instead of relying on basic statistics alone:

ANALYZE TABLE orders UPDATE HISTOGRAM ON order_status
WITH 32 BUCKETS;

Histograms give the CBO a much more accurate picture of skew, which directly affects join order decisions and predicate selectivity estimates.

Diagnostic Query Profiling with EXPLAIN ANALYZE

When a query is slower than expected, don’t guess, profile it. EXPLAIN ANALYZE runs the query and returns the actual execution plan along with real runtime metrics per operator, rather than just the estimated plan.

EXPLAIN ANALYZE
SELECT c.customer_name, SUM(o.total_amount)
FROM iceberg_catalog.sales.orders o
JOIN iceberg_catalog.sales.customers c ON o.customer_id = c.customer_id
WHERE o.order_date >= '2026-01-01'
GROUP BY c.customer_name;

What to look for in the output:

• Which join type was chosen (broadcast vs. shuffle join) and whether it matches the actual size of the tables involved. A shuffle join on a table small enough to broadcast is a common sign that statistics are stale or missing.
• Scan-level metrics, including how much data was read from cache versus remote storage, which directly reflects whether your Data Cache setup is actually working for this query.
• Per-operator time and memory cost, which tells you whether the bottleneck is the scan itself, the join, or the aggregation step, rather than treating “the query is slow” as one undifferentiated problem.

Advanced Acceleration: Asynchronous Materialized Views (MVs)

For queries that get run repeatedly, especially dashboard queries hitting the same aggregation pattern, materialized views let you pre-compute the result once and serve it many times, without changing the queries your application or BI tool sends.

Creating an Async MV over Lakehouse Tables

CREATE MATERIALIZED VIEW mv_daily_sales
REFRESH ASYNC EVERY (INTERVAL 1 HOUR)
AS
SELECT
    order_date,
    region,
    SUM(total_amount) AS daily_revenue,
    COUNT(*) AS order_count
FROM iceberg_catalog.sales.orders
GROUP BY order_date, region;

A few things worth knowing before you rely on this in production:

• External catalog-based materialized views don’t auto-refresh on base table changes. Since StarRocks can’t always detect partition-level changes in external formats the way it can on its own native tables, refresh has to be scheduled on an interval or triggered manually, as shown above with the hourly refresh.
• Partial partition refresh keeps refresh costs down. For large, partitioned fact tables, configure the materialized view to refresh only the partitions that changed, rather than recomputing the entire view every cycle.
• Query rewrite only applies to SPJG-pattern views. StarRocks can transparently rewrite queries to use a materialized view automatically, but only when the view’s definition fits the Scan-Filter-Project-Aggregate pattern. More complex view definitions can still be queried directly, but won’t be picked up automatically by unrelated queries.

Transparent Query Rewriting in Action

The real value of async MVs shows up when a query that never mentions the materialized view still benefits from it:

-- This query doesn't reference mv_daily_sales at all
SELECT region, SUM(total_amount)
FROM iceberg_catalog.sales.orders
WHERE order_date = '2026-08-01'
GROUP BY region;

If mv_daily_sales is active and its refreshed data covers the queried date range, StarRocks’ optimizer detects that this query can be satisfied by reading from the materialized view instead of scanning and aggregating the full base table, and rewrites the execution plan accordingly.

Sizing & Production Tuning Checklist

Before going live, work through this list:

Why Choose Ksolves for StarRocks Consulting & Managed Services?

Getting StarRocks running is straightforward. Getting it tuned so it consistently delivers sub-second lakehouse queries under real production load takes more hands-on experience with the specifics covered above. As part of our broader big data consulting services, every StarRocks engagement starts with a workload audit, so tuning effort goes to the queries that actually hurt.

Frequently Asked Questions (FAQs)

Still have questions about StarRocks? Contact our team.