In the world of PostgreSQL performance tuning, work_mem is one of the most frequently misunderstood parameters. Many administrators assume it represents a total memory limit for a database session or a single query, but in reality, its impact is much more granular and potentially explosive. Understanding how this setting scales with complex SQL statements is the key to balancing high-speed execution with system stability.
Table of Contents
What is work_mem?
The work_mem parameter sets the base maximum amount of memory that an individual query operation—such as a sort or a hash table—can consume before PostgreSQL is forced to write data to temporary disk files. While writing to disk (often called “spilling”) prevents the system from crashing due to memory exhaustion, it is significantly slower than processing data in RAM.
The Hidden Multiplier: Multiple Operations
The most critical takeaway is that work_mem is not a per-query limit. A single complex query often involves multiple operations simultaneously, such as several joins and a final sort. Each of these nodes in the execution plan is allowed to use the full work_mem allocation. Consequently, a single connection could easily consume many times the value of work_mem at its peak.
Amplification through Parallelism
When PostgreSQL utilizes parallel query, the memory demand scales even further. Resource limits like work_mem are applied individually to each worker process. For example, a query using four background workers and one leader process could theoretically utilize five times the memory of a serial query, as each process manages its own set of sorts and hashes.
Practical Examples of Memory Consumption
Example 1: The Multi-Join and Sort Scenario Consider a query that joins three large tables and sorts the final result:
SELECT * FROM orders o
JOIN customers c ON o.customer_id = c.id
JOIN products p ON o.product_id = p.id
ORDER BY o.order_date;
If the execution plan for this query uses two Hash Joins and one Sort node, and work_mem is set to 64MB:
- Hash Join 1: Up to 128MB (64MB *
hash_mem_multiplierof 2.0). - Hash Join 2: Up to 128MB.
- Sort Node: Up to 64MB.
- Total Peak RAM: This single query could consume 320MB of local memory.
Example 2: Parallel Query Impact If you run a heavy aggregation on a partitioned table using four parallel workers:
SELECT category, SUM(sales) FROM large_sales_table GROUP BY category;
If this plan involves a Parallel Hash Aggregate, every worker process and the leader can allocate their own memory for the hash table. If work_mem is 100MB, the system might allocate roughly 500MB (1 leader + 4 workers) to satisfy this single statement.
How to Audit and Adjust
To determine if your work_mem is set correctly, you should use EXPLAIN ANALYZE on your most complex queries.
- Check for Disk Spilling: Look for the phrase “External sort Disk” or “Batches” in the output. If these appear, the operation exceeded your
work_memand was forced to use slow disk I/O. - Verify Memory Usage: The sort node will explicitly state the Memory used (e.g., “Memory: 74kB”) if the operation completed entirely in RAM.
- Real-Time Diagnostics: If a connection seems to be hogging RAM, you can use the function
pg_log_backend_memory_contexts(PID)to dump a detailed breakdown of that process’s memory usage into the PostgreSQL server logs.
Summary Table: Memory Limits by Operation
| Operation Type | Memory Limit Formula | Key Configuration |
|---|---|---|
Standard Sort (ORDER BY, DISTINCT) | work_mem | |
| Hash-based (Hash Join, Hash Agg) | work_mem * hash_mem_multiplier | |
| Parallel Query | (Workers + 1) * Operation Limit | |
Maintenance (CREATE INDEX, VACUUM) | maintenance_work_mem |
Final Recommendation: Be conservative with the global work_mem setting to avoid the Linux OOM (Out of Memory) Killer. If specific reporting queries require more RAM, it is safer to increase the limit for just that session using SET work_mem = '256MB'; rather than changing it for the entire cluster.
See also: Mastering the Linux Command Line — Your Complete Free Training Guide
