PostgreSQL work_mem Tuning: How I Fixed Slow Queries by Understanding Memory Allocation

Apr 17, 2026

The Problem

I ran a query that sorted 1 million rows and got this in my EXPLAIN ANALYZE output:

Sort Method: external merge  Disk: 17696kB
Execution Time: 273.973 ms

I thought 273ms was acceptable until I learned what “external merge Disk” actually means. PostgreSQL was sorting on disk instead of in memory. Disk operations are orders of magnitude slower than RAM operations.

I set work_mem = '100MB' and reran the query:

Sort Method: quicksort  Memory: 71452kB
Execution Time: 243.031 ms

30ms improvement—about 10% faster. But I almost caused a worse problem by setting work_mem too high globally.

What work_mem Controls

work_mem determines the maximum memory PostgreSQL can use per query operation for:

Sort operations (ORDER BY, DISTINCT, merge joins)
Hash tables (hash joins, hash aggregates)
Bitmap index scans

The key insight I missed: each node in a query plan can allocate its own work_mem. A query with 3 hash joins and 2 sorts could consume 5x the configured work_mem value.

Query Plan Example:
  Hash Join (work_mem allocation 1)
    -> Hash Join (work_mem allocation 2)
    -> Sort (work_mem allocation 3)

Total memory = 3 × work_mem for ONE query

The Formula That Works

EnterpriseDB recommends this starting formula:

work_mem = (Total RAM - shared_buffers) / (16 × CPU cores)

My server specs:

Total RAM: 16GB
shared_buffers (25% of RAM): 4GB
CPU cores: 4

Calculation:

Available memory = 16GB - 4GB = 12GB
work_mem = 12GB / (16 × 4) = 12GB / 64 = 192MB

Why divide by 16? This accounts for concurrent connections. With 4 CPU cores, you might have 16 concurrent queries running. Each needs its own memory allocation.

How I Detected the Problem

I checked my slow queries with EXPLAIN ANALYZE and found these red flags:

-- Sort spilling to disk
EXPLAIN (ANALYZE ON, COSTS OFF) SELECT * FROM large_table ORDER BY column;
-- Output: Sort Method: external merge Disk: XXXkB  <- PROBLEM

-- Hash join in multiple batches
EXPLAIN (ANALYZE ON, COSTS OFF) SELECT * FROM t1 JOIN t2 USING (id);
-- Output: Buckets: 2048 Batches: 16 Memory Usage: 40kB  <- PROBLEM
-- Ideal: Batches: 1 (single batch means in-memory operation)

To catch all disk spills, I enabled logging:

-- Log all temporary file creation
SET log_temp_files = 0;

This logs every time PostgreSQL creates a temporary file on disk—a sure sign work_mem is insufficient.

Trial and Error: Hash Join Example

I tested hash join performance with different work_mem settings:

SET work_mem = '64kB';
EXPLAIN (ANALYZE ON, COSTS OFF) SELECT * FROM t1 JOIN t2 USING (c);

Result:

Buckets: 2048  Batches: 16  Memory Usage: 40kB
Execution Time: 115.790 ms

16 batches means the hash table was split into 16 parts because memory was insufficient. Each batch requires disk I/O.

RESET work_mem;  -- Returns to my calculated value
EXPLAIN (ANALYZE ON, COSTS OFF) SELECT * FROM t1 JOIN t2 USING (c);

Result:

Buckets: 16384  Batches: 1  Memory Usage: 480kB
Execution Time: 63.893 ms

Nearly 2x faster. Single batch means the entire hash table fits in memory.

Common Mistakes I Almost Made

Mistake 1: Setting work_mem too high globally

I initially thought: “More memory = faster queries. Let me set work_mem = 512MB globally.”

This would have crashed my server during peak load. With 20 concurrent connections and complex queries using 5 work_mem allocations each, I could exhaust 100GB of memory on my 16GB server.

20 concurrent connections × 5 work_mem allocations × 512MB = 50GB required
But I only have 16GB total RAM -> OOM crash

Mistake 2: Ignoring the per-node multiplier

A single query can use multiple work_mem allocations:

SELECT *
FROM orders o
JOIN customers c ON o.customer_id = c.id   -- Hash Join (1 allocation)
JOIN products p ON o.product_id = p.id     -- Hash Join (2 allocation)
ORDER BY o.created_at DESC                  -- Sort (3 allocation)

Total memory for this query = 3 × work_mem

Mistake 3: Copying production settings to development

My dev server has 4GB RAM. Using 192MB work_mem from production would be wrong:

Dev: (4GB - 1GB shared_buffers) / (16 × 2 cores) = 3GB / 32 = 96MB

Different hardware needs different calculations.

Per-Query Tuning Strategy

For occasional heavy queries, I don’t raise global work_mem. Instead:

-- Increase work_mem for this session only
SET work_mem = '512MB';

-- Run the heavy query
SELECT * FROM large_table ORDER BY complex_expression;

-- Reset to default
RESET work_mem;

This gives me performance for specific queries without risking global memory exhaustion.

How I Verify My Settings

After applying the formula, I monitor for problems:

-- Check current work_mem
SHOW work_mem;

-- Monitor temporary files in logs
-- Look for entries like: "temporary file: path "base/pgsql_tmp/pgsql_tmp12345"

If I see temporary file creation in logs, I increase work_mem selectively for those specific queries rather than globally.

Why This Matters

The formula accounts for three critical factors:

Concurrent queries: Production servers handle multiple simultaneous requests
Multiple nodes: Complex queries use multiple memory allocations
System stability: Other processes need RAM too

Total RAM = 16GB
├── shared_buffers (4GB) - PostgreSQL cache
├── work_mem pool (12GB / 64 = 192MB per allocation)
├── maintenance_work_mem - for vacuum, index creation
└── OS + other processes - must have headroom

Summary

I learned that work_mem tuning is a balancing act. Too low causes disk spills and slow queries. Too high risks memory exhaustion during concurrent load.

The formula (RAM - shared_buffers) / (16 × CPU cores) gives a safe starting point. Monitor EXPLAIN ANALYZE for “external merge” sorts and “Batches: N > 1” hash joins to identify tuning opportunities.

For occasional heavy queries, use per-session SET commands rather than raising global values. This approach keeps my server stable while getting the performance I need.

Final Words + More Resources

My intention with this article was to help others share my knowledge and experience. If you want to contact me, you can contact by email: Email me

Here are also the most important links from this article along with some further resources that will help you in this scope:

👨‍💻 EnterpriseDB: PostgreSQL work_mem Configuration
👨‍💻 PostgreSQL Documentation: Resource Consumption

Oh, and if you found these resources useful, don’t forget to support me by starring the repo on GitHub!