PostgreSQL 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 sizeSettings 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:

• Autovacuum max workers.
• Autovacuum vacuum cost delay.
• Autovacuum vacuum cost limit.
• Conn limit.
• Max connections.
• Shared buffers.

The settings that depend on the storage size are:

• Max wal size
• Min wal size

Cluster-level settingsCluster-level settings

You can use the following settings at the cluster level:

• Archive timeout All interfaces

  Period (in milliseconds) for archiving the PostgreSQL transaction log.

  The minimum value is 10000; the maximum value is 86400000; the default value is 30000.

  For more information, see the PostgreSQL documentation.

• Array nulls All interfaces

  Manages the recognition of the NULL elements when inserting an array. If this setting is enabled, such elements are recognized as an empty NULL field. Otherwise, as a string with the NULL text.

  This setting is on by default.

  For more information, see the PostgreSQL documentation.

• Auto explain log analyze Management console API Terraform

  Determines whether to automatically output query plan statistics to the PostgreSQL log without having to run EXPLAIN manually. This allows you to track unoptimized queries. This setting uses the auto_explain module. To enable it, connect the auto_explain library.

  It is disabled by default (query plan statistics are not logged).

  For more information, see the PostgreSQL documentation.

• Auto explain log buffers Management console API Terraform

  Determines whether to output buffer usage statistics to the PostgreSQL log using the auto_explain module. It works similar to the BUFFERS parameter in the EXPLAIN statement. Applies only when Auto explain log analyze is enabled.

  This setting is disabled by default (buffer usage statistics are not logged).

  For more information, see the PostgreSQL documentation.

• Auto explain log min duration Management console API Terraform

  Minimum query execution time (in milliseconds) at which logging is enabled in the auto_explain module.

  The minimum value is -1 (logging disabled); the maximum value is 2147483647; the default value is -1. If set to 0, plans are logged for all queries without regard to their execution time.

  For more information, see the PostgreSQL documentation.

• 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. Applies only when Auto explain log analyze is enabled.

  This setting is disabled by default (plans are logged for top-level queries only).

  For more information, see the PostgreSQL documentation.

• Auto explain log timing Management console API Terraform

  Determines whether the auto_explain module will log execution time information for individual query plan steps. It works similar to the TIMING parameter in the EXPLAIN statement. Applies only when Auto explain log analyze is enabled.

  This setting is disabled by default (timing information for individual steps is not logged).

  For more information, see the PostgreSQL documentation.

• Auto explain log triggers Management console API Terraform

  Determines whether to output trigger execution statistics in the auto_explain module. Applies only when Auto explain log analyze is enabled.

  This setting is disabled by default (trigger execution statistics are not output).

  For more information, see the PostgreSQL documentation.

• Auto explain log verbose Management console API Terraform

  Determines whether to provide log details in the auto_explain module. It works similar to the VERBOSE parameter in the EXPLAIN statement. Applies only when Auto explain log analyze is enabled.

  This setting is disabled by default (no logging details).

  For more information, see the PostgreSQL documentation.

• Auto explain sample rate Management console API Terraform

  Rate of queries logged per session using the auto_explain module. Applies only when Auto explain log analyze is enabled.

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

  For more information, see the PostgreSQL documentation.

• Autovacuum analyze scale factor All interfaces

  Rate of changed or deleted table records at which autovacuum will run the ANALYZE statement for statistics collection.

  The minimum value is 0.0; the maximum value is 1.0; the default value is 0.0001.

  For more information, see the PostgreSQL documentation.

• Autovacuum max workers Management console API Terraform CLI

  Maximum number of autovacuum processes running in parallel. Autovacuuming runs at intervals for each DB, looks up the table records marked for deletion, and deletes them.

  The minimum value is 1; the maximum value is 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 PostgreSQL documentation.

• Autovacuum naptime Management console API Terraform

  Sets the minimum interval (in milliseconds) between autovacuums. If your data changes frequently, you can increase this interval to avoid overloading your database.

  The minimum value is 1000; the maximum value is 86400000; the default value is 15000.

  For more information, see the PostgreSQL documentation.

• Autovacuum vacuum cost delay All interfaces

  Time (in milliseconds) the autovacuum process will remain idle if the cost limit is exceeded.

  The minimum value is -1 (disables the setting); the maximum value 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 value is set to 5.

  • In other cases, the default value is calculated using the following formula:

    55 - 5 × <number_of_vCPUs_per_host>
    

  For more information, see the PostgreSQL documentation.

• Autovacuum vacuum cost limit All interfaces

  Cost limit that, when exceeded, will freeze the autovacuum process for the time specified by the Autovacuum vacuum cost delay parameter.

  The minimum value is -1 (disables the setting); the maximum value is 10000. The default value depends on the selected host class and is determined by the formula:

  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 PostgreSQL documentation.

• Autovacuum vacuum insert scale factor All interfaces

  Table size share added to the Autovacuum vacuum insert threshold setting value at which autovacuum will be activated.

  The minimum value is 0.0; the maximum value is 1.0; the default value is 0.2.

  For more information, see the PostgreSQL documentation.

• Autovacuum vacuum insert threshold All interfaces

  Sets the number of rows that, when inserted into a table, triggers the autovacuum process.

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

  For more information, see the PostgreSQL documentation.

• Autovacuum vacuum scale factor All interfaces

  Rate of changed or deleted table records at which autovacuum will start vacuuming using the VACUUM statement.

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

  For more information, see the PostgreSQL documentation.

• 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 value is 2147483647; the default value is -1.

  For more information, see the PostgreSQL documentation.

• Backend flush after All interfaces

  Maximum amount of data (in kilobytes) a utility process can write to the OS kernel page cache. If it is exceeded, the DBMS commands the OS to flush data to the disk. The higher the parameter, the less likely the slowdown when flushing data to the disk using fsync (e.g., after completing a checkpoint).

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

  For more information, see the PostgreSQL documentation.

• Backslash quote All interfaces

  Controls the way quotation mark is represented in an SQL string.

  Acceptable values:

  • backslash_quote (BACKSLASH_QUOTE for Terraform, API, and CLI): Quotation mark can be represented as \' (equivalent to 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 as the SQL's standard ''.
  • safe_encoding (BACKSLASH_QUOTE_SAFE_ENCODING for Terraform, API, and CLI): Quotation mark can be represented as \' only for client encodings not using \ in multi-byte characters.

  The default value is safe_encoding.

  For more information, see the PostgreSQL documentation.

