How ClickHouse Stores and Finds Data So Fast — Explained for SQL Users
I’ve spent the last few months building on top of ClickHouse, and the thing that hooked me wasn’t a benchmark chart. It was a moment of disbelief.
A query that crawled for close to a minute on a traditional database came back from ClickHouse in a few hundred milliseconds — over the same volume of data. My first reaction was that I’d made a mistake and it was reading a cached result. I hadn’t. That gap is real, and it isn’t magic. It comes down to two design decisions that ClickHouse commits to completely: how it stores data on disk, and how it finds the exact bytes it needs without reading everything else.
This post walks through both, using nothing more than a basic understanding of SQL. If you’ve written SELECT …WHERE … before, you’re ready.
First, why is ClickHouse different? OLAP vs OLTP
Before the internals, one idea has to click, because everything else follows from it.
Databases like PostgreSQL and MySQL are OLTP systems — Online Transaction Processing. They’re built for workloads that touch a few whole rows at a time: fetch this one user, update that one order, insert a new payment. Thousands of tiny, precise reads and writes, each dealing with complete rows.
ClickHouse is an OLAP system — Online Analytical Processing. It’s built for the opposite shape of work: scan an enormous number of rows, but only a handful of columns, and boil them down to an answer. “ What was the average order value per country last quarter? ” That query doesn’t care about any single row. It wants two columns across a hundred million rows.
These are genuinely different jobs, and the honest takeaway is this: ClickHouse is not a replacement for your transactional database. If your app does frequent single-row lookups and updates, keep using Postgres. ClickHouse is the tool you reach for when you need to slice, aggregate, and analyze huge tables quickly. Every design choice below makes sense once you see it as a database optimized for reading a lot of a little.
The first trick: columnar storage
Here’s the single most important difference. In a traditional row store, a table like this:
id | name | age
1 | Harsh | 29
2 | Virat | 39
3 | Rohit | 35is laid out on disk one row at a time — all of row 1’s fields sit together, then all of row 2’s, and so on:
[1, Harsh, 29][2, Virat, 39][3, Rohit, 35]ClickHouse flips this. It stores each column together instead:
id: [1, 2, 3]
name: [Harsh, Virat, Rohit]
age: [29, 39, 35]
Why does this matter so much? Consider SELECT avg(age) FROM users.
In a row store, the age values are scattered — one every few bytes, buried between the id and name of each row. To read every age, the database effectively has to walk over every full row, dragging id and name off the disk even though nobody asked for them.
In ClickHouse, every age sits in one contiguous file (age.bin). The query reads that single file, start to finish, and never touches id.bin or name.bin at all. On a wide table with fifty columns where you only need two, that’s the difference between reading 4% of the data and reading 100% of it.
The second trick: compression that actually works
Storing columns together buys a second win almost for free: dramatically better compression.
Think about why. In a row store, adjacent bytes on disk are a jumble of different types — a number, then a name, then an age, then another id. Compression algorithms look for repetition and patterns, and there isn’t much to find in that soup.
A column is the opposite. Every value is the same type, and very often they’re similar — a country column is mostly a small set of repeating strings, an age column is a narrow band of small integers, a status column might be three possible values across a billion rows. That’s a compression algorithm’s dream. Similar, repetitive data squeezes down enormously.
ClickHouse gives you two tiers of compression to work with.
General-purpose codecs compress any column:
- LZ4 — the default. Very fast to compress and decompress, moderate size savings. A great all-rounder.
- ZSTD — smaller files at the cost of more CPU. Worth it when disk or network is your bottleneck rather than CPU.
Specialized codecs are ClickHouse’s real party trick — they exploit the shape of specific data. The clearest example is Delta, which is perfect for timestamps and steadily-increasing counters.
Imagine a column of timestamps, one per minute:
1719750000, 1719750060, 1719750120, 1719750180, …Every value is a large number, and storing large numbers costs bytes. But look at the differences between neighbors — they’re all +60. That’s exactly what Delta stores: keep the first value as-is, then store only the gap to each following value.
1719750000, +60, +60, +60, …A long run of large, distinct numbers becomes a long run of one tiny repeated number — and thatcompresses down to almost nothing. There’s also DoubleDelta (the difference between the differences), which shines when even the step itself is nearly constant. You apply these in the table definition, layered on top of a general codec:
CREATE TABLE events (
event_time DateTime CODEC(Delta, ZSTD),
user_id UInt64,
country String
) ENGINE = MergeTree
ORDER BY event_time;Less data on disk isn’t just about saving storage. Every byte you don’t store is a byte you don’t have to read back later. Compression and columnar storage compound each other.
MergeTree: the engine underneath it all
You’ll notice ENGINE = MergeTree in that CREATE TABLE. MergeTree is the workhorse table engine in ClickHouse, and its name tells you how it behaves.
Get Harsh Godkar’s stories in your inbox
Join Medium for free to get updates from this writer.
Every time you run an INSERT, ClickHouse does not go and update existing files on disk. Instead it writes a brand-new, self-contained part — a little folder containing the .bin files for each column, plus some index files we’ll meet in a moment. Parts are immutable: once written, they’re never modified in place.
That raises an obvious question: if every insert makes a new part, won’t you drown in thousands of tiny parts? This is where the “Merge” comes in. A background process constantly takes small parts and merges them into fewer, larger, still-sorted parts — quietly, while your queries keep running.
This design has one practical consequence worth burning into memory: insert in big batches, not row by row. A thousand single-row inserts create a thousand tiny parts and pile enormous pressure on the merge process. One insert of a thousand rows creates one tidy part. Batch your writes and let the engine do the merging.
It also explains why ClickHouse handles updates and deletes so differently from an OLTP database. Because parts are immutable, changing existing data means rewriting whole parts (these are called mutations), which is deliberately expensive. It’s another reminder that ClickHouse is built for appending and reading, not for constant in-place edits.
The fetch chain: how ClickHouse reads only what it needs
Columnar storage narrows which columns get read. But there’s a second question: within a column of a hundred million rows, how does ClickHouse avoid scanning all of it to satisfy a WHERE? This is the part that most tutorials skip, and it’s the most elegant piece of the whole design.
The setup rests on three ideas.
Granules. Inside a part, each column isn’t indexed row by row. It’s divided into granules of 8192 rows each (the default index_granularity). The granule is the smallest unit ClickHouse reads — it never reads half a granule.
The sparse primary index. Here’s the clever bit. A traditional B-tree index (Postgres) stores one entry per row. ClickHouse’s primary index stores one entry per granule. If your table is sorted by event_time, the index just records the first event_time value of each granule. For 100 million rows, that’s roughly 12,000 index entries instead of 100 million — small enough to keep in memory permanently.
The trade-off is deliberate: this index can’t pinpoint an exact row. It can only tell you “the rows you want are somewhere inside granule 4,217”. So ClickHouse reads that whole 8192-row granule and scans it. For a single-row lookup that’s a terrible deal — you wanted one row and read 8192. For an analytical scan it’s a fantastic deal, because you’re processing millions of rows anyway and this lets you skip all the granules that can’t possibly match. That one sentence captures the whole philosophy: ClickHouse trades pinpoint row accuracy for the ability to skip enormous chunks of data cheaply.
The marks file. The index tells you which granule you want. But it doesn’t know where that granule’s bytes physically live inside the compressed .bin file. That’s the job of a third file — the marks file (.mrk2), one per column. It’s the bridge between the index and the raw data.
Put together, a lookup flows like this:
1. primary.idx — using the WHERE condition, figure out which granule(s) could contain matching rows. Skip everything else.
2. column.mrk2 — for each surviving granule, look up where its bytes are.
3. column.bin — read and decompress just that one block, and scan its 8192 rows.
So a query filtering to a single day out of two years of data doesn’t read two years of data. It reads a handful of granules — often a rounding error’s worth of the total file.
One more detail: why the mark stores two offsets
This is the subtle part, and understanding it means you truly understand the fetch path. A .bin file is not one continuous compressed stream — it’s a sequence of separately compressed blocks. And you can’t seek to an arbitrary byte inside a compressed block; you have to grab the whole block and decompress it first.
That constraint is why each mark holds two numbers:
- Offset 1— the byte position in the .bin file where the compressed block starts. This tells ClickHouse which block to pull off disk and decompress.
- Offset 2 — the position inside that decompressed block where the granule you actually want begins (a block can hold several granules).
One number can’t do the job, because the two live in different coordinate systems: a position in the compressed-on-disk file and a position in the decompressed-in-memory buffer, and compression makes them line up unpredictably. So a mark is a pair of coordinates: which block to open, and where to look once it’s open.
And how does ClickHouse know where a block ends, given that block sizes aren’t fixed? Each compressed block begins with a tiny header that records its own compressed and uncompressed size. ClickHouse jumps to offset 1, reads the header, learns exactly how many bytes to read, and grabs precisely one block — no more.
Putting it together
ClickHouse’s speed isn’t one trick, it’s a stack of them that reinforce each other:
- Columnar storage means a query reads only the columns it needs.
- Compression — especially specialized codecs like `Delta` — means each of those columns is far smaller on disk, so there are fewer bytes to read.
- MergeTree keeps data in sorted, immutable parts that the engine quietly consolidates.
- The sparse index + granules + marks mean that within those columns, ClickHouse skips straight to the handful of blocks that matter and ignores the rest.
Read fewer columns, that are smaller, and skip most of what remains. Stack those together over a hundred million rows and the minute-long query becomes a blink.
If you want to go deeper, ClickHouse’s own docs are the best next stop: the MergeTree engine reference covers exactly how parts, the primary index, and marks are laid out on disk. Once you can picture the granules being skipped, ClickHouse stops feeling like magic — and starts feeling like a very good idea, executed thoroughly.
