The one thing to understand first
work_mem is the memory budget for a single sort, hash, or similar operation — not per query and not per connection. A complex query can have many such operations running at once, each entitled to its own work_mem. With parallel workers, each worker also gets its own allotment. This multiplicative behaviour is why a seemingly modest work_mem can still exhaust RAM under load.
work_mem is charged per operation, not per query — so the real ceiling is work_mem × concurrent nodes × parallel workers. Set it too low and sorts and hashes spill to temp files; set it too high and a burst of concurrency OOMs the server. The whole skill is “modest global, generous local.”
Sorts: quicksort vs external merge
The sort code (src/backend/utils/sort/tuplesort.c) keeps tuples in memory and quicksorts them if they fit in work_mem. If they do not, it switches to an external merge sort: it writes sorted runs to temporary files and merges them. EXPLAIN ANALYZE shows the difference directly: