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	A good place to start for query optimization, this simple guide describes common scenarios of how to use different performance and optimization techniques to improve query performance.
Primary Indexes Advanced Guide	A deep dive into ClickHouse indexing including how it differs from other DB systems, how ClickHouse builds and uses a table's spare primary index and what some of the best practices are for indexing in ClickHouse.
Query Parallelism	Explains how ClickHouse parallelizes query execution using processing lanes and the max_threads setting. Covers how data is distributed across lanes, how max_threads is applied, when it isn’t fully used, and how to inspect execution with tools like EXPLAIN and trace logs.
Partitioning Key	Delves into ClickHouse partition key optimization. Explains how choosing the right partition key can significantly improve query performance by allowing ClickHouse to quickly locate relevant data segments. Covers best practices for selecting efficient partition keys and potential pitfalls to avoid.
Data Skipping Indexes	Explains data skipping indexes as a way to optimize performance.
PREWHERE Optimization	Explains how PREWHERE reduces I/O by avoiding reading unnecessary column data. Shows how it’s applied automatically, how the filtering order is chosen, and how to monitor it using EXPLAIN and logs.
Bulk Inserts	Explains the benefits of using bulk inserts in ClickHouse.
Asynchronous Inserts	Focuses on ClickHouse's asynchronous inserts feature. It likely explains how asynchronous inserts work (batching data on the server for efficient insertion) and their benefits (improved performance by offloading insert processing). It might also cover enabling asynchronous inserts and considerations for using them effectively in your ClickHouse environment.
Avoid Mutations	Discusses the importance of avoiding mutations (updates and deletes) in ClickHouse. It recommends using append-only inserts for optimal performance and suggests alternative approaches for handling data changes.
Avoid Nullable Columns	Discusses why you may want to avoid Nullable columns to save space and increase performance. Demonstrates how to set a default value for a column.
Avoid Optimize Final	Explains how the OPTIMIZE TABLE ... FINAL query is resource-intensive and suggests alternative approaches to optimize ClickHouse performance.
Analyzer	Looks at the ClickHouse Analyzer, a tool for analyzing and optimizing queries. Discusses how the Analyzer works, its benefits (e.g., identifying performance bottlenecks), and how to use it to improve your ClickHouse queries' efficiency.
Query Profiling	Explains ClickHouse's Sampling Query Profiler, a tool that helps analyze query execution.
Query Cache	Details ClickHouse's Query Cache, a feature that aims to improve performance by caching the results of frequently executed SELECT queries.
Testing Hardware	How to run a basic ClickHouse performance test on any server without installation of ClickHouse packages. (Not applicable to ClickHouse Cloud)