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:

   → 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. 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 later.

• edb_pg_tuner.tune_work_mem — Dynamically increases 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's 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 turns off logging.

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

• edb_pg_tuner.container_memory_limit_file_path — Specifies the path to the file that contains the memory limit set by the container runtime, such as Docker or Kubernetes. This file is read by edb_pg_tuner to determine the maximum amount of memory available to the database process when running inside a container. Based on this limit, the tuner can generate optimised PostgreSQL configuration recommendations that are container-aware. The file is at /sys/fs/cgroup/memory/memory.limit_in_bytes by default.

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.