Why ClickHouse Is So Fast: Deep Dive into Storage and Compute Engine Optimizations

Storage Engine Perspective

ClickHouse reduces query latency primarily by minimizing disk I/O. Over 90% of the time spent on a typical query is I/O, so the storage engine design is critical.

Pre‑sorting

Data is sorted before being written to disk according to a user‑defined sorting key. Because rows are stored in order, range scans become sequential reads, which are far faster than random reads. Point‑lookups retain the same performance as with unsorted data, so pre‑sorting improves range queries without hurting point queries.

Columnar storage

Each column is stored in its own file, making the values of a column contiguous on disk. This layout is ideal for OLAP workloads that read large blocks of a single column, because only the required columns are read and the I/O pattern is sequential.

Block‑level compression

ClickHouse compresses data at the block level (default block size = 8,192 rows). A compression operation works on an entire block, so the CPU cost of (de)compression is amortized over many rows. Columnar data is more regular, yielding high compression ratios, and the reduced amount of data transferred from disk outweighs the CPU overhead.

Compute Engine Perspective

The compute engine achieves high throughput through vectorized execution, but it lacks a cost‑based optimizer, especially for JOIN operations.

Prerequisites for high performance

Use ClickHouse’s built‑in functions, which are automatically vectorized.

Avoid or minimize JOINs; ClickHouse only implements a simple broadcast join and does not choose optimal join strategies.

SELECT (2/(1.0 + exp(-2 * x))-1) AS tanh_x ...  -- inefficient, prevents vectorization

SELECT tanh(x) AS tanh_x ...  -- efficient, uses built‑in vectorized function

Why the compute engine is fast

When the above conditions are satisfied, ClickHouse processes rows in batches using SIMD instructions, reducing per‑row overhead and fully exploiting CPU hardware acceleration. The engine is deliberately positioned as a single‑node OLAP engine; distributed join capabilities are expected to be handled by external systems such as Spark.

Performance Checklist

Use a MergeTree family storage engine.

Define an appropriate sorting key and write queries that respect the left‑most principle.

Prefer built‑in vectorized functions for calculations.

Limit or eliminate JOIN operations; push complex joins to external platforms if needed.