Recommendations for PostgreSQL

As a database administrator, you want your database to achieve top performance. This topic provides information on mandatory and recommended database settings, as well as basic maintenance recommendations for installations with PostgreSQL databases.

Optimizing your PostgreSQL Database

There is a number of mandatory and recommended database settings that will help you optimize your database to work with the Automation Engine. You need to restart the database after changing them.

Note: The parameters that are not listed as mandatory or recommended can be changed by the database administrator as described in the PostgreSQL configuration settings (postgresql.conf). For more information, see https://www.postgresql.org/docs/current/static/config-setting.html.

Mandatory Settings

• shared_preload_libraries = pg_stat_statements

• log_lock_waits = on

• idle_in_transaction_session_timeout = 600000

• pg_stat_statements.track_utility= on

• autovacuum_vacuum_cost_delay = 0

• vacuum_cost_limit = 10000

Recommended Settings

• shared_buffers = 25% to 40% of RAM

• work_mem = Please refer to the PostgreSQL documentation.

• maintenance_work_mem = Please refer to the PostgreSQL documentation.

• effective_cache_size = 50 % of RAM

• autovacuum_vacuum_scale_factor = 0.01

• autovacuum_naptime = max. 1 min

• random_page_cost = 1.0

See also:

• Preparing the AE Database - PostgreSQL

• Automation Engine Database