It is important to properly configure all PostgreSQL instances managed by Database Lab Engine: the single "sync" instance, and clones.
The "sync" instance
The "sync" instance, which is an asynchronous replica by nature, is currently supported only for the physical mode of data directory initialization, see option
syncInstance in job physicalRestore. The only purpose of this PostgreSQL is fetching and replaying WAL segments, maintaining the data directory in sync.
Normally, there is no need in configuring this PostgreSQL instance, as Database Lab Engine controls it fully, using a small value for
shared_buffers, and very reliable values for all the configuration options. The only option that can be controlled by the Databae Lab Engine administrator is
restore_command (see physicalRestore).
PostgreSQL configuration in clones
It is possible and in many cases necessary to configure various PostgreSQL options in clones. It can be done both for logical and physical modes of data directory initialization:
- for the logical mode, all PostgreSQL parameters are to be specified in option
- for the physical mode, these parameters are configured in option
Technically, the specified PostgreSQL parameters are applied to
postgresql.conf located in the data directory when preparing a working snapshot used for thin cloning. All thin clones automatically inherit these values.
When configuring Database Lab Engine, review and set up if needed the following parameters:
shared_buffers: one of the most important parameters. The use of the same value that is used on the source is not recommended because it might lead to out-of-memory errors and the inability to create more than a few clones. Instead, use some moderate value such as
1GB; with this value, if your server has, say, 64 GiB of RAM, then theoretical maximum number of clones is ~63 (some RAM is already used by OS and other apps)
shared_preload_libraries: use the same value as on the source, to allow the same extensions that is used there
work_mem: set the same value as used on the source database unless your Database Lab Engine server lacks memory and there are significant risks of out-of-memory errors
- Query Planning parameters (all of them). This is essential to ensure that cloned PostgreSQL most likely generates the same plans as on the source (specifically, it is crutial for query performance troubleshooting and optimization, including working with EXPLAIN plans)
Use the following SQL on the source database to get all non-default values of the parameters affecting the planner's behavior:
format(' %s: "%s"', name, setting) as configs
source <> 'default'
name ~ '(work_mem$|^enable_|_cost$|scan_size$|effective_cache_size|^jit)'
or name ~ '(^geqo|default_statistics_target|constraint_exclusion|cursor_tuple_fraction)'
or name ~ '(collapse_limit$|parallel|plan_cache_mode|shared_preload_libraries)'
Here is an example of who the
databaseConfigs configuration section is supposed to look: