ClickHouse memory limits and spilling to disk

Why ClickHouse memory limits decide whether dashboards survive peak load

ClickHouse memory limits are set per query, per user profile and per server, and large GROUP BY and sort operations can spill to disk once they pass configured thresholds. Without these controls, one heavy query or a burst of dashboard refreshes can consume the memory that every other query on the node depends on, so the failure reaches work that had nothing to do with the original query. The limits exist to keep one workload from taking the server down, and they give the operator a predictable way to fail a single query instead of the whole node. The ClickHouse documentation describes the controls as layered. Per-query settings such as max_memory_usage cap a single statement, while max_bytes_before_external_group_by and max_bytes_before_external_sort tell the server when to write intermediate state to temporary files instead of holding it in RAM. Server-wide ceilings and settings profiles then bound what a user or group of users can consume in total. Limits are a safety control, not a sizing substitute: they stop runaway queries but do not make an undersized node adequate for its workload. Confirm the default values and exact setting names for your ClickHouse version in the settings reference, because defaults and behavior around spilling have changed across releases. Teams usually need these controls once dashboards share a cluster with ingestion or ad hoc analysis, or once a query that works on last month's data starts failing as volume grows. Before setting thresholds, review your own peak pattern and the queries that dominate it, then test the proposed limits on a copy of representative data. Plan for the failure mode you want: a query that fails with a clear memory error is usually easier to manage than one that pushes the node into swap or triggers an out-of-memory kill.

Which memory setting fits which problem

This table compares the main memory controls in self-managed ClickHouse by scope, the failure each one prevents, its cost in slower or failed queries, and when it fits. Read it top to bottom to choose the first control to set, then confirm exact setting names and defaults in the documentation for your version.

Control	Scope and failure it prevents	Trade-off	When it fits
Per-query maximum memory	One query. Stops a single runaway GROUP BY, JOIN or sort from consuming the whole server.	The offending query fails with a memory error instead of finishing, so analysts must rewrite it or ask for a higher limit.	Ad hoc and dashboard queries with predictable shapes where a clear failure is better than server instability.
Per-user settings profile	A user, role or group. Applies different memory ceilings to dashboards, ingestion and analysts.	Needs upkeep as teams and roles change, and a profile that is too tight blocks legitimate heavy reports.	Shared clusters where service accounts and human users have very different query patterns.
Server-wide memory limit	The whole ClickHouse process. Prevents the operating system from killing the server when concurrent queries add up.	Queries may fail or wait under pressure even when each one is within its own limit, so the server limit must leave room for the OS and caches.	Every production node, as a backstop beneath per-query and per-user limits.
External GROUP BY threshold	Aggregation state for one query. Spills intermediate GROUP BY data to disk once it exceeds the threshold.	The query finishes but runs slower because of disk I/O, and the spill directory needs enough free space.	Large aggregations over event or log data where a slow answer is acceptable and failure is not.
External sort threshold	Sort state for one query. Spills ORDER BY data to disk above the threshold.	Same trade-off as external GROUP BY: completion at the cost of latency and temporary disk use.	Queries that sort large result sets, such as top-N over a full time range.
Workload scheduling	CPU, I/O, memory and concurrency across groups of queries. Keeps ingestion and dashboards from starving each other.	Adds design and configuration effort on top of the basic limits, and it is usually a later step once the simpler controls work.	Clusters where several teams or workloads compete for the same resources after the first limits are in place.

How to set ClickHouse memory limits in order

This sequence moves from measuring real memory demand on a pre-production copy to a tested configuration in which a heavy query either completes or spills to disk without destabilizing the server. Each step ends with a check you can record before moving on.

