Config - Too small work_mem

Impact

The Postgres work_mem setting controls how much memory individual sort and hash operations can consume. These are used while executing queries to evaluate sorts, joins, and other query features. If set too low, can cause queries to create temporary files on disk more often, which will affect performance.

If you have log_temp_files set to an appropriate value (or 0 to log all temporary files) you can see the temporary files created, split by query, in Log Insights. Increasing work_mem would likely improve performance for these queries.

Solution

The ideal work_mem setting depends heavily on your workload, but pganalyze uses the following rule of thumb:

• start with dividing shared_buffers by max_connections
• use no less than 1MB
• use no more than 256MB

Update the setting to the recommended value by using ALTER SYSTEM or modifying the parameters in your cloud provider portal.