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.
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;
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
> alter database <database-name> set work_mem = '16MB';
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;
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
> show work_mem;
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.
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 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
- My Heroku Postgres instance is running out of temporary space
- PostgreSQL Docs for
- Understanding Temp Files in Postgres
- How We Solved a Storage Problem in PostgreSQL Without Adding a Single Byte of Storage