Understanding ClickHouse Performance: Storage Engine and Compute Engine Perspectives

The post introduces the book “ClickHouse Performance at Its Peak: Decoding the Architecture Design” by Chen Feng, a senior big‑data architect, and announces a limited‑time 50% discount and a giveaway for community members.

Storage Engine Perspective

ClickHouse achieves speed by reducing disk I/O through three main mechanisms:

Pre‑sorting: data is sorted before being written to disk, enabling range queries to be served by sequential reads.

Columnar storage: each column’s data resides in a single file, providing contiguous layout ideal for OLAP workloads.

Compression: data is compressed at the block level (typically 8192 rows), lowering I/O volume while keeping CPU overhead acceptable.

Compute Engine Perspective

The compute engine’s performance relies on:

Extensive use of vectorized operations and built‑in functions, which the engine automatically optimizes.

Avoiding or minimizing JOIN operations because ClickHouse lacks a cost‑based optimizer and only supports broadcast joins, which can cause memory pressure.

Example of proper versus improper SQL usage:

SELECT (2/(1.0 + exp(-2 * x)) -1) as tanh_x  // Incorrect

SELECT tanh(x) as tanh_x  // Correct, using ClickHouse built‑in function

When the above four conditions (MergeTree engine, proper sorting key, minimal joins, heavy use of built‑in functions) are met, ClickHouse can deliver excellent performance for analytical workloads.

The article concludes with a summary of the storage‑engine and compute‑engine prerequisites and a reminder that ClickHouse is best suited for OLAP scenarios rather than transactional or ODS modeling.