PostgreSQL settings

Written by

Improved by

Updated at December 25, 2025

Settings dependency on the host class and storage size
Cluster-level settings
User-level settings

For Managed Service for PostgreSQL clusters, you can configure PostgreSQL settings. Some settings are configured at the cluster level, while others, at the user level.

The label next to the setting name shows which interface can be used to set the value of this setting: the management console, CLI, API, or Terraform. The All interfaces label means that all of the above interfaces are supported.

Depending on the selected interface, the same setting may be represented in a different way, e.g.:

Backend flush after in the management console is the same as:
- backend_flush_after in the gRPC API, CLI, and Terraform.
- backendFlushAfter in the REST API.
Default transaction isolation in the user-level settings in the management console is the same as:
- --default-transaction-isolation in the CLI.
- default_transaction_isolation in the gRPC API.
- defaultTransactionIsolation in the REST API.

Settings dependency on the host class and storage size

The values of some PostgreSQL settings can be automatically adjusted when you change the host class or storage size:

If the values were not specified or are not suitable for the new class or size, the default settings for this class or size will be applied.
If the settings you specified manually are suitable for the new class or size, they will not change.

The settings that depend on the host class are:

The settings that depend on the storage size are:

Cluster-level settings

You can use the following settings at the cluster level:

Archive timeout All interfaces

PostgreSQL transaction log archiving interval (ms).

The minimum value is 10000, the maximum is 86400000, and the default is 30000.

For more information, see the archive_timeout setting description in this PostgreSQL article.
Array nulls All interfaces

Manages the recognition of the NULL elements during array insertion. If enabled, they are interpreted as SQL NULL values. If disabled, they are interpreted as the literal string NULL.

This setting is enabled by default.

For more information, see the array_nulls setting description in this PostgreSQL article.
Auto explain log analyze Management console API Terraform

Determines whether query plan statistics are automatically written to the PostgreSQL log, without using the EXPLAIN statement. This allows you to track unoptimized queries. This setting uses the auto_explain module. To enable it, load the auto_explain library.

By default, this setting is disabled, meaning query plan statistics are not logged.

For more information, see the auto_explain.log_analyze setting description in this PostgreSQL article.
Auto explain log buffers Management console API Terraform

Determines whether buffer usage statistics are written to the PostgreSQL log by the auto_explain module. It works the same as the BUFFERS option in the EXPLAIN statement. This setting only applies when Auto explain log analyze is enabled.

By default, this setting is disabled, meaning buffer usage statistics are not logged.

For more information, see the auto_explain.log_buffers setting description in this PostgreSQL article.
Auto explain log min duration Management console API Terraform

Minimum query execution time (in ms) required to activate logging in the auto_explain module.

The minimum and default value is -1 (logging disabled) and the maximum is 2147483647. If set to 0, all query plans are logged, regardless of their execution time.

For more information, see the auto_explain.log_min_duration setting description in this PostgreSQL article.
Auto explain log nested statements Management console API Terraform

Determines whether the auto_explain module will log the execution of nested queries inside SQL functions. This setting only applies when Auto explain log analyze is enabled.

By default, this setting is disabled, meaning only top-level query plans are logged.

For more information, see the auto_explain.log_nested_statements setting description in this PostgreSQL article.
Auto explain log timing Management console API Terraform

Determines whether the auto_explain module will log the execution time for each step of the query plan. It works the same as the TIMING option in the EXPLAIN command. This setting only applies when Auto explain log analyze is enabled.

By default, this setting is disabled, meaning that execution time for individual query steps is not logged.

For more information, see the auto_explain.log_timing setting description in this PostgreSQL article.
Auto explain log triggers Management console API Terraform

Determines whether the auto_explain module will output trigger execution statistics. This setting only applies when Auto explain log analyze is enabled.

By default, this setting is disabled, meaning trigger execution statistics are not reported.

For more information, see the auto_explain.log_triggers setting description in this PostgreSQL article.
Auto explain log verbose Management console API Terraform

Determines whether the auto_explain module will generate detailed logs. It works the same as the VERBOSE option in the EXPLAIN command. This setting only applies when Auto explain log analyze is enabled.

By default, this setting is disabled, meaning no logging details are provided.

For more information, see the auto_explain.log_verbose setting description in this PostgreSQL article.
Auto explain sample rate Management console API Terraform

Fraction of queries logged by the auto_explain module in each session. This setting only applies when Auto explain log analyze is enabled.

The minimum value is 0.0, and the maximum value is 1.0 (all queries are logged). The default value is 1.0.

For more information, see the auto_explain.sample_rate setting description in this PostgreSQL article.
Autovacuum analyze scale factor All interfaces

Fraction of changed or deleted rows in a table that will trigger the autovacuum process to run the ANALYZE command for statistics collection.

The minimum value is 0.0, the maximum is 1.0, and the default is 0.0001.

For more information, see the autovacuum_analyze_scale_factor setting description in this PostgreSQL article.
Autovacuum max workers Management console API Terraform CLI

Maximum number of autovacuum worker processes running in parallel. Autovacuuming runs periodically for each database. It identifies which rows in tables are marked for deletion and removes them.

This setting can range from 1 to 32. The default value depends on the selected host class and is equal to the number of vCPUs on a single host, with a minimum of 3.

For more information, see the autovacuum_max_workers setting description in this PostgreSQL article.
Autovacuum naptime Management console API Terraform

Determines the minimum interval (in ms) between autovacuum jobs. To avoid overloading the database during periods of frequent data changes, increase this setting.

The minimum value is 1000, the maximum is 86400000, and the default is 15000.

For more information, see the autovacuum_naptime setting description in this PostgreSQL article.
Autovacuum vacuum cost delay All interfaces

Time (in ms) the autovacuum process will idle after exceeding the cost limit.

The minimum value is -1 (disables the setting) and the maximum is 100. The default value depends on the selected host class.
- If the number of vCPUs for the selected host class is 10 or more, the default value is set to 5.
- In other cases, the default value is computed as follows:
```
55 - 5 × <number_of_vCPUs_per_host>
```
For more information, see the autovacuum_vacuum_cost_delay setting description in this PostgreSQL article.
Autovacuum vacuum cost limit All interfaces

Cost limit which, when exceeded, will pause the autovacuum process for the duration specified in the Autovacuum vacuum cost delay setting.

The minimum value is -1 (disables the setting) and the maximum is 10000. The default value depends on the selected host class and is computed as follows:
```
150 × <number_of_vCPUs_per_host> + 400
```
Here is an example:

Host class vCPU Default value

s2.small 4 150 × 4 + 400 = 1000

m2.medium 6 150 × 6 + 400 = 1300

For more information, see the autovacuum_vacuum_cost_limit setting description in this PostgreSQL article.
Autovacuum vacuum insert scale factor All interfaces

Fraction of a table’s size that is added to Autovacuum vacuum insert threshold to determine the condition for triggering an autovacuum.

The minimum value is 0.0, the maximum is 1.0, and the default is 0.2.

This feature is supported in PostgreSQL as of version 13.

For more information, see the autovacuum_vacuum_insert_scale_factor setting description in this PostgreSQL article.
Autovacuum vacuum insert threshold All interfaces

Determines the number of rows inserted into a table that will trigger an autovacuum.

The minimum value is -1 (disables the setting), the maximum is 2147483647, and the default is 1000.

This feature is supported in PostgreSQL as of version 13.

For more information, see the autovacuum_vacuum_insert_threshold setting description in this PostgreSQL article.
Autovacuum vacuum scale factor All interfaces

Fraction of changed or deleted rows in a table that will trigger the autovacuum process to run the VACUUM command for reclaiming storage occupied by dead tuples.

The minimum value is 0.0 (disables the setting), the maximum is 1.0, and the default is 0.00001.

For more information, see the autovacuum_vacuum_scale_factor setting description in this PostgreSQL article.
Autovacuum work mem Management console API Terraform CLI

Memory (in bytes) allocated to each autovacuum process.

The minimum value is -1 (disables the setting), the maximum is 2147483647, and the default is -1.

For more information, see the autovacuum_work_mem setting description in this PostgreSQL article.
Backend flush after All interfaces

Maximum amount of data (in KB) a utility process can write to the OS kernel’s page cache. When this amount is exceeded, the DBMS instructs the OS to flush the data to disk. The higher value for this setting reduces the likelihood of write slowdowns when data is flushed to disk by the fsync, e.g., after a checkpoint is completed.

The minimum value is 0 (disables the setting), the maximum is 2048, and the default is 0.

For more information, see the backend_flush_after setting description in this PostgreSQL article.
Backslash quote All interfaces

Controls how a quotation mark is represented within an SQL string.

The allowed values are:
- backslash_quote (BACKSLASH_QUOTE for Terraform, API, and CLI): Quotation mark can be represented as \' (same as on).
- on (BACKSLASH_QUOTE_ON for Terraform, API, and CLI): Quotation mark can be represented as \'.
- off (BACKSLASH_QUOTE_OFF for Terraform, API, and CLI): Quotation mark can only be represented using the standard SQL syntax ''.
- safe_encoding (BACKSLASH_QUOTE_SAFE_ENCODING for Terraform, API, and CLI): Representing a quotation mark as \' is only permitted for client encodings where \ is not used for multibyte characters.
The default value is safe_encoding.

For more information, see the backslash_quote setting description in this PostgreSQL article.
Bgwriter delay All interfaces

Time (in ms) between background writer runs. This process writes new or changed pages from the PostgreSQL buffer cache to the disk. The delay prevents rewriting the same page on every update, thus reducing disk load.

The minimum value is 10, the maximum is 10000, and the default is 200.

For more information, see the bgwriter_delay setting description in this PostgreSQL article.
Bgwriter flush after Management console API Terraform

Maximum amount of data (in KB) processed by the background writer. When this limit is exceeded, the DBMS instructs the OS to flush the data to disk. This setting limits the amount of dirty data in the kernel page cache, reducing the risk of slowdowns when executing fsync at the end of a checkpoint or during the OS’s background writeback process.

The minimum value is 0, the maximum is 2048, and the default is 512.

For more information, see the bgwriter_flush_after setting description in this PostgreSQL article.
Bgwriter lru maxpages All interfaces

Maximum number of PostgreSQL buffer cache pages the background writer can save per activity round. A value of zero disables background writing.

The minimum value is 0, the maximum is 1073741823, and the default is 100.

For more information, see the bgwriter_lru_maxpages setting description in this PostgreSQL article.
Bgwriter lru multiplier All interfaces

Multiplier determining the factor by which the average number of buffers held by the background writeback process will be increased during the next round.

The minimum value is 0, the maximum is 10, and the default is 2.

For more information, see the bgwriter_lru_multiplier setting description in this PostgreSQL article.
Bytea output All interfaces

Defines the bytea output format for binary string values:
- hex (BYTEA_OUTPUT_HEX for Terraform, API, and CLI): Each byte is represented by two hexadecimal characters, e.g., 'SELECT '\xDEADBEEF';'.
- escape (BYTEA_OUTPUT_ESCAPE for Terraform, API, and CLI): Standard PostgreSQL format with ASCII characters only.
The default value is hex.

For more information, see this PostgreSQL article.
Checkpoint completion target All interfaces

Fraction of the checkpoint interval determining the maximum allowed time for a checkpoint to complete. For example, if this value is set to 0.5, the checkpoint will finish with about half the time left until the next checkpoint.

The minimum value is 0.0, the maximum is 1.0, and the default is 0.5.

For more information, see the checkpoint_completion_target setting description in this PostgreSQL article.
Checkpoint flush after Management console API Terraform

The amount of dirty data in memory (in KB) that triggers a flush during a checkpoint. Pages beyond this limit will be flushed to disk and deleted from the OS page cache.

The minimum value is 0, the maximum is 2048, and the default is 256.

For more information, see the checkpoint_flush_after setting description in this PostgreSQL article.
Checkpoint timeout All interfaces

Interval between checkpoints (in ms).

The minimum value is 30000, the maximum is 86400000, and the default is 300000.

For more information, see the checkpoint_timeout setting description in this PostgreSQL article.
Client connection check interval Management console API CLI

Client connection check interval during query execution (in ms). The check polls the server’s connection socket and aborts long queries upon detecting that the connection has been terminated.

The minimum value is 0 (disables checks) and the maximum is 2147483647. By default, the system uses the minimum value.

This feature is supported in PostgreSQL as of version 14.

For more information, see the client_connection_check_interval setting description in this PostgreSQL article.
Client min messages All interfaces

Logging level for messages sent to client applications. Possible values (in ascending order of severity): debug5, debug4, debug3, debug2, debug1, info, notice, warning, error, log, fatal, and panic (for Terraform, API, and CLI, LOG_LEVEL_DEBUG5, LOG_LEVEL_DEBUG4, LOG_LEVEL_DEBUG3, LOG_LEVEL_DEBUG2, LOG_LEVEL_DEBUG1, LOG_LEVEL_INFO, LOG_LEVEL_NOTICE, LOG_LEVEL_WARNING, LOG_LEVEL_ERROR, LOG_LEVEL_LOG, LOG_LEVEL_FATAL, and LOG_LEVEL_PANIC).

The default value is NOTICE.

For more information, see the client_min_messages setting description in this PostgreSQL article.
Constraint exclusion All interfaces

Allows the planner to use table constraints for query optimization.

The allowed values are:
- on (CONSTRAINT_EXCLUSION_ON for Terraform, API, and CLI): Use constraints for all tables.
- off (CONSTRAINT_EXCLUSION_OFF for Terraform, API, and CLI): Do not use constraints.
- partition (CONSTRAINT_EXCLUSION_PARTITION for Terraform, API, and CLI): Only use constraints for child tables and UNION ALL clauses.
The default value is partition.

For more information, see the constraint_exclusion setting description in this PostgreSQL article.
Cursor tuple fraction All interfaces

Defines the planner's estimate for the fraction of rows that will be retrieved via a cursor.

The minimum value is 0.0, the maximum is 1.0, and the default is 0.1.

For more information, see the cursor_tuple_fraction setting description in this PostgreSQL article.
Deadlock timeout Management console API Terraform CLI

Timeout period (in ms) before the system will check for a deadlock condition.

The minimum value is 1, the maximum is 2147483647, and the default is 1000.

For more information, see the deadlock_timeout setting description in this PostgreSQL article.
Default statistics target All interfaces

Maximum number of statistical records per column in database tables. Using these statistics, the planner estimates the number of rows returned by queries in order to choose the most optimal query plan.

For more accurate planner estimates, increase this setting.

The minimum value is 1, the maximum is 10000, and the default is 1000.

For more information, see the default_statistics_target setting description in this PostgreSQL article.
Default transaction isolation All interfaces

This setting determines the default isolation level for new SQL transactions.

The allowed values are:
- read committed (TRANSACTION_ISOLATION_READ_COMMITTED for Terraform, API, and CLI): A query can only see rows that were committed before it started.
- read uncommitted (TRANSACTION_ISOLATION_READ_UNCOMMITTED for Terraform, API, and CLI): In PostgreSQL, this isolation level is identical to read committed.
- repeatable read (TRANSACTION_ISOLATION_REPEATABLE_READ for Terraform, API, and CLI): All queries in the current transaction see only those rows that were committed before the first SELECT or UPDATE query in this transaction.
- serializable (TRANSACTION_ISOLATION_SERIALIZABLE for Terraform, API, and CLI): The strictest isolation level. In PostgreSQL, this isolation level is identical to repeatable read. However, if the interleaving of read and write operations in concurrent serializable transactions is incompatible with their serial execution, one of the transactions will be rolled back with the serialization failure error.
The default value is read committed.

