How do you design partition keys and ordering keys for optimal ClickHouse query performance?

Think of organizing a massive file archive. Partitioning is like choosing which room to put files in (by year/month), so you can lock old rooms and throw away entire rooms when data expires. Ordering is like choosing how to sort files within each room (by department, then by date) so that when someone asks for all marketing files from June, you know exactly which shelf to check without scanning the entire room.

Partition keys in ClickHouse determine how data is physically split into independent subdirectories on disk. Each partition can be independently dropped, moved to cold storage, or expired via TTL. The ideal partition key creates 20-100 partitions over the table's lifetime (not thousands). Partitioning by toYYYYMM(date_column) creates monthly partitions, which is the most common choice. Too many partitions (e.g., partitioning by day on a multi-year dataset) creates thousands of small partitions that slow down metadata operations and INSERT performance.

Internally, the ORDER BY clause determines how rows are sorted within each part and directly controls the sparse primary index. ClickHouse creates one index entry per granule (8192 rows), storing the ORDER BY key values at granule boundaries. When a query filters by columns in the ORDER BY key, ClickHouse uses binary search on the sparse index to identify relevant granules, skipping the rest. The rule of thumb: place the lowest-cardinality column first (e.g., status with 5 values) then medium cardinality (e.g., service_name with 100 values) then highest cardinality (e.g., user_id or timestamp) last. This maximizes the probability of entire granules being skipped.

At production scale, the PRIMARY KEY is a prefix of the ORDER BY clause and determines what goes into the sparse index. Usually PRIMARY KEY equals ORDER BY, but you can define a shorter PRIMARY KEY to reduce index size while keeping the full ORDER BY for storage ordering. For example, ORDER BY (service, user_id, timestamp) with PRIMARY KEY (service, user_id) means the sparse index contains only service and user_id, reducing memory usage while still keeping data sorted by all three columns on disk.

The non-obvious gotcha is that the ORDER BY key also affects compression ratios. Columns that appear early in the ORDER BY key will have long runs of identical or similar values (because they are sorted first), compressing much better. If you order by (random_uuid, timestamp), the UUID column will have unique values in every row with no compression benefit, and the timestamp column will be randomly distributed rather than monotonically increasing. Always test different key orderings with real data and compare both query latency AND compression ratios using system.columns.