Performance and Optimizations

This section contains tips and best practices for improving performance with ClickHouse. We recommend users read Core Concepts as a precursor to this section, which covers the main concepts required to improve performance.

Topic	Description
Query optimization guide	Start here for query optimization fundamentals, covering common scenarios and performance techniques to improve query execution speed.
Primary indexes advanced guide	Deep dive into ClickHouse’s unique sparse primary indexing system, how it differs from traditional databases, and best practices for optimal indexing strategies.
Query parallelism	Learn how ClickHouse parallelizes query execution using processing lanes and `max_threads` settings, including how to inspect and optimize parallel execution.
Partitioning key	Master partition key selection to dramatically improve query performance by enabling efficient data segment pruning and avoiding common partitioning pitfalls.
Data skipping indexes	Apply secondary indexes strategically to skip irrelevant data blocks and accelerate filtered queries on non-primary key columns.
`PREWHERE` optimization	Understand how `PREWHERE` automatically reduces I/O by filtering data before reading unnecessary columns, plus how to monitor its effectiveness.
Bulk inserts	Maximize ingestion throughput and reduce resource overhead by batching data insertions effectively.
Asynchronous inserts	Improve insert performance by leveraging server-side batching to reduce client-side complexity and increase throughput for high-frequency insertions.
Avoid mutations	Design append-only workflows that eliminate costly `UPDATE` and `DELETE` operations while maintaining data accuracy and performance.
Avoid nullable columns	Reduce storage overhead and improve query performance by using default values instead of nullable columns where possible.
Avoid `OPTIMIZE FINAL`	Understand when you should and shouldn’t use `OPTIMIZE TABLE FINAL`
Analyzer	Leverage ClickHouse’s analyzer to identify performance bottlenecks and optimize query execution plans for better efficiency.
Query profiling	Use the sampling query profiler to analyze query execution patterns, identify performance hot spots, and optimize resource usage.
Query cache	Accelerate frequently executed `SELECT` queries by enabling and configuring ClickHouse’s built-in query result caching.
Testing hardware	Run ClickHouse performance benchmarks on any server without installation to evaluate hardware capabilities. (Not applicable to ClickHouse Cloud)