To learn more about isolation levels, see this PostgreSQL article.

The setting applies at the Managed Service for PostgreSQL cluster level, but can be overridden for individual users.
Default transaction read only All interfaces

Sets the default transaction access mode to read only for all tables except temporary ones.

By default, this setting is disabled, meaning that both reads and writes are permitted.

For more information, see the default_transaction_read_only setting description in this PostgreSQL article.
Default with oids All interfaces

Adds an OID column containing a unique row ID. This setting only applies when a table is created:
- Without an explicit WITH OIDS or WITHOUT OIDS clause, OR.
- Using the SELECT INTO statement.
By default, this setting is disabled, meaning no OID column is added.

Support for this feature was removed in PostgreSQL version 12. Use this setting only for compatibility with legacy applications.

For more information, see the default_with_oids setting description in this PostgreSQL article.
Effective cache size All interfaces

An estimate of the effective disk cache size (in bytes) available for a single query. Higher values of this setting increase the possibility of the query planner to use an index scan. Conversely, lower values make the planner more likely to choose a sequential scan.

The minimum value is 0, the maximum is 549755813888 (512 GB), and the default is 107374182400 (100 GB).

For more information, see the effective_cache_size setting description in this PostgreSQL article.
Effective io concurrency All interfaces

Maximum of concurrent disk I/O operations per database session. A higher value increases the number of operations that a PostgreSQL session will try to run concurrently.

The minimum value is 0, the maximum is 1000, and the default is 1.

For more information, see the effective_io_concurrency setting description in this PostgreSQL article.
Enable async append Management console API CLI

Allows the planner to account for asynchronous query plan execution on multiple hosts during data ingestion from external sources.

This setting is enabled by default.

This feature is supported in PostgreSQL as of version 14.

For more information, see the enable_async_append setting description in this PostgreSQL article.
Enable bitmapscan All interfaces

Allows the planner to perform a Bitmap Index Scan, even if it is not explicitly specified in the query. This access method is similar to a regular index scan, but occurs in two stages:
1. A Bitmap Index Scan builds a bitmap, marking all relevant row locations.
2. Then the system performs a Bitmap Heap Scan on the table. This approach has the following benefits:
  - Sequential page reads increase the chance of hitting the OS cache.
  - The system reads each page only once.
This setting is most effective for repeated queries to the same tables.

This setting is enabled by default.

For more information, see the enable_bitmapscan setting description in this PostgreSQL article.
Enable gathermerge Management console API CLI

Allows the planner to use the Gather Merge node to merge rows produced by parallel processes while preserving the original output order.

This setting is enabled by default.

This feature is supported in PostgreSQL as of version 14.

For more information, see the enable_gathermerge setting description in this PostgreSQL article.
Enable hashagg All interfaces

Allows the planner to build a hash table from the initial results, even if it is not explicitly specified in the query. This setting is most effective for frequent, unsorted queries on large tables.

This setting is enabled by default.

For more information, see the enable_hashagg setting description in this PostgreSQL article.
Enable hashjoin All interfaces

Allows the planner to perform hash joins (JOIN) on tables, even if it is not explicitly specified in the query. This setting is most effective when working with large data volumes or when the tables are not pre-sorted by their join keys.

This setting is enabled by default.

For more information, see the enable_hashjoin setting description in this PostgreSQL article.
Enable incremental sort All interfaces

Allows the planner to use incremental sort. This sorting method reduces query execution time and the amount of RAM required when you need to sort by multiple columns, and one or more of them are already sorted.

This setting is enabled by default.

This feature is supported in PostgreSQL as of version 13.

For more information, see the enable_incremental_sort setting description in this PostgreSQL article.
Enable indexonlyscan All interfaces

Allows the planner to perform an index-only scan, even if it is not explicitly specified in the query.

This setting is enabled by default.

For more information, see the enable_indexonlyscan setting description in this PostgreSQL article.
Enable indexscan All interfaces

Allows the planner to use an index scan, even if it is not explicitly specified in the query.

This setting is enabled by default.

For more information, see the enable_indexscan setting description in this PostgreSQL article.
Enable material All interfaces

Allows the planner to use materialize nodes.

This setting is enabled by default.

For more information, see the enable_material setting description in this PostgreSQL article.
Enable mergejoin All interfaces

Allows the query planner to use the merge JOIN method.

This setting is enabled by default.

For more information, see the enable_mergejoin setting description in this PostgreSQL article.
Enable nestloop All interfaces

Allows the planner to use the nested loop JOIN method. When this setting is disabled, the planner will use alternative methods.

This setting is enabled by default.

For more information, see the enable_nestloop setting description in this PostgreSQL article.
Enable parallel append All interfaces

Allows the planner to use parallel-aware append.

This setting is enabled by default.

For more information, see the enable_parallel_append setting description in this PostgreSQL article.
Enable parallel hash All interfaces

Allows the planner to use parallel hash joins. This setting only applies when Enable hashjoin is turned on.

This setting is enabled by default.

For more information, see the enable_parallel_hash setting description in this PostgreSQL article.
Enable partition pruning All interfaces

Allows the planner to prune unnecessary partitions when building query plans for partitioned tables.

This setting is enabled by default.

For more information, see the enable_partition_pruning setting description in this PostgreSQL article.
Enable partitionwise aggregate All interfaces

Allows the planner to perform grouping and aggregate operations, partition-by-partition.

By default, this setting is disabled, meaning that the planner ignores partitions.

For more information, see the enable_partitionwise_aggregate setting description in this PostgreSQL article.
Enable partitionwise join All interfaces

Allows the planner to use partition-wise joins. This setting enables the execution of joins on partitioned tables by joining their matching partitions.

By default, this setting is disabled, meaning that the planner ignores partitions.

For more information, see the enable_partitionwise_join setting description in this PostgreSQL article.
Enable seqscan All interfaces

Allows the planner to use sequential table scans.

This setting is enabled by default.

For more information, see the enable_seqscan setting description in this PostgreSQL article.
Enable sort All interfaces

Allows the planner to use explicit sort operations.

This setting is enabled by default.

For more information, see the enable_sort setting description in this PostgreSQL article.
Enable tidscan All interfaces

Allows the planner to use TID (tuple ID) scans for faster query performance.

This setting is enabled by default (the planner uses TID scans).

For more information, see the enable_tidscan setting description in this PostgreSQL article.
Escape string warning All interfaces

Enables a warning for situations when a backslash (\) appears in a regular string constant (with the '...' syntax). This setting only applies if Standard conforming strings is disabled.

This setting is enabled by default.

For more information, see the escape_string_warning setting description in this PostgreSQL article.
Exit on error All interfaces

Enables session termination upon any query error.

This setting is disabled by default.

For more information, see the exit_on_error setting description in this PostgreSQL article.
Force parallel mode All interfaces

Allows query parallelization for testing purposes:
- off (FORCE_PARALLEL_MODE_OFF for Terraform, API, and CLI): Enable parallel mode only if it is expected to increase performance.
- on (FORCE_PARALLEL_MODE_ON for Terraform, API, and CLI): Force parallel mode for all queries that can be executed safely in parallel.
- regress (FORCE_PARALLEL_MODE_REGRESS for Terraform, API, and CLI): Equivalent to on, but generates output identical to the off state.
The default value is off.

For more information, see the force_parallel_mode setting description in this PostgreSQL article.
From collapse limit All interfaces

As long as the number of elements in the FROM clause does not exceed this setting, the query planner will merge subqueries with the outer query. Decreasing this value reduces planning time, but can result in a less efficient query plan.

The minimum value is 1, the maximum is 2147483647, and the default is 8.

