Fine-tune work_mem in PostgreSQL to get the best performance. 

Our Support Team is here to demonstrate how to use work_mem to get the most out of it.

Today, at Bobcares, we answer similar queries 24/7 as a part of our Server Management Services. Let’s take a look at how our in-house experts handle fine-tuning work_mem.

How to use work_mem in PostgreSQL?

Interestingly, work_mem is one of the most confusing settings in PostgreSQL. We have had a number of queries from our customers about this setting.

We can describe work_mem as a setting in PostgreSQL that is responsible for determining the amount of memory for certain operations.

While this may seem straightforward enough, leaving it unconfigured can lead to a host of issues. Furthermore, sometimes tuning work-mem can lead it to behave in a puzzling manner at times.

Setting the default memory with work_mem in PostgreSQL

By default, the work_mem value is set to 4 MB in PostgreSQL. In other words, each PostgreSQL activity can consume up to 4 MB, after which they have to use the disk. Unfortunately, writing temp files to disk, making things a lot slower.

You can verify whether your PostgreSQL activity is spilling to the disk by looking for temporary files within the PostgreSQL logs. If you notice temporary files increasing your work_mem can be worthwhile.

According to our Support Techs, you can set 64 MB as the default if you prefer a one size fits all solution.

Interestingly, you can automatically tune your work_mem requirement based on the overall memory available by using Hyperscale. It is a built-in option for PostgreSQL managed service in the Azure Database. The tuning bases on data collected from PostgreSQL managed service, computing variation based on cluster sizing, and so on.

Recently, PostgreSQL 13 has come up with a new setting known as hash_mem_multiplier. This is an additional setting that helps tune memory usage.

Did you know hash operations do better with more memory than other operations?

In fact, our Support Team recommends increasing this setting to a value above 1.0. This allows hash operations to process faster and prevents other operations from using too much memory.

Beyond memory for queries

Let’s take a look at how our Support Tech helped out one of our customers recently.

In this specific situation, 10 GB of memory was available. With around 100 running PostgreSQL queries at a time on average, each of the queries was expected to have a 10MB overhead, leaving behind 9 GB of memory.

Out of the 9 GB memory, we can give 90 MB to work_mem for 100 running queries. However, work_mem is based on the number of hash/sort operations and not on a per-query basis. This leads to the question, what do we set work_mem to?

Furthermore, we will also require additional memory for maintenance tasks and parallel queries. Although Citrus may help out for a while, it would be better to have it on a single node PostgreSQL. In other words, a single query will run multiple processes. This leads to a significant improvement in speed.

Problems to look out for with work_mem in PostgreSQL

As seen above, getting work_mem to be perfect requires a little more tweaking than usual. Our Support Techs recommend starting with small, for instance, 16 MB, and then gradually increasing it when a temporary file is sighted.

This leads to the question, why not give each query 1 GB of memory? Well, this may exhaust your memory when you wind up having 100 queries with five different sort operations and hash operations as well on top of everything.

This will lead to an out-of-memory error message in your Postgres logs. Furthermore, it may even provoke the OOM killer to randomly kill processes in an attempt to free up memory.

In case you run into an out-of-memory error, it is ideal to increase the overall RAM off the machine by upgrading to a larger instance. We can also opt to reduce the work_mem value.

Our Support Team recommends decreasing the work_mem value if it is not possible to upgrade your instance at this point in time.

Additional tips to use work_mem in PostgreSQL

While tuning and tweaking your work_mem is the best way to get it working perfectly, our Support Techs are here with a few tips to make the task easier:

1. If you have several short-running frequent queries that perform simple lookups and joins, a low work_mem will be ideal. Increasing work_mem in this scenario will be foolhardy as the additional memory will remain unused.
2. If the workload has fewer active queries that are performing complex sorts and joins, increasing the work_mem value prevents things from spilling.

[Looking for further support with Server Management? We are just a click away.]

Conclusion

At the end of the day, PostgreSQL offers a lot of features that can we can fine-tune to offer optimal performance. The work_mem feature is one such feature and the Support Techs demonstrated how to tweak it to get the best performance.