ClickHouse Real-Time Analytics: OLAP Engine Design, Table Engines, and Production Tuning

数据库

Summary

  • ClickHouse is the preferred real-time OLAP engine in 2026, used by ByteDance, Tencent, and Alibaba
  • MergeTree family table engines are the foundation — choosing the right engine determines 90% of performance
  • Materialized views enable real-time pre-aggregation but can cause data bloat if poorly designed
  • Production tuning core: proper partitioning, sort key design, avoid SELECT *, control concurrent queries
  • Complete solution from theory to SQL practice, including cluster deployment and monitoring

Table of Contents

Why ClickHouse in 2026

Scenario Daily Volume Latency Typical Query
User behavior analytics 1B+ rows Seconds Funnel, retention
Business monitoring 100M+ rows Seconds Real-time GMV, order count
Log analysis 10B+ rows Minutes Error rate, slow query top
Ad attribution 500M+ rows Minutes Conversion path, ROI
IoT time-series 5B+ rows Seconds Device status, anomaly detection

ClickHouse positioning: Columnar storage + vectorized execution + real-time writes = second-level OLAP.

Core Advantages

Advantage Description
Query speed Columnar + vectorized + SIMD — 100-1000x faster than MySQL
Compression Columnar + LZ4/ZSTD — 1/5 to 1/10 of raw data
Real-time writes Millions of rows per second
SQL support Standard SQL + rich aggregation functions
Horizontal scaling Sharding + replicas

ClickHouse Architecture Principles

Columnar vs Row Storage

For a 100-column user behavior table querying "daily UV for last 7 days":

  • Row storage (MySQL): Read one row → load 100 columns → use only 2 → waste 99% IO
  • Columnar (ClickHouse): Read only user_id + timestamp columns → 98% less IO

Write and Merge Process

INSERT → MemTable → Part file (immutable) → Background Merge → larger Part files.

MergeTree Table Engine Family

Need real-time dedup?
  ├─ Yes → ReplacingMergeTree / CollapsingMergeTree
  └─ No → Need pre-aggregation?
           ├─ Yes → AggregatingMergeTree / SummingMergeTree
           └─ No → MergeTree (default)

CREATE TABLE user_events (
    event_date Date,
    event_time DateTime,
    user_id UInt64,
    event_type LowCardinality(String),
    page_url String,
    device_type LowCardinality(String),
    properties String
) ENGINE = MergeTree()
PARTITION BY toYYYYMM(event_date)
ORDER BY (event_type, user_id, event_time)
TTL event_date + INTERVAL 90 DAY;

Table Design: Partition and Sort Keys

Partition Key Rules

Rule Description
Partition by time Most common, supports TTL cleanup
Max 50M rows per partition Too large = slow merge; too small = too many parts
Avoid high-cardinality fields user_id partitioning creates millions of partitions

Sort Key Rules

Rule Description
High-frequency filter fields first WHERE event_type = 'click' → event_type first
High cardinality in middle user_id
Time field last event_time (range queries)
Max 4-5 fields More hurts write performance

Materialized Views and Pre-Aggregation

CREATE MATERIALIZED VIEW daily_active_users
ENGINE = AggregatingMergeTree()
PARTITION BY toYYYYMM(event_date)
ORDER BY (event_date)
AS SELECT event_date, uniqState(user_id) AS uv
FROM user_events GROUP BY event_date;

Three Materialized View Traps

Trap Symptom Fix
No historical backfill Data before view creation missing Manual INSERT SELECT backfill
Chained views Hard to debug Max 2 levels
Write amplification Each view writes duplicate data Limit view count

Query Optimization in Practice

Slow query (5.2s):

SELECT * FROM user_events
WHERE toDate(event_time) = '2026-07-03' AND event_type = 'purchase'
GROUP BY user_id HAVING count() > 5;

Optimized (0.3s):

SELECT user_id, count() AS purchase_count
FROM user_events
WHERE event_date = '2026-07-03' AND event_type = 'purchase'
GROUP BY user_id HAVING purchase_count > 5;

