PostgreSQL on VPS: How much RAM do you need and how to calculate work_mem

This is an update to the guide "PostgreSQL on a VPS, how much RAM do you really need". Here we add tuning of work_mem, the most confusing parameter and the one most commonly cited in support requests about slow databases. Tested version: PostgreSQL 16 and 17, Ubuntu 24.04.

What work_mem controls

The parameter work_mem sets the amount of memory that the engine reserves for a single sort operation or hash table operation before writing temporary data to disk. According to the official PostgreSQL documentation, work_mem sets the base maximum amount of memory to be used by a query operation (such as a sort or hash table) before writing to temporary disk files, and the default value is four megabytes (4 MB).

The point that generates the most sizing errors: the value is not per connection, it is per operation. A complex query can perform multiple sort and hash operations simultaneously, each of which can use up to work_mem before writing to disk, and multiple running sessions can do this in parallel: the total memory consumption can therefore be many times the configured value.

For hash table operations the effective limit is not work_mem but a multiple of it: the memory limit for a hash table is calculated by multiplying work_mem by hash_mem_multiplier, which makes it possible to use more memory than the base work_mem value. On PostgreSQL 16 and 17 the default value of hash_mem_multiplier is 2.0, which causes hash operations to use twice the base work_mem value, and this is often effective with mixed workloads.

Reading the current value

From psql:

SHOW work_mem;

Returns the value in effect for the current session (inherited from the configuration file or any session-level ALTER ROLE/SET commands).

How to calculate it for a VPS

There is no official PostgreSQL formula for work_mem: it is a parameter to be tuned to your workload, not set by formula. A reasonable starting point, commonly used in practical tuning, is to divide the available RAM after reserving shared_buffers by the maximum number of concurrent connections expected, accounting for the fact that each connection can open multiple sort/hash operations in parallel. On a VPS with 4 GB of RAM, shared_buffers set to 25% (around 1 GB, a typical value for dedicated systems according to the official documentation) and max_connections at 50, a prudent starting value is in the range of 8–16 MB, to be raised only after verifying with EXPLAIN that queries spill to disk.

The parameter does not require a service restart: it has "user" context, so it applies to subsequent connections after a reload.

Apply the value with ALTER SYSTEM

ALTER SYSTEM SET work_mem = '16MB';
SELECT pg_reload_conf();

To apply it to only one role, without affecting the rest of the server:

ALTER ROLE username SET work_mem = '32MB';

For the current session only, useful for testing a value without permanent impact:

SET work_mem = '32MB';

Verify the effect with EXPLAIN (ANALYZE, BUFFERS)

The correct way to find out if the new value makes a real difference is to compare the execution plan before and after on the same query:

EXPLAIN (ANALYZE, BUFFERS) SELECT ... ORDER BY ...;

In the output, look for the Sort or Hash node line: if you see Sort Method: external merge Disk: NNNNkB the query is writing to disk and work_mem for that operation is insufficient. After raising the value and running pg_reload_conf(), repeat the same EXPLAIN on the same query: if the method changes to quicksort or Memory without a Disk line, the increase has had an effect. The BUFFERS section also shows shared hit/read and temp read/written: a decrease in temp blocks between the two runs is the numerical proof, not just an impression, that the tuning worked.

A value that is too high gives no benefit beyond the threshold that eliminates spill to disk: it only increases the risk of running out of RAM when multiple connections execute heavy operations concurrently. For this reason, every increase should be verified, not just stated.