Configuring EDB Postgres Tuner

To run EDB Postgres Tuner, you need to add it to the shared_preload_libraries and create the extension in the database. EDB Postgres Tuner can then gather metrics to make tuning recommendations.

1. In the postgresql.conf file, add edb_pg_tuner to the shared_preload_libraries parameter:

   → WrapCopy

   shared_preload_libraries = 'edb_pg_tuner'

   Note

   If shared_preload_libraries has other extensions, then you can add edb_pg_tuner to the list. The order doesn't matter.

2. Restart Postgres.

3. Create the EDB Postgres Tuner extension in your database with the following command:

   → WrapCopy

   CREATE EXTENSION edb_pg_tuner;

Parameters

The following custom GUCs control the EDB Postgres Tuner extension behavior. If you modify these parameters, then reload Postgres to apply the changes.

• edb_pg_tuner.autotune — Applies tuning recommendations automatically. The default is false.

• edb_pg_tuner.naptime — Sets the interval between each check in seconds. The default is 600 seconds (10 minutes).

• edb_pg_tuner.max_wal_size_limit — Sets the maximum value for the max_wal_size recommendation. The default is 0, which sets no limit.

The following custom GUCs control the EDB Postgres Tuner work_mem tuning behavior for Postgres 14 and higher.

• edb_pg_tuner.tune_work_mem — Dynamically increase the work mem for queries with disk spill to improve performance. The default is true.

• edb_pg_tuner.work_mem_pool — Maximum additional work_mem reserve that is allocated to queries with disk spill. The default is 2GB.

• edb_pg_tuner.log_min_duration — Execution time threshold in ms for statistics logging. The default is 0, which logs everything. The value -1 will turn off logging.

• edb_pg_tuner.buffer_size — Maximum query count for tracking statistics. The default is 5000.

Recommended GUCs

EDB Postgres Tuner can recommend the following GUCs. The static category provides fixed recommendation settings. The dynamic category uses specific algorithms to suggest a better setting according to your workload or hardware resources.