For more information, see the from_collapse_limit setting description in this PostgreSQL article.
Geqo Management console API

Enables genetic query optimization (GEQO).

Default value: false (disables genetic optimization).

For more information, see the geqo setting description in this PostgreSQL article.
Geqo effort Management console API

Sets the trade-off between query planning time and plan quality in the GEQO algorithm.

A higher value increases query planning time but also raises the probability of selecting an efficient query plan. This setting does not directly control the algorithm; instead, it is used to calculate default values for other settings.

The minimum value is 1, the maximum is 10, and the default is 5.

For more information, see the geqo_effort setting description in this PostgreSQL article.
Geqo generations Management console API

Defines the number of generations in the GEQO algorithm.

The practical range for this setting is from 100 to 1000.

If this setting’s value is left to default, i.e.,0, the number of generations is selected based on Geqo pool size.

For more information, see the geqo_generations setting description in this PostgreSQL article.
Geqo pool size Management console API

Specifies the number of individuals in the genetic population for the GEQO algorithm.

This value cannot be 1. The practical range for this setting is from 100 to 1000.

If this setting’s value is left to default, i.e., 0, the number of individuals is selected based on Geqo effort and the number of tables in the query.

For more information, see the geqo_pool_size setting description in this PostgreSQL article.
Geqo seed Management console API

Defines the seed value for the GEQO‘s random number generator used when exploring the join order search space.

Changing this setting alters the set of join paths explored, which may either improve or degrade the effectiveness of the resulting path.

The minimum value is 0, the maximum is 1, and the default is 0.

For more information, see the geqo_seed setting description in this PostgreSQL article.
Geqo selection bias Management console API

Defines the selection pressure within the GEQO population.

The minimum value is 1.50, the maximum is 2.00, and the default is 2.00.

For more information, see the geqo_selection_bias setting description in this PostgreSQL article.
Geqo threshold Management console API

The GEQO algorithm will only be used to plan queries where the number of tables in the FROM clause is equal to or greater than this setting.

For small queries, it is generally better to use the standard planner performing an exhaustive search. However, for queries involving a large number of tables, an exhaustive search method may take longer than just using a suboptimal plan.

The minimum value is 2, the maximum is 2147483647, and the default is 12.

For more information, see the geqo_threshold setting description in this PostgreSQL article.
Gin pending list limit All interfaces

Defines the maximum size (in bytes) of the GIN index's pending list. This list is used when fastupdate mode is enabled. If the pending list exceeds the specified limit, all its entries are moved to the main GIN index structure and the list is cleared.

The minimum value is 64, the maximum is 2147483647 (2 GB), and the default is 4194304.

For more information, see the gin_pending_list_limit setting description in this PostgreSQL article.
Hash mem multiplier Management console API Terraform

Defines the maximum amount of memory available for hash table operations. The total amount is calculated as this setting’s value multiplied by Work mem.

The minimum value is 0.0, the maximum is 1000.0, and the default is 1.0.

This feature is supported in PostgreSQL as of version 13.

For more information, see the hash_mem_multiplier setting description in this PostgreSQL article.
Idle in transaction session timeout All interfaces

Open transaction idle timeout (in ms). If exceeded, the transaction's session will be terminated.

The minimum value is 0, the maximum is 2147483647, and the default is 0.

For more information, see the idle_in_transaction_session_timeout setting description in this PostgreSQL article.
Jit Management console API Terraform

Enables Just-in-Time (JIT) compilation of PostgreSQL queries. If this setting is enabled, SQL queries are compiled into machine code at runtime, which accelerates the execution of complex CPU-intensive queries.

This setting is disabled by default.

For more information, see the jit setting description in this PostgreSQL article.
Join collapse limit All interfaces

Defines a threshold for the number of elements in the FROM list. Until it is exceeded, the planner will transfer explicit JOIN constructs (with the exception of FULL JOIN) into the list. Decreasing this value reduces planning time, but can result in a less efficient query plan.

The minimum value is 1, the maximum is 2147483647, and the default is 8.

For more information, see the join_collapse_limit setting description in this PostgreSQL article.
Lo compat privileges Management console API CLI Terraform

Disables access permission checks for large objects. Prior to version 9.0, access permissions did not apply to large objects, allowing any user to read and write them. Enable this setting if you need compatibility with pre-9.0 releases of PostgreSQL.

This setting is disabled by default, meaning that access permission checks are enabled.

For more information, see the lo_compat_privileges setting description in this PostgreSQL article.
Lock timeout All interfaces

Specifies the waiting time (in ms) for the lock to be released. Locks can be used for tables, indexes, rows, and other database objects. If the timeout for an operation has expired, the operation is aborted.

The minimum value is 0 (disables the timeout; allowing the system to wait for a lock indefinitely), the maximum is 2147483647, and the default is 1000.

For more information, see the lock_timeout setting description in this PostgreSQL article.

The setting applies at the Managed Service for PostgreSQL cluster level, but can be overridden for individual users.
Log checkpoints All interfaces

Enables logging of checkpoints and server restarts.

The setting is disabled by default (no logging).

For more information, see the log_checkpoints setting description in this PostgreSQL article.
Log connections Management console API CLI Terraform

Enables logging of all PostgreSQL server connection attempts, including those where client authentication was successful.

The setting is disabled by default (no logging).

For more information, see the log_connections setting description in this PostgreSQL article.
Log disconnections Management console API CLI Terraform

Enables logging of session terminations.

The setting is disabled by default (no logging).

For more information, see the log_disconnections setting description in this PostgreSQL article.
Log duration Management console API CLI Terraform

Enables logging of the execution time for each completed command.

The setting is disabled by default (no logging).

For more information, see the log_duration setting description in this PostgreSQL article.
Log error verbosity Management console API CLI Terraform

Defines the verbosity level for PostgreSQL log entries. Log detail levels, from least to most verbose:
- terse (LOG_ERROR_VERBOSITY_TERSE for Terraform, CLI, and API): DETAIL, HINT, QUERY, and CONTEXT fields are excluded from the error message.
- default (LOG_ERROR_VERBOSITY_DEFAULT for Terraform, CLI, and API): Default.
- verbose (LOG_ERROR_VERBOSITY_VERBOSE for Terraform, CLI, and API): Error message includes the SQLSTATE error code, source filename, function name, and the line number where the error occurred.
For more information, see the log_error_verbosity setting description in this PostgreSQL article.
Log lock waits Management console API CLI Terraform

Controls the logging of long lock waits. When this setting is enabled, the system writes a log entry whenever a PostgreSQL session’s lock acquisition time exceeds the Deadlock timeout value.

This setting is disabled by default.

For more information, see the log_lock_waits setting description in this PostgreSQL article.
Log min duration sample Management console API Terraform

This setting works the same as Log min duration statement, but only applies to the statements listed in Log statement sample rate.

A value of 0 enables execution time logging for all listed statements.

The minimum value is -1 (disables execution time logging), the maximum is 2147483647, and the default is -1.

This feature is supported in PostgreSQL as of version 13.

For more information, see the log_min_duration_sample setting description in this PostgreSQL article.
Log min duration statement Management console API CLI Terraform

Defines the minimum statement runtime (in ms) required for it to be logged.

A value of 0 enables execution time logging for all statements.

The minimum value is -1 (disables execution time logging), the maximum is 2147483647, and the default is -1.

For more information, see the log_min_duration_statement setting description in this PostgreSQL article.

The setting applies at the Managed Service for PostgreSQL cluster level, but can be overridden for individual users.
Log min error statement Management console API CLI Terraform

Defines the logging level for SQL runtime errors. The system logs statement execution information if the statement completes with an error of the specified severity level or higher. The possible values are DEBUG5, DEBUG4, DEBUG3, DEBUG2, DEBUG1, INFO, NOTICE, WARNING, ERROR, LOG, FATAL and PANIC.

