Large GROUP BY or ORDER BY Runs Out of Memory in ClickHouse

A query works on a small date range but fails when you run it against several months of production data. ClickHouse reports MEMORY_LIMIT_EXCEEDED, or the query stops before it returns results.

Large GROUP BY and ORDER BY operations are common causes. They can require substantial memory to hold aggregation states or sort data.

For example, this query groups events by customer and event type.

SELECT
    customer_id,
    event_type,
    count() AS event_count
FROM events
GROUP BY
    customer_id,
    event_type
ORDER BY event_count DESC
LIMIT 100;

The query may process millions of rows and produce many distinct groups. ClickHouse must maintain aggregation states before it can return the final results.

The sort also needs memory to order the aggregated results. The LIMIT 100 clause does not guarantee that ClickHouse can avoid processing a large intermediate result.

The production problem is not always a lack of server RAM. A query can exceed its memory limit even when the server still has free memory.

The fix is to let ClickHouse spill intermediate data to disk before memory pressure becomes critical. You must also control how much temporary disk space queries can consume.

This article covers the settings for external aggregation, external sorting, and temporary disk limits.

2. Why it happens

ClickHouse normally performs aggregation and sorting in memory when possible.

During aggregation, it builds and updates data structures for each group. High-cardinality keys can create many groups. Complex aggregate functions can also require large aggregation states.

During sorting, ClickHouse must organize rows according to the requested sort keys. Large intermediate results can require substantial memory.

Two settings control when these operations can move intermediate data to disk:

• max_bytes_before_external_group_by controls external aggregation.
• max_bytes_before_external_sort controls external sorting.

This process is called spilling to disk.

When an operation reaches its spill threshold, ClickHouse writes intermediate data to temporary files. It later reads and merges the data to complete the operation.

Spilling reduces the memory required for some stages. It does not remove all memory requirements. ClickHouse still needs memory for processing, buffers, and merging intermediate data.

There are two ways to choose a spill threshold.

Byte thresholds use fixed sizes. They are useful when you know the memory budget for a workload.

Ratio thresholds use a fraction of available memory. They adapt the threshold to available memory instead of relying only on a fixed byte value.

ClickHouse provides these settings:

• max_bytes_ratio_before_external_group_by
• max_bytes_ratio_before_external_sort

The ratio settings are especially useful when you also set max_memory_usage. However, the ratio is not simply a percentage of that query limit. It relates to available memory, and the exact calculation depends on the operation and version. Verify the behavior on your deployed build.

3. How to confirm

First, inspect the current values on your server. Do not assume the defaults match the documentation or another environment.

Use system.settings to inspect session settings.

SELECT
    name,
    value,
    changed
FROM system.settings
WHERE name IN
(
    'max_memory_usage',
    'max_bytes_before_external_group_by',
    'max_bytes_before_external_sort',
    'max_bytes_ratio_before_external_group_by',
    'max_bytes_ratio_before_external_sort',
    'max_temporary_data_on_disk_size_for_query',
    'max_temporary_data_on_disk_size_for_user'
)
ORDER BY name;

Use system.server_settings to inspect server-level settings where applicable. The table and available columns can vary by version.

SELECT
    name,
    value,
    default
FROM system.server_settings
WHERE name = 'max_temporary_data_on_disk_size';

Next, inspect the query log. The ProfileEvents field records events associated with query execution. The ExternalAggregationWritePart and ExternalSortWritePart events can indicate that ClickHouse wrote external aggregation or sort data.

Run this query after the relevant query has completed and its log entry is available.

SELECT
    event_time,
    query_id,
    type,
    query_duration_ms,
    memory_usage,
    ProfileEvents['ExternalAggregationWritePart']
        AS aggregation_write_parts,
    ProfileEvents['ExternalSortWritePart']
        AS sort_write_parts,
    exception_code,
    exception
FROM system.query_log
WHERE type IN ('QueryFinish', 'ExceptionWhileProcessing')
  AND event_time >= now() - INTERVAL 1 HOUR
ORDER BY event_time DESC
LIMIT 100;

A positive write-part count is evidence of external processing. A value of zero does not prove that no spill occurred if the event is unavailable, unrecorded, or not applicable to that query path.