• Bgwriter delay All interfaces

  Time (in milliseconds) between the background writer runs. This process writes new or changed items from the PostgreSQL buffer to the disk. The delay helps avoid rewriting the same page many times for every page modification and reduce IO load.

  The minimum value is 10; the maximum value is 10000; the default value is 200.

  For more information, see the PostgreSQL documentation.

• Bgwriter flush after Management console API Terraform

  Limits the amount of data processed by the background writer (in kilobytes). When the limit is exceeded, the DBMS instructs the OS to flush this data to disk. This parameter limits the amount of dirty data in the kernel page cache, thus reducing the likelihood of slowdowns when an fsync statement is issued at the end of a checkpoint or when the OS flushes data to the disk in the background.

  The minimum value is 0; the maximum value is 2048; the default value is 512.

  For more information, see the PostgreSQL documentation.

• Bgwriter lru maxpages All interfaces

  Maximum number of PostgreSQL buffers that can be written by the background writer per activity round. The value of zero disables background writing.

  The minimum value is 0; the maximum value is 1073741823; the default value is 100.

  For more information, see the PostgreSQL documentation.

• Bgwriter lru multiplier All interfaces

  Multiplies the average need in buffers over the previous rounds to calculate the background writer's buffer need for the next round.

  The minimum value is 0; the maximum value is 10; the default value is 2.

  For more information, see the PostgreSQL documentation.

• Bytea output All interfaces

  Sets the output format for binary string values (the bytea type):

  • hex (BYTEA_OUTPUT_HEX for Terraform, API, and CLI) to encode binary data as two hexadecimal digits per byte, e.g., 'SELECT '\xDEADBEEF';'.
  • escape (BYTEA_OUTPUT_ESCAPE for Terraform, API, and CLI) to use the standard PostgreSQL format (ASCII characters only).

  The default value is hex.

  For more information, see the PostgreSQL documentation.

• Checkpoint completion target All interfaces

  Portion of the interval between checkpoints representing the maximum duration of completing a checkpoint. For example, if the value is 0.5, the checkpoint will be completed within about half the interval until the next checkpoint.

  The minimum value is 0.0; the maximum value is 1.0; the default value is 0.5.

  For more information, see the PostgreSQL documentation.

• Checkpoint flush after Management console API Terraform

  Size of page cache (in kilobytes) that triggers data flush at a checkpoint. The pages beyond the value are flushed to the disk and deleted from the OS page cache.

  The minimum value is 0; the maximum value is 2048; the default value is 256.

  For more information, see the PostgreSQL documentation.

• Checkpoint timeout All interfaces

  Interval between checkpoints (in milliseconds).

  The minimum value is 30000; the maximum value is 86400000; the default value is 300000.

  For more information, see the PostgreSQL documentation.

• Client connection check interval Management console API CLI

  Client connection check interval when executing queries (in milliseconds). The check polls the server socket for a connection and aborts long-running queries if it detects that the connection has been terminated. PostgreSQL supports this feature starting from version 14.

  The minimum value is 0 (disables checks); the maximum value is 2147483647. By default, the minimum value is used.

  For more information, see the PostgreSQL documentation.

• Client min messages All interfaces

  Level of logging 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 PostgreSQL documentation.

• Constraint exclusion All interfaces

  Allows the query planner to use table constraints to optimize queries.

  Acceptable values:

  • 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 PostgreSQL documentation.

• Cursor tuple fraction All interfaces

  Sets the planner's estimate of the fraction of rows to be retrieved via a cursor.

  The minimum value is 0.0; the maximum value is 1.0; the default value is 0.1.

  For more information, see the PostgreSQL documentation.

• Deadlock timeout Management console API Terraform CLI

  Waiting time (in milliseconds) before checking for a deadlock condition.

  The minimum value is 1; the maximum value is 2147483647; the default value is 1000.

  For more information, see the PostgreSQL documentation.

• Default statistics target All interfaces

  Maximum number of statistics records for each DB table column. The query planner uses statistics to estimate the number of rows returned by queries to select the most suitable query plan.

  To improve the quality of the planner's estimates, increase the target.

  The minimum value is 1; the maximum value is 10000; the default value is 1000.

  For more information, see the PostgreSQL documentation.

• Default transaction isolation All interfaces

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

  Acceptable values:

  • read committed (TRANSACTION_ISOLATION_READ_COMMITTED for Terraform, API, and CLI): The query only sees the rows that were committed before it started.
  • read uncommitted (TRANSACTION_ISOLATION_READ_UNCOMMITTED for Terraform, API, and CLI): This isolation level's behavior in PostgreSQL is identical to read committed.
  • repeatable read (TRANSACTION_ISOLATION_REPEATABLE_READ for Terraform, API, and CLI): All queries in the current transaction only see the rows that were committed before the first query to select or update data in this transaction.
  • serializable (TRANSACTION_ISOLATION_SERIALIZABLE for Terraform, API, and CLI): The strictest isolation level out of all those mentioned above. This isolation level's behavior in PostgreSQL is identical to repeatable read. However, if the overlap of read and write operations of parallel serializable transactions is incompatible with their serial execution, one of the transactions is rolled back with the serialization failure error.

  The default value is read committed.

  To learn more about isolation levels, see the PostgreSQL documentation.

  The setting applies at the Managed Service for PostgreSQL cluster level, but it can be overridden at the user level.

• Default transaction read only All interfaces

  Sets the read only mode in each new transaction for any tables except temporary ones.

  This setting is disabled by default (both reads and writes are allowed).

  For more information, see the PostgreSQL documentation.

• Default with oids All interfaces

  Adds the OID column containing a unique row ID. The setting has effect if the table is created:

  • Either without specifying WITH OIDS and WITHOUT OIDS.
  • Or using the SELECT INTO statement.

  The practice of using OID in user tables is considered obsolete, so enable this setting for backward compatibility reasons only.

  This setting is disabled by default (no OID column is added).

  For more information, see the PostgreSQL documentation.

• Effective cache size All interfaces

  Sets the assumption about the effective size of the disk cache that is available to a single query. With a higher value, index scans are more likely to be used by the query planner. With a lower value, sequential scans are more likely.

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

  For more information, see the PostgreSQL documentation.

• Effective io concurrency All interfaces

  Number of concurrent disk I/O operations for an individual DB session. The higher this number, the more operations PostgreSQL will attempt to execute in parallel within a session.

  The minimum value is 0; the maximum value is 1000; the default value is 1.

  For more information, see the PostgreSQL documentation.

