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.

Source. This page is written from the ClickHouse documentation, read on 21 November 2026. ClickHouse was not run in these experiments, so the numbers are the docs' own examples, labelled as such.

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

A table = many data partsOne part (a folder on disk)primary.idx the sparse index<column>.bin one file per column<column>.mrk where each granule starts(skip index files, if any)Rows are sorted by theORDER BY key beforebeing written to the partso equal values sit togetherEvery insert makes a new part;background merges combineparts into bigger sorted onesWhy this is fast to readonly the needed columnsare opened (one file each)sorted data compresses welland can be located by atiny index

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 BY key, 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

One sorted column, cut into granules of 8,192 rows. The index keeps one entry (mark) per granule.binary search lands hereWith the primary index (the docs' example)1 of 1,083 granules read8.19 thousand rows, 740 KBWithout a useful indexall granules read8.87 million rows, 70.45 MBThe primary index held 96.93 KiB for 8.87 million rows, small enough to keep in memory.

Index entries are per granule, not per row, so the index stays small.

  • Granule: the smallest unit ClickHouse reads. index_granularity defaults to 8192 rows (and index_granularity_bytes to 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

MechanismWhat it doesFrom the docs
Skipping indexesPer block of granules, store min/max, a set of values or a bloom filter; skip blocks that cannot matchExample: a set index dropped 6,102 of 6,104 granules; 100 million rows scanned became 32.77 thousand
ProjectionsA hidden second copy of the data inside each part, sorted or pre-aggregated differently; the engine picks the cheapestExample read the same 39 marks as a table sorted by that key
Materialised viewsPre-aggregate at insert timeExample 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: Delta and DoubleDelta for timestamps and counters, Gorilla for slowly changing floats, T64 for integers, and LowCardinality(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

LevelDelta Lake + ParquetClickHouse
Choose files or partsPartition pruning, then min/max in the delta logPartition pruning, then the sparse primary index
Inside a file or partParquet footer min/max per row groupGranule marks, plus skipping indexes
Which columnsColumn chunks named in the queryOne file per column, only named ones opened
How data is orderedWhatever the writer produced, unless clustered or Z-orderedAlways sorted by ORDER BY inside every part
Pre-aggregationMaterialized views, metric view materialisationProjections, materialised views
UpdatesCopy on write, deletion vectorsParts 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 recommend wait_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.

Not confirmed: defaults for the mark and uncompressed caches, how much the operating system page cache helps, the default block size, and join behaviour. The docs pages I could read did not state them. Also, the mark counts quoted for the second key column, projections and materialised views (1,076 of 1,083, 39, 41) came from a summarised part of the long sparse-index guide in the docs rather than its verbatim text, so check them there.

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.

← 9 · Write path