Schema and MergeTree
Choose the ordering key
Design ORDER BY from frequent filters and cardinality because physical row order drives data skipping and compression.
9 minute lesson
In ClickHouse, ORDER BY defines how rows are sorted inside each part. The sparse primary index uses that order to skip granules.
Put frequently filtered, lower-cardinality dimensions early when they eliminate useful ranges, then time and other fields according to query patterns. This is not the same decision as a PostgreSQL uniqueness constraint.
Compare ordering events by timestamp alone with ordering by service, event type, and timestamp. List the queries each layout can skip efficiently.
Create two disposable tables with the same rows but different orderings:
create table by_time (service LowCardinality(String), ts DateTime)
engine = MergeTree order by ts;
create table by_service (service LowCardinality(String), ts DateTime)
engine = MergeTree order by (service, ts);Load enough data to span many granules. Filter one service over a time range and compare read rows and bytes. The result turns ORDER BY from a naming rule into a measured physical-design decision.
Lesson completed