OLAP Engine Selection for Billion‑Row Analytics: Doris, ClickHouse, StarRocks

1. Core Insight: OLAP selection is not about raw speed

Many teams fall into two traps when building real‑time analytics: focusing only on single‑SQL benchmark results and ignoring whether features scale under continuous writes and high concurrency. The decisive factors are data characteristics (append‑only vs frequent updates), query patterns (single‑table aggregation vs multi‑table joins), concurrency requirements, and operational capabilities.

2. Business Background and Why Traditional Stacks Fail

A typical e‑commerce scenario generates 300 billion events per day, retains hot data for 30 days, and requires minute‑level dashboard updates and second‑level analyst drill‑downs. The legacy pipeline (App → Kafka → MySQL → Canal/Debezium → Flink → Elasticsearch → Hive → offline reports) works at small scale but collapses when data volume and query complexity grow.

2.1 Problems with Existing Components

Elasticsearch excels at log search but cannot handle high‑cardinality OLAP queries, leading to memory spikes and storage bloat.

Hive/Spark are batch‑oriented; even with Presto/Trino, they suffer from metadata and scheduling overhead for interactive queries.

Mixed workloads (continuous writes, dashboards, ad‑hoc analyst queries, external query APIs) overload any single OLAP engine without proper isolation and governance.

3. Why Some Engines Perform Faster

Modern OLAP performance stems from columnar storage, vectorized execution, and effective data pruning via partitioning, sorting keys, and bucketing. Vectorized execution processes data in batches, reducing CPU branch overhead and improving cache utilization.

3.2 Data Pruning Factors

Partitioning determines time‑range pruning.

Sorting keys enable range scans.

Bucket distribution controls parallelism and data skew.

Misusing high‑cardinality columns as bucket keys can cause small files, compaction pressure, complex query plans, and longer rebalancing.

3.3 MPP Fundamentals

Data is distributed across nodes.

SQL is split into fragments for parallel execution.

Filters, aggregations, and pre‑computations are pushed down to data nodes.

The trade‑off is that distributed shuffle, data skew, and join strategy can become bottlenecks.

4. Engine‑Level Trade‑offs

4.1 ClickHouse – Strong for High‑Throughput Append‑Only Single‑Table Analytics

MergeTree storage, sequential writes, excellent compression.

Best for log, behavior, APM, and monitoring use‑cases.

Weaknesses: limited upserts, complex multi‑table joins, high‑concurrency service queries, and less smooth scaling.

4.2 Apache Doris – Balanced Real‑Time Data‑Warehouse

Clear FE/BE architecture, table model close to data‑warehouse design.

Supports detail, aggregation, deduplication, materialized views, and external tables.

Low learning curve and stable operations.

4.3 StarRocks – Enhanced for Mixed Loads

Improved execution engine, optimizer, primary‑key model, materialized views, compute‑storage separation, and lake integration.

Excels at complex joins, real‑time updates, and high‑concurrency service queries.

5. Workload Profiles and Engine Fit

Profile A – Log Analysis : single‑table, massive appends, minimal updates, moderate concurrency → ClickHouse.

Profile B – Real‑Time Data‑Warehouse : continuous Kafka/Flink ingestion, many dimension joins, both detail and aggregation → Doris or StarRocks.

Profile C – Mixed Service : dashboards + external query APIs, frequent updates, high concurrency → StarRocks.

6. Final Architecture: Flink + StarRocks

Based on the analysis, the authors chose a stack of Kafka → Flink (real‑time ETL, deduplication, dimension enrichment) → StarRocks (core OLAP store) with Redis as a low‑latency cache for hot results.

CREATE TABLE dwd_user_event_detail (
    event_id BIGINT NOT NULL COMMENT '事件唯一ID',
    user_id BIGINT NOT NULL COMMENT '用户ID',
    device_id VARCHAR(64) NOT NULL COMMENT '设备ID',
    ...
    PRIMARY KEY(event_id),
    PARTITION BY RANGE(stat_date) (),
    DISTRIBUTED BY HASH(user_id) BUCKETS 64,
    PROPERTIES (
        "replication_num" = "3",
        "enable_persistent_index" = "true",
        "compression" = "LZ4"
    )
) ENGINE=OLAP;

The primary key guarantees idempotent upserts, while partitioning on stat_date enables efficient time‑range pruning. Bucketing by user_id improves join locality.

Design Highlights

Batch flush balances write throughput and ingestion latency.

Using event_id as the primary key ensures duplicate consumption is safely overwritten.

At‑least‑once semantics is acceptable for most analytics workloads, but strong consistency requires additional checkpoint and replay handling.

