PostgreSQL comes with a ton of configuration settings, but with documentation scattered all over The Manual! Here is a handy cheat sheet of all Postgres 10 configuration options, split into categories, with clickable links that go to the official documentation. Enjoy!

Autovacuum

autovacuum Starts the autovacuum subprocess. autovacuum_analyze_scale_factor Number of tuple inserts, updates, or deletes prior to analyze as a fraction of reltuples. autovacuum_analyze_threshold Minimum number of tuple inserts, updates, or deletes prior to analyze. autovacuum_freeze_max_age Age at which to autovacuum a table to prevent transaction ID wraparound. autovacuum_max_workers Sets the maximum number of simultaneously running autovacuum worker processes. autovacuum_multixact_freeze_max_age Multixact age at which to autovacuum a table to prevent multixact wraparound. autovacuum_naptime Time to sleep between autovacuum runs. autovacuum_vacuum_cost_delay Vacuum cost delay in milliseconds, for autovacuum. autovacuum_vacuum_cost_limit Vacuum cost amount available before napping, for autovacuum. autovacuum_vacuum_scale_factor Number of tuple updates or deletes prior to vacuum as a fraction of reltuples. autovacuum_vacuum_threshold Minimum number of tuple updates or deletes prior to vacuum.

Client Connection Defaults / Locale and Formatting

client_encoding Sets the client’s character set encoding. DateStyle Sets the display format for date and time values. default_text_search_config Sets default text search configuration. extra_float_digits Sets the number of digits displayed for floating-point values. IntervalStyle Sets the display format for interval values. lc_collate Shows the collation order locale. lc_ctype Shows the character classification and case conversion locale. lc_messages Sets the language in which messages are displayed. lc_monetary Sets the locale for formatting monetary amounts. lc_numeric Sets the locale for formatting numbers. lc_time Sets the locale for formatting date and time values. server_encoding Sets the server (database) character set encoding. TimeZone Sets the time zone for displaying and interpreting time stamps. timezone_abbreviations Selects a file of time zone abbreviations.

Client Connection Defaults / Other Defaults

dynamic_library_path Sets the path for dynamically loadable modules. gin_fuzzy_search_limit Sets the maximum allowed result for exact search by GIN. tcp_keepalives_count Maximum number of TCP keepalive retransmits. tcp_keepalives_idle Time between issuing TCP keepalives. tcp_keepalives_interval Time between TCP keepalive retransmits.

Client Connection Defaults / Shared Library Preloading

local_preload_libraries Lists unprivileged shared libraries to preload into each backend. session_preload_libraries Lists shared libraries to preload into each backend. shared_preload_libraries Lists shared libraries to preload into server.

Client Connection Defaults / Statement Behavior

bytea_output Sets the output format for bytea. check_function_bodies Check function bodies during CREATE FUNCTION. default_tablespace Sets the default tablespace to create tables and indexes in. default_transaction_deferrable Sets the default deferrable status of new transactions. default_transaction_isolation Sets the transaction isolation level of each new transaction. default_transaction_read_only Sets the default read-only status of new transactions. gin_pending_list_limit Sets the maximum size of the pending list for GIN index. idle_in_transaction_session_timeout Sets the maximum allowed duration of any idling transaction. lock_timeout Sets the maximum allowed duration of any wait for a lock. search_path Sets the schema search order for names that are not schema-qualified. session_replication_role Sets the session’s behavior for triggers and rewrite rules. statement_timeout Sets the maximum allowed duration of any statement. temp_tablespaces Sets the tablespace(s) to use for temporary tables and sort files. transaction_deferrable Whether to defer a read-only serializable transaction until it can be executed with no possible serialization failures. transaction_isolation Sets the current transaction’s isolation level. transaction_read_only Sets the current transaction’s read-only status. vacuum_freeze_min_age Minimum age at which VACUUM should freeze a table row. vacuum_freeze_table_age Age at which VACUUM should scan whole table to freeze tuples. vacuum_multixact_freeze_min_age Minimum age at which VACUUM should freeze a MultiXactId in a table row. vacuum_multixact_freeze_table_age Multixact age at which VACUUM should scan whole table to freeze tuples. xmlbinary Sets how binary values are to be encoded in XML. xmloption Sets whether XML data in implicit parsing and serialization operations is to be considered as documents or content fragments.

