Unlock ClickHouse’s Lightning‑Fast Queries: Architecture, Storage, and Index Secrets

Overall Architecture

ClickHouse is an open‑source distributed OLAP system developed by Yandex. It follows a massively parallel processing (MPP) model where each node is peer‑to‑peer, handling both storage and query processing without relying on external storage services. The storage layer manages data files, while the query layer executes user SQL, enabling extremely fast inserts and selects.

Columnar Storage ("Broken Sword")

Data is stored column‑wise, with each column written to a separate file. This allows queries to read only the needed columns, dramatically reducing I/O and improving compression ratios (often up to 8:1). During query execution, only the relevant column blocks are decompressed.

Vectorized Execution ("Broken Blade")

ClickHouse processes data in batches (e.g., 1024 rows) using SIMD instructions, which reduces CPU cache misses and boosts per‑core performance. This vectorized engine is a key factor behind ClickHouse’s ability to handle billions of rows on a single machine.

Pre‑Sorting ("Broken Gun")

Before persisting data, ClickHouse sorts it using a LSM‑like algorithm, guaranteeing ordered storage on disk. Primary and sorting keys must align, enabling range scans and reducing disk reads, which underpins many of its performance advantages.

Table Engines ("Broken Whip")

Different table engines provide specialized optimizations for various workloads. Engine choice determines data placement, write paths, read paths, indexing support, concurrency handling, and replication behavior.

Data Types

ClickHouse supports over 100 data types, grouped as follows:

Basic Types: Bool, UInt8/16/32/64, Int8/16/32/64, Float32/64, Decimal32/64/128, String, FixedString(N)

Date/Time Types: Date, DateTime, DateTime64(N)

Complex Types: Array(T), Nested, Tuple(...), Map(K,V), Enum

Aggregate Types: AggregateFunction, SimpleAggregateFunction

Other Types: UUID, IPv4/IPv6, Nullable(T), LowCardinality(T)

The choice of type affects memory layout, compression, and query speed.

Sharding and Replication ("Broken Palm")

Data is horizontally sharded across nodes and vertically replicated for fault tolerance. Sharding keys can be fixed fields, random functions, or hash functions. Replicas are selected via load‑balancing policies such as Random, Nearest hostname, Hostname Levenshtein distance, In Order, First or Random, and Round Robin.

Index Design ("Broken Arrow")

ClickHouse uses sparse primary‑key indexes (one entry per 8192 rows) stored in primary.idx and {column}.mrk files, allowing fast range scans while keeping the index memory‑resident. Skip indexes such as minmax, set, and Bloom filter further prune data blocks during query execution.

Computation Engine ("Broken Qi")

The engine translates SQL into physical plans and executes them using multithreading and distributed query processing. Although ClickHouse lacks a sophisticated optimizer and has limited JOIN support, its multithreaded, vectorized execution and distributed architecture deliver high throughput.

Conclusion

The combination of columnar storage, vectorized execution, aggressive compression, pre‑sorting, flexible table engines, rich data types, sophisticated sharding/replication, and targeted indexing makes ClickHouse a leading choice for large‑scale analytical workloads in the big‑data era.