Measuring correctly

Every measurement this workshop quotes is read from a number ClickHouse itself reports -- read_rows from system.query_log, or data_compressed_bytes from system.parts -- never from a stopwatch on the console. This page is why, told partly through mistakes made while building the workshop, because a reader who measures the same way will reach the same wrong conclusions and should be able to recognise them when they do.

Why the number to trust is read_rows, not latency

Latency is the number a reader instinctively reaches for, because it is the number the console shows without being asked. It is also the wrong number to judge a schema-design change on, for four reasons that all point the same direction:

• It is not deterministic. The identical query against the identical table can take a different number of milliseconds on two consecutive runs -- CPU scheduling, page cache state, and background merges all move the wall clock without the query having changed at all. read_rows for the same query against the same data does not move.
• It is not immune to the network. Every console run includes a real round trip between your browser and the service -- typically tens to hundreds of milliseconds -- layered on top of whatever the query itself takes. A round trip that size can be larger than the entire gap a module is asking you to find.
• It is not cache-independent. ClickHouse's query condition cache can make a query against a badly-designed table answer fast on a second run, for reasons that have nothing to do with the table's design -- see below.
• It measures the symptom, not the mechanism. Latency tells you a query got faster. read_rows tells you why: fewer granules had to be touched to answer it, which is the actual lesson each module is teaching about sort keys and materialized views.

The numbers from this workshop's own reference measurements make the gap between the two concrete. Module 05's naive-versus-tuned latency ratio is 66 ms against 4 ms (c2.naive.duration_ms / c2.tuned.duration_ms) -- about 16.5x. Its read_rows ratio over the identical two queries is 3,959,712 against 16,384 (c2.naive.read_rows / c2.tuned.read_rows) -- 241.7x (c2.ratio). Module 06's latency ratio is 52 ms against 14 ms (c3.raw.duration_ms / c3.mv.duration_ms) -- about 3.7x. Its read_rows ratio is 4,295,584 against 16,384 (c3.raw.read_rows / c3.mv.read_rows) -- 262.2x (c3.ratio). Had either module quoted latency as its headline number, module 06's would have been "52 ms versus 14 ms" -- a difference small enough to read as noise, for a materialized view that is genuinely doing 262 times less work.

The console wall-clock trap

Open a module's contract query in the SQL console, run it twice, and the two run times will differ from each other -- not because the query did different work, but because each run's total includes a network round trip of tens to hundreds of milliseconds, on top of a query that may only take single-digit milliseconds either way. That round trip is frequently larger than the entire gap a module asks you to close. Two schemas whose read_rows differ by two orders of magnitude can look indistinguishable on the console's stopwatch, and two runs of the identical query against the identical schema can look meaningfully different from each other purely from jitter. The console's clock cannot settle any of this; it was never measuring the right thing.

The process-startup trap, and what actually happened here

A local, single-process measurement carries a cost the console does not: process startup. clickhouse local has to start a whole process, load its configuration, and tear back down for every invocation -- a fixed cost paid once per run, on top of whatever the query inside it takes.

While this workshop was being built, that fixed cost produced two wrong conclusions in a row from an otherwise correct measurement setup. A first pass measured the materialized-view dashboard as slower than the raw scan it was supposed to beat -- 105 ms against 57 ms -- and separately reported the optimised (tuned) table as slower than the naive one it was supposed to beat too. Both inversions were clickhouse local starting a fresh process for each side of the comparison, not the queries themselves: the process-startup cost dominated both measurements and buried the actual query time underneath it. Measured in-process and warm instead -- one long-lived session, both queries run inside it, first run discarded -- the ordering flips back to the correct one, matching the published c3.mv.duration_ms (14 ms) against c3.raw.duration_ms (52 ms) figures used throughout this workshop.

This is published deliberately, not smoothed over: a participant who measures a materialized view by shelling out to clickhouse local once per query, or who compares two schemas across two separate connections, is set up to reach the exact same wrong conclusion -- that the optimisation made things worse -- for the exact same reason. Recognise the shape (a fixed per-run cost large enough to swamp the difference you are trying to measure) rather than trusting the first number that comes back.

