StarRocks vs. ClickHouse: 2026 Real-Time OLAP Benchmarks, Star Schema Joins & Upsert Architecture

# StarRocks vs ClickHouse OLAP Benchmark: 2026 Architecture, Star Schema Joins & Upsert Guide

Summary: This 2026 technical guide evaluates the StarRocks vs ClickHouse OLAP benchmark across 100-million-row star schema and wide-table analytical workloads. ClickHouse delivers superior raw scan throughput on flat, append-only datasets, but StarRocks demonstrates lower query latencies on multi-table star schema joins via its Cascades Cost-Based Optimizer and dynamic runtime filtering. Furthermore, StarRocks Primary Key tables process mutable streaming updates via a persistent index without the severe read-time latency degradation incurred by ClickHouse ReplacingMergeTree FINAL queries.

---

The 2026 Real-Time OLAP Landscape: Vectorized Engines and Mutable Workloads

Analytical database architecture has shifted away from brittle batch pipelines toward interactive, sub-second query execution directly over mutable production data. For years, data engineering teams accepted an uncomfortable compromise: maintaining complex Apache Spark or dbt Extract-Transform-Load (ETL) DAGs to flatten relational tables into massive denormalized "One Big Table" (OBT) schemas. This preprocessing was required because first-generation columnar engines suffered severe performance degradation or memory exhaustion when executing multi-table joins.

In this real-time analytical database comparison 2026, two open-source engines dominate high-throughput, low-latency deployments: ClickHouse and StarRocks.

ClickHouse established the gold standard for raw columnar scan performance on wide, immutable datasets. Its shared-nothing architecture, aggressive data compression, and specialized vectorized execution primitives deliver unmatched throughput for single-table event streams. However, modern operational platforms increasingly require joining high-frequency transaction data with rapidly changing dimension tables—such as customer profiles, order fulfillment states, and pricing tiers—without introducing multi-minute ETL latency.

StarRocks was engineered specifically to bridge this gap. By coupling a Cascades-style Cost-Based Optimizer (CBO) with dynamic runtime filtering and an LSM-tree-backed Primary Key storage engine, StarRocks executes multi-table star schema joins directly without mandatory denormalization.

Choosing between these two technologies requires balancing raw scan throughput against multi-table join complexity, memory utilization, and the computational cost of handling mutable Change Data Capture (CDC) streams.

---

Distributed Architecture: StarRocks Decoupled CBO vs. ClickHouse MergeTree

Understanding query execution differences requires examining how each database structures storage layout, memory buffers, and distributed query planning.

StarRocks: Cascades Cost-Based Optimizer and Decoupled Compute Nodes (CN)

StarRocks employs a decoupled architecture comprising two primary daemon types: Frontend (FE) and Backend (BE), or stateless Compute Nodes (CN) in its Shared-Data deployment mode.

1. Frontend (FE) Coordination & CBO: The FE node parses SQL, manages cluster metadata, and runs a Cascades-style Cost-Based Optimizer (CBO). The CBO collects column-level statistics (cardinality, null fractions, min/max values, histograms) to construct optimal physical execution Directed Acyclic Graphs (DAGs). The optimizer dynamically reorders multi-table joins, chooses between broadcast hash joins, shuffle joins, and bucket shuffle joins, and injects Global Runtime Filters (RF) into downstream scan operators.

2. SIMD Vectorized Execution Engine: Compute nodes process relational tuples in memory using vectorized batch blocks (typically 4,096 rows). The execution pipeline maximizes CPU cache locality by applying Single Instruction, Multiple Data (SIMD) vector instructions (AVX-512, AVX2, and ARM NEON) across contiguous columnar memory buffers.

3. Decoupled Compute (Shared-Data Mode): In StarRocks 3.x+, storage can be completely decoupled from compute. Compute Nodes (CN) execute stateless queries against object stores (AWS S3, Google Cloud Storage, or MinIO), maintaining an intelligent local NVMe cache for hot data blocks. When query demand surges, CN instances scale horizontally in Kubernetes without triggering expensive data rebalancing.

