How to tune PostgreSQL for memory

April 06, 2022

This article looks at parameters that can be used to help manage memory in PostgreSQL. Recommended settings for each parameter are also provided.

1. Shared_buffers (integer)

2. Work_mem (integer)

3. Maintenance_work_mem (integer)

4. Effective_cache_size (integer)


In this post, we are going to look at some of the important GUC parameters recommended for memory management in PostgreSQL, which is helpful for improving the performance of your database server. All of these parameters reside under the postgresql.conf file (inside $PDATA directory), which manages the configurations of the database server. 


shared_buffers (integer)

The shared_buffers parameter determines how much memory is dedicated to the server for caching data. 

The default value for this parameter, which is set in postgresql.conf, is:

#shared_buffers = 128MB


The value should be set to 15% to 25% of the machine’s total RAM. For example: if your machine’s RAM size is 32 GB, then the recommended value for shared_buffers is 8 GB. Please note that the database server needs to be restarted after this change.


work_mem (integer)

The work_mem parameter basically provides the amount of memory to be used by internal sort operations and hash tables before writing to temporary disk files. Sort operations are used for order by, distinct, and merge join operations. Hash tables are used in hash joins and hash based aggregation.

The default value for this parameter, which is set in postgresql.conf, is:

#work_mem = 4MB


Setting the correct value of work_mem parameter can result in less disk-swapping, and therefore far quicker queries. 

We can use the formula below to calculate the optimal work_mem value for the database server:

Total RAM * 0.25 / max_connections


The max_connections parameter is one of the GUC parameters to specify the maximum number of concurrent connections to the database server. By default it is set to 100 connections. 

We can also directly assign work_mem to a role: 

postgres=# alter user test set work_mem='4GB';



maintenance_work_mem (integer)

The maintenance_work_mem parameter basically provides the maximum amount of memory to be used by maintenance operations like vacuum, create index, and alter table add foreign key operations. 

The default value for this parameter, which is set in postgresql.conf, is:

#maintenance_work_mem = 64MB


It’s recommended to set this value higher than work_mem; this can improve performance for vacuuming. In general it should be: 

Total RAM * 0.05


effective_cache_size (integer)

The effective_cache_size parameter estimates how much memory is available for disk caching by the operating system and within the database itself. The PostgreSQL query planner decides whether it’s fixed in RAM or not. Index scans are most likely to be used against higher values; otherwise, sequential scans will be used if the value is low. Recommendations are to set Effective_cache_size at 50% of the machine’s total RAM.

For more details and other parameters, please refer to the PostgreSQL documentation:


Hope it helps! 

Share this

Relevant Blogs

Surviving Without a Superuser: Coming to v16

EDB is proud to present the following excerpt from Surviving Without a Superuser from EDB VP, Chief Data Scientist Robert Haas' personal blog. You can read the entire article here...
January 30, 2023

More Blogs