PostgreSQL’s work_mem parameter is one of the most impactful yet misunderstood configuration settings for database performance. This post explores how adjusting work_mem can dramatically improve query execution times, especially for operations involving sorting, hashing, and joins.

Understanding work_mem

work_mem specifies the amount of memory PostgreSQL can use for internal sort operations and hash tables before writing to temporary disk files. The default value is 4MB, which is conservative to ensure PostgreSQL runs on smaller machines.

When work_mem matters

  • Sort operations: ORDER BY, DISTINCT, merge joins
  • Hash operations: Hash joins, hash-based aggregation, IN subqueries
  • Window functions: Some window function operations

When data exceeds the work_mem limit, PostgreSQL switches to disk-based algorithms (external merge sort), which are significantly slower than in-memory operations.

The problem: disk-based sorting

With insufficient work_mem, queries may show:

Sort Method: external merge Disk: 280kB

This indicates the sort operation spilled to disk, causing:

  • Increased execution time
  • Higher I/O write spikes (even for read-only queries)
  • Reduced throughput under load

Practical example: soccer player statistics

Consider a system analyzing soccer player performance with the following schema:

CREATE TABLE players (
  player_id SERIAL PRIMARY KEY,
  nationality TEXT,
  age INT,
  position TEXT
);

CREATE TABLE matches (
  match_id SERIAL PRIMARY KEY,
  match_date DATE,
  home_team TEXT,
  away_team TEXT
);

CREATE TABLE player_stats (
  player_stat_id SERIAL PRIMARY KEY,
  player_id INT REFERENCES players(player_id),
  match_id INT REFERENCES matches(match_id),
  goals INT,
  assists INT,
  minutes_played INT
);

A query to find top 2000 players by total score:

SELECT p.player_id, SUM(ps.goals + ps.assists) AS total_score
FROM player_stats ps
JOIN players p ON ps.player_id = p.player_id
GROUP BY p.player_id
ORDER BY total_score DESC
LIMIT 2000;

Analyzing with EXPLAIN

Use EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS) to understand query execution:

BEGIN;
SET LOCAL work_mem = '64kB';
EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS)
SELECT p.player_id, SUM(ps.goals + ps.assists) AS total_score
FROM player_stats ps
INNER JOIN players p ON p.player_id = ps.player_id
GROUP BY p.player_id
ORDER BY total_score DESC
LIMIT 2000;
COMMIT;

With work_mem = 64kB (too low)

  • Execution time: ~82ms
  • Sort Method: external merge Disk: 280kB
  • Multiple sort operations spill to disk
  • Merge Join used instead of Hash Join

With work_mem = 4MB (default)

  • Execution time: ~42ms (50% improvement)
  • Sort Method: top-N heapsort Memory: 227kB
  • Hash Join used instead of Merge Join
  • All operations fit in memory

Impact on API performance

Load testing with k6 demonstrates the real-world impact:

Test with work_mem = 64kB:

  • Average response time: 108ms
  • p90 latency: 138.63ms
  • Throughput: 63 requests/second

Test with work_mem = 4MB:

  • Average response time: 71.77ms
  • p90 latency: 95.3ms (43ms improvement)
  • Throughput: 95 requests/second (50% increase)

Configuring work_mem

User-level configuration

ALTER USER foo SET work_mem='32MB';

Important: If using connection pools, recycle old sessions for the new configuration to take effect.

Transaction-level configuration

BEGIN;
SET LOCAL work_mem = '32MB';
-- Your queries here
COMMIT;

Memory considerations

A single complex query can consume multiple times the work_mem value because:

  • Multiple sort operations can each use work_mem
  • Hash joins use work_mem (influenced by hash_mem_multiplier)
  • Merge joins may require sorting

Formula to estimate total memory:

Total memory ≈ work_mem × (number of sort operations + hash operations)

Finding the right value

There’s no magic formula. Consider:

  1. Available system memory: Don’t set so high that PostgreSQL runs out of memory
  2. Concurrent connections: Each connection can use work_mem per operation
  3. Query patterns: Analyze your slow queries with EXPLAIN
  4. Workload characteristics: OLTP vs OLAP have different requirements
  1. Start with default (4MB) and monitor
  2. Identify slow queries with disk-based operations
  3. Gradually increase work_mem for specific users/databases
  4. Monitor memory usage and query performance
  5. Test thoroughly before applying globally

Tools and resources

  • TimescaleDB autotune: Automated tuning tool
  • EXPLAIN visualizers: explain.dalibo.com
  • PostgreSQL source code: Understanding internal algorithms

Best practices

  1. Monitor first: Use pg_stat_statements to identify slow queries
  2. Test incrementally: Don’t jump from 4MB to 1GB immediately
  3. Use transaction-level settings: For specific heavy queries
  4. Consider connection pooling: Limit concurrent connections to control total memory
  5. Monitor system memory: Watch for OOM (Out of Memory) errors

Conclusion

The work_mem parameter is a powerful tool for PostgreSQL performance tuning, but it requires careful consideration of your system’s memory, workload, and query patterns. The key is to test, measure, and iterate until you find the optimal value for your use case.

Remember: TEST. TEST TODAY. TEST TOMORROW. TEST FOREVER. Keep testing until you find an acceptable value that enhances query performance without overwhelming your database server.