flowchart TD
    subgraph Client["Client Application Layer"]
        BI["BI Dashboards / SQL Clients"]
        CDC["Real-Time CDC Stream (Kafka)"]
    end

    subgraph StarRocksCluster["StarRocks Decoupled MPP Architecture"]
        direction TB
        FE["StarRocks Frontend (FE)
Metadata + Cascades CBO Optimizer"]
        
        subgraph ComputeLayer["Stateless Compute Layer (CN / BE)"]
            CN1["Compute Node 1
Vectorized SIMD + Hash Join"]
            CN2["Compute Node 2
Dynamic Runtime Filter Engine"]
            CN3["Compute Node 3
Vectorized SIMD + Hash Join"]
        end
        
        subgraph StorageLayer["Decoupled Storage & Persistent Index"]
            OBJ["Shared Object Store (S3 / MinIO)
Immutable Parquet-like Segments"]
            PK_IDX["Local NVMe Disk
Primary Key RocksDB-Style Index"]
        end
        
        FE -->|Physical Execution Plan| CN1
        FE -->|Physical Execution Plan| CN2
        FE -->|Physical Execution Plan| CN3
        
        CN1 <-->|Data Exchange / Shuffle| CN2
        CN2 <-->|Data Exchange / Shuffle| CN3
        
        CN1 -->|Block Fetch & Cache| OBJ
        CN2 -->|Block Fetch & Cache| OBJ
        CN3 -->|Block Fetch & Cache| OBJ
        
        CN1 --- PK_IDX
    end

    subgraph ClickHouseCluster["ClickHouse Shared-Nothing Architecture"]
        direction TB
        COORD["ClickHouse Node 1
(Query Coordinator + MergeTree)"]
        NODE2["ClickHouse Node 2
(Worker + MergeTree Data Part)"]
        NODE3["ClickHouse Node 3
(Worker + MergeTree Data Part)"]
        
        COORD <-->|Scatter-Gather Sub-Queries| NODE2
        COORD <-->|Scatter-Gather Sub-Queries| NODE3
        
        subgraph CH_Storage["Shared-Nothing Attached NVMe Disks"]
            DISK1["Node 1 NVMe
MergeTree Parts"]
            DISK2["Node 2 NVMe
MergeTree Parts"]
            DISK3["Node 3 NVMe
MergeTree Parts"]
        end
        
        COORD --- DISK1
        NODE2 --- DISK2
        NODE3 --- DISK3
    end

    Client -->|Analytical Query| FE
    Client -->|Single-Table Scan| COORD
    CDC -->|Stream Load| CN1
    CDC -->|Batch Insert| COORD

ClickHouse: Shared-Nothing MergeTree and Vectorized Monolithic Execution

ClickHouse follows a traditional shared-nothing architecture centered on the MergeTree storage engine family.

1. MergeTree Data Layout: Tables are divided into immutable physical directories called data parts. Each part stores data in compressed columnar files (.bin) alongside sparse primary index marks (.mrk3, typically indexing every 8,192 rows). When queries include filter predicates matching the primary sort key, ClickHouse uses sparse mark ranges to prune massive block sequences without scanning disk blocks.

2. Scatter-Gather Coordination: Any ClickHouse node can serve as a query coordinator. When an analytical query arrives at a distributed table, the receiving coordinator generates local sub-queries and dispatches them across all shards. Each shard executes its scan independently, returning serialized intermediate result sets to the coordinator for final aggregation.

3. Join Processing Constraints: ClickHouse treats joins differently than a classical Massively Parallel Processing (MPP) system. Because it historically lacked a cost-based exchange optimizer, ClickHouse performs hash joins by loading the entire right-side table into an in-memory hash table on the coordinator or broadcasting it across nodes. If the right-side table exceeds the max_bytes_before_external_join threshold, ClickHouse must spill hash partitions to disk or abort the query with an out-of-memory (OOM) exception.

---

Benchmark Methodology and Environment Specification

To evaluate performance under production conditions, analytical systems are evaluated across two standard analytical workloads:

1. Star Schema Benchmark (SSB) at Scale Factor 100 (100 million rows in the central lineorder fact table, joined against customer, supplier, part, and date dimension tables).

2. ClickBench Web Analytics Benchmark (100 million rows from the standard hits dataset containing 105 columns of mixed types).

Cluster Hardware Topology

The reference evaluation cluster was deployed on Amazon Web Services (AWS) within a single Availability Zone (us-east-1a) using high-performance compute instances:

• Instance Type: 3x AWS EC2 c6i.8xlarge dedicated nodes.
• Compute Resources: 32 vCPUs (Intel Xeon 8375C @ 3.50 GHz, AVX-512 enabled) and 64 GiB DDR4 RAM per node.
• Storage Configuration: 2x 1TB AWS gp3 NVMe SSDs configured in RAID 0 per node (12,000 provisioned IOPS, 500 MB/s sustained disk throughput).
• Network Fabric: Dedicated 12.5 Gbps enhanced networking interface (ENA) with single-digit microsecond inter-node latencies.
• Operating System: Ubuntu 22.04 LTS (Linux Kernel 5.15.0-1048-aws, transparent_hugepage=madvise).

Falsifiability & Primary Evidence Protocol

Query performance and execution characteristics must be verified across consecutive warm-cache runs following cold-cache priming runs. Hardware telemetry and diagnostic traces are monitored using standard Linux profiling and database introspection tools:

• StarRocks Diagnostics: Monitored via EXPLAIN ANALYZE execution profiles, node-level memory metrics from http://<be_host>:8040/mem_tracker, and query audit records in fe.audit.log.
• ClickHouse Diagnostics: Captured directly from system metadata tables (system.query_log, system.part_log, system.metric_log), evaluating memory_usage, read_rows, read_bytes, and ProfileEvents.
• Falsifiability Check: A benchmark trial is invalidated if ProfileEvents['MemoryOvercommitWaitTimeMicroseconds'] in ClickHouse exceeds zero, or if a StarRocks query execution plan triggers unpartitioned broadcast shuffles when bucket shuffle joins were viable.

---

Empirical Performance Matrix: SSB and ClickBench Across 100M Rows

The benchmark results reflect core architectural divergence: ClickHouse excels at wide, flat single-table scanning, whereas StarRocks displays definitive superiority across multi-table star schema joins and mutable row-level ingestion.

Performance Summary Matrix

Query Execution Scaling Across SSB Query Categories (SF100)
Complexity Progression: Single-Table Scan (Q1) -> Multi-Table Joins (Q2-Q4)

SSB Q1.x (Single-Table Scan & Filter)
ClickHouse:  [===] Faster raw scan throughput via branchless SIMD
StarRocks:   [====] Vectorized scan with runtime statistics

SSB Q2.x (Two-Table Star Join)
ClickHouse:  [========] Coordinator hash table / broadcast build
StarRocks:   [=====] Early fact pruning via dynamic Bloom runtime filter

SSB Q3.x (Three-Table Star Join)
ClickHouse:  [================] Fixed join order; larger intermediate state
StarRocks:   [=======] CBO dynamic join reordering minimizes intermediate rows

SSB Q4.x (Five-Table Multi-Way Join)
ClickHouse:  [============================] Significant data shuffle & hash memory
StarRocks:   [=========] Multi-stage MPP shuffle pushdown & runtime filtering

Multi-Table Join Performance: StarRocks Cost-Based Optimizer vs. ClickHouse

The empirical gap between the two engines broadens dramatically as query complexity scales from single-table scans to multi-table joins. In evaluating clickhouse vs starrocks multi table join capabilities, StarRocks maintains sub-second query latency on complex 5-table queries (SSB Q4.1–Q4.3), whereas ClickHouse experiences significant latency and memory scaling challenges.

This divergence stems from the execution mechanics of the starrocks cost based optimizer vs clickhouse:

1. Global Runtime Filters (GRF): During a distributed join, StarRocks builds a compact Bloom filter from the smaller dimension table (e.g., supplier filtered by region) on the build side of the hash join. This filter is transmitted across the network exchange and injected directly into the scan operators of the large lineorder fact table on remote nodes. By evaluating the Bloom filter against column blocks at the storage layer, StarRocks prunes the majority of non-matching fact table rows before tuples are unpacked, serialized into memory, or transmitted across the network.

2. Dynamic Join Reordering: The StarRocks CBO calculates intermediate result sizes across join permutations. In SSB Q4.2, it automatically places the highly selective date and supplier dimensions ahead of part and customer, reducing intermediate memory hash table footprints.

3. ClickHouse Hash Join Bottlenecks: ClickHouse was originally architected for pre-aggregated, denormalized datasets. When evaluating distributed joins across shards (such as GLOBAL JOIN), ClickHouse typically serializes the complete right-side table into an in-memory hash table on the coordinator or broadcasts it across worker nodes. Without cost-based join reordering or cross-operator dynamic runtime filters, large dimension tables can saturate network bandwidth and cause resident memory to surge, potentially triggering out-of-memory errors or forcing external disk spills via max_bytes_before_external_join.

-- StarRocks EXPLAIN output snippet demonstrating Dynamic Runtime Filter injection
-- Hash Join between lineorder (fact) and supplier (dimension)
|  Join Predicates: `lo_suppkey` = `s_suppkey`                               |
|  build runtime filters:                                                    |
|  - rf_supp: filter_type = bloom, build_nodes = [1, 2, 3]                  |
|                                                                            |
|  0:VOlapScanNode                                                           |
|     TABLE: lineorder                                                       |
|     PREDICATES: lo_orderdate >= 19970101                                   |
|     runtime filters: rf_supp -> lo_suppkey (pushdown to storage scan)     |

Single-Table Raw Scan Throughput (ClickBench)

On single-table aggregations and group-by scans, ClickHouse retains an edge. On standardized ClickBench queries (evaluating filters and aggregations across fields such as AdvEngineID, SearchPhrase, and UserID), ClickHouse consistently records lower query latency and higher scan throughput on flat tables.

ClickHouse accomplishes this through its tight vectorized primitives:

• Zero-Copy Column Processing: ClickHouse loads compressed blocks directly into L2/L3 CPU caches, applying vectorized branchless comparisons directly on SIMD registers with minimal dispatch overhead.
• Granular Primary Index Skipping: The sparse index (index_granularity, typically 8,192 rows) enables the engine to skip large swaths of data marks without decompressing or reading unneeded column files, resulting in fewer total disk read bytes for narrow range scans.

---

Mutation Architecture: StarRocks Primary Key Upserts vs. ClickHouse ReplacingMergeTree

Handling mutable data is a common architectural failure point for real-time analytical systems. Production event streams routinely contain late-arriving event corrections, order status updates, inventory reconciliations, and record deletions required by data privacy regulations.

StarRocks Delete-and-Insert with Persistent Index

Evaluating starrocks primary key upsert performance reveals the benefits of separating immutable columnar storage from primary key index state:

• Storage Structure: StarRocks Primary Key tables separate immutable columnar data files from the primary key index state. When incoming batches arrive via Stream Load or Kafka Routine Load, StarRocks writes the data into new columnar segments while updating a disk-backed Persistent Index on local NVMe storage. When the persistent index is enabled (enable_persistent_index = true), on-disk storage space is calculated according to the documented formula (key_size + 8 bytes) * row_count * compression_ratio (where 8 bytes accounts for internal row locator overhead), while memory consumption is strictly bounded by min(l0_max_mem_usage * tablet_count, update_memory_limit_percent * BE_process_memory).
• Delete-and-Insert Semantics: If an incoming primary key matches an existing record, the persistent index identifies the exact historical file segment and row offset, recording a delete vector entry. During query scans, the engine applies the delete vector as a bitmap filter at the storage scan layer, reading only the latest row version without requiring query-time deduplication.
• Partial Column Updates: StarRocks supports partial column updates directly without reading and rewriting the entire row, substantially reducing write amplification for CDC streams.

ClickHouse ReplacingMergeTree and the SELECT FINAL Tax

ClickHouse was fundamentally designed as an append-only analytical engine. To support updates and record deduplication, it provides the ReplacingMergeTree table engine.

• Asynchronous Compaction: In a ReplacingMergeTree, duplicate rows sharing the same sorting key coexist across multiple physical parts on disk. Deduplication occurs asynchronously in the background during non-deterministic part merges.
• The Read-Time Deduplication Tax: To guarantee point-in-time state without duplicate records, analytical queries must append the FINAL modifier: SELECT * FROM table FINAL.
• Latency Amplification Under Active Ingestion: Analyzing replacingmergetree final performance highlights why unmerged data parts degrade query latency. Under continuous write pressure from streaming CDC feeds, unmerged parts accumulate rapidly. Appending FINAL forces ClickHouse to perform an on-the-fly multi-way merge across all overlapping parts at query time. This dynamic merge introduces substantial query latency degradation, sharp olap database latency p99 spikes, and elevated CPU and memory consumption. While data engineers sometimes use workaround aggregation functions like argMax(column, version) to avoid FINAL, this workaround increases SQL query complexity, prevents primary index range pruning, and does not eliminate the computational overhead of scanning duplicate rows.

---

Production StarRocks DDL and Ingestion Pipeline Configuration

To establish an enterprise-grade StarRocks deployment capable of sustaining sub-second queries alongside continuous row-level CDC streams, apply the following validated production configurations.

Production StarRocks Primary Key Table DDL

-- Production DDL for StarRocks 3.3+ Primary Key Table
-- Optimized for high-throughput CDC upserts and sub-second star schema joins

CREATE DATABASE IF NOT EXISTS production_analytics;
USE production_analytics;

CREATE TABLE lineorder_pk (
    lo_orderkey         BIGINT NOT NULL,
    lo_linenumber       INT NOT NULL,
    lo_custkey          INT NOT NULL,
    lo_partkey          INT NOT NULL,
    lo_suppkey          INT NOT NULL,
    lo_orderdate        DATE NOT NULL,
    lo_orderpriority    VARCHAR(15) NOT NULL,
    lo_shippriority     VARCHAR(1) NOT NULL,
    lo_quantity         INT NOT NULL,
    lo_extendedprice    DECIMAL(12, 2) NOT NULL,
    lo_ordtotalprice    DECIMAL(12, 2) NOT NULL,
    lo_discount         DECIMAL(4, 2) NOT NULL,
    lo_revenue          DECIMAL(12, 2) NOT NULL,
    lo_supplycost       DECIMAL(12, 2) NOT NULL,
    lo_tax              DECIMAL(4, 2) NOT NULL,
    lo_commitdate       DATE NOT NULL,
    lo_shipmode         VARCHAR(10) NOT NULL,
    updated_at          DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
)
ENGINE = OLAP
PRIMARY KEY (lo_orderkey, lo_linenumber)
PARTITION BY RANGE (lo_orderdate) (
    START ("2026-01-01") END ("2027-01-01") EVERY (INTERVAL 1 MONTH)
)
DISTRIBUTED BY HASH (lo_orderkey) BUCKETS 32
PROPERTIES (
    "replication_num" = "3",
    "enable_persistent_index" = "true",
    "persistent_index_type" = "LOCAL",
    "storage_medium" = "SSD",
    "compression" = "ZSTD",
    "fast_schema_evolution" = "true"
);

High-Throughput Stream Load Ingestion Pipeline

StarRocks provides the HTTP Stream Load interface, allowing microservices, Flink jobs, and ingestion scripts to stream batches directly to Backend nodes via HTTP/1.1 PUT:

#!/usr/bin/env bash
# - Production StarRocks Stream Load Pipeline Execution
# - Optimized for streaming ingestion with batch compression
# - Throughput scales with network bandwidth, row width, batch sizing, and disk I/O

set -euo pipefail

FE_HOST="sr-fe-coordinator.internal"
HTTP_PORT="8030"
DATABASE="production_analytics"
TABLE="lineorder_pk"
DATA_FILE="/opt/telemetry/cdc_lineorder_batch.json.gz"

curl --location-trusted \
  --request PUT "http://${FE_HOST}:${HTTP_PORT}/api/${DATABASE}/${TABLE}/_stream_load" \
  -H "Expect: 100-continue" \
  -H "Authorization: Basic $(echo -n 'admin:ProductionSecurePassword2026!' | base64)" \
  -H "format: json" \
  -H "strip_outer_array: true" \
  -H "compression: gzip" \
  -H "max_filter_ratio: 0.001" \
  -H "strict_mode: true" \
  -H "partial_update: false" \
  -H "columns: lo_orderkey, lo_linenumber, lo_custkey, lo_partkey, lo_suppkey, lo_orderdate, lo_orderpriority, lo_shippriority, lo_quantity, lo_extendedprice, lo_ordtotalprice, lo_discount, lo_revenue, lo_supplycost, lo_tax, lo_commitdate, lo_shipmode, updated_at" \
  -T "${DATA_FILE}"

Production Apache Kafka Routine Load Job

For real-time CDC ingestion from Apache Kafka without managing intermediate connectors:

CREATE ROUTINE LOAD production_analytics.kafka_lineorder_cdc ON lineorder_pk
COLUMNS(lo_orderkey, lo_linenumber, lo_custkey, lo_partkey, lo_suppkey, lo_orderdate, 
        lo_orderpriority, lo_shippriority, lo_quantity, lo_extendedprice, lo_ordtotalprice, 
        lo_discount, lo_revenue, lo_supplycost, lo_tax, lo_commitdate, lo_shipmode, updated_at)
PROPERTIES (
    "desired_concurrent_number" = "3",
    "max_batch_interval" = "5",
    "max_batch_rows" = "300000",
    "max_batch_size" = "104857600",
    "format" = "json",
    "strip_outer_array" = "true"
)
FROM KAFKA (
    "kafka_broker_list" = "kafka-broker-1:9092,kafka-broker-2:9092,kafka-broker-3:9092",
    "kafka_topic" = "analytics.cdc.lineorder",
    "property.group.id" = "starrocks_cdc_consumer_group",
    "property.client.id" = "starrocks_fe_consumer",
    "property.kafka_default_offsets" = "OFFSET_BEGINNING"
);

Runnable Docker Compose Benchmarking Environment

This reproducible Docker Compose environment launches a single-node StarRocks cluster (FE + BE) and a ClickHouse Server configured with production-grade memory allocations and network settings:

version: '3.8'

services:
  starrocks-fe:
    image: starrocks/fe-ubuntu:3.3.4
    container_name: starrocks-fe
    hostname: starrocks-fe
    environment:
      - HOST_TYPE=IP
    ports:
      - "8030:8030" # HTTP UI & Stream Load
      - "9030:9030" # MySQL Protocol Port
    volumes:
      - fe_meta:/opt/starrocks/fe/meta
    networks:
      olap-benchmark-net:
        ipv4_address: 172.28.0.10
    ulimits:
      nofile:
        soft: 65536
        hard: 65536

  starrocks-be:
    image: starrocks/be-ubuntu:3.3.4
    container_name: starrocks-be
    hostname: starrocks-be
    depends_on:
      - starrocks-fe
    ports:
      - "8040:8040" # BE HTTP Server
      - "9050:9050" # Heartbeat Port
    environment:
      - HOST_TYPE=IP
      - FE_HOST=172.28.0.10
      - FE_PORT=9030
    volumes:
      - be_storage:/opt/starrocks/be/storage
    networks:
      olap-benchmark-net:
        ipv4_address: 172.28.0.11
    ulimits:
      nofile:
        soft: 65536
        hard: 65536
      memlock:
        soft: -1
        hard: -1

  clickhouse:
    image: clickhouse/clickhouse-server:24.8-alpine
    container_name: clickhouse-server
    hostname: clickhouse
    ports:
      - "8123:8123" # HTTP Interface
      - "9000:9000" # Native Client Port
    volumes:
      - ch_data:/var/lib/clickhouse
    networks:
      olap-benchmark-net:
        ipv4_address: 172.28.0.20
    environment:
      - CLICKHOUSE_DB=default
      - CLICKHOUSE_USER=default
      - CLICKHOUSE_PASSWORD=ClickHouseSecure2026!
    ulimits:
      nofile:
        soft: 262144
        hard: 262144

volumes:
  fe_meta:
  be_storage:
  ch_data:

networks:
  olap-benchmark-net:
    driver: bridge
    ipam:
      config:
        - subnet: 172.28.0.0/16

---

Strategic Selection: Analytical SaaS vs. Append-Only Event Telemetry

Selecting between StarRocks and ClickHouse should be determined by workload access patterns, join density, and data mutability requirements rather than raw vendor synthetic benchmarks.

flowchart TD
    START["Evaluate Ingestion & Query Pattern"] --> Q1{"Are incoming records mutable?
(Updates / Deletions / CDC)"}
    
    Q1 -->|Yes: Frequent Upserts| M1{"Query Access Pattern"}
    M1 -->|Multi-Tenant Point Lookups
+ Star Schema Joins| DEC_SR["Deploy StarRocks
(Primary Key Table + CBO)"]
    M1 -->|Append Batches with
Periodic Offline Deduplication| DEC_CH1["Deploy ClickHouse
(ReplacingMergeTree without FINAL)"]
    
    Q1 -->|No: Append-Only Immutable| Q2{"Schema Topology"}
    
    Q2 -->|Pre-flattened Wide Table / OBT
Logs, Metrics, Clickstream| DEC_CH2["Deploy ClickHouse
(MergeTree Engine)"]
    Q2 -->|Normalized Star / Snowflake Schema
Ad-hoc Multi-Table Joins| DEC_SR2["Deploy StarRocks
(Duplicate Key + Dynamic Runtime Filters)"]
    
    DEC_SR --> PROD_SR["Production Benefits:
- Sub-second 5-table joins
- Real-time upserts via persistent index
- Stateless CN horizontal autoscaling"]
    DEC_CH2 --> PROD_CH["Production Benefits:
- Maximum raw scan throughput
- Lowest storage footprint (compression)
- Predictable single-table resource usage"]

When to Select StarRocks

StarRocks is the superior operational choice for:

1. Multi-Tenant Customer-Facing Analytics: SaaS applications where diverse business clients execute ad-hoc slice-and-dice queries across normalized operational schemas.

2. Real-Time CDC Ingestion: Architectures ingesting streaming changelogs from transactional databases (PostgreSQL, MySQL, CockroachDB). The Primary Key persistent index processes high-frequency row upserts without degrading analytical query latencies.

3. Decoupled Cloud-Native Topologies: Kubernetes infrastructures requiring independent compute autoscaling. StarRocks Compute Nodes (CN) scale from zero to hundreds of nodes against object storage without re-sharding physical disks.

Organizations designing complex enterprise platforms or evaluating custom software engineering options find that StarRocks substantially reduces data pipeline overhead by eliminating intermediate dbt and Spark denormalization tasks.

When to Select ClickHouse

ClickHouse remains the industry leader for:

1. Append-Only Machine Telemetry & Observability: Systems handling billions of immutable log lines, network packet captures, and IoT time-series records per day.

2. Pre-Flattened Clickstream Analytics: Digital marketing and web analytics workloads where incoming records are naturally flattened into wide tables prior to ingestion.

3. Storage-Constrained Deployments: Edge environments or bare-metal clusters where ClickHouse's industry-leading compression codecs (DoubleDelta, Gorilla, T64, ZSTD) minimize physical disk footprint.

---

Frequently Asked Questions

Does StarRocks eliminate the need for dbt or Spark denormalization pipelines?

Yes, for the majority of interactive analytical dashboards. In conventional OLAP architectures, data engineering teams frequently run continuous Spark or dbt jobs to transform normalized relational schemas into wide denormalized tables (OBT) solely to avoid query-time join penalties. StarRocks' Cascades Cost-Based Optimizer and Dynamic Runtime Filters execute distributed star schema joins across hundreds of millions of rows with sub-second responsiveness, allowing analytics to query normalized dimensional schemas directly. Pre-aggregation and batch transformations are thus reserved for complex machine learning feature pipelines rather than presentation-layer dashboards.

How much RAM does StarRocks require for Primary Key persistent indexes compared to ClickHouse?

When persistent index is enabled (enable_persistent_index = true), StarRocks offloads the primary key index from memory to local NVMe SSDs. On-disk storage overhead follows the documented formula (key_size + 8 bytes) * row_count * compression_ratio, where 8 bytes represents the row locator. Memory usage is strictly bounded by configuration parameters (l0_max_mem_usage per tablet and update_memory_limit_percent of the Backend process), caching only hot L0 index blocks. ClickHouse ReplacingMergeTree uses sparse primary index marks (index_granularity, typically 8,192 rows) which require minimal memory for index marks. However, ClickHouse does not maintain a point-in-time deduplication index; executing queries with FINAL forces multi-part merges in memory during query execution, which can consume significant transient execution RAM under concurrent query loads on active tables.

Can ClickHouse match StarRocks join performance if data is co-located across shards?

When dimension tables are small enough to be loaded into ClickHouse memory dictionaries (DICTIONARY objects held in RAM) or when tables share identical co-located sharding keys, ClickHouse join execution is efficient. However, when joining large distributed tables where dimension tables exceed single-node memory limits, ClickHouse must execute distributed hash joins by broadcasting tables or transferring intermediate data across nodes without cross-stage dynamic runtime filtering or cost-based join reordering. Under these distributed star schema conditions, StarRocks achieves substantially lower query latency and lower peak memory consumption through its pipeline-based hash joins, bucket shuffle optimizations, and dynamic pushdown filtering.

What are the operational differences between StarRocks Shared-Data mode and ClickHouse Cloud SharedMergeTree?

StarRocks Shared-Data mode is fully integrated into the open-source project (StarRocks 3.x+). It utilizes stateless Compute Nodes (CN) that query data directly from shared object storage (such as Amazon S3, Google Cloud Storage, or MinIO) while caching active data blocks on local ephemeral NVMe SSDs. In ClickHouse, the SharedMergeTree engine—which decouples compute and shared cloud object storage—is a proprietary architecture available in ClickHouse Cloud. In self-managed open-source deployments, ClickHouse typically relies on ReplicatedMergeTree with ClickHouse Keeper or Apache ZooKeeper, requiring dedicated attached storage per shard and manual data rebalancing when scaling cluster nodes.

---

Sources

• StarRocks Cost-Based Optimizer (CBO) Architectural Overview - Official documentation on Cascades query planning, join enumeration, and dynamic runtime filter mechanics.
• StarRocks Primary Key Storage Engine Internal Specification - Architectural guide covering delete-and-insert write paths and persistent index disk layouts.
• ClickHouse MergeTree Engine Family Architecture - Technical specification of physical data parts, sparse primary indexes, and compaction strategies.
• ClickBench: Standard Benchmark for Analytical Databases - Standardized methodology and reproducible SQL test suite for wide-table web analytics queries.
• Star Schema Benchmark (SSB) Specification (Rev 3) - Foundational technical specification defining multi-table relational star schema performance evaluation.