• Enable async append Management console API CLI

  Allows the query planner to consider asynchronous execution of the query plan on different hosts when adding data from external sources. PostgreSQL supports this feature starting from version 14.

  This setting is on by default.

  For more information, see the PostgreSQL documentation.

• Enable bitmapscan All interfaces

  Allows the query planner to use the bitmap-scan plan types, even if this is not specified in the query explicitly. This access method is similar to regular access by index, but occurs in two steps:

  1. The index is scanned (Bitmap Index Scan) and a bitmap is built where the rows to be read by the query are flagged.

  2. The table is scanned (Bitmap Heap Scan). In which case:

     • The pages are read sequentially (this increases the likelihood of using the OS cache).
     • Each page is viewed only once.

  The setting is effective when repeating queries to tables.

  This setting is on by default.

  For more information, see the PostgreSQL documentation.

• Enable gathermerge Management console API CLI

  Allows the query planner to use a Gather Merge node for merging query execution results while preserving their order in the parallel query plan. PostgreSQL supports this feature starting from version 14.

  This setting is on by default.

  For more information, see the PostgreSQL documentation.

• Enable hashagg All interfaces

  Allows the planner to build a hash table from the result set, even if this is not specified in the query explicitly. This setting is effective for frequent unsorted queries to large database tables.

  This setting is on by default.

  For more information, see the PostgreSQL documentation.

• Enable hashjoin All interfaces

  Allows the scheduler to JOIN tables based on row hash values, even if this is not specified in the query explicitly. This setting is effective when handling large amounts of data or when the tables are not sorted by the join columns.

  This setting is on by default.

  For more information, see the PostgreSQL documentation.

• Enable incremental sort All interfaces

  Allows the query planner to use incremental sorting. This type of sorting can reduce query execution time and RAM requirements if rows need to be sorted by multiple columns, and one or more of them have already been sorted. This feature has been supported by PostgreSQL starting from version 13.

  This setting is on by default.

  For more information, see the PostgreSQL documentation.

• Enable indexonlyscan All interfaces

  Allows the query planner to use a table index without accessing the data, even if this is not specified in the query explicitly.

  This setting is on by default.

  For more information, see the PostgreSQL documentation.

• Enable indexscan All interfaces

  Allows the query planner to use a table index, even if this is not explicitly specified in the query.

  This setting is on by default.

  For more information, see the PostgreSQL documentation.

• Enable material All interfaces

  Allows the query planner to use materialization.

  This setting is on by default.

  For more information, see the PostgreSQL documentation.

• Enable mergejoin All interfaces

  Allows the query planner to merge JOIN operations when joining tables.

  This setting is on by default.

  For more information, see the PostgreSQL documentation.

• Enable nestloop All interfaces

  Allows the query planner to use JOIN with nested cycles. When the setting is disabled, the query planner will use other methods.

  This setting is on by default.

  For more information, see the PostgreSQL documentation.

• Enable parallel append All interfaces

  Allows the query planner to use parallel-aware append.

  This setting is on by default.

  For more information, see the PostgreSQL documentation.

• Enable parallel hash All interfaces

  Allows the query planner to use hash-join with parallel hash. The setting only applies when Enable hashjoin is enabled.

  This setting is on by default.

  For more information, see the PostgreSQL documentation.

• Enable partition pruning All interfaces

  Allows the query planner to remove unneeded partitions from the query plans for partitioned tables.

  This setting is on by default.

  For more information, see the PostgreSQL documentation.

• Enable partitionwise aggregate All interfaces

  Allows the query planner to perform grouping or aggregation on partitioned tables separately for each partition.

  This setting is disabled by default (query planner ignores partitions).

  For more information, see the PostgreSQL documentation.

• Enable partitionwise join All interfaces

  Allows the query planner to plan queries partitionwise. The setting lets you join partitioned tables by joining their matching partitions.

  This setting is disabled by default (query planner ignores partitions).

  For more information, see the PostgreSQL documentation.

• Enable seqscan All interfaces

  Allows the query planner to use sequential table scan plans.

  This setting is on by default.

  For more information, see the PostgreSQL documentation.

• Enable sort All interfaces

  Allows the query planner to use explicit sort steps.

  This setting is on by default.

  For more information, see the PostgreSQL documentation.

• Enable tidscan All interfaces

  Allows the query planner to use TID (tuple identifier) scan plans to speed up queries.

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

  For more information, see the PostgreSQL documentation.

• Escape string warning All interfaces

  Enables a warning when a backslash (\) appears in a regular string constant (with the '...' syntax). Only applies if Standard conforming strings is off.

  This setting is on by default.

  For more information, see the PostgreSQL documentation.

• Exit on error All interfaces

  Enables session interruption in case of any error in the query.

  This setting is disabled by default.

  For more information, see the PostgreSQL documentation.

• Force parallel mode All interfaces

  Allows executing queries in parallel mode for testing purposes:

  • off (FORCE_PARALLEL_MODE_OFF for Terraform, API, and CLI): Use parallel mode only when a performance increase is expected.
  • on (FORCE_PARALLEL_MODE_ON for Terraform, API, and CLI): Force parallel mode for all queries where it is safe.
  • regress (FORCE_PARALLEL_MODE_REGRESS for Terraform, API, and CLI): Equivalent to on, but the standard output is the same as when using off.

  The default value is off.

  For more information, see the PostgreSQL documentation.

• From collapse limit All interfaces

  As long as the number of elements in the FROM list does not exceed the setting value, the query planner will combine nested queries with the external query. You can decrease planning time using smaller values, but your query plan might get less effective.

  The minimum value is 1; the maximum value is 2147483647; the default value is 8.

  For more information, see the PostgreSQL documentation.

• Geqo Management console API

  Enables genetic query optimization (GEQO).

  Default value: false (disables genetic optimization).

  For more information, see the PostgreSQL documentation.

• Geqo effort Management console API

  Sets the ratio between the query planning time and query plan quality in the GEQO algorithm.

  The higher the value, the longer it takes to plan the query. But it also increases the probability of selecting an efficient query plan. This setting does not affect the algorithm operation directly but is used to calculate default values for other settings.

  The minimum value is 1; the maximum value is 10; the default value is 5.

  For more information, see the PostgreSQL documentation.

• Geqo generations Management console API

  Sets the number of iterations for the GEQO algorithm.

  Usable values are in the range from 100 to 1000.

  If set to 0 (default), the number of algorithm iterations is selected based on the Geqo pool size setting.

  For more information, see the PostgreSQL documentation.

