Don't Panic — ClickHouse Fundamentals

ClickHouse is a database that reads tables sideways. Store a million rows in a transactional database and the rows sit on disk one after another; ClickHouse instead puts every column in its own tidy pile, so a query that needs three of a hundred columns never touches the other ninety-seven. That is the entire trick, and the rest of the system is engineering discipline built on top of it.

The problem it exists to solve: analytics. A dashboard over web events reads millions of rows, needs three columns, and wants the answer before the page finishes loading. Row stores can't do that at scale because disk I/O moves in fixed blocks, so every column of every row takes the trip. Column stores were the fix, but most were too slow or too expensive to answer ad-hoc questions, which is why analysts pre-aggregated everything and cried when the question changed. ClickHouse's bet: build the columnar engine fast enough that nobody needs to pre-aggregate. Yandex spent years on that bet, first inside its web analytics product, then released the result under Apache 2.0 in 2016.

Three ideas carry everything. First, parts: every insert writes a small immutable directory of compressed columns, and a background job merges small parts into bigger ones, like an oak tree made of CSV files with ambition. Second, the sorting key: the ORDER BY clause decides the physical order on disk, and queries that filter on those columns skip almost everything. Third, merges do the work: deduplication, updates, TTL expiry, all happen in the background when parts combine, which is why a "row update" here means inserting a new version and letting time resolve it.

The one surprise worth preparing for: ORDER BY is not a uniqueness constraint. Nothing stops you from inserting the same key twice. The index it builds is sparse, one entry per 8,192-row granule, which is why it fits in memory and why single-row lookups are ClickHouse's weakest party trick. If your workload is "find the row with this key, update it, commit", you want an OLTP database, and ClickHouse will not be offended. It has a whole product page for what it does not do, which is refreshingly honest.

What to read next: the Intro for the full architecture, the Slides when the part-merge model needs a picture, the Cheatsheet for the insert-batching numbers and the engine table. The Practice Reference walks a local table from insert through merges to deduplication, and the Quiz checks whether you can predict what a query reads before running it.