Storage: columnar parts

The question this module answers is: how can a database scan billions of rows and read only what the query needs? The answer is not “columnar storage”, which is only a third of it. ClickHouse made one decision, to store each column separately, in sorted order, in compressed blocks addressed by a tiny sparse index, and every property you will meet in the rest of the course follows from it. Once you can compute what a query will read from the table’s layout, you stop guessing about performance. When you can’t, you drift toward habits that came from row stores, and those habits are expensive here.

This module has four parts, always in the same order. First the physical invariant: what is on disk and in memory, and why the three properties are one choice. Second the cost equation: bytes read per query, as a product you can calculate and then measure. Third the failure modes: what happens when you treat ClickHouse like PostgreSQL, one measured demonstration each, with the shape that works instead. Fourth the comparison matrix for the storage domain, including when not to use ClickHouse.

It does not repeat the mechanics of parts, merges and mutations; those are in How MergeTree stores data. Here the question is why the layout is what it is and what it costs you. Everything below was run on ClickHouse 26.4 with clickhouse local, so every number is real. You can reproduce all of it with no server.

1. The physical invariant

The demo table

Ten million rows of the demo schema. The values come from hashes, so the data is deliberately unstructured: no column correlates with any other. That is the hardest case for compression, so real data will do better, but the effects below come from the layout, not from the data’s luck.

CREATE TABLE events (event_time DateTime, user_id UInt64, event_type LowCardinality(String),
                     url String, duration_ms UInt32, country LowCardinality(String))
ENGINE = MergeTree ORDER BY (event_type, event_time);

INSERT INTO events
SELECT toDateTime('2026-01-01 00:00:00') + intDiv(number, 40) + (cityHash64(number, 1) % 60),
       cityHash64(number, 2) % 1000000,
       ['view','click','buy','scroll','login','logout','search','share'][cityHash64(number, 3) % 8 + 1],
       concat('/p/', toString(cityHash64(number, 4) % 5000), '/item'),
       cityHash64(number, 5) % 5000,
       ['US','DE','TR','FR','JP','BR','IN','GB','CA','AU'][cityHash64(number, 6) % 10 + 1]
FROM numbers(10000000)
SETTINGS max_insert_block_size = 10000000, min_insert_block_size_rows = 10000000;

OPTIMIZE TABLE events FINAL;   -- merge the insert's blocks into one part, for clean numbers

Run it with clickhouse local --path db, so the data persists between invocations and you can keep experimenting. One part:

SELECT name, rows, marks, formatReadableSize(bytes_on_disk) AS disk,
       formatReadableSize(data_uncompressed_bytes) AS uncompressed,
       formatReadableSize(primary_key_bytes_in_memory) AS pk_memory,
       formatReadableSize(marks_bytes) AS marks_size, round(rows / marks) AS rows_per_mark
FROM system.parts WHERE `table` = 'events' AND active FORMAT Vertical;

Row 1:
──────
name:          all_1_2_1
rows:          10000000 -- 10.00 million
marks:         1222
disk:          137.11 MiB
uncompressed:  360.32 MiB
pk_memory:     6.08 KiB
marks_size:    17.75 KiB
rows_per_mark: 8183

Three numbers carry the whole module. The data is 360 MiB raw and 137 MiB on disk. It is divided into 1,222 marks, about 8,192 rows each. And the structure that lets a query find the right marks, the primary index, is 6 KiB in memory. Not 6 MiB: six kibibytes, for ten million rows. (If your pk_memory shows 0 on a freshly merged part, nothing is wrong: the primary index is loaded lazily, on first use, governed by the primary_key_lazy_load MergeTree setting. The figure above is after a query touched it.)

What a column costs

Ask the same part, column by column:

SELECT column, type,
       round(column_data_uncompressed_bytes / rows, 2) AS bytes_per_row_raw,
       round(column_data_compressed_bytes / rows, 3)   AS bytes_per_row_disk,
       round(column_data_uncompressed_bytes / column_data_compressed_bytes, 1) AS ratio,
       formatReadableSize(column_data_compressed_bytes / marks) AS per_granule
