# 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 max_memory_usage is the last resort, not the fix.

## The error

```text
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 peak_memory_usage 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.query_log peak_memory_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 max_memory_usage 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 max_memory_usage 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