Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
Azure Database for PostgreSQL provides a set of configurable parameters for each server.
These parameters can correspond to:
- Database engine: parameters defined by the PostgreSQL database engine or by binary libraries that implement functionality of extensions. Some examples of database engine built-in parameters are
autovacuum_max_workers,DateStyle,client_min_messages,password_encryption,max_connections,geqo,from_collapse_limit,cpu_tuple_cost,cpu_tuple_cost,max_standby_streaming_delay,log_connections,log_min_duration_statement,max_parallel_workers,bgwriter_delay, andshared_buffers. Some examples of parameters defined by extensions arepg_qs.max_query_text_length(pg_qs extension, implementing functionality for query store),pg_stat_statements.max(pg_stat_statements extension),pgaudit.log_catalog(pgaudit extension), andcron.database_name(cron extension). - Non-database engine: parameters that control some built-in functionality, which is core to the Azure Database for PostgreSQL service, but is not part of the database engine or any of its extensions. Some examples of these are
metrics.collector_database_activity(controls whether or not the service should collect the list of metrics which are considered enhanced metrics and aren't collected by default),pgbouncer.enabled(allows the user to activate the instance of PgBouncer which is built into the service),index_tuning.analysis_interval(sets the frequency at which automatic index tuning should wake up to produce recommendations)
Customize parameters
Both, database engine and non-database engine parameters can be configured at the server level. For more information, see set the value of one or more server parameters.
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.
Database engine parameters can also be configured at more granular scopes. These adjustments override globally set values. Their scope and duration depend on the level at which you make them:
Database level: Use the
ALTER DATABASEcommand for database-specific configurations.Role or user level: Use the
ALTER USERcommand for user-centric settings.Function, procedure level: When you're defining a function or procedure, you can specify or alter the configuration parameters that are used when the function is called.
Table level: As an example, you can modify parameters related to autovacuum at this level.
Session level: For the life of an individual database session, you can adjust specific parameters. PostgreSQL facilitates this adjustment with the following SQL commands:
- Use the
SETcommand to make session-specific adjustments. These changes serve as the default settings during the current session. Access to these changes might require specificSETprivileges, 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
SHOWcommand to examine existing parameter settings. Its SQL function equivalent iscurrent_setting(setting_name text).
- Use the
Work with time zone parameters
If you plan to work with date and time data in PostgreSQL, make sure that you set the correct time zone for your location. All timezone-aware dates and times are stored internally in PostgreSQL in UTC. They're converted to local time in the zone specified by the TimeZone server parameter before being displayed to the client. This parameter can be edited on Server parameters page. PostgreSQL allows you to specify time zones in three different forms:
A full time zone name, for example America/New_York. The recognized time zone names are listed in the pg_timezone_names view.
Example to query this view in psql and get list of time zone names:select name FROM pg_timezone_names LIMIT 20;
You should see result set like:
name ----------------------- GMT0 Iceland Factory NZ-CHAT America/Panama America/Fort_Nelson America/Pangnirtung America/Belem America/Coral_Harbour America/Guayaquil America/Marigot America/Barbados America/Porto_Velho America/Bogota America/Menominee America/Martinique America/Asuncion America/Toronto America/Tortola America/Managua (20 rows)A time zone abbreviation, for example PST. Such a specification merely defines a particular offset from UTC, in contrast to full time zone names which can imply a set of daylight savings transition-date rules as well. The recognized abbreviations are listed in the pg_timezone_abbrevs view Example to query this view in psql and get list of time zone abbreviations:
select abbrev from pg_timezone_abbrevs limit 20;
You should see result set like:
abbrev| ------+ ACDT | ACSST | ACST | ACT | ACWST | ADT | AEDT | AESST | AEST | AFT | AKDT | AKST | ALMST | ALMT | AMST | AMT | ANAST | ANAT | ARST | ART |In addition to the timezone names and abbreviations PostgreSQL accepts POSIX-style time zone specifications of the form STDoffset or STDoffsetDST. STD is a zone abbreviation. Offset is a numeric offset in hours west from UTC. DST is an optional daylight-savings zone abbreviation, assumed to stand for one hour ahead of the given offset.
Supported server parameters
Autovacuum
| Name | Versions | Description |
|---|---|---|
autovacuum |
11, 12, 13, 14, 15, 16, 17, 18 | Starts the autovacuum subprocess. |
autovacuum_analyze_scale_factor |
11, 12, 13, 14, 15, 16, 17, 18 | Number of tuple inserts, updates, or deletes prior to analyze as a fraction of reltuples. |
autovacuum_analyze_threshold |
11, 12, 13, 14, 15, 16, 17, 18 | Minimum number of tuple inserts, updates, or deletes prior to analyze. |
autovacuum_freeze_max_age |
11, 12, 13, 14, 15, 16, 17, 18 | Age at which to autovacuum a table to prevent transaction ID wraparound. |
autovacuum_max_workers |
11, 12, 13, 14, 15, 16, 17, 18 | Sets the maximum number of simultaneously running autovacuum worker processes. |
autovacuum_multixact_freeze_max_age |
11, 12, 13, 14, 15, 16, 17, 18 | Multixact age at which to autovacuum a table to prevent multixact wraparound. |
autovacuum_naptime |
11, 12, 13, 14, 15, 16, 17, 18 | Time to sleep between autovacuum runs. |
autovacuum_vacuum_cost_delay |
11, 12, 13, 14, 15, 16, 17, 18 | Vacuum cost delay in milliseconds, for autovacuum. |
autovacuum_vacuum_cost_limit |
11, 12, 13, 14, 15, 16, 17, 18 | Vacuum cost amount available before napping, for autovacuum. |
autovacuum_vacuum_insert_scale_factor |
13, 14, 15, 16, 17, 18 | Number of tuple inserts prior to vacuum as a fraction of reltuples. |
autovacuum_vacuum_insert_threshold |
13, 14, 15, 16, 17, 18 | Minimum number of tuple inserts prior to vacuum, or -1 to disable insert vacuums. |
autovacuum_vacuum_max_threshold |
18 | Maximum number of tuple updates or deletes prior to vacuum. -1 disables the maximum threshold. |
autovacuum_vacuum_scale_factor |
11, 12, 13, 14, 15, 16, 17, 18 | Number of tuple updates or deletes prior to vacuum as a fraction of reltuples. |
autovacuum_vacuum_threshold |
11, 12, 13, 14, 15, 16, 17, 18 | Minimum number of tuple updates or deletes prior to vacuum. |
autovacuum_worker_slots |
18 | Sets the number of backend slots to allocate for autovacuum workers. |
vacuum_max_eager_freeze_failure_rate |
18 | Fraction of pages in a relation vacuum can scan and fail to freeze before disabling eager scanning. A value of 0.0 disables eager scanning and a value of 1.0 will eagerly scan up to 100 percent of the all-visible pages in the relation. If vacuum successfully freezes these pages, the cap is lower than 100 percent, because the goal is to amortize page freezing across multiple vacuums. |
vacuum_truncate |
18 | Enables vacuum to truncate empty pages at the end of the table. |
Client Connection Defaults / Locale and Formatting
| Name | Versions | Description |
|---|---|---|
client_encoding |
11, 12, 13, 14, 15, 16, 17, 18 | Sets the client's character set encoding. |
DateStyle |
11, 12, 13, 14, 15, 16, 17, 18 | Sets the display format for date and time values. Also controls interpretation of ambiguous date inputs. |
default_text_search_config |
11, 12, 13, 14, 15, 16, 17, 18 | Sets default text search configuration. |
extra_float_digits |
11, 12, 13, 14, 15, 16, 17, 18 | Sets the number of digits displayed for floating-point values. This affects real, double precision, and geometric data types. A zero or negative parameter value is added to the standard number of digits (FLT_DIG or DBL_DIG as appropriate). Any value greater than zero selects precise output mode. |
icu_validation_level |
16, 17, 18 | Log level for reporting invalid ICU locale strings. |
IntervalStyle |
11, 12, 13, 14, 15, 16, 17, 18 | Sets the display format for interval values. |
lc_messages |
11, 12, 13, 14, 15, 16, 17, 18 | Sets the language in which messages are displayed. |
lc_monetary |
11, 12, 13, 14, 15, 16, 17, 18 | Sets the locale for formatting monetary amounts. |
lc_numeric |
11, 12, 13, 14, 15, 16, 17, 18 | Sets the locale for formatting numbers. |
lc_time |
11, 12, 13, 14, 15, 16, 17, 18 | Sets the locale for formatting date and time values. |
TimeZone |
11, 12, 13, 14, 15, 16, 17, 18 | Sets the time zone for displaying and interpreting time stamps. |
timezone_abbreviations |
11, 12, 13, 14, 15, 16, 17, 18 | 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, 18 | Sets the path for dynamically loadable modules. If a dynamically loadable module needs to be opened and the specified name does not have a directory component (i.e., the name does not contain a slash), the system will search this path for the specified file. |
gin_fuzzy_search_limit |
11, 12, 13, 14, 15, 16, 17, 18 | 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, 18 | JIT provider to use. |
local_preload_libraries |
11, 12, 13, 14, 15, 16, 17, 18 | Lists unprivileged shared libraries to preload into each backend. |
session_preload_libraries |
11, 12, 13, 14, 15, 16, 17, 18 | Lists shared libraries to preload into each backend. |
shared_preload_libraries |
11, 12, 13, 14, 15, 16, 17, 18 | Lists shared libraries to preload into server. |
Client Connection Defaults / Statement Behavior
| Name | Versions | Description |
|---|---|---|
bytea_output |
11, 12, 13, 14, 15, 16, 17, 18 | Sets the output format for bytea. |
check_function_bodies |
11, 12, 13, 14, 15, 16, 17, 18 | Check routine bodies during CREATE FUNCTION and CREATE PROCEDURE. |
client_min_messages |
11, 12, 13, 14, 15, 16, 17, 18 | Sets the message levels that are sent to the client. Each level includes all the levels that follow it. The later the level, the fewer messages are sent. |
createrole_self_grant |
16, 17, 18 | Sets whether a CREATEROLE user automatically grants the role to themselves, and with which options. |
default_table_access_method |
12, 13, 14, 15, 16, 17, 18 | Sets the default table access method for new tables. |
default_tablespace |
11, 12, 13, 14, 15, 16, 17, 18 | Sets the default tablespace to create tables and indexes in. An empty string selects the database's default tablespace. |
default_toast_compression |
14, 15, 16, 17, 18 | Sets the default compression method for compressible values. |
default_transaction_deferrable |
11, 12, 13, 14, 15, 16, 17, 18 | Sets the default deferrable status of new transactions. |
default_transaction_isolation |
11, 12, 13, 14, 15, 16, 17, 18 | Sets the transaction isolation level of each new transaction. |
default_transaction_read_only |
11, 12, 13, 14, 15, 16, 17, 18 | Sets the default read-only status of new transactions. |
event_triggers |
17, 18 | Enables event triggers. When enabled, event triggers will fire for all applicable statements. |
extension_control_path |
18 | A path to search for extensions, specifically extension control files (name.control). |
gin_pending_list_limit |
11, 12, 13, 14, 15, 16, 17, 18 | Sets the maximum size of the pending list for GIN index. |
idle_in_transaction_session_timeout |
11, 12, 13, 14, 15, 16, 17, 18 | Sets the maximum allowed idle time between queries, when in a transaction. A value of 0 turns off the timeout. |
idle_session_timeout |
14, 15, 16, 17, 18 | Sets the maximum allowed idle time between queries, when not in a transaction. A value of 0 turns off the timeout. |
lock_timeout |
11, 12, 13, 14, 15, 16, 17, 18 | Sets the maximum allowed duration of any wait for a lock. A value of 0 turns off the timeout. |
restrict_nonsystem_relation_kind |
12, 16, 17, 18 | Prohibits access to non-system relations of specified kinds. |
row_security |
11, 12, 13, 14, 15, 16, 17, 18 | Enable row security. When enabled, row security will be applied to all users. |
search_path |
11, 12, 13, 14, 15, 16, 17, 18 | Sets the schema search order for names that are not schema-qualified. |
session_replication_role |
11, 12, 13, 14, 15, 16, 17, 18 | Sets the session's behavior for triggers and rewrite rules. |
statement_timeout |
11, 12, 13, 14, 15, 16, 17, 18 | Sets the maximum allowed duration of any statement. A value of 0 turns off the timeout. |
temp_tablespaces |
11, 12, 13, 14, 15, 16, 17, 18 | Sets the tablespace(s) to use for temporary tables and sort files. |
transaction_deferrable |
11, 12, 13, 14, 15, 16, 17, 18 | 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, 18 | Sets the current transaction's isolation level. |
transaction_read_only |
11, 12, 13, 14, 15, 16, 17, 18 | Sets the current transaction's read-only status. |
transaction_timeout |
17, 18 | Sets the maximum allowed duration of any transaction within a session (not a prepared transaction). A value of 0 turns off the timeout. |
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, 18 | Age at which VACUUM should trigger failsafe to avoid a wraparound outage. |
vacuum_freeze_min_age |
11, 12, 13, 14, 15, 16, 17, 18 | Minimum age at which VACUUM should freeze a table row. |
vacuum_freeze_table_age |
11, 12, 13, 14, 15, 16, 17, 18 | Age at which VACUUM should scan whole table to freeze tuples. |
vacuum_multixact_failsafe_age |
14, 15, 16, 17, 18 | 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, 18 | Minimum age at which VACUUM should freeze a MultiXactId in a table row. |
vacuum_multixact_freeze_table_age |
11, 12, 13, 14, 15, 16, 17, 18 | Multixact age at which VACUUM should scan whole table to freeze tuples. |
xmlbinary |
11, 12, 13, 14, 15, 16, 17, 18 | Sets how binary values are to be encoded in XML. |
xmloption |
11, 12, 13, 14, 15, 16, 17, 18 | 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, 18 | Sets the maximum allowed time to complete client authentication. |
db_user_namespace |
11, 12, 13, 14, 15, 16, 17 | Enables per-database user names. |
gss_accept_delegation |
16, 17, 18 | Sets whether GSSAPI delegation should be accepted from the client. |
krb_caseins_users |
11, 12, 13, 14, 15, 16, 17, 18 | Sets whether Kerberos and GSSAPI user names should be treated as case-insensitive. |
krb_server_keyfile |
11, 12, 13, 14, 15, 16, 17, 18 | Sets the location of the Kerberos server key file. |
password_encryption |
11, 12, 13, 14, 15, 16, 17, 18 | Chooses the algorithm for encrypting passwords. |
scram_iterations |
16, 17, 18 | Sets the iteration count for SCRAM secret generation. |
Connections and Authentication / Connection Settings
| Name | Versions | Description |
|---|---|---|
bonjour |
11, 12, 13, 14, 15, 16, 17, 18 | Enables advertising the server via Bonjour. |
bonjour_name |
11, 12, 13, 14, 15, 16, 17, 18 | Sets the Bonjour service name. |
listen_addresses |
11, 12, 13, 14, 15, 16, 17, 18 | Sets the host name or IP address(es) to listen to. |
max_connections |
11, 12, 13, 14, 15, 16, 17, 18 | Sets the maximum number of concurrent connections. |
oauth_validator_libraries |
18 | Lists libraries that may be called to validate OAuth v2 bearer tokens. |
port |
11, 12, 13, 14, 15, 16, 17, 18 | Sets the TCP port the server listens on. |
reserved_connections |
11, 12, 13, 14, 15, 16, 17, 18 | Sets the number of connection slots reserved for roles with privileges of pg_use_reserved_connections. |
superuser_reserved_connections |
11, 12, 13, 14, 15, 16, 17, 18 | Sets the number of connection slots reserved for superusers. |
unix_socket_directories |
11, 12, 13, 14, 15, 16, 17, 18 | Sets the directories where Unix-domain sockets will be created. |
unix_socket_group |
11, 12, 13, 14, 15, 16, 17, 18 | Sets the owning group of the Unix-domain socket. The owning user of the socket is always the user that starts the server. |
unix_socket_permissions |
11, 12, 13, 14, 15, 16, 17, 18 | Sets the access permissions of the Unix-domain socket. Unix-domain sockets use the usual Unix file system permission set. The parameter value is expected to be a numeric mode specification in the form accepted by the chmod and umask system calls. (To use the customary octal format the number must start with a 0 (zero).). |
Connections and Authentication / SSL
| Name | Versions | Description |
|---|---|---|
ssl |
11, 12, 13, 14, 15, 16, 17, 18 | Enables SSL connections. |
ssl_ca_file |
11, 12, 13, 14, 15, 16, 17, 18 | Location of the SSL certificate authority file. |
ssl_cert_file |
11, 12, 13, 14, 15, 16, 17, 18 | Location of the SSL server certificate file. |
ssl_ciphers |
11, 12, 13, 14, 15, 16, 17, 18 | Sets the list of allowed SSL ciphers. |
ssl_crl_dir |
14, 15, 16, 17, 18 | Location of the SSL certificate revocation list directory. |
ssl_crl_file |
11, 12, 13, 14, 15, 16, 17, 18 | Location of the SSL certificate revocation list file. |
ssl_dh_params_file |
11, 12, 13, 14, 15, 16, 17, 18 | 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, 18 | Location of the SSL server private key file. |
ssl_max_protocol_version |
12, 13, 14, 15, 16, 17, 18 | Sets the maximum SSL/TLS protocol version to use. |
ssl_min_protocol_version |
12, 13, 14, 15, 16, 17, 18 | Sets the minimum SSL/TLS protocol version to use. |
ssl_passphrase_command |
11, 12, 13, 14, 15, 16, 17, 18 | Command to obtain passphrases for SSL. |
ssl_passphrase_command_supports_reload |
11, 12, 13, 14, 15, 16, 17, 18 | Controls whether "ssl_passphrase_command" is called during server reload. |
ssl_prefer_server_ciphers |
11, 12, 13, 14, 15, 16, 17, 18 | Give priority to server ciphersuite order. |
Connections and Authentication / TCP Settings
| Name | Versions | Description |
|---|---|---|
client_connection_check_interval |
14, 15, 16, 17, 18 | Sets the time interval between checks for disconnection while running queries. |
tcp_keepalives_count |
11, 12, 13, 14, 15, 16, 17, 18 | Maximum number of TCP keepalive retransmits. Number of consecutive keepalive retransmits that can be lost before a connection is considered dead. A value of 0 uses the system default. |
tcp_keepalives_idle |
11, 12, 13, 14, 15, 16, 17, 18 | Time between issuing TCP keepalives. A value of 0 uses the system default. |
tcp_keepalives_interval |
11, 12, 13, 14, 15, 16, 17, 18 | Time between TCP keepalive retransmits. A value of 0 uses the system default. |
tcp_user_timeout |
12, 13, 14, 15, 16, 17, 18 | TCP user timeout. A value of 0 uses the system default. |
Customized Options
| Name | Versions | Description |
|---|---|---|
age.enable_containment |
13, 14, 15, 16 | Use @> operator to transform MATCH's filter. Otherwise, use -> operator. |
anon.algorithm |
12, 13, 14, 15, 16, 17, 18 | The hash method used for pseudonymizing functions. |
anon.k_anonymity_provider |
12, 13, 14, 15, 16, 17, 18 | The security label provider used for k-anonymity. |
anon.masking_policies |
12, 13, 14, 15, 16, 17, 18 | Define multiple masking policies (NOT IMPLEMENTED YET). |
anon.maskschema |
12, 13, 14, 15, 16, 17, 18 | The schema where the dynamic masking views are stored. |
anon.privacy_by_default |
12, 13, 14, 15, 16, 17, 18 | Mask all columns with NULL (or the default value for NOT NULL columns). |
anon.restrict_to_trusted_schemas |
12, 13, 14, 15, 16, 17, 18 | Masking filters must be in a trusted schema. Activate this option to prevent non-superuser from using their own masking filters. |
anon.salt |
12, 13, 14, 15, 16, 17, 18 | The salt value used for the pseudonymizing functions. |
anon.sourceschema |
12, 13, 14, 15, 16, 17, 18 | The schema where the table are masked by the dynamic masking engine. |
anon.strict_mode |
12, 13, 14, 15, 16, 17, 18 | A masking rule cannot change a column data type, unless you disable this. Disabling the mode is not recommended. |
anon.transparent_dynamic_masking |
12, 13, 14, 15, 16, 17, 18 | New masking engine (EXPERIMENTAL). |
auto_explain.log_analyze |
11, 12, 13, 14, 15, 16, 17, 18 | Use EXPLAIN ANALYZE for plan logging. |
auto_explain.log_buffers |
11, 12, 13, 14, 15, 16, 17, 18 | Log buffers usage. |
auto_explain.log_format |
11, 12, 13, 14, 15, 16, 17, 18 | EXPLAIN format to be used for plan logging. |
auto_explain.log_level |
12, 13, 14, 15, 16, 17, 18 | Log level for the plan. |
auto_explain.log_min_duration |
11, 12, 13, 14, 15, 16, 17, 18 | 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, 18 | Log nested statements. |
auto_explain.log_parameter_max_length |
16, 17, 18 | Sets the maximum length of query parameters to log. Zero logs no query parameters, -1 logs them in full. |
auto_explain.log_settings |
12, 13, 14, 15, 16, 17, 18 | Log modified configuration parameters affecting query planning. |
auto_explain.log_timing |
11, 12, 13, 14, 15, 16, 17, 18 | Collect timing data, not just row counts. |
auto_explain.log_triggers |
11, 12, 13, 14, 15, 16, 17, 18 | 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, 18 | Use EXPLAIN VERBOSE for plan logging. |
auto_explain.log_wal |
13, 14, 15, 16, 17, 18 | Log WAL usage. |
auto_explain.sample_rate |
11, 12, 13, 14, 15, 16, 17, 18 | Fraction of queries to process. |
azure.accepted_password_auth_method |
11, 12, 13, 14, 15, 16, 17, 18 | Password authentication methods, separated by comma, that are accepted by the server. |
azure.allow_hostname_in_username |
11 | Specifies if hostname is allowed in the username for Azure Database for PostgreSQL Flexible Server. |
azure_cdc.change_batch_buffer_size |
13, 14, 15, 16, 17, 18 | Buffer size, in megabytes, for change batches. These buffers are used to temporarily store CDC changes before they are written to disk. |
azure_cdc.change_batch_export_timeout |
13, 14, 15, 16, 17, 18 | Maximum time, in seconds, to wait before a batch of changes is ready to be exported. |
azure_cdc.max_fabric_mirrors |
13, 14, 15, 16, 17, 18 | Maximum number of parallel fabric mirrors that can be run at the same time. |
azure_cdc.max_snapshot_workers |
13, 14, 15, 16, 17, 18 | Maximum number of workers launched for snapshot export. Each worker exports one table at a time. |
azure_cdc.onelake_buffer_size |
13, 14, 15, 16, 17, 18 | Buffer size, in megabytes, for upload to Onelake. Onelake uploads files in chunks, buffering the data in memory up to this limit. |
azure_cdc.parquet_compression |
13, 14, 15, 16, 17, 18 | Compression algorithm to use for parquet files. Determines the compression algorithm to use for parquet files. Supported values are 'uncompressed', 'snappy', 'gzip', and 'zstd'. |
azure_cdc.snapshot_buffer_size |
13, 14, 15, 16, 17, 18 | Buffer size, in megabytes, for snapshot data files. These buffers are used for writing snapshot data. While this indirectly influences the file size, the actual file size may be smaller due to compression and other factors. |
azure_cdc.snapshot_export_timeout |
13, 14, 15, 16, 17, 18 | Maximum time, in minutes, to wait before reporting an error when exporting a snapshot of a database. |
azure.enable_temp_tablespaces_on_local_ssd |
11, 12, 13, 14, 15, 16, 17, 18 | Stores temporary objects on local Solid State Disk. |
azure.extensions |
11, 12, 13, 14, 15, 16, 17, 18 | List of extensions, separated by comma, that are allowlisted. If an extension is not in this list, trying to execute CREATE, ALTER, COMMENT, DROP EXTENSION statements on that extension fails. |
azure.fabric_mirror_enabled |
13, 14, 15, 16, 17, 18 | Validates prerequisites for Fabric Mirroring to function properly. Validation only occurs at the very moment this setting is changed from 'off' to 'on'. |
credcheck.auth_delay_ms |
18 | Milliseconds to delay before reporting authentication failure. |
credcheck.auth_failure_cache_size |
18 | Maximum of entries in the auth failure cache. |
credcheck.encrypted_password_allowed |
18 | Allow encrypted password to be used or throw an error. |
credcheck.history_max_size |
18 | Maximum of entries in the password history. |
credcheck.max_auth_failure |
18 | Maximum number of authentication failures before the user login account is invalidated. |
credcheck.password_contain |
18 | Password should contain these characters |
credcheck.password_contain_username |
18 | Password contains username |
credcheck.password_ignore_case |
18 | Ignore case while password checking |
credcheck.password_min_digit |
18 | Minimum password digits |
credcheck.password_min_length |
18 | Minimum password length |
credcheck.password_min_lower |
18 | Minimum password lowercase letters |
credcheck.password_min_repeat |
18 | Minimum password characters repeat |
credcheck.password_min_special |
18 | Minimum special characters |
credcheck.password_min_upper |
18 | Minimum password uppercase letters |
credcheck.password_not_contain |
18 | Password should not contain these characters |
credcheck.password_reuse_history |
18 | Minimum number of password changes before permitting reuse |
credcheck.password_reuse_interval |
18 | Minimum number of days elapsed before permitting reuse |
credcheck.password_valid_max |
18 | Force use of VALID UNTIL clause in CREATE ROLE statement with a maximum number of days |
credcheck.password_valid_until |
18 | Force use of VALID UNTIL clause in CREATE ROLE statement with a minimum number of days |
credcheck.reset_superuser |
18 | Restore superuser access when they have been banned. |
credcheck.username_contain |
18 | Username should contain these characters |
credcheck.username_contain_password |
18 | Username contains password |
credcheck.username_ignore_case |
18 | Ignore case while username checking |
credcheck.username_min_digit |
18 | Minimum username digits |
credcheck.username_min_length |
18 | Minimum username length |
credcheck.username_min_lower |
18 | Minimum username lowercase letters |
credcheck.username_min_repeat |
18 | Minimum username characters repeat |
credcheck.username_min_special |
18 | Minimum username special characters |
credcheck.username_min_upper |
18 | Minimum username uppercase letters |
credcheck.username_not_contain |
18 | Username should not contain these characters |
credcheck.whitelist |
18 | Comma separated list of usernames to exclude from password policy check. |
credcheck.whitelist_auth_failure |
18 | Comma separated list of usernames to exclude from max authentication failure check. |
cron.database_name |
11, 12, 13, 14, 15, 16, 17, 18 | Database in which pg_cron metadata is kept. |
cron.enable_superuser_jobs |
12, 13, 14, 15, 16, 17, 18 | Allow jobs to be scheduled as superuser. |
cron.host |
12, 13, 14, 15, 16, 17, 18 | Hostname to connect to postgres. This setting has no effect when background workers are used. |
cron.launch_active_jobs |
12, 13, 14, 15, 16, 17, 18 | Launch jobs that are defined as active. |
cron.log_min_messages |
12, 13, 14, 15, 16, 17, 18 | log_min_messages for the launcher bgworker. |
cron.log_run |
11, 12, 13, 14, 15, 16, 17, 18 | Log all jobs runs into the job_run_details table. |
cron.log_statement |
11, 12, 13, 14, 15, 16, 17, 18 | Log all cron statements prior to execution. |
cron.max_running_jobs |
11, 12, 13, 14, 15, 16, 17, 18 | Maximum number of jobs that can run concurrently. |
cron.timezone |
12, 13, 14, 15, 16, 17, 18 | Specify timezone used for cron schedule. |
cron.use_background_workers |
12, 13, 14, 15, 16, 17, 18 | Use background workers instead of client sessions. |
pgaadauth.enable_group_sync |
12, 13, 14, 15, 16, 17, 18 | Enables synchronization of Microsoft Entra ID group members. |
pgaudit.log |
11, 12, 13, 14, 15, 16, 17, 18 | Specifies which classes of statements will be logged by session audit logging. Multiple classes can be provided using a comma-separated list and classes can be subtracted by prefacing the class with a - sign. |
pgaudit.log_catalog |
11, 12, 13, 14, 15, 16, 17, 18 | Specifies that session logging should be enabled in the case where all relations in a statement are in pg_catalog. Disabling this setting will reduce noise in the log from tools like psql and PgAdmin that query the catalog heavily. |
pgaudit.log_client |
11, 12, 13, 14, 15, 16, 17, 18 | Specifies whether audit messages should be visible to the client. This setting should generally be left disabled but may be useful for debugging or other purposes. |
pgaudit.log_level |
11, 12, 13, 14, 15, 16, 17, 18 | Specifies the log level that will be used for log entries. This setting is used for regression testing and may also be useful to end users for testing or other purposes. It is not intended to be used in a production environment as it may leak which statements are being logged to the user. |
pgaudit.log_parameter |
11, 12, 13, 14, 15, 16, 17, 18 | Specifies that audit logging should include the parameters that were passed with the statement. When parameters are present they will be be included in CSV format after the statement text. |
pgaudit.log_parameter_max_size |
16, 17, 18 | Specifies, in bytes, the maximum length of variable-length parameters to log. If 0 (the default), parameters are not checked for size. If set, when the size of the parameter is longer than the setting, the value in the audit log is replaced with a placeholder. Note that for character types, the length is in bytes for the parameter's encoding, not characters. |
pgaudit.log_relation |
11, 12, 13, 14, 15, 16, 17, 18 | Specifies whether session audit logging should create a separate log entry for each relation referenced in a SELECT or DML statement. This is a useful shortcut for exhaustive logging without using object audit logging. |
pgaudit.log_rows |
14, 15, 16, 17, 18 | Specifies whether logging will include the rows retrieved or affected by a statement. |
pgaudit.log_statement |
14, 15, 16, 17, 18 | Specifies whether logging will include the statement text and parameters. Depending on requirements, the full statement text might not be required in the audit log. |
pgaudit.log_statement_once |
11, 12, 13, 14, 15, 16, 17, 18 | Specifies whether logging will include the statement text and parameters with the first log entry for a statement/substatement combination or with every entry. Disabling this setting will result in less verbose logging but may make it more difficult to determine the statement that generated a log entry, though the statement/substatement pair along with the process id should suffice to identify the statement text logged with a previous entry. |
pgaudit.role |
11, 12, 13, 14, 15, 16, 17, 18 | Specifies the master role to use for object audit logging. Multiple audit roles can be defined by granting them to the master role. This allows multiple groups to be in charge of different aspects of audit logging. |
pg_failover_slots.drop_extra_slots |
12, 13, 14, 15, 16 | whether to drop extra slots on standby that don't match pg_failover_slots.synchronize_slot_names. |
pg_failover_slots.primary_dsn |
12, 13, 14, 15, 16 | connection string to the primary server for synchronization logical slots on standby. if empty, uses the defaults to primary_conninfo. |
pg_failover_slots.standby_slot_names |
12, 13, 14, 15, 16 | list of names of slot that must confirm changes before they're sent by the decoding plugin. List of physical replication slots that must confirm durable flush of a given lsn before commits up to that lsn may be replicated to logical peers by the output plugin. Imposes ordering of physical replication before logical replication. |
pg_failover_slots.standby_slots_min_confirmed |
12, 13, 14, 15, 16 | Number of slots from pg_failover_slots.standby_slot_names that must confirm lsn. Modifies behaviour of pg_failover_slots.standby_slot_names so to allow logical replication of a transaction after at least pg_failover_slots.standby_slots_min_confirmed physical peers have confirmed the transaction as durably flushed. The value -1 (default) means all entries in pg_failover_slots.standby_slot_namesmust confirm the write. The value 0 causes pg_failover_slots.standby_slots_min_confirmedto be effectively ignored. |
pg_failover_slots.synchronize_slot_names |
12, 13, 14, 15, 16 | list of slots to synchronize from primary to physical standby. |
pg_failover_slots.version |
12, 13, 14, 15, 16 | pg_failover_slots module version. |
pg_failover_slots.wait_for_inactive_slots |
12, 13, 14, 15, 16 | whether to wait for an inactive replication slots on primary to catchup with standby. |
pg_hint_plan.debug_print |
12, 13, 14, 15, 16, 17, 18 | Logs results of hint parsing. |
pg_hint_plan.enable_hint |
12, 13, 14, 15, 16, 17, 18 | Force planner to use plans specified in the hint comment preceding to the query. |
pg_hint_plan.enable_hint_table |
12, 13, 14, 15, 16, 17, 18 | Let pg_hint_plan look up the hint table. |
pg_hint_plan.hints_anywhere |
13, 14, 15, 16 | Read hints from anywhere in a query. This option lets pg_hint_plan ignore syntax so be cautious for false reads. |
pg_hint_plan.message_level |
12, 13, 14, 15, 16, 17, 18 | Message level of debug messages. |
pg_hint_plan.parse_messages |
12, 13, 14, 15, 16, 17, 18 | Message level of parse errors. |
pglogical.batch_inserts |
11, 12, 13, 14, 15, 16, 17, 18 | Tells PGLogical to use batch insert mechanism if possible. |
pglogical.conflict_log_level |
11, 12, 13, 14, 15, 16, 17, 18 | 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, 18 | Sets the resolution method for any detected conflicts between local data and incoming changes. |
pglogical.extra_connection_options |
12, 13, 14, 15, 16, 17, 18 | connection options to add to all peer node connections. |
pglogical.synchronous_commit |
12, 13, 14, 15, 16, 17, 18 | pglogical specific synchronous commit value. |
pglogical.temp_directory |
12, 13, 14, 15, 16, 17, 18 | Directory to store dumps for local restore. |
pglogical.use_spi |
11, 12, 13, 14, 15, 16, 17, 18 | 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, 18 | Internal Use Only: This parameter is used as a feature override switch. |
pgms_wait_sampling.history_period |
11, 12, 13, 14, 15, 16, 17, 18 | Sets the 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, 18 | Selects types of wait events are tracked by this extension. Need to reload the config to make change take effect. |
pg_partman_bgw.analyze |
11, 12, 13, 14, 15, 16, 17, 18 | Whether to run an analyze on a partition set whenever a new partition is created during run_maintenance(). Set to 'on' to send TRUE (default). Set to 'off' to send FALSE. |
pg_partman_bgw.dbname |
11, 12, 13, 14, 15, 16, 17, 18 | CSV list of specific databases in the cluster to run pg_partman BGW on. |
pg_partman_bgw.interval |
11, 12, 13, 14, 15, 16, 17, 18 | How often run_maintenance() is called (in seconds). |
pg_partman_bgw.jobmon |
11, 12, 13, 14, 15, 16, 17, 18 | Whether to log run_maintenance() calls to pg_jobmon if it is installed. Set to 'on' to send TRUE (default). Set to 'off' to send FALSE. |
pg_partman_bgw.maintenance_wait |
16, 17, 18 | How long to wait between each partition set when running maintenance (in seconds). |
pg_partman_bgw.role |
11, 12, 13, 14, 15, 16, 17, 18 | Role to be used by BGW. Must have execute permissions on run_maintenance(). |
pg_prewarm.autoprewarm |
12, 13, 14, 15, 16, 17, 18 | Starts the autoprewarm worker. |
pg_prewarm.autoprewarm_interval |
12, 13, 14, 15, 16, 17, 18 | Sets the interval between dumps of shared buffers. If set to zero, time-based dumping is disabled. |
pg_qs.interval_length_minutes |
11, 12, 13, 14, 15, 16, 17, 18 | Sets the aggregration window in minutes. Need to reload the config to make change take effect. |
pg_qs.max_captured_queries |
13, 14, 15, 16, 17, 18 | Specifies the number of most relevant queries for which query store captures runtime statistics at each interval. |
pg_qs.max_plan_size |
11, 12, 13, 14, 15, 16, 17, 18 | Sets the maximum number of bytes that will be saved for query plan text; longer plans will be truncated. Need to reload the config for this change to take effect. |
pg_qs.max_query_text_length |
11, 12, 13, 14, 15, 16, 17, 18 | Sets the maximum query text length that will be saved; longer queries will be truncated. Need to reload the config to make change take effect. |
pg_qs.parameters_capture_mode |
11, 12, 13, 14, 15, 16, 17, 18 | Selects how positional query parameters are captured by pg_qs. Need to reload the config for the change to take effect. |
pg_qs.query_capture_mode |
11, 12, 13, 14, 15, 16, 17, 18 | Selects which statements are tracked by pg_qs. Need to reload the config to make change take effect. |
pg_qs.retention_period_in_days |
11, 12, 13, 14, 15, 16, 17, 18 | Sets the retention period window in days for pg_qs - after this time data will be deleted. Need to restart the server to make change take effect. |
pg_qs.store_query_plans |
11, 12, 13, 14, 15, 16, 17, 18 | Turns saving query plans on or off. Need to reload the config for the change to take effect. |
pg_qs.track_utility |
11, 12, 13, 14, 15, 16, 17, 18 | Selects whether utility commands are tracked by pg_qs. Need to reload the config to make change take effect. |
pg_stat_statements.max |
11, 12, 13, 14, 15, 16, 17, 18 | Sets the maximum number of statements tracked by pg_stat_statements. |
pg_stat_statements.save |
11, 12, 13, 14, 15, 16, 17, 18 | Save pg_stat_statements statistics across server shutdowns. |
pg_stat_statements.track |
11, 12, 13, 14, 15, 16, 17, 18 | Selects which statements are tracked by pg_stat_statements. |
pg_stat_statements.track_planning |
13, 14, 15, 16, 17, 18 | Selects whether planning duration is tracked by pg_stat_statements. |
pg_stat_statements.track_utility |
11, 12, 13, 14, 15, 16, 17, 18 | Selects whether utility commands are tracked by pg_stat_statements. |
postgis.gdal_enabled_drivers |
11, 12, 13, 14, 15, 16, 17, 18 | Controls postgis GDAL enabled driver settings. |
squeeze.max_xlock_time |
12, 13, 14, 15, 16, 17, 18 | The maximum time the processed table may be locked exclusively. The source table is locked exclusively during the final stage of processing. If the lock time should exceed this value, the lock is released and the final stage is retried a few more times. |
squeeze.worker_autostart |
12, 13, 14, 15, 16, 17, 18 | Names of databases for which background workers start automatically. Comma-separated list for of databases which squeeze worker starts as soon as the cluster startup has completed. |
squeeze.worker_role |
12, 13, 14, 15, 16, 17, 18 | Role that background workers use to connect to database. If background worker was launched automatically on cluster startup, it uses this role to initiate database connection(s). |
squeeze.workers_per_database |
15, 16, 17, 18 | Maximum number of squeeze worker processes launched for each database. |
timescaledb.bgw_launcher_poll_time |
12, 13, 14, 15, 16, 17 | Launcher timeout value in milliseconds. Configure the time the launcher waits to look for new TimescaleDB instances. |
timescaledb.disable_load |
12, 13, 14, 15, 16, 17 | Disable the loading of the actual extension. |
timescaledb.max_background_workers |
12, 13, 14, 15, 16, 17 | Maximum background worker processes allocated to TimescaleDB. Max background worker processes allocated to TimescaleDB - set to at least 1 + number of databases in Postgres instance to use background workers. |
timescaledb_osm.disable_load |
13, 14, 15, 16, 17 | Disable the loading of the actual extension. |
Developer Options
| Name | Versions | Description |
|---|---|---|
allow_in_place_tablespaces |
11, 12, 13, 14, 15, 16, 17, 18 | Allows tablespaces directly inside pg_tblspc, for testing. |
allow_system_table_mods |
11, 12, 13, 14, 15, 16, 17, 18 | Allows modifications of the structure of system tables. |
backtrace_functions |
13, 14, 15, 16, 17, 18 | Log backtrace for errors in these functions. |
debug_discard_caches |
14, 15, 16, 17, 18 | Aggressively flush system caches for debugging purposes. |
debug_io_direct |
16, 17, 18 | Use direct I/O for file access. |
debug_logical_replication_streaming |
16, 17, 18 | Forces immediate streaming or serialization of changes in large transactions. On the publisher, it allows streaming or serializing each change in logical decoding. On the subscriber, it allows serialization of all changes to files and notifies the parallel apply workers to read and apply them at the end of the transaction. |
debug_parallel_query |
16, 17, 18 | Forces the planner's use parallel query nodes. This can be useful for testing the parallel query infrastructure by forcing the planner to generate plans that contain nodes that perform tuple communication between workers and the main process. |
force_parallel_mode |
11, 12, 13, 14, 15 | Forces use of parallel query facilities. |
ignore_checksum_failure |
11, 12, 13, 14, 15, 16, 17, 18 | Continues processing after a checksum failure. Detection of a checksum failure normally causes PostgreSQL to report an error, aborting the current transaction. Setting ignore_checksum_failure to true causes the system to ignore the failure (but still report a warning), and continue processing. This behavior could cause crashes or other serious problems. Only has an effect if checksums are enabled. |
ignore_invalid_pages |
13, 14, 15, 16, 17, 18 | Continues recovery after an invalid pages failure. Detection of WAL records having references to invalid pages during recovery causes PostgreSQL to raise a PANIC-level error, aborting the recovery. Setting "ignore_invalid_pages" to true causes the system to ignore invalid page references in WAL records (but still report a warning), and continue recovery. This behavior may cause crashes, data loss, propagate or hide corruption, or other serious problems. Only has an effect during recovery or in standby mode. |
ignore_system_indexes |
11, 12, 13, 14, 15, 16, 17, 18 | Disables reading from system indexes. It does not prevent updating the indexes, so it is safe to use. The worst consequence is slowness. |
jit_debugging_support |
11, 12, 13, 14, 15, 16, 17, 18 | Register JIT-compiled functions with debugger. |
jit_dump_bitcode |
11, 12, 13, 14, 15, 16, 17, 18 | Write out LLVM bitcode to facilitate JIT debugging. |
jit_expressions |
11, 12, 13, 14, 15, 16, 17, 18 | Allow JIT compilation of expressions. |
jit_profiling_support |
11, 12, 13, 14, 15, 16, 17, 18 | Register JIT-compiled functions with perf profiler. |
jit_tuple_deforming |
11, 12, 13, 14, 15, 16, 17, 18 | Allow JIT compilation of tuple deforming. |
post_auth_delay |
11, 12, 13, 14, 15, 16, 17, 18 | Sets the amount of time to wait after authentication on connection startup. This allows attaching a debugger to the process. |
pre_auth_delay |
11, 12, 13, 14, 15, 16, 17, 18 | Sets the amount of time to wait before authentication on connection startup. This allows attaching a debugger to the process. |
remove_temp_files_after_crash |
14, 16, 17, 18 | Remove temporary files after backend crash. |
send_abort_for_crash |
16, 17, 18 | Send SIGABRT not SIGQUIT to child processes after backend crash. |
send_abort_for_kill |
16, 17, 18 | Send SIGABRT not SIGKILL to stuck child processes. |
trace_connection_negotiation |
17, 18 | Logs details of pre-authentication connection handshake. |
trace_notify |
11, 12, 13, 14, 15, 16, 17, 18 | 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, 18 | Emit information about resource usage in sorting. |
wal_consistency_checking |
11, 12, 13, 14, 15, 16, 17, 18 | Sets the WAL resource managers for which WAL consistency checks are done. Full-page images will be logged for all data blocks and cross-checked against the results of WAL replay. |
zero_damaged_pages |
11, 12, 13, 14, 15, 16, 17, 18 | Continues processing past damaged page headers. Detection of a damaged page header normally causes PostgreSQL to report an error, aborting the current transaction. Setting "zero_damaged_pages" to true causes the system to instead report a warning, zero out the damaged page, and continue processing. This behavior will destroy data, namely all the rows on the damaged page. |
Error Handling
| Name | Versions | Description |
|---|---|---|
data_sync_retry |
11, 12, 13, 14, 15, 16, 17, 18 | Whether to continue running after a failure to sync data files. |
exit_on_error |
11, 12, 13, 14, 15, 16, 17, 18 | Terminate session on any error. |
recovery_init_sync_method |
14, 15, 16, 17, 18 | Sets the method for synchronizing the data directory before crash recovery. |
restart_after_crash |
11, 12, 13, 14, 15, 16, 17, 18 | Reinitialize server after backend crash. |
File Locations
| Name | Versions | Description |
|---|---|---|
config_file |
11, 12, 13, 14, 15, 16, 17, 18 | Sets the server's main configuration file. |
data_directory |
11, 12, 13, 14, 15, 16, 17, 18 | Sets the server's data directory. |
external_pid_file |
11, 12, 13, 14, 15, 16, 17, 18 | Writes the postmaster PID to the specified file. |
hba_file |
11, 12, 13, 14, 15, 16, 17, 18 | Sets the server's "hba" configuration file. |
ident_file |
11, 12, 13, 14, 15, 16, 17, 18 | Sets the server's "ident" configuration file. |
Intelligent Tuning
| Name | Versions | Description |
|---|---|---|
index_tuning.analysis_interval |
12, 13, 14, 15, 16, 17, 18 | Sets the frequency at which each index optimization session is triggered when index_tuning.mode is set to 'REPORT'. |
index_tuning.max_columns_per_index |
12, 13, 14, 15, 16, 17, 18 | Maximum number of columns that can be part of the index key for any recommended index. |
index_tuning.max_index_count |
12, 13, 14, 15, 16, 17, 18 | Maximum number of indexes that can be recommended for each database during one optimization session. |
index_tuning.max_indexes_per_table |
12, 13, 14, 15, 16, 17, 18 | Maximum number of indexes that can be recommended for each table. |
index_tuning.max_queries_per_database |
12, 13, 14, 15, 16, 17, 18 | Number of slowest queries per database for which indexes can be recommended. |
index_tuning.max_regression_factor |
12, 13, 14, 15, 16, 17, 18 | Acceptable regression introduced by a recommended index on any of the queries analyzed during one optimization session. |
index_tuning.max_total_size_factor |
12, 13, 14, 15, 16, 17, 18 | Maximum total size, in percentage of total disk space, that all recommended indexes for any given database can use. |
index_tuning.min_improvement_factor |
12, 13, 14, 15, 16, 17, 18 | Cost improvement that a recommended index must provide to at least one of the queries analyzed during one optimization session. |
index_tuning.mode |
12, 13, 14, 15, 16, 17, 18 | Configures index optimization as disabled ('OFF') or enabled to only emit recommendation. Requires Query Store to be enabled by setting pg_qs.query_capture_mode to 'TOP' or 'ALL'. |
index_tuning.unused_dml_per_table |
12, 13, 14, 15, 16, 17, 18 | Minimum number of daily average DML operations affecting the table, so that their unused indexes are considered for dropping. |
index_tuning.unused_min_period |
12, 13, 14, 15, 16, 17, 18 | Minimum number of days the index has not been used, based on system statistics, so that it is considered for dropping. |
index_tuning.unused_reads_per_table |
12, 13, 14, 15, 16, 17, 18 | Minimum number of daily average read operations affecting the table, so that their unused indexes are considered for dropping. |
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. |
logfiles.download_enable |
11, 12, 13, 14, 15, 16, 17, 18 | Enables or disables server logs functionality. |
logfiles.retention_days |
11, 12, 13, 14, 15, 16, 17, 18 | Sets the retention period window in days for server logs - after this time data will be deleted. |
Lock Management
| Name | Versions | Description |
|---|---|---|
deadlock_timeout |
11, 12, 13, 14, 15, 16, 17, 18 | Sets the time to wait on a lock before checking for deadlock. |
max_locks_per_transaction |
11, 12, 13, 14, 15, 16, 17, 18 | Sets the maximum number of locks per transaction. The shared lock table is sized on the assumption that at most "max_locks_per_transaction" objects per server process or prepared transaction will need to be locked at any one time. |
max_pred_locks_per_page |
11, 12, 13, 14, 15, 16, 17, 18 | Sets the maximum number of predicate-locked tuples per page. If more than this number of tuples on the same page are locked by a connection, those locks are replaced by a page-level lock. |
max_pred_locks_per_relation |
11, 12, 13, 14, 15, 16, 17, 18 | Sets the maximum number of predicate-locked pages and tuples per relation. If more than this total of pages and tuples in the same relation are locked by a connection, those locks are replaced by a relation-level lock. |
max_pred_locks_per_transaction |
11, 12, 13, 14, 15, 16, 17, 18 | Sets the maximum number of predicate locks per transaction. The shared predicate lock table is sized on the assumption that at most "max_pred_locks_per_transaction" objects per server process or prepared transaction will need to be locked at any one time. |
Metrics
| Name | Versions | Description |
|---|---|---|
metrics.autovacuum_diagnostics |
11, 12, 13, 14, 15, 16, 17, 18 | Enables metrics collection for all table statistics within a database |
metrics.collector_database_activity |
11, 12, 13, 14, 15, 16, 17, 18 | Enables metrics collection for database and activity statistics |
metrics.pgbouncer_diagnostics |
11, 12, 13, 14, 15, 16, 17, 18 | Enables metrics collection for PgBouncer. |
Migration
| Name | Versions | Description |
|---|---|---|
azure.migration_copy_with_binary |
11, 12, 13, 14, 15, 16, 17, 18 | When set to on, this parameter will enable the use of the binary format for copying data during migration. |
azure.migration_skip_analyze |
11, 12, 13, 14, 15, 16, 17, 18 | When set to on, this parameter will skip the analyze phase (vacuumdb --analyze-only) during the migration. |
azure.migration_skip_extensions |
11, 12, 13, 14, 15, 16, 17, 18 | When set to on, this parameter will skip the migration of extensions. |
azure.migration_skip_large_objects |
11, 12, 13, 14, 15, 16, 17, 18 | When set to on, this parameter will skip the migration of large objects such as BLOBs. |
azure.migration_skip_role_user |
11, 12, 13, 14, 15, 16, 17, 18 | When set to on, this parameter will exclude user roles from the migration process. |
azure.migration_table_split_size |
11, 12, 13, 14, 15, 16, 17, 18 | When set, this parameter specifies the size at which tables will be partitioned during migration. |
PgBouncer
| Name | Versions | Description |
|---|---|---|
pgbouncer.default_pool_size |
11, 12, 13, 14, 15, 16, 17, 18 | How many server connections to allow per user/database pair. |
pgbouncer.enabled |
11, 12, 13, 14, 15, 16, 17, 18 | Denotes if pgBouncer service is enabled. |
pgbouncer.ignore_startup_parameters |
11, 12, 13, 14, 15, 16, 17, 18 | 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, 18 | Maximum number of client connections allowed. |
pgbouncer.max_prepared_statements |
11, 12, 13, 14, 15, 16, 17, 18 | 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, 18 | Add more server connections to pool if below this number. |
pgbouncer.pool_mode |
11, 12, 13, 14, 15, 16, 17, 18 | Specifies when a server connection can be reused by other clients. |
pgbouncer.query_wait_timeout |
11, 12, 13, 14, 15, 16, 17, 18 | 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, 18 | 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, 18 | 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, 18 | Shows the size of a disk block. |
data_checksums |
11, 12, 13, 14, 15, 16, 17, 18 | Shows whether data checksums are turned on for this cluster. |
data_directory_mode |
11, 12, 13, 14, 15, 16, 17, 18 | Shows the mode of the data directory. The parameter value is a numeric mode specification in the form accepted by the chmod and umask system calls. (To use the customary octal format the number must start with a 0 (zero).). |
debug_assertions |
11, 12, 13, 14, 15, 16, 17, 18 | Shows whether the running server has assertion checks enabled. |
huge_pages_status |
17, 18 | Indicates the status of huge pages. |
in_hot_standby |
14, 15, 16, 17, 18 | Shows whether hot standby is currently active. |
integer_datetimes |
11, 12, 13, 14, 15, 16, 17, 18 | 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, 18 | Shows the maximum number of function arguments. |
max_identifier_length |
11, 12, 13, 14, 15, 16, 17, 18 | Shows the maximum identifier length. |
max_index_keys |
11, 12, 13, 14, 15, 16, 17, 18 | Shows the maximum number of index keys. |
num_os_semaphores |
18 | Shows the number of semaphores required for the server. |
segment_size |
11, 12, 13, 14, 15, 16, 17, 18 | Shows the number of pages per disk file. |
server_encoding |
11, 12, 13, 14, 15, 16, 17, 18 | Shows the server (database) character set encoding. |
server_version |
11, 12, 13, 14, 15, 16, 17, 18 | Shows the server version. |
server_version_num |
11, 12, 13, 14, 15, 16, 17, 18 | Shows the server version as an integer. |
shared_memory_size |
15, 16, 17, 18 | 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, 18 | Shows the number of huge pages needed for the main shared memory area. -1 indicates that the value could not be determined. |
ssl_library |
12, 13, 14, 15, 16, 17, 18 | Shows the name of the SSL library. |
wal_block_size |
11, 12, 13, 14, 15, 16, 17, 18 | Shows the block size in the write ahead log. |
wal_segment_size |
11, 12, 13, 14, 15, 16, 17, 18 | Shows the size of write ahead log segments. |
Process Title
| Name | Versions | Description |
|---|---|---|
cluster_name |
11, 12, 13, 14, 15, 16, 17, 18 | Sets the name of the cluster, which is included in the process title. |
update_process_title |
11, 12, 13, 14, 15, 16, 17, 18 | Updates the process title to show the active SQL command. Enables updating of the process title every time a new SQL command is received by the server. |
Query Tuning / Genetic Query Optimizer
| Name | Versions | Description |
|---|---|---|
geqo |
11, 12, 13, 14, 15, 16, 17, 18 | Enables genetic query optimization. This algorithm attempts to do planning without exhaustive searching. |
geqo_effort |
11, 12, 13, 14, 15, 16, 17, 18 | GEQO: effort is used to set the default for other GEQO parameters. |
geqo_generations |
11, 12, 13, 14, 15, 16, 17, 18 | GEQO: number of iterations of the algorithm. Zero selects a suitable default value. |
geqo_pool_size |
11, 12, 13, 14, 15, 16, 17, 18 | GEQO: number of individuals in the population. Zero selects a suitable default value. |
geqo_seed |
11, 12, 13, 14, 15, 16, 17, 18 | GEQO: seed for random path selection. |
geqo_selection_bias |
11, 12, 13, 14, 15, 16, 17, 18 | GEQO: selective pressure within the population. |
geqo_threshold |
11, 12, 13, 14, 15, 16, 17, 18 | 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, 18 | Enables the planner to use constraints to optimize queries. Table scans will be skipped if their constraints guarantee that no rows match the query. |
cursor_tuple_fraction |
11, 12, 13, 14, 15, 16, 17, 18 | 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, 18 | Sets the default statistics target. This applies to table columns that have not had a column-specific target set via ALTER TABLE SET STATISTICS. |
from_collapse_limit |
11, 12, 13, 14, 15, 16, 17, 18 | Sets the FROM-list size beyond which subqueries are not collapsed. The planner will merge subqueries into upper queries if the resulting FROM list would have no more than this many items. |
jit |
11, 12, 13, 14, 15, 16, 17, 18 | Allow JIT compilation. |
join_collapse_limit |
11, 12, 13, 14, 15, 16, 17, 18 | Sets the FROM-list size beyond which JOIN constructs are not flattened. The planner will flatten explicit JOIN constructs into lists of FROM items whenever a list of no more than this many items would result. |
plan_cache_mode |
12, 13, 14, 15, 16, 17, 18 | Controls the planner's selection of custom or generic plan. Prepared statements can have custom and generic plans, and the planner will attempt to choose which is better. This can be set to override the default behavior. |
recursive_worktable_factor |
15, 16, 17, 18 | 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, 18 | 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, 18 | Sets the planner's estimate of the cost of processing each operator or function call. |
cpu_tuple_cost |
11, 12, 13, 14, 15, 16, 17, 18 | Sets the planner's estimate of the cost of processing each tuple (row). |
effective_cache_size |
11, 12, 13, 14, 15, 16, 17, 18 | Sets the planner's assumption about the total size of the data caches. That is, the total size of the caches (kernel cache and shared buffers) used for PostgreSQL data files. This is measured in disk pages, which are normally 8 kB each. |
jit_above_cost |
11, 12, 13, 14, 15, 16, 17, 18 | Perform JIT compilation if query is more expensive. -1 disables JIT compilation. |
jit_inline_above_cost |
11, 12, 13, 14, 15, 16, 17, 18 | Perform JIT inlining if query is more expensive. -1 disables inlining. |
jit_optimize_above_cost |
11, 12, 13, 14, 15, 16, 17, 18 | Optimize JIT-compiled functions if query is more expensive. -1 disables optimization. |
min_parallel_index_scan_size |
11, 12, 13, 14, 15, 16, 17, 18 | Sets the minimum amount of index data for a parallel scan. If the planner estimates that it will read a number of index pages too small to reach this limit, a parallel scan will not be considered. |
min_parallel_table_scan_size |
11, 12, 13, 14, 15, 16, 17, 18 | Sets the minimum amount of table data for a parallel scan. If the planner estimates that it will read a number of table pages too small to reach this limit, a parallel scan will not be considered. |
parallel_setup_cost |
11, 12, 13, 14, 15, 16, 17, 18 | 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, 18 | Sets the planner's estimate of the cost of passing each tuple (row) from worker to leader backend. |
random_page_cost |
11, 12, 13, 14, 15, 16, 17, 18 | Sets the planner's estimate of the cost of a nonsequentially fetched disk page. |
seq_page_cost |
11, 12, 13, 14, 15, 16, 17, 18 | 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, 18 | Enables the planner's use of async append plans. |
enable_bitmapscan |
11, 12, 13, 14, 15, 16, 17, 18 | Enables the planner's use of bitmap-scan plans. |
enable_distinct_reordering |
18 | Enables reordering of DISTINCT keys. |
enable_gathermerge |
11, 12, 13, 14, 15, 16, 17, 18 | Enables the planner's use of gather merge plans. |
enable_group_by_reordering |
17, 18 | Enables reordering of GROUP BY keys. |
enable_hashagg |
11, 12, 13, 14, 15, 16, 17, 18 | Enables the planner's use of hashed aggregation plans. |
enable_hashjoin |
11, 12, 13, 14, 15, 16, 17, 18 | Enables the planner's use of hash join plans. |
enable_incremental_sort |
13, 14, 15, 16, 17, 18 | Enables the planner's use of incremental sort steps. |
enable_indexonlyscan |
11, 12, 13, 14, 15, 16, 17, 18 | Enables the planner's use of index-only-scan plans. |
enable_indexscan |
11, 12, 13, 14, 15, 16, 17, 18 | Enables the planner's use of index-scan plans. |
enable_material |
11, 12, 13, 14, 15, 16, 17, 18 | Enables the planner's use of materialization. |
enable_memoize |
14, 15, 16, 17, 18 | Enables the planner's use of memoization. |
enable_mergejoin |
11, 12, 13, 14, 15, 16, 17, 18 | Enables the planner's use of merge join plans. |
enable_nestloop |
11, 12, 13, 14, 15, 16, 17, 18 | Enables the planner's use of nested-loop join plans. |
enable_parallel_append |
11, 12, 13, 14, 15, 16, 17, 18 | Enables the planner's use of parallel append plans. |
enable_parallel_hash |
11, 12, 13, 14, 15, 16, 17, 18 | Enables the planner's use of parallel hash plans. |
enable_partition_pruning |
11, 12, 13, 14, 15, 16, 17, 18 | Enables plan-time and execution-time partition pruning. Allows the query planner and executor to compare partition bounds to conditions in the query to determine which partitions must be scanned. |
enable_partitionwise_aggregate |
11, 12, 13, 14, 15, 16, 17, 18 | Enables partitionwise aggregation and grouping. |
enable_partitionwise_join |
11, 12, 13, 14, 15, 16, 17, 18 | Enables partitionwise join. |
enable_presorted_aggregate |
16, 17, 18 | Enables the planner's ability to produce plans that provide presorted input for ORDER BY / DISTINCT aggregate functions. Allows the query planner to build plans that provide presorted input for aggregate functions with an ORDER BY / DISTINCT clause. When disabled, implicit sorts are always performed during execution. |
enable_self_join_elimination |
18 | Enables removal of unique self-joins. |
enable_seqscan |
11, 12, 13, 14, 15, 16, 17, 18 | Enables the planner's use of sequential-scan plans. |
enable_sort |
11, 12, 13, 14, 15, 16, 17, 18 | Enables the planner's use of explicit sort steps. |
enable_tidscan |
11, 12, 13, 14, 15, 16, 17, 18 | 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, 18 | Number of synchronous standbys and list of names of potential synchronous ones. |
Replication / Primary Server
| Name | Versions | Description |
|---|---|---|
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, 18 | Sets the maximum number of simultaneously defined replication slots. |
max_slot_wal_keep_size |
13, 14, 15, 16, 17, 18 | Sets the maximum WAL size that can be reserved by replication slots. Replication slots will be marked as failed, and segments released for deletion or recycling, if this much space is occupied by WAL on disk. |
max_wal_senders |
11, 12, 13, 14, 15, 16, 17, 18 | Sets the maximum number of simultaneously running WAL sender processes. |
track_commit_timestamp |
11, 12, 13, 14, 15, 16, 17, 18 | 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, 18 | Sets the size of WAL files held for standby servers. |
wal_sender_timeout |
11, 12, 13, 14, 15, 16, 17, 18 | Sets the maximum time to wait for WAL replication. |
Replication / Standby Servers
| Name | Versions | Description |
|---|---|---|
hot_standby |
11, 12, 13, 14, 15, 16, 17, 18 | Allows connections and queries during recovery. |
hot_standby_feedback |
11, 12, 13, 14, 15, 16, 17, 18 | Allows feedback from a hot standby to the primary that will avoid query conflicts. |
idle_replication_slot_timeout |
18 | Sets the duration a replication slot can remain idle before it is invalidated. |
max_active_replication_origins |
18 | Sets the maximum number of active replication origins. |
max_standby_archive_delay |
11, 12, 13, 14, 15, 16, 17, 18 | Sets the maximum delay before canceling queries when a hot standby server is processing archived WAL data. |
max_standby_streaming_delay |
11, 12, 13, 14, 15, 16, 17, 18 | Sets the maximum delay before canceling queries when a hot standby server is processing streamed WAL data. |
primary_conninfo |
12, 13, 14, 15, 16, 17, 18 | Sets the connection string to be used to connect to the sending server. |
primary_slot_name |
12, 13, 14, 15, 16, 17, 18 | 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, 18 | Sets the minimum delay for applying changes during recovery. |
synchronized_standby_slots |
17, 18 | Lists streaming replication standby server replication slot names that logical WAL sender processes will wait for. Logical WAL sender processes will send decoded changes to output plugins only after the specified replication slots have confirmed receiving WAL. |
sync_replication_slots |
17, 18 | Enables a physical standby to synchronize logical failover replication slots from the primary server. |
wal_receiver_create_temp_slot |
13, 14, 15, 16, 17, 18 | 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, 18 | Sets the maximum interval between WAL receiver status reports to the sending server. |
wal_receiver_timeout |
11, 12, 13, 14, 15, 16, 17, 18 | Sets the maximum wait time to receive data from the sending server. |
wal_retrieve_retry_interval |
11, 12, 13, 14, 15, 16, 17, 18 | 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, 18 | Maximum number of logical replication worker processes. |
max_parallel_apply_workers_per_subscription |
16, 17, 18 | Maximum number of parallel apply workers per subscription. |
max_sync_workers_per_subscription |
11, 12, 13, 14, 15, 16, 17, 18 | 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, 18 | Sets the application name to be reported in statistics and logs. |
debug_pretty_print |
11, 12, 13, 14, 15, 16, 17, 18 | Indents parse and plan tree displays. |
debug_print_parse |
11, 12, 13, 14, 15, 16, 17, 18 | Logs each query's parse tree. |
debug_print_plan |
11, 12, 13, 14, 15, 16, 17, 18 | Logs each query's execution plan. |
debug_print_rewritten |
11, 12, 13, 14, 15, 16, 17, 18 | Logs each query's rewritten parse tree. |
log_autovacuum_min_duration |
11, 12, 13, 14, 15, 16, 17, 18 | Sets the minimum execution time above which autovacuum actions will be logged. Zero prints all actions. -1 turns autovacuum logging off. |
log_checkpoints |
11, 12, 13, 14, 15, 16, 17, 18 | Logs each checkpoint. |
log_connections |
11, 12, 13, 14, 15, 16, 17, 18 | Logs each successful connection. |
log_disconnections |
11, 12, 13, 14, 15, 16, 17, 18 | Logs end of a session, including duration. |
log_duration |
11, 12, 13, 14, 15, 16, 17, 18 | Logs the duration of each completed SQL statement. |
log_error_verbosity |
11, 12, 13, 14, 15, 16, 17, 18 | Sets the verbosity of logged messages. |
log_hostname |
11, 12, 13, 14, 15, 16, 17, 18 | Logs the host name in the connection logs. By default, connection logs only show the IP address of the connecting host. If you want them to show the host name you can turn this on, but depending on your host name resolution setup it might impose a non-negligible performance penalty. |
log_line_prefix |
11, 12, 13, 14, 15, 16, 17, 18 | Controls information prefixed to each log line. If blank, no prefix is used. |
log_lock_failure |
18 | Controls whether a detailed log message is produced when a lock acquisition fails. |
log_lock_waits |
11, 12, 13, 14, 15, 16, 17, 18 | Logs long lock waits. |
log_parameter_max_length |
13, 14, 15, 16, 17, 18 | Sets the maximum length in bytes of data logged for bind parameter values when logging statements. -1 to print values in full. |
log_parameter_max_length_on_error |
13, 14, 15, 16, 17, 18 | Sets the maximum length in bytes of data logged for bind parameter values when logging statements, on error. -1 to print values in full. |
log_recovery_conflict_waits |
14, 15, 16, 17, 18 | Logs standby recovery conflict waits. |
log_replication_commands |
11, 12, 13, 14, 15, 16, 17, 18 | Logs each replication command. |
log_statement |
11, 12, 13, 14, 15, 16, 17, 18 | Sets the type of statements logged. |
log_temp_files |
11, 12, 13, 14, 15, 16, 17, 18 | Log the use of temporary files larger than this number of kilobytes. Zero logs all files. The default is -1 (turning this feature off). |
log_timezone |
11, 12, 13, 14, 15, 16, 17, 18 | 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, 18 | Sets the minimum execution time above which a sample of statements will be logged. Sampling is determined by log_statement_sample_rate. Zero logs a sample of all queries. -1 turns this feature off. |
log_min_duration_statement |
11, 12, 13, 14, 15, 16, 17, 18 | Sets the minimum execution time above which all statements will be logged. Zero prints all queries. -1 turns this feature off. |
log_min_error_statement |
11, 12, 13, 14, 15, 16, 17, 18 | Causes all statements generating error at or above this level to be logged. Each level includes all the levels that follow it. The later the level, the fewer messages are sent. |
log_min_messages |
11, 12, 13, 14, 15, 16, 17, 18 | Sets the message levels that are logged. Each level includes all the levels that follow it. The later the level, the fewer messages are sent. |
log_startup_progress_interval |
15, 16, 17, 18 | Time between progress updates for long-running startup operations. 0 turns this feature off. |
log_statement_sample_rate |
13, 14, 15, 16, 17, 18 | Fraction of statements exceeding "log_min_duration_sample" to be logged. Use a value between 0.0 (never log) and 1.0 (always log). |
log_transaction_sample_rate |
12, 13, 14, 15, 16, 17, 18 | Sets the fraction of transactions from which to log all statements. Use a value between 0.0 (never log) and 1.0 (log all statements for all transactions). |
Reporting and Logging / Where to Log
| Name | Versions | Description |
|---|---|---|
event_source |
11, 12, 13, 14, 15, 16, 17, 18 | Sets the application name used to identify PostgreSQL messages in the event log. |
log_destination |
11, 12, 13, 14, 15, 16, 17, 18 | Sets the destination for server log output. Valid values are combinations of "stderr", "syslog", "csvlog", "jsonlog", and "eventlog", depending on the platform. |
log_directory |
11, 12, 13, 14, 15, 16, 17, 18 | Sets the destination directory for log files. Can be specified as relative to the data directory or as absolute path. |
log_file_mode |
11, 12, 13, 14, 15, 16, 17, 18 | Sets the file permissions for log files. The parameter value is expected to be a numeric mode specification in the form accepted by the chmod and umask system calls. (To use the customary octal format the number must start with a 0 (zero).). |
log_filename |
11, 12, 13, 14, 15, 16, 17, 18 | Sets the file name pattern for log files. |
logging_collector |
11, 12, 13, 14, 15, 16, 17, 18 | Start a subprocess to capture stderr, csvlog and/or jsonlog into log files. |
log_rotation_age |
11, 12, 13, 14, 15, 16, 17, 18 | Sets the amount of time to wait before forcing log file rotation. |
log_rotation_size |
11, 12, 13, 14, 15, 16, 17, 18 | Sets the maximum size a log file can reach before being rotated. |
log_truncate_on_rotation |
11, 12, 13, 14, 15, 16, 17, 18 | Truncate existing log files of same name during log rotation. |
md5_password_warnings |
18 | Enables deprecation warnings for MD5 passwords. |
syslog_facility |
11, 12, 13, 14, 15, 16, 17, 18 | Sets the syslog "facility" to be used when syslog enabled. |
syslog_ident |
11, 12, 13, 14, 15, 16, 17, 18 | Sets the program name used to identify PostgreSQL messages in syslog. |
syslog_sequence_numbers |
11, 12, 13, 14, 15, 16, 17, 18 | Add sequence number to syslog messages to avoid duplicate suppression. |
syslog_split_messages |
11, 12, 13, 14, 15, 16, 17, 18 | 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, 18 | Number of pages after which previously performed writes are flushed to disk. |
effective_io_concurrency |
11, 12, 13, 14, 15, 16, 17, 18 | Number of simultaneous requests that can be handled efficiently by the disk subsystem. |
file_copy_method |
18 | Selects the file copy method. |
maintenance_io_concurrency |
13, 14, 15, 16, 17, 18 | A variant of "effective_io_concurrency" that is used for maintenance work. |
max_notify_queue_pages |
17, 18 | Sets the maximum number of allocated pages for NOTIFY / LISTEN queue. |
max_parallel_maintenance_workers |
11, 12, 13, 14, 15, 16, 17, 18 | Sets the maximum number of parallel processes per maintenance operation. |
max_parallel_workers |
11, 12, 13, 14, 15, 16, 17, 18 | Sets the maximum number of parallel workers that can be active at one time. |
max_parallel_workers_per_gather |
11, 12, 13, 14, 15, 16, 17, 18 | Sets the maximum number of parallel processes per executor node. |
max_worker_processes |
11, 12, 13, 14, 15, 16, 17, 18 | Maximum number of concurrent worker processes. |
old_snapshot_threshold |
12, 13, 14, 15, 16 | Time before a snapshot is too old to read pages changed after the snapshot was taken. |
parallel_leader_participation |
11, 12, 13, 14, 15, 16, 17, 18 | Controls whether Gather and Gather Merge also run subplans. Should gather nodes also run subplans or just gather tuples?. |
Resource Usage / Background Writer
| Name | Versions | Description |
|---|---|---|
bgwriter_delay |
11, 12, 13, 14, 15, 16, 17, 18 | Background writer sleep time between rounds. |
bgwriter_flush_after |
11, 12, 13, 14, 15, 16, 17, 18 | Number of pages after which previously performed writes are flushed to disk. |
bgwriter_lru_maxpages |
11, 12, 13, 14, 15, 16, 17, 18 | Background writer maximum number of LRU pages to flush per round. |
bgwriter_lru_multiplier |
11, 12, 13, 14, 15, 16, 17, 18 | Multiple of the average buffer usage to free per round. |
Resource Usage / Cost-Based Vacuum Delay
| Name | Versions | Description |
|---|---|---|
vacuum_cost_delay |
11, 12, 13, 14, 15, 16, 17, 18 | Vacuum cost delay in milliseconds. |
vacuum_cost_limit |
11, 12, 13, 14, 15, 16, 17, 18 | Vacuum cost amount available before napping. |
vacuum_cost_page_dirty |
11, 12, 13, 14, 15, 16, 17, 18 | Vacuum cost for a page dirtied by vacuum. |
vacuum_cost_page_hit |
11, 12, 13, 14, 15, 16, 17, 18 | Vacuum cost for a page found in the buffer cache. |
vacuum_cost_page_miss |
11, 12, 13, 14, 15, 16, 17, 18 | Vacuum cost for a page not found in the buffer cache. |
Resource Usage / Disk
| Name | Versions | Description |
|---|---|---|
temp_file_limit |
11, 12, 13, 14, 15, 16, 17, 18 | Limits the total size of all temporary files used by each process. -1 means no limit. |
Resource Usage / Kernel Resources
| Name | Versions | Description |
|---|---|---|
max_files_per_process |
11, 12, 13, 14, 15, 16, 17, 18 | 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, 18 | Sets the maximum memory to be used by each autovacuum worker process. |
commit_timestamp_buffers |
17, 18 | Sets the size of the dedicated buffer pool used for the commit timestamp cache. Specify 0 to have this value determined as a fraction of shared_buffers. |
dynamic_shared_memory_type |
11, 12, 13, 14, 15, 16, 17, 18 | Selects the dynamic shared memory implementation used. |
hash_mem_multiplier |
12, 13, 14, 15, 16, 17, 18 | Multiple of "work_mem" to use for hash tables. |
huge_pages |
11, 12, 13, 14, 15, 16, 17, 18 | Use of huge pages on Linux or Windows. |
huge_page_size |
14, 15, 16, 17, 18 | The size of huge page that should be requested. |
io_combine_limit |
17, 18 | Limit on the size of data reads and writes. |
io_max_combine_limit |
18 | Server-wide limit that clamps io_combine_limit. |
io_max_concurrency |
18 | Max number of IOs that one process can execute simultaneously. |
io_method |
18 | Selects the method for executing asynchronous I/O. |
io_workers |
18 | Number of IO worker processes, for io_method=worker. |
logical_decoding_work_mem |
13, 14, 15, 16, 17, 18 | Sets the maximum memory to be used for logical decoding. This much memory can be used by each internal reorder buffer before spilling to disk. |
maintenance_work_mem |
11, 12, 13, 14, 15, 16, 17, 18 | Sets the maximum memory to be used for maintenance operations. This includes operations such as VACUUM and CREATE INDEX. |
max_prepared_transactions |
11, 12, 13, 14, 15, 16, 17, 18 | Sets the maximum number of simultaneously prepared transactions. |
max_stack_depth |
11, 12, 13, 14, 15, 16, 17, 18 | Sets the maximum stack depth, in kilobytes. |
min_dynamic_shared_memory |
14, 15, 16, 17, 18 | Amount of dynamic shared memory reserved at startup. |
multixact_member_buffers |
17, 18 | Sets the size of the dedicated buffer pool used for the MultiXact member cache. |
multixact_offset_buffers |
17, 18 | Sets the size of the dedicated buffer pool used for the MultiXact offset cache. |
notify_buffers |
17, 18 | Sets the size of the dedicated buffer pool used for the LISTEN/NOTIFY message cache. |
serializable_buffers |
17, 18 | Sets the size of the dedicated buffer pool used for the serializable transaction cache. |
shared_buffers |
11, 12, 13, 14, 15, 16, 17, 18 | Sets the number of shared memory buffers used by the server. |
shared_memory_type |
12, 13, 14, 15, 16, 17, 18 | Selects the shared memory implementation used for the main shared memory region. |
subtransaction_buffers |
17, 18 | Sets the size of the dedicated buffer pool used for the subtransaction cache. Specify 0 to have this value determined as a fraction of shared_buffers. |
temp_buffers |
11, 12, 13, 14, 15, 16, 17, 18 | Sets the maximum number of temporary buffers used by each session. |
transaction_buffers |
17, 18 | Sets the size of the dedicated buffer pool used for the transaction status cache. Specify 0 to have this value determined as a fraction of shared_buffers. |
vacuum_buffer_usage_limit |
16, 17, 18 | Sets the buffer pool size for VACUUM, ANALYZE, and autovacuum. |
work_mem |
11, 12, 13, 14, 15, 16, 17, 18 | Sets the maximum memory to be used for query workspaces. This much memory can be used by each internal sort operation and hash table before switching to temporary disk files. |
Statistics / Cumulative Query and Index Statistics
| Name | Versions | Description |
|---|---|---|
stats_fetch_consistency |
15, 16, 17, 18 | Sets the consistency of accesses to statistics data. |
track_activities |
11, 12, 13, 14, 15, 16, 17, 18 | Collects information about executing commands. Enables the collection of information on the currently executing command of each session, along with the time at which that command began execution. |
track_activity_query_size |
11, 12, 13, 14, 15, 16, 17, 18 | Sets the size reserved for pg_stat_activity.query, in bytes. |
track_cost_delay_timing |
18 | Collects timing statistics for cost-based vacuum delay. |
track_counts |
11, 12, 13, 14, 15, 16, 17, 18 | Collects statistics on database activity. |
track_functions |
11, 12, 13, 14, 15, 16, 17, 18 | Collects function-level statistics on database activity. |
track_io_timing |
11, 12, 13, 14, 15, 16, 17, 18 | Collects timing statistics for database I/O activity. |
track_wal_io_timing |
14, 15, 16, 17, 18 | Collects timing statistics for WAL I/O activity. |
Statistics / Monitoring
| Name | Versions | Description |
|---|---|---|
compute_query_id |
14, 15, 16, 17, 18 | Enables in-core computation of query identifiers. |
log_executor_stats |
11, 12, 13, 14, 15, 16, 17, 18 | Writes executor performance statistics to the server log. |
log_parser_stats |
11, 12, 13, 14, 15, 16, 17, 18 | Writes parser performance statistics to the server log. |
log_planner_stats |
11, 12, 13, 14, 15, 16, 17, 18 | Writes planner performance statistics to the server log. |
log_statement_stats |
11, 12, 13, 14, 15, 16, 17, 18 | 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, 18 | 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 |
|---|---|---|
allow_alter_system |
17, 18 | Allows running the ALTER SYSTEM command. Can be set to off for environments where global configuration changes should be made using a different method. |
transform_null_equals |
11, 12, 13, 14, 15, 16, 17, 18 | Treats "expr=NULL" as "expr IS NULL". When turned 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. The correct behavior of expr = NULL is to always return null (unknown). |
Version and Platform Compatibility / Previous PostgreSQL Versions
| Name | Versions | Description |
|---|---|---|
array_nulls |
11, 12, 13, 14, 15, 16, 17, 18 | Enable input of NULL elements in arrays. When turned on, unquoted NULL in an array input value means a null value; otherwise it is taken literally. |
backslash_quote |
11, 12, 13, 14, 15, 16, 17, 18 | Sets whether "\'" is allowed in string literals. |
escape_string_warning |
11, 12, 13, 14, 15, 16, 17, 18 | Warn about backslash escapes in ordinary string literals. |
lo_compat_privileges |
11, 12, 13, 14, 15, 16, 17, 18 | Enables backward compatibility mode for privilege checks on large objects. Skips privilege checks when reading or modifying large objects, for compatibility with PostgreSQL releases prior to 9.0. |
operator_precedence_warning |
11, 12, 13 | Emits a warning for constructs that changed meaning since PostgreSQL 9.4. |
quote_all_identifiers |
11, 12, 13, 14, 15, 16, 17, 18 | When generating SQL fragments, quote all identifiers. |
standard_conforming_strings |
11, 12, 13, 14, 15, 16, 17, 18 | Causes '...' strings to treat backslashes literally. |
synchronize_seqscans |
11, 12, 13, 14, 15, 16, 17, 18 | Enable synchronized sequential scans. |
Write-Ahead Log / Archive Recovery
| Name | Versions | Description |
|---|---|---|
archive_cleanup_command |
12, 13, 14, 15, 16, 17, 18 | Sets the shell command that will be executed at every restart point. |
recovery_end_command |
12, 13, 14, 15, 16, 17, 18 | Sets the shell command that will be executed once at the end of recovery. |
restore_command |
12, 13, 14, 15, 16, 17, 18 | 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, 18 | Sets the shell command that will be called to archive a WAL file. This is used only if "archive_library" is not set. |
archive_library |
15, 16, 17, 18 | Sets the library that will be called to archive a WAL file. An empty string indicates that "archive_command" should be used. |
archive_mode |
11, 12, 13, 14, 15, 16, 17, 18 | Allows archiving of WAL files using "archive_command". |
archive_timeout |
11, 12, 13, 14, 15, 16, 17, 18 | Sets the amount of time to wait before forcing a switch to the next WAL file. |
Write-Ahead Log / Checkpoints
| Name | Versions | Description |
|---|---|---|
checkpoint_completion_target |
11, 12, 13, 14, 15, 16, 17, 18 | Time spent flushing dirty buffers during checkpoint, as fraction of checkpoint interval. |
checkpoint_flush_after |
11, 12, 13, 14, 15, 16, 17, 18 | Number of pages after which previously performed writes are flushed to disk. |
checkpoint_timeout |
11, 12, 13, 14, 15, 16, 17, 18 | Sets the maximum time between automatic WAL checkpoints. |
checkpoint_warning |
11, 12, 13, 14, 15, 16, 17, 18 | Sets the maximum time before warning if checkpoints triggered by WAL volume happen too frequently. Write a message to the server log if checkpoints caused by the filling of WAL segment files happen more frequently than this amount of time. Zero turns off the warning. |
max_wal_size |
11, 12, 13, 14, 15, 16, 17, 18 | Sets the WAL size that triggers a checkpoint. |
min_wal_size |
11, 12, 13, 14, 15, 16, 17, 18 | Sets the minimum size to shrink the WAL to. |
Write-Ahead Log / Recovery
| Name | Versions | Description |
|---|---|---|
recovery_prefetch |
15, 16, 17, 18 | Prefetch referenced blocks during recovery. Look ahead in the WAL to find references to uncached data. |
wal_decode_buffer_size |
15, 16, 17, 18 | Buffer size for reading ahead in the WAL during recovery. Maximum distance to read ahead in the WAL to prefetch referenced data blocks. |
Write-Ahead Log / Recovery Target
| Name | Versions | Description |
|---|---|---|
recovery_target |
12, 13, 14, 15, 16, 17, 18 | Set to "immediate" to end recovery as soon as a consistent state is reached. |
recovery_target_action |
12, 13, 14, 15, 16, 17, 18 | Sets the action to perform upon reaching the recovery target. |
recovery_target_inclusive |
12, 13, 14, 15, 16, 17, 18 | Sets whether to include or exclude transaction with recovery target. |
recovery_target_lsn |
12, 13, 14, 15, 16, 17, 18 | Sets the LSN of the write-ahead log location up to which recovery will proceed. |
recovery_target_name |
12, 13, 14, 15, 16, 17, 18 | Sets the named restore point up to which recovery will proceed. |
recovery_target_time |
12, 13, 14, 15, 16, 17, 18 | Sets the time stamp up to which recovery will proceed. |
recovery_target_timeline |
12, 13, 14, 15, 16, 17, 18 | Specifies the timeline to recover into. |
recovery_target_xid |
12, 13, 14, 15, 16, 17, 18 | Sets the transaction ID up to which recovery will proceed. |
summarize_wal |
17, 18 | Starts the WAL summarizer process to enable incremental backup. |
Write-Ahead Log / Settings
| Name | Versions | Description |
|---|---|---|
commit_delay |
11, 12, 13, 14, 15, 16, 17, 18 | Sets the delay in microseconds between transaction commit and flushing WAL to disk. |
commit_siblings |
11, 12, 13, 14, 15, 16, 17, 18 | Sets the minimum number of concurrent open transactions required before performing "commit_delay". |
fsync |
11, 12, 13, 14, 15, 16, 17, 18 | Forces synchronization of updates to disk. The server will use the fsync() system call in several places to make sure that updates are physically written to disk. This ensures that a database cluster will recover to a consistent state after an operating system or hardware crash. |
full_page_writes |
11, 12, 13, 14, 15, 16, 17, 18 | Writes full pages to WAL when first modified after a checkpoint. A page write in process during an operating system crash might be only partially written to disk. During recovery, the row changes stored in WAL are not enough to recover. This option writes pages when first modified after a checkpoint to WAL so full recovery is possible. |
synchronous_commit |
11, 12, 13, 14, 15, 16, 17, 18 | Sets the current transaction's synchronization level. |
wal_buffers |
11, 12, 13, 14, 15, 16, 17, 18 | Sets the number of disk-page buffers in shared memory for WAL. Specify -1 to have this value determined as a fraction of shared_buffers. |
wal_compression |
11, 12, 13, 14, 15, 16, 17, 18 | Compresses full-page writes written in WAL file. |
wal_init_zero |
12, 13, 14, 15, 16, 17, 18 | Writes zeroes to new WAL files before first use. |
wal_level |
11, 12, 13, 14, 15, 16, 17, 18 | Sets the level of information written to the WAL. |
wal_log_hints |
11, 12, 13, 14, 15, 16, 17, 18 | 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, 18 | Recycles WAL files by renaming them. |
wal_skip_threshold |
13, 14, 15, 16, 17, 18 | Minimum size of new file to fsync instead of writing WAL. |
wal_summary_keep_time |
17, 18 | Time for which WAL summary files should be kept. |
wal_sync_method |
11, 12, 13, 14, 15, 16, 17, 18 | Selects the method used for forcing WAL updates to disk. |
wal_writer_delay |
11, 12, 13, 14, 15, 16, 17, 18 | Time between WAL flushes performed in the WAL writer. |
wal_writer_flush_after |
11, 12, 13, 14, 15, 16, 17, 18 | Amount of WAL written out by WAL writer that triggers a flush. |