What is work_mem in PostgreSQL

Written by Fabio Pardi

Properly setting work_mem on your Postgres instance is one important things you should do on your database. It helps queries run fast and keeps users happy!

- Let's do an experiment together today: touch your nose with your finger.

- Done?

- Easy, right?

Of course, it is easy, but that doesn’t mean it is simple: your brain is involved to give the order to perform the action, and a great number of muscles get involved to perform the movement. I’m oversimplifying all the nerves, ligaments, and other ‘things’ involved but if you really want to know the nitty-gritty details, ask your doctor!

Similarly to our body, every time we perform a query on our beloved Postgres database, a lot of action happens behind the scenes. The query planner tries to find the best execution plan for our query based on its (immense, to me) knowledge. CPU, memory, disk, Shared Buffers...you name it.

Every resource might be used, without the user even realizing the complexity behind even the most simple query. The query gets executed in one way or another also based on the database settings. One of the settings that often create slowness if not properly configured is work_mem.

work_mem tells Postgres how much RAM can be allocated by a query before a file on disk is created. Not all operations require such a 'buffer' to work on, but it is important to let the system know that some amount of memory can be used when needed. Something important to know is that one query might need multiple buffers at once, for instance when parallelization is used, therefore N times work_mem. The default setting is 4MB and it might not be enough for you.

The slower the query, the more time the user has to wait

In the case of the amount of RAM set in work_mem not being enough, the disk is used instead. And the disk is slow, we know that, and the slower your disk, the slower the query. The slower the query, the more time the user has to wait. And more waiting time means fewer happy users. And fewer happy users might mean that users will go away.

Bottom line: you will lose your job. You will not be able to afford your mortgage, you are going to lose your house if you do not properly set work_mem. Now that you understand how important work_mem is, just keep reading and I will tell you everything you need to know.

The best practice is to configure your Postgres instance to log every temporary file.

Set log_temp_files = 0 in your Postgres instance, and all temporary files will be logged for future inspection. Also to graph them is a good idea, and perhaps to receive alerts when temp files have been created. Now, a bit of hands-on...

mydb=# CREATE TABLE work_mem_example (i int);
CREATE TABLE

mydb=# INSERT INTO work_mem_example VALUES (generate_series (0,10000000));
INSERT 0 10000001

mydb=# \dt+ work_mem_example ;
                      List of relations
Schema |       Name       | Type  | Owner |  Size  | Description
--------+------------------+-------+-------+--------+-------------
public | work_mem_example | table | pgsql | 346 MB |
(1 row)

mydb=# SET work_mem = 64

mydb=# SHOW work_mem ;

 work_mem
----------
 64kB

What we have is our system configured to use the least work_mem possible, and a table 346 MB big.

Now let's see work_mem in action:

mydb=# EXPLAIN ANALYZE SELECT * FROM work_mem_example ORDER BY i;

Sort  (cost=1697550.03..1722549.98 rows=9999979 width=4) (actual time=7995.605..10673.361 rows=10000001 loops=1)
 Sort Key: i
 Sort Method: external sort  Disk: 136856kB
 ->  Seq Scan on work_mem_example  (cost=0.00..144247.79 rows=9999979 width=4) (actual time=0.024..2204.899 rows=10000001 l
oops=1)
Planning time: 0.137 ms
Execution time: 12403.510 ms
(6 rows)

We can see that 136856kB are used by the sort operation. After the query is executed, in the logs we can see (in bytes):

2021-07-08 11:19:02.713 CEST pgsql mydb 127.0.0.1 psql PID=5040 EXPLAIN 25 0 LOG:  temporary file: path "base/pgsql_tmp/pgsql_tmp5040.2", size 140140544

So we know that we need 133MB of disk space to sort 10000001 records coming from a 346MB table. The query takes 12.403 seconds (note: it is a sequential scan, which is costly).

So, one could think that it would be enough to set work_mem to 134 MB to fit the sort in memory. Right?

mydb=# EXPLAIN ANALYZE SELECT * FROM work_mem_example ORDER BY i;
                                                             QUERY PLAN

-----------------------------------------------------------------------------------------------------------------------------
--------
 Sort  (cost=1306922.97..1331922.97 rows=10000001 width=4) (actual time=6892.531..9378.814 rows=10000001 loops=1)
   Sort Key: i
   Sort Method: external merge  Disk: 136848kB
   ->  Seq Scan on work_mem_example  (cost=0.00..144248.01 rows=10000001 width=4) (actual time=0.037..2449.336 rows=10000001
loops=1)
 Planning time: 0.070 ms
 Execution time: 11190.423 ms

Unfortunately, this is not quite right! The sort still does not fit, and the reason is that the temporary files use a more compact format than data in memory, therefore we need to increase work_mem to more than 133MB.

How much? For this query to fit, we need at least 700MB of RAM:

mydb=# set work_mem = "700MB";
SET
mydb=# EXPLAIN ANALYZE SELECT * FROM work_mem_example ORDER BY i;
                                                            QUERY PLAN

-----------------------------------------------------------------------------------------------------------------------------
---------
Sort  (cost=1306922.97..1331922.97 rows=10000001 width=4) (actual time=5017.973..7138.745 rows=10000001 loops=1)
 Sort Key: i
 Sort Method: quicksort  Memory: 707684kB
 ->  Seq Scan on work_mem_example  (cost=0.00..144248.01 rows=10000001 width=4) (actual time=0.062..2287.086 rows=10000001
loops=1)
Planning time: 0.068 ms
Execution time: 8892.219 ms

Also we are saving more than 2 seconds in executing the query. It might not appear significant in this example because the query is doing a sequential scan and the machine is equipped with fast SSD, but the real world is often different.

Queries might be running simultaneously contending the disk, disks might be slow ones, such as HDD, and complex queries might spend a considerable amount of time on operations which require work_mem.

Another takeaway for today is that we realized that the first query used 133MB of disk space, while the last one used 691MB of RAM. We can therefore observe that in our example a temporary file on disk is compressed 5 times what would be on RAM. It might be different in your case (more or less compression) but it is definitely something to take into account.