• Geqo pool size Management console API

  Sets the number of species in the GEQO algorithm's genetic population.

  Cannot be equal to 1. Usable values are in the range from 100 to 1000.

  If set to 0 (default), the number of species is selected depending on the Geqo effort setting and the number of tables in the query.

  For more information, see the PostgreSQL documentation.

• Geqo seed Management console API

  Sets an initial value for the random number generator used by the GEQO algorithm to select paths in the join sequence search space.

  When you change the setting, you alter the set of join paths examined which might either improve or degrade the resulting path.

  The minimum value is 0; the maximum value is 1; the default value is 0.

  For more information, see the PostgreSQL documentation.

• Geqo selection bias Management console API

  Sets the selective pressure within the GEQO population.

  The minimum value is 1.50; the maximum value is 2.00; the default value is 2.00.

  For more information, see the PostgreSQL documentation.

• 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, we recommend the standard planner that uses full scan. However, for the queries across many tables, a full scan might take more time than a suboptimal plan.

  The minimum value is 2; the maximum value is 2147483647; the default value is 12.

  For more information, see the PostgreSQL documentation.

• Gin pending list limit All interfaces

  Sets the maximum size of a GIN index's pending list in bytes. The list is used when fastupdate mode is enabled. If the pending list exceeds the specified limit, its entries are moved to the index's main GIN data structure in bulk and the list is cleared.

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

  For more information, see the PostgreSQL documentation.

• Hash mem multiplier Management console API Terraform

  Defines the maximum amount of memory that operations with hash tables can use The amount is calculated by multiplying the setting value by Work mem.

  The minimum value is 0.0; the maximum value is 1000.0; the default value is 1.0.

  For more information, see the PostgreSQL documentation.

• Idle in transaction session timeout All interfaces

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

  The minimum value is 0; the maximum value is 2147483647; the default value is 0.

  For more information, see the PostgreSQL documentation.

• Jit Management console API Terraform

  Enables Just-in-Time (JIT) compilation of queries for PostgreSQL. If this setting is enabled, SQL queries are compiled into machine code directly at runtime. This will speed up execution of complex CPU-intensive queries.

  This setting is disabled by default.

  For more information, see the PostgreSQL documentation.

• Join collapse limit All interfaces

  As long as the number of elements in the FROM list does not exceed this value, the query planner will move explicit JOIN instructions (except FULL JOIN) to the list. You can decrease planning time using smaller values, but your query plan might get less effective.

  The minimum value is 1; the maximum value is 2147483647; the default value is 8.

  For more information, see the PostgreSQL documentation.

• Lo compat privileges Management console API CLI Terraform

  Disables access permission check for large objects. Prior to version 9.0, large objects were not covered by access permissions, so any user could access them for reading and writing. Enable this setting if you need compatibility with PostgreSQL versions below 9.0.

  The setting is disabled by default (privilege checks are enabled).

  For more information, see the PostgreSQL documentation.

• Lock timeout All interfaces

  Specifies how long to wait for the lock to be released (in milliseconds). 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; you can await a lock indefinitely); the maximum value is 2147483647; the default value is 1000.

  For more information, see the PostgreSQL documentation.

  The setting applies at the Managed Service for PostgreSQL cluster level, but it can be overridden at the user level.

• Log checkpoints All interfaces

  Enables logging of checkpoints and server restart points.

  The setting is disabled by default (no logging).

  For more information, see the PostgreSQL documentation.

• Log connections Management console API CLI Terraform

  Enables logging of all attempts to connect to the PostgreSQL server, including those where clients were successfully authenticated.

  The setting is disabled by default (no logging).

  For more information, see the PostgreSQL documentation.

• 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 PostgreSQL documentation.

• Log duration Management console API CLI Terraform

  Enables logging of the duration of each completed statement.

  The setting is disabled by default (no logging).

  For more information, see the PostgreSQL documentation.

• Log error verbosity Management console API CLI Terraform

  This setting controls the amount of detail written to the PostgreSQL log for each message. Log detail levels in ascending order of verbosity:

  • terse (LOG_ERROR_VERBOSITY_TERSE for Terraform, CLI, and API). The 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). The error message includes the SQLSTATE error code, the source code file name, function name, and the number of the row that generated the error.

  For more information, see the PostgreSQL documentation.

• Log lock waits Management console API CLI Terraform

  Controls logging of long lock waits. When enabled, a log entry is created when a PostgreSQL session waits longer than Deadlock timeout to get a lock.

  This setting is disabled by default.

  For more information, see the PostgreSQL documentation.

• Log min duration sample Management console API Terraform

  This setting is similar to Log min duration statement, but only applies to the statements listed in Log statement sample rate.

  If set to 0, the duration is logged for all the specified statements.

  The minimum value is -1 (disables statement runtime logging); the maximum value is 2147483647; the default value is -1.

  For more information, see the PostgreSQL documentation.

• Log min duration statement Management console API CLI Terraform

  This setting specifies the minimum statement runtime required to log the statement (in milliseconds).

  If set to 0, the runtime of all statements is logged.

  The minimum value is -1 (disables runtime logging); the maximum value is 2147483647; the default value is -1.

  For more information, see the PostgreSQL documentation.

  The setting applies at the Managed Service for PostgreSQL cluster level, but it can be overridden at the user level.

• Log min error statement Management console API CLI Terraform

  Defines the logging level for errors of SQL statement execution. A message about statement execution is logged if it at least has the specified severity level. Possible values: DEBUG5, DEBUG4, DEBUG3, DEBUG2, DEBUG1, INFO, NOTICE, WARNING, ERROR, LOG, FATAL, and PANIC.

  The default value is ERROR. This means that the PostgreSQL log will include statements that ended with a message with the ERROR, LOG, FATAL, or PANIC severity level.

  To disable logging the majority of statements with errors, select the PANIC value.

  For more information, see the PostgreSQL documentation.

• Log min messages Management console API CLI Terraform

  This setting defines the logging level in PostgreSQL. All messages of the selected severity level (or higher) are logged. Possible values (in ascending order of severity): DEBUG5, DEBUG4, DEBUG3, DEBUG2, DEBUG1, INFO, NOTICE, WARNING, ERROR, LOG, FATAL, and PANIC.

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

  To disable logging of most messages, select PANIC.

  For more information, see the PostgreSQL documentation.

