PostgreSQL Enterprise Server (PgES) supports dynamic tuning of the database server
to make the optimal use of the system resources available on the host machine where it's installed. The
parameter that controls this functionality is located in the postgresql.conf file and is
named pges_autotune.
Enabling this feature will automatically ajust the values of work_mem,
maintenance_work_mem si effective_cache_size system parameters.
pges_autotune determines how much of the host system's resources for the database server
to use. It bases the determination on the host machine's total available resources and the intended use of the
host machine.
When PgES is first installed, you set the pges_autotune parameter according to the host machine's use as a
development machine, mixed-use machine, or dedicated server. For most purposes, the database administrator
doesn't need to adjust the configuration parameters in the postgresql.conf file to improve
performance.
Here are some things to keep in mind when setting up dynamic tuning:
You can change the value of the pges_autotune parameter after the initial installation of PgES by
editing the postgresql.conf file. You must restart the postmaster for the new
configuration to take effect.
You can set the pges_autotune parameter to any integer value from 0 to 100.
A value of 0 turns off the dynamic tuning feature, leaving the database server resource use under
the control of the other configuration parameters in the postgresql.conf file.
A low, non-zero value, that is, 1–33, dedicates the least amount of the host machine's resources to the database server. Use this setting for a development machine where many other applications are being used.
A value in the range of 34–66 dedicates a moderate amount of resources to the database server. You might use this setting for a dedicated application server that has a fixed number of other applications running on the same machine as PgES.
The highest values, that is, 67–100, dedicate most of the server's resources to the database server. Use this setting for a host machine that's totally dedicated to running PgES.
After you select a value for pges_autotune, you can further fine-tune database server
performance by adjusting the other configuration parameters in the postgresql.conf file.
Any adjusted setting overrides the corresponding value chosen by pges_autotune.
To change the value of a parameter: