When deploying a PostgreSQL cluster to prod, one of the most difficult tasks is setting the values for configurations inside PostgreSQL config file. The default configurations is very conservative on the use of server resources. Never use the default configuration for production clusters. A good place to start setting PostgreSQL initial configuration is the pgtune web page. Bear in mind it gives you a good starting configuration, but it might need further configuration tuning depending on the size of the database and tables and the complexity of the queries among other things.

In this series of blogs, I will explain the purpose of each of the postgresql.conf settings and how to set them. In the first part of the series I will start explaining the main memory settings in PostgreSQL config file. These are:

• shared_buffers
• effective_cache_size
• wal_buffers
• work_mem
• maintenance_work_mem

shared_buffers

The setting shared_buffers determines how much memory will PostgreSQL use as temporary memory space for result sets. This is one of the most commonly known settings and you might be tempted to set it to get most of your servers memory, don’t. shared_buffers serves only as temp memory for result sets. The server needs memory for the OS, sorting and grouping data, writing to wal files, maintenance jobs like vacuum among other things. PostgreSQL documentation recommends setting this no more than 40% of the memory in the server. Also, the documentation recommends increasing max_wal_size “in order to spread out the process of writing large quantities of new or changed data over a longer period of time.”

effective_cache_size

This number indicates to the query planner how much memory is available for disk caching by the OS and PostgreSQL. If the server is having performance issues and is not consuming half the memory in the server, it is very likely that effective_cache_size is set too low. It is important to understand that this setting is used to indicate how much memory is available but is not allocated until needed. This could be set between 50% and 75% percent of the memory.

wal_buffers

This is a subset of the shared_buffers to be used for wal data that has not yet been written to disk. By default, it will take about 3% of the memory set in shared_buffers. You can increase performance writing to the wal files by increasing this setting a little. Setting this value too high will not benefit the server and will take resources from other tasks.

work_mem

This number is the maximum amount of memory to be used in a query operations, like sorting and grouping. It is very important that you understand how many of these operations take place in the cluster before setting it up.

maintenance_work_memory

This number is the maximum amount of memory to be used in a maintenance operations, like vacuum and create index. Be mindful and take into consideration that each of the workers set autovacuum_max_workers will use be able to allocate the memory set here. To avoid this issue, set autovacuum_work_mem.

Final comments

I hope you gained a better understanding of these settings so you can make conscious decisions when deciding the values for each one of them. If you set these values right and make sure to allocate enough memory for each of these settings, you won’t have many issues. Remember, you can use tools like pgtune web page to do an initial setup, but ultimately, you will always need to fine tune your PostgreSQL cluster based on the activity in your databases.