Your PostgreSQL work_mem Is a 400 GB Memory Bomb

Here is a setting that looks completely innocent: work_mem, set to one

gigabyte. On your laptop, it is perfect. But put it on a busy production

server, let fifty sessions hit it at once, and that one innocent line

Meet the query we'll follow the whole way through, just a normal revenue

report, nothing exotic about it. With work_mem at a reasonable thirty-

two megabytes, the sort is simply too big to fit in memory, so

Now here is the trap that catches almost everyone. You would assume

work_mem is one shared pool that the server carefully divides up, but it

is not, not even close. It gets handed out fresh to every single

So you do the natural thing, the thing we have all done at some point.

You bump work_mem up to a full gigabyte for the whole server, you

reload, and you run the report again. And it is beautiful, the sort

Because here is the cruel part: it sails through every test you will

ever run. Alone in development, one session, about a gig of memory,

absolutely flawless. Then Friday at five o'clock rolls around, fifty

And the math behind it is brutally simple. Take your sessions, times the

operations in each query, times the parallel workers, times work_mem,

and on this server that lands on a four hundred times multiplier. So

So let's do this properly, and it all begins with reading the plan. The

moment you see the sort method listed as external merge, with a disk

size sitting right next to it, PostgreSQL is basically waving a flag,

Want to know whether it is just this one query, or a server-wide habit?

Take a look at pg_stat_database. The temp_files and temp_bytes columns

tell you exactly how much the whole server has been spilling to disk,

So the real fix is almost boringly simple: pick a low global value, and

then leave it completely alone. Sixteen megabytes for high concurrency,

thirty-two as a comfortable everyday default, sixty-four if you run

And when one heavy report genuinely needs more, you give it more, but

only inside its own session. You set work_mem to five hundred and twelve

megabytes, you run the report, and the instant it is done, you reset it.

And now, the payoff. Same query, same data, and the only thing that

changed is that one session. Before, we had an external merge, five

hundred and forty-two megs dumped to disk, nine thousand eight hundred

Of course, the perfect strategy depends on your workload. For OLTP, keep

that global low and basically never touch it. For reporting like ours,

stay low globally but bump it per session and reset. Batch jobs usually

Now let me save you some pain, because there are four classic ways this

blows up. Setting a full gig globally. Changing work_mem without ever

glancing at an EXPLAIN plan. Forgetting that parallel workers quietly

So here is your playbook, from start to finish. Find the spills, in the

plan and across the server. Set a sensible low global from your

concurrency. Raise it per session for the rare heavy job, and always

And if you take just one thing from this whole video, make it this: low

globally, high only for controlled sessions, then reset. work_mem was

never a shared pool, so once you respect that multiplier, one careless