How to Tune PostgreSQL GUC Parameters

May 5, 2020

This article discusses GUC parameters that can be used for PostgreSQL tuning and how to configure them for improved performance.

  1. shared_buffers (integer)
  2. work_mem (integer)
  3. autovacuum
  4. Effective_cache_size
  5. max_parallel_worker
  6. maintenance_work_mem (integer)

 

In this post, we are going to review the parameters for some of the different GUCs (grand unified configuration settings) that are helpful for PostgreSQL tuning. These parameters reside under postgresql.conf file (inside $PGDATA directory), which manages the configuration and performance of your 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 the postgresql.conf file, is:

shared_buffers = 128MB

The value should be set to 15% to 25% of the machine total RAM. For example, if your machine RAM size is 32 GB, then the recommended value for shared_buffers is 8 GB. 

The server needs to be restarted after this change.

 

work_mem (integer)

The work_mem parameter essentially 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 JOINS operations. Hash tables are used in hash joins and hash-based aggregation.

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

work_mem = 4MB

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

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

Total RAM * 0.25 / max_connections

Max_connections is one of the GUC parameters that specifies the maximum number of concurrent connections to the database server. By default, its value is set to 100 connections. 

We can also directly assign work_mem to a role:

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

 

autovacuum 

This GUC parameter is enabled ON by default in the postgresql.conf file, and this setting is recommended. if this parameter is set to OFF, then autovacumming will NOT happen in the server, but if it is disabled, then the system will launch autovacuum processes to prevent transaction ID wraparound.

The database server needs to be restarted after changing this parameter value in the postgresql.conf file.

Other autovacuum related GUC parameters include:

autovacuum_max_workers

This parameter will set how many worker processes will run in parallel. By default it is 3. The value needs to be set higher if there are a large number of tables with frequent update or delete operations. 

autovacuum_naptime

This parameter specifies the minimum delay between autovacuum runs on any given database. The default is 1 minute. If there are 60 databases, then the autovacuum process would have to be started every second. In this case it would be recommended to increase the value so that autovacuum doesn’t start too often.

autovacuum_vacuum_threshold  

This parameter specifies the minimum number of updated or deleted tuples needed to trigger a VACUUM in any one table. The default is 50 tuples, which is very low for tables

autovacuum_analyze_threshold

This parameter specifies the minimum number of inserted, updated, or deleted tuples needed to trigger an ANALYZE in any one table. The default is 50 tuples, which is very low for tables.

autovacuum_vacuum_scale_factor

This parameter specifies the percentage of a table that needs to have changes in order to start the autovacuum process. The default value is 0.2 (i.e., 20% of table size), and autovacuum_analyze_scale_factor is 0.1 (i.e., 10%).This value is fine for small tables, but If the table size is large (i.e., 150 GB), then there would be 30 GB of dead tuples before the autovacuum process starts. However, if there are a small number of large tables then we can set on the table level rather than modifying the postgresql.conf file: 

postgres=# alter table test set (autovacuum_vacuum_scale_factor=0.1);
ALTER TABLE

 

For additional autovacuum parameters, please refer to the PostgreSQL online documentation:

https://www.postgresql.org/docs/11/runtime-config-autovacuum.html.

 

effective_cache_size 

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 fits in RAM or not. Index scans are most likely used against higher value otherwise sequential scans will be used if value is low . 

Recommendations are to set effective_cache_size to 50% of the machine’s total RAM.

 

max_parallel_worker

The max_parallel_worker parameter specifies the maximum number of workers to perform parallel operations, which depends on the value specified in GUC parameter max_worker_processes (which specifies the maximum number of background processes that the system can support; the default is 8).

 

maintenance_work_mem (integer)

The maintenance_work_mem parameter essentially provides the maximum amount of memory to be used by maintenance operations like VACUUM, CREATE INDEX, ALTER TABLE, and add foreign key operations. The default value for this parameter, which is set in the postgresql.conf file, is:

maintenance_work_mem = 64MB

It is recommended to set this value higher than work_mem, which may improve performance for vacuuming. In general, it should be: 

Total RAM * 0.05

 

Please refer to the PostgreSQL tuning wiki page for more details:

https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server.