Introduction

ClickHouse queries often look simple at the SQL level while doing a large amount of work underneath. A query with one GROUP BY, one ORDER BY, or one JOIN can still allocate a lot of memory because the execution engine builds in-memory data structures to stay fast.

That does not mean ClickHouse is inefficient. It usually means the physical plan is more expensive than the SQL text suggests. To fix the issue, you need to understand what operation is actually consuming memory.

Why a "Simple" Query Can Use a Lot of RAM

Consider this query:

1SELECT country, count()
2FROM events
3WHERE event_date >= '2026-03-01'
4GROUP BY country
5ORDER BY count() DESC
6LIMIT 100;

It is short, but several memory-heavy steps may happen:

• columns must be read and decompressed into blocks
• aggregation states must be stored for each distinct country
• the ORDER BY may require sorting many result rows
• the query may run in parallel, which means per-thread intermediate states

ClickHouse documentation repeatedly points out that the biggest memory users are usually high-cardinality aggregations, joins, and sorts. A query that groups by a field with many distinct values can build a large hash table even if the SQL itself is only a few lines long.

Parallelism Is Part of the Story

ClickHouse is fast partly because it parallelizes work across multiple threads. That improves throughput, but it can also increase peak memory usage because each pipeline lane may build partial aggregation or sorting state before the results are merged.

This is why two queries with identical SQL can use very different memory depending on settings and data shape. A small number of groups may fit comfortably in memory, while a high-cardinality key can create millions of hash-table entries.

If you suspect parallelism is amplifying the problem, test with fewer threads:

1SET max_threads = 4;
2
3SELECT country, count()
4FROM events
5WHERE event_date >= '2026-03-01'
6GROUP BY country
7ORDER BY count() DESC
8LIMIT 100;

This may reduce peak memory at the cost of runtime.

Diagnose the Real Bottleneck

Do not guess. Inspect query logs and execution details.

For completed queries, system.query_log is a good starting point:

1SELECT
2    query,
3    memory_usage,
4    query_duration_ms,
5    read_rows,
6    read_bytes
7FROM system.query_log
8WHERE type = 'QueryFinish'
9ORDER BY event_time DESC
10LIMIT 5;

That helps answer practical questions:

• Did the query read more rows than expected
• Was the sort or aggregation bigger than the final result suggests
• Are repeated ad hoc queries competing for memory

You should also inspect the plan shape with EXPLAIN and check whether the query contains expensive joins or a sort after aggregation.

Ways to Reduce Memory Usage

The right fix depends on the operator that is consuming memory.

For large aggregations or sorts, ClickHouse can spill intermediate state to disk:

1SET max_bytes_before_external_group_by = 500000000;
2SET max_bytes_before_external_sort = 500000000;
3
4SELECT user_id, sum(revenue) AS total_revenue
5FROM purchases
6GROUP BY user_id
7ORDER BY total_revenue DESC;

External aggregation and external sort reduce RAM pressure, but they are slower because disk I/O replaces pure in-memory processing.

For joins, the usual rules apply:

• filter early
• put the smaller table on the right for hash joins
• consider a less memory-hungry join algorithm if the default is too expensive
• use specialized join forms such as ANY JOIN when the query semantics allow it

For repeated heavy analytics, a materialized view or summary table is often a better solution than forcing the same large aggregation at query time.

Common Pitfalls

• Assuming small result sets imply small memory use. The expensive part is often the intermediate state, not the final output.
• Grouping by very high-cardinality columns without realizing a large hash table must be built.
• Sorting large post-aggregation result sets in memory.
• Letting every query use many threads when memory is already tight.
• Raising max_memory_usage without first checking whether the query shape should be redesigned.

Summary

• ClickHouse memory usage is driven by the physical execution plan, not by how short the SQL looks.
• Aggregations, joins, sorts, and parallel pipelines are the main causes of high RAM consumption.
• Use system.query_log, EXPLAIN, and query settings to identify the actual bottleneck.
• External sort and external group-by can trade speed for lower memory usage.
• If the same expensive query runs often, pre-aggregate instead of paying the full memory cost every time.