clickhouse memory limit exceeded query fix

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.

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:

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.

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

-- 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.

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

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.

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

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.

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

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