Connections and Authentication / Connection Settings

bonjour Enables advertising the server via Bonjour. bonjour_name Sets the Bonjour service name. listen_addresses Sets the host name or IP address(es) to listen to. max_connections Sets the maximum number of concurrent connections. port Sets the TCP port the server listens on. superuser_reserved_connections Sets the number of connection slots reserved for superusers. unix_socket_directories Sets the directories where Unix-domain sockets will be created. unix_socket_group Sets the owning group of the Unix-domain socket. unix_socket_permissions Sets the access permissions of the Unix-domain socket.

Connections and Authentication / Security and Authentication

authentication_timeout Sets the maximum allowed time to complete client authentication. db_user_namespace Enables per-database user names. krb_caseins_users Sets whether Kerberos and GSSAPI user names should be treated as case-insensitive. krb_server_keyfile Sets the location of the Kerberos server key file. password_encryption Encrypt passwords. row_security Enable row security. ssl Enables SSL connections. ssl_ca_file Location of the SSL certificate authority file. ssl_cert_file Location of the SSL server certificate file. ssl_ciphers Sets the list of allowed SSL ciphers. ssl_crl_file Location of the SSL certificate revocation list file. ssl_dh_params_file Location of the SSL DH parameters file. ssl_ecdh_curve Sets the curve to use for ECDH. ssl_key_file Location of the SSL server private key file. ssl_prefer_server_ciphers Give priority to server ciphersuite order.

Developer Options

allow_system_table_mods Allows modifications of the structure of system tables. ignore_checksum_failure Continues processing after a checksum failure. ignore_system_indexes Disables reading from system indexes. post_auth_delay Waits N seconds on connection startup after authentication. pre_auth_delay Waits N seconds on connection startup before authentication. trace_notify Generates debugging output for LISTEN and NOTIFY. trace_recovery_messages Enables logging of recovery-related debugging information. trace_sort Emit information about resource usage in sorting. wal_consistency_checking Sets the WAL resource managers for which WAL consistency checks are done. zero_damaged_pages Continues processing past damaged page headers.

Error Handling

exit_on_error Terminate session on any error. restart_after_crash Reinitialize server after backend crash.

File Locations

config_file Sets the server’s main configuration file. data_directory Sets the server’s data directory. external_pid_file Writes the postmaster PID to the specified file. hba_file Sets the server’s “hba” configuration file. ident_file Sets the server’s “ident” configuration file.

Lock Management

deadlock_timeout Sets the time to wait on a lock before checking for deadlock. max_locks_per_transaction Sets the maximum number of locks per transaction. max_pred_locks_per_page Sets the maximum number of predicate-locked tuples per page. max_pred_locks_per_relation Sets the maximum number of predicate-locked pages and tuples per relation. max_pred_locks_per_transaction Sets the maximum number of predicate locks per transaction.

Preset Options

block_size Shows the size of a disk block. data_checksums Shows whether data checksums are turned on for this cluster. debug_assertions Shows whether the running server has assertion checks enabled. integer_datetimes Datetimes are integer based. max_function_args Shows the maximum number of function arguments. max_identifier_length Shows the maximum identifier length. max_index_keys Shows the maximum number of index keys. segment_size Shows the number of pages per disk file. server_version Shows the server version. server_version_num Shows the server version as an integer. wal_block_size Shows the block size in the write ahead log. wal_segment_size Shows the number of pages per write ahead log segment.

Process Title

cluster_name Sets the name of the cluster, which is included in the process title. update_process_title Updates the process title to show the active SQL command.