FROM system.parts_columns WHERE `table` = 'events' AND active
ORDER BY column_data_compressed_bytes DESC;

url          String                  19.78   4.899   4     39.15 KiB
user_id      UInt64                  8       4.618   1.7   36.91 KiB
duration_ms  UInt32                  4       2.988   1.3   23.88 KiB
country      LowCardinality(String)  1       0.956   1     7.64 KiB
event_time   DateTime                4       0.909   4.4   7.27 KiB
event_type   LowCardinality(String)  1       0.005   212.4 38.61 B

(Columns: column, type, bytes_per_row_raw, bytes_per_row_disk, ratio, per_granule.)

Read the last column as the unit price of data. A granule of event_type costs 38 bytes on disk. A granule of url costs 39 KiB, a thousand times more, for the same 8,192 rows. In a row store both live inside every row, so a query that needs only one of them pays for all. Here each is a separate stream and you pay for the ones you name.

The ratios differ for a reason that matters later. event_type compresses 212 times because the table is sorted by it: the file is eight long runs of identical values. user_id compresses 1.7 times because it is 20 bits of entropy in a 64-bit field and nothing orders it. The sort key decides which columns get cheap.

Three layers, three different jobs

The words granule, mark and block get blurred together, and the blur causes real mistakes. They are three separate things.

• A granule is a logical run of rows, 8,192 by default (index_granularity). It has no physical existence of its own. It is the unit the primary index can skip.
• A mark is the address of a granule inside one column’s file: a pair of offsets, one to the compressed block, one into the decompressed bytes of that block. There is one mark per granule per column, 17.75 KiB in total here.
• A compression block is the unit the column file is actually compressed and read in: between 64 KiB and 1 MiB of uncompressed data (min_compress_block_size and max_compress_block_size, 65,536 and 1,048,576 by default). The default codec is LZ4. It is the unit the disk read and the decompression cannot split.

Skipping happens at granule resolution; reading happens at block resolution. For a column like event_type, hundreds of granules fit into one block; for url, a block holds a few dozen. When a query selects a scattered handful of granules, it still decompresses the whole block around each one. We will see that number get ugly in the failure modes. The primary index itself holds one entry per granule, the sorting-key values of its first row; that is why it is 6 KiB rather than a B-tree’s one entry per row. The tutorial on reading a part lists the actual files if you want to see them. The reference for the settings above is the MergeTree docs.

Why it is one decision

Treat the three properties separately and each looks like an optimization you could take or leave. They are not independent.

Columnar makes compression work. A column contains one type, with a narrow range of values. A row contains a timestamp next to a URL next to a country; no codec finds structure in that. Per-column files give LZ4 (or a specialised codec such as Delta or ZSTD, see choosing codecs) a homogeneous stream.

Sorting makes compression work harder. Equal and similar values become neighbours. To see how much, build the same ten million rows with five different sorting keys and compare each column’s compressed size:

CREATE TABLE ev_none      AS events ENGINE = MergeTree ORDER BY tuple();
CREATE TABLE ev_time_type AS events ENGINE = MergeTree ORDER BY (event_time, event_type);
CREATE TABLE ev_user_time AS events ENGINE = MergeTree ORDER BY (user_id, event_time);
CREATE TABLE ev_url_type  AS events ENGINE = MergeTree ORDER BY (url, event_type, event_time);
-- each is filled with INSERT INTO ... SELECT from the generator above (ev_none) or from events,
-- then OPTIMIZE ... FINAL

Compressed size	tuple() (unsorted)	(event_type, event_time)	(event_time, event_type)	(user_id, event_time)	(url, event_type, event_time)
event_type	8.11 MiB	46 KiB	4.02 MiB	8.11 MiB	300 KiB
event_time	27.7 MiB	8.67 MiB	1.22 MiB	38.2 MiB	38.2 MiB
user_id	44.0 MiB	44.0 MiB	44.0 MiB	7.67 MiB	44.0 MiB
url	46.7 MiB	46.7 MiB	46.7 MiB	46.7 MiB	0.9 MiB
duration_ms, country	unchanged	unchanged	unchanged	unchanged	unchanged
Whole table	164 MiB	137 MiB	134 MiB	138 MiB	121 MiB