The default value is ERROR. This means PostgreSQL will log all statements that ended with an error of the ERROR, LOG, FATAL, or PANIC severity level.

To disable logging for most failed statements, select the PANIC value.

For more information, see the log_min_error_statement setting description in this PostgreSQL article.
Log min messages Management console API CLI Terraform

Defines the PostgreSQL logging level. The system logs all messages with the selected severity level or higher. The possible values, from least to most severe, are: DEBUG5, DEBUG4, DEBUG3, DEBUG2, DEBUG1, INFO, NOTICE, WARNING, ERROR, LOG, FATAL, and PANIC.

The default value is WARNING. This means PostgreSQL will log all messages with the WARNING, ERROR, LOG, FATAL, and PANIC severity level.

To disable logging for most messages, select PANIC.

For more information, see the log_min_messages setting description in this PostgreSQL article.
Log parameter max length Management console API CLI Terraform

Shortens the value of each bound SQL parameter to the specified number of bytes for output with SQL statements in non-error log messages.

A value of 0 disables logging.

The minimum value is -1 (parameter values are logged in full) and the maximum is 1073741823. By default, the system uses the minimum value.

This feature is supported in PostgreSQL as of version 13.

For more information, see the log_parameter_max_length setting description in this PostgreSQL article.
Log parameter max length on error All interfaces

Shortens the value of each bound SQL parameter to the specified number of bytes for output with SQL statements in error log messages.

A value of 0 disables logging.

The minimum value is -1 (parameter values are logged in full) and the maximum is 1073741823. By default, the system uses the minimum value.

This feature is supported in PostgreSQL as of version 13.

For more information, see the log_parameter_max_length_on_error setting description in this PostgreSQL article.
Log recovery conflict waits Management console API CLI

Enables logging of replication delays caused by recovery conflicts during WAL reading. The system adds log entries if conflict resolution timeout exceeds the Deadlock timeout value.

This setting is disabled by default.

This feature is supported in PostgreSQL as of version 14.

For more information, see the log_recovery_conflict_waits setting description in this PostgreSQL article.
Log statement Management console API CLI Terraform

Filter for SQL statements to be logged by PostgreSQL:
- none (LOG_STATEMENT_NONE for Terraform, CLI, and API): No SQL statements are logged.
- ddl (LOG_STATEMENT_DDL for Terraform, CLI, and API): System logs DDL statements, e.g., CREATE, ALTER, DROP etc.
- mod (LOG_STATEMENT_MOD for Terraform, CLI, and API): System logs ddl-statements along with data modification commands, e.g., INSERT, UPDATE, etc.
- all (LOG_STATEMENT_ALL for Terraform, CLI, and API): System logs all SQL statements.
The default value is none.

For more information, see the log_statement setting description in this PostgreSQL article.

The setting applies at the Managed Service for PostgreSQL cluster level, but can be overridden for individual users.
Log statement sample rate Management console API Terraform

Fraction of SQL statements that will be logged as a supplement to statements logged for other reasons.

The minimum value is 0.0, the maximum is 1.0, and the default is 0.0.

This feature is supported in PostgreSQL as of version 13.

For more information, see the log_statement_sample_rate setting description in this PostgreSQL article.
Log temp files Management console API CLI Terraform

Sets the minimum size of a temporary file, triggering PostgreSQL to log its details upon deletion.

The minimum value is -1 (information about deleted files is not logged) and the maximum is 2147483647 (2 GB). A value of 0 enables logging of all temporary file names and sizes. The default value is -1.

For more information, see the log_temp_files setting description in this PostgreSQL article.
Log transaction sample rate Management console API Terraform

Fraction of transactions whose statements will be logged as a supplement to statements logged for other reasons.

The minimum value is 0.0, the maximum is 1.0, and the default is 0.0.

This feature is supported in PostgreSQL as of version 12.

For more information, see the log_transaction_sample_rate setting description in this PostgreSQL article.
Logical decoding work mem All interfaces

Maximum memory (in bytes) allocated for logical decoding before writing to a local storage. This setting limits the amount of memory used by the logical replicatoin connection.

The minimum value is 65536 (64 KB), the maximum is 1099511627776 (1 TB), and the default is 67108864 (64 MB).

This feature is supported in PostgreSQL as of version 13.

For more information, see the logical_decoding_work_mem setting description in this PostgreSQL article.
Maintenance io concurrency All interfaces

Maximum concurrent disk I/O operations per session during PostgreSQL maintenance involving VACUUM, CREATE INDEX, and ALTER TABLE ADD FOREIGN KEY statements. Increasing this value will allow more PostgreSQL maintenance statements to run in parallel.

The minimum value is 0, the maximum is 1000, and the default is 10.

This feature is supported in PostgreSQL as of version 13.

For more information, see the maintenance_io_concurrency setting description in this PostgreSQL article.
Maintenance work mem All interfaces

Maximum memory (in bytes) available for PostgreSQL maintenance operations, such as VACUUM, CREATE INDEX, and ALTER TABLE ADD FOREIGN KEY. The value must be a multiple of 1,024.

The minimum value is 1048576 (1 MB), the maximum is 137438953472 (128 GB), and the default is 67108864 (64 MB).

For more information, see the maintenance_work_mem setting description in this PostgreSQL article.
Max connections Management console API CLI Terraform

Maximum simultaneous PostgreSQL host connections.

The minimum value is 1. The maximum and default values depend on the selected host class and are calculated as follows:
```
200 × <number_of_vCPUs_per_host>
```
Hosts with a guaranteed vCPU share below 100% (burstable) use the fixed maximum value of 200.

By default, the system uses the maximum value.

Warning

Managed Service for PostgreSQL reserves 15 connections for service users per PostgreSQL host. For example, if the cluster has Max connections 100, you can reserve a maximum of 85 connections for cluster users.

For more information, see the max_connections setting description in this PostgreSQL article.
Max locks per transaction Management console API CLI Terraform

Average number of objects that can be locked by a single transaction. Individual transactions can lock more objects as long as the total number of objects locked by all transactions fits in the lock table.

The minimum value is 10, the maximum is 2147483647, and the default is 64.

For more information, see the max_locks_per_transaction setting description in this PostgreSQL article.
Max logical replication workers Management console

Maximum number of PostgreSQL logical replication processes.

This setting can range from 4 to 100. By default, the system uses the minimum value.

For more information, see the max_logical_replication_workers setting description in this PostgreSQL article.
Max parallel maintenance workers All interfaces

Maximum number of parallel PostgreSQL processes that can be initiated by a single maintenance statement, e.g., CREATE INDEX.

The minimum value is 0, the maximum is 1024, and the default is 2.

For more information, see the max_parallel_maintenance_workers setting description in this PostgreSQL article.
Max parallel workers All interfaces

Maximum number of parallel PostgreSQL processes.

The minimum value is 0, the maximum is 1024, and the default is 8.

For more information, see the max_parallel_workers setting description in this PostgreSQL article.
Max parallel workers per gather All interfaces

Maximum number of parallel processes that can be launched by a single Gather node.

The minimum value is 0, the maximum is 1024, and the default is 2.

For more information, see the max_parallel_workers_per_gather setting description in this PostgreSQL article.
Max pred locks per transaction Management console API CLI Terraform

Average number of objects lockable by predicate locks per transaction. Individual transactions can lock more objects than specified as long as the total number of objects locked by all transactions fits in the lock table.

The minimum value is 10, the maximum is 2147483647 (2 GB), and the default is 64.

For more information, see the max_pred_locks_per_transaction setting description in this PostgreSQL article.
Max prepared transactions Management console API CLI Terraform

Maximum number of transactions that can be in a prepared state simultaneously.

The minimum value is 0, the maximum is 262143, and the default is 0.

For more information, see the max_prepared_transactions setting description in this PostgreSQL article.
Max replication slots Management console

