Postgres Azure Server parameters
Jump to navigation
Jump to search
array_nulls
ON
OFF
Dynamic
Enable input of NULL elements in arrays.
autovacuum
ON
OFF
Dynamic
Starts the autovacuum subprocess.
autovacuum_analyze_scale_factor
Dynamic
Number of tuple inserts, updates, or deletes prior to analyze as a fraction of reltuples.
autovacuum_analyze_threshold
Dynamic
Minimum number of tuple inserts, updates, or deletes prior to analyze.
autovacuum_freeze_max_age
Static
Age at which to autovacuum a table to prevent transaction ID wraparound. Any change requires restarting the server to take effect.
autovacuum_max_workers
Static
Sets the maximum number of simultaneously running autovacuum worker processes. Any change requires restarting the server to take effect.
autovacuum_multixact_freeze_max_age
Static
Multixact age at which to autovacuum a table to prevent multixact wraparound. Any change requires restarting the server to take effect.
autovacuum_naptime
Dynamic
Time to sleep between autovacuum runs. Unit is s.
autovacuum_vacuum_cost_delay
Dynamic
Vacuum cost delay in milliseconds, for autovacuum.
autovacuum_vacuum_cost_limit
Dynamic
Vacuum cost amount available before napping, for autovacuum.
autovacuum_vacuum_scale_factor
Dynamic
Number of tuple updates or deletes prior to vacuum as a fraction of reltuples.
autovacuum_vacuum_threshold
Dynamic
Minimum number of tuple updates or deletes prior to vacuum.
autovacuum_work_mem
Dynamic
Sets the maximum memory to be used by each autovacuum worker process. Unit is kb.
backend_flush_after
Dynamic
Number of pages after which previously performed writes are flushed to disk. Unit is 8kb.
backslash_quote
Dynamic
Sets whether "\'" is allowed in string literals.
bgwriter_delay
Dynamic
Background writer sleep time between rounds. Unit is ms.
bgwriter_flush_after
Dynamic
Number of pages after which previously performed writes are flushed to disk. Unit is 8kb.
bgwriter_lru_maxpages
Dynamic
Background writer maximum number of LRU pages to flush per round.
bgwriter_lru_multiplier
Dynamic
Multiple of the average buffer usage to free per round.
bytea_output
Dynamic
Sets the output format for bytea.
check_function_bodies
ON
OFF
Dynamic
Check function bodies during CREATE FUNCTION.
checkpoint_completion_target
Dynamic
Time spent flushing dirty buffers during checkpoint, as fraction of checkpoint interval.
checkpoint_warning
Dynamic
Enables warnings if checkpoint segments are filled more frequently than this. Unit is s.
client_encoding
Dynamic
Sets the client's character set encoding.
client_min_messages
Dynamic
Sets the message levels that are sent to the client.
commit_delay
Dynamic
Sets the delay in microseconds between transaction commit and flushing WAL to disk.
commit_siblings
Dynamic
Sets the minimum concurrent open transactions before performing commit_delay.
connection_throttling
ON
OFF
Dynamic
Enables temporary connection throttling per IP for too many invalid password login failures.
constraint_exclusion
Dynamic
Enables the planner to use constraints to optimize queries.
cpu_index_tuple_cost
Dynamic
Sets the planner's estimate of the cost of processing each index entry during an index scan.
cpu_operator_cost
Dynamic
Sets the planner's estimate of the cost of processing each operator or function call.
cpu_tuple_cost
Dynamic
Sets the planner's estimate of the cost of processing each tuple (row).
cursor_tuple_fraction
Dynamic
Sets the planner's estimate of the fraction of a cursor's rows that will be retrieved.
datestyle
Dynamic
Sets the display format for date and time values.
deadlock_timeout
Dynamic
Sets the amount of time, in milliseconds, to wait on a lock before checking for deadlock.
debug_print_parse
ON
OFF
Dynamic
Logs each query's parse tree.
debug_print_plan
ON
OFF
Dynamic
Logs each query's execution plan.
debug_print_rewritten
ON
OFF
Dynamic
Logs each query's rewritten parse tree.
default_statistics_target
Dynamic
Sets the default statistics target.
default_text_search_config
Dynamic
Sets default text search configuration.
default_transaction_deferrable
ON
OFF
Dynamic
Sets the default deferrable status of new transactions.
default_transaction_isolation
Dynamic
Sets the transaction isolation level of each new transaction.
default_transaction_read_only
ON
OFF
Dynamic
Sets the default read-only status of new transactions.
default_with_oids
ON
OFF
Dynamic
Create new tables with OIDs by default.
effective_cache_size
Dynamic
Sets the planner's assumption about the size of the disk cache. Unit is 8kb.
enable_bitmapscan
ON
OFF
Dynamic
Enables the planner's use of bitmap-scan plans.
enable_hashagg
ON
OFF
Dynamic
Enables the planner's use of hashed aggregation plans.
enable_hashjoin
ON
OFF
Dynamic
Enables the planner's use of hash join plans.
enable_indexonlyscan
ON
OFF
Dynamic
Enables the planner's use of index-only-scan plans.
enable_indexscan
ON
OFF
Dynamic
Enables the planner's use of index-scan plans.
enable_material
ON
OFF
Dynamic
Enables the planner's use of materialization.
enable_mergejoin
ON
OFF
Dynamic
Enables the planner's use of merge join plans.
enable_nestloop
ON
OFF
Dynamic
Enables the planner's use of nested-loop join plans.
enable_partitionwise_aggregate
ON
OFF
Dynamic
Enables or disables the query planner's use of partitionwise grouping or aggregation, which allows grouping or aggregation on a partitioned tables performed separately for each partition.
enable_partitionwise_join
ON
OFF
Dynamic
Enables or disables the query planner's use of partitionwise join, which allows a join between partitioned tables to be performed by joining the matching partitions.
enable_seqscan
ON
OFF
Dynamic
Enables the planner's use of sequential-scan plans.
enable_sort
ON
OFF
Dynamic
Enables the planner's use of explicit sort steps.
enable_tidscan
ON
OFF
Dynamic
Enables the planner's use of TID scan plans.
escape_string_warning
ON
OFF
Dynamic
Warn about backslash escapes in ordinary string literals.
exit_on_error
ON
OFF
Dynamic
Terminate session on any error.
extra_float_digits
Dynamic
Sets the number of digits displayed for floating-point values.
force_parallel_mode
Dynamic
Forces use of parallel query facilities.
from_collapse_limit
Dynamic
Sets the FROM-list size beyond which subqueries are not collapsed.
geqo
ON
OFF
Dynamic
Enables genetic query optimization.
geqo_effort
Dynamic
GEQO: effort is used to set the default for other GEQO parameters.
geqo_generations
Dynamic
GEQO: number of iterations of the algorithm.
geqo_pool_size
Dynamic
GEQO: number of individuals in the population.
geqo_seed
Dynamic
GEQO: seed for random path selection.
geqo_selection_bias
Dynamic
GEQO: selective pressure within the population.
geqo_threshold
Dynamic
Sets the threshold of FROM items beyond which GEQO is used.
gin_fuzzy_search_limit
Dynamic
Sets the maximum allowed result for exact search by GIN.
gin_pending_list_limit
Dynamic
Sets the maximum size of the pending list for GIN index. Unit is kb.
hot_standby_feedback
ON
OFF
Dynamic
Allows feedback from a hot standby to the primary that will avoid query conflicts.
idle_in_transaction_session_timeout
Dynamic
Sets the maximum allowed duration of any idling transaction. Unit is ms.
intervalstyle
Dynamic
Sets the display format for interval values.
join_collapse_limit
Dynamic
Sets the FROM-list size beyond which JOIN constructs are not flattened.
lc_monetary
Dynamic
Sets the locale for formatting monetary amounts.
lc_numeric
Dynamic
Sets the locale for formatting numbers.
lo_compat_privileges
ON
OFF
Dynamic
Enables backward compatibility mode for privilege checks on large objects.
lock_timeout
Dynamic
Sets the maximum allowed duration (in milliseconds) of any wait for a lock. 0 turns this off.
log_autovacuum_min_duration
Dynamic
Sets the minimum execution time above which autovacuum actions will be logged. Unit is ms.
log_checkpoints
ON
OFF
Dynamic
Logs each checkpoint.
log_connections
ON
OFF
Dynamic
Logs each successful connection.
log_disconnections
ON
OFF
Dynamic
Logs end of a session, including duration.
log_duration
ON
OFF
Dynamic
Logs the duration of each completed SQL statement.
log_error_verbosity
Dynamic
Sets the verbosity of logged messages.
log_line_prefix
Dynamic
Sets the printf-style string that is output at the beginning of each log line.
log_lock_waits
ON
OFF
Dynamic
Logs long lock waits.
log_min_duration_statement
Dynamic
Sets the minimum execution time (in milliseconds) above which statements will be logged. -1 disables logging statement durations.
log_min_error_statement
Dynamic
Causes all statements generating error at or above this level to be logged.
log_min_messages
Dynamic
Sets the message levels that are logged.
log_replication_commands
ON
OFF
Dynamic
Logs each replication command.
log_retention_days
Dynamic
Sets how many days a log file is saved for.
log_statement
Dynamic
Sets the type of statements logged.
log_statement_stats
ON
OFF
Dynamic
For each query, writes cumulative performance statistics to the server log.
log_temp_files
Dynamic
Log the use of temporary files larger than this number of kilobytes.
logging_collector
ON
OFF
Static
Enable .log files. Setting is independent of Azure Monitor logging options. Any change requires restarting the server to take effect.
maintenance_work_mem
Dynamic
Sets the maximum memory to be used for maintenance operations. Unit is kb.
max_locks_per_transaction
Static
Sets the maximum number of locks per transaction. Any change requires restarting the server to take effect. When running a replica server, you must set this parameter to the same or higher value than on the master server.
max_parallel_workers
Dynamic
Sets the maximum number of parallel workers than can be active at one time.
max_parallel_workers_per_gather
Dynamic
Sets the maximum number of parallel processes per executor node.
max_prepared_transactions
Static
Sets the maximum number of simultaneously prepared transactions. Any change requires restarting the server to take effect. When running a replica server, you must set this parameter to the same or higher value than on the master server.
max_replication_slots
Static
IMPORTANT NOTE: Generally a single digit value is sufficient for this parameter for most scenarios. Only in scenarios like migration where there is a need to migrate 10's of databases at once should you set this to a higher value. Also change max_wal_senders accordingly. Description: Specifies the maximum number of replication slots that the server can support.
max_standby_archive_delay
Dynamic
Sets the maximum delay before canceling queries when a hot standby server is processing archived WAL data. Unit is ms.
max_standby_streaming_delay
Dynamic
Sets the maximum delay before canceling queries when a hot standby server is processing streamed WAL data. Unit is ms.
max_wal_senders
Static
IMPORTANT NOTE: In PG 11 and lower, the value max_wal_senders MUST be much smaller relative to max_connections since it takes up the connection slots. See https://docs.microsoft.com/en-us/azure/postgresql/single-server/concepts-limits for max_connections info in single server. Typically a single digit value suffices for most scenarios. Only in scenarios like migration where there is a need to migrate 10's of databases at once should you set this to a higher value. Also change max_replication_slots accordingly. Description: The parameter specifies the maximum number of concurrent connections from standby servers or streaming base backup clients (i.e., the maximum number of simultaneously running WAL sender processes).
max_wal_size
Dynamic
Sets the WAL size that triggers a checkpoint. Unit is mb.
min_parallel_index_scan_size
Dynamic
Sets the minimum amount of index data for a parallel scan. Unit is 8kb.
min_parallel_table_scan_size
Dynamic
Sets the minimum amount of table data for a parallel scan. Unit is 8kb.
min_wal_size
Dynamic
Sets the minimum size to shrink the WAL to. Unt is mb.
old_snapshot_threshold
Static
Time before a snapshot is too old to read pages changed after the snapshot was taken. Any change requires restarting the server to take effect. Unit is min.
operator_precedence_warning
ON
OFF
Dynamic
Emit a warning for constructs that changed meaning since PostgreSQL 9.4.
parallel_leader_participation
ON
OFF
Dynamic
Enables or disables the query planner's use of partitionwise grouping or aggregation, which allows grouping or aggregation on a partitioned tables performed separately for each partition.
parallel_setup_cost
Dynamic
Sets the planner's estimate of the cost of starting up worker processes for parallel query.
parallel_tuple_cost
Dynamic
Sets the planner's estimate of the cost of passing each tuple (row) from worker to master backend.
pg_qs.interval_length_minutes
Static
Sets the query_store capture interval in minutes for pg_qs - this is the frequency of data persistence.
pg_qs.max_query_text_length
Dynamic
Sets the maximum query text length that will be saved; longer queries will be truncated.
pg_qs.query_capture_mode
Dynamic
Selects which statements are tracked by pg_qs.
pg_qs.replace_parameter_placeholders
ON
OFF
Dynamic
Selects whether parameter placeholders are replaced in parameterized queries.
pg_qs.retention_period_in_days
Dynamic
Sets the retention period window in days for pg_qs - after this time data will be deleted.
pg_qs.track_utility
ON
OFF
Dynamic
Selects whether utility commands are tracked by pg_qs.
pg_stat_statements.max
Static
Sets the maximum number of statements tracked by pg_stat_statements. Any change requires restarting the server to take effect.
pg_stat_statements.save
ON
OFF
Dynamic
Save pg_stat_statements statistics across server shutdowns.
pg_stat_statements.track
Dynamic
Controls which statements are counted by pg_stat_statements.
pg_stat_statements.track_utility
ON
OFF
Dynamic
Selects whether utility commands are tracked by pg_stat_statements.
pgms_wait_sampling.history_period
Dynamic
Set the frequency, in milliseconds, at which wait events are sampled.
pgms_wait_sampling.query_capture_mode
Dynamic
Selects which statements are tracked by the pgms_wait_sampling extension.
postgis.gdal_enabled_drivers
Dynamic
Controls postgis GDAL enabled driver settings.
quote_all_identifiers
ON
OFF
Dynamic
When generating SQL fragments, quote all identifiers.
random_page_cost
Dynamic
Sets the planner's estimate of the cost of a nonsequentially fetched disk page.
row_security
ON
OFF
Dynamic
Enable row security.
search_path
Dynamic
Sets the schema search order for names that are not schema-qualified.
seq_page_cost
Dynamic
Sets the planner's estimate of the cost of a sequentially fetched disk page.
session_replication_role
Dynamic
Sets the session's behavior for triggers and rewrite rules.
shared_preload_libraries
Static
Sets which shared libraries are preloaded at server start. Any change requires a restart to take effect.
statement_timeout
Dynamic
Sets the maximum allowed duration (in milliseconds) of any statement. 0 turns this off.
synchronize_seqscans
ON
OFF
Dynamic
Enable synchronized sequential scans.
synchronous_commit
Dynamic
Sets the current transaction's synchronization level.
tcp_keepalives_count
Dynamic
Maximum number of TCP keepalive retransmits.
tcp_keepalives_idle
Dynamic
Time between issuing TCP keepalives. Unit is s.
tcp_keepalives_interval
Dynamic
Time between TCP keepalive retransmits.Unit is s.
temp_buffers
Dynamic
Sets the maximum number of temporary buffers used by each database session. Unit is 8kb.
timezone
Dynamic
Sets the time zone for displaying and interpreting time stamps
track_activities
ON
OFF
Dynamic
Collects information about executing commands.
track_activity_query_size
Static
Sets the size reserved for pg_stat_activity.query, in bytes. Any change requires restarting the server to take effect.
track_commit_timestamp
ON
OFF
Static
Collects transaction commit time. Any change requires restarting the server to take effect.
track_counts
ON
OFF
Dynamic
Collects statistics on database activity.
track_functions
Dynamic
Collects function-level statistics on database activity.
track_io_timing
ON
OFF
Dynamic
Collects timing statistics for database I/O activity.
transform_null_equals
ON
OFF
Dynamic
Treats "expr=NULL" as "expr IS NULL".
vacuum_cost_delay
Dynamic
Vacuum cost delay in milliseconds.
vacuum_cost_limit
Dynamic
Vacuum cost amount available before napping.
vacuum_cost_page_dirty
Dynamic
Vacuum cost for a page dirtied by vacuum.
vacuum_cost_page_hit
Dynamic
Vacuum cost for a page found in the buffer cache.
vacuum_cost_page_miss
Dynamic
Vacuum cost for a page not found in the buffer cache.
vacuum_defer_cleanup_age
Dynamic
Number of transactions by which VACUUM and HOT cleanup should be deferred, if any.
vacuum_freeze_min_age
Dynamic
Minimum age at which VACUUM should freeze a table row.
vacuum_freeze_table_age
Dynamic
Age at which VACUUM should scan whole table to freeze tuples.
vacuum_multixact_freeze_min_age
Dynamic
Minimum age at which VACUUM should freeze a MultiXactId in a table row.
vacuum_multixact_freeze_table_age
Dynamic
Multixact age at which VACUUM should scan whole table to freeze tuples.
wal_buffers
Static
Sets the number of disk-page buffers in shared memory for WAL. Any change requires restarting the server to take effect. Unit is 8kb.
wal_receiver_status_interval
Dynamic
Sets the maximum interval between WAL receiver status reports to the primary. Unit is s.