The query condition cache

ClickHouse remembers, per granule, whether a previous WHERE condition already failed to match there, and reuses that answer on a later query carrying the exact same literal predicate. That is a legitimate optimization, and it is also exactly the wrong thing to have enabled while measuring a naive baseline: it rewards asking the identical question over and over, which is not the shape either module models -- module 05 is a per-user lookup where the literal user_pseudo_id differs on every real request, and module 06 is a live dashboard whose time window slides forward on every real request.

Measured directly against this dataset: the first run of module 05's contract query against the naive layout read 3,959,712 rows (c2.naive.read_rows) -- a real, unpruned scan. Simply repeating that identical query -- testing it, in the way anyone naturally would -- then read as few as 16,384 rows, once the condition cache had warmed on that exact predicate. That is the same magnitude a genuinely well-sorted table achieves by design, produced instead by asking a badly-sorted table the same question four times in a row. Every timed measurement quoted anywhere in this workshop was taken with the cache explicitly disabled for exactly this reason:

SETTINGS use_query_condition_cache = 0

Add it to every run you time yourself. Without it, a naive baseline you have already queried once during your own testing will look artificially efficient, and the gap you are trying to demonstrate will look smaller than it really is.

Measuring before merging

A single bulk INSERT lands as several parts, each sorted independently, and a specific row can therefore sit in every one of those parts until a background merge combines them -- so a query touches one granule per part instead of one granule total, even against a perfectly chosen sort key. Measured directly on module 05's tuned table: 57,344 rows across 5 scattered granules right after loading, and 16,384 across 2 contiguous granules a few minutes later, once background merges caught up on their own. That gap understates a genuinely good sort key by more than 3x -- not because the design is wrong, but because the table had not yet settled into the steady state it reaches minutes after a real load finishes. Give a freshly-loaded table a few minutes, then re-run the measurement, before you trust a read_rows number -- the same way this workshop's own reference figures were measured.

Compression figures do not transfer between platforms

Everything above concerns latency and read_rows, which are comparatively stable across environments. Compressed bytes are not, and this was proven directly while building this workshop, not assumed: the identical rows and the identical schema measured 1,012,473,751 bytes for the naive table on local, open-source clickhouse local, against 348,759,309 bytes for the same table measured on a ClickHouse Cloud service -- 2.9x larger locally for no difference in the data at all (c1.naive.compressed_bytes). The knock-on effect on the resulting ratio is worse than the raw byte counts suggest: module 04's final tuned table (bq.events_tuned) compresses 2.463x on Cloud (c1.ratio) against 6.26x measured locally on the identical full export -- open-source ClickHouse and ClickHouse Cloud simply do not compress this schema alike.

If you or a participant re-measures anything in this workshop that touches compressed bytes, do it on Cloud. A local number here is not a conservative estimate of the Cloud figure -- it is a different, larger number, and publishing it in place of the Cloud measurement would promise a reduction nobody running the workshop for real could reproduce.

How to measure properly

• Assign every timed query an explicit query_id before you run it.
• Read read_rows, read_bytes and query_duration_ms back from system.query_log WHERE type = 'QueryFinish' AND query_id = ..., never from the console's own display.
• Run every comparison at least twice, and discard the first run -- the same "cold first run" discipline this workshop's own reference figures were measured with (median of three runs after discarding a cold first one).
• Take the median across the retained runs, not the best or the most recent.
• Run the measurement query (system.parts for compressed bytes, system.query_log for read_rows) in its own session, separate from the correctness queries. Chaining a bytes-measuring query immediately after two full-table correctness scans in one long-lived session has been observed to read a transiently inflated value; run alone, or read directly against system.parts, the number is stable.
• Add SETTINGS use_query_condition_cache = 0 to every timed run, and give a freshly-loaded candidate table a few minutes to let background merges catch up before measuring it.

None of this is bureaucracy for its own sake. Every rule above exists because skipping it produced a specific, documented wrong number while this workshop was being built -- that is what makes each one worth keeping.