Skip to main content

PostgreSQL settings

The PostgreSQL settings affect the stability and performance of the database cluster. When creating a cluster, default values are applied. These default values are optimized for high cluster performance and vary depending on the cluster configuration and the version of PostgreSQL.

You can:

  • view the default setting values and change them when creating a cluster;
  • or view settings and change them in an already created cluster.

We recommend changing setting values only when necessary—incorrect values can make the cluster inoperable or reduce its performance.

When scaling a cluster, some setting values are automatically replaced with default values. You can view a list of such settings in the Automatic settings change when scaling a cluster subsection.

View settings

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

If you have changed the settings, you can separately view a list of settings whose values differ 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 Managed Databases.
  2. Open the Active tab.
  3. Open the cluster page → Settings tab.
  4. In the DBMS Settings block, click Change.
  5. Optional: to view only those settings for which you have changed the default value, select the Show changed parameters checkbox.
  6. View settings.

Change settings

carefully

We recommend changing setting values only when necessary—incorrect values can make the cluster inoperable or reduce its performance.

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

  1. In the Dashboard, on the top menu, click Products and select Managed Databases.
  2. Open the Active tab.
  3. Open the cluster page → Settings tab.
  4. In the DBMS Settings block, click Change.
  5. Specify new values.
  6. Click Save.

List of settings that require a restart

  • 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;
  • timezone;
  • track_activity_query_size.

Automatic setting changes upon cluster scaling

DBMS settings have defined ranges of acceptable values. If settings values fall outside the acceptable range during cluster scaling, they are automatically reset to default values to ensure the cluster remains operational.

Once the cluster has been scaled and its status switches to ACTIVE, you will be able to change the settings and set new values.

List of settings that are reset to default values upon cluster scaling:

  • 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.

Configuring huge_pages

In Managed Databases, PostgreSQL you can enable and disable Huge Pages using the huge_pages setting.

Huge Pages is a memory management technology that allows for the use of memory pages larger than standard ones. The size of standard memory pages is 4 KB. After enabling the huge_pages setting, 2 MB memory pages become available (Huge Pages). You can use Huge Pages technology, for example, for shared buffers. This technology can reduce overhead on computing resources: vCPU and RAM.

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

Possible values are on and off. The default value is off. Changing this setting will automatically restart the cluster, making it unavailable during the restart process. The restart will begin immediately after saving the changes.

If the huge_pages setting is enabled, but shared memory constraints prevent the use of Huge Pages, the cluster will enter the ERROR status. In this case, we recommend reducing the value of the shared_buffers.