Choosing the Right OLAP Engine: Druid vs ClickHouse and Optimization Tips

1. OLAP Technology Overview

OLAP (On‑Line Analytical Processing) enables multidimensional analysis for enterprise decision‑making, contrasting with OLTP (On‑Line Transaction Processing) which handles real‑time transactional workloads.

1.1 Basic OLAP Operations

Key multidimensional operations are Drill‑down, Roll‑up, Slice, Dice and Pivot.

1.2 OLAP Storage Models

MOLAP : Stores pre‑computed cubes; provides fast query performance but incurs high storage cost and update latency.

ROLAP : Uses relational tables for on‑demand queries; offers real‑time data but slower aggregation.

HOLAP : Hybrid model that keeps detailed data in ROLAP and aggregates in MOLAP, balancing flexibility and performance.

1.3 Representative OLAP Engines

Druid : Pre‑computes aggregates for high‑throughput time‑series data; low query latency, multi‑tenant, suited for user‑behavior and network‑traffic analysis.

Kylin : MOLAP system that builds cubes on petabyte‑scale datasets; excels at complex SQL joins when data changes infrequently.

Doris : MPP query engine with a simple FE/BE architecture; supports real‑time analytics on PB‑scale data, high‑concurrency joins, and online schema changes.

ClickHouse : Columnar storage optimized for fast analytical queries; provides vectorized execution, skip‑indexing and distributed aggregation, but is less suited for high‑concurrency multi‑table joins.

2. Application Scenarios and Architecture

Typical scenarios include self‑service analytics for daily transactions, real‑time dashboards during large promotional events, and high‑frequency query workloads requiring sub‑second latency.

Key requirements gathered from multiple business teams are:

Billions of rows ingested per day.

Millisecond‑level query latency.

Real‑time data freshness.

High concurrency and platform stability.

The resulting architecture combines Druid for high‑throughput time‑series analysis and ClickHouse for detailed reporting, forming a hybrid OLAP solution that satisfies the above requirements.

3. Optimization Practices

3.1 Druid Optimizations

Materialized Views

Identify frequently co‑occurring dimension groups and pre‑aggregate them into dedicated indexes. When a query’s dimension set matches a materialized view, Druid can read the smaller pre‑aggregated index instead of the full raw index, dramatically reducing I/O and query latency, especially for high‑cardinality dimensions.

Query Caching with Redis

Cache query results at the finest time granularity (day / hour). If a new query spans both cached and uncached intervals, fetch only the missing intervals from Druid, merge them with the cached data, and return the combined result. This approach improves cache hit rates for overlapping time windows.

Hot/Cold Data Tiering

Configure node tiers so that hot data is distributed across multiple broker nodes, while cold data resides on lower‑priority nodes. Adjust tier and priority settings to balance load and reduce hotspot contention.

# Example Druid tiering configuration
# Assign hot tier (default is _default_tier)
druid.server.tier=hot
# Give hot nodes higher query priority (default 0)
druid.server.priority=100
# Processing buffer size (default 1 GB)
processing.buff=4G
# Buffer size in bytes for processing
druid.processing.buffer.sizeBytes=1073741824
# Number of processing threads
druid.processing.numThreads=30

3.2 ClickHouse Optimizations

Distributed Aggregation

Enable distributed query processing so that each shard performs local aggregation before sending intermediate results to the coordinator. Early filtering on each node reduces network traffic and improves overall query performance.

Skip Indexes

ClickHouse provides column‑level skip indexes to prune data blocks during query execution. Common types are:

minmax : Stores the minimum and maximum value of each data block; ideal for columns that are roughly sorted.

set : Stores a set of distinct values per block (parameter max_size); useful for low‑cardinality columns within a block.

Bloom filter : Probabilistic structure that efficiently tests membership for high‑cardinality columns such as order_id or product_id. False positives are acceptable because they only cause extra block reads.

Avoid Using FINAL

Instead of the costly FINAL keyword, use the ReplacingMergeTree engine to deduplicate rows based on a version column. Queries can then retrieve the latest record with aggregation functions (e.g., argMax) without triggering a full merge.

CREATE TABLE t_replacing_table (
    id UInt8,
    name String,
    age UInt8
) ENGINE = ReplacingMergeTree(age)
ORDER BY id;

INSERT INTO t_replacing_table VALUES
    (1,'张三',18),(2,'李四',19),(3,'王五',20),
    (1,'张三',20),(2,'李四',15);

SELECT id,
       argMax(name, age) AS namex,
       max(age) AS agex
FROM t_replacing_table
GROUP BY id;

4. Summary

The hybrid OLAP architecture combines Druid for high‑throughput time‑series analysis and ClickHouse for detailed reporting, meeting requirements for massive data scale, low latency, real‑time freshness, and high concurrency. Current limitations include sub‑optimal support for ultra‑high‑concurrency self‑service analysis and complex multi‑table joins, indicating future work toward a more versatile OLAP platform.