This document describes how to specify configuration parameters of the Postgres Pro server for the maximum performance and stability when working with 1C solutions.
For fast automatic tuning of Postgres Pro, use pgpro_tune. This command‑line tool automatically adjusts the server parameters based on the available hardware configuration.
The use of pgpro_tune is enabled in initdb by default. This means that pgpro_tune runs automatically when you initialize a new database cluster. During this first start, the utility selects an appropriate preset:
If you are using the Postgres Pro for 1C edition,
the utility applies the 1c.tune preset. This preset
specifies optimal values for 1C-specific
configuration parameters.
For Postgres Pro Standard and
Postgres Pro Enterprise editions, only the
default preset is used automatically. This preset
provides a basic configuration but does not include the
1C‑specific optimization. It is recommended
to specify the 1c.tune preset manually in order to
ensure better performance.
Though pgpro_tune is designed to run automatically at cluster creation, you can also run it manually at any time to tune parameters of an existing cluster.
Once initdb is executed, the parameters modified
by pgpro_tune are written to the
postgresql.conf file. They are placed in a separate
section at the end of the file, marked with comments containing the
utility name, the preset name, and the timestamp:
The following settings were added by pgpro_tune. pgpro_tune was run with the following options: -Ppreset_nameAtTIMESTAMP
If the server is already running, you can also find the current
parameter values in the pg_settings view. All
parameters set by pgpro_tune have the
configuration file value in the
source column. For example:
SELECT name, setting, short_desc FROM pg_settings WHERE source = 'configuration file';
Most configuration parameters are optimally set by pgpro_tune. The sections below list only those parameters that are not configured by the utility or that have a recommended range rather than a single fixed value.
For more details on each configuration parameter, its behavior, and recommended values, refer to Chapter 19.
This section covers configuration parameters that are either set automatically by pgpro_tune but may need manual adjustment, or are not set by the utility and require manual configuration for optimal performance with 1C solutions.
The parameters listed in the table below are automatically configured by pgpro_tune. However, in some cases, they can be changed manually.
Table K.1. Automatically Set Parameters with Recommended Values
| Parameter | Description | Value Set by pgpro_tune | Reason for Manual Changes |
|---|---|---|---|
| shared_buffers | The amount of memory allocated by the database for data caching |
| You can increase this value for better performance. It is not recommended to set it lower than 25% of the available system RAM. |
| work_mem | The maximum amount of memory to be used by a single query operation before writing to temporary disk files |
512MB
| You can increase this value based on the available resources to allow more operations to be performed in memory, reducing I/O. However, setting the value too high may lead to memory exhaustion. It is recommended to increase the parameter value gradually while monitoring the overall memory load. |
| temp_buffers | The maximum amount of memory used for temporary buffers within each database session |
256MB
|
You can change this value to ensure that all temporary tables
are handled correctly. The recommended value is 50% of
work_mem, but not exceeding
256MB, because larger values may lead
to lower performance. The exact value depends on the amount
of memory on the server and the number of concurrent
connections. The more connections and the smaller the amount
of memory, the smaller the parameter value should be.
|
| maintenance_work_mem |
The maximum amount of memory to be used by maintenance database
operations, such as VACUUM or
CREATE INDEX
|
|
You can increase this value because only one of these operations can be executed at a time by a database session. Larger values can improve performance for vacuuming and restoring database dumps.
The recommended maximum value is |
| max_files_per_process | The maximum number of simultaneously open files allowed to each server subprocess |
10000
|
You can increase this value depending on the load. The maximum
allowed value depends on the operating system. If the server
reaches the specified limit, it starts to open and close files,
which affects the performance. You can monitor open files
using the lsof command.
|
| bgwriter_delay | The delay between activity rounds for the background writer |
20ms
| You can change this value manually depending on the load. Note, however, that a too high parameter value increases the load on the checkpoint process and the backend processes, while a too low value results in the full load of one of the cores. |
| bgwriter_lru_multiplier and bgwriter_lru_maxpages | The number of dirty buffers written by the background writer in each round |
4.0 and 4000
respectively
| You can increase these values manually depending on the load to allow the background writer to flush dirty buffers more aggressively. However, setting them too high may cause excessive disk writes. |
| effective_io_concurrency | The number of concurrent disk I/O operations that any individual Postgres Pro session attempts to initiate in parallel |
500
|
You can adjust this value based on your disk type. The
automatically set value is suitable for NVMe SSD disks.
Set this parameter to 200 for SATA SSD
disks and to 1 for HDD disks.
|
| random_page_cost | The planner's estimate of the cost of a non-sequentially-fetched disk page |
1.1
|
You can adjust this value based on your disk type. The
automatically set value is suitable for NVMe SSD disks.
Set this parameter to 1.3 for SATA SSD
disks and to 4.0 for HDD disks.
|
| autovacuum_max_workers | The maximum number of autovacuum processes running simultaneously | CPU cores/2 (but no less than 4 and no more than 100) | You can increase this value because more write requests executed in the system require more autovacuum processes. However, the value must be in the range from 4 to 100. |
temp_tablespacesSpecify the directory in which to create temporary tables and indexes on temporary tables using the temp_tablespaces configuration parameter. Typically, 1C solutions use a lot of temporary tables. To increase performance when working with these tables, locate this directory on separate fast disks.
To properly configure temp_tablespaces, follow
these steps:
Create the directory that will serve as the mountpoint:
sudo mkdir -p path_to_directory
Stop the Postgres Pro server:
systemctl stop server_name
Mount a fast disk to the created directory:
sudo mountdisk_namepath_to_directory
Make the postgres user the owner of the
directory and ensure that only this user has full access:
sudo chown postgres:postgrespath_to_directorysudo chmod 700path_to_directory
Start the Postgres Pro server:
systemctl start server_name
Create a tablespace in the prepared directory:
CREATE TABLESPACEtablespace_nameLOCATION 'path_to_directory';
Set the temp_tablespaces parameter:
temp_tablespaces = tablespace_name
Reload the configuration to apply changes:
SELECT pg_reload_conf();
The parameters below are not set by pgpro_tune but are important for better performance with 1C solutions. Their default values are usually sufficient, but you should be aware of them and consider manual adjustment in specific scenarios.
Table K.2. Manually Configured Parameters
| Parameter | Description | Recommended Value | Reason for Manual Changes |
|---|---|---|---|
| fsync |
Specifies whether the Postgres Pro
server tries to make sure that updates are physically written
to disk by issuing the fsync() system
calls
|
on (default)
| Ensure this parameter is enabled. Although disabling it often leads to a performance benefit, this can result in unrecoverable data corruption in case of a power failure or system crash. When enabled, it ensures the cluster can recover to a consistent state. |
| geqo | Enables the genetic query optimizer (GEQO) that does query planning using the heuristic search |
on (default)
| You can disable this parameter if you prefer the regular exhaustive‑search algorithm, which may produce more efficient plans for queries with many joins but at the cost of increased planning time. |
| geqo_threshold |
The minimum number of FROM items
in a query for which the genetic query optimizer
(GEQO) will be used
|
12 (default)
| You can change this value to control when GEQO is used. Lower values enable GEQO for fewer joins, reducing planning time but possibly producing less efficient plans. Higher values delay GEQO, using exhaustive search for more query patterns. |
| autovacuum | Controls whether the server should run the autovacuum launcher daemon |
on (default)
| Ensure this parameter is enabled. Disabling it leads to increasing the database size and significant performance degradation. |