Parameter · Resource Usage / Memory
work_mem
Sets the maximum memory to be used for query workspaces. This much memory can be used by each internal sort operation and hash table before switching to temporary disk files.
- Default:
4MB - Type: integer (kB)
- Range: 64kB … max (2147483647)
- Applies: Session (any user)
In plain words
When a query sorts rows or builds a hash table, it does that in memory up to work_mem. If the data is bigger, PostgreSQL writes the overflow to temporary files on disk, which is much slower.
The catch: the limit is per operation, not per query or per connection. One complex query can run several sorts and hashes at once, in several parallel workers, in many sessions.
For DBAs and operators
Used by sort nodes (ORDER BY, DISTINCT, merge joins, window functions), bitmap heap scans (the TID bitmap; it becomes "lossy" instead of spilling), and, multiplied by hash_mem_multiplier, by hash joins, hash aggregation, memoize and hashed subplans. Each parallel worker gets its own allowance.
Observe spills
EXPLAIN (ANALYZE, BUFFERS) ...
-- Sort Method: external merge Disk: 102400kB (spilled)
-- Sort Method: quicksort Memory: 2048kB (fit)
-- Buckets: 65536 Batches: 8 Memory Usage: ... (hash spilled)
SELECT datname, temp_files, pg_size_pretty(temp_bytes)
FROM pg_stat_database;With log_temp_files = 0 every spill is logged: LOG: temporary file: path "base/pgsql_tmp/pgsql_tmp12345.0", size 104857600. pg_stat_statements has temp_blks_written per query.
Sizing
Rough ceiling: RAM left after shared_buffers ÷ (active connections × ~2–3 operations). Keep the global value modest (8–64 MB is common on OLTP) and raise it per role or per query: ALTER ROLE reporting SET work_mem = '512MB' or SET LOCAL work_mem in a transaction. Raising it can also change plans (the planner prefers hashing when it fits). Setting it to gigabytes globally is the classic cause of OOM-killer events; the kernel kills a backend and the postmaster then restarts all sessions (LOG: server process (PID 1234) was terminated by signal 9: Killed).
How to change it
Any user can change it for their session with SET.
See it in context
Local (per-backend) memory Join methods Life of a query Memory architecture The big picture Client backend Scan methods
Related parameters
hash_mem_multiplier maintenance_work_mem temp_file_limit log_temp_files max_parallel_workers_per_gather