Temp File Disk Usage

Scenario

An analytics report runs overnight. By morning, the data disk is 90% full. The DBA finds a directory $PGDATA/base/pgsql_tmp/ with 200 GB of temp files. The offending query does a multi-table hash join and sorts 2 billion rows with work_mem = 4MB — each sort spill creates temp files. After the query finishes, temp files are deleted, but during execution they fill the disk.

How to Identify

Conditions:

• pg_ls_tmpdir() shows many large temp files during query execution
• EXPLAIN ANALYZE shows Sort Method: external merge Disk: XXXXKB
• log_temp_files threshold exceeded in PostgreSQL logs
• pg_stat_statements shows high temp_blks_written for specific queries
• work_mem is very small relative to the data being sorted/hashed

Analysis Steps

-- Check current temp file usage (while query is running):
SELECT name, size, modification
FROM pg_ls_tmpdir()
ORDER BY size DESC;
-- Large files here = sort spills for current queries

-- Find queries currently using temp files:
SELECT pid, usename, state, temp_blks_written, query
FROM pg_stat_activity
WHERE temp_blks_written > 0
ORDER BY temp_blks_written DESC;

-- Check log_temp_files threshold
SHOW log_temp_files;
-- -1 = never log; 0 = log all temp files; N = log temp files > N KB

-- Find queries that historically generated large temp files (pg_stat_statements):
SELECT query, calls, temp_blks_written,
       round(temp_blks_written * 8192.0 / 1024^2 / calls, 1) AS avg_temp_mb_per_call
FROM pg_stat_statements
WHERE temp_blks_written > 0
ORDER BY temp_blks_written DESC
LIMIT 10;

-- Check work_mem setting
SHOW work_mem;
-- Low work_mem = more sort spills = more temp files

Pitfalls

• Temp files are deleted when the query finishes — disk fills only while queries run. Monitoring must be real-time.
• Increasing work_mem globally multiplies by max concurrent sorts — 64 MB × 500 connections × 3 sorts each = 96 GB. Always increase per-session or per-role instead.
• temp_tablespaces can redirect temp files to a different filesystem — a fast SSD or a filesystem with more space.
• log_temp_files = 0 logs all temp file creation — useful for discovering which queries spill, but generates log volume.
• Hash joins also write to temp storage when the hash table doesn’t fit in work_mem. The fix is the same: more work_mem or a hash-friendly query plan.

Resolution Approach

For the immediate run: kill the query or let it finish. Long-term: redirect temp files to a dedicated filesystem via temp_tablespaces, increase work_mem per-role for analytics users, and optimize the query to reduce sort volume (add indexes, pre-aggregate).