• Log parameter max length Management console API CLI Terraform

  Reduces to a specified number of bytes the value of each referenced SQL parameter that is output together with SQL operators when logging all messages other than error messages.

  If set to 0, logging is disabled.

  The minimum value is -1 (parameter values are logged fully); the maximum value is 1073741823. By default, the minimum value is used.

  For more information, see the PostgreSQL documentation.

• Log parameter max length on error All interfaces

  Reduces to a specified number of bytes the value of each referenced SQL parameter that is output together with SQL operators when logging error messages.

  If set to 0, logging is disabled.

  The minimum value is -1 (parameter values are logged fully); the maximum value is 1073741823. By default, the minimum value is used.

  For more information, see the PostgreSQL documentation.

• Log recovery conflict waits Management console API CLI

  Controls logging of long recovery conflict waits when reading WAL to continue replication. When enabled, a log entry is created when a PostgreSQL session waits longer than Deadlock timeout to resolve a recovery conflict. PostgreSQL supports this feature starting from version 14.

  This setting is disabled by default.

  For more information, see the PostgreSQL documentation.

• Log statement Management console API CLI Terraform

  Filter for SQL statements that will be written to the PostgreSQL log:

  • none (LOG_STATEMENT_NONE for Terraform, CLI, and API): The filter is disabled, no SQL statements are logged.
  • ddl (LOG_STATEMENT_DDL for Terraform, CLI, and API): Logs the SQL statements allowing to change data structure definitions (CREATE, ALTER, DROP etc.).
  • mod (LOG_STATEMENT_MOD for Terraform, CLI, and API): Logs the ddl-filtered SQL statements and the statements allowing to modify data (INSERT, UPDATE, etc.).
  • all (LOG_STATEMENT_ALL for Terraform, CLI, and API): Logs all SQL statements.

  The default value is none.

  For more information, see the PostgreSQL documentation.

  The setting applies at the Managed Service for PostgreSQL cluster level, but it can be overridden at the user level.

• Log statement sample rate Management console API Terraform

  Rate of SQL statements that will be logged in addition to statements logged for other reasons.

  The minimum value is 0.0; the maximum value is 1.0; the default value is 0.0.

  For more information, see the PostgreSQL documentation.

• Log temp files Management console API CLI Terraform

  Sets the minimum size of a temporary file to be logged in PostgreSQL when deleted.

  The minimum value is -1 (information about deleted files is not logged); the maximum value is 2147483647 (2 GB). If set to 0, the sizes and names of all temporary files are logged. The default value is -1.

  For more information, see the PostgreSQL documentation.

• Log transaction sample rate Management console API Terraform

  Rate of transactions whose statements will be logged in addition to statements logged for other reasons.

  The minimum value is 0.0; the maximum value is 1.0; the default value is 0.0.

  For more information, see the PostgreSQL documentation.

• Logical decoding work mem All interfaces

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

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

  For more information, see the PostgreSQL documentation.

• Maintenance io concurrency All interfaces

  Maximum number of concurrent disk I/O operations during PostgreSQL maintenance using the VACUUM, CREATE INDEX, and ALTER TABLE ADD FOREIGN KEY statements for an individual DB session. The higher this number, the more maintenance statements PostgreSQL will attempt to execute in parallel within a session.

  The minimum value is 0; the maximum value is 1000; the default value is 10.

  For more information, see the PostgreSQL documentation.

• Maintenance work mem All interfaces

  Maximum memory (in bytes) 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 value is 137438953472 (128 GB); the default value is 67108864 (64 MB).

  For more information, see the PostgreSQL documentation.

• Max connections Management console API CLI Terraform

  Maximum number of simultaneous connections to a PostgreSQL host.

  The minimum value is 1. The maximum and default values depend on the selected host class and the formula:

  200 × <number_of_vCPUs_per_host>
  

  Hosts with guaranteed vCPU share under 100% (burstable) use the fixed maximum value of 200.

  By default, the maximum value is used.

  For more information, see the PostgreSQL documentation.

• Max locks per transaction Management console API CLI Terraform

  Maximum number of objects that can be locked by a single transaction. Individual transactions can lock more objects if the locks of all transactions fit in the lock table.

  The minimum value is 10; the maximum value is 2147483647; the default value is 64.

  For more information, see the PostgreSQL documentation.

• Max logical replication workers Management console

  Maximum number of PostgreSQL logical replication processes.

  The minimum value is 4; the maximum value is 100. By default, the minimum value is used.

  For more information, see the PostgreSQL documentation.

• Max parallel maintenance workers All interfaces

  Maximum number of parallel PostgreSQL processes initiated by a single utility statement (e.g., CREATE INDEX).

  The minimum value is 0; the maximum value is 1024; the default value is 2.

  For more information, see the PostgreSQL documentation.

• Max parallel workers All interfaces

  Maximum number of parallel PostgreSQL processes.

  The minimum value is 0; the maximum value is 1024; the default value is 8.

  For more information, see the PostgreSQL documentation.

• Max parallel workers per gather All interfaces

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

  The minimum value is 0; the maximum value is 1024; the default value is 2.

  For more information, see the PostgreSQL documentation.

• Max pred locks per transaction Management console API CLI Terraform

  Maximum number of objects that can be locked by predicate locks per transaction. Individual transactions can lock more objects than specified in the setting if the locks of all transactions fit in the lock table.

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

  For more information, see the PostgreSQL documentation.

• Max prepared transactions Management console API CLI Terraform

  Maximum number of transactions in prepared state at the same time.

  The minimum value is 0; the maximum value is 262143; the default value is 0.

  For more information, see the PostgreSQL documentation.

• Max replication slots Management console

  Maximum number of replication slots. Replication slots automatically provide a mechanism for saving WAL (Write-Ahead Log) files until they are received by all replicas.

  The minimum value is 20; the maximum value is 100. By default, the minimum value is used.

  For more information, see the PostgreSQL documentation.

• Max slot wal keep size Management console API Terraform

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

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

  For more information, see the PostgreSQL documentation.

• Max standby streaming delay All interfaces

  Waiting time (in milliseconds) after which the hot standby host replica will start canceling the queries conflicting with pending WAL updates.

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

  For more information, see the PostgreSQL documentation.

• Max wal senders Management console

  The maximum number of concurrent connections from streaming replication source hosts.

  The minimum value is 20; the maximum value is 100. By default, the minimum value is used.

  For more information, see the PostgreSQL documentation.

• Max wal size All interfaces

  Maximum WAL file size (in bytes) that, when reached, will initiate completing automatic checkpoints.

  The minimum value is 2. The maximum value depends on storage size and equals 10% of that value, but not more than 8589934592 (8 GB). By default, the maximum value is used.

  For more information, see the PostgreSQL documentation.