Query Tuning / Genetic Query Optimizer

geqo Enables genetic query optimization. geqo_effort GEQO: effort is used to set the default for other GEQO parameters. geqo_generations GEQO: number of iterations of the algorithm. geqo_pool_size GEQO: number of individuals in the population. geqo_seed GEQO: seed for random path selection. geqo_selection_bias GEQO: selective pressure within the population. geqo_threshold Sets the threshold of FROM items beyond which GEQO is used.

Query Tuning / Other Planner Options

constraint_exclusion Enables the planner to use constraints to optimize queries. cursor_tuple_fraction Sets the planner’s estimate of the fraction of a cursor’s rows that will be retrieved. default_statistics_target Sets the default statistics target. force_parallel_mode Forces use of parallel query facilities. from_collapse_limit Sets the FROM-list size beyond which subqueries are not collapsed. join_collapse_limit Sets the FROM-list size beyond which JOIN constructs are not flattened.

Query Tuning / Planner Cost Constants

cpu_index_tuple_cost Sets the planner’s estimate of the cost of processing each index entry during an index scan. cpu_operator_cost Sets the planner’s estimate of the cost of processing each operator or function call. cpu_tuple_cost Sets the planner’s estimate of the cost of processing each tuple (row). effective_cache_size Sets the planner’s assumption about the size of the disk cache. min_parallel_index_scan_size Sets the minimum amount of index data for a parallel scan. min_parallel_table_scan_size Sets the minimum amount of table data for a parallel scan. parallel_setup_cost Sets the planner’s estimate of the cost of starting up worker processes for parallel query. parallel_tuple_cost Sets the planner’s estimate of the cost of passing each tuple (row) from worker to master backend. random_page_cost Sets the planner’s estimate of the cost of a nonsequentially fetched disk page. seq_page_cost Sets the planner’s estimate of the cost of a sequentially fetched disk page.

Query Tuning / Planner Method Configuration

enable_bitmapscan Enables the planner’s use of bitmap-scan plans. enable_gathermerge Enables the planner’s use of gather merge plans. enable_hashagg Enables the planner’s use of hashed aggregation plans. enable_hashjoin Enables the planner’s use of hash join plans. enable_indexonlyscan Enables the planner’s use of index-only-scan plans. enable_indexscan Enables the planner’s use of index-scan plans. enable_material Enables the planner’s use of materialization. enable_mergejoin Enables the planner’s use of merge join plans. enable_nestloop Enables the planner’s use of nested-loop join plans. enable_seqscan Enables the planner’s use of sequential-scan plans. enable_sort Enables the planner’s use of explicit sort steps. enable_tidscan Enables the planner’s use of TID scan plans.

Replication

track_commit_timestamp Collects transaction commit time.

Replication / Master Server

synchronous_standby_names Number of synchronous standbys and list of names of potential synchronous ones. vacuum_defer_cleanup_age Number of transactions by which VACUUM and HOT cleanup should be deferred, if any.

Replication / Sending Servers

max_replication_slots Sets the maximum number of simultaneously defined replication slots. max_wal_senders Sets the maximum number of simultaneously running WAL sender processes. wal_keep_segments Sets the number of WAL files held for standby servers. wal_sender_timeout Sets the maximum time to wait for WAL replication.

Replication / Standby Servers

hot_standby Allows connections and queries during recovery. hot_standby_feedback Allows feedback from a hot standby to the primary that will avoid query conflicts. max_standby_archive_delay Sets the maximum delay before canceling queries when a hot standby server is processing archived WAL data. max_standby_streaming_delay Sets the maximum delay before canceling queries when a hot standby server is processing streamed WAL data. wal_receiver_status_interval Sets the maximum interval between WAL receiver status reports to the primary. wal_receiver_timeout Sets the maximum wait time to receive data from the primary. wal_retrieve_retry_interval Sets the time to wait before retrying to retrieve WAL after a failed attempt.

