ClickHouse performance tuning starts with a simple question: how many rows and bytes does the query read before it returns the result? For MergeTree tables, ORDER BY and the sorting key are usually the first places to look because they control physical row order and determine how effectively ClickHouse can skip data.
Raff Technologies recommends fixing the read path before adding compute. If an analytical query returns a small result but scans a large share of the table, increasing CPU or memory may reduce latency temporarily without fixing the underlying query-design problem.
For most production workloads, use this sequence:
- check rows and bytes read;
- verify that filters align with the sorting key;
- review selected columns and
PREWHERE;
- inspect aggregation, join, and memory pressure;
- test realistic concurrency;
- resize infrastructure only after the query path is understood.
This guide covers ClickHouse query optimization across ORDER BY, primary and sorting keys, filters, memory, joins, and concurrency. For lifecycle design, see ClickHouse Partitioning & TTL. For repeated aggregations, see ClickHouse Materialized Views. For infrastructure ownership, see Managed ClickHouse vs Self-Hosted ClickHouse.
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:
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:
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 BY and 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.
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.