Maximum number of replication slots. Replication slots automatically ensure that WAL (Write-Ahead Log) files are preserved until they have been received by all replicas.

This setting can range from 20 to 100. By default, the system uses the minimum value.

For more information, see the max_replication_slots setting description in this PostgreSQL article.
Max slot wal keep size Management console API Terraform

Maximum size (in bytes) of the Write-Ahead Log (WAL) files stored on the master host during replication.

The minimum value is 67108864 (64 MB), the maximum is 50% of the storage size, and the default is -1 (unlimited). This value must be a multiple of 1,024.

This feature is supported in PostgreSQL as of version 13.

For more information, see the max_slot_wal_keep_size setting description in this PostgreSQL article.
Max standby streaming delay All interfaces

The timeout period (in ms) before a hot standby replica begins terminating the queries conflicting with pending WAL updates.

The minimum value is -1 (unlimited waiting time), the maximum is 2147483647, and the default is 30000 (30 seconds).

For more information, see the max_standby_streaming_delay setting description in this PostgreSQL article.
Max wal senders Management console

Maximum number of parallel connections allowed from replication source hosts.

This setting can range from 20 to 100. By default, the system uses the minimum value.

For more information, see the max_wal_senders setting description in this PostgreSQL article.
Max wal size All interfaces

Maximum WAL file size (in bytes) that triggers automatic checkpoints.

The minimum value is 2. The maximum value equals 10% of the storage size, capped at 8589934592 (8 GB). By default, the system uses the maximum value.

For more information, see the max_wal_size setting description in this PostgreSQL article.
Max worker processes Management console API CLI Terraform

Maximum number of PostgreSQL background processes that can be run on the current system.

The minimum value is 0, the maximum is 1024, and the default is 8.

For more information, see the max_worker_processes setting description in this PostgreSQL article.
Min wal size All interfaces

WAL disk usage threshold (in bytes) triggering the deletion of old WAL files during checkpoints.

The minimum value is 2. The maximum value equals 5% of the storage size, capped at 1073741824 (1 GB). By default, the system uses the maximum value.

For more information, see the min_wal_size setting description in this PostgreSQL article.
Old snapshot threshold Management console API CLI Terraform

Minimum time (in ms) a snapshot can be safely used for query execution without risk of an error.

The minimum and default values are -1 (unlimited), and the maximum is 86400000 (24 hours).

For more information, see the old_snapshot_threshold setting description in this PostgreSQL article.
Online analyze enable Management console API

Enables the online_analyze module, which updates statistics after the INSERT, UPDATE, DELETE, and SELECT INTO operations on the target tables.
Operator precedence warning All interfaces

Enables the query parser to issue warnings for all constructs whose behavior changed due to the precedence rule updates in version 9.5.

This setting is disabled by default.

Support for this feature was removed in PostgreSQL version 14.

For more information, see the operator_precedence_warning setting description in this PostgreSQL article.
Parallel leader participation All interfaces

Allows the leader process to execute sections of the query plan below Gather and Gather Merge nodes without waiting for worker processes.

This setting is enabled by default.

For more information, see the parallel_leader_participation setting description in this PostgreSQL article.
Pg hint plan debug print Management console API Terraform

Debug output configuration and verbosity for the pg_hint_plan module. This setting only applies when Pg hint plan enable hint is enabled. The possible values, in ascending order of verbosity, are:
- off (PG_HINT_PLAN_DEBUG_PRINT_OFF for Terraform and API): The output is disabled.
- on (PG_HINT_PLAN_DEBUG_PRINT_ON for Terraform and API): Default.
- detailed (PG_HINT_PLAN_DEBUG_PRINT_DETAILED for Terraform and API).
- verbose (PG_HINT_PLAN_DEBUG_PRINT_VERBOSE for Terraform and API).
Pg hint plan enable hint Management console API Terraform

Enables the pg_hint_plan module, which adjusts automatic query plans by applying hints written as simple directives within special SQL comments.
Pg hint plan enable hint table Management console API Terraform

Enables the use of the hint_plan.hints table containing hints for the pg_hint_plan module that you can use when you cannot modify queries. This table has the following columns:
- id: Unique row ID of a hint. This column is populated automatically.
- norm_query_string: Pattern for selecting queries targeted by the hint. Replace constants with the ? character. Whitespace characters are treated as part of the pattern and affect matching.
- application_name: Initiating application for the sessions where the hint applies.
- hint: Hints not wrapped in comment markers.
Pg hint plan message level Management console API Terraform

Verbosity level for the pg_hint_plan debug messages written to the PostgreSQL log. This setting only applies when Pg hint plan enable hint is enabled. The possible values are:
- error
- warning
- notice
- info (default)
- log
- debug
Pg qualstats enabled Management console API CLI

Enables the pg_qualstats module to collect statistics on predicates in WHERE operators and JOIN blocks. This setting is used to analyze the most frequently evaluated query predicates.
Pg qualstats max Management console API CLI

Maximum number of predicates tracked by the pg_qualstats module. This setting only applies when Pg qualstats enabled is on.

The minimum value is 100, the maximum is 2147483647, and the default is 1000.
Pg qualstats resolve oids Management console API CLI

Enables table OID-to-name recovery during query statistics collection for the pg_qualstats module. This setting simplifies data analysis, but requires more disk space to store statistics. It only applies when Pg qualstats enabled is on.

This setting is disabled by default.
Pg qualstats sample rate Management console API CLI

Fraction of queries (0-1) for which statistics are collected by the pg_qualstats module. This setting only applies when Pg qualstats enabled is on. Special setting values:
- 0: Disables the setting.
- 1: All queries are analyzed.
- -1: Automatic mode. The sampling rate is the reciprocal of the Max connections setting and depends on the selected host class.
The default value is -1.
Pg qualstats track constants Management console API CLI

Enables tracking of previously encountered predicates in subsequent queries during statistics collection by the pg_qualstats module. This setting only applies when Pg qualstats enabled is on.

This setting is enabled by default.
Plan cache mode Management console API Terraform

Determines the query plan type, i.e., generic or custom, that will be used to execute prepared statements. The possible values are:
- auto (PLAN_CACHE_MODE_AUTO for Terraform and API): Automatic selection.
- force_custom_plan (PLAN_CACHE_MODE_FORCE_CUSTOM_PLAN for Terraform and API): Forces the use of custom plans.
- force_generic_plan (PLAN_CACHE_MODE_FORCE_GENERIC_PLAN for Terraform and API): Forces the use of generic plans.
The default value is auto.

This feature is supported in PostgreSQL as of version 12.

For more information, see this PostgreSQL article.
Plantuner fix empty table Management console API

Allows the plantuner module to hide individual indexes from the planner to prevent their use in query plans.

This setting is disabled by default.
Quote all identifiers All interfaces

Forces quoting all identifiers in SQL queries generated from a database, e.g., during an EXPLAIN operation.

This setting is disabled by default.

For more information, see the quote_all_identifiers setting description in this PostgreSQL article.
Random page cost All interfaces

Defines the planner's estimated cost of reading one arbitrary disk page. If this setting’s value is less than Seq page cost, the planner will favor index scans.

The minimum value is 0 and the default is 1.

For more information, see the random_page_cost setting description in this PostgreSQL article.
Row security All interfaces

Manages processing of queries subject to at least one row-level security policy. If you disable this setting, such queries will return an error.

This setting is enabled by default.

For more information, see the row_security setting description in this PostgreSQL article.
Search path All interfaces

Comma-separated list of database schema names. When accessing objects in these schemas, the schema name can be omitted.

The default value is $user, public, i.e., the current user’s schema and the public schema.

For more information, see the search_path setting description in this PostgreSQL article.
Seq page cost All interfaces

Defines the planner's estimated cost of reding one page from disk during a series of sequential reads.

The minimum value is 0 and the default is 1.

