Part 9: Performance Tuning
Squeeze every ounce of performance out of ClickHouse with these tuning tips.
ClickHouse is fast by default, but it can be blazing fast if you know how to tune it. In this part, we’ll cover indexes, compression, and query optimization.
Primary Keys & Sorting
The single most important factor for performance is your ORDER BY clause. It determines the primary key and how data is physically sorted
on disk.
Tip: Low Cardinality First
Always put columns with lower cardinality (fewer unique values) first in your primary key. This makes the index more effective.
Skipping Indexes
For columns not in the primary key, you can use data skipping indexes to speed up queries.
ALTER TABLE hits ADD INDEX idx_user_agent user_agent TYPE set(0) GRANULARITY 1;
Compression Codecs
ClickHouse supports column-level compression. ZSTD is generally
the best balance between ratio and speed, while Delta is
amazing for time-series data.
CREATE TABLE metrics ( timestamp DateTime CODEC(Delta, ZSTD), value Float64 CODEC(Gorilla, ZSTD) ) ENGINE = MergeTree() ORDER BY timestamp;
Query Optimization
Use the EXPLAIN statement to understand how your query is
being executed. Look out for full table scans and inefficient joins.
Conclusion
Tuning is an iterative process. Measure, tune, and measure again. In the final part of this series, we’ll discuss scaling ClickHouse to handle petabytes of data.