Question 1

What does ClickHouse performance tuning cover?

Accepted Answer

MergeTree compaction tuning (parts merging strategy, background_pool_size), projection design (when projections beat materialized views for read acceleration), query optimization (PREWHERE placement, JOIN reordering, SETTINGS hints), shard-key audit for skew correction, replication lag remediation (ZooKeeper / Keeper tuning), memory limits + max_concurrent_queries balance. Deliverables are written tuning recommendations with before/after benchmarks.

Question 2

Slow queries — where do you start?

Accepted Answer

system.query_log analysis to identify dominant slow patterns by digest, then EXPLAIN PIPELINE + EXPLAIN ESTIMATE on representative queries. Common findings: missing PREWHERE on selective filters (5-50x speedups), suboptimal JOIN order on multi-table queries, sort key not aligned with query filter columns, materialized view that should be a projection, or compaction backlog on ReplacingMergeTree. We've never seen a slow-ClickHouse audit without at least 3 of these issues.

Question 3

Compaction backlog — how to fix it?

Accepted Answer

First diagnose: system.parts tells you parts-per-partition; system.merges shows active merges. Backlog usually means background_pool_size is too small for ingestion rate, or max_parts_in_total has been hit forcing inserts to throttle. Fix: increase background_pool_size carefully (more merges = more I/O), tune merge_max_block_size, consider FINAL modifier or OPTIMIZE FINAL during low-traffic windows. For ReplacingMergeTree-heavy workloads, sometimes CollapsingMergeTree is the better choice.

Question 4

When do projections beat materialized views?

Accepted Answer

Projections: same-table read acceleration, automatic query rewrite by optimizer, share base-table partition lifecycle. Materialized views: fan-out to differently-shaped target tables, can target different MergeTree variants, don't slow down base SELECTs. Use projections for ad-hoc query acceleration on existing tables. Use MVs for downsampling, aggregation roll-ups feeding dashboards, or cross-shard fanout patterns. We audit query patterns to map each slow query to the right tool.

Question 5

ClickHouse Cloud auto-scaling vs self-managed sizing?

Accepted Answer

ClickHouse Cloud auto-scales compute per workload, billing per-second — easier to tune. Self-managed needs explicit sizing: parts-per-second ingestion rate, working-set RAM requirements, CPU cores per shard, replica count. We model both against your workload before recommending sizing strategy. For the broader engine-decision phase, see ClickHouse consulting.

Question 6

Does performance tuning include code changes?

Accepted Answer

Yes — query rewriting is often the highest-leverage intervention. We rewrite queries with proper PREWHERE, JOIN order, materialized-view-aware filtering, and SETTINGS hints. Schema changes (sort keys, partition keys, index types) require coordination with engineering. Configuration changes (memory limits, pool sizes, compaction settings) we apply to staging first, validate, then production.

ClickHouse Performance Tuning

What our ClickHouse perf tuning covers

ClickHouse perf tuning — common questions

Ready to fix ClickHouse performance?