work_mem sets non-shared memory available to an individual PostgreSQL operation. The 4 MB default is intended to let PostgreSQL run on very small machines, not to suit most production workloads. Raising it can help operations that need more memory, but it is not a blanket performance setting: workload shape and concurrency determine whether additional allocation helps or harms. section 6.1 section 6.2
A common mistake is to raise work_mem aggressively after seeing a slow workload. In one benchmark, increasing shared_buffers and setting work_mem to 2 GB did not improve throughput at 100 clients; under 1,000 clients, the test stalled and Linux’s OOM killer terminated a PostgreSQL process. Memory allocation must therefore be evaluated under realistic concurrent load, not tuned from a single-query intuition. section 6.2
Treat work_mem as a workload-sensitive capacity decision:
- Start from a tested production configuration rather than assuming the default is appropriate. section 6.1
- Benchmark from another host when possible, so the test client does not become the bottleneck on the database server. section 6.2
- Increase concurrency during testing; a setting that appears harmless at moderate load can exhaust system memory under heavy load. section 6.2
- Keep watching for disk-backed temporary files, which can indicate that query work did not fit in work_mem. section 7.3.1
Diagnosing a value that is too low
With log_temp_files enabled, a temporary-file log entry attached to an ORDER BY query is evidence that PostgreSQL wrote query work to disk. This may indicate that work_mem is too low for that query. Because disk has far higher latency and lower speed than memory, repeated spills can severely affect performance; investigate the query and its workload before simply raising the global setting. section 7.3.1
2024-09-21 13:51:15.133 BST [3226458] frogge@frogge LOG: temporary file:
path "base/pgsql_tmp/pgsql_tmp3226458.0", size 8437760
2024-09-21 13:51:15.133 BST [3226458] frogge@frogge STATEMENT: SELECT *
FROM erp.payments WHERE tstamp >= date_trunc('y', now()) ORDER BY
amount;
Inspect the temporary-file entry and the associated ORDER BY statement to connect disk spill evidence with a query that may not fit in work_mem. section 7.3.1
work_mem is distinct from shared_buffers, which supplies memory for PostgreSQL buffers, and maintenance_work_mem, which supports operations such as CREATE INDEX and VACUUM. These settings should not be enlarged together by default: the benchmark shows that allocating more memory did not automatically improve TPS, while excessive allocation under load created an availability risk. section 6.1 section 6.2
