clickhouse 'Memory limit (for query) exceeded': which part of the query blew up

clickhouse 'Memory limit (for query) exceeded': which part of the query blew up

TL;DR

Check the query log for the failed query's peak memory, then EXPLAIN the pipeline to find the memory-hungry stage - usually a GROUP BY with huge cardinality, a big JOIN, or an ORDER BY over the full result. Shrink that stage first (PREWHERE, smaller key sets, join order); raising maxmemoryusage is the last resort, not the fix.

The error

Memory limit (for query) exceeded

Steps

1. Find the failed query and its peak memory in the query log:

SELECT query, memory_usage, peak_memory_usage, query_duration_ms
FROM system.query_log
WHERE type = 'ExceptionBeforeStart' OR exception LIKE '%Memory limit%'
ORDER BY event_time DESC
LIMIT 5;

Expected: the failed query text plus how far past the limit its peak went.

2. EXPLAIN the query's pipeline to see the stages:

EXPLAIN PIPELINE [your query]

Expected: a stage list where you can spot the aggregation, join, or sort that materializes the most data.

3. Check the cardinality of your GROUP BY keys. A GROUP BY over a high-cardinality key (user IDs, request IDs) builds a hash table with one entry per group:

SELECT uniqExact([group key]) FROM [table] WHERE [your filters]

Expected: a cardinality number; millions of groups means the aggregation state is the memory hog.

4. Shrink the hungry stage: add PREWHERE to filter before the aggregation, aggregate at a coarser grain first, or rewrite the JOIN so the smaller table is on the right side. Re-run and watch peakmemoryusage fall.

   Expected: the same query completes with peak memory well under the limit.

5. Only if the query is legitimately large, raise the limit for that query alone rather than globally:

SET max_memory_usage = 20000000000;
-- then run the query in this session

Expected: the query completes; the global limit still protects the server from runaway queries.

Use this when

• ClickHouse returns the exact Memory limit (for query) exceeded error.
• You need to attribute the memory to a specific stage of the query.
• A previously working query starts failing as data grows.

Not for this skill when

• The error is Memory limit (total) exceeded (that is server-wide pressure, not one query; check concurrent load).
• The failure is at INSERT time (Too many parts) - that is a merge/insert-pattern problem, not query memory.
• You are on ClickHouse Cloud with fixed tier memory; the fix is query shape or a bigger tier, not settings.

Variant phrasings

clickhouse memory limit exceeded which part of query

The query-log plus EXPLAIN PIPELINE workflow above.

clickhouse query dies with memory limit on group by

High-cardinality GROUP BY is the classic cause; step 3 confirms it.

how to find memory hungry stage clickhouse

system.querylog peakmemory_usage plus EXPLAIN PIPELINE.

Why it happens

ClickHouse executes queries as a pipeline of stages, and stages like aggregation, joins, and sorts must hold their working set in memory - a GROUP BY keeps one state per group, a JOIN keeps the right-hand table. The memory tracker enforces maxmemoryusage per query, and when the working set outgrows it the query is killed. Growth in data or in group cardinality pushes previously fine queries over the line.

Edge cases

• max_bytes_before_external_group_by and max_bytes_before_external_sort let those two operators spill to disk instead of dying; useful as a safety valve, slower than fixing the query.
• Nullable and LowCardinality types change aggregation memory a lot; wrapping a high-cardinality string key in LowCardinality can shrink the hash table dramatically.
• JOIN with the large table on the right side is the most common self-inflicted cause; ClickHouse docs put the smaller table on the right.
• A query that passes at 20B maxmemoryusage but the server only has 16GB RAM will OOM the box; keep the per-query limit below physical memory minus headroom for concurrent queries.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_pbLDkvXJmWGrQZnKrbKtPA