Choose the ordering key

In ClickHouse, ORDER BY defines how rows are physically sorted inside each part. The sparse primary index uses that order to skip whole blocks of rows, called granules, that cannot match your filter. A granule is 8192 rows by default.

This is not the same thing as a PostgreSQL primary key. It does not enforce uniqueness. Two rows with the same key are fine. It’s a physical layout decision, and it decides which queries can skip data and which ones scan everything.

Put the right columns first

My rule: put frequently filtered, lower-cardinality dimensions first, then time. service has four or five values and shows up in almost every WHERE. ts comes next, because dashboards always ask for a range. A high-cardinality column like user_id in first position is the classic mistake. Rows for one service end up scattered everywhere, and a filter on service can’t skip anything.

Compare ordering by ts alone with ordering by (service, ts). The first skips well when you filter by time and nothing else. The second skips well when you filter by service, or by service and time, which is what most dashboards do. Write down the queries your table will serve, then pick the order that lets the common ones skip.

Measure it

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.

Ten million rows is plenty. This fills both tables with one event per second, cycling through four services:

INSERT INTO by_time
SELECT ['api', 'checkout', 'auth', 'search'][(number % 4) + 1],
  toDateTime('2026-08-01 00:00:00') + number
FROM numbers(10000000);

INSERT INTO by_service SELECT * FROM by_time;

Now run the same query against both tables:

SELECT count() FROM by_time
WHERE service = 'checkout'
  AND ts BETWEEN '2026-08-10 00:00:00' AND '2026-08-11 00:00:00';

Read the summary clickhouse-client prints after each query. It looks like Processed 90.11 thousand rows, 450.56 KB. Against by_time, the index narrows the scan to one day, but all four services are mixed inside that day, so it reads every row in it. Against by_service, checkout’s rows sit together, so it reads only checkout’s slice of that day. Roughly a quarter of the rows and bytes.

Now drop the time condition and filter on service alone. by_time reads all ten million rows. by_service reads about a quarter. That’s the difference between a dashboard that answers in milliseconds and one that scans the whole table on every refresh.

Be careful with the opposite failure too. An ordering key with many columns makes the index bigger, and columns past the first three or four rarely help skipping. Keep it short and keep it aligned with your filters.