• Max worker processes Management console API CLI Terraform

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

  The minimum value is 0; the maximum value is 1024; the default value is 8.

  For more information, see the PostgreSQL documentation.

• Min wal size All interfaces

  Amount of disk space (in bytes) taken up by the WAL the exceeding of which will cause deletion of old WAL files at checkpoints.

  The minimum value is 2. The maximum value depends on storage size and equals 5% of that value, but not more than 1073741824 (1 GB). By default, the maximum value is used.

  For more information, see the PostgreSQL documentation.

• Old snapshot threshold Management console API CLI Terraform

  Minimum time (in milliseconds) during which a status snapshot can be used for query without the risk of an error.

  The minimum value is -1 (unlimited); the maximum value is 86400000 (24 hours); the default value is -1.

  For more information, see the PostgreSQL documentation.

• Online analyze enable Management console API

  Enables the online_analyze module that updates statistics after the INSERT, UPDATE, DELETE, or SELECT INTO operations in the target tables.

• Operator precedence warning All interfaces

  Allows the query parser to emit a warning for any constructs that changed their behavior since version 9.4 as a result of changes in operator precedence. This feature is not supported by PostgreSQL starting from version 14.

  This setting is disabled by default.

  For more information, see the PostgreSQL documentation.

• Parallel leader participation All interfaces

  Allows the leader process to execute the query plan downstream of Gather and Gather Merge nodes without waiting for workflows.

  This setting is on by default.

  For more information, see the PostgreSQL documentation.

• Pg hint plan debug print Management console API Terraform

  Debugging information output and detail setting for the pg_hint_plan module. Only applies when Pg hint plan enable hint is enabled. Possible values (in ascending order of verbosity):

  • 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 use of the pg_hint_plan module to correct automatic query plans with the help of so-called hints, which are simple descriptions contained in special SQL comments.

• Pg hint plan enable hint table Management console API Terraform

  Enables the use of the hint_plan.hints table. It contains hints for the pg_hint_plan module and is used when you cannot edit queries. The table contains the following columns:

  • id: Unique ID of the row with the hint. This column is populated automatically.
  • norm_query_string: Template for selecting the queries the hint will refer to. Replace constants with the ? character. Whitespace characters in the template are significant.
  • application_name: Application that initiated the sessions the hint will operate in.
  • hint: Hints without the comment markup.

• Pg hint plan message level Management console API Terraform

  Level of debug messages for the pg_hint_plan module that will get into the PostgreSQL log. 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

  Activates the pg_qualstats module to collect statistics on predicates in WHERE operators and JOIN blocks. It is used to analyze the most frequently calculated query predicates.

• Pg qualstats max Management console API CLI

  Maximum number of tracked predicates for the pg_qualstats module. Only applies when the Pg qualstats enabled setting is on.

  The minimum value is 100; the maximum value is 2147483647; the default value is 1000.

• Pg qualstats resolve oids Management console API CLI

  Enables table name recovery using OID when collecting query statistics for the pg_qualstats module. The setting makes it easier to analyze data, but requires more disk space for storing statistics. Only applies when the Pg qualstats enabled setting is on.

  This setting is disabled by default.

• Pg qualstats sample rate Management console API CLI

  Rate of queries (as a number from 0 to 1) the pg_qualstats module collects statistics for. Only applies when the Pg qualstats enabled setting is on. Specific setting values:

  • 0: Disables the setting.
  • 1: All queries are analyzed.
  • -1: Automatic mode, the rate of queries is inverse to the Max connections setting value and depends on selected host class.

  The default value is -1.

• Pg qualstats track constants Management console API CLI

  Enables tracking of already encountered predicates in subsequent queries when collecting statistics using the pg_qualstats module. Only applies when the Pg qualstats enabled setting is on.

  This setting is on by default.

• Plan cache mode Management console API Terraform

  Determines which type of query plan (generic or custom) 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): Force custom plans.
  • force_generic_plan (PLAN_CACHE_MODE_FORCE_GENERIC_PLAN for Terraform and API): Force generic plans.

  The default value is auto.

  For more information, see the PostgreSQL documentation.

• 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 enclosing all identifiers in quotation marks when generating SQL queries from a database, e.g., when running the EXPLAIN operation.

  This setting is disabled by default.

  For more information, see the PostgreSQL documentation.

• Random page cost All interfaces

  Sets the planner's estimate of the cost of reading an arbitrary disk page. If the setting value is less than Seq page cost, the planner will prefer index scans.

  The minimum value is 0; the default value is 1.

  For more information, see the PostgreSQL documentation.

• Row security All interfaces

  Controls processing of queries that apply at least one row security policy. If you disable the setting, such queries will return an error.

  This setting is on by default.

  For more information, see the PostgreSQL documentation.

• Search path All interfaces

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

  The default value is $user, public (the current user schema and the public schema).

  For more information, see the PostgreSQL documentation.

• Seq page cost All interfaces

  Sets the planner's estimate of the cost of a disk page read when doing a series of sequential reads.

  The minimum value is 0; the default value is 1.

  For more information, see the PostgreSQL documentation.

• Session duration timeout Management console API CLI

  Maximum TTL of the longest active session or transaction (in milliseconds). Only applies to sessions with the active and idle in transaction status.

  To make sure the setting does not impact the cluster performance, the longest transaction/session is checked on a regular basis. The checking interval is selected randomly and ranges from 5 to 10 minutes. For example, if set to 1000, the session will terminate within 1 second + 5 to 10 minutes.

  If the value you set is larger than the default one, this may increase the DB size and slow down the OS.

  The minimum value is 0 (the active session/transaction TTL is unlimited); the maximum value is 2147483647; the default value is 43200000 (12 hours). The minimum granularity for the setting is 1000.

  For more information about possible session statuses, see the PostgreSQL documentation.

• Shared buffers Management console API CLI Terraform

  Memory (in bytes) 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. By default, the value is 25% of the total RAM size, with a maximum of 8 GB.

  For more information, see the PostgreSQL documentation.

• Shared preload libraries Management console API Terraform

  Comma-separated list of shared libraries to preload when the PostgreSQL server starts. Libraries are required for using some PostgreSQL extensions.

  For more information, see the PostgreSQL documentation.

• Standard conforming strings Management console API Terraform

  Treating backslashes (\) in regular string constants ('...') as a regular character (as per the SQL standard), not as as a special character.

  This setting is on by default.

  For more information, see the PostgreSQL documentation.

