Section 3 of 3 · 7 minutes
Budget memory for concurrent work
Your goal
What you will be able to do
Estimate how per-operation memory settings can multiply across queries and sessions.
Why this matters at work
The practical reason
A work_mem value that is safe for one sort can exhaust memory when many queries run several sorts at once.
Learn
The idea in plain English
`shared_buffers` is a shared cache. `work_mem` is different: a query can use it for each sort or hash operation, and parallel workers can multiply the total again.
Do not calculate memory as `work_mem × max_connections` alone, but do treat concurrency and plan shape as the real budget. Prefer a lower global value and targeted session changes for controlled workloads.
Remember these points
- work_mem applies per plan operation, not once per server.
- Parallel workers can increase a query's memory use.
- Measure temporary files and concurrent plans before changing the global value.
See it in SQL
Find databases creating temporary files
Identify workloads whose sorts or hashes spill beyond available memory.
SELECT
datname AS database_name,
temp_files,
pg_size_pretty(temp_bytes) AS temporary_bytes
FROM pg_stat_database
WHERE temp_files > 0
ORDER BY temp_bytes DESC;Temporary bytes are cumulative since the statistics reset. Record that observation window before comparing databases.
Spills can justify query or memory investigation, but a global increase still requires a concurrency budget.
Databases with temporary-file activity, ordered by total bytes written.
Useful words
Important terms
- Temporary spill
- Work written to temporary disk files when an operation cannot stay in memory.
Quick check · Not graded
Check your understanding
Why can a large work_mem value be dangerous?
Your progress is saved to your signed-in account.