Give your Postgres Queries More Memory to Work With
Tags: postgres, database, performance, devops
Published: 2021-06-10 23:41:17

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](https://www.postgresql.org/docs/current/runtime-config-resource.html#GUC-WORK-MEM))

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](https://twitter.com/jbrancha/status/1405218428017852419?s=20).

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

```sql
> 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:

```sql
> 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.

```sql
> 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.

```sql
> 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](https://crafty-builder-6996.ck.page/e169c61186) or following me on [twitter](https://twitter.com/jbrancha)._

## 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](https://twitter.com/jbrancha/status/1405218428017852419?s=20)

## Resources and Further Reading

- [My Heroku Postgres instance is running out of temporary space](https://help.heroku.com/1I3XBKMT/my-heroku-postgres-instance-is-running-out-of-temporary-space)
- [Configuring `work_mem` on Postgres](https://www.citusdata.com/blog/2018/06/12/configuring-work-mem-on-postgres/)
- [PostgreSQL Docs for `work_mem`](https://www.postgresql.org/docs/current/runtime-config-resource.html#GUC-WORK-MEM)
- [Understanding Temp Files in Postgres](https://adriennedomingus.com/blog/understanding-temp-files-in-postgres)
- [How We Solved a Storage Problem in PostgreSQL Without Adding a Single Byte of Storage](https://hakibenita.com/how-we-solved-a-storage-problem-in-postgre-sql-without-adding-a-single-bytes-of-storage)

---

<em>Cover photo by <a href="https://unsplash.com/@harrisonbroadbent?utm_source=unsplash&utm_medium=referral&utm_content=creditCopyText">Harrison Broadbent</a> on <a href="https://unsplash.com/s/photos/memory?utm_source=unsplash&utm_medium=referral&utm_content=creditCopyText">Unsplash</a></em>