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 16 GB and 32 GB of memory, respectively:
#------------------------------------------------------------------------------
# pgtune config Based on 16433176 KB RAM, platform Linux
#------------------------------------------------------------------------------
default_statistics_target = 100
maintenance_work_mem = 960MB
checkpoint_completion_target = 0.9
effective_cache_size = 11GB
#------------------------------------------------------------------------------
# pgtune config Based on 32947648 KB RAM, platform Linux
#------------------------------------------------------------------------------
maintenance_work_mem = 1920MB
effective_cache_size = 22GB
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.