PostgreSQL 10 High Performance - Third Edition

work_mem

If you are sorting data, work_mem determines when those sorts are allowed to execute in memory and when they have to swap to disk instead. Starting in PostgreSQL 8.3, you can turn on log_temp_files and see all the cases where work_mem was not large enough, and the external merge Disk sort is used instead. Note that you may be confused to see such usage appear in the logs even though the value shown is smaller than work_mem. An example and the reason behind why that happens is explained in the Sort section seen earlier.

Setting a value for this parameter is tricky. Ideally, you'd like it to be large on a system with plenty of memory, so that sorts happen quickly. But every client can use this much memory for each sort node in a query ...