ClickHouse query performance is shaped primarily by how much data a query must read and how much work it must perform after reading it. For MergeTree tables, ORDER BY is one of the most important physical-design choices because it determines row order on disk and enables sparse-index data skipping.
For production analytics, the useful sequence is reduce rows and bytes read first, then optimize aggregation, memory, concurrency, and hardware. Scaling compute before fixing a poor read path often makes an inefficient design merely more expensive.
This guide owns ClickHouse query-performance decisions across ORDER BY, primary keys, filters, memory, and query workload. For partitions and retention, use ClickHouse Partitioning & TTL. For pre-aggregation, use ClickHouse Materialized Views.
ORDER BY is physical storage design in ClickHouse
In MergeTree-family tables, ORDER BY determines how rows are sorted on disk.
That ordering lets ClickHouse maintain a sparse primary index over granules and skip ranges that cannot satisfy a filter.
This is different from an OLTP database where a primary key often means uniqueness and row identity.
| ClickHouse concept | Main role |
|---|---|
ORDER BY | physical row sorting and data locality |
PRIMARY KEY | sparse indexing expression, often same as ORDER BY prefix |
PARTITION BY | coarse lifecycle boundary |
| skipping indexes | additional pruning for selected patterns |
The sorting key should follow the dominant query filters, not the application's entity identifier by default.
Put frequent selective filters early in the sorting key
Suppose dashboards usually query:
account_id = ? AND event_time BETWEEN ? AND ?
A table ordered by:
ORDER BY (account_id, event_time)
can keep rows for an account close together and use time as the next pruning dimension.
An ordering of only:
ORDER BY event_time
may be better for global time-range scans but less efficient for tenant-specific dashboards.
The correct key comes from the actual workload.
Collect the most important queries and record:
- equality filters;
- range filters;
- grouping dimensions;
- sort requirements;
- query frequency;
- expected time range.
Then design the ordering to reduce the data read by the highest-value workload.
Low-cardinality-first is a useful heuristic, not a universal rule
ClickHouse guidance often recommends considering lower-cardinality columns earlier when they are frequently filtered, followed by more selective dimensions and time.
But no universal column order replaces testing.
For multi-tenant analytics, an account/tenant dimension can be valuable early even when cardinality is high if nearly every customer-facing query filters by it.
The test is whether the key improves pruning for the workload.
Measure:
- rows read;
- bytes read;
- query duration;
- mark/granule pruning;
- compression and storage impact.
A key that looks elegant on paper but reads most of the table is not effective.
Primary key does not need to enforce uniqueness
ClickHouse primary keys are sparse indexes and do not provide uniqueness enforcement like a conventional transactional primary key.
Duplicate rows are possible unless the table engine and ingestion model handle them deliberately.
This means a source event ID should not automatically become the ClickHouse primary/sorting key just because it is unique.
A random/high-cardinality ID often provides poor locality for time- or account-oriented analytical queries.
Keep business uniqueness and ClickHouse physical ordering as separate design questions.
Measure rows and bytes read before tuning CPU
Query latency should be interpreted alongside scan volume.
A useful diagnosis table is:
| Symptom | Likely direction |
|---|---|
| High rows read, few rows returned | filtering/order-key problem |
| Low rows read, high CPU | expensive aggregation/expression/join |
| High memory, GROUP BY | cardinality or aggregation strategy |
| Good single query, poor concurrent latency | concurrency/resource contention |
| Query slowed after data growth | pruning/working-set issue |
| Time range reads too much | order key / partition / predicate issue |
If a query returns 500 rows but reads 500 million, adding vCPU should not be the first response.
Reduce unnecessary reads first.
PREWHERE can reduce I/O for selective filters
ClickHouse can use PREWHERE to read filtering columns first and avoid reading other columns for rows that will be discarded.
The optimizer can apply PREWHERE automatically in many cases, but query design still matters.
Avoid selecting wide unused columns:
SELECT *
when a dashboard needs only a few fields.
Columnar storage is most efficient when the query reads only the required columns.
A narrow projection plus selective filters can reduce both I/O and decompression work substantially.
Data types affect memory and scan efficiency
Choose the narrowest correct types.
Examples include:
- avoid storing numbers as String;
- use appropriate integer widths;
- use Date/DateTime types for time;
- consider LowCardinality for suitable repeated string dimensions;
- avoid unnecessary Nullable use when null semantics are not required.
Data type decisions affect:
- compressed size;
- memory during grouping/joining;
- comparison cost;
- index effectiveness.
Do not change types solely for theoretical savings; validate compatibility and measured query behavior.
GROUP BY memory follows cardinality and concurrency
Aggregations build in-memory state according to grouping dimensions and aggregate functions.
A query grouping by a low-cardinality region is very different from grouping by millions of user IDs.
Estimate group cardinality before running large production aggregations.
Watch:
- peak query memory;
- number of groups;
- concurrent heavy queries;
- spill/external aggregation behavior;
- result-set size.
If the same high-cardinality aggregation runs constantly, a materialized view or pre-aggregated table may be more efficient than repeating it at query time.
Joins should be sized from the right-hand side and workload
ClickHouse supports several join algorithms and can adapt based on settings and memory.
For production designs, ask:
- how large is each side?
- can dimensions be denormalized at ingestion?
- is the joined table reused frequently?
- does a dictionary or precomputed representation fit?
- does join memory fit under concurrency?
Do not denormalize everything blindly, but remember that analytical systems often benefit from moving stable relationships into a query-friendly structure.
A complex join repeated for every dashboard request can become a better candidate for ingestion-time transformation.
Materialized views fit repeated expensive aggregations
When the same stable aggregation dominates query cost, pre-aggregation can reduce rows and CPU dramatically.
Use ClickHouse Materialized Views when:
- query pattern is stable;
- aggregate grain is known;
- source data can rebuild the result;
- added write/storage cost is acceptable.
Do not create a materialized view before fixing an obviously poor base-table ordering key.
The two optimizations solve different layers.
Query concurrency can be the bottleneck even when one query is fast
Benchmarking one query at a time can hide production contention.
Measure representative concurrency:
1 query 10 concurrent queries 50 concurrent queries
according to actual workload.
Watch how:
- p95 latency;
- memory;
- CPU;
- disk throughput;
- merge activity;
- query failures
change as concurrency rises.
A query that uses 8 GB of memory comfortably alone may become impossible when ten copies run together.
Capacity planning should use the production concurrency model, not only individual query speed.
Background merges compete with queries for resources
ClickHouse performs merges continuously for MergeTree tables.
High ingest, too many small parts, TTL work, or mutations can increase background CPU and disk activity.
If query latency worsens only during merge pressure, the fix may involve:
- larger insert batches;
- fewer parts;
- scheduling heavy mutations;
- storage headroom;
- capacity separation.
Use ClickHouse Monitoring to correlate foreground query cost with background work.
Benchmark with realistic data distribution
A query benchmark on 1% of production data can choose a completely different execution profile.
Use representative:
- table size;
- time range;
- tenant distribution;
- high/low-cardinality dimensions;
- hot and cold periods;
- concurrent load.
Record:
query pattern rows read bytes read elapsed time memory result rows concurrency
Then compare before and after schema or query changes.
This produces evidence that survives data growth better than an isolated millisecond result.
Managed ClickHouse reduces infrastructure tuning, not query design
Raff Managed ClickHouse reduces the work around database hosts, monitoring, backups, storage expansion, scaling workflows, and optional HA.
The data/application team still owns:
ORDER BYand primary-key design;- query filters;
- selected columns;
- joins;
- aggregation cardinality;
- materialized views;
- workload/concurrency behavior.
At Raff, the performance rule is: before resizing ClickHouse, identify how many rows and bytes the important query reads and whether the physical ordering is helping it skip data.
Query-performance checklist
- Top query patterns are ranked by business importance and frequency.
-
ORDER BYmatches dominant filters. - Primary key is treated as sparse-index design, not uniqueness enforcement.
- Rows/bytes read are measured.
- Queries select only required columns.
- Data types fit the workload.
- GROUP BY cardinality and memory are understood.
- Repeated joins are reviewed for denormalization/precomputation.
- Repeated aggregations are evaluated for materialized views.
- Tests include realistic concurrency.
- Merge/mutation pressure is correlated with query latency.
- Capacity increases follow measured resource constraints.
Conclusion
ClickHouse performance tuning begins with physical data access.
Design ORDER BY around the dominant analytical filters, measure rows and bytes read, then optimize aggregation, joins, memory, and concurrency. Partitioning, materialized views, and larger compute can all help, but they work best after the base table makes important queries read as little unnecessary data as possible.