max_logical_replication_workers Maximum number of logical replication worker processes. max_sync_workers_per_subscription Maximum number of table synchronization workers per subscription.

Reporting and Logging / What to Log

application_name Sets the application name to be reported in statistics and logs. debug_pretty_print Indents parse and plan tree displays. debug_print_parse Logs each query’s parse tree. debug_print_plan Logs each query’s execution plan. debug_print_rewritten Logs each query’s rewritten parse tree. log_autovacuum_min_duration Sets the minimum execution time above which autovacuum actions will be logged. log_checkpoints Logs each checkpoint. log_connections Logs each successful connection. log_disconnections Logs end of a session, including duration. log_duration Logs the duration of each completed SQL statement. log_error_verbosity Sets the verbosity of logged messages. log_hostname Logs the host name in the connection logs. log_line_prefix Controls information prefixed to each log line. log_lock_waits Logs long lock waits. log_replication_commands Logs each replication command. log_statement Sets the type of statements logged. log_temp_files Log the use of temporary files larger than this number of kilobytes. log_timezone Sets the time zone to use in log messages.

Reporting and Logging / When to Log

client_min_messages Sets the message levels that are sent to the client. log_min_duration_statement Sets the minimum execution time above which statements will be logged. log_min_error_statement Causes all statements generating error at or above this level to be logged. log_min_messages Sets the message levels that are logged.

Reporting and Logging / Where to Log

event_source Sets the application name used to identify PostgreSQL messages in the event log. log_destination Sets the destination for server log output. log_directory Sets the destination directory for log files. log_file_mode Sets the file permissions for log files. log_filename Sets the file name pattern for log files. log_rotation_age Automatic log file rotation will occur after N minutes. log_rotation_size Automatic log file rotation will occur after N kilobytes. log_truncate_on_rotation Truncate existing log files of same name during log rotation. logging_collector Start a subprocess to capture stderr output and/or csvlogs into log files. syslog_facility Sets the syslog “facility” to be used when syslog enabled. syslog_ident Sets the program name used to identify PostgreSQL messages in syslog. syslog_sequence_numbers Add sequence number to syslog messages to avoid duplicate suppression. syslog_split_messages Split messages sent to syslog by lines and to fit into 1024 bytes.

Resource Usage / Asynchronous Behavior

backend_flush_after Number of pages after which previously performed writes are flushed to disk. effective_io_concurrency Number of simultaneous requests that can be handled efficiently by the disk subsystem. max_parallel_workers Sets the maximum number of parallel workers than can be active at one time. max_parallel_workers_per_gather Sets the maximum number of parallel processes per executor node. max_worker_processes Maximum number of concurrent worker processes. old_snapshot_threshold Time before a snapshot is too old to read pages changed after the snapshot was taken.

Resource Usage / Background Writer

bgwriter_delay Background writer sleep time between rounds. bgwriter_flush_after Number of pages after which previously performed writes are flushed to disk. bgwriter_lru_maxpages Background writer maximum number of LRU pages to flush per round. bgwriter_lru_multiplier Multiple of the average buffer usage to free per round.

Resource Usage / Cost-Based Vacuum Delay

vacuum_cost_delay Vacuum cost delay in milliseconds. vacuum_cost_limit Vacuum cost amount available before napping. vacuum_cost_page_dirty Vacuum cost for a page dirtied by vacuum. vacuum_cost_page_hit Vacuum cost for a page found in the buffer cache. vacuum_cost_page_miss Vacuum cost for a page not found in the buffer cache.

Resource Usage / Disk

temp_file_limit Limits the total size of all temporary files used by each process.

Resource Usage / Kernel Resources

max_files_per_process Sets the maximum number of simultaneously open files for each server process.

Resource Usage / Memory

