Home › Learn › How reads and writes work › 10 · ClickHouse
Delta Lake and Spark · from zero to advanced
Why reads are so fast in ClickHouse
How ClickHouse reads data: sorted parts, one file per column, the sparse primary index with 8,192-row granules, skipping indexes, codecs, vectorised parallel execution, and how that compares with Delta and Parquet, from the ClickHouse docs.
The idea
Read less, and read it faster
ClickHouse is a column database built for analytical reads. Its speed comes from stacking several ideas: store columns separately, keep rows sorted so that a tiny index can find them, skip whole blocks, compress well, and process data in batches on all cores. Most of these have a counterpart in the Delta and Parquet world you have just seen.
On disk
Sorted parts, one file per column
Each part is a folder; each column is its own file.
- Parts. A table is a set of immutable data parts. Each insert creates one, sorted by the
ORDER BYkey, with its own sparse primary index. - One file per column in the usual (wide) format, plus a marks file saying where each granule starts. A query opens only the columns it names.
- Compact parts keep all columns in one file for small, frequent inserts; the docs say a part is stored compact or wide by size thresholds (
min_bytes_for_wide_part,min_rows_for_wide_part), but I could not confirm their defaults. - Background merges combine parts within a partition into larger sorted ones; the docs give a target of about 150 GB compressed.
The sparse primary index
A tiny index that points at blocks of 8,192 rows
Index entries are per granule, not per row, so the index stays small.
- Granule: the smallest unit ClickHouse reads.
index_granularitydefaults to 8192 rows (andindex_granularity_bytesto 10 MB). - Sparse: the index stores the first value of each granule, not every row, so it fits in memory. Finding rows is a binary search over those marks, then a read of just the matching granules.
- Order matters: the index only helps filters on the leading sort-key columns. The docs show a filter on the second key column reading 1,076 of 1,083 marks, while the same query on a table sorted the other way read 39.
- A filter on a non-leading column uses a weaker 'generic exclusion search' that works well only if the columns before it have few distinct values.
From the docs' worked example: WHERE UserID = 749927693 read 1 mark of 1,083 and processed 8.19 thousand rows (740 KB); the same query on a table without that key processed 8.87 million rows (70.45 MB).
Skipping and pre-aggregation
Three more ways not to read
| Mechanism | What it does | From the docs |
|---|---|---|
| Skipping indexes | Per block of granules, store min/max, a set of values or a bloom filter; skip blocks that cannot match | Example: a set index dropped 6,102 of 6,104 granules; 100 million rows scanned became 32.77 thousand |
| Projections | A hidden second copy of the data inside each part, sorted or pre-aggregated differently; the engine picks the cheapest | Example read the same 39 marks as a table sorted by that key |
| Materialised views | Pre-aggregate at insert time | Example read 41 of 1,083 marks |
The docs warn that adding a skipping index to a key column is often the wrong fix: indexes only help when the indexed column correlates with the primary key. Try the sort key, a projection or a materialised view first.
Compression and execution
Small on disk, wide in parallel
- Codecs. The default is LZ4 for self-managed ClickHouse and ZSTD in ClickHouse Cloud. Per column you can chain specialised codecs:
DeltaandDoubleDeltafor timestamps and counters,Gorillafor slowly changing floats,T64for integers, andLowCardinality(String)for dictionary encoding. - Vectorised processing. Operators pass batches of rows, not single rows, which suits CPU caches and SIMD instructions.
- Parallelism. The plan is unfolded into lanes, typically one per core, each handling a separate range of the data.
- Less I/O is also faster compute: the docs note compression often speeds queries up, because disks and networks are the bottleneck.
Side by side
ClickHouse and Delta, level by level
| Level | Delta Lake + Parquet | ClickHouse |
|---|---|---|
| Choose files or parts | Partition pruning, then min/max in the delta log | Partition pruning, then the sparse primary index |
| Inside a file or part | Parquet footer min/max per row group | Granule marks, plus skipping indexes |
| Which columns | Column chunks named in the query | One file per column, only named ones opened |
| How data is ordered | Whatever the writer produced, unless clustered or Z-ordered | Always sorted by ORDER BY inside every part |
| Pre-aggregation | Materialized views, metric view materialisation | Projections, materialised views |
| Updates | Copy on write, deletion vectors | Parts and merges; point updates are not its strength |
The biggest difference is the sort. ClickHouse sorts every part by the key as it writes, so a narrow index always works. Delta leaves ordering to you, so the layout steps on the layout page (clustering, Z-order) are how you get the same effect.
The write side
Why inserts want batches
- Every synchronous insert creates a part, so the docs recommend batches of at least 1,000 rows, ideally 10,000 to 100,000, and about one insert per second. Too many small parts means too many merges ('too many parts' errors).
- Async inserts (
async_insert) buffer small inserts on the server: the docs list a 100 MiB size limit, a 200 ms busy timeout and up to 450 queries per flush as defaults, and recommendwait_for_async_insert = 1. - Where it is weaker (docs): point lookups and point updates (the sparse index finds ranges, not single rows), and single-row high-frequency inserts. I found no statement about join performance in the pages I read.
Checking
How to see what a query read
EXPLAIN indexes = 1 SELECT … shows how many parts and granules the index selected, for example Granules: 1/1083. The system.query_log table records read_rows and read_bytes per query.
FAQ
Questions people ask
Why are ClickHouse reads so fast?
It reads fewer bytes (one file per column, a sparse primary index that finds 8,192-row granules, skipping indexes), compresses well, and processes batches of data in parallel on all cores. In the docs' example an index lookup read 8.19 thousand rows instead of 8.87 million.
What is a granule?
The smallest block ClickHouse reads, by default 8,192 rows. The primary index stores one entry per granule, not per row, so it is small enough to sit in memory.
Why does ClickHouse want batched inserts?
Each insert creates a new data part, and background merges must combine them. The docs recommend batches of at least 1,000 rows, ideally 10,000 to 100,000, at about one insert per second, or async inserts.