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();

           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');

             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';

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.