Skip to main content

PostgreSQL settings

PostgreSQL settings affect the stability and performance of the database cluster. When creating a cluster, default values are applied. These default values are selected to ensure 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, the values of some settings are automatically replaced with default values. You can view the list of such settings in the subsection Automatic change of settings when scaling a cluster.

View settings

You can view the list of settings available for modification when creating a cluster or editing 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 be unavailable during the reboot. The reboot will start immediately after saving the changes. You can view the list of such settings in the subsection List of settings that require a reboot.

  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 (for all versions except PostgreSQL 11);
  • shared_preload_libraries;
  • timezone;
  • track_activity_query_size.

Automatic settings adjustment during cluster scaling

DBMS settings have valid value ranges. If, during cluster scaling, the setting values go beyond the allowed ranges, these values are automatically replaced with default values so that the cluster can operate.

Once the cluster is scaled and enters the ACTIVE status, you will be able to change settings and set new values.

List of settings that are reset to default values during 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

For your information

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

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

Huge Pages is a memory management technology that allows you to use larger memory pages 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 technology can be used, for example, for a buffer in shared memory (shared buffers). This technology allows reducing overhead on compute resources: vCPU and RAM.

Possible values are on and off. The default value is off. If you change the setting value, the cluster will automatically restart and will be unavailable during the restart. The restart will begin immediately after saving the changes.

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