• Statement timeout Management console API CLI Terraform

  Maximum statement runtime (in milliseconds) after which the statement is aborted.

  The minimum value is 0; the maximum value is 2147483647; the default value is 0.

  For more information, see the PostgreSQL documentation.

• Synchronize seqscans All interfaces

  Allows sequential scans of large tables to synchronize with each other, so that concurrent scans read the same block at about the same time. When disabled, ensures pre-8.3 behavior where a sequential scan always started from the beginning of the table.

  This setting is on by default.

  For more information, see the PostgreSQL documentation.

• Synchronous commit All interfaces

  Determines at what stage of WAL data processing the server will commit a transaction.

  Acceptable values:

  Management console/CLI

  Terraform

  API

  • on: Transaction is committed if the WAL is written to the master disk and quorum replica disk.
  • off: Transaction is committed even if its data is not yet in the WAL. Writes are not synchronous, and the transaction data might be lost as a result of a disk subsystem failure.
  • local: Transaction is committed if the WAL is written to the master disk.
  • remote_write: Transaction is committed if the WAL is written to the master disk, the quorum replica received the WAL and forwarded it to the OS to write it to the disk. If the master disk system is lost and the OS on the quorum replica fails, transaction data with this level of synchronization may be lost.
  • remote_apply: Transaction is committed if the WAL is written to the master disk, the quorum replica received the WAL and applied the updates from it.

  The default value is on.

  • 1 or "SYNCHRONOUS_COMMIT_ON": Transaction is committed if the WAL is written to the master disk and quorum replica disk.
  • 2 or "SYNCHRONOUS_COMMIT_OFF": Transaction is committed even if its data is not yet in the WAL. Writes are not synchronous, and the transaction data might be lost as a result of a disk subsystem failure.
  • 3 or "SYNCHRONOUS_COMMIT_LOCAL": Transaction is committed if the WAL is written to the master disk.
  • 4 or "SYNCHRONOUS_COMMIT_REMOTE_WRITE": Transaction is committed if the WAL is written to the master disk, the quorum replica received the WAL and forwarded it to the OS to write it to the disk. If the master disk system is lost and the OS on the quorum replica fails, transaction data with this level of synchronization may be lost.
  • 5 or "SYNCHRONOUS_COMMIT_REMOTE_APPLY": Transaction is committed if the WAL is written to the master disk, the quorum replica received the WAL and applied the changes from it.

  The default value is "SYNCHRONOUS_COMMIT_ON".

  • SYNCHRONOUS_COMMIT_ON: Transaction is committed if the WAL is written to the master disk and quorum replica disk.
  • SYNCHRONOUS_COMMIT_OFF: Transaction is committed even if its data is not yet in the WAL. Writes are not synchronous, and the transaction data might be lost as a result of a disk subsystem failure.
  • SYNCHRONOUS_COMMIT_LOCAL: Transaction is committed if the WAL is written to the master disk.
  • SYNCHRONOUS_COMMIT_REMOTE_WRITE: Transaction is committed if the WAL is written to the master disk, the quorum replica received the WAL and forwarded it to the OS to write it to the disk. If the master disk system is lost and the OS on the quorum replica fails, transaction data with this level of synchronization may be lost.
  • SYNCHRONOUS_COMMIT_REMOTE_APPLY: Transaction is committed if the WAL is written to the master disk, the quorum replica received the WAL and applied the changes from it.

  The default value is SYNCHRONOUS_COMMIT_ON.

  For more information, see the PostgreSQL documentation.

  The setting applies at the Managed Service for PostgreSQL cluster level, but it can be overridden at the user level.

• Temp buffers All interfaces

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

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

  For more information, see the PostgreSQL documentation.

• Temp file limit Management console API CLI Terraform

  Maximum amount of disk space (in bytes) a single process can use for temporary files. A transaction that tries to exceed this limit will be canceled.

  Large queries are executed in the disk space rather than in RAM. Queries that are too large overload the disk and prevent other queries from being executed. The setting prevents queries that degrade performance by limiting the size of temporary files.

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

  For more information, see the PostgreSQL documentation.

  The setting applies at the Managed Service for PostgreSQL cluster level, but it can be overridden at the user level.

• Timezone All interfaces

  Timezone for time output and input.

  The default value is Europe/Moscow.

  For more information, see the PostgreSQL documentation.

• Track activity query size Management console API Terraform

  Memory (in bytes) reserved in each active session for the text of the statement currently being executed.

  The minimum value is 100; the maximum value is 102400; the default value is 1024.

  For more information, see the PostgreSQL documentation.

• Transform null equals All interfaces

  Enables treating the condition expression = NULL as expression IS NULL. The condition is true if the expression has the NULL value, otherwise it is false. According to the SQL standard, expression = NULL always returns NULL (unknown value).

  This setting is disabled by default (the SQL standard applies).

  For more information, see the PostgreSQL documentation.

• Vacuum cleanup index scale factor All interfaces

  Rate of tuples in the index counted in the previous statistics collection. When running the VACUUM operation, index statistics are considered obsolete if the ratio of newly inserted tuples to the total number of tuples in the index exceeds this value. In this case, the index will be re-scanned. This feature is not supported by PostgreSQL starting from version 14.

  The minimum value is 0.0; the maximum value is 10000000000.0; the default value is 0.1.

  For more information, see the PostgreSQL documentation.

• Vacuum cost delay All interfaces

  Time (in milliseconds) the VACUUM and ANALYZE operations will remain idle if the cost limit is exceeded (see Vacuum cost limit).

  The minimum value is 0; the maximum value is 100; the default value is 0.

  For more information, see the PostgreSQL documentation.

• Vacuum cost limit All interfaces

  Accumulated cost threshold at which the VACUUM process will go idle (sleep mode).

  The minimum value is 1; the maximum value is 10000; the default value is 200.

  For more information, see the PostgreSQL documentation.

• Vacuum cost page dirty All interfaces

  Notional cost of a VACUUM operation that modifies a block not modified before.

  The minimum value is 0; the maximum value is 10000; the default value is 20.

  For more information, see the PostgreSQL documentation.

• Vacuum cost page hit All interfaces

  Notional cost of vacuuming a buffer found in shared cache.

  The minimum value is 0; the maximum value is 10000; the default value is 1.

  For more information, see the PostgreSQL documentation.

• Vacuum cost page miss All interfaces

  Notional cost of vacuuming a buffer that has to be read from a disk.

  The minimum value is 0; the maximum value is 10000; the default value is 10.

  For more information, see the PostgreSQL documentation.