Measure memory on a pre-production copy
Load a copy of production data into a pre-production ClickHouse environment and replay representative dashboards, ingestion jobs and ad hoc queries. Use the system.query_log table to record peak memory per query and per user, and note the typical and worst-case values for each workload. The step is done when you have a written baseline listing the heaviest queries, their peak memory, and the users or applications that run them. Ask your team to confirm the query mix with the owners of each dashboard before you rely on the numbers.
Set a server-wide memory ceiling
Choose a server-wide memory limit below the host's physical RAM, leaving headroom for the operating system, background merges, mutations and the page cache that ClickHouse depends on. Set the value in the server configuration so it applies after every restart, and confirm the figure against the memory settings in the ClickHouse documentation for your release. The check is that the server starts, the setting reports the value you configured in the system.server_settings table, and a merge-heavy test load does not push resident memory into the OS swap zone.
Create a dedicated test user
Create a non-admin test user with a role that matches a real dashboard or application account, rather than testing with the default user, which starts with all permissions. Confirm the access model with your security team first, since the default user should be restricted as part of the same change. The check is that the test user can run its normal queries and that SHOW GRANTS for that user lists only the privileges you intended.
Apply per-user memory profiles to non-admin roles
Create a settings profile that sets a per-query memory limit and a per-query memory cap for each class of non-admin user, then assign it to the matching roles or users. Keep administrative and maintenance accounts on a separate profile so that merges, backups and schema work are not starved by a tight query limit. The check is that SELECT on the test user's session shows the profile's max_memory_usage value, and that a deliberately large query from that user is stopped with a memory-limit error rather than consuming the host.
Enable external GROUP BY and sort thresholds
Enable external aggregation and external sorting by setting thresholds at which GROUP BY and ORDER BY operations write intermediate data to temporary files on disk instead of holding everything in memory. Choose the thresholds relative to the per-query limit so that spilling starts before the query reaches the hard cap, and confirm the setting names and default behavior in the release you run. The check is that a query with a large GROUP BY on the test copy produces temporary data on disk in the server's temporary location, which you can see in the query log's memory and profile events.
Compare spilled and in-memory results
Run the same heavy aggregation and sort twice on the test copy, once with spilling enabled and once with the thresholds raised so the query runs fully in memory on a machine large enough to hold it. Compare row counts, checksums or sampled output, and confirm that results are identical, including ordering where the query specifies it. The step is complete when the results match exactly and the spilled run's peak memory stays under the configured per-query limit.
Test a deliberately heavy query under load
Run a query designed to exceed the per-query limit as the test user, while ingestion and a dashboard workload run in parallel on the same copy. Confirm that the heavy query either completes through spilling or fails with a clear memory error, that the server stays responsive, and that the other workloads keep returning results. Record the outcome, the peak memory and the spill volume, then decide with your operations team whether the limits and thresholds are acceptable before applying them to production.

Where each limit belongs for ingestion, dashboards and ad hoc analysis

Inserts and background merges use server memory whether or not anyone is querying. They run continuously, so a per-query cap that is too tight for the writer account can cause inserts to fail or merges to fall behind. When merges stall, parts accumulate, and query performance degrades for every other workload. Treat the writer path as a separate budget from reads.

Server-wide headroom: confirm that the server memory ceiling leaves room for merges and inserts after query memory is reserved. Check the current value in your configuration before changing any query limit.
Writer account limits: keep the ingestion user on its own settings profile so read caps do not apply to inserts by accident. Review the profile assignment during deployment.
Merge backlog: watch part counts and merge activity in the system tables after any limit change. A rising backlog is an early sign that the limit is too tight.
Batch size and row width: larger batches and wide rows raise peak memory during an insert. Batch size is a trade-off between throughput and memory, so test it with representative payloads.
Burst behavior: ingestion spikes from agents or collectors can overlap with merges. Load test the peak, not the average.

Before changing any ingestion limit, run a copy of production-like ingestion at peak rate and confirm that merges keep pace.

How to find the queries that use the most memory before they fail

Start from the query log, not from failures. ClickHouse records each finished or failed query in system.query_log (and the related system.query_thread_log if you enable it), including fields such as peak_memory_usage, read_rows, query_duration_ms, user and normalized_query_hash. Group by normalized query hash, user and a fixed time window such as an hour or a day, then sort by peak memory. Compare each group's peak against the limits you configured, such as max_memory_usage in the user's settings profile and the server-wide memory ceiling. A query that sits close to its per-query limit on most runs is the one that will fail on the next data growth, even if it has not failed yet. Confirm the exact column names and their availability in your version against the ClickHouse documentation before you write dashboards on them.

A query that spills is not the same as one that fails. When external GROUP BY or external sort thresholds are reached, the query writes intermediate data to disk and finishes, usually slower but successfully. A query that exceeds its memory limit without a spill path is aborted with a memory limit error. Track both: spill events are recorded in the profile events that appear in the query log (confirm the event names for your version), and failures appear as exceptions with their error codes. A rising spill rate for a stable workload is an early warning. It usually means data volume, cardinality or concurrency has grown past what the thresholds were tuned for, and failures are the next step if nothing changes. Treat a new spike in spills after a schema change, a new dashboard or a version upgrade as a signal to review that query before it starts failing.

This history is only useful if you capture it from the start. The query log is held in memory and in system tables governed by retention settings, so the rows you need for last month's incident may already be gone. Decide early how long to keep query log data, where it is written and who can read it, and forward it to storage you control. Keep this in mind when you set up monitoring for the cluster, because the same exported data supports capacity planning and the workload isolation work described elsewhere in this guide. Ask your team to confirm the retention and flush settings for query logs in your environment before the first production load.

Security and operational risks of loose or tight memory limits

Memory limits protect a shared ClickHouse server from runaway queries, but set them carelessly and they open access gaps, starve background work or fill disks. The cards below pair each risk with a control you can check in your own environment, starting with who is allowed to change the limits.

Unrestricted profiles let users raise limits

A settings profile that does not cap max_memory_usage lets any member of that profile lift their own per-query ceiling with a SET statement. One analyst can then consume the memory that dashboards and ingestion depend on. Review every profile assigned to human users and confirm that memory settings are marked as constraints, so a user can lower them but not exceed the agreed maximum. Use the access control documentation to trace which profiles each user inherits.

Default user starts with broad permissions

ClickHouse's default user starts with all permissions, so a server left with that account open ignores the memory limits you set for named users. Anyone who reaches the native or HTTP port without credentials can run unbounded queries. Confirm that the default user is disabled or restricted, that application accounts carry their own profiles, and that the server rejects anonymous connections on every listener you expose.

Quotas that interact with memory limits

Quotas count queries and resource use over time windows, while memory limits act on each query. A quota that is too loose lets many moderate queries exhaust server memory together, and one that is too tight blocks legitimate work after a single heavy report. Decide which limit protects which failure mode, then test both together with a realistic concurrent load on a pre-production cluster before you rely on either.

Limits that starve replication and merges

Background merges and replica fetches also draw on server memory. If query limits are set so that user queries can claim nearly all of it, merges fall behind, parts accumulate and replicas lag. Leave headroom for background work by setting a server-wide ceiling below physical RAM and confirming the split in your settings. Watch merge backlog and replication queue depth after each change to see whether the balance holds.

Spill directories filling local disk

When a GROUP BY or sort exceeds its threshold, ClickHouse writes temporary data to disk. Without a size plan, that spill can fill the same volume that holds table parts, which stops inserts and can take a replica offline. Place spill files on a dedicated volume where possible, set thresholds that match its capacity, and alert on free space before a heavy query starts. Confirm the exact spill settings against your ClickHouse version's documentation.

Monitoring gaps hide memory pressure

A limit that fails a query only tells you that one query was refused. Without tracking peak memory per query and per user, you cannot tell whether limits are too tight, too loose or being hit by a single report. Record memory and spill metrics from the query log and system tables, chart them against user and profile, and review the top consumers after each limit change. Use this history to tune thresholds rather than guessing at them.

Questions teams ask before changing ClickHouse memory settings

How long does it take to tune ClickHouse memory settings?

The duration depends on how many query patterns the cluster serves, how much representative data exists in a pre-production environment, and how many rounds of testing the team needs before limits go live. Simple clusters with a few known dashboards move faster than shared clusters serving ingestion, dashboards and ad hoc analysts at the same time. No fixed duration applies to every environment, so ask any partner to state the assumptions behind its estimate and to tie the timeline to a written scope. Your own team should confirm how many distinct query shapes it runs and how often they change.

Can our own team tune ClickHouse memory limits without a partner?

Yes, many teams do this in-house, provided they have someone who can read query logs, run EXPLAIN and test changes safely. The work is mostly measurement: finding the heaviest queries in system.query_log, deciding which ones deserve a schema or query fix instead of a bigger limit, and then setting per-query and server thresholds. Teams without that skill often gain more from a short, fixed-scope review that leaves behind documented settings and a test procedure their staff can repeat.

What do we need to provide before memory work starts?

Plan to provide four inputs:

Query logs covering a representative period, including peak hours and scheduled jobs.
A sample of production-like data in a pre-production cluster, so memory behavior matches reality.
A list of the dashboards, pipelines and users who depend on the heaviest queries.
Access to the server configuration, host sizing and the change process for production.

The ClickHouse production FAQ recommends a pre-production environment with representative data and queries before production changes, so confirm early whether that environment exists.

What drives the cost and effort of ClickHouse memory tuning up or down?

Effort rises with the number of query shapes, the size of the data and the number of teams sharing the cluster. Costs fall when the schema already uses a sound ordering key, when queries are stable, and when a pre-production cluster can reproduce production load. Cost components are usually the engineering time for analysis, testing and documentation, plus any extra hardware if tuning shows that memory is genuinely short. Hardware is a one-time or recurring expense depending on how it is procured. Ask any quote to separate analysis, configuration changes, testing and handover so you can see which parts you can take in-house.

Do memory limits cause query failures or outages?

Limits can cause failures if they are set below what real queries need. A query that exceeds its limit may fail with a memory error, and a poorly chosen server limit can affect other workloads on the same host. Spilling to disk, controlled by external GROUP BY and sort thresholds, trades memory for slower execution and disk I/O. Before enforcing limits, test them on representative data, roll them out in stages and watch for failed queries in the logs. Confirm the exact setting names and behavior for your ClickHouse release in its documentation, because defaults and behavior can change between versions.

