workmem

Work_mem is a PostgreSQL configuration parameter that controls the amount of memory available to a back-end process for internal operations during query execution, such as sorting, hashing, and materialization of intermediate results. The value applies to individual operations and is allocated for the duration of a query or a set of operations within a session.

Units and default: The work_mem value is expressed in kilobytes or megabytes (for example "4MB", "64MB"). The default in current releases is 4MB. Larger values allow more work to be done in memory, potentially reducing disk I/O for large sorts or hash joins, but increase per-backend memory usage and the risk of exhausting RAM under high concurrency.

Configuration and scope: Work_mem can be set in the server configuration file postgresql.conf, or per-session with SET work_mem = '64MB'. It can also be established as a default for a role or database using ALTER ROLE or ALTER USER commands. Changes affect subsequent operations in the session or in new sessions that inherit the specified default.

Impact on performance and planning: Increasing work_mem can speed up queries that rely on sorts or hash operations by keeping more data in memory and avoiding temporary disk files. However, because memory is allocated per operation and per active backend, overly large values can lead to higher overall memory usage and potential memory contention on busy systems. On systems with many concurrent connections, careful sizing is advised.

Relation to other memory settings: work_mem is distinct from maintenance_work_mem, which governs resources used for maintenance operations like VACUUM and CREATE INDEX. It operates alongside shared buffers and available system RAM; effective planning balances work_mem with total RAM, other PostgreSQL memory settings, and workload characteristics.