• Vacuum failsafe age Management console API CLI

  Maximum age of frozen (already completed) transaction measured as the number of transactions initiated after it. As soon as this value is reached, the VACUUM process runs vacuuming to avoid transaction counter overflow. PostgreSQL supports this feature starting from version 14.

  The minimum value is 0; the maximum value is 2100000000; the default value is 1600000000.

  For more information, see the PostgreSQL documentation.

• Vacuum multixact failsafe age Management console API CLI

  Maximum age of frozen (already completed) multi-transaction measured as the number of multi-transactions initiated after it. As soon as this value is reached, the VACUUM process runs vacuuming to avoid multi-transaction counter overflow. PostgreSQL supports this feature starting from version 14.

  The minimum value is 0; the maximum value is 2100000000; the default value is 1600000000.

  For more information, see the PostgreSQL documentation.

• Wal keep size Management console API Terraform

  Minimum size (in bytes) of obsolete log segments kept in the WAL directory for replicas to download, if required.

  The minimum value is 0 (past segments are not saved for replication); the maximum value is 2251799812636672 (2 TB); the default value is 0.

  For more information, see the PostgreSQL documentation.

• Work mem All interfaces

  Basic memory (in bytes) available for internal query processing operations (e.g., sorting or hash tables) before making use of temporary files on disk.

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

  For more information, see the PostgreSQL documentation.

• Xmlbinary All interfaces

  Method used for encoding binary data in XML. The possible values are:

  • base64 (XML_BINARY_BASE64 for Terraform, API, and CLI): BASE64 encoding.
  • hex (XML_BINARY_HEX for Terraform, API, and CLI): Hexadecimal encoding.

  The default value is base64.

  For more information, see the PostgreSQL documentation.

• Xmloption All interfaces

  Default type of conversion between XML and character data. The possible values are:

  • document (XML_OPTION_DOCUMENT for Terraform, API, and CLI): XML document.
  • content (XML_OPTION_CONTENT for Terraform, API, and CLI): XML document fragment.

  The default value is content.

  For more information, see the PostgreSQL documentation.

User-level settingsUser-level settings

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

• Catchup timeout Management console Terraform API

  The maximum allowed replica's lag behind the master (in seconds).

  If the setting has a non-zero value, the Odyssey connection pooler will not allow connections to replicas lagging far behind the master. This prevents reading stale data from such replicas.

  Odyssey regularly requests information about lagging replicas from the cluster. When attempting to connect to a replica whose lag exceeds the setting value, the connection will be terminated. Odyssey will return the following message:

  remote server read/write error: failed to wait replica for catchup
  

  The minimum and default value is 0 (allows connecting to any replica regardless of its lag behind the master).

• Conn limit Management console API CLI

  Maximum allowed number of connections for the user.

  In transaction pooling, this setting limits the number of simultaneous connections per user transaction. When using this type of pooling, the user can open thousands of connections, but only N connections can run concurrently (where N is the setting value).

  In session pooling, the setting limits the number of user connections to each host in a PostgreSQL cluster. When using this type of pooling, make sure the setting value is not less than the sum of all connections that can be opened by the user service backends. Each open server connection slightly slows down the OLTP performance for PostgreSQL.

  The following principles apply to session pooling:

  • When adding a user, Managed Service for PostgreSQL reserves 50 connections per host in a PostgreSQL cluster by default. The minimum number of connections per user is 1.

  • The total number of connections reserved for users must not exceed the value of the Max connections parameter. Managed Service for PostgreSQL reserves 15 service connections per host.

    For example, if the cluster is set to "max_connections": 100, you can reserve a maximum of 85 connections per cluster host for users.

  • We recommend distributing services that use PostgreSQL across different users and setting the required value for each user. If issues in one service lead to creating a large number of connections, other services will not be affected and will be able to connect to PostgreSQL.

  The setting depends on the selected host class.

• Default transaction isolation Management console API CLI

  See the description of the Default transaction isolation cluster-level setting.

• Grants Management console API CLI Terraform

  Roles granted to the user.

• Lock timeout Management console API CLI

  See the description of the Lock timeout cluster-level setting.

• Log min duration statement Management console API CLI

  See the description of the Log min duration statement cluster-level setting.

  We recommend you to define the meaning of slow query execution for each service and its user and log only the queries that meet the definition. For example, for a web service, a query is slow if it runs for more than one second, while, for the reporting service, it is a query that runs for more than 10 minutes.

  For more information, see the PostgreSQL documentation.

• Log statement Management console API CLI

  See the description of the Log statement cluster-level setting.

• Login Management console API CLI

  Whether or not the user can connect to the PostgreSQL cluster.

  Default value: true (the user can connect to the cluster).

• Pg audit log Management console

  Defines which user queries will be written to the audit log.

  The setting is only valid if:

  • pgaudit library is connected to the cluster.
  • pgaudit extension is enabled for the database.

  The possible values are:

  • read: SELECT and COPY queries are logged if the data source is a relation or query.
  • write: INSERT, UPDATE, DELETE, TRUNCATE, and COPY queries are logged if the data source is a relation.
  • function: Function invocations and DO sections are logged.
  • role: Statements related to role and privilege management, such as GRANT, REVOKE, or CREATE/ALTER/DROP ROLE, are logged.
  • ddl: All DDL statements not belonging to the ROLE class are logged.
  • misc: Auxiliary commands, such as DISCARD, FETCH, CHECKPOINT, VACUUM, and SET, are logged.
  • misc_set: The SET auxiliary commands, such as SET ROLE, are logged.

  You can select multiple values. By default, audit logs are disabled for the user.

  For more information about setting up audit logs, see Using pgaudit.

• Pooling mode Management console Terraform API

  Note

  • Terraform and the gRPC API use the pool_mode setting name.
  • In the REST API, the poolMode setting name is used.

  Connection management mode used by the Odyssey connection pooler.

  The possible values are:

  • SESSION: Session mode.
  • TRANSACTION: Transaction mode.
  • STATEMENT: Query mode.

  The default value is SESSION.

• Prepared statements pooling Management console

  Allows using prepared statements with merged transactions.

• Synchronous commit Management console API CLI

  See the description of the Synchronous commit cluster-level setting.

• Temp file limit Management console API CLI

  See the description of the Temp file limit cluster-level setting.

• Wal sender timeout Management console

  Time, in milliseconds, after which inactive replication connections are terminated. Available for Managed Service for PostgreSQL clusters starting with version 12.