For more information, see the seq_page_cost setting description in this PostgreSQL article.
Session duration timeout Management console API CLI

Maximum TTL (in ms) of the longest active session or transaction. This setting only applies to sessions with the active or idle in transaction status.

To prevent this setting from impacting cluster performance, the system checks the longest transaction/session at certain intervals. The interval between checks is chosen randomly within a range of 5 to 10 minutes. For example, if you set this parameter to 1000, a session will terminate within 1 second + 5 to 10 minutes.

A value higher than default may increase the database size and slow down the operating system.

The minimum value is 0 (no limit on active session/transaction TTL), the maximum value is 2147483647, and the default is 43200000 (12 hours). The minimum adjustment granularity for this setting is 1000.

Learn more about session statuses in this PostgreSQL article.
Shared buffers Management console API CLI Terraform

Memory (in bytes) that PostgreSQL can use for shared memory buffers.

The minimum value is 131072 (128 KB). The maximum value depends on the selected host class and is equal to 80% of the Managed Service for PostgreSQL cluster host's total RAM. The default value is set to 25% of the total RAM, capped at 8 GB.

For more information, see the shared_buffers setting description in this PostgreSQL article.
Shared preload libraries Management console API Terraform

Comma-separated list of shared libraries to load upon PostgreSQL server startup. These libraries are required for some PostgreSQL extensions.

For more information, see the shared_preload_libraries setting description in this PostgreSQL article.
Standard conforming strings Management console API Terraform

Enables interpreting backslashes (\) in regular string constants ('...') as a literal rather than a special character, as per SQL standard.

This setting is enabled by default.

For more information, see the standard_conforming_strings setting description in this PostgreSQL article.
Statement timeout Management console API CLI Terraform

Maximum statement runtime (in ms) before it is aborted.

The minimum value is 0, the maximum is 2147483647, and the default is 0.

For more information, see the statement_timeout setting description in this PostgreSQL article.
Synchronize seqscans All interfaces

Enables synchronized scanning for large table sequential scans, causing concurrent operations to read the same block at roughly the same time. When this setting is disabled, a sequential scan will always start from the beginning of the table, reverting to pre-8.3 behavior.

This setting is enabled by default.

For more information, see the synchronize_seqscans setting description in this PostgreSQL article.
Synchronous commit All interfaces

Determines the WAL processing stage at which a transaction is considered committed.

The allowed values are:
Management console/CLI

Terraform

API
- on: Transaction is committed once its WAL record is written to the master’s disk and to the disks of the quorum of replicas.
- off: Transaction is committed even if its data has not yet been flushed to WAL. Due to the asynchronous nature of writes, transaction data in this case can be lost in the event of a disk subsystem failure.
- local: Transaction is committed once its WAL record is written to the master’s disk.
- remote_write: Transaction is committed once its WAL record is written to the master’s disk and every quorum replica has received the WAL and passed it to its operating system for writing to disk. If the master’s disk fails and the operating system on a quorum replica crashes, transaction data with this synchronization level can be lost.
- remote_apply: Transaction is committed once its WAL record is written to the master’s disk and every quorum replica has received and processed the changes from the WAL.
The default value is on.
- 1 or "SYNCHRONOUS_COMMIT_ON": Transaction is committed once its WAL record is written both to the master’s disk and to the disk of every quorum replica.
- 2 or "SYNCHRONOUS_COMMIT_OFF": Transaction is committed even before its data has been written to the WAL. Due to the asynchronous nature of writes, transaction data in this case can be lost in the event of a disk subsystem failure.
- 3 or "SYNCHRONOUS_COMMIT_LOCAL": Transaction is committed once its WAL record is written to the master’s disk.
- 4 or "SYNCHRONOUS_COMMIT_REMOTE_WRITE": Transaction is committed once its WAL record is written to the master’s disk, and every quorum replica has received the WAL and passed it to its operating system for writing to disk. If the master’s disk fails and the operating system on a quorum replica crashes, transaction data with this synchronization level can be lost.
- 5 or "SYNCHRONOUS_COMMIT_REMOTE_APPLY": Transaction is committed once its WAL record is written to the master’s disk and every quorum replica has received and processed the changes from the WAL.
The default value is "SYNCHRONOUS_COMMIT_ON".
- SYNCHRONOUS_COMMIT_ON: Transaction is committed once its WAL record is written both to the master’s disk and to the disks of every quorum replica.
- SYNCHRONOUS_COMMIT_OFF: Transaction is committed even before its data has been written to the WAL. Due to the asynchronous nature of writes, transaction data in this case can be lost in the event of a disk subsystem failure.
- SYNCHRONOUS_COMMIT_LOCAL: Transaction is committed once its WAL record is written to the master’s disk.
- SYNCHRONOUS_COMMIT_REMOTE_WRITE: Transaction is committed once its WAL record is written to the master’s disk and every quorum replica has received the WAL and passed it to its operating system for writing to disk. If the master’s disk fails and the operating system on a quorum replica crashes, transaction data with this synchronization level can be lost.
- SYNCHRONOUS_COMMIT_REMOTE_APPLY: Transaction is committed once its WAL record is written to the master’s disk and every quorum replica has received and processed the changes from the WAL.
The default value is SYNCHRONOUS_COMMIT_ON.
For more information, see the synchronous_commit setting description in this PostgreSQL article.

The setting applies at the Managed Service for PostgreSQL cluster level, but can be overridden for individual users.
Temp buffers All interfaces

Maximum memory (in bytes) allocated for temporary buffers in each session.

The minimum value is 100, the maximum is 1073741823 (1 GB), and the default is 8388608 (8 MB).

For more information, see the temp_buffers setting description in this PostgreSQL article.
Temp file limit Management console API CLI Terraform

Maximum disk space (in bytes) a single process can use for temporary files. Any transaction attempting to exceed this limit will be aborted.

Large queries are executed on disk rather than in memory. Excessively large queries overload the disk and impede the execution of other queries. This setting prevents the execution of performance-degrading queries by limiting the size of temporary files.

The default value is -1 (no limits), and the maximum is 2147483647 (2 GB).

For more information, see the temp_file_limit setting description in this PostgreSQL article.

The setting applies at the Managed Service for PostgreSQL cluster level, but can be overridden for individual users.
Timezone All interfaces

Timezone for time input and output.

The default value is Europe/Moscow.

For more information, see the timezone setting description in this PostgreSQL article.
Track activity query size Management console API Terraform

Memory (in bytes) per active session to store the text of the currently executed statement.

The minimum value is 100, the maximum is 102400, and the default is 1024.

For more information, see the track_activity_query_size setting description in this PostgreSQL article.
Transform null equals All interfaces

Enables treating the condition expression = NULL as expression IS NULL. The condition is true if the expression is NULL, and false otherwise. As per SQL standard, expression = NULL always returns NULL (an unknown value).

By default, this setting is disabled, meaning that the SQL standard is in effect.

For more information, see the transform_null_equals setting description in this PostgreSQL article.
Vacuum cleanup index scale factor All interfaces

Fraction of index rows used in the previous statistics sample. During the VACUUM operation, index statistics are considered obsolete if the ratio of newly added index rows to the total current rows exceeds the specified threshold. This will trigger a new index scan.

The minimum value is 0.0, the maximum is 10000000000.0, and the default is 0.1.

Support for this feature was removed in PostgreSQL version 14.

For more information, see the vacuum_cleanup_index_scale_factor setting description in this PostgreSQL article.
Vacuum cost delay All interfaces

Idle time (in ms) for a VACUUM or ANALYZE process that has exceeded the vacuum cost limit.

The minimum value is 0, the maximum is 100, and the default is 0.

For more information, see the vacuum_cost_delay setting description in this PostgreSQL article.
Vacuum cost limit All interfaces

Accumulated cost threshold at which the VACUUM process will enter sleep mode.

