snowflake error: query spilling to remote disk - how to find the memory hog

Not specified by the original author.

TL;DR

Spilling means the query needed more memory than the warehouse nodes have. Fix it by using a larger warehouse, reducing the working set (fewer columns, pre-aggregation), or restructuring the join. Spill is correctness-preserving but brutally slow.

snowflake query spilling to remote disk fix

Use this when

• Query profiles show "bytes spilled to remote disk"
• A query is slow despite a reasonable plan
• You are sizing a warehouse for heavy analytics

Not for this skill when

• BigQuery queries queue on slots
• You are hitting zero-copy clone limitations
• dbt models need tuning

Steps

1. Confirm the spill in the query profile:

SELECT query_id, bytes_spilled_to_remote_storage
FROM snowflake.account_usage.query_history
WHERE bytes_spilled_to_remote_storage > 0
ORDER BY start_time DESC LIMIT 10;

Expected output: the queries that spilled and how many bytes. Local-disk spill is a warning; remote-disk spill is the performance killer.

2. The fastest fix is usually a bigger warehouse. Spill is per-node memory:

ALTER WAREHOUSE analytics SET warehouse_size = 'LARGE';

Expected output: each node gets more RAM, so the same query spills less or not at least. Doubling the warehouse doubles per-node memory (and cost per second, but the query finishes faster).

3. Reduce the working set before the heavy operation:

-- aggregate before the join, not after
WITH daily AS (
  SELECT user_id, date, sum(amount) AS amount
  FROM events GROUP BY user_id, date
)
SELECT ... FROM daily JOIN users USING (user_id);

Expected output: the join builds its hash table on thousands of rows instead of billions. Pre-aggregation is the highest-leverage rewrite for spill.

4. Look for the classic spill generators in the plan:

- huge joins with no selective filter on either side
- ORDER BY over the full result set (add LIMIT or sort less)
- window functions over unpartitioned billions of rows
- SELECT * through a join (carry only needed columns)

Expected output: the operation to restructure. Each of these multiplies the memory the engine must hold at once.

5. For recurring heavy queries, consider a dedicated larger warehouse rather than upsizing the shared one:

CREATE WAREHOUSE heavy_etl WITH warehouse_size = 'XLARGE' auto_suspend = 300;

Expected output: the heavy query gets its memory without paying XLARGE prices for every lightweight query on the shared warehouse. Auto-suspend keeps the cost bounded.

Variant phrasings

snowflake bytes spilled to remote storage

The query exceeded node memory and spilled to S3-backed storage (step 1). Larger warehouse or smaller working set.

snowflake query slow spilling

Remote spill is orders of magnitude slower than memory. Treat any remote spill as a bug to fix, not a state to accept.

snowflake warehouse sizing for large joins

Size so the largest build side fits in node memory. When in doubt, test one size up and compare elapsed time vs cost.

Why it happens

Each warehouse node has fixed RAM. Operations like joins, sorts, and aggregations need their working set in memory; when it does not fit, Snowflake spills to local SSD first, then to remote storage. Remote spill keeps the query correct but the I/O dominates runtime.

Edge cases

• Multi-cluster warehouses add nodes, not per-node memory; they fix concurrency, not spill. Only a larger size fixes spill.
• Spill accounting is per query; concurrent queries on the same warehouse do not share the spill, they each get node memory.
• bytes_spilled_to_local_storage is normal under pressure and much cheaper than remote; optimize for zero remote spill first.
• Result caching does not help spilled queries go faster; it only helps identical repeats skip execution.

Provenance

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

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

Thread query: snowflake query spilling to remote disk fix

TL;DR: Spilling means the query needed more memory than the warehouse nodes have. Fix it by using a larger warehouse, reducing the working set (fewer columns, pre-aggregation), or restructuring the join. Spill is correctness-preserving but brutally slow.

Not specified by the original author.

postgres 'Temporary file size exceeds tempfilelimit': which query is spilling to disk

TL;DR

