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 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:
Cluster-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 one is86400000
. The default value is30000
.For more information, see the PostgreSQL documentation
. -
Array nulls
All interfaces
Used to manage recognition of
NULL
elements when inserting an array. If this setting is enabled, these elements are recognized as aNULL
field. Otherwise, they are recognized as a string containing the wordNULL
.This setting is enabled 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 theauto_explain
module. To enable it, connect theauto_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. Similar to theBUFFERS
parameter in theEXPLAIN
command. 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
The minimum query execution time (in milliseconds) when logging is enabled in the
auto_explain
module.The minimum value is
-1
(logging disabled), while the maximum one is2147483647
. The default value is-1
. When the value is0
, execution plans for all queries are logged regardless of 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 (execution plans are logged for top-level statements).
For more information, see the PostgreSQL documentation
. -
Auto explain log timing
Management console
API
Terraform
Determines whether the
auto_explain
module will log timing information for individual query execution plan steps. Similar to theTIMING
parameter in theEXPLAIN
command. 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. Similar to theVERBOSE
parameter in theEXPLAIN
command. 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
Percentage 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), while the maximum one is1.0
(all queries are logged). The default value is1.0
.For more information, see the PostgreSQL documentation
. -
Autovacuum analyze scale factor
All interfaces
Percentage of changed or deleted records in the table that, when reached, triggers autovacuum
to start theANALYZE
statistics collection command.The minimum value is
0.0
, while the maximum one is1.0
. The default value is0.0001
.For more information, see the PostgreSQL documentation
. -
Autovacuum max workers
Management console
API
Terraform
CLI
The maximum number of autovacuum worker processes
running in parallel. Autovacuuming runs on for each DB a regular basis, determines the table records marked for deletion, and deletes them.The minimum value is
1
, while the maximum one is32
. The default value depends on the selected host class and is equal to the number of vCPUs on a single host, with a minimum of3
.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
, while the maximum one is86400000
. The default value is15000
.For more information, see the PostgreSQL documentation
. -
Autovacuum vacuum cost delay
All interfaces
The amount of time (in milliseconds) for the autovacuum
process to sleep when the cost limit has been exceeded.The minimum value is
-1
(the setting is not applied), while the maximum one is100
. 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
The 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
(the setting is not applied), while the maximum one is10000
. The default value depends on the selected host class and is determined by the formula:150 × <number_of_vCPUs_per_host> + 400
For 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
Percentage of the table size added to the Autovacuum vacuum insert threshold value that, when reached, triggers the autovacuum process
.The minimum value is
0.0
, while the maximum one is1.0
. The default value is0.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
(the setting is not applied), while the maximum one is2147483647
. The default value is1000
.For more information, see the PostgreSQL documentation
. -
Autovacuum vacuum scale factor
All interfaces
Percentage of changed or deleted records in the table that, when reached, triggers autovacuum
to start theVACUUM
cleaning command.The minimum value is
0.0
(the setting is not applied), while the maximum one is1.0
. The default value is0.00001
.For more information, see the PostgreSQL documentation
. -
Autovacuum work mem
Management console
API
Terraform
CLI
The amount of memory (in bytes) allocated to each autovacuum process
.The minimum value is
-1
(the setting is not applied), while the maximum one is2147483647
. The default value is-1
.For more information, see the PostgreSQL documentation
. -
Backend flush after
All interfaces
The maximum amount of data (in kilobytes) that a utility process can write to the OS kernel's page cache. If it is exceeded, the DBMS commands the OS to flush data to the disk. The higher the parameter, the less likely a slowdown is when flushing data to the disk using
fsync
(for example, on completing a checkpoint).The minimum value is
0
(the setting is not applied), while the maximum one is2048
. The default value is0
.For more information, see the PostgreSQL documentation
. -
Backslash quote
All interfaces
Controls representation of quotation mark in an SQL string.
Acceptable values include:
backslash_quote
(BACKSLASH_QUOTE
for Terraform, API, and CLI): Quotation marks can be represented as\'
(equivalent toon
).on
(BACKSLASH_QUOTE_ON
for Terraform, API, and CLI): Quotation marks can be represented as\'
.off
(BACKSLASH_QUOTE_OFF
for Terraform, API, and CLI): Quotation marks may only be used in the usual SQL way (''
).safe_encoding
(BACKSLASH_QUOTE_SAFE_ENCODING
for Terraform, API, and CLI): Quotation marks may be represented as\'
only for the client encodings that do not use\
in multi-byte characters.
The default value is
safe_encoding
.For more information, see the PostgreSQL documentation
. -
Bgwriter delay
All interfaces
Time to sleep (in milliseconds) after running the background writer. 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
, while the maximum one is10000
. The default value is200
.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's page cache, reducing the likelihood of stalls when anfsync
command is issued when the checkpoint is complete, or when the OS writes the data to the disk in the background.The minimum value is
0
, while the maximum one is2048
. The default value is512
.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
, while the maximum one is1073741823
. The default value is100
.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
, while the maximum one is10
. The default value is2
.For more information, see the PostgreSQL documentation
. -
Bytea output
All interfaces
Sets the output format for binary string values
(thebytea
type):hex
(BYTEA_OUTPUT_HEX
for Terraform, API, and CLI) encodes binary data as two hexadecimal digits per byte, for example, 'SELECT '\xDEADBEEF';
'.escape
(BYTEA_OUTPUT_ESCAPE
for Terraform, API, and CLI) means the standard PostgreSQL format (ASCII characters only).
The default value is
hex
.For more information, see the PostgreSQL documentation
. -
Checkpoint completion target
All interfaces
A fraction of the checkpoint interval that defines the maximum length of issuing a checkpoint. For example, if the value is
0.5
, the system will try to complete the checkpoint in about half the interval before the next checkpoint is issued.The minimum value is
0.0
, while the maximum one is1.0
. The default value is0.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
, while the maximum one is2048
. The default value is256
.For more information, see the PostgreSQL documentation
. -
Checkpoint timeout
All interfaces
The interval between checkpoints (in milliseconds).
The minimum value is
30 000
, while the maximum one is86400000
. The default value is300 000
.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
(no check is run), while the maximum one is2147483647
. By default, the minimum value is used.For more information, see the PostgreSQL documentation
. -
Client min messages
All interfaces
The level of logging messages sent to client applications. Acceptable values (in ascending order of severity):
debug5
,debug4
,debug3
,debug2
,debug1
,info
,notice
,warning
,error
,log
,fatal
, andpanic
(for Terraform, API, CLILOG_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
, andLOG_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 include:
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 andUNION 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
, while the maximum one is1.0
. The default value is0.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
, while the maximum one is2147483647
. The default value is1000
.For more information, see the PostgreSQL documentation
. -
Default statistics target
All interfaces
The 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
, while the maximum one is10000
. The default value is1000
.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 include:
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): The behavior of this isolation level in PostgreSQL is identical toread 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 of all those mentioned. The behavior of this isolation level in PostgreSQL is identical torepeatable 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 theserialization 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 an
OID
column containing a unique row ID. The setting has effect if the table is created:- Without specifying
WITH OIDS
orWITHOUT OIDS
. - Using the
SELECT INTO
command.
Using
OID
is considered deprecated in user tables, so enable this setting for backward compatibility reasons only.Disabled by default (no
OID
column is added).For more information, see the PostgreSQL documentation
. - Without specifying
-
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
, while the maximum one is549755813888
(512 GB). The default value is107374182400
(100 GB).For more information, see the PostgreSQL documentation
. -
Effective io concurrency
All interfaces
The number of concurrent disk I/O operations for an individual DB session. The higher this number, the more operations PostgreSQL will attempt to initiate in parallel within a session.
The minimum value is
0
, while the maximum one is1000
. The default value is1
.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 enabled 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:
-
The index is scanned (Bitmap Index Scan) and a bitmap is built where the rows to be read by the query are flagged.
-
The table is scanned (Bitmap Heap Scan). Note that:
- 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 enabled 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 enabled 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 enabled by default.
For more information, see the PostgreSQL documentation
. -
Enable hashjoin
All interfaces
Allows the scheduler to
JOIN
tables using the 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 enabled 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. PostgreSQL supports this feature starting from version 13.
This setting is enabled 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 enabled 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 enabled by default.
For more information, see the PostgreSQL documentation
. -
Enable material
All interfaces
Allows the query planner to use materialization.
This setting is enabled by default.
For more information, see the PostgreSQL documentation
. -
Enable mergejoin
All interfaces
Allows the query planner to merge
JOIN
operations with tables.This setting is enabled by default.
For more information, see the PostgreSQL documentation
. -
Enable nestloop
All interfaces
Allows the query planner to use
JOIN
with nested loops. When the setting is disabled, the query planner will use other methods.This setting is enabled 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 enabled 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 enabled 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 enabled 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 enabled 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 enabled 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
A warning is issued if a backslash (
\
) appears in a regular string constant ('...'
syntax). Only applies if Standard conforming strings is off.This setting is enabled 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 performance increase is expected.on
(FORCE_PARALLEL_MODE_ON
for Terraform, API, and CLI): Force parallelize all the queries where it is safe.regress
(FORCE_PARALLEL_MODE_REGRESS
for Terraform, API, and CLI): Equivalent toon
, but the standard output is the same as when usingoff
.
The default value is
off
.For more information, see the PostgreSQL documentation
. -
From collapse limit
All interfaces
Maximum number of items in the
FROM
list. As long as this number is not exceeded, the query planner will merge nested queries with upper queries. You can decrease planning time using smaller values, but your query plan might get less effective.The minimum value is
1
, while the maximum one is2147483647
. The default value is8
.For more information, see the PostgreSQL documentation
. -
Geqo
Management console
API
Enables genetic query optimization (GEQO
).Default value:
false
(genetic optimization is disabled).For more information, see the PostgreSQL documentation
. -
Geqo effort
Management console
API
Used in the GEQO
algorithm to set the ratio between the time allotted for query planning and quality of the query execution plan.The higher is the value, the longer it takes to plan the query. However, the probability of selecting an efficient query execution plan increases. This setting does not affect the algorithm operation directly but is used to calculate default values for other settings.
The minimum value is
1
, while the maximum one is10
. The default value is5
.For more information, see the PostgreSQL documentation
. -
Geqo generations
Management console
API
Sets the number of iterations for the GEQO
algorithm.Useful values are in the range between
100
and1000
.If the value of the setting is
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
. Useful values are in the range between100
and1000
.If the setting is
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
, while the maximum one is1
. The default value is0
.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
, while the maximum one is2.00
. The default value is2.00
.For more information, see the PostgreSQL documentation
. -
Geqo threshold
Management console
API
The GEQO
algorithm will only be used to plan such queries where the number of tables in theFROM
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
, while the maximum one is2147483647
. The default value is12
.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 pending list is used whenfastupdate
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
, while the maximum one is2147483647
(2 GB). The default value is4194304
.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
, while the maximum one is1000.0
. The default value is1.0
.For more information, see the PostgreSQL documentation
. -
Idle in transaction session timeout
All interfaces
The maximum idle time of an open transaction (in milliseconds). When exceeded, the session running this transaction terminates.
The minimum value is
0
, while the maximum one is2147483647
. The default value is0
.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
Maximum number of items in the
FROM
list. As long as this value is not exceeded, the planner will move explicitJOIN
constructs (exceptFULL JOIN
s) to the list. You can decrease planning time using smaller values, but your query plan might get less effective.The minimum value is
1
, while the maximum one is2147483647
. The default value is8
.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
(timeout is off, lock waiting time can be indefinite), the maximum one is2147483647
. The default value is1000
.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
Sets the level of detail for the PostgreSQL log entry per message. Log detail levels in ascending order of verbosity:
terse
(LOG_ERROR_VERBOSITY_TERSE
for Terraform, CLI, and API). TheDETAIL
,HINT
,QUERY
, andCONTEXT
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 theSQLSTATE
error code, the source code file name, function name, and line number 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
The setting is similar to Log min duration statement, but only applies to the statements listed in Log statement sample rate.
When the value is
0
, the duration is logged for all the specified statements.The minimum value is
-1
(disables logging of the statement duration), while the maximum one is2147483647
. The default value is-1
.For more information, see the PostgreSQL documentation
. -
Log min duration statement
Management console
API
CLI
Terraform
Sets the threshold for statement duration logging (in milliseconds).
When the value is
0
, the duration is logged for all the specified statements.The minimum value is
-1
(disables duration logging), while the maximum one is2147483647
. 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. Acceptable values:
DEBUG5
,DEBUG4
,DEBUG3
,DEBUG2
,DEBUG1
,INFO
,NOTICE
,WARNING
,ERROR
,LOG
,FATAL
, andPANIC
.The default value is
ERROR
. This means that the PostgreSQL log will include all the statements that ended with a message having theERROR
,LOG
,FATAL
, andPANIC
severity level.To disable logging of most failed statements, select the
PANIC
value.For more information, see the PostgreSQL documentation
. -
Log min messages
Management console
API
CLI
Terraform
Defines the logging level in PostgreSQL. All messages of the selected severity level (or higher) are logged. Acceptable values (in ascending severity):
DEBUG5
,DEBUG4
,DEBUG3
,DEBUG2
,DEBUG1
,INFO
,NOTICE
,WARNING
,ERROR
,LOG
,FATAL
, andPANIC
.The default value is
WARNING
. This means that the PostgreSQL log will include all the messages with theWARNING
,ERROR
,LOG
,FATAL
, andPANIC
severity level.To disable logging of most messages, select the
PANIC
value.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 along with SQL operators when logging all messages except error ones.
Logging is disabled when the value is
0
.The minimum value is
-1
(parameter values are completely logged), while the maximum one is1073741823
. 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 along with SQL operators when logging error messages.
Logging is disabled when the value is
0
.The minimum value is
-1
(parameter values are completely logged), while the maximum one is1073741823
. 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
A filter for SQL statements to be written to the PostgreSQL log:
none
(LOG_STATEMENT_NONE
for Terraform, CLI, and API): Filter is disabled and SQL statements are not logged.ddl
(LOG_STATEMENT_DDL
for Terraform, CLI, and API): SQL statements that allow you to change data definitions, such asCREATE
,ALTER
, andDROP
, are logged.mod
(LOG_STATEMENT_MOD
for Terraform, CLI, and API): Allddl
statements and data-modifying statements, such asINSERT
andUPDATE
, are logged.all
(LOG_STATEMENT_ALL
for Terraform, CLI, and API): All SQL statements are logged.
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
The rate of SQL statements that will be logged in addition to statements logged for other reasons.
The minimum value is
0.0
, while the maximum one is1.0
. The default value is0.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 is2147483647
(2 GB). If0
, 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
The rate of transactions whose statements will be logged in addition to statements logged for other reasons.
The minimum value is
0.0
, while the maximum one is1.0
. The default value is0.0
.For more information, see the PostgreSQL documentation
. -
Logical decoding work mem
All interfaces
The maximum amount of 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), while the maximum one is1099511627776
(1 TB). The default value is67108864
(64 MB).For more information, see the PostgreSQL documentation
. -
Maintenance io concurrency
All interfaces
The maximum number of concurrent disk I/O operations when maintaining PostgreSQL with the commands
VACUUM
,CREATE INDEX
, andALTER TABLE ADD FOREIGN KEY
for an individual DB session. The higher this number, the more maintenance commands PostgreSQL will attempt to initiate in parallel within a session.The minimum value is
0
, while the maximum one is1000
. The default value is10
.For more information, see the PostgreSQL documentation
. -
Maintenance work mem
All interfaces
The maximum amount of memory (in bytes) to be used by PostgreSQL maintenance operations, such as
VACUUM
,CREATE INDEX
, andALTER TABLE ADD FOREIGN KEY
. The value must be a multiple of 1024.The minimum value is
1048576
(1 MB), while the maximum one is137438953472
(128 GB). The default value is67108864
(64 MB).For more information, see the PostgreSQL documentation
. -
Max connections
Management console
API
CLI
Terraform
The maximum number of simultaneous connections to the PostgreSQL host.
The minimum value is
1
. The maximum and default values depend on the selected host class and are determined by the formula:200 × <number_of_vCPUs_per_host>
Hosts with guaranteed vCPU performance under 100% (
burstable
) use the fixed maximum value:200
.By default, the maximum value is used.
For more information, see the PostgreSQL documentation
. -
Max locks per transaction
Management console
API
CLI
Terraform
The 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
, while the maximum one is2147483647
. The default value is64
.For more information, see the PostgreSQL documentation
. -
Max logical replication workers
Management console
The maximum number of PostgreSQL logical replication workers.
The minimum value is
4
, while the maximum one is100
. By default, the minimum value is used.For more information, see the PostgreSQL documentation
. -
Max parallel maintenance workers
All interfaces
The maximum number of parallel PostgreSQL workers that can be started by a single utility command (for example,
CREATE INDEX
).The minimum value is
0
, while the maximum one is1024
. The default value is2
.For more information, see the PostgreSQL documentation
. -
Max parallel workers
All interfaces
The maximum number of parallel PostgreSQL workers.
The minimum value is
0
, while the maximum one is1024
. The default value is8
.For more information, see the PostgreSQL documentation
. -
Max parallel workers per gather
All interfaces
The maximum number of parallel workers that can be started by a single Gather
node.The minimum value is
0
, while the maximum one is1024
. The default value is2
.For more information, see the PostgreSQL documentation
. -
Max pred locks per transaction
Management console
API
CLI
Terraform
The 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
, while the maximum one is2147483647
(2 GB). The default value is64
.For more information, see the PostgreSQL documentation
. -
Max prepared transactions
Management console
API
CLI
Terraform
The maximum number of transactions that can be in the prepared state
at the same time.The minimum value is
0
, while the maximum one is262143
. The default value is0
.For more information, see the PostgreSQL documentation
. -
Max replication slots
Management console
The 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
, while the maximum one is100
. By default, the minimum value is used.For more information, see the PostgreSQL documentation
. -
Max slot wal keep size
Management console
API
Terraform
The 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 1024.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 that conflict with the about-to-be-applied WAL
entries.The minimum value is
-1
(unlimited waiting time), the maximum one is2147483647
. The default value is30000
(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
, while the maximum one is100
. By default, the minimum value is used.For more information, see the PostgreSQL documentation
. -
Max wal size
All interfaces
The maximum WAL
file size (in bytes) that, when reached, triggers automatic checkpoints.The minimum value is
2
. The maximum value depends on the storage size and is equal to 10% of this value, with a maximum of8589934592
(8 GB). By default, the maximum value is used.For more information, see the PostgreSQL documentation
. -
Max worker processes
Management console
API
CLI
Terraform
The maximum number of PostgreSQL background processes that can be run on the current system.
The minimum value is
0
, while the maximum one is1024
. The default value is8
.For more information, see the PostgreSQL documentation
. -
Min wal size
All interfaces
The amount of disk space (in bytes) occupied by the WAL
that, when exceeded, triggers deletion of old WAL files at checkpoints.The minimum value is
2
. The maximum value depends on the storage size and is equal to 5% of this value, with a maximum of1073741824
(1 GB). By default, the maximum value is used.For more information, see the PostgreSQL documentation
. -
Old snapshot threshold
Management console
API
CLI
Terraform
The minimum time (in milliseconds) that a query snapshot can be used without risk of an error.
The minimum value is
-1
(unlimited), while the maximum one is86400000
(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 theINSERT
,UPDATE
,DELETE
, orSELECT 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 under Gather and Gather Merge nodes without waiting for worker processes.
This setting is enabled by default.
For more information, see the PostgreSQL documentation
. -
Pg hint plan debug print
Management console
API
Terraform
Sets the level of output and detail of debugging information 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
pg_hint_plan
module that lets you adjust automatic execution plans using so-calledhints
that are simple descriptions in the SQL comments of special format. -
Pg hint plan enable hint table
Management console
API
Terraform
Enables the use of the
hint_plan.hints
table. It containshints
for thepg_hint_plan
module and is used when it is not possible edit queries. The table contains the following columns:id
: A unique ID of the row with thehint
. This column is populated automatically.norm_query_string
: A pattern to match thehinted
queries. Replace constants with a?
. Whitespace characters are significant in the pattern.application_name
: The application that initiated the sessions to apply thehint
to.hint
: A series ofhints
excluding surrounding comment marks.
-
Pg hint plan message level
Management console
API
Terraform
The level of debug messages for the
pg_hint_plan
module that will be included in the PostgreSQL log. Only applies when Pg hint plan enable hint is enabled. The possible values include:error
warning
notice
info
(default)log
debug
-
Pg qualstats enabled
Management console
API
CLI
Enables the
pg_qualstats
module to collect statistics on predicates inWHERE
operators andJOIN
blocks. Used to analyze the most frequently calculated query predicates. -
Pg qualstats max
Management console
API
CLI
The maximum number of tracked predicates for the
pg_qualstats
module. Only applies when Pg qualstats enabled is enabled.The minimum value is
100
, while the maximum one is2147483647
. The default value is1000
. -
Pg qualstats resolve oids
Management console
API
CLI
Enables table name recovery using
OID
when collecting statistics for thepg_qualstats
module. The setting makes it easier to analyze data, but requires more disk space for storing statistics. Only applies when Pg qualstats enabled is enabled.This setting is disabled by default.
-
Pg qualstats sample rate
Management console
API
CLI
Percentage of queries (from 0 to 1) that the
pg_qualstats
module collects statistics for. Only applies when Pg qualstats enabled is enabled. Specific setting values:0
: Disables the setting.1
: All queries are analyzed.-1
: Automatic mode, percentage of queries is inverse to the value of the Max connections setting and depends on the selected host class.
By default,
-1
. -
Pg qualstats track constants
Management console
API
CLI
Enables tracking of already encountered predicates in further queries when statistics are collected by the
pg_qualstats
module. Only applies when Pg qualstats enabled is enabled.This setting is enabled by default.
-
Plan cache mode
Management console
API
Terraform
Determines the type of query plan (generic or custom) to be used to execute prepared statements
. The possible values include: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 certain indexes from the planner to prevent them from being used in query plans.This setting is disabled by default.
-
Quote all identifiers
All interfaces
Forces quoting of all identifiers when generating SQL queries from a database, for example, 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 is1
.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 enabled by default.
For more information, see the PostgreSQL documentation
. -
Search path
All interfaces
A 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 andpublic
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 is1
.For more information, see the PostgreSQL documentation
. -
Session duration timeout
Management console
API
CLI
The maximum TTL of the longest active session or transaction (in milliseconds). It applies only to sessions with the
active
andidle 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 you set the setting to
1000
, a 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 is2147483647
. The default value is43200000
(12 hours). The minimum granularity for changing the setting value is1000
.For more information about possible session statuses, see the PostgreSQL documentation
. -
Shared buffers
Management console
API
CLI
Terraform
The amount of 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 total RAM size of the Managed Service for PostgreSQL cluster host. 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
A 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 ('...'
) literally (as specified in the SQL standard) rather than as a special character.This setting is enabled by default.
For more information, see the PostgreSQL documentation
. -
Statement timeout
Management console
API
CLI
Terraform
The maximum statement duration (in milliseconds) after which the command is aborted.
The minimum value is
0
, while the maximum one is2147483647
. The default value is0
.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 enabled 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 include:
Management console/CLITerraformAPI-
on
: A transaction is committed if the WAL is written to the master disk and quorum replica disk. -
off
: A 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
: A transaction is committed if the WAL is written to the master disk. -
remote_write
: A transaction is committed if the WAL is written to the master disk, and 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
: A transaction is committed if the WAL is written to the master disk, and the quorum replica accepted the WAL and applied the changes from it.The default value is
on
.
-
1
or"SYNCHRONOUS_COMMIT_ON"
: A transaction is committed if the WAL is written to the master disk and quorum replica disk. -
2
or"SYNCHRONOUS_COMMIT_OFF"
: A 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"
: A transaction is committed if the WAL is written to the master disk. -
4
or"SYNCHRONOUS_COMMIT_REMOTE_WRITE"
: A transaction is committed if the WAL is written to the master disk, and 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"
: A transaction is committed if the WAL is written to the master disk, and the quorum replica accepted the WAL and applied the changes from it.The default value is
"SYNCHRONOUS_COMMIT_ON"
.
-
SYNCHRONOUS_COMMIT_ON
: A transaction is committed if the WAL is written to the master disk and quorum replica disk. -
SYNCHRONOUS_COMMIT_OFF
: A 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
: A transaction is committed if the WAL is written to the master disk. -
SYNCHRONOUS_COMMIT_REMOTE_WRITE
: A transaction is committed if the WAL is written to the master disk, and 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
: A transaction is committed if the WAL is written to the master disk, and the quorum replica accepted 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
The maximum amount of memory (in bytes) allocated for temporary buffers in each session.
The minimum value is
100
, while the maximum one is1073741823
(1 GB). The default value is8388608
(8 MB).For more information, see the PostgreSQL documentation
. -
Temp file limit
Management console
API
CLI
Terraform
The maximum storage space size (in kilobytes) that a single process can use to create 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 limit), while the maximum one is2147483647
(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
Time zone for output and input of time values.
Default value:
Europe/Moscow
.For more information, see the PostgreSQL documentation
. -
Track activity query size
Management console
API
Terraform
The amount of memory (in bytes) reserved to store the text of the currently executing command for each active session.
The minimum value is
100
, while the maximum one is102400
. The default value is1024
.For more information, see the PostgreSQL documentation
. -
Transform null equals
All interfaces
Enables treating the condition
expression = NULL
asexpression IS NULL
. The condition is true if the expression has the valueNULL
, otherwise it is false. According to the SQL standard,expression = NULL
always returnsNULL
(an 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
The fraction of heap tuples counted in the previous statistics collection. When running the
VACUUM
operation, index statistics are considered to be stale if the ratio of newly inserted tuples to the total number of heap tuples exceeds this fraction. 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
, while the maximum one is10000000000.0
. The default value is0.1
.For more information, see the PostgreSQL documentation
. -
Vacuum cost delay
All interfaces
The amount of time (in milliseconds) for the
VACUUM
andANALYZE
operations to sleep when the cost limit has been exceeded (see Vacuum cost limit).The minimum value is
0
, while the maximum one is100
. The default value is0
.For more information, see the PostgreSQL documentation
. -
Vacuum cost limit
All interfaces
The accumulated cost that will cause the
VACUUM
process to sleep.The minimum value is
1
, while the maximum one is10000
. The default value is200
.For more information, see the PostgreSQL documentation
. -
Vacuum cost page dirty
All interfaces
The estimated cost that is charged when
VACUUM
modifies a block that was previously clean.The minimum value is
0
, while the maximum one is10000
. The default value is20
.For more information, see the PostgreSQL documentation
. -
Vacuum cost page hit
All interfaces
The estimated cost for vacuuming a buffer found in the shared buffer cache.
The minimum value is
0
, while the maximum one is10000
. The default value is1
.For more information, see the PostgreSQL documentation
. -
Vacuum cost page miss
All interfaces
The estimated cost for vacuuming a buffer that has to be read from a disk.
The minimum value is
0
, while the maximum one is10000
. The default value is10
.For more information, see the PostgreSQL documentation
. -
Vacuum failsafe age
Management console
API
CLI
The maximum age of a
frozen
(already ended) transaction measured as the number of transactions initiated after it. After this value is reached, theVACUUM
process triggers vacuuming to avoid transaction counter overflow. PostgreSQL supports this feature starting from version 14.The minimum value is
0
, while the maximum one is2100000000
. The default value is1600000000
.For more information, see the PostgreSQL documentation
. -
Vacuum multixact failsafe age
Management console
API
CLI
The maximum age of a frozen (already ended) multi-transaction
measured as the number of multi-transactions initiated after it. After this value is reached, theVACUUM
process triggers vacuuming to avoid multi-transaction counter overflow. PostgreSQL supports this feature starting from version 14.The minimum value is
0
, while the maximum one is2100000000
. The default value is1600000000
.For more information, see the PostgreSQL documentation
. -
Wal keep size
Management console
API
Terraform
The minimum size (in bytes) of past log segments kept in the WAL
directory so that replicas could fetch them, if needed.The minimum value is
0
(past segments are not saved for replication), while the maximum one is2251799812636672
(2 TB). The default value is0
.For more information, see the PostgreSQL documentation
. -
Work mem
All interfaces
The base maximum amount of memory to be used by an internal query operation (such as a sort or hash table) before writing to temporary disk files.
The minimum value is
64
, while the maximum one is2147483647
(2 GB). The default value is4194304
(4 MB).For more information, see the PostgreSQL documentation
. -
Xmlbinary
All interfaces
The method used for encoding binary data in XML. The possible values include:
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
The default type of conversion between the XML and character string data. The possible values include:
document
(XML_OPTION_DOCUMENT
for Terraform, API, and CLI): An XML document.content
(XML_OPTION_CONTENT
for Terraform, API, and CLI): A fragment of an XML document.
The default value is
content
.For more information, see the PostgreSQL documentation
.
User-level settings
These settings affect the behavior of PostgreSQL when handling user queries:
-
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 (whereN
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 has the
"max_connections": 100
setting, 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
For more information, refer to the Default transaction isolation cluster-level setting.
-
Grants
Management console
API
CLI
Terraform
Roles granted to the user.
-
Lock timeout
Management console
API
CLI
For more information, refer to the Lock timeout cluster-level setting.
-
Log min duration statement
Management console
API
CLI
For more information, refer to 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
For more information, refer to the Log statement cluster-level setting.
-
Login
Management console
API
CLI
Determines whether the user can connect to a PostgreSQL cluster.
The default value is
true
, which means the user can connect to the cluster. -
Pooling mode
Management console
The PgBouncer
connection pooler's operating mode.The possible values include:
session
: Server connection is assigned to the client when establishing a connection, and is maintained for it. The connection gets back to the pool once the client disconnects.transaction
: Server connection is assigned to the client for the transaction execution time only. The connection gets back to the pool once the transaction is completed.statement
: Server connection gets back to the pool once the query has been executed. Multi-statement transactions are not allowed in this mode.
The default value is
session
.For more information, see the PgBouncer documentation
. -
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 include:
read
:SELECT
andCOPY
queries are logged if the data source is a relation or query.write
:INSERT
,UPDATE
,DELETE
,TRUNCATE
, andCOPY
queries are logged if the data target is a relation.function
: Function invocations andDO
sections are logged.role
: Statements related to role and privilege management, such asGRANT
,REVOKE
, orCREATE/ALTER/DROP ROLE
, are logged.ddl
: AnyDDL
statements that do not belong to theROLE
class are logged.misc
: Miscellaneous commands, such asDISCARD
,FETCH
,CHECKPOINT
,VACUUM
, andSET
, are logged.misc_set
: MiscellaneousSET
commands, e.g.,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.
-
Prepared statements pooling
Management console
Allows using prepared statements with merged transactions.
-
Synchronous commit
Management console
API
CLI
For more information, refer to the Synchronous commit cluster-level setting.
-
Temp file limit
Management console
API
CLI
For more information, refer to 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.