postgres 'Temporary file size exceeds temp_file_limit': which query

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.

Not specified by the original author.

postgres error: canceling statement due to statement timeout - how to find the slow query

TL;DR

A query ran longer than statementtimeout and Postgres killed it. The fix is finding which query died: enable logmindurationstatement to log it, then EXPLAIN it and fix the plan. Raising the timeout without fixing the query just moves the pain.

ERROR: canceling statement due to statement timeout

Steps

1. Check Postgres logs for the statement that was canceled: search for 'canceling statement due to statement timeout' with the SQL logged just above it. Expected: You see the full SQL text of the killed query.

2. Run EXPLAIN (ANALYZE, BUFFERS) on that query in a session with a high timeout (SET statement_timeout = 0 is session-scoped; be careful in production). Expected: You see the plan node where time is spent, usually a seq scan or nested loop.

3. Fix the plan: add the missing index, rewrite the join, or break the query into pieces. Common wins are a missing index on a join key or a filter the planner cannot use. Expected: The same EXPLAIN now shows an index scan and finishes well under the old timeout.

4. Set logminduration_statement = 1000 (ms) in postgresql.conf so future slow queries are logged before they time out. Expected: New slow statements appear in the logs with their durations.

5. Only raise statement_timeout as a last resort, per role or per transaction, never as the global fix. Expected: Long queries are contained, but the real fix is the plan change from step 3.

When to use

• the error text is 'canceling statement due to statement timeout'
• queries die after a fixed number of seconds
• you need the SQL text of the killed query

When not to use

• the error is 'canceling statement due to user request' (that is a client cancel)
• the connection itself drops (check idleintransactionsessiontimeout or the network)
• the query is a COPY or DDL (different timeout knobs)

Tool compatibility

• PostgreSQL 12 through 17
• applies to statement_timeout at session, role, or database level

Variant phrasings

• "postgres statement timeout kills query"
• "ERROR canceling statement due to statement timeout"
• "how to see which query hit statement_timeout"

Why it happens

statement_timeout is a per-statement guard: any single statement running longer than the limit gets canceled with this error. The timeout is the messenger; the slow plan is the cause. The query text is in the logs only if logging is configured to capture it.

Edge cases

• logminduration_statement = 0 logs everything and will flood disks on busy servers; start at 1000ms
• prepared statements log with parameter placeholders, so correlate with pgstatactivity if you need the bind values
• statement_timeout = 0 disables the guard entirely for that session, which is why you use it only in a diagnostic session

Provenance

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

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

Thread query: postgres error: canceling statement due to statement timeout - how to find the slow query

TL;DR: A query ran longer than statementtimeout and Postgres killed it. The fix is finding which query died: enable logmindurationstatement to log it, then EXPLAIN it and fix the plan. Raising the timeout without fixing the query just moves the pain.