Also check exception_code and exception. A query can spill data successfully but still fail later because merging the intermediate results requires more memory.

For a controlled test, run the query with a low spill threshold. Then compare the profile events and peak memory with the original run.

4. The settings

The following defaults come from the specified ClickHouse master commit, dated 30 October 2026. They are not guaranteed to match every 26.9 build.

Byte thresholds versus ratio thresholds

A byte threshold gives you a fixed trigger. For example, max_bytes_before_external_group_by = 2000000000 sets a threshold of approximately 2 GB.

A ratio threshold uses a fraction of available memory. For example, max_bytes_ratio_before_external_group_by = 0.5 uses a ratio of 0.5.

The ratio is not necessarily half of max_memory_usage. ClickHouse calculates the threshold from available memory for the operation. The calculation can account for server, user, and other memory limits.

The byte and ratio settings can interact. Do not assume that setting a ratio makes the byte threshold irrelevant. In particular, ClickHouse documents that max_bytes_before_external_sort remains respected when the sort ratio is set.

For a production workload, configure the ratio settings and max_memory_usage together. Test the resulting behavior under realistic concurrency.

Temporary disk limits

Spilling moves pressure from RAM to storage. It can therefore create a different production problem.

max_temporary_data_on_disk_size_for_query limits temporary disk data for an individual query. max_temporary_data_on_disk_size_for_user limits temporary disk data for a user's queries.

A value of 0 means no limit.

These limits help protect storage from large temporary workloads. However, verify their enforcement on your exact build, especially for external sorting. A reported issue describes temporary disk limits not stopping external sort spills in ClickHouse 26.2.4. Do not treat configuration alone as proof that your disk is protected.

5. How to fix it

Start with per-query settings. This approach lets you test the change without affecting every workload.

The example below uses a 20 GB query memory limit and a ratio of 0.5 for both operations. It also sets a 10 GB temporary disk limit per query and a 20 GB limit per user.

These are example starting values, not universal recommendations. Adjust them to match available RAM, storage capacity, concurrency, and workload requirements.

Change 1: Configure memory and spill thresholds

For a single query, use the SETTINGS clause.

SELECT
    customer_id,
    event_type,
    count() AS event_count
FROM events
GROUP BY
    customer_id,
    event_type
ORDER BY event_count DESC
LIMIT 100
SETTINGS
    max_memory_usage = 20000000000,
    max_bytes_ratio_before_external_group_by = 0.5,
    max_bytes_ratio_before_external_sort = 0.5,
    max_bytes_before_external_group_by = 0,
    max_bytes_before_external_sort = 0;

The example sets the byte thresholds to zero to avoid relying on fixed byte thresholds alongside the ratio settings. Verify that this combination behaves as expected on your build before production use.

The 20 GB memory limit gives the query a defined budget. The ratios allow ClickHouse to trigger external processing based on available memory.

Do not copy the 20 GB value into every workload without checking concurrency. Several concurrent queries can compete for the same server memory.

Change 2: Set limits for the session

Use SET when you need several statements to share the same settings in one session.

SET max_memory_usage = 20000000000;
 
SET max_bytes_ratio_before_external_group_by = 0.5;
 
SET max_bytes_ratio_before_external_sort = 0.5;
 
SET max_bytes_before_external_group_by = 0;
 
SET max_bytes_before_external_sort = 0;

The settings apply to subsequent queries in the session. They do not require a server restart.

Before using this approach, confirm that your client keeps the same session between statements.

Change 3: Apply settings through a settings profile

For a production service, a settings profile provides a central place to manage these limits. Assign the profile to the appropriate users.

For example, add a profile to users.xml or the relevant configuration file included by your installation.

<clickhouse>
    <profiles>
        <analytics_profile>
            <max_memory_usage>20000000000</max_memory_usage>
            <max_bytes_ratio_before_external_group_by>0.5</max_bytes_ratio_before_external_group_by>
            <max_bytes_ratio_before_external_sort>0.5</max_bytes_ratio_before_external_sort>
            <max_bytes_before_external_group_by>0</max_bytes_before_external_group_by>
            <max_bytes_before_external_sort>0</max_bytes_before_external_sort>
            <max_temporary_data_on_disk_size_for_query>10000000000</max_temporary_data_on_disk_size_for_query>
            <max_temporary_data_on_disk_size_for_user>20000000000</max_temporary_data_on_disk_size_for_user>
        </analytics_profile>
    </profiles>
