Server parameters in Azure Database for PostgreSQL - Flexible Server
APPLIES TO: Azure Database for PostgreSQL - Flexible Server
Azure Database for PostgreSQL provides a subset of configurable parameters for each server. For more information on Postgres parameters, see the PostgreSQL documentation.
Parameter types
Azure Database for PostgreSQL - Flexible Server comes preconfigured with optimal default settings for each parameter. Parameters are categorized into one of the following types:
- Static: These parameters require a server restart to implement any changes.
- Dynamic: These parameters can be altered without the need to restart the server instance. However, changes will apply only to new connections established after the modification.
- Read-only: These parameters aren't user configurable because of their critical role in maintaining reliability, security, or other operational aspects of the service.
To determine the parameter type, go to the Azure portal and open the Server parameters pane. The parameters are grouped into tabs for easy identification.
Parameter customization
Various methods and levels are available to customize your parameters according to your specific needs.
Global level
For altering settings globally at the instance or server level, go to the Server parameters pane in the Azure portal. You can also use other available tools such as the Azure CLI, the REST API, Azure Resource Manager templates, or partner tools.
Note
Because Azure Database for PostgreSQL is a managed database service, users don't have host or operating system access to view or modify configuration files such as postgresql.conf. The content of the files is automatically updated based on parameter changes that you make.
Granular levels
You can adjust parameters at more granular levels. These adjustments override globally set values. Their scope and duration depend on the level at which you make them:
Database level: Use the
ALTER DATABASE
command for database-specific configurations.Role or user level: Use the
ALTER USER
command for user-centric settings.Function, procedure level: When you're defining a function or procedure, you can specify or alter the configuration parameters that will be set when the function is called.
Table level: As an example, you can modify parameters related to autovacuum at this level.
Session level: For the duration of an individual database session, you can adjust specific parameters. PostgreSQL facilitates this adjustment with the following SQL commands:
- Use the
SET
command to make session-specific adjustments. These changes serve as the default settings during the current session. Access to these changes might require specificSET
privileges, and the limitations for modifiable and read-only parameters described earlier don't apply. The corresponding SQL function isset_config(setting_name, new_value, is_local)
. - Use the
SHOW
command to examine existing parameter settings. Its SQL function equivalent iscurrent_setting(setting_name text)
.
- Use the
Supported server parameters
Autovacuum
Name | Versions | Description |
---|---|---|
autovacuum |
11, 12, 13, 14, 15, 16, 17 | Controls whether the server should run the autovacuum subprocess. |
autovacuum_analyze_scale_factor |
11, 12, 13, 14, 15, 16, 17 | Specifies a fraction of the table size to add to autovacuum_vacuum_threshold when deciding whether to trigger a VACUUM. |
autovacuum_analyze_threshold |
11, 12, 13, 14, 15, 16, 17 | Sets the minimum number of inserted, updated or deleted tuples needed to trigger an ANALYZE in any one table. |
autovacuum_freeze_max_age |
11, 12, 13, 14, 15, 16, 17 | Maximum age (in transactions) before triggering autovacuum on a table to prevent transaction ID wraparound. |
autovacuum_max_workers |
11, 12, 13, 14, 15, 16, 17 | Sets the maximum number of simultaneously running autovacuum worker processes. |
autovacuum_multixact_freeze_max_age |
11, 12, 13, 14, 15, 16, 17 | Maximum age (in multixact) before triggering autovacuum on a table to prevent multixact wraparound. |
autovacuum_naptime |
11, 12, 13, 14, 15, 16, 17 | Sets minimum delay between autovacuum runs on any given database. |
autovacuum_vacuum_cost_delay |
11, 12, 13, 14, 15, 16, 17 | Sets cost delay value (milliseconds) that will be used in automatic VACUUM operations. |
autovacuum_vacuum_cost_limit |
11, 12, 13, 14, 15, 16, 17 | Sets cost limit value that will be used in automatic VACUUM operations. |
autovacuum_vacuum_insert_scale_factor |
13, 14, 15, 16, 17 | Specifies a fraction of the table size to add to autovacuum_vacuum_insert_threshold when deciding whether to trigger a VACUUM. |
autovacuum_vacuum_insert_threshold |
13, 14, 15, 16, 17 | Specifies the number of inserted tuples needed to trigger a VACUUM in any one table. |
autovacuum_vacuum_scale_factor |
11, 12, 13, 14, 15, 16, 17 | Specifies a fraction of the table size to add to autovacuum_vacuum_threshold when deciding whether to trigger a VACUUM. |
autovacuum_vacuum_threshold |
11, 12, 13, 14, 15, 16, 17 | Specifies the minimum number of updated or deleted tuples needed to trigger a VACUUM in any one table. |
Client Connection Defaults / Locale and Formatting
Name | Versions | Description |
---|---|---|
client_encoding |
11, 12, 13, 14, 15, 16, 17 | Sets the client-side encoding (character set). The default is to use the database encoding. |
DateStyle |
11, 12, 13, 14, 15, 16, 17 | Sets the display format for date and time values. |
default_text_search_config |
11, 12, 13, 14, 15, 16, 17 | Selects the text search configuration that is used by those variants of the text search functions that do not have an explicit argument specifying it. |
extra_float_digits |
11, 12, 13, 14, 15, 16, 17 | Sets the number of digits displayed for floating-point values. |
IntervalStyle |
11, 12, 13, 14, 15, 16, 17 | Sets the display format for interval values. |
lc_messages |
11, 12, 13, 14, 15, 16, 17 | Sets the language in which messages are displayed. |
lc_monetary |
11, 12, 13, 14, 15, 16, 17 | Sets the locale for formatting monetary amounts. |
lc_numeric |
11, 12, 13, 14, 15, 16, 17 | Sets the locale for formatting numbers. |
lc_time |
11, 12, 13, 14, 15, 16, 17 | Sets the locale for formatting date and time values. |
TimeZone |
11, 12, 13, 14, 15, 16, 17 | Sets the time zone for displaying and interpreting time stamps. |
timezone_abbreviations |
11, 12, 13, 14, 15, 16, 17 | Selects a file of time zone abbreviations. |
Client Connection Defaults / Other Defaults
Name | Versions | Description |
---|---|---|
dynamic_library_path |
11, 12, 13, 14, 15, 16, 17 | Sets the path for dynamically loadable modules. |
gin_fuzzy_search_limit |
11, 12, 13, 14, 15, 16, 17 | Sets the maximum allowed result for exact search by GIN. |
Client Connection Defaults / Shared Library Preloading
Name | Versions | Description |
---|---|---|
jit_provider |
11, 12, 13, 14, 15, 16, 17 | JIT provider to use. |
local_preload_libraries |
11, 12, 13, 14, 15, 16, 17 | Lists unprivileged shared libraries to preload into each backend. |
session_preload_libraries |
11, 12, 13, 14, 15, 16, 17 | Lists shared libraries to preload into each backend. |
shared_preload_libraries |
11, 12, 13, 14, 15, 16, 17 | Sets which shared libraries are preloaded at server start. |
Client Connection Defaults / Statement Behavior
Name | Versions | Description |
---|---|---|
bytea_output |
11, 12, 13, 14, 15, 16, 17 | Sets the output format for values of type bytea. Valid values are hex (the default) and escape (the traditional PostgreSQL format). |
check_function_bodies |
11, 12, 13, 14, 15, 16, 17 | Checks function bodies during CREATE FUNCTION. |
client_min_messages |
11, 12, 13, 14, 15, 16, 17 | Controls the message levels that are sent to the client. |
default_table_access_method |
12, 13, 14, 15, 16, 17 | Sets the default table access method for new tables. |
default_tablespace |
11, 12, 13, 14, 15, 16, 17 | Sets the default tablespace to create tables and indexes in. |
default_toast_compression |
14, 15, 16, 17 | Sets the default compression method for compressible values. |
default_transaction_deferrable |
11, 12, 13, 14, 15, 16, 17 | This parameter controls the default deferrable status of each new transaction. It has no effect on read-write transactions or those operating at isolation levels lower than serializable. |
default_transaction_isolation |
11, 12, 13, 14, 15, 16, 17 | This parameter controls the default isolation level of each new transaction. The default is 'read committed'. |
default_transaction_read_only |
11, 12, 13, 14, 15, 16, 17 | Sets the default read-only status of each new transaction. |
gin_pending_list_limit |
11, 12, 13, 14, 15, 16, 17 | Sets the maximum size of the pending list for GIN index. |
idle_in_transaction_session_timeout |
11, 12, 13, 14, 15, 16, 17 | Sets the maximum allowed duration of any idling transaction. |
idle_session_timeout |
14, 15, 16, 17 | Sets the maximum allowed idle time between queries, when not in a transaction. |
lock_timeout |
11, 12, 13, 14, 15, 16, 17 | Sets the maximum allowed duration (in milliseconds) of any wait for a lock. 0 turns this off. |
row_security |
11, 12, 13, 14, 15, 16, 17 | Enables row security. |
search_path |
11, 12, 13, 14, 15, 16, 17 | Sets the schema search order for names that are not schema-qualified. |
session_replication_role |
11, 12, 13, 14, 15, 16, 17 | Controls firing of replication-related triggers and rules for the current session. |
statement_timeout |
11, 12, 13, 14, 15, 16, 17 | Sets the maximum allowed duration (in milliseconds) of any statement. 0 turns this off. |
temp_tablespaces |
11, 12, 13, 14, 15, 16, 17 | Sets the default tablespace(s) to use for temporary tables and sort files if not specified in the CREATE command. |
transaction_deferrable |
11, 12, 13, 14, 15, 16, 17 | Whether to defer a read-only serializable transaction until it can be executed with no possible serialization failures. |
transaction_isolation |
11, 12, 13, 14, 15, 16, 17 | Sets the current transaction's isolation level. |
transaction_read_only |
11, 12, 13, 14, 15, 16, 17 | Sets the current transaction's read-only status. |
vacuum_cleanup_index_scale_factor |
11, 12, 13 | Number of tuple inserts prior to index cleanup as a fraction of reltuples. |
vacuum_failsafe_age |
14, 15, 16, 17 | Age at which VACUUM should trigger failsafe to avoid a wraparound outage. |
vacuum_freeze_min_age |
11, 12, 13, 14, 15, 16, 17 | Specifies the cutoff age (in transactions) that VACUUM should use to decide whether to freeze row versions while scanning a table. |
vacuum_freeze_table_age |
11, 12, 13, 14, 15, 16, 17 | Age at which VACUUM should scan whole table to freeze tuples. |
vacuum_multixact_failsafe_age |
14, 15, 16, 17 | Multixact age at which VACUUM should trigger failsafe to avoid a wraparound outage. |
vacuum_multixact_freeze_min_age |
11, 12, 13, 14, 15, 16, 17 | Specifies the cutoff age (in multixacts) that VACUUM should use to decide whether to replace multixact IDs with a newer transaction ID or multixact ID while scanning a table. |
vacuum_multixact_freeze_table_age |
11, 12, 13, 14, 15, 16, 17 | VACUUM performs a full table scan to freeze rows if the table has reached the age specified by this setting. |
xmlbinary |
11, 12, 13, 14, 15, 16, 17 | Sets how binary values are to be encoded in XML. |
xmloption |
11, 12, 13, 14, 15, 16, 17 | Sets whether XML data in implicit parsing and serialization operations is to be considered as documents or content fragments. |
Connections and Authentication / Authentication
Name | Versions | Description |
---|---|---|
authentication_timeout |
11, 12, 13, 14, 15, 16, 17 | Sets the maximum allowed time to complete client authentication. |
db_user_namespace |
11, 12, 13, 14, 15, 16, 17 | Enables per-database user names. |
krb_caseins_users |
11, 12, 13, 14, 15, 16, 17 | Sets whether Kerberos and GSSAPI user names should be treated as case-insensitive. |
krb_server_keyfile |
11, 12, 13, 14, 15, 16, 17 | Sets the location of the Kerberos server key file. |
password_encryption |
11, 12, 13, 14, 15, 16, 17 | Determines the algorithm to use to encrypt the password. |
Connections and Authentication / Connection Settings
Name | Versions | Description |
---|---|---|
bonjour |
11, 12, 13, 14, 15, 16, 17 | Enables advertising the server via Bonjour. |
bonjour_name |
11, 12, 13, 14, 15, 16, 17 | Sets the Bonjour service name. |
listen_addresses |
11, 12, 13, 14, 15, 16, 17 | Sets the host name or IP address(es) to listen to. |
max_connections |
11, 12, 13, 14, 15, 16, 17 | Sets the maximum number of concurrent connections to the database server. |
port |
11, 12, 13, 14, 15, 16, 17 | Sets the TCP port the server listens on. |
reserved_connections |
11, 12, 13, 14, 15, 16, 17 | Sets the number of connections slots reserved for replication users and super users. |
superuser_reserved_connections |
11, 12, 13, 14, 15, 16, 17 | Sets the number of connection slots reserved for superusers. |
unix_socket_directories |
11, 12, 13, 14, 15, 16, 17 | Sets the directories where Unix-domain sockets will be created. |
unix_socket_group |
11, 12, 13, 14, 15, 16, 17 | Sets the owning group of the Unix-domain socket. |
unix_socket_permissions |
11, 12, 13, 14, 15, 16, 17 | Sets the access permissions of the Unix-domain socket. |
Connections and Authentication / SSL
Name | Versions | Description |
---|---|---|
ssl |
11, 12, 13, 14, 15, 16, 17 | Enables SSL connections. |
ssl_ca_file |
11, 12, 13, 14, 15, 16, 17 | Location of the SSL certificate authority file. |
ssl_cert_file |
11, 12, 13, 14, 15, 16, 17 | Location of the SSL server certificate file. |
ssl_ciphers |
11, 12, 13, 14, 15, 16, 17 | Sets the list of allowed SSL ciphers. |
ssl_crl_dir |
14, 15, 16, 17 | Location of the SSL certificate revocation list directory. |
ssl_crl_file |
11, 12, 13, 14, 15, 16, 17 | Location of the SSL certificate revocation list file. |
ssl_dh_params_file |
11, 12, 13, 14, 15, 16, 17 | Location of the SSL DH parameters file. |
ssl_ecdh_curve |
11, 12, 13, 14, 15, 16, 17 | Sets the curve to use for ECDH. |
ssl_key_file |
11, 12, 13, 14, 15, 16, 17 | Location of the SSL server private key file. |
ssl_max_protocol_version |
12, 13, 14, 15, 16, 17 | Sets the maximum SSL/TLS protocol version to use. |
ssl_min_protocol_version |
12, 13, 14, 15, 16, 17 | Sets the minimum SSL/TLS protocol version to use. |
ssl_passphrase_command |
11, 12, 13, 14, 15, 16, 17 | Command to obtain passphrases for SSL. |
ssl_passphrase_command_supports_reload |
11, 12, 13, 14, 15, 16, 17 | Controls whether ssl_passphrase_command is called during server reload. |
ssl_prefer_server_ciphers |
11, 12, 13, 14, 15, 16, 17 | Give priority to server ciphersuite order. |
Connections and Authentication / TCP Settings
Name | Versions | Description |
---|---|---|
client_connection_check_interval |
14, 15, 16, 17 | Sets the time interval between checks for disconnection while running queries. |
tcp_keepalives_count |
11, 12, 13, 14, 15, 16, 17 | Specifies the number of TCP keepalives that can be lost before the server's connection to the client is considered dead. |
tcp_keepalives_idle |
11, 12, 13, 14, 15, 16, 17 | Specifies the number of seconds of inactivity after which TCP should send a keepalive message to the client. |
tcp_keepalives_interval |
11, 12, 13, 14, 15, 16, 17 | Specifies the number of seconds after which a TCP keepalive message that is not acknowledged by the client should be retransmitted. |
tcp_user_timeout |
12, 13, 14, 15, 16, 17 | Specifies the amount of time that transmitted data may remain unacknowledged before the TCP connection is forcibly closed. |
Customized Options
Name | Versions | Description |
---|---|---|
auto_explain.log_analyze |
11, 12, 13, 14, 15, 16, 17 | Use EXPLAIN ANALYZE for plan logging. |
auto_explain.log_buffers |
11, 12, 13, 14, 15, 16, 17 | Log buffers usage. |
auto_explain.log_format |
11, 12, 13, 14, 15, 16, 17 | EXPLAIN format to be used for plan logging. |
auto_explain.log_level |
12, 13, 14, 15, 16, 17 | Log level for the plan. |
auto_explain.log_min_duration |
11, 12, 13, 14, 15, 16, 17 | Sets the minimum execution time above which plans will be logged. Zero prints all plans. -1 turns this feature off. |
auto_explain.log_nested_statements |
11, 12, 13, 14, 15, 16, 17 | Log nested statements. |
auto_explain.log_settings |
12, 13, 14, 15, 16, 17 | Log modified configuration parameters affecting query planning. |
auto_explain.log_timing |
11, 12, 13, 14, 15, 16, 17 | Collect timing data, not just row counts. |
auto_explain.log_triggers |
11, 12, 13, 14, 15, 16, 17 | Include trigger statistics in plans. This has no effect unless log_analyze is also set. |
auto_explain.log_verbose |
11, 12, 13, 14, 15, 16, 17 | Use EXPLAIN VERBOSE for plan logging. |
auto_explain.log_wal |
13, 14, 15, 16, 17 | Log WAL usage. |
auto_explain.sample_rate |
11, 12, 13, 14, 15, 16, 17 | Fraction of queries to process. |
azure.accepted_password_auth_method |
11, 12, 13, 14, 15, 16, 17 | Accepted password authentication method. |
azure.allow_hostname_in_username |
11 | Specifies if hostname is allowed in the username for Azure Database for PostgreSQL Flexible Server. |
azure.enable_temp_tablespaces_on_local_ssd |
11, 12, 13, 14, 15, 16, 17 | Create temp tablespace on ephemeral disk. |
azure.extensions |
11, 12, 13, 14, 15, 16, 17 | Specifies which extensions are allowed to be created in the server. |
azure.single_to_flex_migration |
11, 12, 13, 14, 15, 16, 17 | Specifies if this is a server created for migrating from Azure Database for PostgreSQL Single Server to Flexible Server. |
azure_storage.blob_block_size_mb |
12, 13, 14, 15, 16, 17 | Blob block size in megabytes for blob_put in extension azure_storage. |
cron.database_name |
11, 12, 13, 14, 15, 16, 17 | Sets the database in which pg_cron metadata is kept. |
cron.log_run |
11, 12, 13, 14, 15, 16, 17 | Log all jobs runs into the job_run_details table. |
cron.log_statement |
11, 12, 13, 14, 15, 16, 17 | Log all cron statements prior to execution. |
cron.max_running_jobs |
11, 12, 13, 14, 15, 16, 17 | Sets the maximum number of jobs that can run concurrently. This value is limited by max_connections. |
pgaudit.log |
11, 12, 13, 14, 15, 16, 17 | Specifies which classes of statements will be logged by session audit logging. |
pgaudit.log_catalog |
11, 12, 13, 14, 15, 16, 17 | Specifies that session logging should be enabled in the case where all relations in a statement are in pg_catalog. |
pgaudit.log_client |
11, 12, 13, 14, 15, 16, 17 | Specifies whether audit messages should be visible to client. |
pgaudit.log_level |
11, 12, 13, 14, 15, 16, 17 | Specifies the log level that will be used for log entries. |
pgaudit.log_parameter |
11, 12, 13, 14, 15, 16, 17 | Specifies that audit logging should include the parameters that were passed with the statement. |
pgaudit.log_relation |
11, 12, 13, 14, 15, 16, 17 | Specifies whether session audit logging should create a separate log entry for each relation referenced in a SELECT or DML statement. |
pgaudit.log_statement_once |
11, 12, 13, 14, 15, 16, 17 | Specifies whether logging will include the statement text and parameters with the first log entry for a statement/substatement combination or with every entry. |
pgaudit.role |
11, 12, 13, 14, 15, 16, 17 | Specifies the master role to use for object audit logging. |
pglogical.batch_inserts |
11, 12, 13, 14, 15, 16, 17 | Tells PGLogical to use batch insert mechanism if possible. |
pglogical.conflict_log_level |
11, 12, 13, 14, 15, 16, 17 | Sets the log level for reporting detected conflicts when the pglogical.conflict_resolution is set to anything else than error. |
pglogical.conflict_resolution |
11, 12, 13, 14, 15, 16, 17 | Sets the resolution method for any detected conflicts between local data and incoming changes. |
pglogical.use_spi |
11, 12, 13, 14, 15, 16, 17 | Tells PGLogical to use SPI interface to form actual SQL (INSERT, UPDATE, DELETE) statements to apply incoming changes instead of using internal low level interface. |
pgms_stats.is_enabled_fs |
11, 12, 13, 14, 15, 16, 17 | Internal Use Only: This parameter is used as a feature override switch. |
pgms_wait_sampling.history_period |
11, 12, 13, 14, 15, 16, 17 | Set the frequency, in milliseconds, at which wait events are sampled. |
pgms_wait_sampling.is_enabled_fs |
11, 12, 13, 14, 15, 16, 17 | Internal Use Only: This parameter is used as a feature override switch. If it shows as off, wait sampling will be disabled despite the value set for pgms_wait_sampling.query_capture_mode. |
pgms_wait_sampling.query_capture_mode |
11, 12, 13, 14, 15, 16, 17 | Selects which statements are tracked by the pgms_wait_sampling extension. |
pg_partman_bgw.analyze |
11, 12, 13, 14, 15, 16, 17 | Same purpose as the p_analyze argument to run_maintenance(). |
pg_partman_bgw.dbname |
11, 12, 13, 14, 15, 16, 17 | Required. The database(s) that run_maintenance() will run on. If more than one, use a comma separated list. If not set, BGW will do nothing. |
pg_partman_bgw.interval |
11, 12, 13, 14, 15, 16, 17 | Number of seconds between calls to run_maintenance(). |
pg_partman_bgw.jobmon |
11, 12, 13, 14, 15, 16, 17 | Same purpose as the p_jobmon argument to run_maintenance(). |
pg_partman_bgw.role |
11, 12, 13, 14, 15, 16, 17 | The role that run_maintenance() will run as. Default is postgres. Only a single role name is allowed. |
pg_qs.interval_length_minutes |
11, 12, 13, 14, 15, 16, 17 | Sets the query_store capture interval in minutes for pg_qs - this is the frequency of data persistence. |
pg_qs.is_enabled_fs |
11, 12, 13, 14, 15, 16, 17 | Internal Use Only: This parameter is used as a feature override switch. If it shows as off, Query Store will be disabled despite the value set for pg_qs.query_capture_mode. |
pg_qs.max_plan_size |
11, 12, 13, 14, 15, 16, 17 | Sets the maximum number of bytes that will be saved for query plan text for pg_qs; longer plans will be truncated. |
pg_qs.max_query_text_length |
11, 12, 13, 14, 15, 16, 17 | Sets the maximum query text length that will be saved; longer queries will be truncated. |
pg_qs.query_capture_mode |
11, 12, 13, 14, 15, 16, 17 | Sets query capture mode for query store. None disables any capturing. |
pg_qs.retention_period_in_days |
11, 12, 13, 14, 15, 16, 17 | Sets the retention period window in days for pg_qs - after this time data will be deleted. |
pg_qs.store_query_plans |
11, 12, 13, 14, 15, 16, 17 | Turns saving query plans on or off for pg_qs |
pg_qs.track_utility |
11, 12, 13, 14, 15, 16, 17 | Selects whether utility commands are tracked by pg_qs. |
pg_stat_statements.max |
11, 12, 13, 14, 15, 16, 17 | Sets the maximum number of statements tracked by pg_stat_statements. |
pg_stat_statements.save |
11, 12, 13, 14, 15, 16, 17 | Save pg_stat_statements statistics across server shutdowns. |
pg_stat_statements.track |
11, 12, 13, 14, 15, 16, 17 | Controls which statements are counted by pg_stat_statements. |
pg_stat_statements.track_utility |
11, 12, 13, 14, 15, 16, 17 | Selects whether utility commands are tracked by pg_stat_statements. |
Developer Options
Name | Versions | Description |
---|---|---|
allow_in_place_tablespaces |
11, 12, 13, 14, 15, 16, 17 | Allows tablespaces directly inside pg_tblspc, for testing. |
allow_system_table_mods |
11, 12, 13, 14, 15, 16, 17 | Allows modifications of the structure of system tables. |
backtrace_functions |
13, 14, 15, 16, 17 | Log backtrace for errors in these functions. |
debug_discard_caches |
14, 15, 16, 17 | Aggressively flush system caches for debugging purposes. |
debug_parallel_query |
16, 17 | Forces use of parallel query facilities. |
force_parallel_mode |
11, 12, 13, 14, 15 | Forces use of parallel query facilities. |
ignore_checksum_failure |
11, 12, 13, 14, 15, 16, 17 | Continues processing after a checksum failure. |
ignore_invalid_pages |
13, 14, 15, 16, 17 | Continues recovery after an invalid pages failure. |
ignore_system_indexes |
11, 12, 13, 14, 15, 16, 17 | Disables reading from system indexes. |
jit_debugging_support |
11, 12, 13, 14, 15, 16, 17 | Register JIT-compiled functions with debugger. |
jit_dump_bitcode |
11, 12, 13, 14, 15, 16, 17 | Write out LLVM bitcode to facilitate JIT debugging. |
jit_expressions |
11, 12, 13, 14, 15, 16, 17 | Allow JIT compilation of expressions. |
jit_profiling_support |
11, 12, 13, 14, 15, 16, 17 | Register JIT-compiled functions with perf profiler. |
jit_tuple_deforming |
11, 12, 13, 14, 15, 16, 17 | Allow JIT compilation of tuple deforming. |
post_auth_delay |
11, 12, 13, 14, 15, 16, 17 | Sets the amount of time to wait after authentication on connection startup. |
pre_auth_delay |
11, 12, 13, 14, 15, 16, 17 | Sets the amount of time to wait before authentication on connection startup. |
remove_temp_files_after_crash |
14, 16, 17 | Remove temporary files after backend crash. |
trace_notify |
11, 12, 13, 14, 15, 16, 17 | Generates debugging output for LISTEN and NOTIFY. |
trace_recovery_messages |
11, 12, 13, 14, 15, 16 | Enables logging of recovery-related debugging information. |
trace_sort |
11, 12, 13, 14, 15, 16, 17 | Emit information about resource usage in sorting. |
wal_consistency_checking |
11, 12, 13, 14, 15, 16, 17 | Sets the WAL resource managers for which WAL consistency checks are done. |
zero_damaged_pages |
11, 12, 13, 14, 15, 16, 17 | Continues processing past damaged page headers. |
Error Handling
Name | Versions | Description |
---|---|---|
data_sync_retry |
11, 12, 13, 14, 15, 16, 17 | Whether to continue running after a failure to sync data files. |
exit_on_error |
11, 12, 13, 14, 15, 16, 17 | Terminates session on any error. |
recovery_init_sync_method |
14, 15, 16, 17 | Sets the method for synchronizing the data directory before crash recovery. |
restart_after_crash |
11, 12, 13, 14, 15, 16, 17 | Reinitialize server after backend crash. |
File Locations
Name | Versions | Description |
---|---|---|
config_file |
11, 12, 13, 14, 15, 16, 17 | Sets the server's main configuration file. |
data_directory |
11, 12, 13, 14, 15, 16, 17 | Sets the server's data directory. |
external_pid_file |
11, 12, 13, 14, 15, 16, 17 | Writes the postmaster PID to the specified file. |
hba_file |
11, 12, 13, 14, 15, 16, 17 | Sets the server's "hba" configuration file. |
ident_file |
11, 12, 13, 14, 15, 16, 17 | Sets the server's "ident" configuration file. |
Intelligent Tuning
Name | Versions | Description |
---|---|---|
intelligent_tuning |
11, 12, 13, 14, 15, 16, 17 | Enables intelligent tuning |
intelligent_tuning.metric_targets |
11, 12, 13, 14, 15, 16, 17 | Specifies which metrics will be adjusted by intelligent tuning. |
Lock Management
Name | Versions | Description |
---|---|---|
deadlock_timeout |
11, 12, 13, 14, 15, 16, 17 | Sets the amount of time, in milliseconds, to wait on a lock before checking for deadlock. |
max_locks_per_transaction |
11, 12, 13, 14, 15, 16, 17 | Sets the maximum number of locks can be taken per transaction. When running a replica server, you must set this parameter to the same or higher value than on the master server. |
max_pred_locks_per_page |
11, 12, 13, 14, 15, 16, 17 | Sets the maximum number of predicate-locked tuples per page. |
max_pred_locks_per_relation |
11, 12, 13, 14, 15, 16, 17 | Sets the maximum number of predicate-locked pages and tuples per relation. |
max_pred_locks_per_transaction |
11, 12, 13, 14, 15, 16, 17 | Sets the maximum number of predicate locks per transaction. |
Metrics
Name | Versions | Description |
---|---|---|
metrics.autovacuum_diagnostics |
11, 12, 13, 14, 15, 16, 17 | Enables metrics collection for all table statistics within a database |
metrics.collector_database_activity |
11, 12, 13, 14, 15, 16, 17 | Enables metrics collection for database and activity statistics |
metrics.pgbouncer_diagnostics |
11, 12, 13, 14, 15, 16, 17 | Enables metrics collection for PgBouncer. |
PgBouncer
Name | Versions | Description |
---|---|---|
pgbouncer.default_pool_size |
11, 12, 13, 14, 15, 16, 17 | How many server connections to allow per user/database pair. |
pgbouncer.enabled |
11, 12, 13, 14, 15, 16, 17 | Denotes if pgBouncer service is enabled. |
pgbouncer.ignore_startup_parameters |
11, 12, 13, 14, 15, 16, 17 | Comma-separated list of parameters that PgBouncer can ignore because they are going to be handled by the admin. |
pgbouncer.max_client_conn |
11, 12, 13, 14, 15, 16, 17 | Maximum number of client connections allowed. |
pgbouncer.max_prepared_statements |
11, 12, 13, 14, 15, 16, 17 | When this is set to a non-zero value PgBouncer tracks protocol-level named prepared statements related commands sent by the client in transaction and statement pooling mode. |
pgbouncer.min_pool_size |
11, 12, 13, 14, 15, 16, 17 | Add more server connections to pool if below this number. |
pgbouncer.pool_mode |
11, 12, 13, 14, 15, 16, 17 | Specifies when a server connection can be reused by other clients. |
pgbouncer.query_wait_timeout |
11, 12, 13, 14, 15, 16, 17 | Maximum time (in seconds) queries are allowed to spend waiting for execution. If the query is not assigned to a server during that time, the client is disconnected. |
pgbouncer.server_idle_timeout |
11, 12, 13, 14, 15, 16, 17 | If a server connection has been idle more than this many seconds it will be dropped. If 0 then timeout is disabled. |
pgbouncer.stats_users |
11, 12, 13, 14, 15, 16, 17 | Comma-separated list of database users that are allowed to connect and run read-only queries on the pgBouncer console. |
Preset Options
Name | Versions | Description |
---|---|---|
block_size |
11, 12, 13, 14, 15, 16, 17 | Shows the size of a disk block. |
data_checksums |
11, 12, 13, 14, 15, 16, 17 | Shows whether data checksums are turned on for this cluster. |
data_directory_mode |
11, 12, 13, 14, 15, 16, 17 | Shows the mode of the data directory. |
debug_assertions |
11, 12, 13, 14, 15, 16, 17 | Shows whether the running server has assertion checks enabled. |
in_hot_standby |
14, 15, 16, 17 | Shows whether hot standby is currently active. |
integer_datetimes |
11, 12, 13, 14, 15, 16, 17 | Shows whether datetimes are integer based. |
lc_collate |
11, 12, 13, 14, 15 | Shows the collation order locale. |
lc_ctype |
11, 12, 13, 14, 15 | Shows the character classification and case conversion locale. |
max_function_args |
11, 12, 13, 14, 15, 16, 17 | Shows the maximum number of function arguments. |
max_identifier_length |
11, 12, 13, 14, 15, 16, 17 | Shows the maximum identifier length. |
max_index_keys |
11, 12, 13, 14, 15, 16, 17 | Shows the maximum number of index keys. |
segment_size |
11, 12, 13, 14, 15, 16, 17 | Shows the number of pages per disk file. |
server_encoding |
11, 12, 13, 14, 15, 16, 17 | Shows the server (database) character set encoding. |
server_version |
11, 12, 13, 14, 15, 16, 17 | Shows the server version. |
server_version_num |
11, 12, 13, 14, 15, 16, 17 | Shows the server version as an integer. |
shared_memory_size |
15, 16, 17 | Shows the size of the server's main shared memory area (rounded up to the nearest MB). |
shared_memory_size_in_huge_pages |
15, 16, 17 | Shows the number of huge pages needed for the main shared memory area. |
ssl_library |
12, 13, 14, 15, 16, 17 | Shows the name of the SSL library. |
wal_block_size |
11, 12, 13, 14, 15, 16, 17 | Shows the block size in the write ahead log. |
wal_segment_size |
11, 12, 13, 14, 15, 16, 17 | Shows the size of write ahead log segments. |
Process Title
Name | Versions | Description |
---|---|---|
cluster_name |
11, 12, 13, 14, 15, 16, 17 | Sets the name of the cluster, which is included in the process title. |
update_process_title |
11, 12, 13, 14, 15, 16, 17 | Updates the process title to show the active SQL command. |
Query Tuning / Genetic Query Optimizer
Name | Versions | Description |
---|---|---|
geqo |
11, 12, 13, 14, 15, 16, 17 | Enables genetic query optimization. |
geqo_effort |
11, 12, 13, 14, 15, 16, 17 | GEQO: effort is used to set the default for other GEQO parameters. |
geqo_generations |
11, 12, 13, 14, 15, 16, 17 | GEQO: number of iterations of the algorithm. |
geqo_pool_size |
11, 12, 13, 14, 15, 16, 17 | GEQO: number of individuals in the population. |
geqo_seed |
11, 12, 13, 14, 15, 16, 17 | GEQO: seed for random path selection. |
geqo_selection_bias |
11, 12, 13, 14, 15, 16, 17 | GEQO: selective pressure within the population. |
geqo_threshold |
11, 12, 13, 14, 15, 16, 17 | Sets the threshold of FROM items beyond which GEQO is used. |
Query Tuning / Other Planner Options
Name | Versions | Description |
---|---|---|
constraint_exclusion |
11, 12, 13, 14, 15, 16, 17 | Controls the query planner's use of table constraints to optimize queries. |
cursor_tuple_fraction |
11, 12, 13, 14, 15, 16, 17 | Sets the planner's estimate of the fraction of a cursor's rows that will be retrieved. |
default_statistics_target |
11, 12, 13, 14, 15, 16, 17 | Sets the default statistics target for table columns without a column-specific target. |
from_collapse_limit |
11, 12, 13, 14, 15, 16, 17 | The planner will merge sub-queries into upper queries upto this limit in FROM clause. Smaller values reduce planning time but might yield inferior query plans. |
jit |
11, 12, 13, 14, 15, 16, 17 | Determines whether JIT compilation may be used by PostgreSQL. |
join_collapse_limit |
11, 12, 13, 14, 15, 16, 17 | Sets the FROM-list size beyond which JOIN constructs are not flattened. |
plan_cache_mode |
12, 13, 14, 15, 16, 17 | Controls the planner's selection of custom or generic plan. |
recursive_worktable_factor |
15, 16, 17 | Sets the planner's estimate of the average size of a recursive query's working table. |
Query Tuning / Planner Cost Constants
Name | Versions | Description |
---|---|---|
cpu_index_tuple_cost |
11, 12, 13, 14, 15, 16, 17 | Sets the planner's estimate of the cost of processing each index entry during an index scan. |
cpu_operator_cost |
11, 12, 13, 14, 15, 16, 17 | Sets the planner's estimate of the cost of processing each operator or function executed during a query. |
cpu_tuple_cost |
11, 12, 13, 14, 15, 16, 17 | Sets the planner's estimate of the cost of processing each row during a query. |
effective_cache_size |
11, 12, 13, 14, 15, 16, 17 | Sets the planner's assumption about the size of the disk cache. |
jit_above_cost |
11, 12, 13, 14, 15, 16, 17 | Sets the query cost above which JIT compilation is activated, if enabled. |
jit_inline_above_cost |
11, 12, 13, 14, 15, 16, 17 | Sets the query cost above which JIT compilation attempts to inline functions and operators. |
jit_optimize_above_cost |
11, 12, 13, 14, 15, 16, 17 | Sets the query cost above which JIT compilation applies expensive optimizations. |
min_parallel_index_scan_size |
11, 12, 13, 14, 15, 16, 17 | Sets the minimum amount of index data for a parallel scan. |
min_parallel_table_scan_size |
11, 12, 13, 14, 15, 16, 17 | Sets the minimum amount of table data that must be scanned for a parallel scan to be considered. |
parallel_setup_cost |
11, 12, 13, 14, 15, 16, 17 | Sets the planner's estimate of the cost of starting up worker processes for parallel query. |
parallel_tuple_cost |
11, 12, 13, 14, 15, 16, 17 | Sets the planner's estimate of the cost of passing each tuple (row) from worker to master backend. |
random_page_cost |
11, 12, 13, 14, 15, 16, 17 | Sets the planner's estimate of the cost of a nonsequentially fetched disk page. |
seq_page_cost |
11, 12, 13, 14, 15, 16, 17 | Sets the planner's estimate of the cost of a sequentially fetched disk page. |
Query Tuning / Planner Method Configuration
Name | Versions | Description |
---|---|---|
enable_async_append |
14, 15, 16, 17 | Enables the planner's use of async append plans. |
enable_bitmapscan |
11, 12, 13, 14, 15, 16, 17 | Enables the planner's use of bitmap-scan plans. |
enable_gathermerge |
11, 12, 13, 14, 15, 16, 17 | Enables the planner's use of gather merge plans. |
enable_hashagg |
11, 12, 13, 14, 15, 16, 17 | Enables the planner's use of hashed aggregation plans. |
enable_hashjoin |
11, 12, 13, 14, 15, 16, 17 | Enables the planner's use of hash join plans. |
enable_incremental_sort |
13, 14, 15, 16, 17 | Enables the planner's use of incremental sort steps. |
enable_indexonlyscan |
11, 12, 13, 14, 15, 16, 17 | Enables the planner's use of index-only-scan plans. |
enable_indexscan |
11, 12, 13, 14, 15, 16, 17 | Enables the planner's use of index-scan plans. |
enable_material |
11, 12, 13, 14, 15, 16, 17 | Enables the planner's use of materialization. |
enable_memoize |
14, 15, 16, 17 | Enables the planner's use of memoization. |
enable_mergejoin |
11, 12, 13, 14, 15, 16, 17 | Enables the planner's use of merge join plans. |
enable_nestloop |
11, 12, 13, 14, 15, 16, 17 | Enables the planner's use of nested loop join plans. |
enable_parallel_append |
11, 12, 13, 14, 15, 16, 17 | Enables the planner's use of parallel append plans. |
enable_parallel_hash |
11, 12, 13, 14, 15, 16, 17 | Enables the planner's use of parallel hash plans. |
enable_partition_pruning |
11, 12, 13, 14, 15, 16, 17 | Enables plan-time and execution-time partition pruning. |
enable_partitionwise_aggregate |
11, 12, 13, 14, 15, 16, 17 | Enables or disables the query planner's use of partitionwise grouping or aggregation, which allows grouping or aggregation on a partitioned tables performed separately for each partition. |
enable_partitionwise_join |
11, 12, 13, 14, 15, 16, 17 | Enables or disables the query planner's use of partitionwise join, which allows a join between partitioned tables to be performed by joining the matching partitions. |
enable_seqscan |
11, 12, 13, 14, 15, 16, 17 | Enables the planner's use of sequential-scan plans. |
enable_sort |
11, 12, 13, 14, 15, 16, 17 | Enables the planner's use of explicit sort steps. |
enable_tidscan |
11, 12, 13, 14, 15, 16, 17 | Enables the planner's use of TID scan plans. |
Replication / Master Server
Name | Versions | Description |
---|---|---|
synchronous_standby_names |
11, 12, 13, 14, 15, 16, 17 | Number of synchronous standbys and list of names of potential synchronous ones. |
Replication / Primary Server
Name | Versions | Description |
---|---|---|
synchronous_standby_names |
11, 12, 13, 14, 15, 16, 17 | Number of synchronous standbys and list of names of potential synchronous ones. |
vacuum_defer_cleanup_age |
11, 12, 13, 14, 15 | Specifies the number of transactions by which VACUUM and HOT updates will defer cleanup of dead row versions. |
Replication / Sending Servers
Name | Versions | Description |
---|---|---|
max_replication_slots |
11, 12, 13, 14, 15, 16, 17 | Specifies the maximum number of replication slots that the server can support. |
max_slot_wal_keep_size |
13, 14, 15, 16, 17 | Sets the maximum WAL size that can be reserved by replication slots. |
max_wal_senders |
11, 12, 13, 14, 15, 16, 17 | Sets the maximum number of simultaneously running WAL sender processes. |
track_commit_timestamp |
11, 12, 13, 14, 15, 16, 17 | Collects transaction commit time. |
wal_keep_segments |
11, 12 | Sets the number of WAL files held for standby servers. |
wal_keep_size |
13, 14, 15, 16, 17 | Sets the size of WAL files held for standby servers. |
wal_sender_timeout |
11, 12, 13, 14, 15, 16, 17 | Sets the maximum time to wait for WAL replication. |
Replication / Standby Servers
Name | Versions | Description |
---|---|---|
hot_standby |
11, 12, 13, 14, 15, 16, 17 | Allows connections and queries during recovery. |
hot_standby_feedback |
11, 12, 13, 14, 15, 16, 17 | Enables/disables the option whether hot standby needs to send feedback to the primary or upstream standby about queries currently executing on the standby. |
max_standby_archive_delay |
11, 12, 13, 14, 15, 16, 17 | Sets the maximum delay before canceling queries that conflict when a hot standby server is processing archived WAL data. |
max_standby_streaming_delay |
11, 12, 13, 14, 15, 16, 17 | Sets the maximum delay before canceling queries that conflict when a hot standby server is processing streamed WAL data. |
primary_conninfo |
12, 13, 14, 15, 16, 17 | Sets the connection string to be used to connect to the sending server. |
primary_slot_name |
12, 13, 14, 15, 16, 17 | Sets the name of the replication slot to use on the sending server. |
promote_trigger_file |
12, 13, 14, 15 | Specifies a file name whose presence ends recovery in the standby. |
recovery_min_apply_delay |
12, 13, 14, 15, 16, 17 | Sets the minimum delay for applying changes during recovery. |
wal_receiver_create_temp_slot |
13, 14, 15, 16, 17 | Sets whether a WAL receiver should create a temporary replication slot if no permanent slot is configured. |
wal_receiver_status_interval |
11, 12, 13, 14, 15, 16, 17 | Sets the maximum interval between WAL receiver status reports to the primary. |
wal_receiver_timeout |
11, 12, 13, 14, 15, 16, 17 | Sets the maximum wait time to receive data from the sending server. |
wal_retrieve_retry_interval |
11, 12, 13, 14, 15, 16, 17 | Sets the time to wait before retrying to retrieve WAL after a failed attempt. |
Replication / Subscribers
Name | Versions | Description |
---|---|---|
max_logical_replication_workers |
11, 12, 13, 14, 15, 16, 17 | Specifies maximum number of logical replication workers. This includes both apply workers and table synchronization workers. |
max_parallel_apply_workers_per_subscription |
16, 17 | Sets the maximum number of parallel apply workers that can be used per subscription in logical replication. |
max_sync_workers_per_subscription |
11, 12, 13, 14, 15, 16, 17 | Maximum number of table synchronization workers per subscription. |
Reporting and Logging / What to Log
Name | Versions | Description |
---|---|---|
application_name |
11, 12, 13, 14, 15, 16, 17 | Sets the application name to be reported in statistics and logs. |
debug_pretty_print |
11, 12, 13, 14, 15, 16, 17 | Indents parse and plan tree displays. |
debug_print_parse |
11, 12, 13, 14, 15, 16, 17 | Logs each query's parse tree. |
debug_print_plan |
11, 12, 13, 14, 15, 16, 17 | Logs each query's execution plan. |
debug_print_rewritten |
11, 12, 13, 14, 15, 16, 17 | Logs each query's rewritten parse tree. |
log_autovacuum_min_duration |
11, 12, 13, 14, 15, 16, 17 | Sets the minimum execution time above which autovacuum actions will be logged. |
log_checkpoints |
11, 12, 13, 14, 15, 16, 17 | Logs each checkpoint. |
log_connections |
11, 12, 13, 14, 15, 16, 17 | Causes each attempted connection to the server to be logged, as well as successful completion of client authentication. |
log_disconnections |
11, 12, 13, 14, 15, 16, 17 | Logs end of a session, including duration. |
log_duration |
11, 12, 13, 14, 15, 16, 17 | Logs the duration of each completed SQL statement. |
log_error_verbosity |
11, 12, 13, 14, 15, 16, 17 | Controls the amount of detail written in the server log for each message that is logged. |
log_hostname |
11, 12, 13, 14, 15, 16, 17 | Logs the host name in the connection logs. |
log_line_prefix |
11, 12, 13, 14, 15, 16, 17 | Sets the printf-style string that is output at the beginning of each log line. |
log_lock_waits |
11, 12, 13, 14, 15, 16, 17 | Controls whether a log message is produced when a session waits longer than deadlock_timeout to acquire a lock. |
log_parameter_max_length |
13, 14, 15, 16, 17 | Sets the maximum length in bytes of data logged for bind parameter values when logging statements. |
log_parameter_max_length_on_error |
13, 14, 15, 16, 17 | Sets the maximum length in bytes of data logged for bind parameter values when logging statements, on error. |
log_recovery_conflict_waits |
14, 15, 16, 17 | Controls whether a log message is produced when the startup process waits longer than deadlock_timeout for recovery conflicts. |
log_replication_commands |
11, 12, 13, 14, 15, 16, 17 | Logs each replication command. |
log_statement |
11, 12, 13, 14, 15, 16, 17 | Sets the type of statements logged. |
log_temp_files |
11, 12, 13, 14, 15, 16, 17 | Logs the use of temporary files larger than this number of kilobytes. |
log_timezone |
11, 12, 13, 14, 15, 16, 17 | Sets the time zone to use in log messages. |
Reporting and Logging / When to Log
Name | Versions | Description |
---|---|---|
log_min_duration_sample |
13, 14, 15, 16, 17 | Sets the minimum execution time above which a sample of statements will be logged. Sampling is determined by log_statement_sample_rate. |
log_min_duration_statement |
11, 12, 13, 14, 15, 16, 17 | Sets the minimum execution time (in milliseconds) above which statements will be logged. -1 disables logging statement durations. |
log_min_error_statement |
11, 12, 13, 14, 15, 16, 17 | Causes all statements generating error at or above this level to be logged. |
log_min_messages |
11, 12, 13, 14, 15, 16, 17 | Controls which message levels are written to the server log. |
log_startup_progress_interval |
15, 16, 17 | Time between progress updates for long-running startup operations. |
log_statement_sample_rate |
13, 14, 15, 16, 17 | Fraction of statements exceeding log_min_duration_sample to be logged. |
log_transaction_sample_rate |
12, 13, 14, 15, 16, 17 | Sets the fraction of transactions from which to log all statements. |
Reporting and Logging / Where to Log
Name | Versions | Description |
---|---|---|
event_source |
11, 12, 13, 14, 15, 16, 17 | Sets the application name used to identify PostgreSQL messages in the event log. |
log_destination |
11, 12, 13, 14, 15, 16, 17 | Sets the destination for server log output. |
log_directory |
11, 12, 13, 14, 15, 16, 17 | Sets the destination directory for log files. |
log_file_mode |
11, 12, 13, 14, 15, 16, 17 | Sets the file permissions for log files. |
log_filename |
11, 12, 13, 14, 15, 16, 17 | Sets the file name pattern for log files. |
logging_collector |
11, 12, 13, 14, 15, 16, 17 | Start a subprocess to capture stderr output and/or csvlogs into log files. |
log_rotation_age |
11, 12, 13, 14, 15, 16, 17 | Sets the amount of time to wait before forcing log file rotation. |
log_rotation_size |
11, 12, 13, 14, 15, 16, 17 | Sets the maximum size a log file can reach before being rotated. |
log_truncate_on_rotation |
11, 12, 13, 14, 15, 16, 17 | Truncate existing log files of same name during log rotation. |
syslog_facility |
11, 12, 13, 14, 15, 16, 17 | Sets the syslog "facility" to be used when syslog enabled. |
syslog_ident |
11, 12, 13, 14, 15, 16, 17 | Sets the program name used to identify PostgreSQL messages in syslog. |
syslog_sequence_numbers |
11, 12, 13, 14, 15, 16, 17 | Add sequence number to syslog messages to avoid duplicate suppression. |
syslog_split_messages |
11, 12, 13, 14, 15, 16, 17 | Split messages sent to syslog by lines and to fit into 1024 bytes. |
Resource Usage / Asynchronous Behavior
Name | Versions | Description |
---|---|---|
backend_flush_after |
11, 12, 13, 14, 15, 16, 17 | Number of pages after which previously performed writes are flushed to disk. |
effective_io_concurrency |
11, 12, 13, 14, 15, 16, 17 | Sets the number of concurrent disk I/O operations that PostgreSQL expects can be executed simultaneously. |
maintenance_io_concurrency |
13, 14, 15, 16, 17 | A variant of effective_io_concurrency that is used for maintenance work. |
max_parallel_maintenance_workers |
11, 12, 13, 14, 15, 16, 17 | Sets the maximum number of parallel processes per maintenance operation. |
max_parallel_workers |
11, 12, 13, 14, 15, 16, 17 | Sets the maximum number of workers than can be supported for parallel operations. |
max_parallel_workers_per_gather |
11, 12, 13, 14, 15, 16, 17 | Sets the maximum number of parallel processes per executor node. |
max_worker_processes |
11, 12, 13, 14, 15, 16, 17 | Sets the maximum number of background processes that the system can support. |
parallel_leader_participation |
11, 12, 13, 14, 15, 16, 17 | Controls whether Gather and Gather Merge also run subplans. |
Resource Usage / Background Writer
Name | Versions | Description |
---|---|---|
bgwriter_delay |
11, 12, 13, 14, 15, 16, 17 | Specifies the delay between activity rounds for the background writer. In each round the writer issues writes for some number of dirty buffers. |
bgwriter_flush_after |
11, 12, 13, 14, 15, 16, 17 | Number of pages after which previously performed writes by the background writer are flushed to disk. |
bgwriter_lru_maxpages |
11, 12, 13, 14, 15, 16, 17 | In each round, no more than this many buffers will be written by the background writer. |
bgwriter_lru_multiplier |
11, 12, 13, 14, 15, 16, 17 | The average recent need of buffers is multiplied by bgwriter_lru_multiplier to arrive at an estimate of the number of buffers that will be needed during the next round. |
Resource Usage / Cost-Based Vacuum Delay
Name | Versions | Description |
---|---|---|
vacuum_cost_delay |
11, 12, 13, 14, 15, 16, 17 | the amount of time (in milliseconds) that the vacuum process will sleep when the cost limit has been exceeded. |
vacuum_cost_limit |
11, 12, 13, 14, 15, 16, 17 | The accumulated cost that will cause the vacuuming process to sleep. |
vacuum_cost_page_dirty |
11, 12, 13, 14, 15, 16, 17 | The estimated cost charged when vacuum modifies a block that was previously clean. |
vacuum_cost_page_hit |
11, 12, 13, 14, 15, 16, 17 | The estimated cost for vacuuming a buffer found in the shared buffer cache. |
vacuum_cost_page_miss |
11, 12, 13, 14, 15, 16, 17 | The estimated cost for vacuuming a buffer that must be read from disk. |
Resource Usage / Disk
Name | Versions | Description |
---|---|---|
temp_file_limit |
11, 12, 13, 14, 15, 16, 17 | Limits the total size of all temporary files used by each process. |
Resource Usage / Kernel Resources
Name | Versions | Description |
---|---|---|
max_files_per_process |
11, 12, 13, 14, 15, 16, 17 | Sets the maximum number of simultaneously open files for each server process. |
Resource Usage / Memory
Name | Versions | Description |
---|---|---|
autovacuum_work_mem |
11, 12, 13, 14, 15, 16, 17 | Sets the maximum memory to be used by each autovacuum worker process. |
dynamic_shared_memory_type |
11, 12, 13, 14, 15, 16, 17 | Selects the dynamic shared memory implementation used. |
hash_mem_multiplier |
12, 13, 14, 15, 16, 17 | Multiple of work_mem to use for hash tables. |
huge_pages |
11, 12, 13, 14, 15, 16, 17 | Enables/disables the use of huge memory pages. This setting is not applicable to servers having less than 4 vCores. |
huge_page_size |
14, 15, 16, 17 | The size of huge page that should be requested. |
logical_decoding_work_mem |
13, 14, 15, 16, 17 | Sets the maximum memory to be used for logical decoding. |
maintenance_work_mem |
11, 12, 13, 14, 15, 16, 17 | Sets the maximum memory to be used for maintenance operations such as VACUUM, Create Index. |
max_prepared_transactions |
11, 12, 13, 14, 15, 16, 17 | Sets the maximum number of simultaneously prepared transactions. When running a replica server, you must set this parameter to the same or higher value than on the primary server. |
max_stack_depth |
11, 12, 13, 14, 15, 16, 17 | Sets the maximum stack depth, in kilobytes. |
min_dynamic_shared_memory |
14, 15, 16, 17 | Amount of dynamic shared memory reserved at startup. |
shared_buffers |
11, 12, 13, 14, 15, 16, 17 | Sets the number of shared memory buffers used by the server. Unit is 8kb. Allowed values are inside the range of 10% - 75% of available memory. |
shared_memory_type |
12, 13, 14, 15, 16, 17 | Selects the shared memory implementation used for the main shared memory region. |
temp_buffers |
11, 12, 13, 14, 15, 16, 17 | Sets the maximum number of temporary buffers used by each database session. |
vacuum_buffer_usage_limit |
16, 17 | Sets the buffer pool size for VACUUM, ANALYZE, and autovacuum. |
work_mem |
11, 12, 13, 14, 15, 16, 17 | Sets the amount of memory to be used by internal sort operations and hash tables before writing to temporary disk files. |
Statistics / Cumulative Query and Index Statistics
Name | Versions | Description |
---|---|---|
stats_fetch_consistency |
15, 16, 17 | Sets the consistency of accesses to statistics data. |
track_activities |
11, 12, 13, 14, 15, 16, 17 | Collects information about executing commands for each session. |
track_activity_query_size |
11, 12, 13, 14, 15, 16, 17 | Sets the amount of memory reserved for pg_stat_activity.query, in bytes. |
track_counts |
11, 12, 13, 14, 15, 16, 17 | Enables collection of statistics on database activity |
track_functions |
11, 12, 13, 14, 15, 16, 17 | Enables tracking of function call counts and time used. |
track_io_timing |
11, 12, 13, 14, 15, 16, 17 | Enables timing of database I/O calls. |
track_wal_io_timing |
14, 15, 16, 17 | Collects timing statistics for WAL I/O activity. |
Statistics / Monitoring
Name | Versions | Description |
---|---|---|
compute_query_id |
14, 15, 16, 17 | Enables in-core computation of query identifiers. |
log_executor_stats |
11, 12, 13, 14, 15, 16, 17 | Writes executor performance statistics to the server log. |
log_parser_stats |
11, 12, 13, 14, 15, 16, 17 | Writes parser performance statistics to the server log. |
log_planner_stats |
11, 12, 13, 14, 15, 16, 17 | Writes planner performance statistics to the server log. |
log_statement_stats |
11, 12, 13, 14, 15, 16, 17 | For each query, writes cumulative performance statistics to the server log. |
Statistics / Query and Index Statistics Collector
Name | Versions | Description |
---|---|---|
stats_temp_directory |
11, 12, 13, 14 | Writes temporary statistics files to the specified directory. |
TLS
Name | Versions | Description |
---|---|---|
require_secure_transport |
11, 12, 13, 14, 15, 16, 17 | Whether client connections to the server are required to use some form of secure transport. |
Version and Platform Compatibility / Other Platforms and Clients
Name | Versions | Description |
---|---|---|
transform_null_equals |
11, 12, 13, 14, 15, 16, 17 | When on, expressions of the form expr = NULL (or NULL = expr) are treated as expr IS NULL, that is, they return true if expr evaluates to the null value, and false otherwise. |
Version and Platform Compatibility / Previous PostgreSQL Versions
Name | Versions | Description |
---|---|---|
array_nulls |
11, 12, 13, 14, 15, 16, 17 | Enables input of NULL (case insensitive) to be considered as NULL value rather than the literal String 'NULL'. |
backslash_quote |
11, 12, 13, 14, 15, 16, 17 | Sets whether "\'" is allowed in string literals. |
escape_string_warning |
11, 12, 13, 14, 15, 16, 17 | Warns about backslash escapes in ordinary string literals. |
lo_compat_privileges |
11, 12, 13, 14, 15, 16, 17 | Enables backward compatibility mode for privilege checks on large objects. |
operator_precedence_warning |
11, 12 | Emits a warning for constructs that changed meaning since PostgreSQL 9.4. |
quote_all_identifiers |
11, 12, 13, 14, 15, 16, 17 | When generating SQL fragments, quotes all identifiers. |
standard_conforming_strings |
11, 12, 13, 14, 15, 16, 17 | Causes '...' strings to treat backslashes literally. |
synchronize_seqscans |
11, 12, 13, 14, 15, 16, 17 | Enables synchronized sequential scans. |
Write-Ahead Log / Archive Recovery
Name | Versions | Description |
---|---|---|
archive_cleanup_command |
12, 13, 14, 15, 16, 17 | Sets the shell command that will be executed at every restart point. |
recovery_end_command |
12, 13, 14, 15, 16, 17 | Sets the shell command that will be executed once at the end of recovery. |
restore_command |
12, 13, 14, 15, 16, 17 | Sets the shell command that will be called to retrieve an archived WAL file. |
Write-Ahead Log / Archiving
Name | Versions | Description |
---|---|---|
archive_command |
11, 12, 13, 14, 15, 16, 17 | Sets the shell command that will be called to archive a WAL file. |
archive_library |
15, 16, 17 | Sets the library that will be called to archive a WAL file. |
archive_mode |
11, 12, 13, 14, 15, 16, 17 | Allows archiving of WAL files using archive_command. |
archive_timeout |
11, 12, 13, 14, 15, 16, 17 | Forces a switch to the next WAL file if a new file has not been started within N seconds. |
Write-Ahead Log / Checkpoints
Name | Versions | Description |
---|---|---|
checkpoint_completion_target |
11, 12, 13, 14, 15, 16, 17 | Specifies the target of checkpoint completion, as a fraction of total time between checkpoints. |
checkpoint_flush_after |
11, 12, 13, 14, 15, 16, 17 | Number of pages after which previously performed writes are flushed to disk. |
checkpoint_timeout |
11, 12, 13, 14, 15, 16, 17 | Maximum time between automatic WAL checkpoints, in seconds. The valid range is between 30 seconds and one day. |
checkpoint_warning |
11, 12, 13, 14, 15, 16, 17 | Writes a warning message if checkpoints caused by the filling of WAL segment more frequently than this. |
max_wal_size |
11, 12, 13, 14, 15, 16, 17 | Maximum size to let the WAL grow before triggering automatic checkpoint. |
min_wal_size |
11, 12, 13, 14, 15, 16, 17 | Sets the minimum size to shrink the WAL to. |
Write-Ahead Log / Recovery
Name | Versions | Description |
---|---|---|
recovery_prefetch |
15, 16, 17 | Prefetch referenced blocks during recovery. |
wal_decode_buffer_size |
15, 16, 17 | Buffer size for reading ahead in the WAL during recovery. |
Write-Ahead Log / Recovery Target
Name | Versions | Description |
---|---|---|
recovery_target |
12, 13, 14, 15, 16, 17 | Set to "immediate" to end recovery as soon as a consistent state is reached. |
recovery_target_action |
12, 13, 14, 15, 16, 17 | Sets the action to perform upon reaching the recovery target. |
recovery_target_inclusive |
12, 13, 14, 15, 16, 17 | Sets whether to include or exclude transaction with recovery target. |
recovery_target_lsn |
12, 13, 14, 15, 16, 17 | Sets the LSN of the write-ahead log location up to which recovery will proceed. |
recovery_target_name |
12, 13, 14, 15, 16, 17 | Sets the named restore point up to which recovery will proceed. |
recovery_target_time |
12, 13, 14, 15, 16, 17 | Sets the time stamp up to which recovery will proceed. |
recovery_target_timeline |
12, 13, 14, 15, 16, 17 | Specifies the timeline to recover into. |
recovery_target_xid |
12, 13, 14, 15, 16, 17 | Sets the transaction ID up to which recovery will proceed. |
Write-Ahead Log / Settings
Name | Versions | Description |
---|---|---|
commit_delay |
11, 12, 13, 14, 15, 16, 17 | Sets the delay in microseconds between transaction commit and flushing WAL to disk. |
commit_siblings |
11, 12, 13, 14, 15, 16, 17 | Sets the minimum concurrent open transactions before performing commit_delay. |
fsync |
11, 12, 13, 14, 15, 16, 17 | Forces synchronization of updates to disk. |
full_page_writes |
11, 12, 13, 14, 15, 16, 17 | Writes full pages to WAL when first modified after a checkpoint. |
synchronous_commit |
11, 12, 13, 14, 15, 16, 17 | Sets the current transaction's synchronization level. |
wal_buffers |
11, 12, 13, 14, 15, 16, 17 | Sets the number of disk-page buffers in shared memory for WAL. Unit is 8kb. |
wal_compression |
11, 12, 13, 14, 15, 16, 17 | Compresses full-page writes written in WAL file. |
wal_init_zero |
12, 13, 14, 15, 16, 17 | Writes zeroes to new WAL files before first use. |
wal_level |
11, 12, 13, 14, 15, 16, 17 | It determines how much information is written to the WAL. |
wal_log_hints |
11, 12, 13, 14, 15, 16, 17 | Writes full pages to WAL when first modified after a checkpoint, even for a non-critical modification. |
wal_recycle |
12, 13, 14, 15, 16, 17 | Recycles WAL files by renaming them. |
wal_skip_threshold |
13, 14, 15, 16, 17 | Minimum size of new file to fsync instead of writing WAL. |
wal_sync_method |
11, 12, 13, 14, 15, 16, 17 | Selects the method used for forcing WAL updates to disk. |
wal_writer_delay |
11, 12, 13, 14, 15, 16, 17 | Time interval between WAL flushes performed by the WAL writer. |
wal_writer_flush_after |
11, 12, 13, 14, 15, 16, 17 | Amount of WAL written out by WAL writer that triggers a flush. |
Next steps
For information on supported PostgreSQL extensions, see PostgreSQL extensions in Azure Database for PostgreSQL - Flexible Server.