## TL;DR
Lower the query's memory footprint first (fewer columns, pre-aggregation, smaller LIMIT-free sorts), then allow disk spilling with `max_bytes_before_external_group_by` and `max_bytes_before_external_sort`. Raising `max_memory_usage` alone just moves the crash.

```text
Code: 241. DB::Exception: Memory limit (for query) exceeded
```

## Use this when
- Queries fail with error 241 memory limit exceeded
- Big GROUP BY, JOIN, or ORDER BY blows the limit
- You need the query to finish, slower, rather than die

## Not for this skill when
- ClickHouse reports too many parts instead
- The query completes but is slow
- You are tuning Postgres, not ClickHouse

## Steps

1. Read the error's numbers. They tell you the actual limit and usage:

```text
Memory limit (for query) exceeded: would use 9.31 GiB (attempt to allocate chunk of 4.19 MiB), maximum: 10.00 GiB
```
Expected output: the configured max and how far over the query went. If usage is 10x the limit, restructuring beats tuning.

2. Cut the memory the query needs. The biggest wins in order:

```sql
-- select only needed columns, avoid SELECT *
-- pre-aggregate before the big join
-- add a LIMIT to exploratory queries
SELECT user_id, count() FROM events GROUP BY user_id
```
Expected output: the query fits. Wide rows and `SELECT *` through a GROUP BY are the most common avoidable blowups.

3. Enable external aggregation and sorting so the query spills to disk instead of dying:

```sql
SET max_bytes_before_external_group_by = 20000000000;
SET max_bytes_before_external_sort = 20000000000;
```
Expected output: GROUP BY and ORDER BY spill to disk past 20GB instead of throwing error 241. The query gets slower but completes.

4. For joins, spill the join to disk as well:

```sql
SET max_bytes_before_external_join = 20000000000;
```
Expected output: large joins complete via disk instead of dying. Alternatively restructure: filter the big side before the join, or use a `GLOBAL IN` subquery when the small side is truly small.

5. Only then consider raising the per-query limit, and do it per query, not globally:

```sql
SET max_memory_usage = 20000000000;
```
Expected output: a 20GB per-query cap for this session. Raising the global default invites one bad query to starve the whole server.

## Variant phrasings

### clickhouse memory limit for query exceeded
Error 241. Work steps 2-4 before reaching for step 5.

### clickhouse group by memory limit
`max_bytes_before_external_group_by` is the direct fix. Also check for high-cardinality GROUP BY keys, grouping by a UUID column is often the real problem.

### clickhouse join memory exceeded
Spill with `max_bytes_before_external_join`, or shrink the build side. The join algorithm loads one side fully into memory.

## Why it happens
ClickHouse executes queries in memory for speed and enforces a per-query cap (`max_memory_usage`, default 10GB). GROUP BY builds a hash table of all groups, JOIN builds a hash table of one side, and ORDER BY buffers everything, so any of them can exceed the cap on large data. The external-spill settings trade speed for completion.

## Edge cases
- `max_memory_usage_for_user` caps the user across concurrent queries, a single query can pass while the user cap still kills it.
- Dictionaries and IN-subqueries also consume memory outside the main query accounting.
- Spilling needs disk space and disk speed, on small cloud disks the spill itself can fail.
- Memory tracking is approximate, a query can die slightly under the nominal limit.

## Provenance

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