Ten Optimization Rules

  1. No SELECT * — columnar advantage is reading only needed columns
  2. Filter conditions match sort key prefix
  3. Filter by partition field
  4. Avoid wrapping fields in functions (e.g., toDate() breaks index)
  5. Large table JOIN: small table on right
  6. Use PREWHERE instead of WHERE
  7. Control GROUP BY cardinality
  8. Use materialized views for pre-aggregation
  9. Set query timeout: max_execution_time = 60
  10. Use EXPLAIN to analyze execution plan

Cluster Deployment and High Availability

Distributed table routes writes to shards and aggregates query results across shards. Each shard needs at least 2 replicas coordinated by ZooKeeper/ClickHouse Keeper.

ClickHouse vs Elasticsearch vs Doris

Dimension ClickHouse Elasticsearch Apache Doris
Positioning Real-time OLAP Search + analytics Real-time warehouse
Write performance Extremely high Medium High
Aggregation Extremely high Medium High
Full-text search Weak Extremely strong Weak
Storage cost Low High (3-5x) Medium
JOIN support Limited None MPP JOIN

Selection: Pure analytics → ClickHouse; Full-text + logs → Elasticsearch; Warehouse + complex JOIN → Doris.

Interview Topics and Production Pitfalls

Q1: Why is ClickHouse fast?

Columnar storage (read only needed columns), vectorized execution (SIMD batch processing), data compression (less IO), plus MergeTree sparse index for data skipping.

Q2: Is ClickHouse suitable for OLTP?

No. Optimized for batch reads and aggregation. Poor single-row INSERT/UPDATE, no transactions. It is OLAP, not OLTP.

Q3: Does ReplacingMergeTree guarantee real-time dedup?

No. Dedup only happens during background merge. Query needs FINAL or GROUP BY ... argMax() for real-time dedup.

Pitfall Checklist

Pitfall Symptom Fix
No partitioning Full table scan Monthly/weekly partition + TTL
Poor sort key Filters cannot use index High-frequency filters first
SELECT * 10x+ slower Query only needed columns
Too many materialized views Write delay, storage bloat Keep under 5
No monitoring Merge backlog unnoticed Monitor part count, merge speed, query latency

Kafka to ClickHouse Ingestion

CREATE TABLE user_events_queue (...) ENGINE = Kafka()
SETTINGS kafka_topic_list = 'user_events', kafka_format = 'JSONEachRow';

CREATE MATERIALIZED VIEW user_events_mv TO user_events AS SELECT * FROM user_events_queue;

Control part count: batch inserts (50K-100K rows) beat per-row inserts by 10x.

Funnel and Retention SQL

E-commerce funnel: page_view → add_cart → checkout → purchase with countIf aggregation. 7-day retention via self-JOIN on install and active events. Always partition-prune with event_date.

Capacity Planning

Storage ≈ daily rows × avg bytes × compression (0.1-0.2) × replicas. Shard when data exceeds 10TB or daily inserts exceed 5 billion rows.

Troubleshooting Playbook

  • Slow queries: check system.merges, part count (>3000 alert), EXPLAIN indexes=1
  • Write latency: too many parts → tune parts_to_delay_insert
  • OOM: high-cardinality GROUP BY → materialized views or external aggregation

ClickHouse + S3 Cold Storage

TTL-based tiered storage: hot (NVMe, 30 days) → cold (S3 Parquet, 90+ days).

Hands-On: Docker Compose Setup

Deploy ClickHouse + Grafana in 10 minutes. Insert 1M test rows, verify aggregation completes under 1 second.

ClickHouse Cloud, Iceberg/Delta Lake integration, refreshable materialized views, AI-assisted query optimization, vector search fusion.

Summary and Further Reading

Key takeaways:

  1. Columnar + vectorized execution is why it's fast
  2. MergeTree is default; Replacing/Aggregating by scenario
  3. Partition by time, sort key by high-frequency filters
  4. Materialized views for pre-aggregation with backfill awareness
  5. Query optimization: no SELECT *, PREWHERE, match sort key prefix

Related reading:

References:

Try these browser-local tools — no sign-up required →

#ClickHouse#实时分析#OLAP引擎#列式存储#数据仓库#2026