Find the culprit with pgstatstatements ordered by temp blocks written, or turn on logtempfiles to catch it red-handed. The spill almost always comes from a sort or hash join whose working set exceeds workmem - the fix is a better plan (index, less data sorted) or a bigger workmem for that query, not just raising tempfilelimit.

The error

Temporary file size exceeds temp_file_limit

Steps

1. Find the top temp-file writers with pgstatstatements (needs the extension enabled):

SELECT query, temp_blks_written, calls,
       round(temp_blks_written * 8.0 / 1024) AS temp_mb_total
FROM pg_stat_statements
ORDER BY temp_blks_written DESC
LIMIT 10;

Expected: one or two queries with tempmbtotal far above the rest - those are your spillers.

2. If pgstatstatements is not available, catch the query in the act. Set logtempfiles to 0 temporarily so every temp file is logged with the query text:

ALTER SYSTEM SET log_temp_files = 0;
SELECT pg_reload_conf();
-- reproduce the failing query, then check the Postgres log

Expected: log lines naming the temp file size and the query that created it.

3. Run EXPLAIN (ANALYZE, BUFFERS) on the suspect query and look for Sort Method: external merge or Hash Join with Buckets spilling to disk.

   Expected: the plan shows an on-disk sort or hash, and the row estimates vs actuals reveal why the planner mis-sized the memory.

4. Fix the plan first: add the missing index the sort wants, push filters earlier, or reduce the sorted set. Re-run EXPLAIN to confirm the sort went in-memory.

   Expected: Sort Method: quicksort with no temp files; the query stops tripping the limit.

5. If the plan is already optimal and the data is just big, raise workmem for that session only, and only then consider raising tempfile_limit as a guardrail (it is a safety limit, not a tuning knob):

SET work_mem = '256MB';
-- run the query in this session

Expected: the query completes within the session without touching temp files.

Use this when

• A query fails with the exact Temporary file size exceeds temp_file_limit error.
• You need to attribute disk temp usage to a specific query.
• Sorts or hash joins are suspected of spilling.

Not for this skill when

• The error is about WAL or data-directory disk space (that is storage capacity, not temp files).
• tempfilelimit is set absurdly low by config (check the setting first; the default is -1, unlimited).
• The spill comes from a maintenance operation like CREATE INDEX or VACUUM (those use maintenanceworkmem, a different knob).

Variant phrasings

postgres temp file size exceeds limit

Short form of the same error.

which query is writing temp files in postgres

The pgstatstatements query in step 1 answers this directly.

sort spilling to disk postgres how to find

logtempfiles plus EXPLAIN, as above.

Why it happens

Sorts and hash joins work in workmem-sized chunks; when the data does not fit, Postgres spills the overflow to temp files on disk. tempfile_limit caps how much temp space one session may use, and breaching it aborts the query. The usual root cause is a plan that sorts or hashes far more rows than necessary - missing index, bad row estimates from stale stats, or a join order the planner got wrong.

Edge cases

• Stale table statistics cause the planner to under-size the sort memory; run ANALYZE on the involved tables before blaming work_mem.
• Raising workmem globally multiplies by maxconnections; a big global work_mem can OOM the box under concurrency. Prefer per-session SET.
• Parallel queries give each worker its own workmem, so a parallel sort can use N times what you expect; check maxparallelworkersper_gather.
• Remember to set logtempfiles back to its normal value after the investigation; logging every temp file is noisy in production.

Provenance

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

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

Thread query: postgres 'Temporary file size exceeds tempfilelimit': which query is spilling to disk

TL;DR: Find the culprit with pgstatstatements ordered by temp blocks written, or turn on logtempfiles to catch it red-handed. The spill almost always comes from a sort or hash join whose working set exceeds workmem - the fix is a better plan (index, less data sorted) or a bigger workmem for that query, not just raising tempfilelimit.

Not specified by the original author.

clickhouse 'Memory limit (for query) exceeded': which part of the query blew up

TL;DR

