What is work_mem and what is this parameter used for in PostgreSQL?

“work_mem” is an important configuration parameter in PostgreSQL, that defines the limit, i.e., the maximum amount of memory (if not enough, it’s going to use the hard disk) allocated for executing operations such as:

• Sorting, when performing operations like ORDER BY, DISTINCT, GROUP BY.
• JOINs usage (with hashing to build in-memory hash-tables, for example for the hash join).
• Set operations like UNION, INTERSECT and EXCEPT.
• Creating the bitmap arrays for the bitmap scan method, particularly focusing on exact & lossy heap blocks. You can read more about this in my other article: https://medium.com/p/4d4876003cda (section: “Is there a relationship between “work_mem” and the bitmap scan method, and can it negatively affect the scanning process?”).

This parameter affects the efficiency of query execution and the overall performance of the database.

It’s important to note, that “work_mem” is allocated for each operation, not per the PostgreSQL session. This is a crucial detail, as a single SQL query can perform the multiple sorting or join operations, each of which will consume its own area of memory.

You can verify this by using the following SQL script. Let’s create a test table and insert into it the million records:

CREATE TABLE public.test_table AS
SELECT generate_series(1, 1000000) AS id,
MD5(random()::TEXT) AS random_text;

Let’s verify, that the table data has been filled:

SELECT * FROM public.test_table LIMIT 10;
SELECT COUNT(1) FROM public.test_table;

And let’s execute the following SQL query:

EXPLAIN (ANALYZE, BUFFERS)
SELECT id
FROM (
  SELECT id, random_text
  FROM public.test_table
  ORDER BY random_text
) subquery
ORDER BY id;

The execution plan will show how much memory has used for each sorting operation:

a plan for an executed SQL query

Let’s review this output for understanding, that it’s used for each operation separately.

The execution plan shows two sorting operations, each of which uses the “external merge” method and writes temporary results to disk (i.e. the set value of “work_mem” was insufficient). Let’s take a look at the details of these operations.

1. Main sorting opearion (by id):

Sort (cost=262750.23..264833.56 rows=833334 width=4) (actual time=1002.945..1061.759 rows=1000000 loops=1)
Sort Key: subquery.id
Sort Method: external merge Disk: 11768kB

Sort Method: external merge Disk: 11768kB: indicates, that the sort operation used 11768 kB of disk space, because the allocated memory from “work_mem” wasn’t enough to perform the operation in memory.

2. Sorting in subquery (by random_text):

Sort (cost=62839.48..63881.15 rows=416667 width=37) (actual time=378.818..509.315 rows=333333 loops=3)
Sort Key: test_table.random_text
Sort Method: external merge Disk: 14840kB

Sort Method: external merge Disk: 14840kB: the second sort, performed inside the subquery, used 14840 kB of disk space.

Let’s analyze the output of the execution plan in more detail to understand how the “work_mem” parameter is allocated for each operation.

What does this mean? Some kind of the confirmation, that the “work_mem” parameter is used for each operation.

Each of these sort operations used its own memory from “work_mem”. This is confirmed by the following:

• Different disk space sizes: Sorting operations use different amounts of disk space (11768kB and 14840kB). If “work_mem” was allocated per a single session, these values would be the same or the whole operation would use the shared memory.
• Different temporary files on disk: Each sort operation has created its own temporary files on disk, confirming the separated allocation process of memory for each sorting operation.

The final execution plan shows, that each sort operation uses its own amount of memory, determined by the “work_mem” value. Thus, it can be concluded that “work_mem” is indeed allocated for each operation, not for the entire session in PostgreSQL.

How can I make the temporary data not be saved to a disk, but instead happen in RAM?

To do this, you can increase the value of “work_mem”. Let’s try this for testing purposes, based on the same test table created earlier.

We’ll increase the value of “work_mem” to 128Mb (purely for testing, this should not be done in a real production environment, I will explain later in this article).

SET work_mem = '128MB';
SHOW work_mem;

And let’s again execute the SQL-query, I’ve prepared earlier:

EXPLAIN (ANALYZE, BUFFERS)
SELECT id
FROM (
  SELECT id, random_text
  FROM public.test_table
  ORDER BY random_text
) subquery
ORDER BY id;

And let’s look at the updated query plan output:

new query plan statistics output, after increasing the “work_mem” value

What has changed?

