Recommended ADPG settings

The postgresql.conf file is the main configuration file in ADPG and the main source of configuration parameter settings. Changing the parameter values in this file can significantly affect the performance of ADPG.

Changing some settings requires restarting the ADPG configuration or rebooting the ADPG cluster.

CAUTION
Use only ADCM to update the configuration and restart the ADPG cluster.

max_connections parameter

The max_connections parameter is responsible for the maximum number of simultaneous connections to ADPG. For a replica host, the value of this parameter must be greater than or equal to the value on the leader host. If this requirement isn’t met, the replica host will reject all requests.

Use the following formula to calculate the optimal value of max_connections:

max_connections = <maximum_number_of_expected_connections> * 2

After making changes, restart ADPG.

shared_buffers parameter

The shared_buffers parameter sets the memory limit for shared memory buffers. The higher the value of this parameter, the less the load on the host’s hard drives will be.

Use the following formula to calculate the optimal value of shared_buffers:

shared_buffers = 15% to 25% of available host memory
Example
If the host with ADPG has 32 GB of memory, then the recommended value will be 8 GB

When increasing the value of shared_buffers, it’s necessary to increase the value for the max_wal_size parameter parameter accordingly to stretch the process of writing a large amount of new or modified data for a longer time.

After making changes, restart ADPG.

temp_buffers parameter

The temp_buffers parameter sets the memory limit that will be used for each client connected to the temporary tables.

There is no recommended value for this parameter since the parameter should be adjusted to the amount of free memory. If there is high-load access to temporary tables, it’s recommended to increase the memory amount.

TIP
The allocated memory for each client is non-shared memory.

work_mem parameter

The work_mem parameter sets the non-shared memory limit that will be used for internal operations when executing queries, for example, to temporary tables, before temporary files on the disk are used. This limit acts as simple resource control, preventing the host from going into swapping due to overallocation. Since the allocated memory is non-shared memory, large and complex requests can exceed the specified limit.

There is no recommended value since the parameter should be adjusted to the amount of free memory. Small values will create a lot of temporary files, which can lead to a decrease in the performance of the host disk subsystem. Use the following formula to calculate the optimal value of work_mem:

(Total RAM * 0.25) / (max_connections)

maintenance_work_mem parameter

The maintenance_work_mem parameter sets the memory limit for maintenance operations. Increasing the value for this parameter can lead to faster operations for cleaning and restoring the database from the copy. At the same time, if automatic cleaning is used, which is set by the autovacuum_work_mem parameter, it’s not recommended to allocate too much memory, since this will significantly slow down the performance.

Use the following formula to calculate the optimal value of maintenance_work_mem:

Total RAM * 0.05

It’s recommended to manage the cleanup using the autovacuum_work_mem parameter parameter.

autovacuum_work_mem parameter

The autovacuum_work_mem parameter sets the memory limit that each workflow will use during the automatic cleanup. It’s recommended to use the cleaning function using this parameter.

Use the following formula to calculate the optimal value of autovacuum_work_mem:

Total RAM * 0.05

wal_level parameter

The wal_level parameter determines what is written to the pre-recording log (WAL), which is a standard method of ensuring data integrity. There are three values available for this parameter:

  • minimal;

  • logical;

  • replica.

The minimal value provides higher performance, but yet at the same time, some operations will be performed bypassing the log. Therefore, the log will not contain the necessary information to restore data from the base copy and logs. Selecting the minimal value is recommended if operations such as replication and archiving are absent or extremely rare, since minimal only provides the possibility of recovery after a failure or an emergency shutdown.

The logical value provides logging of the extended information required to support logical decoding. Each subsequent level will include information that has been recorded at all levels below. Choosing this value can lead to a significant increase in the volume of WAL and a slowdown in the work of ADPG.

It’s not recommended to set this value if the tables in ADPG have the REPLICA IDENTITY FULL characteristic and mainly commands like UPDATE and `DELETE' are executed. At the same time, choosing logical is recommended for logical replication.

When the replica value is selected, only the data necessary to support WAL archiving and replication, (including reading requests on the replica host) will be written to the log. Choosing replica ensures that streaming replication is performed and is the recommended choice for binary replication.

After making changes, restart ADPG.

synchronous_commit parameter

The synchronous_commit parameter determines whether the host with ADPG during a transaction will wait for the records from WAL to be saved on disk before informing the client that the operation was successfully completed. Several values are available:

  • on;

  • remote_apply;

  • remote_write;

  • local;

  • off.

The on value is the default value and is recommended in most cases.

The choice of the value off leads to the transfer of logs to asynchronous operation mode and as a result There is a possibility that the client will receive a notification of the successful completion of the transaction, whereas the transaction was not guaranteed to be completed. At the same time, in case of any failure, the choice of the value off doesn’t lead to a violation of the integrity of the database, but only to the loss of the last transactions that were considered fixed. The choice of this value is recommended if the integrity of the data is less important than the response time.

When the value local is set, the transaction is committed after a local data reset without waiting for replication. This isn’t a recommended choice for synchronous replication.

checkpoint_timeout parameter

The checkpoint_timeout parameter sets the maximum time between automatic checkpoints in WAL. If large queries prevail in ADPG, it’s recommended to increase the value for this parameter, this will reduce the disk load. But in case of any failure, this will lead to an increase in the time it will take to recover.

max_wal_size parameter

The max_walk_size parameter sets the memory limit to which the log size can grow between automatic checkpoints. Increasing this value for this parameter may lead to an increase in the time it will take to recover from a failure.

The specified limit can be exceeded automatically with a high load on ADPG.

effective_cache_size parameter

The efficie_cache_size parameter tells the ADPG query scheduler what RAM limit is available for caching data for the shared_buffers parameter parameter and the file system cache.

The efficie_cache_size parameter helps the scheduler to correctly estimate the costs of efficiently allocating memory for the disk cache per single request. This estimate affects how the cost of using the index will be estimated. The higher this value, the more likely the index scan will be applied. The lower this value, the more likely the sequential scan will be selected.

When setting the value, you should take into account both the number of shared buffers and the percentage of the kernel disk cache that data files will occupy, although some data may end up in both places. In addition, the expected number of parallel queries to different tables should be taken into account, since the total size will be divided between them.

Use the following formula to calculate the optimal value of efficie_cache_size:

Total RAM * 0.7
Found a mistake? Seleсt text and press Ctrl+Enter to report it