Our order database runs on Postgres 15 on a virtual machine with sixty four gigabytes of memory, sixteen of them for shared_buffers. Last spring the finance team complained that their Monday reports were slow, and we raised work_mem from sixteen megabytes to five hundred and twelve for everyone, which made them fast.
On the first Monday of September, at five past nine, every application connected to that database lost its connections at once. Checkout returned errors for about forty seconds and then recovered by itself. The Postgres log said a server process had been terminated by signal 9, Killed, and that it was terminating any other active server processes. The kernel log named the process the OOM killer had chosen: a Postgres backend with forty one gigabytes resident. It happened again in October.
work_mem is not a limit per query. It is what each sort or hash operation may use before spilling to disk, a single query can run many of those at once, each parallel worker gets its own allowance, and since Postgres 15 a hash operation may by default use twice work_mem. Finance's revenue report had nine hash joins and four parallel workers. Linux, with default overcommit, had granted every allocation and found there was not enough memory only when the pages were touched. The OOM killer then chose the process with the highest score, the report.
Postgres cannot know whether a backend killed by signal 9 left shared memory in a bad state, so it assumes it did. The postmaster ends every other backend and runs crash recovery, so killing one report killed every connection.
We set vm.overcommit_memory to 2, as the Postgres documentation recommends, with an overcommit ratio sized for the machine. The kernel now refuses an allocation it cannot back, and Postgres reports out of memory to the one query that asked for too much. work_mem went back to sixteen megabytes for everyone and two hundred and fifty six for the reporting role only, set with ALTER ROLE, and the reports moved to a replica. The postmaster also runs with an OOM score adjustment that keeps the kernel from ever choosing it.
The query that asked for too much memory was never going to finish. What we had not understood was how it would fail: not as an error for finance, but as forty seconds of outage for everyone else.
– Sergey Shinder
