PostgreSQL Temporary File Spilling & Out of Memory: work_mem Tuning, Disk Spill Forensics & Query Optimization

Diagnosing excessive work_mem x concurrency spikes, disk sort spills, and temp_file_limit violations, emergency session overrides, and safe memory budgeting formula.

Reviewed by:Senior Database Architect & SRE (dataiotech Labs)

Tested Versions: PostgreSQL 14 / 15 / 16 / 17

Operational Verification & Reliability Metadata

Last Technical Review: 2 Kasım 2026

Tested VersionsPostgreSQL 14.10, 15.6, 16.4, 17.11 (Alpine / Ubuntu 24.04)

Required PrivilegesSUPERUSER or pg_read_all_stats

Risk Assessment

HighExceeding temp_file_limit cancels queries; indiscriminately increasing global work_mem triggers Linux OOM killer

Expected OutputTemporary file bytes stabilized below temp_file_limit and sort operations fit within memory

Rollback Procedure:Revert temp_file_limit and work_mem to baseline capacity values

PostgreSQL Docs: Resource Consumption & temp_file_limit

First 60 Seconds Emergency Protocol:

The error string is a symptom, not the root cause. Do not restart the server blindly. Run the diagnostic queries below to pinpoint the actual failure mode.

▸Symptom

Large analytical queries fail with:

plaintext

ERROR:  temporary file size exceeds temp_file_limit (10485760kB)
ERROR:  out of memory
DETAIL: Failed on request of size 33554432 in memory context "ExecutorState".

▸What You See in Production

Queries abort with quota exhaustion. pg_stat_database.temp_bytes spikes drastically, generating heavy disk I/O.

▸Root Cause Matrix

Probability	Root Cause	Description
%50	Massive Sort/Hash Spill	Execution nodes spill gigabytes to disk until hitting `temp_file_limit`.
%30	Aggressive `work_mem` Multiplier	High `work_mem` multiplied across concurrent queries triggers OS OOM.
%20	Restrictive `temp_file_limit`	Limit set too low for legitimate batch workloads.

▸EMERGENCY RUNBOOK (0-5 min)

sql

-- 1. Identify active sessions spilling to disk
SELECT pid, usename, query, pg_size_pretty(temp_bytes) AS temp_size 
FROM pg_stat_activity WHERE temp_bytes > 0 ORDER BY temp_bytes DESC;

-- 2. Cancel offending query
SELECT pg_cancel_backend(12345);

-- 3. Grant temporary session headroom
SET temp_file_limit = '20GB';
SET LOCAL work_mem = '256MB';

▸PERMANENT BLUEPRINT

1Safe `work_mem` Sizing: Apply $(RAM \times 0.25) / (max\_connections \times 2)$.
2Session Elevation: Apply larger work_mem strictly within specific sessions using SET LOCAL.
3Telemetry: Log spills exceeding 10MB via log_temp_files = 10240.

▸Verification

sql

SHOW work_mem;
SHOW temp_file_limit;
SELECT datname, temp_files, pg_size_pretty(temp_bytes) FROM pg_stat_database WHERE datname = current_database();

Other Database Error Triage Playbooks

Other battle-tested diagnostic guides for high-severity database errors.

All Errors (82)

POSTGRESQL7 min