Why ClickHouse is fast

It reads columns, not rows#

A row-oriented database stores all fields of a row together. Reading one column of a hundred-column table still pulls the other ninety-nine off disk, because they share the same physical blocks.

ClickHouse stores each column in its own file inside a data part, alongside a mapping from granule number to the offset of its compressed block. A query that names three columns reads three columns and ignores the rest.

Column-oriented layout stores each column separately, so a query reads only the columns it names:

The trade is direct: reading one full row now costs several file reads instead of one. This is why ClickHouse suits aggregation over many rows and not single-row lookups.

Sorted data compresses better#

Rows are physically sorted by the table's ORDER BY key before being written. Sorting puts similar values next to each other, and adjacent similar values compress far better than randomly ordered ones.

Blocks combine neighbouring granules within a column, are formed on a configurable byte size (1 MB by default), and are compressed on disk and decompressed on the fly when read. ClickHouse uses LZ4 by default in the self-managed version and ZSTD in Cloud.

Specialized codecs exploit the shape of the data: Delta stores differences between neighbouring values for monotonic sequences, Gorilla XORs each float against the previous one for slowly-changing gauges.

CREATE TABLE codec_example
(
    dt Date CODEC(ZSTD),
    ts DateTime CODEC(Delta, ZSTD),
    gauge Float32 CODEC(Gorilla, ZSTD)
)
ENGINE = MergeTree
ORDER BY dt;

Getting types and codecs right took one real 143 GiB dataset down to 68 GiB uncompressed and halved its on-disk size. See compression and codecs.

The index skips most of the table#

Because the data is sorted, ClickHouse does not need an index entry per row. It divides each column into granules of 8,192 rows and stores the key values from just the first row of each granule. The result is small enough to hold entirely in memory.

When a query filters on primary key columns, ClickHouse scans that index, works out which granules cannot possibly match, and reads only the rest. On a 29.5-million-row table, a filter on two key columns selects 3 granules out of 3,609:

PrimaryKey
  Keys:
    town
    street
  Condition: and((street in ['OXFORD STREET', 'OXFORD STREET']), (town in ['LONDON', 'LONDON']))
  Parts: 3/3
  Granules: 3/3609

1 row in set. Elapsed: 0.010 sec. Processed 24.58 thousand rows, 159.04 KB (2.53 million rows/s., 16.35 MB/s.)

About 25,000 rows were processed instead of 29.5 million. The sparse primary index explains the mechanism in detail.

The cost of this design: it works only for the columns in the key, and in that key order. A filter on a non-key column reads everything. That is why choosing a primary key is the schema decision that matters most.

Execution is vectorized and parallel#

ClickHouse processes data in blocks of columns rather than row by row, which keeps CPU pipelines full and allows SIMD instructions to operate on many values per cycle. Work is spread across all available cores, and across shards and replicas in a cluster.

Parts add a second axis of parallelism: because each data part carries its own index and is self-contained, parts are scanned independently and merged at the end.

Measured effect of getting it right#

The same query on the same 60-million-row dataset, with and without a matching primary key:

-- ORDER BY tuple() — no useful primary key
SELECT count()
FROM stackoverflow.posts_unordered
WHERE (CreationDate >= '2024-01-01') AND (PostTypeId = 'Question')
 
1 row in set. Elapsed: 0.055 sec. Processed 59.82 million rows, 361.34 MB

-- ORDER BY (PostTypeId, toDate(CreationDate))
SELECT count()
FROM stackoverflow.posts_ordered
WHERE (CreationDate >= '2024-01-01') AND (PostTypeId = 'Question')
 
1 row in set. Elapsed: 0.013 sec. Processed 196.53 thousand rows, 1.77 MB

Both return 192,611. The second reads 300 times less data and runs about 4 times faster — the same engine, a different key.

What this design is not good at#

These trade-offs are worth stating plainly:

• Single-row lookups by id are slower than in a row store, because the row is assembled from several column files.
• Frequent updates and deletes rewrite whole parts. Use ReplacingMergeTree instead of mutations.
• Many small inserts create many parts and force constant merging. Batch them, or use async inserts — see inserting data.
• Transactions across rows are not the model ClickHouse implements.

Next steps#

• MergeTree and data parts — what an insert physically produces
• The sparse primary index — granule skipping in detail
• Choose a primary key — apply this to your own schema