Understand columnar storage

A row database stores all the values of one record together. A columnar database stores all the values of one column together. That one difference explains almost everything about how ClickHouse behaves.

Picture a pageview table with twenty columns: timestamp, URL, referrer, country, browser, and so on. In a row store, one pageview’s twenty values sit side by side on disk. In ClickHouse, all the timestamps sit together in one file, all the URLs in another, all the countries in a third.

Why the layout wins for analytics

An analytical query usually reads a few columns from many rows:

SELECT country, count() AS views
FROM pageviews
WHERE ts >= '2026-07-01'
GROUP BY country;

This query needs ts and country. Nothing else. ClickHouse skips the other eighteen columns entirely. The URLs and referrers never leave the disk. A row store has no such option. The values are interleaved, so it drags every column of every row through memory to use two of them.

The second win is compression. A column file holds millions of values of the same type, and similar values sitting next to each other compress extremely well. A country column is thousands of repeats of a few dozen strings. It might shrink fifty-fold. Smaller files mean less disk I/O, and disk I/O is where analytical queries spend most of their time.

The third win is vectorized execution. Values arrive as long arrays of the same type, so ClickHouse processes them in batches with CPU instructions that operate on many values at once. It never interprets one row at a time.

The cost of the layout

The same layout makes row-oriented work expensive. Fetching one complete event means opening twenty column files and reassembling the row from pieces. That is the row-store trade-off turned upside down, and it is why the first lesson told you to keep OLTP state somewhere else.

Be careful with SELECT * for the same reason. Every column you name is a file ClickHouse has to read. Naming all of them throws away the main advantage of the engine.

The ratio that predicts speed

Try this on paper. Sketch a wide events table, say fifteen columns for a web analytics event. Mark which columns a daily count query reads and which ones ClickHouse never touches.

Then do the same for “show me everything about event X”. The first query touches two or three files out of fifteen. The second touches all of them.

That ratio, columns read versus columns stored, is the best single predictor of whether a query will fly or crawl on columnar storage. Keep it in mind for the rest of the course. Every schema and query decision we make from here comes back to it.