Diagnosing ClickHouse 'Memory Limit (for Query) Exceeded' Errors: A Repeatable Triage Guide

Your ClickHouse query dies with DB::Exception: Memory limit (for query) exceeded, and the instinctive fix — raise max_memory_usage — often just moves the failure from a clean per-query exception to a server-level OOM kill that takes down unrelated work. The better path is a short, repeatable triage loop: confirm what actually failed, identify which of a handful of known causes applies, apply the fix that matches that cause, and only then consider changing limits or hardware.

This guide assumes a reasonably recent ClickHouse release. Setting names, defaults, and system table columns vary by version, so confirm names against your deployment before relying on them.

Recognizing the condition

The error surfaces in two ways. Clients see the exception with a message like Memory limit (for query) exceeded: would use X GiB ... maximum: Y GiB. On the server, the log shows MemoryTracker messages, and system.query_log records the failed query with an exception code and a memory_usage value close to the configured limit. If users report "the query just fails after a while" without quoting the error, the query log is where you confirm it.

Cause-to-diagnostic map

Symptom in query_log	Likely cause	Confirming check	Targeted fix
High memory_usage, high read_rows, aggregation in query	GROUP BY over high-cardinality keys	Compare read_rows to result size; check normalized_query_hash for GROUP BY	Enable external aggregation; reduce cardinality; pre-aggregate
High memory_usage, join present	Large right-hand table in JOIN (right side is loaded into memory as a hash table)	Estimate right-side row count and width	Put the smaller table on the right; pre-filter the right side
High memory_usage, huge read_bytes relative to result	Missing PREWHERE / no partition pruning, full-part scans	EXPLAIN indexes_used / PLAN; compare read_rows to table size	Add filters matching the sorting key; align ORDER BY with table key
Moderate memory_usage per query, but failures under load	Concurrency: many queries share server memory	system.processes count at failure time; system.metrics	Cap concurrency; stagger workloads; per-user limits
ORDER BY without LIMIT over large input	Full sort in memory	EXPLAIN PLAN shows Sorting step over large read_rows	Enable external sort; exploit table sorting key; add LIMIT

Ordered checks

Run these in order; each narrows the cause before you touch any setting.

1. Confirm the failure in the query log. From a client with access to the system database (default database, admin or equivalent privileges):

   SELECT query_id, exception_code, exception, memory_usage,
          read_rows, read_bytes, normalized_query_hash, query_duration_ms
   FROM system.query_log
   WHERE event_time > now() - INTERVAL 1 DAY
     AND type = 'ExceptionWhileProcessing'
     AND positionCaseInsensitive(exception, 'Memory limit') > 0
   ORDER BY memory_usage DESC
   LIMIT 20;

   This gives you the worst offenders and their normalized_query_hash, which groups repeats of the same query shape. Note that query_log retention and logging configuration may exclude some queries, and queries that fail at parse time may not appear.
2. Check the effective limits. Look at max_memory_usage, max_bytes_before_external_group_by, and max_bytes_before_external_sort for the user profile running the query (system.settings, system.settings_profiles). A low per-query limit combined with a legitimately heavy query is a different problem than a runaway query hitting a generous limit.
3. Inspect the plan. Run EXPLAIN PLAN (or EXPLAIN indexes = 1 where supported) on the failing query shape with a small time range. Check whether partition pruning and the primary index actually reduce read_rows. If the plan reads the whole table to answer a narrow question, the fix is in the query or schema, not the memory limit.
4. Check concurrency at failure time. Query system.processes during a failure window (or reconstruct from query_log start times) to see how many heavy queries overlapped. Several queries each under their individual limit can still exhaust server RAM.
5. Verify scan volume. Compare read_rows from step 1 against the table's total rows in system.parts. Reading 90% of a table to aggregate it is expected to be expensive; reading 90% to return 100 rows means filtering is broken.

Fixes tied to findings

Oversized GROUP BY or ORDER BY: enable spilling to disk rather than failing. For example, per session or in the user profile:

SET max_bytes_before_external_group_by = 20000000000;  -- 20 GB
SET max_bytes_before_external_sort = 20000000000;

These let aggregation and sorting spill to temporary storage once they exceed the threshold. This prevents the exception but is not a performance fix — spilled operations can be dramatically slower, so treat it as a safety valve while you reduce the actual working set. Verify by rerunning the query and checking in system.query_log that it completes and that memory_usage stays under the limit.

Joins: ClickHouse builds an in-memory hash table from the right-hand side of a JOIN. Rewrite so the smaller, pre-filtered table is on the right. If the right side is inherently large, consider whether a dictionary or a denormalized dimension table removes the join entirely.

Missing filtering: add a PREWHERE condition or restructure the filter to match the table's sorting key so the primary index and partition pruning cut read_rows. Confirm with EXPLAIN before rerunning at full scale — the plan should show fewer parts and granules read.

Concurrency: cap parallel work at the application or proxy layer, or set per-user max_memory_usage and max_concurrent_queries_for_user-style profile limits so one workload cannot starve the rest. Raising max_memory_usage without reducing per-query demand is the last resort, not the first: it converts a catchable per-query exception into a potential server OOM, which is strictly worse.

When to escalate

Escalate from query tuning to schema or cluster changes when either of these holds:

• Memory errors persist after external aggregation/sort is enabled and the query has been rewritten to prune and filter properly.
• Memory per row scanned is inherently high — very wide aggregations, huge dictionaries, or high-cardinality GROUP BY keys that the business logic genuinely needs.

At that point the realistic options are: pre-aggregate with materialized views into an AggregatingMergeTree table so queries read partial states instead of raw rows; shard the table so each node holds a fraction of the data; or add RAM or replicas. These are design changes with their own costs, which is why they belong after the cheaper fixes, not before.

Verifying the fix

Rerun the original failing query (or its normalized_query_hash family) and confirm in system.query_log that it completes, and compare memory_usage, read_rows, and query_duration_ms to the pre-fix values. Then check system.asynchronous_metrics and OS-level memory during peak concurrent load to confirm headroom remains — a fix that works for one query but leaves the server at 95% memory under normal load will resurface as the next incident.