autovacuum_work_mem Sets the maximum memory to be used by each autovacuum worker process. dynamic_shared_memory_type Selects the dynamic shared memory implementation used. huge_pages Use of huge pages on Linux. maintenance_work_mem Sets the maximum memory to be used for maintenance operations. max_prepared_transactions Sets the maximum number of simultaneously prepared transactions. max_stack_depth Sets the maximum stack depth, in kilobytes. replacement_sort_tuples Sets the maximum number of tuples to be sorted using replacement selection. shared_buffers Sets the number of shared memory buffers used by the server. temp_buffers Sets the maximum number of temporary buffers used by each session. track_activity_query_size Sets the size reserved for pg_stat_activity.query, in bytes. work_mem Sets the maximum memory to be used for query workspaces.

Statistics / Monitoring

log_executor_stats Writes executor performance statistics to the server log. log_parser_stats Writes parser performance statistics to the server log. log_planner_stats Writes planner performance statistics to the server log. log_statement_stats Writes cumulative performance statistics to the server log.

Statistics / Query and Index Statistics Collector

stats_temp_directory Writes temporary statistics files to the specified directory. track_activities Collects information about executing commands. track_counts Collects statistics on database activity. track_functions Collects function-level statistics on database activity. track_io_timing Collects timing statistics for database I/O activity.

Version and Platform Compatibility / Other Platforms and Clients

transform_null_equals Treats “expr=NULL” as “expr IS NULL”.

Version and Platform Compatibility / Previous PostgreSQL Versions

array_nulls Enable input of NULL elements in arrays. backslash_quote Sets whether “'” is allowed in string literals. default_with_oids Create new tables with OIDs by default. escape_string_warning Warn about backslash escapes in ordinary string literals. lo_compat_privileges Enables backward compatibility mode for privilege checks on large objects. operator_precedence_warning Emit a warning for constructs that changed meaning since PostgreSQL 9.4. quote_all_identifiers When generating SQL fragments, quote all identifiers. standard_conforming_strings Causes ‘…’ strings to treat backslashes literally. synchronize_seqscans Enable synchronized sequential scans.

Write-Ahead Log / Archiving

archive_command Sets the shell command that will be called to archive a WAL file. archive_mode Allows archiving of WAL files using archive_command. archive_timeout Forces a switch to the next WAL file if a new file has not been started within N seconds.

Write-Ahead Log / Checkpoints

checkpoint_completion_target Time spent flushing dirty buffers during checkpoint, as fraction of checkpoint interval. checkpoint_flush_after Number of pages after which previously performed writes are flushed to disk. checkpoint_timeout Sets the maximum time between automatic WAL checkpoints. checkpoint_warning Enables warnings if checkpoint segments are filled more frequently than this. max_wal_size Sets the WAL size that triggers a checkpoint. min_wal_size Sets the minimum size to shrink the WAL to.

Write-Ahead Log / Settings

commit_delay Sets the delay in microseconds between transaction commit and flushing WAL to disk. commit_siblings Sets the minimum concurrent open transactions before performing commit_delay. fsync Forces synchronization of updates to disk. full_page_writes Writes full pages to WAL when first modified after a checkpoint. synchronous_commit Sets the current transaction’s synchronization level. wal_buffers Sets the number of disk-page buffers in shared memory for WAL. wal_compression Compresses full-page writes written in WAL file. wal_level Set the level of information written to the WAL. wal_log_hints Writes full pages to WAL when first modified after a checkpoint, even for a non-critical modifications. wal_sync_method Selects the method used for forcing WAL updates to disk. wal_writer_delay Time between WAL flushes performed in the WAL writer. wal_writer_flush_after Amount of WAL written out by WAL writer that triggers a flush.

About pgDash

pgDash is a modern, in-depth monitoring solution designed specifically for PostgreSQL deployments. pgDash shows you information and metrics about every aspect of your PostgreSQL database server, collected using the open-source tool pgmetrics.

pgDash provides core reporting and visualization functionality, including collecting and displaying PostgreSQL information and providing time-series graphs, detailed reports, alerting, teams and more.