Skip to main content

PostgreSQL settings

Last update:

The PostgreSQL settings affect the health and performance of the database cluster.When the cluster is created, default values are set in the settings.The default values are chosen to ensure high cluster performance and vary depending on the cluster configuration and the version of PostgreSQL.

You can:

  • see the default values for the settings and change them when creating the cluster;
  • or see the settings and change them in an already created cluster.

We recommend that you change the settings only when necessary — incorrect values may render the cluster inoperable or degrade its performance.

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

View settings

You can view a list of settings available for modification when creating a cluster or changing settings.

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

A detailed description of all settings can be found in the Server Configuration section of the PostgreSQL documentation.

  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 Change.
  5. Optional: to see only settings where you have changed the default value, check the Show changed settings checkbox.
  6. Take a look at the settings.

Change settings

carefully

We recommend that you change the settings only when necessary — incorrect values may render the cluster inoperable or degrade its performance.

After changing some settings, the cluster will automatically reboot and will be unavailable during the reboot.The reboot will start immediately after saving the changes.The list of such settings can be found in the subsection List of settings that require a reboot.

  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. Indicate the new values.
  6. Click Save.

List of settings that require a reboot

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

Automatic change of settings when scaling the cluster

DBMS settings have valid value boundaries.If during cluster scaling the settings values go beyond the valid value boundaries, such values are automatically replaced with default values so that the cluster can work.

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

A list of settings whose values change to default values when the cluster is scaled:

  • autovacuum_max_workers;
  • effective_cache_size;
  • maintenance_work_mem;
  • max_parallel_workers;
  • max_wal_size;
  • max_worker_processes;
  • shared_buffers;
  • vacuum_cost_limit;
  • work_mem.

Customizing huge_pages

In cloud databases, PostgreSQL with 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 memory pages larger 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.Huge Pages technology can be used, for example, to buffer shared buffers.With this technology, you can reduce the overhead of computing resources: vCPU and RAM.

The huge_pages setting is available for versions of PostgreSQL 15 and later.

The possible values are on and off. The default setting is off. If you change the setting value, the cluster will automatically reboot and will be unavailable for the duration of the reboot.The reboot will start immediately after saving the changes.

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