Fix PostgreSQL 'temporary file size exceeds temp_file_limit' Error

The Error

You fire off a query — a big sort, a hash join, a GROUP BY on millions of rows — and PostgreSQL cuts it dead:

ERROR: temporary file size exceeds temp_file_limit (1048576kB)

The query doesn't slow down. It just dies. PostgreSQL needed to spill intermediate data to disk, the spill crossed the temp_file_limit cap, and that was that.

Root Cause

PostgreSQL has a fixed amount of RAM per sort or hash operation, set by work_mem. When the data doesn't fit, it spills to disk as temporary files. The temp_file_limit parameter puts a ceiling on how much disk a single session can use for those files.

Cross that ceiling and the query gets killed — no partial results, no retry. The default limit is often 1 GB (1048576 kB), which sounds large until you're joining two 500 MB tables without indexes.

Typical triggers:

• Sorting or grouping millions of rows on an unindexed column
• Hash joins across large unfiltered tables
• DISTINCT, ORDER BY, or UNION on wide result sets
• Multiple CTEs that each materialize a large intermediate result

Fix 1 — Increase work_mem (Start Here)

More work_mem means more fits in RAM before anything touches disk. Test with a session-level override first — no restart needed:

SET work_mem = '256MB';
-- Then re-run your query

Query completes? Make it stick in postgresql.conf:

# postgresql.conf
work_mem = 64MB   # default is 4MB

Reload config without a restart:

SELECT pg_reload_conf();
-- or: sudo systemctl reload postgresql

Watch the math before raising this globally. work_mem applies per sort node, per session. A query with 10 sort nodes running across 100 concurrent connections can consume 10 × 100 × work_mem RAM — that's 256 GB if work_mem = 256MB. Keep the global value under 10–20% of total RAM. For heavy analytical queries, override it at the session or role level instead.

Fix 2 — Raise or Remove temp_file_limit

For workloads that genuinely need large spills — nightly ETL, data warehouse exports — raise the cap rather than fighting it. Values are in kilobytes; -1 disables the limit.

-- Session only:
SET temp_file_limit = '4GB';  -- or -1 for unlimited

-- For a specific role permanently:
ALTER ROLE etl_user SET temp_file_limit = '4GB';

-- Globally in postgresql.conf:
temp_file_limit = 4194304   # 4 GB in kB

After editing postgresql.conf:

SELECT pg_reload_conf();

Don't set -1 globally. On a shared server with 20 app users, one runaway query could fill the disk and take down every other session. Reserve unlimited for trusted DBA or ETL roles only.

Fix 3 — Optimize the Query

Sometimes the limit isn't the problem — the query is. Pull the execution plan first:

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT ...;

Two things to look for: Sort Method: external merge Disk and Hash Batches: > 1. Either means the operation spilled to disk.

Add indexes to avoid sorts

-- Sorting or grouping by an unindexed column forces a full sort pass.
CREATE INDEX idx_orders_created ON orders(created_at);

Filter earlier

-- Bad: sort 10 million rows, then discard 9.99 million
SELECT * FROM events ORDER BY ts DESC LIMIT 100;

-- Better: filter to one tenant first, sort a fraction of the data
SELECT * FROM events WHERE tenant_id = 42 ORDER BY ts DESC LIMIT 100;

Drop unnecessary DISTINCT or ORDER BY inside CTEs

-- Every CTE with ORDER BY or DISTINCT materializes a temp result set.
-- Skip the ordering inside the CTE unless the logic actually requires it.
WITH recent AS (
  SELECT user_id, action FROM logs
  WHERE created_at > now() - interval '7 days'
  -- no ORDER BY here
)
SELECT ...;

Stage large intermediate results

A single query joining 6 tables and aggregating 50M rows is asking for trouble. Break it up:

CREATE TEMP TABLE stage AS
  SELECT user_id, sum(amount) AS total
  FROM transactions
  WHERE status = 'completed'
  GROUP BY user_id;

CREATE INDEX ON stage(user_id);

SELECT u.name, s.total
FROM users u
JOIN stage s USING (user_id)
WHERE s.total > 1000;

Each step is smaller, easier for the planner to handle, and easier for you to debug.

Verification

After applying a fix, check that the query completes and that temp file usage has dropped:

-- Confirm active settings
SHOW work_mem;
SHOW temp_file_limit;

-- Cumulative temp usage for the current database:
SELECT datname, temp_files, temp_bytes
FROM pg_stat_database
WHERE datname = current_database();

-- Active queries and their temp usage (PostgreSQL 14+ with pg_stat_statements):
SELECT pid, left(query, 80) AS query_snippet, wait_event_type, state
FROM pg_stat_activity
WHERE state = 'active';

Run the cumulative check before and after your query. If temp_bytes barely moves, the data stayed in RAM.

Prevention

• Assign temp_file_limit per role, not globally. A reasonable split: 1GB for application users, -1 for DBA and ETL roles.
• Tune work_mem to match your workload. OLAP databases can often handle 64–256MB globally. OLTP databases should stay at 4–16MB globally and override per-session for heavy queries.
• Turn on log_temp_files to catch runaway queries before they become incidents:

# postgresql.conf
log_temp_files = 0        # 0 = log every temp file write
# log_temp_files = 10240  # or log only files larger than 10 MB

• Make EXPLAIN ANALYZE part of your review process for any new query touching more than a few hundred thousand rows. Catching an external merge sort in development is free. Catching it at 2 AM in production is not.