Let's say you are experiencing a Postgres error that reads like this: "Postgres running out of temp space".
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.
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.
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.
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 try adjusting it up a bit. 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 up to 8MB or 16MB.
Here is how you can apply that change from a psql session:
> alter database <database-name> set work_mem = '16MB';
ALTER DATABASEIt 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
----------
8MB
(1 row)Exit this psql 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)Though you likely won't have the load conditions to see the full impact, it is always good to try out changes like this in a staging environment first. This will give you a chance to go through the motions and build some confidence around the sequence of commands.
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.
Some helpful articles: