Tuning the PostgreSQL database

The basic PostgreSQL configuration shipped with the product is tuned for a wide compatibility rather than performance, and some default parameters are undersized.

We recommend that you use an online version of the pgtune ( https://pgtune.leopard.in.ua/#/ ) tool to optimize the configuration. The tool generates a set of configuration items that can directly be replaced in the postgresql.conf file. You must restart the server after the changes are applied.

Note

Previously, the standard recommendation was to use the third-party tool pgtune (https://github.com/gregs1104/pgtune) to optimize the configuration. However, the Python script tool was discontinued with PostgresSQL version 9.1. 

Example

Example of the configuration items modified for a system with 24 cores and 48 GB of memory for PostgreSQL version 13.9:

max_connections = 1500

shared_buffers = 12GB

effective_cache_size = 36GB

maintenance_work_mem = 2GB

checkpoint_completion_target = 0.9

wal_buffers = 16MB

default_statistics_target = 100

random_page_cost = 1.1

effective_io_concurrency = 300

work_mem = 31457kB

min_wal_size = 1GB

max_wal_size = 4GB

max_worker_processes = 24

max_parallel_workers_per_gather = 4

max_parallel_workers = 24

max_parallel_maintenance_workers = 4

After the installation or upgrade or before taking a backup, we also recommend that you run the VACUUM FULL ANALYZE command from the pgAdmin of AR database. This reclaims and optimizes the space used by the database server and rebuilds the statistics. For more information about the VACUUM FULL ANALYZE command, see  Routine Vacuuming in the PostgreSQL documentation. 

 

Tip: For faster searching, add an asterisk to the end of your partial query. Example: cert*