The method has changed, now instead of external merge and using the disk, we get:

“Sort Method: quicksort Memory”

This means that:

• the data was sorted and fit entirely in RAM, and the sorting was completed entirely within it. Since we increased “work_mem” to 128 MB, the two sorting operations fit within the memory utilization (24577 KB or ~25 MB, and 87077 KB or ~87 MB, which adds up to: 25+87=112 MB).
• the quicksort algorithm was used.

Now, let’s recall why you shouldn’t change the value for the “work_mem“ parameter, just because you want more memory to allocate:

• firstly, the memory used by “work_mem” is not type of the shared memory like “shared_buffers”.
• secondly, “work_mem” may be related to the parallel query execution, which is interconnected with the number of workers (background processes in PostgreSQL), which are managed through the next PostgreSQL parameters: max_worker_processes, max_parallel_workers, max_parallel_workers_per_gather, max_parallel_maintenance_workers.
• thirdly, the incorrectly calculated value for “work_mem” can produce the execution delays with the growth of data volume on the DBMS host in a production environment, as well as with the growth of the number of the client applications, that will use this DBMS with the not suitable “work_mem” value.

The correct value of this parameter depends on many factors, such as:

• allocated compute resources for the PostgreSQL service (meant CPU, RAM), the correct system parameter settings.
• the proportions between different PostgreSQL parameters, themselves (shared_buffers, work_mem, maintenance_work_mem, and others).
• the number of concurrent and active client connections to your PostgreSQL service.
• the frequency and complexity of executed SQL queries.

Therefore as you can see, quite a few parameters need to be considered for the correct “work_mem” parameter tuning. The example provided in this article with the “work_mem” value change was shown only for the demonstration purposes. The correct value in real life depends on various factors.

However, a generic formula can be defined, which can be used as a “starting point”, if you are still interested in knowing how to calculate “work_mem” value:

work_mem = ( (allocated RAM on PostgreSQL host) * 0.8 — shared_buffers) / (average amount of active client connections to your PostgreSQL host)

As one of the optimization methods, that you can use is redefining the “work_mem” value at the transaction level:

1). First, let’s print the value of “work_mem”:

SHOW work_mem;

2). Let’s execute an anonymous plpgsql-block, within which a transaction is going to be executed, and within which we are going to change the value of “work_mem” and print it through “RAISE NOTICE” call:

DO $$
BEGIN
  SET LOCAL work_mem = '128MB';
  RAISE NOTICE 'New value for work_mem: %',
  current_setting('work_mem');
END;
$$;

3). After the anonymous plpgsql-block execution, let’s print the value of “work_mem” again:

SHOW work_mem;

You must get next result, if you haven’t changed the default value of “work_mem” in the PostgreSQL configuration (which is 4MB):

• Initially output would be: 4MB.
• Then after using RAISE NOTICE: “New value for work_mem: 128MB”.
• Then after executing the anonymous plpgsql block, output would be again: 4MB.

So, how does the high-level logic of PostgreSQL’s work with “work_mem”?

Off-topic: It seems like this question should be the start of an article :) But I wanted to give some examples to get people interested, so this is the order of the narrative.

It looks like this in “general terms”:

1. The coordinator process (the main PostgreSQL process) determines how much RAM is needed for the current operation. The coordinator process estimates the amount of memory needed to perform the operations based on the available “work_mem” value and on the given volume of data.
2. Workers (background child processes) request the necessary “work_mem” amount from the coordinator. In the case of parallel operations, child processes (workers) request their own separate area of memory. Each child’s process operation simply uses “work_mem” within its own process.
3. The coordinator allocates available RAM to the workers, taking into account the limits of the RDBMS parameters. It’s worth noting, that in multithreaded mode, “work_mem” is applied to each worker process individually. This means, that the total resource consumption can be much higher, than for any individual process.
4. If “work_mem” is insufficient, workers start using the temporary files on host OS disk. As it was shown in the example with the SQL code (earlier in this article with the query plan output), if there is not enough RAM for a sort or hash operation, the temporary files on disk are used to perform the entire operation.
5. Upon completion of the operation, the memory previously used by workers is freed. Since “work_mem” is not allocated from a shared pool, but is a memory limit for each operation, when the operation is complete, the memory is simply freed for use by other operations in PostgreSQL.

PS: The following version of PostgreSQL was used, when I was writing this article: v16.3