05 The sort key

One column choice, made concrete with real DDL, that turns a lookup reading almost the whole table into one reading two granules -- measured server-side, not on the console's clock.

Outcome

In about 30 minutes you will answer the same single-user lookup module 01 opened with, and measure how few rows ClickHouse has to read to answer it. Same twenty rows out. Far fewer rows touched to get them, and you will build the exact table that gets you there.

The lookup, and why it needs a new table

The query this module makes fast returns, for one user_pseudo_id, the most recent twenty events first, with exactly these columns and types:

Column	Type
`event_time`	`DateTime64(6)`
`event_name`	`String`
`geo_country`	`String`

Neither table you already have can answer that directly. bq.events_naive has no event_time column at all -- it has event_timestamp, a raw Int64 of microseconds -- and no flat geo_country column either, only geo.country nested inside a tuple. bq.events_tuned, the table module 04 had you build, already has flat event_time, event_name and geo_country columns with exactly these names and types -- but its sort key, (event_date, event_name, user_pseudo_id), was chosen for module 04's compression story, not for this lookup.

Use the same probe user module 01 already used against BigQuery: 3272961.4196485002, 39 events in the full export, comfortably above the 25-event minimum that makes "most recent 20" a real filter instead of a full read of that user's history.

Step 1: Measure the baseline

Run the contract query against bq.events_tuned first, so you have a real "before" number, not an assumed one. Run it once, with the condition cache explicitly off, and read read_rows straight off the console's results bar:

SELECT event_time, event_name, geo_country
FROM bq.events_tuned
WHERE user_pseudo_id = '3272961.4196485002'
ORDER BY event_time DESC
LIMIT 20
SETTINGS use_query_condition_cache = 0;

Against bq.events_tuned, this query reads 3,959,712 rows -- effectively the whole table. user_pseudo_id is third in that table's sort key, (event_date, event_name, user_pseudo_id), and a MergeTree sort key only lets ClickHouse skip granules for an equality filter on its leading column, or a leading prefix held equal. Third contributes nothing on its own.

Step 2: Build a table sorted for this lookup

CREATE TABLE bq.events_by_user
(
  user_pseudo_id String        CODEC(ZSTD(1)),
  event_time     DateTime64(6) CODEC(Delta, ZSTD(1)),
  event_name     LowCardinality(String),
  geo_country    LowCardinality(String)
)
ENGINE = MergeTree
ORDER BY (user_pseudo_id, event_time);

INSERT INTO bq.events_by_user
SELECT user_pseudo_id, event_time, event_name, geo_country FROM bq.events_tuned;

user_pseudo_id leads the sort key, so every granule ClickHouse's sparse primary index can rule out for a given user, it does rule out: the table is physically sorted so that one user's rows sit together rather than scattered across the whole table in insertion order. event_time comes second so that, within one user's rows, they are already sorted the way the contract's ORDER BY event_time DESC LIMIT 20 wants them, avoiding a separate in-memory sort over that user's full history.

Two things will make your own re-measurement lie to you

The query condition cache. ClickHouse remembers, per granule, whether a previous WHERE already matched there, and reuses that answer for a later query with the exact same literal predicate. That rewards asking the exact same question over and over, which is not this lookup's real shape: a profile page filters on whatever user just loaded it, not the same user_pseudo_id four times in a row. Keep SETTINGS use_query_condition_cache = 0 on every timed run.

Un-merged parts after a bulk load. A single bulk INSERT lands as several parts, each sorted independently, so the same user's rows sit in every part until a background merge catches up: one granule read per part instead of one granule total, even with a perfectly chosen sort key. Measured here: a table read 57,344 rows across 5 scattered granules right after loading bq.events_by_user, and 16,384 rows across 2 contiguous granules a few minutes later, once background merges caught up on their own. Give the table a few minutes after loading, then re-run the contract query -- trust that later number.

Step 3: Confirm the win

Re-run the same contract query, this time against bq.events_by_user:

SELECT event_time, event_name, geo_country
FROM bq.events_by_user
WHERE user_pseudo_id = '3272961.4196485002'
ORDER BY event_time DESC
LIMIT 20
SETTINGS use_query_condition_cache = 0;

Read read_rows the same way you did in Step 1 -- straight off the console's results bar:

3,959,712 rowsbq.events_tuned, before

16,384 rowsbq.events_by_user, after

241.7xfewer rows read

The latency gap between the two is only about 16x (66 ms against 4 ms) -- real, but a fraction of the story, and it is the number that would have made the headline if latency had been the metric instead of read_rows.

Before trusting that number, look at why with EXPLAIN indexes = 1 in front of the query:

EXPLAIN indexes = 1
SELECT event_time, event_name, geo_country
FROM bq.events_by_user
WHERE user_pseudo_id = '3272961.4196485002'
ORDER BY event_time DESC
LIMIT 20;

The line to read is Granules under the PrimaryKey step. Run this against bq.events_tuned and that line reports a granule count at or near the table's total -- the primary index found nothing it could rule out, because user_pseudo_id sits third in that sort key. Run it against bq.events_by_user and the same line reports a small number selected out of the same total. That before/after comparison, not a stopwatch, is how you confirm a sort-key change actually changed anything.

Why 16,384, not 20

ClickHouse's primary index is sparse -- one entry per granule (8,192 rows by default), not one per row. It can tell you which granules might hold a match; it was never built to say which rows inside a granule do. So no sort key, however well chosen, can read fewer rows than one full granule's worth for any match at all.

bq.events_by_user's 16,384 rows is exactly two granules (16,384 / 8,192 = 2), not one. That is not a looser result than it could have been -- it is the realistic floor for this kind of lookup. An equality lookup's target value sits somewhere inside the range one index mark covers, and that range's boundary rarely lines up exactly with where the matching rows start, so a sparse index commonly has to bracket a match between two adjacent marks rather than landing cleanly inside one. Twenty rows out of 16,384 read is still a completely different world from twenty rows out of 3,959,712. It is just not the same thing as reading twenty.

Verify the rebuild didn't change the data

Sorting the same columns into a new table should never change what they say -- only how fast they answer. bq.events_by_user is a straight column copy from bq.events_tuned, with no filter and no computed value, so the only way it could go wrong is a dropped or duplicated row. Confirm the count matches the source:

SELECT count() FROM bq.events_by_user;

Expect 4,295,584, the same as bq.events_naive and bq.events_tuned.

Done when

bq.events_by_user returns the right shape, its row count matches the source, and you can point at the EXPLAIN indexes = 1 output that explains why read_rows dropped from 3,959,712 to 16,384. Continue to 06 The impossible dashboard when you are ready.