Sign in to save your place.
Understand HOW ClickHouse achieves its insane speed. Columnar storage, compression, sparse indexes, and the query pipeline — all explained with analogies.
ClickHouse's architecture has 5 key layers that work together to make it blazing fast:
Imagine you have a wardrobe with 1000 outfits. Each outfit has: shirt, pants, shoes, hat, belt.
Row storage: Each outfit is in its own drawer. To count all red shirts, you open ALL 1000 drawers and check each shirt. Exhausting!
Column storage (ClickHouse): All shirts are in one section, all pants in another, all shoes in another. To count red shirts? Just go to the shirt section. You never touch pants, shoes, hats, or belts. 5x less work!
When you insert data into a ClickHouse table, here's what happens:
-- Your table has 3 columns -- On disk, ClickHouse creates SEPARATE files: data/ ├── my_table/ │ ├── all_1_1_0/ ← "data part" (batch of rows) │ │ ├── name.bin ← ALL names, compressed │ │ ├── age.bin ← ALL ages, compressed │ │ ├── city.bin ← ALL cities, compressed │ │ ├── primary.idx ← sparse index │ │ └── checksums.txt │ └── all_2_2_0/ ← another data part │ ├── name.bin │ ├── age.bin │ └── ...
Each column is a separate file! When you query SELECT AVG(age) FROM users, ClickHouse ONLY reads age.bin. The name.bin and city.bin files are never touched. This is why columnar storage is so fast for analytics.
Columnar storage enables incredible compression because similar values are stored together:
| Data Type | Row DB Compression | ClickHouse Compression | Why? |
|---|---|---|---|
| Country codes | ~2x | ~50x | Only 200 unique values → dictionary encoding |
| Timestamps | ~1.5x | ~20x | Sequential values → delta encoding |
| Integers | ~2x | ~10x | Same type → gorilla/T64 encoding |
| Strings | ~2x | ~8x | Similar patterns → LZ4/ZSTD |
A dataset that takes 1 TB in PostgreSQL might only take 100 GB in ClickHouse. That's 10x less storage cost AND 10x less data to read from disk for every query!
A regular database index is like a book where every single word has a page number. The index itself becomes as big as the book!
ClickHouse's sparse index is like a book that says: "Chapter 1 starts on page 1, Chapter 2 on page 50, Chapter 3 on page 100..." Much smaller! If you're looking for something on page 75, you know it's between Chapter 2 (page 50) and Chapter 3 (page 100). You only scan 50 pages instead of the whole book.
-- For 8.87 million rows with default granularity (8192): -- Traditional B-tree index: 8,870,000 entries (huge!) -- ClickHouse sparse index: 1,083 entries (tiny!) -- The sparse index fits in CPU L2 cache! -- This means index lookups are essentially FREE.
This is ClickHouse's secret weapon for CPU-bound operations:
When you INSERT data, ClickHouse doesn't modify existing files. Instead:
Imagine you're making a snowman. You start with tiny snowballs (small data parts from each INSERT). Over time, you roll them together into bigger and bigger balls (merges). Eventually you have a few large, well-organized snowballs instead of thousands of tiny ones.
This is why ClickHouse is append-optimized. UPDATEs and DELETEs are expensive because they require rewriting entire data parts. Always prefer INSERT over UPDATE in ClickHouse.
Checked against the ClickHouse documentation in October 2026. The JSON type has been production-ready since version 25.3. ClickHouse Cloud rewrites a MergeTree create onto the matching SharedMergeTree engine.
Data sits in parts. A part is a sorted column set on disk, local or object storage. Merges compact parts in the background. The ORDER BY key is the sparse primary index. PARTITION BY drops whole parts when the query names the partition. Too many partitions means too many small parts. SharedMergeTree keeps part bytes in shared object storage and coordinates metadata through Keeper.
SELECT partition, name, rows
FROM system.parts
WHERE database = 'analytics' AND table = 'events' AND activeWhat is the ORDER BY key used for?