Skip to main content

PostgreSQL settings

Last update:

PostgreSQL settings affect the performance of the database cluster. When creating a PostgreSQL cloud database cluster, you can view the default values for all settings and change them. We recommend that you change the values of settings only when necessary — incorrectly selected values can reduce cluster performance. Default values are selected to ensure high cluster performance, they vary depending on the cluster configuration and PostgreSQL version.

In an existing cluster, you can view or change settings.

When scaling the cluster, the values of some settings are automatically changed to default values. For a list of such settings, see Automatically changing settings when scaling a cl uster.

View settings

You can view cluster settings when you create a cluster or change settings.

If you have changed settings, you can separately view a list of settings with values different from the default values.

  1. In the Dashboard, on the top menu, click Products and select Cloud Databases.
  2. Open the Active tab.
  3. Open the cluster page → Settings tab.
  4. In the DBMS Settings block, click Edit.
  5. Optional: to see only settings where you have changed the default value, check the Show changed settings checkbox.
  6. Look at the settings. Description of settings can be found in the Settings Parameters article of PostgreSQL documentation.

Change settings

For your information

We recommend that you change the settings only when necessary — incorrect values can degrade cluster performance.

Changing some settings entails rebooting the databases in the cluster — the cluster may be unavailable during this time. The list of such settings can be found in the List of settings that require a reboot subsection.

  1. In the Dashboard, on the top menu, click Products and select Cloud Databases.
  2. Open the Active tab.
  3. Open the cluster page → Settings tab.
  4. In the DBMS Settings block, click Edit.
  5. Specify new values. Description of settings can be found in the Settings Parameters article of PostgreSQL documentation.
  6. Click Save.

List of settings that require a reboot

  • autovacuum_freeze_max_age;
  • autovacuum_max_workers;
  • autovacuum_multixact_freeze_max_age;
  • max_files_per_process;
  • max_pred_locks_per_transaction;
  • max_prepared_transactions;
  • old_snapshot_threshold;
  • track_activity_query_size;
  • max_connections;
  • max_locks_per_transaction;
  • max_worker_processes;
  • shared_buffers (for all versions except PostgreSQL 11);
  • huge_pages.

Automatic change of settings when scaling the cluster

Any DBMS setting has limits of acceptable values. When scaling the cluster (changing the configuration), the values of some settings are automatically changed to the acceptable values so that the cluster can work.

When the cluster is scaled and moves to ACTIVE status, you can set new values — change the settings.

A list of settings that change values when the cluster is scaled:

  • shared_buffers;
  • effective_cache_size;
  • maintenance_work_mem;
  • max_worker_processes;
  • max_parallel_workers;
  • autovacuum_max_workers;
  • vacuum_cost_limit;
  • max_parallel_workers_per_gather;
  • max_maintenance_workers.

Customizing huge_pages

In PostgreSQL cloud databases, the huge_pages setting can be used to enable and disable Huge Pages technology.

Huge Pages is a memory management technology that allows you to use larger memory pages than standard memory pages. The size of standard memory pages is 4 KB. After enabling the huge_pages setting, memory pages of 2 MB are available. The Huge Pages technology can be used, for example, to buffer in shared memory (shared buffers). Thanks to this technology you can reduce the overhead of computing resources: vCPU and RAM.

The huge_pages setting is available for PostgreSQL versions 15 and higher.

Possible values are on and off (default value). If the value is changed, the databases must be reloaded.

If the huge_pages setting is enabled, but shared memory limitations do not allow this setting to run, the database will go to ERROR status . In this case, we recommend reducing the value of the shared_buffers setting.