Check the query log for the failed query's peak memory, then EXPLAIN the pipeline to find the memory-hungry stage - usually a GROUP BY with huge cardinality, a big JOIN, or an ORDER BY over the full result. Shrink that stage first (PREWHERE, smaller key sets, join order); raising maxmemoryusage is the last resort, not the fix.

The error

Memory limit (for query) exceeded

Steps

1. Find the failed query and its peak memory in the query log:

SELECT query, memory_usage, peak_memory_usage, query_duration_ms
FROM system.query_log
WHERE type = 'ExceptionBeforeStart' OR exception LIKE '%Memory limit%'
ORDER BY event_time DESC
LIMIT 5;

Expected: the failed query text plus how far past the limit its peak went.

2. EXPLAIN the query's pipeline to see the stages:

EXPLAIN PIPELINE [your query]

Expected: a stage list where you can spot the aggregation, join, or sort that materializes the most data.

3. Check the cardinality of your GROUP BY keys. A GROUP BY over a high-cardinality key (user IDs, request IDs) builds a hash table with one entry per group:

SELECT uniqExact([group key]) FROM [table] WHERE [your filters]

Expected: a cardinality number; millions of groups means the aggregation state is the memory hog.

4. Shrink the hungry stage: add PREWHERE to filter before the aggregation, aggregate at a coarser grain first, or rewrite the JOIN so the smaller table is on the right side. Re-run and watch peakmemoryusage fall.

   Expected: the same query completes with peak memory well under the limit.

5. Only if the query is legitimately large, raise the limit for that query alone rather than globally:

SET max_memory_usage = 20000000000;
-- then run the query in this session

Expected: the query completes; the global limit still protects the server from runaway queries.

Use this when

• ClickHouse returns the exact Memory limit (for query) exceeded error.
• You need to attribute the memory to a specific stage of the query.
• A previously working query starts failing as data grows.

Not for this skill when

• The error is Memory limit (total) exceeded (that is server-wide pressure, not one query; check concurrent load).
• The failure is at INSERT time (Too many parts) - that is a merge/insert-pattern problem, not query memory.
• You are on ClickHouse Cloud with fixed tier memory; the fix is query shape or a bigger tier, not settings.

Variant phrasings

clickhouse memory limit exceeded which part of query

The query-log plus EXPLAIN PIPELINE workflow above.

clickhouse query dies with memory limit on group by

High-cardinality GROUP BY is the classic cause; step 3 confirms it.

how to find memory hungry stage clickhouse

system.querylog peakmemory_usage plus EXPLAIN PIPELINE.

Why it happens

ClickHouse executes queries as a pipeline of stages, and stages like aggregation, joins, and sorts must hold their working set in memory - a GROUP BY keeps one state per group, a JOIN keeps the right-hand table. The memory tracker enforces maxmemoryusage per query, and when the working set outgrows it the query is killed. Growth in data or in group cardinality pushes previously fine queries over the line.

Edge cases

• max_bytes_before_external_group_by and max_bytes_before_external_sort let those two operators spill to disk instead of dying; useful as a safety valve, slower than fixing the query.
• Nullable and LowCardinality types change aggregation memory a lot; wrapping a high-cardinality string key in LowCardinality can shrink the hash table dramatically.
• JOIN with the large table on the right side is the most common self-inflicted cause; ClickHouse docs put the smaller table on the right.
• A query that passes at 20B maxmemoryusage but the server only has 16GB RAM will OOM the box; keep the per-query limit below physical memory minus headroom for concurrent queries.

Provenance

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

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

Thread query: clickhouse 'Memory limit (for query) exceeded': which part of the query blew up

TL;DR: Check the query log for the failed query's peak memory, then EXPLAIN the pipeline to find the memory-hungry stage - usually a GROUP BY with huge cardinality, a big JOIN, or an ORDER BY over the full result. Shrink that stage first (PREWHERE, smaller key sets, join order); raising maxmemoryusage is the last resort, not the fix.