Which versions of ClickHouse should we tune against?

Tune against the version you run in production, and plan to retest settings after upgrades. The ClickHouse security policy lists the currently supported versions, and the production FAQ advises reading the changelog before upgrading because minor backward-incompatible changes can occur. Check the ClickHouse security policy for the supported release lines before you start, and confirm with your team which build is deployed. Memory settings tested on an unsupported release may not reflect the behavior you will get after patching.

What should we ask a partner for in a fixed-scope quote for memory work?

Ask for a written scope that names the clusters, the number of query patterns reviewed, the environments used for testing and the settings delivered. Request the deliverables in writing: the query analysis, the proposed per-query and server limits, the external GROUP BY and sort thresholds, the rollback steps and a runbook for finding memory-heavy queries later. Also ask who performs the work, what access they need, what you must provide, how acceptance is decided and what remains your team's responsibility after handover. DeployOpen agrees scope, deliverables and timelines in writing before work starts, and it does not publish a price list, so the quote follows from that scope.

Limits to confirm in your own environment

Memory limits and spill thresholds behave differently by release, query shape and deployment model. The cards below list what to confirm in your own environment before you set production values, and they point to the sources that answer each question.

Setting names and defaults

Memory and spill setting names and their default values can change between releases, so read them from the release you run rather than from older guides. Check the exact defaults for the chosen release in its settings reference, and record them in your change log. Confirm which settings are per query, per user through profiles, and server wide, because each scope needs its own review. Start with the version choice and changelog review described in the production FAQ.

Spill behavior on your query shapes

Whether a query spills, and how much it spills, depends on its aggregation keys, sort columns and cardinality. Test the GROUP BY and ORDER BY queries your dashboards and ad hoc users actually run, using a copy of representative data. Confirm that spilling completes on your hardware and that the query does not simply move the failure to a different stage. Record which shapes spill and which ones fail outright.

Distributed queries across shards

A distributed query runs part of its work on every shard, and each shard applies its own memory limits. A query that fits on one node can still exhaust memory on the coordinating node when partial results are merged. Test distributed aggregations and joins on a multi-shard copy of your cluster. Confirm which node enforces each limit and how partial results are combined before you set values for the whole cluster.

Spill disk capacity and speed

Spill files consume local disk, and a large sort or aggregation can write more data than your query's inputs suggest. Confirm the free space and write throughput on the volumes that hold spill files, and check that a few concurrent spilling queries cannot fill them. Slow disks turn a spill into a long stall rather than a fast fallback. Measure spill performance on the same storage class you will run in production.

Merges, inserts and background work

Memory limits that protect queries do not automatically govern background merges or insert processing. Confirm which memory pools merges and inserts draw from, and how they compete with user queries on the same node. Test a heavy merge or large insert while dashboards run to see whether queries are rejected. Use the workload controls in your plan to keep ingestion from starving reads.

Managed service controls

ClickHouse Cloud and other managed offerings may expose different memory controls, default values or limits than a self-managed server. Ask the provider which settings you can change, which are fixed, and how they apply to your service tier. If you run a managed service alongside self-managed clusters, document the differences so queries tuned in one environment are not assumed to behave the same way in the other.

How DeployOpen works on this

DeployOpen approaches ClickHouse memory tuning as a written engagement inside your own infrastructure. We start from the queries that fail or spill today, set limits against your hardware and workload mix, and test every change on a pre-production copy before it reaches production. The result is documented settings, a repeatable query review and a handover your team can run.

How we scope

Inventory the clusters, query users, dashboards and ingestion jobs that share the memory budget.
Agree in writing which per-query limits, server limits and spill thresholds are in scope.
Define acceptance tests, such as target queries that must complete without exceeding agreed memory.

How we build

Apply memory and external aggregation or sort settings through settings profiles in a pre-production environment first.
Run representative heavy queries from system.query_log before and after each change to compare peak memory and spill.
Keep every change in version-controlled configuration so it can be reviewed and rolled back.

How we hand over

Deliver the settings register, with each limit, its reason and the workload it protects.
Provide saved queries for finding top memory consumers and the thresholds that should trigger review.
Walk your operators through the runbook for raising limits, handling failed queries and rolling back a change.

How we support

Engagements are fixed-scope, with scope, deliverables and timelines agreed in writing before work starts.
Ongoing support, version upgrades and continuous review of memory use are available as a retainer.
Confirm with your team which dashboards and ingestion paths will be retested after each ClickHouse upgrade.

Controlling ClickHouse memory use and spilling to disk