PostgreSQL work_mem: A Guide to Memory Tuning

PostgreSQL work_mem: Optimizing Query Performance

The work_mem setting in PostgreSQL controls the amount of memory allocated for internal operations such as sorting and hash joins during query execution. Tuning this parameter can significantly improve performance for resource-intensive queries.

Syntax for Setting work_mem

You can set the work_mem parameter either temporarily for the current session or globally in the configuration file:

1. Set work_mem for a Session:

SET work_mem = '4MB';

2. Update work_mem in postgresql.conf:

work_mem = 4MB

Examples of Using work_mem

Example 1: Adjusting work_mem for a Session

Code:

-- Set work_mem for the current session
SET work_mem = '8MB';

-- Example of a query utilizing work_mem
SELECT * 
FROM large_table
ORDER BY column_name;

Explanation:

• Increasing work_mem helps with sorting large datasets without spilling to disk.
• Use session-level settings for specific queries requiring additional memory.

Example 2: Query Impact with work_mem

Code:

-- Default work_mem (e.g., 4MB)
SET work_mem = '4MB';
EXPLAIN ANALYZE SELECT * FROM large_table ORDER BY column_name;

-- Increased work_mem (e.g., 16MB)
SET work_mem = '16MB';
EXPLAIN ANALYZE SELECT * FROM large_table ORDER BY column_name;

Result:

• With higher work_mem, the query completes faster as fewer operations spill to disk.

Example 3: Setting Global work_mem

Code:

-- Update in postgresql.conf
work_mem = 16MB

-- Reload the configuration
SELECT pg_reload_conf();

Note:

• Changes in postgresql.conf apply to all sessions unless overridden.

Explanation of code Examples:

• Temporary Adjustments: Use the SET command to adjust work_mem for specific operations without affecting the global configuration.
• Query Performance: By increasing work_mem, queries that involve sorting, hashing, or large joins can execute faster.
• Global Configuration: Modify the postgresql.conf file to set a global default for all database users.

All PostgreSQL Questions, Answers, and Code Snippets Collection.