How I Finally Stopped the Postgres Disk Spill: A Journey into the work_mem Variable
Your queries are fast in development but crawl in production because Postgres is secretly swapping RAM for disk during large sorts.
Production has a funny way of making your "perfect" SQL look like a toddler's finger painting. Everything runs in sub-millisecond time on your laptop with a few thousand rows, but the moment you hit the real world, things get weird.
I recently spent a few days chasing a query that was behaving like a moody teenager. On Tuesday, it was fine. On Wednesday, after a few million more rows hit the table, it took 12 seconds to run. The indexes were there. The CPU wasn't pegged. But the query was crawling.
The culprit? Postgres was quietly dumping data onto the disk because it ran out of "scratchpad" space in the RAM.
The Secret "Disk Spill"
When you ask Postgres to do something complex—like a DISTINCT, an ORDER BY, or a JOIN—it needs a place to shuffle those rows around. This space is governed by a configuration variable called work_mem.
By default, Postgres is incredibly conservative. In many installations, work_mem is set to 4MB.
If your sort operation needs 5MB of space and your limit is 4MB, Postgres doesn't just "try its best" in memory. It panics (quietly) and starts writing temporary files to the local disk. This is called an "External Merge Disk" sort. And as we all know, even the fastest NVMe SSD is a snail compared to DDR4 RAM.
How to Catch Postgres in the Act
You can’t just look at the query timing; you have to look under the hood. Use EXPLAIN (ANALYZE, BUFFERS).
EXPLAIN (ANALYZE, BUFFERS)
SELECT user_id, count(*)
FROM large_activity_log
GROUP BY user_id
ORDER BY count(*) DESC;When I ran this on my slow query, the output looked something like this:
Sort Method: external merge Disk: 15424kB
Buffers: shared hit=421, temp read=1928 written=1928There it is. "External merge Disk." My database was doing manual labor on the hard drive for a measly 15MB of data.
The "Fix" (and Why You Should Be Careful)
The temptation is to jump into postgresql.conf and crank work_mem up to 1GB and call it a day. Don't do that.
work_mem isn't a global cap for the whole database. It is allocated per operation, per query. If a single complex query has four parallel joins and three sorts, it could potentially use work_mem seven times over. If you have 100 concurrent users doing that... well, your OOM (Out of Memory) killer is going to have a very busy afternoon.
Level 1: The Local Fix
The safest way to handle this is to change it only for the session or the specific transaction that needs it.
BEGIN;
-- Set it to 64MB just for this connection
SET LOCAL work_mem = '64MB';
SELECT user_id, count(*)
FROM large_activity_log
GROUP BY user_id
ORDER BY count(*) DESC;
COMMIT;By doing this, you aren't risking the stability of the entire server. You're just giving this one heavy lifter a bigger shovel.
Level 2: Target the User
If you have a specific background worker or a "reporting user" that constantly runs heavy analytics, you can set the memory limit specifically for them.
ALTER USER reporting_service SET work_mem = '128MB';Level 3: The Global Adjustment
If you've looked at your logs and realized *every* query is spilling to disk, you can change the global setting. A common rule of thumb is:(Total RAM * 0.25) / max_connections.
If you have 16GB of RAM and 100 max connections, maybe set it to 32MB or 64MB.
-- Check your current value first
SHOW work_mem;
-- Change it globally (requires a reload or restart depending on the environment)
ALTER SYSTEM SET work_mem = '32MB';
SELECT pg_reload_conf();How Much is Enough?
You don't want to over-allocate. To find the "Goldilocks" zone, look back at that EXPLAIN ANALYZE output. If it says it's spilling 15MB to disk, setting work_mem to 24MB or 32MB will usually be enough to keep the entire operation in memory.
After I bumped my session to 64MB, the EXPLAIN plan changed to:
Sort Method: quicksort Memory: 18240kBThe execution time dropped from 12 seconds to 800ms. Same query, same hardware, just less "disk shuffling."
The Takeaway
Postgres is a bit like a cautious accountant. It doesn't want to waste your RAM, so it defaults to the bare minimum. But modern apps aren't "bare minimum" anymore.
If your queries feel sluggish and your indexes are fine:
1. Run EXPLAIN (ANALYZE, BUFFERS).
2. Look for External merge Disk.
3. Incrementally increase work_mem for that session.
4. Watch your performance return from the dead.
Just remember: RAM is fast, but it’s shared. Don't let one greedy query starve the rest of your system.
