How StarRocks’ Spill to Disk Boosts Query Stability and Performance

Memory Management in StarRocks

StarRocks stores all intermediate query data in memory. When memory is insufficient, queries are aborted, which can affect online workloads. Typical scenarios that stress memory include lightweight ETL jobs (e.g., INSERT INTO … SELECT), building wide‑table materialized views, and ad‑hoc analytical queries that involve large‑table joins or high‑cardinality aggregations.

Resource Isolation (since 2.2)

StarRocks 2.2 introduced resource groups to allocate CPU and memory separately for online queries and background workloads. While this isolates online traffic, it does not guarantee that ETL or occasional large queries can finish without running out of memory.

Spill (Intermediate Result Disk‑Write) – Version 3.0+

StarRocks 3.0 adds a spill mechanism that writes the intermediate results of heavy operators to disk, freeing memory for the rest of the query. The system estimates the memory required for an operator before processing data. If the estimated reservation would exceed 80 % of the query’s memory limit , spill is triggered.

Supported operators:

Aggregation

Sort

HASH JOIN (including LEFT, RIGHT, FULL, OUTER, SEMI, and INNER joins)

Two spill modes are available:

Forced spill : users explicitly enable spill for a query.

Automatic spill : the engine decides at runtime based on current memory usage.

When an operator begins processing, StarRocks pre‑allocates the estimated memory. If the allocation stays below the 80 % threshold, execution continues in memory; otherwise the operator’s intermediate data is written to disk.

Performance Evaluation

Cluster configuration : 1 Frontend (8 CPU × 2 GB) + 3 Backends (16 CPU × 4 GB), each BE attached to two PL1‑level cloud disks.

Test set : 99 TPC‑DS queries on a 10 TB dataset.

Without spill: 15 queries failed due to memory exhaustion.

With spill (automatic): all 99 queries succeeded, demonstrating a dramatic increase in stability.

Comparison with Spark : Under the same hardware and with automatic spill enabled, StarRocks achieved 4.35× higher overall query throughput than Spark.

Materialized Views + Spill

Materialized views (MVs) accelerate common analytical patterns but can fail to build on large datasets because they require full‑table joins without predicate push‑down, exhausting memory. Enabling spill allows MVs to be constructed and refreshed successfully, and queries that are rewritten to use the MV gain significant speed‑ups.

TPC‑H 1 TB test :

Direct queries without spill often failed due to memory limits.

Enabling spill allowed all queries to finish.

Building a wide‑table MV failed without spill; with spill the MV was built and refreshed, and subsequent queries showed marked performance improvement.

Future Directions

Spill will be further integrated with resource isolation and multi‑warehouse capabilities to provide finer‑grained resource control for diverse workloads, enabling customized memory and CPU quotas per workflow.