The minimum value is 1, the maximum is 10000, and the default is 200.

For more information, see the vacuum_cost_limit setting description in this PostgreSQL article.
Vacuum cost page dirty All interfaces

Estimated cost of a VACUUM operation processing a previously unmodified block.

The minimum value is 0, the maximum is 10000, and the default is 20.

For more information, see the vacuum_cost_page_dirty setting description in this PostgreSQL article.
Vacuum cost page hit All interfaces

Estimated cost of vacuuming a buffer found in shared cache.

The minimum value is 0, the maximum is 10000, and the default is 1.

For more information, see the vacuum_cost_page_hit setting description in this PostgreSQL article.
Vacuum cost page miss All interfaces

Estimated cost of vacuuming a buffer that must be read from disk.

The minimum value is 0, the maximum is 10000, and the default is 10.

For more information, see the vacuum_cost_page_miss setting description in this PostgreSQL article.
Vacuum failsafe age Management console API CLI

Maximum age of a frozen, i.e., completed, transaction measured in the number of transactions that have been started after it. Once this threshold is reached, the system runs the VACUUM process to prevent transaction ID wraparound.

The minimum value is 0, the maximum is 2100000000, and the default is 1600000000.

This feature is supported in PostgreSQL as of version 14.

For more information, see the vacuum_failsafe_age setting description in this PostgreSQL article.
Vacuum multixact failsafe age Management console API CLI

Maximum age of a frozen, i.e., completed, multi-transaction measured in the number of multi-transactions that have been started after it. Once this threshold is reached, the system runs the VACUUM process to prevent multixact ID wraparound.

The minimum value is 0, the maximum is 2100000000, and the default is 1600000000.

This feature is supported in PostgreSQL as of version 14.

For more information, see the vacuum_multixact_failsafe_age setting description in this PostgreSQL article.
Wal keep size Management console API Terraform

Minimum amount of obsolete WAL data (in bytes) to retain in the WAL directory, allowing replicas to fetch it if needed.

The minimum value is 0 (no WAL retention for replication), the maximum is 2251799812636672 (2 TB), and the default is 0.

This feature is supported in PostgreSQL as of version 13.

For more information, see the wal_keep_size setting description in this PostgreSQL article.
Work mem All interfaces

Base memory allocation (in bytes) for internal query processing operations, e.g., sorting and hash tables, before spilling over to temporary disk files.

The minimum value is 64, the maximum is 2147483647 (2 GB), and the default is 4194304 (4 MB).

For more information, see the work_mem setting description in this PostgreSQL article.
Xmlbinary All interfaces

Method for encoding binary data in XML. The possible values are:
- base64 (XML_BINARY_BASE64 for Terraform, CLI, and API): Base64 encoding.
- hex (XML_BINARY_HEX for Terraform, API, and CLI): Hexadecimal encoding.
The default value is base64.

For more information, see the xmlbinary setting description in this PostgreSQL article.
Xmloption All interfaces

Default XML-to-text conversion type. The possible values are:
- document (XML_OPTION_DOCUMENT for Terraform, CLI, and API): XML document.
- content (XML_OPTION_CONTENT for Terraform, CLI, and API): XML fragment.
The default value is content.

For more information, see the xmloption setting description in this PostgreSQL article.

Host class	vCPU	Default value
s2.small	4	`150 × 4 + 400 = 1000`
m2.medium	6	`150 × 6 + 400 = 1300`

User-level settings

These settings affect the behavior of PostgreSQL when handling user queries:

Catchup timeout Management console Terraform API

Maximum allowed replica lag behind the master (in seconds).

When this setting has a non-zero value, the Odyssey connection pooler will block connections to severely lagging replicas. This mechanism prevents reading stale data from such replicas.

Odyssey regularly polls the cluster for lagging replicas and terminates connection attempts to replicas with the lag exceeding the configured threshold, throwing the following error:
```
remote server read/write error: failed to wait replica for catchup
```
The minimum and default value is 0, which permits connections to any replica, no matter how far it lags behind the master.
Conn limit Management console API CLI

In transaction pooling mode, this setting limits the number of concurrent active connections per user. This pooling mode allows a user to open thousands of connections, meanwhile, limiting the number of concurrently active connections to the configured value N.

In session pooling mode, this setting limits the number of connections per host a user can make in a PostgreSQL cluster. When using this pooling mode, make sure the setting value is no less than the total number of connections that can be opened by the user service backends. Each open server connection slightly slows down the PostgreSQL OLTP performance.

Session pooling operates as follows:
- By default, Managed Service for PostgreSQL reserves 50 connections per host for each new PostgreSQL cluster user. The minimum number of connections per user is 1.
- The total number of connections reserved for users must not exceed the Max connections value. Note that Managed Service for PostgreSQL reserves 15 service connections per host.
  
  For example, for "max_connections": 100, you can reserve no more than 85 user connections per cluster host.
- We recommend isolating different PostgreSQL services with separate users, each with their own configured connection limit. If a failing service creates a connection storm, other services will remain unaffected and retain PostgreSQL connectivity.
The value of this setting is determined by the selected host class.
Default transaction isolation Management console API CLI

Refer to the Default transaction isolation cluster-level setting description.
Grants Management console API CLI Terraform

Roles granted to the user.
Lock timeout Management console API CLI

Refer to the Lock timeout cluster-level setting description.
Log min duration statement Management console API CLI

Refer to the Log min duration statement cluster-level setting description.

We recommend defining a slow query threshold for each service and its user and logging only those queries that exceed it. For example, a query running longer than one second might be considered slow for a web service, whereas for a reporting service the slow query threshold might be 10 minutes.

For more information, see this PostgreSQL article.
Log statement Management console API CLI

Refer to the Log statement cluster-level setting description.
Login Management console API CLI

Defines whether the user is permitted to connect to the PostgreSQL cluster.

Default value is true, meaning that the user can connect to the cluster.
Pg audit log Management console

Defines which user queries will be included in the audit log.

This setting only applies if the following conditions are met:
- pgaudit library is connected to the cluster.
- pgaudit extension is enabled for the database.
The possible values are:
- read: System logs SELECT and COPY queries if the data source is a relation or a query.
- write: System logs INSERT, UPDATE, DELETE, TRUNCATE, and COPY queries when the destination is a relation.
- function: System logs function calls and DO blocks.
- role: System logs the statements related to roles and privileges, e.g., GRANT, REVOKE, CREATE/ALTER/DROP ROLE.
- ddl: System logs all DDL statements not included in the ROLE class.
- misc: System logs miscellaneous commands, e.g., DISCARD, FETCH, CHECKPOINT, VACUUM, SET.
- misc_set: System logs miscellaneous SET commands, e.g., SET ROLE.
You can specify multiple values. By default, user audit logs are disabled.

To learn more about configuring audit logs, see Using pgaudit.
Pooling mode Management console Terraform API
Note
- In Terraform and the gRPC API, this setting is called pool_mode.
- In the REST API, this setting is called poolMode.
Connection pooling mode used by the Odyssey connection pooler.

The possible values are:
- SESSION: Session mode.
- TRANSACTION: Transaction mode.
- STATEMENT: Statement mode.
The default value is SESSION.
Prepared statements pooling Management console

Allows using prepared statements with transaction pooling.
Synchronous commit Management console API CLI

Refer to the Synchronous commit cluster-level setting description.
Temp file limit Management console API CLI

Refer to the Temp file limit cluster-level setting description.
Wal sender timeout Management console

Time (in ms) after which inactive replication connections are terminated.

This feature is supported in PostgreSQL as of version 12.

PostgreSQL settings

Settings dependency on the host class and storage sizeSettings dependency on the host class and storage size

Cluster-level settingsCluster-level settings

User-level settingsUser-level settings

Was the article helpful?

Settings dependency on the host class and storage size

Cluster-level settings

User-level settings