Whichever column leads the sorting key collapses: url from 46.7 MiB to 0.9 MiB, event_time from 38 MiB to 1.2 MiB. The columns that are not in the key do not change at all, and columns later in the key get only a partial benefit. The unsorted table is also not the baseline of a naive implementation: it is the same data, same codecs, only in insertion order, and it is 20% bigger. (The whole-table gains look modest here only because hash-generated user_id and duration_ms are incompressible noise. On real data with correlated columns, factors of 5 to 10 on the whole table are ordinary.)

Sorting makes the sparse index possible. An index with one entry per 8,192 rows can only locate data if the data is ordered by the indexed value. Sorted data means the rows matching event_type = 'buy' are one contiguous range, and one binary search over 1,222 entries finds both ends. Unsorted data would need an entry per row to answer the same question, and then you have rebuilt the B-tree and its cost.

So the order of dependency is: columns in separate files make per-column compression and per-column reads possible; the sort makes both the compression and the index work; the sparse index makes it cheap enough to keep in memory that reading a range costs nothing before the first byte of data. Remove any one and the others lose most of their value. That is what “one decision” means, and it is also why ORDER BY in a ClickHouse CREATE TABLE is not an index definition. It is the physical layout of the table.

2. The cost equation

The formula

For a query on a single MergeTree table, the bytes pulled from disk are approximately

bytes_read  ≈  Σ over columns c the query touches  [ compressed_bytes(c) × granules_selected / granules_total ]
                + block overshoot

Three levers, then. Which columns: only the ones the query names, in SELECT, WHERE, GROUP BY, ORDER BY. Which granules: the fraction the primary index (and any skipping index) fails to rule out. How well each column compresses: the per-column ratio from part one. The overshoot term is the cost of block-level reading, small for contiguous ranges and large for scattered ones.

How to measure it locally

system.query_log needs a server. On clickhouse local there are three tools that work. EXPLAIN indexes = 1 reports granules selected against granules total (the EXPLAIN tutorial covers the rest of it). --print-profile-events prints the profile events of the query: SelectedMarks, SelectedRows, and ReadCompressedBytes, the compressed bytes actually read from the part files. And --time prints wall-clock seconds. Two notes from running this on 26.4.1: the statistics block of FORMAT JSON reports rows_read and bytes_read as 0 in clickhouse local, so do not rely on it there; and profile events arrive as several increments per query, which must be summed. Every figure below is that sum.

One caveat on timing. Everything here is served from the operating system’s page cache on a laptop-class machine, so queries finish in single-digit milliseconds and the timings are close to noise. Treat bytes read as the stable quantity: on a cold cache, a network disk or object storage, time follows bytes. The times quoted below are the best of five runs with --max_threads=4.

Lever 1: columns touched

Same table, same full scan, different columns:

Query	Columns	Granules	Compressed bytes read
SELECT count() FROM events	none (part metadata)	0 / 0	0
SELECT event_time FROM events	event_time	1221 / 1221	8.7 MiB
SELECT avg(duration_ms) FROM events	duration_ms	1221 / 1221	28.5 MiB
SELECT uniq(url) FROM events	url	1221 / 1221	46.7 MiB
SELECT avg(duration_ms), uniq(user_id) FROM events	two	1221 / 1221	72.5 MiB
SELECT * FROM events	all six	1221 / 1221	137.3 MiB

Each row is the sum of the compressed sizes of the columns it names: 28.5 is exactly duration_ms’s 28.49 MiB from system.parts_columns. A full scan of the whole table is 137 MiB, and an aggregate over one column is between 6% and 34% of that depending on which column. The first row is the extreme case: count() with no filter is answered from part metadata and reads no column at all.

Lever 2: granules selected

Now add predicates on the sorting key (event_type, event_time):

SELECT avg(duration_ms) FROM events WHERE event_type = 'buy';
SELECT avg(duration_ms) FROM events
WHERE event_type = 'buy' AND event_time >= '2026-01-02 00:00:00' AND event_time < '2026-01-02 06:00:00';

