Worksheet 2: Sort key design (ORDER BY)

Time estimate: 20–25 minutes Reference: MergeTree engines — ORDER BY Design section

Concept

ClickHouse's ORDER BY clause is not decorative. It defines the primary index — a sparse, block-level index that lets ClickHouse skip irrelevant blocks of data when evaluating WHERE clauses. It also determines the physical sort order of data within parts, which affects compression.

The wrong ORDER BY = slow queries + wasted storage. A UUID-first ORDER BY means no block skipping for any analytical query (UUIDs are random; there is no sortable prefix). A date-first ORDER BY means queries filtering on date skip most of the table.

Three rules for ORDER BY design

Rule 1: Derive from query filters, not from the source schema. Look at the WHERE, GROUP BY, and JOIN columns across your most frequent queries. The most-filtered columns should probably appear in ORDER BY (provided it is not too high cardinality). The source table's primary key (if any) is usually irrelevant.

Rule 2: Low cardinality first, high cardinality last. ClickHouse's primary index has one entry per ~8192 rows (a granule). Low-cardinality columns (e.g., toStartOfMonth(date) = ~48 distinct values over 4 years) cluster many rows together — the index can skip entire granules. High-cardinality columns (e.g., trip_id = 50M distinct values) are unique per row — putting them first means the index cannot skip anything. This ordering is a default, not an override of Rule 1: a column that a query filters by range, eliminating most rows, can still earn the lead over a lower-cardinality column that's only ever filtered by equality.

Rule 3: For ReplacingMergeTree, end with the unique row identifier. The deduplication key is the full ORDER BY tuple. If trip_id is missing from ORDER BY, two different trips with the same pickup_at and no further columns would be treated as duplicates. Put trip_id last to ensure uniqueness without hurting index performance.

Exercise: query workload analysis

Before designing sort keys, work out what columns the queries actually filter on. The NYC Taxi lab has 7 representative queries; for each, pick the filter column that eliminates the most rows.

Exercise: cardinality estimation

For each candidate ORDER BY column, estimate its cardinality over the 4-year, 50M-row dataset. Most of the table below is reference data — the two open cells are pickup_at's own estimated distinct values and cardinality class. Four years is about 126 million seconds (and only about 2.1 million minutes), the producer timestamps every trip from the wall clock, and PICKUP_AT is stored as DateTime64(3, 'UTC') — work out how many of those slots 50 million trips can occupy before you pick a bracket.

Exercise: sort key design

Using your query workload analysis and cardinality estimates, propose an ORDER BY for trips_raw, fact_trips, and agg_hourly_zone_trips. Reminders:

• Low cardinality first → most block-skipping.
• Columns that appear in the WHERE/GROUP BY of multiple queries → include them.
• For ReplacingMergeTree tables → end with the unique row identifier.
• Don't include columns that are never filtered on.

Then work the reasoning and reflection questions once every table is filled in.

Loading worksheet...

Transfer to migration-plan.md

Once you have filled in this worksheet, copy your ORDER BY decisions to Section 4 of migration-plan.md and check off:

- [ ] Sort key design: completed