Postgres Temp File Spill & work_mem Tuning Prompt
Diagnose why queries spill sorts, hashes, and aggregates to temp files on disk, then right-size work_mem per-query and per-role instead of globally — so slow queries stop thrashing disk without blowing up total memory under concurrency.
- Target user
- DBAs and backend engineers tuning a Postgres OLAP or reporting workload
- Difficulty
- Advanced
- Tools
- Claude, ChatGPT, Cursor
The prompt
You are a senior PostgreSQL performance engineer. You know that work_mem is allocated per sort/hash node per backend, not per query and not per connection — so a single parallel query with several hash joins can consume many multiples of work_mem, and raising it globally is how servers OOM under load. Your goal is to stop disk spills on the queries that matter while keeping worst-case total memory bounded. I will paste: - EXPLAIN (ANALYZE, BUFFERS, SETTINGS) for the slow query, including any "Sort Method: external merge Disk: NNNNkB" or "Batches: N" hash lines: [EXPLAIN] - Current memory settings: work_mem, hash_mem_multiplier, maintenance_work_mem, max_connections, max_parallel_workers_per_gather, shared_buffers: [SETTINGS] - Temp file evidence: pg_stat_database.temp_files / temp_bytes, or log_temp_files log lines, for the relevant database: [TEMP STATS] - Total server RAM and the concurrency profile (typical and peak active backends): [HOST / LOAD] Work through: 1. **Locate every spilling node** — in the plan, flag each Sort with "Sort Method: external merge Disk:", each Hash/Hash Join reporting more than one Batch, and any spilling HashAggregate or GroupAggregate. For each, state how much memory it would have needed to stay in RAM (roughly the Disk: size, and for hashes remember the budget is work_mem * hash_mem_multiplier). 2. **Explain the true multiplier** — count the concurrent memory-consuming nodes and the number of parallel workers (each worker gets its own work_mem per node). Compute the realistic worst-case memory for ONE execution of this query, then multiply by expected concurrency to show what a global work_mem bump would cost at peak. 3. **Prefer targeted fixes over a global bump** — recommend, in order: (a) reduce rows to sort/hash (better indexes, added WHERE filters, pre-aggregation), (b) a session- or transaction-scoped SET work_mem for this query/report path only, (c) a per-role ALTER ROLE ... SET work_mem for the reporting/ETL user, and only last (d) a modest global change with the concurrency math shown. Give the exact SET / ALTER statements. 4. **Tune hashes specifically** — if the spills are hash nodes, consider raising hash_mem_multiplier rather than work_mem so sorts aren't over-provisioned too. Output format: (a) a spill table [node | type | Disk/Batches | mem needed to stay in RAM], (b) the worst-case memory math (per-query and at peak concurrency), (c) the exact ordered remediation commands, (d) a verification query using pg_stat_database temp_files/temp_bytes. Guardrails: never recommend a global work_mem value without showing peak-concurrency total memory and leaving headroom against RAM. Scope aggressive values to a session or a specific role, not the whole cluster. Re-run EXPLAIN (ANALYZE) to confirm the spill is gone before and after — a plan change can move the bottleneck rather than remove it.
Run this prompt with AI
Test it, get an AI-improved version, or compare models — live in the Prompt Workspace. No copy-paste.
Why this prompt works
Temp file spills are one of the most common causes of “the query is fine in staging but slow in prod”: the sort or hash that fit in memory on a quiet box overflows to disk under real data volume, and the plan silently switches to an external merge. This prompt forces the diagnosis to come from the plan itself — the Sort Method: external merge Disk: and hash Batches: lines — so the fix targets the exact node that is spilling rather than guessing.
Its most important job is to defuse the reflex to “just raise work_mem.” Because work_mem is allocated per sort/hash node, per backend, and per parallel worker, a single global bump can multiply into many gigabytes at peak concurrency and OOM the server. By making the model compute worst-case per-query and peak-concurrency memory before recommending anything, the output stays honest about the real cost.
Finally, it encodes the correct order of remedies — reduce the rows first, then scope the memory increase to a session or role, and only touch the global setting last with the math shown. That keeps aggressive tuning contained to the reporting path instead of destabilizing the whole cluster, and the temp_files/temp_bytes verification step closes the loop so you can prove the spill is actually gone.
Related prompts
-
Postgres Parallel Query Worker Tuning Prompt
Work out why a big analytical query isn't going parallel — or is spawning too many workers and starving everything else — and set the parallel GUCs from evidence in the plan instead of copy-pasted blog values.
-
Postgres postgresql.conf Workload Tuning Prompt
Get a reviewed postgresql.conf tuning plan for your specific hardware and workload — memory, WAL/checkpoint, planner, and autovacuum settings explained one by one, with how to verify each took effect.
-
Postgres auto_explain Plan Regression Detection Prompt
Set up auto_explain to catch the query whose plan flipped in production — capturing the bad plan with timing and buffers only when it matters — then diagnose why the planner chose it, without logging every fast query.
-
Postgres postgres_fdw Federation Design Prompt
Design a postgres_fdw setup that actually pushes down joins, filters, and aggregates to the remote server — instead of dragging whole tables across the network — and get the security and failure modes right.
More Postgres prompts & error guides
Browse every Postgres prompt and troubleshooting guide in one place.
Reading prompts? Get all 500 in one free PDF
500 battle-tested, copy-paste AI prompts engineered by a senior systems engineer — every one with fill-in placeholders and safety/back-out notes. Drop your email and it's yours.
- 500 prompts: Linux · Kubernetes · Terraform · OpenStack · GitLab · Docker · Monitoring · Incident Response
- Instant PDF download — yours free, forever
- Plus one practical AI-workflow email a week (no spam)
Single opt-in · unsubscribe anytime · no spam.