Why ClickHouse Outperforms MySQL, Elasticsearch, and HBase for Massive Event Data

Background

The activity platform must record every user action to support customer service, operations, product, and R&D teams. This requires storage and analysis of tens to hundreds of billions of rows, real‑time ingestion via MQ or API, and occasional real‑time analytics, demanding high write throughput and acceptable query latency.

Store massive data volumes

High write performance

Support real‑time analytics

Reasonable query latency

Technology Selection

MySQL Single Table

MySQL is limited by a single server’s disk size. Practical limits are a few million to ~5×10⁸ rows; industry consensus suggests ~1 GB is optimal. It cannot handle tens of billions of rows.

MySQL Sharding

Sharding increases capacity and write throughput but adds resource consumption, complex OLAP queries, costly cross‑node aggregation, and index size that can exceed data size, leading to degraded query performance at very large scales.

Elasticsearch

Provides distributed search and real‑time analytics but suffers from write‑throughput bottlenecks due to tokenization, higher latency under heavy load, slower queries as data grows, and higher storage costs because of low compression ratios.

HBase

Offers strong write performance on HDFS column‑family storage, yet is not a real‑time analytics framework, handles semi‑structured data inefficiently, imposes RowKey design constraints, and has slower read paths for large scans.

ClickHouse

A distributed column‑oriented DBMS designed for OLAP, real‑time analytics, and data‑warehouse workloads. It supports SQL, high‑speed ingestion, columnar compression, and horizontal scaling.

ClickHouse Detailed Introduction

Origin

Developed by Yandex (Russia) and open‑sourced in 2016, ClickHouse is written in C++ and targets online analytical processing (OLAP) with SQL‑based reporting.

Architecture

ClickHouse uses a Multi‑Master architecture where every node is equal; clients can query any node and receive the same result. A Single‑Master mode exists for some query phases but does not scale well under heavy concurrency. Multi‑Master combined with compute (Segment) nodes provides high connection counts and read/write throughput.

Each node hosts a replicated table (ReplicatedMergeTree) that serves as a data shard.

Key Features

Columnar Storage

Values of the same column are stored together, enabling efficient scans of only required columns, better compression, and faster aggregation.

Complete DBMS Functions

DDL : create/modify/drop databases, tables, views without restart.

DML : dynamic insert, update, delete, and query.

Permission control : fine‑grained user privileges.

Backup & recovery : export/import mechanisms.

Distributed management : automatic cluster handling.

Data Compression

Default LZ4 compression reduces 20.3 trillion rows (≈17 PB raw) to ~2 PB (≈8:1 ratio). Compression works on blocks of 8192 rows, minimizing CPU overhead.

压缩前：ABCDE_BCD  压缩后：ABCDE_(5,3)

Vectorized Execution Engine

SIMD‑based vectorization eliminates loops, allowing operations on many rows per CPU instruction.

for (size_t i = 0; i < 100; ++i) c[i] = a[i] + b[i];

Data Sharding & Distributed Queries

Data is horizontally sharded across nodes; a Distributed table acts as a proxy to local shards, enabling parallel query execution and seamless scaling.

Table Engine – MergeTree

MergeTree (and its families) support primary‑key sorting, partitioning, replication, and sampling.

CREATE TABLE IF NOT EXISTS db.table (
    col1 Type,
    col2 Type,
    ...
) ENGINE = MergeTree()
PARTITION BY expr
ORDER BY expr
PRIMARY KEY expr
SAMPLE BY expr
SETTINGS index_granularity = 8192;

PARTITION BY : optional, defines partition key.

ORDER BY : required, defines sorting key (also primary key by default).

PRIMARY KEY : optional, creates a sparse index every index_granularity rows (default 8192).

SAMPLE BY : optional, for sampling queries.

SETTINGS : e.g., index_granularity_bytes for adaptive granularity.

Index Types

Dense index : one entry per row.

Sparse index : one entry per index_granularity rows (default).

Secondary (skip) index : optional, supports minmax, set, ngrambf_v1, tokenbf_v1.

Query Process

ClickHouse narrows the data range using partition, primary, and secondary indexes, then reads only the necessary blocks (marks).

Write Process

Data is first written to a new partition directory, then merged asynchronously. Indexes, marks, and compressed column files are generated per index_granularity block.

ClickHouse Use Cases

Read‑heavy workloads (many queries per write)

Batch updates of >1000 rows

Append‑only data

Wide tables with many columns

Low‑frequency queries (hundreds per second per server)

Latency tolerance ~50 ms for simple queries

Small numeric or short‑string column values

High per‑query throughput (billions of rows/second per server)

No strict transaction requirements

Typical scenarios include traffic analysis, precise marketing, real‑time bidding, BI reporting, user‑behavior analytics, log analysis, real‑time dashboards, time‑series storage, and data visualization.

Summary

Drawbacks

No full ACID transaction support

Lacks full‑text tokenized search

Batch‑only updates/deletes; no low‑latency row‑level modifications

Join performance is weak

Officially recommended QPS ≈ 100; high‑concurrency workloads may suffer because each query uses multiple CPU cores

Why Queries Are Fast

Columnar storage reduces I/O by reading only needed columns

Data is pre‑sorted, turning random reads into sequential reads

Effective compression lowers data volume

Vectorized engine leverages SIMD and built‑in functions

Avoid joins; use external processing (e.g., Spark) if necessary

In‑memory GROUP BY with hash tables

Sparse and skip indexes limit scanned data

Why Writes Are Fast

Columnar layout and compression reduce write amplification

Distributed architecture spreads load across nodes

Multi‑threaded ingestion processes many writes in parallel

LSM‑tree‑like merge‑tree design batches sequential appends and performs background compaction