Give your Postgres Queries More Memory to Work With

Are you getting notices from Heroku warning that you are "Running out of temp space on your Postgres add-on"?

A potential fix to this is adjusting the work_mem value for the database. This will require some tuning to optimize the database for the particular query load that it experiences. So, some trial and error.

In this article, we'll walk through how to check your database's work_mem value and how to change it to a better value that will make this warning go away.

work_mem "specifies the amount of memory to be used by internal sort operations and hash tables before writing to temporary disk files." (source)

Before getting started, you'll want to take note of the specs of your Postgres instance where these errors occur. You'll be interested in both the available RAM and the number of supported connections.

Making More work_mem

The work_mem value tells Postgres how much RAM to let a query consume before starting to write intermediate results to the temporary disk. Giving queries permission to use more RAM is one way of reducing the amount of temporary space that gets written to. This will need to be balanced against how much RAM is being consumed overall. The work_mem value is likely set to the default of 4MB (4 megabytes) -- unless it isn't.

You can check this by running the following query in a psql session connected to the target database:

> show work_mem;
 work_mem
----------
 4MB
(1 row)

You can start by altering it to a slightly higher value. Too high and you could flip from "out of temp space" to "out of memory". Depending on the RAM size and number of connections, you could try going anywhere from 8MB or 16MB up to 64MB. This will be highly dependent on your data and query load, so some trial and error may be necessary.

Here is how you can apply that change from a psql session:

> alter database <database-name> set work_mem = '16MB';
ALTER DATABASE

It has altered the database, but that change will be applied only to new sessions. If you check the work_mem for the existing session, it will appear as if it hasn't changed.

> show work_mem;
 work_mem
----------
 4MB
(1 row)

Exit this psql session and start a new one to check that value again. You show now see that it has been updated to what was specified in the alter statement.

> show work_mem;
 work_mem
----------
 16MB
(1 row)

Unlike some config changes, this is applied immediately to new connections. There is no need to restart the Postgres server. This change will persist between restarts as well.

So, how do you pick the right work_mem value for your database and its query load?

Some rough math on picking a value:

If you have 8GB of RAM and you bump work_mem up to 16MB, then it would take 512 connections all maxing out their work_mem space to cause an out of memory error. Whereas if you bumped it to something like 64MB, then you're looking at ~128 connections.

Again, these are really rough numbers and also doesn't account for base RAM needs of the Postgres instance. It will all depend on the query load that the DB experiences. Depending on how this change performs, you can make further adjustments either up or down to dial in a work_mem value that makes most sense.

If you enjoy my writing, consider joining my newsletter or following me on twitter.

Notes

If you are using a managed database service like Heroku, the default setting for your work_mem value may be dependent on your plan. For instance, Heroku's Standard 4 plan has a default work_mem value of 110MB. source

Resources and Further Reading

• My Heroku Postgres instance is running out of temporary space
• Configuring work_mem on Postgres
• PostgreSQL Docs for work_mem
• Understanding Temp Files in Postgres
• How We Solved a Storage Problem in PostgreSQL Without Adding a Single Byte of Storage

Cover photo by Harrison Broadbent on Unsplash