Dashboard queries feel fast in dev and then scan gigabytes in production. The cause is rarely ClickHouse itself - it's key design. Get ORDER BY, the primary key, and partitions right, and the same queries drop from seconds to tens of milliseconds. The confusion most people hit is that in ClickHouse the primary key and the sorting key are two different things.

The mental model

ClickHouse writes immutable parts and keeps a sparse primary index over data sorted by the sorting key. The index points to granules - ranges of rows - not individual rows, so the engine jumps to the relevant ranges and skips the rest. Two keys do separate jobs:

• Sorting key (ORDER BY): the physical on-disk row order.
• Primary key: the sparse index used for pruning.

If you don't specify PRIMARY KEY, it defaults to the sorting key. If you specify both, the primary key must be a prefix of the sorting key. You narrow the primary key when you want a smaller in-memory index while keeping a richer sort order for compression and merges - useful when filters mostly hit the leading columns. ALTER TABLE … MODIFY ORDER BY changes the sorting-key metadata and leaves the primary key untouched; it's constrained, so test before refactoring in production.

Patterns and when to use them

The goal is to align the leftmost key columns with your most selective, most common filters, and keep the rest for locality and compression.

Use case	Design	Why it works
User-centric analytics (drill by user, then time)	PRIMARY KEY (user_id, event_time); ORDER BY (user_id, event_time, event_type)	Prunes by user, keeps time locality; the extra column helps compression and merges
Time-first dashboards (global time filters, optional narrow dims)	PRIMARY KEY (event_time); ORDER BY (event_time, dim_low_card, …)	Time slicing dominates; low-card dimensions add locality without bloating the index
Multi-tenant logs (isolate tenant, then time)	PRIMARY KEY (tenant_id, ts); ORDER BY (tenant_id, ts, level)	Tenant-first pruning prevents cross-tenant scans; sort keeps writes append-friendly
High-cardinality search on a few fields	PRIMARY KEY (ts); ORDER BY (ts, …) plus data-skipping indexes	When filters can't sit on the left, skipping indexes prune blocks instead

DDL you can adapt

-- Events table: user-first queries with time windows
CREATE TABLE app.events
(
  event_time   DateTime64(3, 'UTC'),
  user_id      UInt64,
  event_type   LowCardinality(String),
  url          String,
  status       UInt16
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(event_time)
PRIMARY KEY (user_id, event_time)
ORDER BY    (user_id, event_time, event_type);

-- Variant: time-first dashboards, lighter in-memory index
CREATE TABLE app.events_time_first
(
  event_time   DateTime64(3, 'UTC'),
  event_type   LowCardinality(String),
  user_id      UInt64,
  status       UInt16
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(event_time)
PRIMARY KEY (event_time)                 -- narrower PK = smaller sparse index
ORDER BY    (event_time, event_type, user_id);

Rows are physically ordered by the sorting key; pruning uses the primary key. An explicit PRIMARY KEY lets you narrow the index for memory efficiency. Use monthly partitions for lifecycle operations (drop, move, TTL), not as a query-speed lever.

Why is my query slow? A checklist

1. Leftmost mismatch. Common filters aren't on the left of the key - reorder the keys.
2. Over-wide primary key. You copied the whole ORDER BY into PRIMARY KEY - consider narrowing it.
3. Too many tiny parts. Fix the ingestion batch size; key design can't undo write amplification.
4. Hard-to-index filters. Add data-skipping indexes for columns the primary key doesn't cover.
5. Key refactor needed. Read the MODIFY ORDER BY constraints before changing sort order in production.

Two targeted examples

Narrow primary key for memory - a rich sort order for compression, a narrow index for pruning:

PRIMARY KEY (account_id)
ORDER BY    (account_id, toDate(event_time), event_time, action)

The table still sorts by all four columns, but the index only tracks (account_id) granules - often enough to prune.

Add a skip index when a filter doesn't fit the key:

ALTER TABLE app.events
ADD INDEX idx_status bloom_filter(status) GRANULARITY 4;

This lets ClickHouse skip blocks where the primary key can't help. Test it on real data - a low-cardinality bloom filter can cost more than it saves.

Common pitfalls

• Cargo-cult keys. Throwing every column into ORDER BY bloats files and rarely helps. Keep it focused.
• Primary key is not OLTP uniqueness. ClickHouse primary keys are for pruning, not row identity - they don't enforce uniqueness.
• Over-partitioning. Daily or hourly partitions cause part explosions. Prefer monthly unless you genuinely need finer lifecycle control.
• Unsafe refactors. MODIFY ORDER BY has real constraints - understand them and test on copies.

What to remember

The sorting key (ORDER BY) sets on-disk order; the primary key is the sparse index that prunes I/O. Defaults tie them together, and you can narrow the primary key to save memory. Put your most selective, most common filters leftmost. Use partitions for lifecycle, not speed, and add data-skipping indexes only when the primary key can't carry the query. To prove it: pull your slowest dashboard query, build a staging table with the narrow-PK / rich-ORDER BY pattern, reload a day of data, and compare scanned bytes.