7. Modeling Best Practices

Separate layers: ODS (raw events, replayable), DWD (wide fact table with deduplication), DWS/ADS (aggregated, service‑oriented tables). This separation aids governance, debugging, back‑fill, and keeps complex logic out of SQL.

CREATE TABLE ads_trade_gmv_1min (
    stat_minute DATETIME NOT NULL,
    shop_id BIGINT NOT NULL,
    category_id BIGINT NOT NULL,
    pay_order_cnt BIGINT SUM DEFAULT '0',
    pay_user_cnt BIGINT SUM DEFAULT '0',
    pay_gmv DECIMAL(18,2) SUM DEFAULT '0.00'
) ENGINE=OLAP
AGGREGATE KEY(stat_minute, shop_id, category_id)
PARTITION BY RANGE(date(stat_minute)) ()
DISTRIBUTED BY HASH(shop_id) BUCKETS 32;

Materialized views pre‑compute frequent minute‑level metrics, dramatically reducing scan volume for dashboards.

CREATE MATERIALIZED VIEW mv_trade_item_1min
REFRESH ASYNC AS
SELECT date_trunc('minute', event_time) AS stat_minute,
       item_id,
       shop_id,
       COUNT_IF(event_type = 'pay') AS pay_order_cnt,
       COUNT(DISTINCT IF(event_type = 'pay', user_id, NULL)) AS pay_user_cnt,
       SUM(IF(event_type = 'pay', pay_amount, 0)) AS pay_gmv
FROM dwd_user_event_detail
GROUP BY stat_minute, item_id, shop_id;

8. Query Governance and API Design

Expose templated, rate‑limited APIs instead of raw SQL. Example: a minute‑level GMV trend endpoint that validates time range (≤ 7 days), requires shop or category filter, and automatically hits the materialized view.

POST /api/olap/trade/gmv-trend
{
  "startTime": "2026-08-01 00:00:00",
  "endTime": "2026-08-01 23:59:59",
  "shopId": 1002001,
  "categoryId": null,
  "granularity": "MINUTE"
}

The Java service validates parameters, builds a safe SQL template, caches results, and logs execution metrics.

public List<TradeTrendPoint> queryTrend(TradeTrendQueryRequest req) {
    validate(req);
    String sql = """
        SELECT stat_minute,
               SUM(pay_order_cnt) AS pay_order_cnt,
               SUM(pay_user_cnt) AS pay_user_cnt,
               SUM(pay_gmv) AS pay_gmv
        FROM ads_trade_gmv_1min
        WHERE stat_minute >= :startTime
          AND stat_minute <= :endTime
          AND (:shopId IS NULL OR shop_id = :shopId)
          AND (:categoryId IS NULL OR category_id = :categoryId)
        GROUP BY stat_minute
        ORDER BY stat_minute
        LIMIT 10080
    """;
    // execute with NamedParameterJdbcTemplate ...
}

9. High Concurrency and Elastic Scaling

Separate workloads into resource groups: real‑time dashboards, BI/adhoc, and external API queries.

Use colocate groups to minimize shuffle for frequent dimension joins.

Apply back‑pressure, rate limiting, and caching to protect the cluster during traffic spikes.

10. Observability and Operations

Key metric groups:

Ingestion: Kafka lag, Flink checkpoint latency/failure, batch size, flush delay, visibility latency.

Storage: tablet count, compaction backlog/score, data skew, primary‑key index memory.

Query: QPS, P95/P99 latency, failure rate, scan rows/bytes, resource‑group queue length.

Node resources: CPU, memory, disk usage, I/O wait, network traffic.

Alert on ingestion delay beyond SLA, prolonged compaction backlog, resource‑group queue buildup, and P99 latency spikes.

11. Common Pitfalls and Mitigations

Assuming “second‑level” means every query is sub‑second – define SLA per query type.

Building a single super‑wide table to avoid joins – leads to high storage cost and maintenance overhead.

Skipping ODS and writing directly from Kafka – makes back‑fill and correction painful.

Using OLAP as a transactional store – unsuitable for strong consistency workloads.

Only benchmarking performance without failure‑injection – real‑world issues appear under node failures, data skew, or version upgrades.

12. Final Recommendation

Choose the engine that matches your workload profile:

ClickHouse : append‑only log analysis, minimal updates, single‑table scans.

Apache Doris : balanced real‑time data‑warehouse with stable operations.

StarRocks : mixed load with frequent updates, complex joins, and high‑concurrency service queries.

The true determinant of long‑term success is not the database name but a complete system that covers modeling, ingestion pipelines, governance, monitoring, and fault‑tolerance.