ClickHouse Certified: Performance Tuning and Optimization

This domain tests whether you can find where a query spends time and fix the cause rather than raise a limit. Questions give a symptom - huge read_rows, memory spikes, slow joins, too many parts - and ask for the highest-leverage change and the evidence that it worked.

The read path

Partition pruning → primary index → skip indexes / projections → decompression → filtering. Verify each stage with EXPLAIN indexes = 1, and confirm real numbers in system.query_log (read_rows, read_bytes, memory_usage, ProfileEvents).

Design levers

• Sorting key first: put the most selective equality column first, then time.
• Projections store an alternative sort order and are chosen automatically by the optimizer - the standard fix for a second query shape.
• Skip indexes (minmax, set, bloom_filter, tokenbf_vs_ngrambf) only help when values are physically clustered; on a random high-cardinality column they add overhead and prune nothing.
• LowCardinality, narrow integer types, and per-column codecs reduce bytes read, which is the dominant cost.

Query levers

• max_threads controls parallelism; max_memory_usage caps a query, and max_bytes_before_external_group_by / max_bytes_before_external_sort spill to disk.
• optimize_aggregation_in_order = 1 helps when GROUP BY matches a sorting-key prefix.
• uniq() instead of uniqExact() for high-cardinality distinct counts.
• Joins: smaller table on the right; join_algorithm = 'grace_hash' for large-large joins, 'partial_merge' when memory is tight.
• Cheaper than FINAL: argMax(col, ver) ... GROUP BY key.

Worked example

A dashboard query reads 2e9 rows to return 500, with 30 GB peak memory. system.query_log shows read_rows = 2e9; the table is ORDER BY (ts, tenant_id) but the query filters on tenant_id and groups by tenant_id. Diagnosis: no index pruning on the second key column, and the aggregation cannot run in sort order. Fix: add a projection ordered by (tenant_id, ts). EXPLAIN indexes = 1 then shows the projection selected and read_rows drops to the tenant's range.

Operational performance

• Watch active parts per partition in system.parts and in-flight work in system.merges; OPTIMIZE TABLE ... FINAL is a heavy manual tool, not routine maintenance.
• Mutations rewrite whole parts and compete with merges for I/O.
• max_concurrent_queries bounds server-side concurrency; per-query limits belong in settings profiles.

Traps that cost marks

• Raising max_threads to cure a memory problem - it usually worsens it.
• Adding a skip index to a random UUID column.
• Confusing the query's ORDER BY with the table's physical order.
• Forgetting that ALTER ... MODIFY COLUMN to LowCardinality rewrites data.