</clickhouse>

The example allows up to 10 GB of temporary data per query and 20 GB per user. These limits aim to reduce the risk of one query or user consuming all available temporary storage.

Configure the user to use analytics_profile through your existing user configuration or access-control management. Do not create a second conflicting profile for the same workload.

Apply the configuration using your normal ClickHouse configuration process. Settings profiles generally do not require a full server restart, but configuration reload behavior depends on the setting and deployment method. Verify that the active profile contains the expected values.

Important: Treat temporary disk limits as an additional safety control, not a substitute for monitoring free disk space. Test their enforcement with representative queries before relying on them to prevent disk exhaustion.

6. How to verify the fix

After applying the settings, repeat the workload with representative production data.

Compare the results with a baseline run. Check these items:

1. The query completes without MEMORY_LIMIT_EXCEEDED.
2. The query's ProfileEvents show external aggregation or sorting when the operation spills.
3. Peak memory stays within the intended budget.
4. Query duration remains acceptable for the workload.
5. Temporary disk use stays within the limits you intended to enforce.

Use system.query_log to inspect completed queries.

SELECT
    event_time,
    query_id,
    query_duration_ms,
    memory_usage,
    ProfileEvents['ExternalAggregationWritePart']
        AS aggregation_write_parts,
    ProfileEvents['ExternalSortWritePart']
        AS sort_write_parts,
    exception_code,
    exception
FROM system.query_log
WHERE type IN ('QueryFinish', 'ExceptionWhileProcessing')
  AND event_time >= now() - INTERVAL 1 HOUR
ORDER BY event_time DESC
LIMIT 50;

A query that completes successfully with external write events provides evidence that spilling occurred.

However, a successful query does not prove that every memory or disk limit worked as intended. Test the limits separately in a controlled environment.

For example, run a workload that is known to produce large temporary files. Check whether it stops when it reaches the configured disk limit. Also inspect free disk space during execution.

If the query still fails, inspect its exception and memory usage. The failure may occur during the merge stage, even after the initial aggregation or sorting stage spills data successfully.

If the query still consumes too much memory, reduce its input data, simplify its aggregation states, or review the query plan. Increasing memory limits without checking concurrent workloads can move the problem to the server level.

7. Risks and trade-offs

Spilling is slower than keeping data in memory. ClickHouse must write temporary files, read them back, and merge intermediate results. The extra disk I/O can increase query latency.

Disk performance matters. Slow storage can turn a query that previously failed quickly into a query that runs for a long time. Concurrent spills can also compete with normal reads, writes, merges, and other workloads.

Spilling does not guarantee that every query will complete. The merge stage still requires memory, and other parts of the query may consume substantial resources.

Temporary disk limits also have trade-offs. A limit that is too low can stop legitimate analytical queries. A limit that is too high can allow temporary data to consume valuable storage.

Monitor query latency, memory usage, temporary disk consumption, and failure rates after deployment. Test with realistic query concurrency rather than one isolated query.

8. Summary

• Enable external aggregation and sorting to let ClickHouse spill intermediate data to disk.
• Use the ratio settings with max_memory_usage to manage memory pressure.
• Set temporary disk limits for queries and users, and verify their enforcement.
• Use system.query_log and ProfileEvents to confirm that spilling occurs.
• Test memory, disk usage, and latency under realistic production workloads before rollout.

References

1. ClickHouse documentation: Memory limit exceeded for query. Explains external aggregation, external sorting, and their performance trade-offs. ClickHouse memory troubleshooting
2. ClickHouse source: Settings.cpp. Provides setting definitions and descriptions for external sorting and ratio thresholds. ClickHouse settings source
3. ClickHouse documentation: ORDER BY implementation details. See the documentation for external sorting and its memory behavior. ClickHouse 26.9 release information
4. ClickHouse documentation: system.query_log. Consult the official documentation for query-log fields and profile-event inspection. ClickHouse query troubleshooting guide