PostgreSQL work_mem Calculator

If you’ve ever heard of work_mem, you may have assumed that it’s simply a one-off value you enter into postgresql.conf and move on. Nope! It’s a multiplier, and arguably the most dangerous PostgreSQL configuration parameter, since it multiplies by the amount of concurrent activity on your system. Each query gets its own portion of RAM for aggregations, sorts, and hash joins.

Trouble occurs when lots of user is running resource-intensive queries concurrently and the database attempts to consume more RAM than actualy available. Out-of-memory errors result, which in turn stall your applications.

How to Set Work_Mem in PostgreSQL

To do this, just enter your node count and concurrency into calculator above, and it will do the work for you. There’s no need to multiply together multiple variables in your head, which never line up anyway in real world. Most folks freak out when they look at max_connections and realize they have hundreds of connection, so they must reserve lots of memory.

But that ignores the fact that most of those connections aren’t doing anything; and you only want to care about the ones that are actually running at any given time. So instead of asking how much capacity there could be, it asks how many active query are running at same time. It makes you consider reality (what happens on your busiest hour) versus theory (theoretical maximums).

Why does that matter? To understand that, we need to look under the hood at a query plan. For a basic select statement, there may be nothing special about it except what comes out of indexes. Adding something like a group by or order by clause will add some sort or hash nodes to the plan and these nodes claim memory (i.e. Work_mem). If your query has three such node, it could easily triple amount of memory used before completing.

The calculator allows you to account for this by defining how many memory nodes per query you tend to see. You need to look at real execution plans to know whether your reports use two memory nodes or five, which means that experience trumps guessing here as well.

And then you have the operating system reserve. Don’t forget Linux also require memory! Keeping the commonly used pages out of the shared_buffers (via the kernel cache) really helps PostgreSQL. Filling every byte with db settings will starve the OS and hurt performance. By including an OS reserve and some memory for maintenance such as auto vacuum workers, we make sure you’re calculating based off something real world. It leaves headroom so background tasks like this can run and keep the system healthy.

When a node’s available memory runs out, it will write excess to disk in what’s called “spill” files. This kills query speed because disk is slower than RAM. Based on your settings for tolerance and temp file rate, this calculator estimates spill risk. Small spills can hurt performance of latency sensitive API requests; occasional spills can be fine for batch reports as long as it doesn’t crash the server. Knowing which camp you fall into will change how aggressively you tune.

Instead of setting work_mem globally, you should set it as high as needed for certain sessions/roles and lower otherwise. That way, tasks that can do some heavy lifting will have room to breathe, but you avoid having a runaway query using up all the memory. The page also has a useful table showing typical workload types and their recommended settings (ranging from loose to strict). For instance, an ETL pipeline could afford to be more generous with higher values for fewer large jobs than a read-heavy API serving a lot of smaller requests.

Because memory pressure varies with your query workload, and data volume increases over time, tuning is a continuous process. Monitor your temp file log; a spike in disk usage indicates something has gone wrong. Re-check the plans. Were some indexes missed? Are stats out of date? Increasing work_mem may help, but usually this simply hides the real problem. You want to use only what you need while maintaining consistent performance.

This isn’t so much about configuration as budgeting: You only have so much money. Should you place it into a single high-risk investment vehicle? Or should you invest some in each, making sure the lights stay on? Likewise, work_mem is how much you allocate per query. Allocate too little and they’ll crawl, disk spills! Allocate too much and… well, then you don’t really have any left.

That balance is what keeps your server responsive and your users happy. When the database runs out of room, it stops working, and that’s not fun.