Query	Granules selected	Rows read	Compressed bytes read
no filter	1221 / 1221	10,000,000	28.50 MiB
event_type = 'buy'	153 / 1221	1,253,376	3.70 MiB
event_type = 'buy' and a 6-hour window	14 / 1221	114,688	0.44 MiB

EXPLAIN indexes = 1 shows how the engine got there for the last one:

ReadFromMergeTree (default.events)
Indexes:
  PrimaryKey
    Keys:
      event_type
      event_time
    Condition: and((event_time in (-Inf, 1767322799]), and((event_time in [1767301200, +Inf)), (event_type in [\'buy\', \'buy\'])))
    Parts: 1/1
    Granules: 14/1221
    Search Algorithm: binary search
  Ranges: 1

Binary search over the primary index, one contiguous range, 14 granules out of 1,221. The equation predicts the bytes. The second query reads three columns, duration_ms, event_time and event_type, at 14 of 1,221 granules:

(28.49 + 8.67 + 0.045) MiB × 14 / 1221 = 0.427 MiB   predicted
                                         0.44  MiB   measured

The first, one filter column plus one aggregate column at 153 of 1,221 granules, predicts 3.58 MiB and measures 3.70. The few percent of overshoot are compression blocks being read whole. A query that went from 28.5 MiB to 0.44 MiB did it by reading 1.1% of the granules, and the table did not change.

Lever 3: the ORDER BY decides how many granules

The sorting key is chosen once, at table creation, and it fixes the granule count for every query you will ever run. Here is the same 6-hour, one-event-type query against the five layouts from part one:

ORDER BY	Granules selected	Compressed bytes read
(event_type, event_time)	14 / 1221	0.44 MiB
(event_time, event_type)	107 / 1221	3.11 MiB
(user_id, event_time)	1221 / 1221	75.0 MiB
(url, event_type, event_time)	1221 / 1221	67.0 MiB
tuple()	1221 / 1221	38.7 MiB

Same data, same query, same hardware: 14 granules against 1,221, a factor of 87. The difference between the first two rows, 7.6 times, is purely column order. Both have exactly the same two columns in the key, and event_type first lets the index cut to one of eight runs before time narrows it. The user_id and url layouts cannot use the filter at all, because the leading key column is high-cardinality: within any one value of user_id, event_time is a few rows, so the data is effectively ordered by user_id alone, and knowing the time window tells you nothing about where to look.

The same layout has an effect when the leading column is not in the filter. A query on event_time alone against (event_type, event_time) still prunes, because each of the eight event_type runs is itself sorted by time:

Keys: event_time
Granules: 122/1221
Search Algorithm: generic exclusion search
Ranges: 23

It reads 122 of 1,221 granules, a tenth of the table, in 23 separate ranges rather than one, using the slower “generic exclusion” algorithm instead of binary search. That is the partial benefit of a low-cardinality prefix, and it shrinks with every order of magnitude of prefix cardinality. The sparse index calculator lets you set row counts, granule size and key cardinalities and see how many granules a given filter is expected to select before you create the table. ORDER BY, PARTITION BY and pruning covers how partition keys interact with this.

Put the three levers next to each other and the budget is clear. Naming columns instead of * is worth 4x to 100x depending on width. A well-chosen sorting key is worth 10x to 100x. Compression ratios are the third multiplier, and the ORDER BY pulls it too: choosing event_time first made that column 7 times smaller. They multiply. They are not rival optimisations; they are the same one.

3. Failure modes: fighting the layout

Each of these patterns is normal in PostgreSQL and will work on a small table. Each is a way of asking the layout for something it was arranged not to provide. For each, the measurement and the shape that fits.

Point lookups by a non-key column

In PostgreSQL, WHERE user_id = 424242 with a B-tree on user_id touches a handful of pages. On events the sorting key is (event_type, event_time), so:

SELECT count(), avg(duration_ms) FROM events WHERE user_id = 424242;

Granules: 1221/1221      Condition: true
compressed bytes read: 44.6 MiB   (user_id + duration_ms, every granule)

The index has nothing to say about user_id, so all 10 million rows are read for roughly ten matching ones. The right fix depends on how often you need this lookup.

• Make the lookup column the leading key. With ORDER BY (user_id, event_time) the same query reads 1 granule and 0.07 MiB. Right when this is the dominant access path and the other queries can live with the new order (and from part two, they may not: that table’s event-type query read 1,221 granules).
• A data-skipping index. ALTER TABLE events_pl ADD INDEX user_bf user_id TYPE bloom_filter(0.01) GRANULARITY 1 followed by MATERIALIZE INDEX cut the query to 22 of 1,221 granules. The bytes read, though, were 13.2 MiB, not the 0.3 MiB you might have computed. The skipping index is itself a file, 11.9 MiB here, and a bloom filter must be read in full to be consulted. Add to that the compression-block overshoot, because the 22 matching granules are scattered. On this data, where each user’s rows are spread at random, it is a 3.4x reduction from 44.6 MiB: real, not dramatic. Skipping indexes shine when the value is clustered with the sort order. See data-skipping indexes.
• A projection: a second copy of the data sorted for the second access path. ADD PROJECTION by_user (SELECT * ORDER BY user_id), then MATERIALIZE PROJECTION, took the same query to 1 granule, 0.06 MiB. The price is storage: the table went from 137 MiB to 273 MiB, because the projection is a full second copy. See projections.

All three are the same trade: you pay disk and write cost to give a second access path the layout it needs. There is no free third option, and none of them turns ClickHouse into a key-value store. Even the ideal case reads one granule: 8,192 rows decompressed, about 0.2 MiB, in two milliseconds here. For one-row-per-request serving at thousands of queries per second, put a row store in front.

SELECT * on a wide table

The fixed costs of a row store are small because rows are read whole anyway. Here SELECT * multiplies the bill by the column count. A table of 100 UInt32 columns and 2 million rows, hash-generated so nothing compresses (766 MiB on disk):

Query	Compressed bytes read
SELECT sum(c042) FROM wide	7.6 MiB
SELECT sum(c042), sum(c043), sum(c044) FROM wide	22.9 MiB
SELECT * FROM wide	771.8 MiB
SELECT * FROM wide WHERE id = 1234567	6.3 MiB (1 granule × 100 columns)
SELECT c042 FROM wide WHERE id = 1234567	0.10 MiB

One column versus all: 101 times the I/O. Even the single-row lookup by the primary key pays 63 times more for * than for the one column it needs, because each of the 100 columns costs at least one compression block. There is a mitigation worth knowing about. On 26.4 the engine can defer reading columns that are not needed to evaluate the filter or the sort, which it calls lazy materialization:

SELECT * FROM wide ORDER BY c042 LIMIT 10;
-- 63.3 MiB read, against 771.8 MiB with query_plan_optimize_lazy_materialization = 0

It reads c042, finds the ten winners, and only then fetches the other 99 columns for ten rows. That is a 12x reduction by a setting the engine applies by itself, and it is the correct answer to “top-N with all columns”. It does not rescue SELECT * in a join, in a subquery that returns everything, or in an export. The rule stays: name the columns. In an application, define the narrow column lists once and treat * as a debugging tool. In a view over a wide table, expose a view per use, not one view of everything.

A high-cardinality column first in ORDER BY

The table in part two is the demonstration. ORDER BY (user_id, event_time) read 1,221 granules for the dashboard query that (event_type, event_time) answered in 14, and it compressed event_type 180 times worse (8.11 MiB against 46 KiB). The mistake comes from Postgres: the leading column of a B-tree index should be the most selective one. In a sparse index the logic inverts. Columns go from lowest to highest cardinality, because a low-cardinality prefix creates long runs that the index can exclude whole, and each following column sorts inside the run before it.

The counter-example keeps this honest. If your dominant query is WHERE user_id = ?, then user_id should lead: that table did the lookup in a single granule. The rule is not “never put the high-cardinality column first”. It is “put first the column that the queries which matter filter on, and prefer low cardinality when several are equal”, and then check with EXPLAIN indexes = 1 that the granule count falls. Do not guess. The sparse index calculator is there for exactly this.

Frequent single-row inserts

Every INSERT creates a part, and a part is a directory of files. In PostgreSQL an insert is an append to a heap page and a WAL record, and a thousand of them per second is unremarkable. In ClickHouse a thousand inserts per second is a thousand parts per second that background merges must then combine, rewriting the same rows several times.

To see what happens when merges fall behind, this demonstration stops them (SYSTEM STOP MERGES) and inserts 900 rows one at a time:

SELECT count() AS parts, sum(rows) FROM system.parts WHERE `table` = 'ev_single' AND active
900   900

Nine hundred parts for 900 rows. A full scan of that table (SELECT sum(duration_ms)) took 15 ms, against 3 ms for a table holding 2,000 rows, more than twice as many, in a single part: five times slower for less data, because opening and reading files dominates and every part is an independent set of files. Read that as a warning on the shape of the cost; the absolute numbers are tiny only because the table is tiny.

The system defends itself, and the thresholds are worth knowing from system.merge_tree_settings:

parts_to_delay_insert   1000    -- inserts start being artificially delayed
parts_to_throw_insert   3000    -- inserts fail with "Too many parts"
max_delay_to_insert     1       -- maximum delay per insert, seconds

These count active parts per partition, so a table partitioned finely is exposed earlier. With merges running, a modest single-row rate may stay under them, but you are paying write amplification continuously for no reason. The part pressure calculator estimates it from your insert rate and merge capacity. The right shape is batching: insert 10,000 to 1,000,000 rows at a time from your application, or let the server batch with async_insert (async inserts explained). The rest is in insert batching and too many parts, and the ingestion domain in this course (module 4) gives it a whole module.

Updating in place

The layout has no “row” to update: a value lives inside a compressed block of a column file inside an immutable part. The ALTER TABLE ... UPDATE mutation is the closest thing, and its cost model is the surprise. One row, one column:

ALTER TABLE ev_mut UPDATE duration_ms = 0 WHERE user_id = 424242 SETTINGS mutations_sync = 2;

It took 0.30 s to change one row, against 0.005 s for inserting one. Listing the new part’s column files with ls -li shows why: eight of the nine .bin stream files are hard links shared with the previous part (link count above 1), and exactly one, the duration_ms file, is a freshly written 29.9 MB file with link count 1. Changing one value rewrote the whole 10-million-row column. The cost is proportional to the column, not to the number of rows changed, which is the correct consequence of immutability: a block cannot be edited, so every block of that column in the part is written again. The MergeTree tutorial measures the cost model of mutations and lightweight updates in more depth.

The shape that fits is to stop updating. Insert the new version of the row and let the engine resolve it:

CREATE TABLE profiles (user_id UInt64, country LowCardinality(String), plan LowCardinality(String), updated_at DateTime)
ENGINE = ReplacingMergeTree(updated_at) ORDER BY user_id;

INSERT INTO profiles SELECT number, 'DE', 'free', toDateTime('2026-01-01 00:00:00') FROM numbers(100000);
INSERT INTO profiles VALUES (424, 'DE', 'pro', toDateTime('2026-02-01 00:00:00'));   -- the "update"

SELECT user_id, plan, updated_at FROM profiles FINAL WHERE user_id = 424;
SELECT user_id, argMax(plan, updated_at) AS plan FROM profiles WHERE user_id = 424 GROUP BY user_id;
SELECT count() FROM profiles WHERE user_id = 424;

424	pro	2026-02-01 00:00:00
424	pro
2

The first two queries resolve to the current row. The third is the lesson: until a merge collapses them, both versions exist, and count() sees two. Reads must use FINAL, argMax, or a query pattern that tolerates duplicates. That is the real trade of this engine family: updates become cheap writes and slightly more expensive reads. It is the right exchange for a profile table that changes occasionally and is read in aggregate. It is the wrong one for a counter incremented thousands of times a second per key, which is an OLTP workload.

4. The honest comparison

The question of this module is about the storage domain, so the matrix stays on storage: how the data is laid out, what a query reads, what lookups and updates cost. It is not a ranking. Each system is the right answer to a different question, and the last row says when ClickHouse is not.

Claims about the other four systems are the general, documented ones. The footnote links go to each project’s own documentation; for anything beyond what is stated here, check the current docs for your version.

	ClickHouse	PostgreSQL	Elasticsearch	Snowflake	DuckDB
Layout	Columnar immutable parts, sorted by the sorting key	Row-oriented heap of fixed-size pages (commonly 8 kB) (PostgreSQL docs)	Lucene segments: inverted index per field, plus columnar doc values and stored source (doc values)	Columnar micro-partitions, 50 to 500 MB uncompressed each (micro-partitions)	Embedded columnar storage in a single database file, in row groups (docs)
What a query reads	Only the named columns, only the granules the sparse index leaves	Whole rows per page; indexes avoid pages. Column pruning does not exist in the heap	Postings for a term search; doc values for sorting and aggregation; _source for the document	Only the named columns, only the micro-partitions that metadata pruning leaves	Only the named columns, row groups pruned by min-max statistics
Primary index	Sparse: one entry per 8,192 rows	Dense B-tree: one entry per row (docs)	Term dictionary and postings per field	None: min-max metadata per micro-partition	Min-max statistics per row group; optional ART index
Compression	Per column, LZ4 default, choose codecs per column; ratio improves with the sorting key	Largely none for ordinary rows; TOAST compresses only large values (TOAST)	Block compression of stored fields and doc values, automatic	Automatic, per column inside each micro-partition	Automatic, per column segment, lightweight schemes chosen per segment
Point lookup	Weak: reads at least one granule of every column named (see part three)	Excellent: B-tree, a few page reads	Strong by _id or an indexed term	Weak by default; the search optimization service adds point-lookup acceleration (search optimization)	Moderate: row groups prune on min-max; the ART index helps selective lookups
Updating a row	A mutation rewrites the column in the part; or insert a new version (Replacing)	Cheap: MVCC writes a new row version (MVCC)	Rewrites the whole document as a new one; old segments reclaimed by merges (merge settings)	DML rewrites the affected micro-partitions; hybrid tables (row store) exist for OLTP (hybrid tables)	Supported, inside one process; not meant for high-rate concurrent writers
Write path	Batches as parts; high throughput, poor at tiny inserts	Row at a time, WAL; excellent at tiny transactions	Documents buffered and made searchable by periodic refresh, default 1 s (near real-time search)	Bulk loads and micro-batches into micro-partitions	Bulk-oriented; embedded, single writer process
Where it runs	Server or cluster, self-hosted or managed	Server, self-hosted or managed	Server or cluster	Managed service only	In-process library, one machine

When not to use ClickHouse

The matrix’s pattern is the module’s pattern: the strengths in the first rows are paid for in the lookup and update rows. So the cases where something else is the better storage layer are not edge cases; they are the inverse of the design.

• Row-at-a-time OLTP. Fetch an order, change an order, commit. A B-tree and MVCC row versions are made for it; a sparse index and immutable parts are not. Use PostgreSQL, and replicate or stream into ClickHouse for the analytics.
• High-rate updates and deletes of individual rows, and counters. Mutations cost a column; replacing rows costs read-time merging. If the workload is mostly update, the engine is working against itself.
• Multi-statement transactions with rollback across tables. ClickHouse’s guarantees are per-insert and per-part. Do not build a ledger on it.
• Small data. Under a few gigabytes, PostgreSQL with sensible indexes or DuckDB on a file is simpler to run and just as fast. The sparse index and the columnar scan pay off when the table is too big to read quickly, and not before.
• Relevance-ranked text search. Scoring, stemming, fuzzy matching and the whole search-engine feature set live in Elasticsearch or similar. ClickHouse has full-text capabilities and is good at filtering and aggregating over text, but it is not a search engine.
• Serving single rows to many users at once. Key-value traffic belongs in a store designed for it; put ClickHouse behind it for the aggregates.

None of this makes ClickHouse a poor choice for what it was built for: scanning large volumes of immutable or append-mostly facts, filtered by a few columns, aggregated over a few more. When your queries look like that, you want all three properties at once, and the sections above told you why.

Exercises

Work these on the events table from part one, with clickhouse local --path db.

1. Predict, then measure. Without running anything, estimate the compressed bytes read by SELECT avg(duration_ms) FROM events WHERE event_type = 'view' AND country = 'DE'. Use the column sizes from system.parts_columns (duration_ms 28.49 MiB, country 9.12 MiB, event_type 0.045 MiB) and the granule count for event_type = 'view'. Then check with EXPLAIN indexes = 1 and --print-profile-events.
2. Choose the key. The query is WHERE country = 'TR' AND event_time in a 6-hour window. Compare events (ORDER BY (event_type, event_time)) with a copy ordered by (country, event_time). How many granules does each select, and what is the ratio? Why is the ratio so much smaller than the 87x from part two?
3. Explain a surprise. A bloom filter on user_id selects 22 of 1,221 granules, yet the query reads 13.2 MiB, while the equation’s naive estimate for those columns is 44.6 MiB × 22 / 1221 = 0.8 MiB. Where did the rest go?
4. Port a Postgres statement. UPDATE users SET plan = 'pro' WHERE id = 424; runs a few hundred times a day in your application. Describe the ClickHouse table (engine, key, column types) and the read query that returns the current plan, and say which of the two costs, write or read, you have increased.
5. Part pressure. A thousand devices each send one single-row insert per second into a table partitioned by day. With no batching, how long until parts_to_delay_insert is reached if no merge ever completes, and what would you change first?

Answers

1. The key prefix event_type = 'view' selects one of eight runs: 153 of 1,221 granules (12.5%). Columns touched: duration_ms, country and event_type. Prediction: (28.49 + 9.12 + 0.045) × 153 / 1221 = 4.7 MiB. Measured: 153 / 1221 granules, 5.02 MiB read, about 6% above the prediction, which is the compression-block overshoot. country is not in the key, so it does not reduce the granule count; it is read for the selected granules and filtered. A predicate on a non-key column does not reduce the granule count; it only filters rows inside the granules already chosen.

2. events selects 122 of 1,221 granules (the “generic exclusion” case from part two, country is not in its key at all so only event_time prunes: 6.07 MiB). The copy with ORDER BY (country, event_time) selects 12 of 1,221 (0.39 MiB). The ratio is about 10x, not 87x, because events still had the eight-run time structure to help: a leading column that is absent from the filter prunes by the partial mechanism, a tenth of the table, instead of nothing. The 87x was against layouts where the leading column was high-cardinality and the filter could not use the index at all.

3. The skipping index is a file read in full. The bloom filter index is 11.9 MiB, and its use is what makes the 22 granules known; 11.9 MiB plus roughly 1.3 MiB for the data is the 13.2 MiB measured, and the 1.3 against the naive 0.8 is block overshoot. Skipping indexes are consulted, and so read, for every granule before the data is. The net result was still a 3.4x reduction against the 44.6 MiB full scan, but the index’s own size is a cost to include in the equation, along with block overshoot for scattered granules.

4. ReplacingMergeTree(updated_at) ORDER BY id with columns id UInt64, plan LowCardinality(String), updated_at DateTime. The application inserts a new row for each change (batched, not one by one if it can help it). The read is either SELECT id, argMax(plan, updated_at) FROM users WHERE id = 424 GROUP BY id or SELECT ... FROM users FINAL WHERE id = 424. The write cost is now an ordinary insert (a new part, batched), no rewrite. The read cost is increased: until merges collapse the versions, a lookup must resolve duplicates. If there are only a few hundred changes a day, that is negligible; if the same key changed millions of times between merges, it would not be.

5. One thousand inserts per second create 1,000 parts per second, all in the same day’s partition, so parts_to_delay_insert (1,000) is reached in about one second if no merge completes, and parts_to_throw_insert (3,000) in about three. Real merges would hold the number down, but they would be rewriting most of the data several times to do it. The first change is to stop inserting from each device directly: put a collector in front that accumulates a second or a few seconds of rows and writes one insert, or enable async_insert so the server does the batching. With 1,000 rows per insert, you have one part per second instead of a thousand.

Module 2 (Indexing) takes the sparse index and granules in detail. The layout you now know is the thing it indexes.