Ordering keys, projections, and skip indexes can all reduce reads in ClickHouse. They are not interchangeable. I choose between them by asking which access pattern deserves a physical copy of the data.

The decision is not “which feature is fastest?” It is “which recurring query is important enough to pay for on every insert and merge?”

Dominant path: ordering key

The main ORDER BY is the strongest optimization. It controls how rows are laid out inside parts and which ranges the sparse primary index can exclude.

Put stable, frequently supplied equality filters first. Follow them with dimensions used for range scans or ordered reads. For an event table,(asset_id, event_time, event_id) works well when most requests identify an asset, then page through time.

Changing the ordering key is expensive and affects every query, so it should represent the dominant access path rather than every possible filter.

Second access path: projection

Suppose the same table also needs frequent lookups by user. A projection can maintain another ordering:

ALTER TABLE events ADD PROJECTION by_user
(
    SELECT *
    ORDER BY (user_id, event_time, event_id)
);

This is effectively another physical representation. It can be the right answer when the alternate query is important and selective enough to justify extra storage, insert work, and merge work.

Planner note. Always confirm that the planner actually selects it. Settings such as read-in-order optimization can make the base table look cheaper for anORDER BY ... LIMIT query even when a projection prunes more data.

Secondary block pruning: skip indexes

Skip indexes summarize blocks. They help only when the summary can rule out many blocks.

ALTER TABLE events
    ADD INDEX user_bf user_id
        TYPE bloom_filter(0.025) GRANULARITY 8,
    ADD INDEX amount_mm amount
        TYPE minmax GRANULARITY 8;

A Bloom filter suits high-cardinality equality or membership checks, such as a user identifier not present in the ordering key. A min-max index suits numeric ranges when values have some locality inside each block. If values are uniformly random, most blocks overlap the requested range and the index skips little.

Granularity is a trade-off. Finer summaries may skip more precisely but cost more storage, CPU, and memory during writes. I start coarse, measure selected granules, then tighten only when the data distribution proves it useful.

Partitions solve other problems

Partitions are best used for lifecycle operations: dropping a month, moving old data, or isolating a bounded backfill. High-cardinality partition keys create too many parts and make merges worse. They are not a substitute for a good ordering key.

Decision order

  1. Make the dominant path work with the main ordering key.
  2. Add a projection only for an important, distinct access pattern.
  3. Add a skip index when block-level summaries match the data distribution.
  4. Align partitions with retention or maintenance boundaries.

Rule of thumb

The fastest design is usually the one with the fewest physical structures that clearly correspond to real query shapes.