Unlock PostgreSQL Performance: An Introduction

PostgreSQL is designed to provide robust performance with default settings based on best practices and past experiences. While these defaults are suitable for many scenarios, there are parameters that can be tuned to optimize performance for specific use cases. One such parameter that can impact performance in PostgreSQL is work_mem.

In this blog we will discuss how work_mem can be used to optimize performance in PostgreSQL.

What is work_mem?

In PostgreSQL, work_mem sets the maximum amount of memory to be used by a query operation like sorting and hashing before writing to temporary disk. By default, work_mem is set to 4MB. It can be set inside postgresql.conf file or at session level.

set work_mem to '1024MB';

Postgres creates temporary files inside the PGDATA/base/pgsql_tmp directory for operations that exceed work_mem. work_mem is allocated for each operation individually, not for the entire query. For example, if a single query has 8 sort operations, work_mem would be allocated eight times, once for each sort operation.

Parallel workers in PostgreSQL allow multiple processes to work on a single query, dividing the task to improve performance. These workers cooperate by sharing intermediate results through shared memory. If 2 parallel workers are processing a query with sort operation each parallel worker will use work_mem separately.

When to tune work_mem in PostgreSQL?

Tuning work_mem can improve performance in a number of scenarios:

• When you are running a query that involves a large amount of sorting or hashing.
• When there are large amounts of temporary files being created on disk.
• When you see external merge disk operation inside EXPLAIN ANALYZE plan.

Benefits of increasing work_mem in PostgreSQL

There are a number of benefits to increasing the work_mem parameter. For example, increasing work_mem can:

• Sorting large result sets is a common database task, and an appropriately set work_mem can significantly accelerate this process. It enables more data to be sorted in memory, reducing the need for slow disk operations.
• Hash joins are used to combine data from various tables during query execution. A larger work_mem value can enhance the performance of hash joins by facilitating more in-memory data storage and minimizing disk I/O needs.
• With faster sorting and joining, queries can be completed more swiftly. This translates to more responsive database performance and shorter response times for users.
•  Adjusting work_mem suitably facilitates parallelism and takes full advantage of multi-core processors, further boosting query efficiency.

NOTE: It’s essential to note that setting work_mem too high can lead to excessive memory usage and potential memory competition issues. Thus, finding the right balance for your specific workload is key.

Testing: Evaluating PostgreSQL Performance with work_mem

We initialized a database using pgbench with a scale factor of 100 using the following command

pgbench -i -s 100

Four tables pgbecnh_history, pgbench_accounts, pgbench_tellers and pgbench_branches will be created. 

\d

              List of relations

 Schema |       Name       | Type  |  Owner   

——–+——————+——-+———-

 public | pgbench_accounts | table | postgres

 public | pgbench_branches | table | postgres

 public | pgbench_history  | table | postgres

 public | pgbench_tellers  | table | postgres

A scale factor of 100 means that 10 million rows will be created inside the pgbench_accounts table. 

      table       | number of rows 

——————+—————-

 pgbench_branches |            100

 pgbench_tellers  |           1000

 pgbench_accounts |       10000000

 pgbench_history  |              0

(4 rows)

Inside the pgbench_accounts table there are aid, bid, abalance and filler columns.

\d pgbench_accounts 

              Table “public.pgbench_accounts”

  Column  |     Type      | Collation | Nullable | Default 

———-+—————+———–+———-+———

 aid      | integer       |           | not null | 

 bid      | integer       |           |          | 

 abalance | integer       |           |          | 

 filler   | character(84) |           |          | 

Indexes:

    “pgbench_accounts_pkey” PRIMARY KEY, btree (aid)

We ran the following query with default work_mem value at 4mb.

explain analyze SELECT * FROM pgbench_accounts ORDER BY bid;

                                                                    QUERY PLAN                                                                     

---------------------------------------------------------------------------------------------------------------------------------------------------

 Gather Merge  (cost=1120471.36..2092761.54 rows=8333334 width=97) (actual time=3708.723..7210.927 rows=10000000 loops=1)

   Workers Planned: 2

   Workers Launched: 2

   Buffers: shared hit=2352 read=161695, temp read=261785 written=262198

   ->  Sort  (cost=1119471.34..1129888.00 rows=4166667 width=97) (actual time=3613.332..4364.242 rows=3333333 loops=3)

         Sort Key: bid

         Sort Method: external merge  Disk: 332024kB

         Buffers: shared hit=2352 read=161695, temp read=261785 written=262198

         Worker 0:  Sort Method: external merge  Disk: 363280kB

         Worker 1:  Sort Method: external merge  Disk: 351904kB

         ->  Parallel Seq Scan on pgbench_accounts  (cost=0.00..205601.67 rows=4166667 width=97) (actual time=0.038..893.507 rows=3333333 loops=3)

               Buffers: shared hit=2240 read=161695

 Planning Time: 0.056 ms

 Execution Time: 8027.815 ms

(14 rows)

We can see that temporary files are being created and the sort algorithm being used is external merge disk which is creating around 370mb of spill files on disk per worker

We then increased the value of work_mem to 1024mb and ran the following query.

explain analyze SELECT * FROM pgbench_accounts ORDER BY bid;

                                                                                                                                           QUERY PLAN                                                                     

---------------------------------------------------------------------------------------------------------------------------------------------------

 Gather Merge  (cost=664736.36..1637026.54 rows=8333334 width=97) (actual time=2237.257..4723.456 rows=10000000 loops=1)

   Workers Planned: 2

   Workers Launched: 2

   Buffers: shared hit=2448 read=161599

   ->  Sort  (cost=663736.34..674153.00 rows=4166667 width=97) (actual time=2227.376..2486.086 rows=3333333 loops=3)

         Sort Key: bid

         Sort Method: quicksort  Memory: 598727kB

         Buffers: shared hit=2448 read=161599

         Worker 0:  Sort Method: quicksort  Memory: 562904kB

         Worker 1:  Sort Method: quicksort  Memory: 539533kB

         ->  Parallel Seq Scan on pgbench_accounts  (cost=0.00..205601.67 rows=4166667 width=97) (actual time=0.034..863.136 rows=3333333 loops=3)

               Buffers: shared hit=2336 read=161599

 Planning Time: 0.054 ms

 Execution Time: 5471.850 ms

(14 rows)

We can see that temporary files are not being created, the sorting is done inside the memory using quick sort and execution time has been reduced by approximately 30%.