Why Stream Load Beats INSERT 30x in StarRocks Compute-Storage Separation

Background

In a StarRocks compute-storage separation cluster, backend engineers observed that inserting 1,000 rows via INSERT took ~20 seconds, while the same operation in a coupled compute-storage cluster completed within 2 seconds. Converting the data to CSV and loading via Stream Load finished in under 1 second. The recommendation was to switch bulk loads from INSERT to Stream Load.

Core Reason: Design Goals, Data Pipeline, and Resource Logic Mismatch

In compute-storage separation clusters, Stream Load outperforms INSERT by over 20x because their design targets, data processing pipelines, and resource utilization logic are fundamentally misaligned with different scenarios. Stream Load deeply fits the "compute-storage network interaction" characteristic of separation architectures, fundamentally avoiding INSERT 's inefficiency bottlenecks. Five core dimensions explain the gap:

1. Design Positioning: Bulk Import vs. Sporadic Writes (Fundamental Difference)

The two mechanisms target completely different use cases, setting their performance ceilings:

Stream Load : Designed for large-scale bulk data import (supporting GB-level single loads), handling CSV/JSON/Parquet formats. Its core goal is "high throughput, low latency for massive data"; all logic revolves around batch optimization.

INSERT : Designed for single-row or small-batch writes (e.g., manual test inserts, small ETL results). Its core goal is "flexibility, low barrier", not "high throughput".

Performance Impact: Loading 1 million rows via INSERT requires tens of thousands of small requests; Stream Load completes in one request. The fixed overhead of network handshake, authentication, and connection establishment per request accumulates in INSERT, creating an order-of-magnitude gap.

2. Data Processing Pipeline: Short Parallel Path vs. Long Row-by-Row Path (Pipeline Efficiency Gap)

In compute-storage separation, compute nodes (CN) interact with storage (OSS/S3) over the network. Pipeline length and processing mode directly affect speed:

Key Differences: Stream Load bypasses the Frontend (FE) and connects directly to compute nodes (CN), eliminating one network hop.

Batch parallel parsing is 1-2 orders of magnitude more efficient than row-by-row parsing (e.g., Parquet enables columnar batch reads; CSV can be split into chunks for parallel processing).

In compute-storage separation, "network transfer" is the core bottleneck. Stream Load 's short path plus parallel processing reduces pipeline latency at the source.

3. Transactions and Metadata: One Bulk Transaction vs. N Small Transactions (Overhead Accumulation Gap)

StarRocks' transaction and metadata interactions (schema validation, partition permissions, transaction log sync) are high-frequency overhead operations. The transaction granularity differs drastically:

Stream Load : One bulk import corresponds to one transaction , requiring only:

One metadata validation (confirming table partitioning and bucketing rules).

One transaction commit (generating one transaction log, syncing metadata once).

INSERT : Each INSERT defaults to one small transaction (even INSERT ... SELECT has far finer granularity than Stream Load). Importing 1 million rows may execute 100,000 transactions, each repeating:

FE interaction for metadata validation.

Transaction ID allocation and log write.

Commit-time sync of partition row counts, file info, and other metadata.

Performance Impact: Transaction and metadata interactions involve distributed locks and log synchronization. INSERT 's "N small transactions" accumulate overhead 10-100x higher than Stream Load 's "one large transaction", a core source of the performance gap.

4. Storage I/O Adaptation: Large Batch Files vs. Scattered Small Files (Architecture Fit Gap)

In compute-storage separation, network I/O efficiency between compute (CN) and storage (OSS) is the key bottleneck. The two write patterns fit completely different scenarios:

Stream Load :

After CN processing, data is written to storage in large file blocks (default 64 MB/block, configurable), drastically reducing network I/O count (one I/O transfers hundreds of thousands of rows).

Supports Snappy/GZIP compression, reducing network transfer and storage write volume (typical compression ratio 2-5x).

Writes directly to target partition bucket files, avoiding temporary files and the "write-delete temp file" I/O amplification.

INSERT :

Each write handles only dozens or thousands of rows, requiring frequent storage interactions ( open file → write → close file ), causing network I/O count to explode.

Generates massive numbers of KB-level small files (each bucket may have hundreds of small files). This not only lowers I/O efficiency but also creates "small file scan" performance issues for subsequent queries (small files are a query performance killer in compute-storage separation).

Performance Impact: Compute-storage separation network I/O latency is typically milliseconds. Stream Load uses "few large I/Os" to avoid latency stacking, while INSERT uses "many small I/Os" that amplify latency. The I/O efficiency gap can reach 10-100x.

5. Resource Utilization: Centralized Scheduling vs. Scattered Consumption (Resource Efficiency Gap)

Compute-storage separation clusters share CN node resources (CPU, memory, network). The two utilization patterns differ significantly:

Stream Load :

Parameters like max_filter_ratio (error rate control) and timeout enable centralized resource scheduling , e.g., letting one CN node use multiple cores to process a batch in parallel, achieving high resource utilization.

During in-memory caching of batch data, bucket data hit rates are higher (reducing duplicate reads), avoiding frequent memory paging.

INSERT :

Scattered requests arrive at different times, forcing CN nodes to frequently context-switch (high CPU context-switch overhead), preventing concentrated core utilization.

High-frequency INSERT causes CN network connection counts to spike (one connection per request), triggering connection pool limits and queuing new requests.

Summary: Batch Optimization + Architecture Fit Amplifies the Gap

Stream Load

's speed stems from the deep alignment of batch processing logic with the compute-storage separation architecture :

Using "batch transactions" to reduce metadata overhead, "parallel parsing" to boost compute efficiency, "large-file I/O" to lower network bottlenecks, and "centralized resources" to improve utilization.

Conversely, INSERT 's "small batches, many transactions, row-by-row processing" has every inefficiency infinitely amplified in the "network-I/O-sensitive" environment of compute-storage separation, ultimately creating a 10x+ performance gap.

Best Practices

Bulk data import (e.g., logs, business data sync): Prefer Stream Load (or Spark Load).

Real-time small-batch writes (e.g., second-level updates): Use Routine Load.

Only sporadic data (e.g., test data): Use INSERT.