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.
WarningNote
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.
InformationExample
Example of the configuration items modified for a system with 20 cores and 32 GB of memory for PostgreSQL version 12.7:
#------------------------------------------------------------------------------
# pgtune config Based on 32947648 KB RAM, platform Linux
#------------------------------------------------------------------------------
max_connections = 1500
shared_buffers = 8GB
effective_cache_size = 24GB
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 = 3990kB
min_wal_size = 1GB
max_wal_size = 4GB
max_worker_processes = 20
max_parallel_workers_per_gather = 4
max_parallel_workers = 20
max_parallel_maintenance_workers = 4
After the installation or upgrade or before taking a backup of BMC Helix Business Workflows, we also recommend that you run the VACUUM FULL ANALYZE command from the pgAdmin for every AR-related 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.
