The work_mem dial
Your sort spills to disk. Give it more memory and it stops. Why does nobody set work_mem to a gigabyte?
One query sorts a million rows, and Postgres gives that sort its own slice of memory, work_mem. Rows that fit are sorted in memory; the rest go to disk as sorted runs and are merged back. Every session gets the same slice at once. Turn the dial and watch spill and the worst case trade places.
Board: The Query Planner →On a narrow screen the three panels stack below the tabs and session slider. The table scrolls sideways.
try this Click The default, then A big desk and The whole room, drag Sessions running it at once, and watch The whole box.
These are teaching values. The query sorts 1,000,000 rows: 100 bytes each in a temporary file and 150 bytes each in memory. The server has 16 GB, 4 GB of it given to shared_buffers, and each copy of the query holds 2 memory-hungry steps at once. The merge rule is Postgres's own, from its sort code.
One query
191 MB
written to temporary files
Sort Method: external merge Disk: 97,656kB
36 sorted runs of 4 MB each went to temporary files, then 2 merge passes read them back, 15 at a time.
About 2.3 s for the sort.
The whole box
0.8 GB
the most these sessions could ask for, of 12 GB free
If every session sorted at once, they could ask for 0.8 GB of the 12 GB left after shared_buffers. It fits.
Largest safe setting at this many sessions: work_mem = 16MB.
The dial, swept
Every setting at the current number of sessions. Read the temporary-file column against the last one. Spill falls in steps, not smoothly, and the row that spills nothing is the row the box cannot afford.
| work_mem | Sorted runs | Merge passes | Temp written | Sort time | Box could need |
|---|---|---|---|---|---|
| 1MB | 144 | 3 | 286 MB | 3.3 s | 0.2 GB |
| 4MB | 36 | 2 | 191 MB | 2.3 s | 0.8 GB |
| 16MB | 9 | 1 | 95 MB | 1.4 s | 3.1 GB |
| 64MB | 3 | 1 | 95 MB | 1.4 s | 13 GB (over) |
| 256MB | in memory | 0 | 0 MB | 400 ms | 50 GB (over) |
At 16 MB the sort writes about 95 MB to temporary files. You raise work_mem to 64 MB, four times as much. How much does it write now?
Sorted runs, then a merge
A sort that fits in work_mem reads its rows into memory, quicksorts them and hands them on. One that does not fit fills its memory, sorts that batch, writes it to a temporary file as a sorted run, and starts the next. When the input ends it merges the runs, reading the front of each and always taking the smallest. A merge can read only so many runs at once, because each needs a buffer in the same work_mem. Too many runs, and it merges them in groups and writes the groups back out, which is a second pass over every row.
That is why spill falls in steps. At 1 MB the sort makes 144 runs and needs 3 passes, writing 286 MB. At 16 MB it makes 9 runs, merges them all at once, and writes95 MB, every row exactly once. At 64 MB it still writes 95 MB: fewer, bigger runs, same one pass. Only at 256 MB, when the whole sort fits, does the disk drop out, and the sort takes400 ms instead of 1.4 s.
The setting is per sort, not per server
work_mem is not a budget the server shares out. Every sort and every hash in every running query may take that much, at the same moment. 100 sessions each running this query at 256 MB could ask for50 GB, on a box with 12 GB to give. Most of the time they will not all sort at once, which is why a large setting can look fine for months. The day they do, Linux's out-of-memory killer picks a process to end, and when that is a Postgres process every connection is dropped while the server recovers. The usual answer is a modest default for everyone and a larger value set for one session, or one role, that runs the big report.
Limits of this model
Teaching values. 1,000,000 rows of 100 bytes on disk and 150 in memory, 400 ms to sort them in memory, and 200 MB/s to the temporary file make the steps easy to read. They are not any machine's speed. The merge-order rule is Postgres's own (work_mem divided by 272 KB, kept between 6 and 500), but a real run count also depends on row width and allocator overhead.
A worst case, not a forecast. The box panel assumes every session uses its full allowance in2 steps at the same moment. Real plans have more or fewer memory-hungry steps, hash steps may use work_mem times hash_mem_multiplier (2 by default), and parallel workers each get their own allowance.
Sorts only. A hash aggregate or hash join that outgrows its memory also spills, in batches rather than runs. The operating system's page cache may hold the temporary files in memory, so a spill can be cheaper than the time column says.