Using EDB Postgres Tuner

You can use EDB Postgres Tuner in two ways: to automatically apply all tuning recommendations or to manually apply selected tuning recommendations.

Automatic tuning

To automatically apply all tuning recommendations, set the edb_pg_tuner.autotune parameter to true . Restart Postgres to apply the change. EDB Postgres Tuner then begins applying tuning recommendations.

Manual tuning

To manually apply selected tuning recommendations, make sure the edb_pg_tuner.autotune parameter is set to false , which is the default. Then, use the edb_pg_tuner_recommendations function to inspect the tuning recommendations from the output.

You can set the format of the tuning recommendations to either conf or sql . For example:

SELECT edb_pg_tuner_recommendations(sql);

Where:

  • conf provides an output like parameter = value , which you can copy and paste into your configuration file. This is the default.

  • sql provides an output like ALTER SYSTEM SET parameter = value , which you can execute in your preferred Postgres client tool.

Example

This example shows how to manually apply tuning recommendations to a postgresql.conf file.

Here’s an example of settings in a postgresql.conf file:

checkpoint_completion_target = 0.2
effective_cache_size = 4GB
enable_seqscan = off
fsync = off
full_page_writes = off
max_wal_size = 2MB
seq_page_cost = 1.3
shared_buffers = 128MB

The following command provides tuning recommendations for the postgresql.conf file in the default conf format:

postgres=# SELECT * FROM edb_pg_tuner_recommendations();
__OUTPUT__
           recommendation
- -----------------------------------
 checkpoint_completion_target = 0.9
 effective_cache_size = 4653 MB
 enable_seqscan = on
 fsync = on
 full_page_writes = on
 max_wal_size = 84MB
 maintenance_work_mem = 524 MB
 shared_buffers = 1474 MB
(8 rows)

The following command provides an output in the sql format:

postgres=# SELECT edb_pg_tuner_recommendations(sql);
__OUTPUT__
             edb_pg_tuner_recommendations
- -----------------------------------------------------
 ALTER SYSTEM SET checkpoint_completion_target = 0.9;
 ALTER SYSTEM SET effective_cache_size = 4653 MB;
 ALTER SYSTEM SET enable_seqscan = on;
 ALTER SYSTEM SET fsync = on;
 ALTER SYSTEM SET full_page_writes = on;
 ALTER SYSTEM SET max_wal_size = 84MB;
 ALTER SYSTEM SET maintenance_work_mem = 524 MB;
 ALTER SYSTEM SET shared_buffers = 1474 MB;
(8 rows)

You can copy and paste the tuning recommendations in the conf format into the postgresql.conf file:

checkpoint_completion_target = 0.9
effective_cache_size = 4653 MB
enable_seqscan = on
fsync = on
full_page_writes = on
max_wal_size = 84MB
maintenance_work_mem = 524 MB
shared_buffers = 1474 MB

You can execute the tuning recommendations in the sql format in your preferred Postgres client tool:

ALTER SYSTEM SET checkpoint_completion_target = 0.9;
ALTER SYSTEM SET effective_cache_size = 4653 MB;
ALTER SYSTEM SET enable_seqscan = on;
ALTER SYSTEM SET fsync = on;
ALTER SYSTEM SET full_page_writes = on;
ALTER SYSTEM SET max_wal_size = 84MB;
ALTER SYSTEM SET maintenance_work_mem = 524 MB;
ALTER SYSTEM SET shared_buffers = 1474 MB;