Understand columnar storage

A row database stores values from one record together. A columnar database stores values from the same column together inside data parts.

Picture a pageview events 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 often 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 can skip unrelated columns entirely — the URLs, referrers, and seventeen other columns are never read from 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 contains millions of values of the same type, and similar values sitting together 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, which is what analytical queries spend most of their time on.

The third win is vectorized execution. Because values arrive as long same-typed arrays, ClickHouse processes them in batches using CPU instructions that operate on many values at once, instead of interpreting one row at a time.

The cost of the layout

The same layout makes row-oriented work expensive. Fetching one complete event means visiting twenty separate column files and reassembling the row. That inverts the row-store trade-off, and it’s why the previous lesson told you to keep OLTP state elsewhere.

Try it on paper

Create a wide events table on paper — say fifteen columns for a web analytics event. Mark which columns a daily count query reads and which columns ClickHouse can avoid.

Then do the same for “show me everything about event X”. You’ll see the first query touches two or three files out of fifteen, while the second touches all of them. That ratio — columns read versus columns stored — is the single best predictor of whether a query will fly or crawl on columnar storage, and it’s the lens to keep for the whole course.