ClickHouse Settings: Performance Tuning

Tip: Max Threads

<max_threads>16</max_threads>

Match to your CPU core count.

Gotcha: Max Memory Usage

SET max_memory_usage = 10000000000;

10GB per query. Prevents OOM kills.

Tip: Merge Tree Settings

<merge_tree>
    <max_bytes_to_merge_at_max_space_in_pool>107374182400</merge_tree>
</merge_tree>

Gotcha: Background Pool Size

<background_pool_size>32</background_pool_size>

Controls concurrent merge operations.

Tip: Query-Level Settings

SET max_execution_time = 30;

30 second query timeout.

Gotcha: Settings Per Query

SELECT * FROM events SETTINGS max_threads = 4;

Override server settings per query.

Tip: Order of Columns in ORDER BY Matters Massively

ClickHouse's primary key is defined by ORDER BY. Put high-cardinality columns first for better data skipping. ORDER BY (timestamp, user_id) is very different from ORDER BY (user_id, timestamp) in query performance.

Tip: Use LowCardinality for Enum-Like Strings

Strings like status, country, browser benefit from LowCardinality(String) — it's stored as a dictionary internally, reducing storage 10x and speeding up scans.

Gotcha: Mutations Are Heavy

ALTER TABLE ... UPDATE and DELETE in ClickHouse create new parts instead of modifying in place. A single mutation on a large table can take hours and block merges. Design for append-only from day one.

Senior Insight

ClickHouse settings are per-query and per-session, allowing fine-grained control. The settings I tune most frequently: max_memory_usage (prevent OOM for large queries), max_threads (CPU parallelism — setting it too high increases overhead), preferred_block_size_bytes (IO block size — 1MB default is usually optimal), and optimize_aggregation_in_order (faster GROUP BY when data is sorted by the key). Each query can have its own settings, which is liberating once you embrace it.

Source: ClickHouse Blog (https://clickhouse.com/blog), Altinity Blog (https://altinity.com/blog), Altinity Knowledge Base (https://kb.altinity.com/)