📌 Based on PostgreSQL 14 installed with apt-get (Advanced Packaging Tool) on Ubuntu
Parameter names with yellow shading require a restart when changed
FILE LOCATIONS
Parameter | Default Value | Description |
data_directory | /var/lib/postgresql/14/main | Data directory path |
external_pid_file | /var/run/postgresql/14-main.pid | File that stores the postmaster PID |
hba_file | /etc/postgresql/14/main/pg_hba.conf | Path to the hba configuration file |
ident_file | /etc/postgresql/14/main/pg_ident.conf | Path to the ident configuration file |
CONNECTIONS AND AUTHENTICATION
Connection Settings
Parameter | Default Value | Range | Description |
bonjour | off | ㅤ | Enables advertising the server via Bonjour |
bonjour_name | ㅤ | ㅤ | Sets the Bonjour service name |
listen_addresses | localhost | ㅤ | Host name or IP address to listen on
* ⇒ all available IPs
0.0.0.0 ⇒ accept IPv4 addresses
:: ⇒ accept IPv6 addresses |
max_connections | 100 | 1~262143 | Maximum number of concurrent connections |
port | 5432 | 1~65535 | TCP port the server listens on |
superuser_reserved_connections | 3 | 0~262143 | Number of connection slots reserved for superusers |
unix_socket_directories | /var/run/postgresql | ㅤ | Directory where the Unix-domain socket is created |
unix_socket_group | ㅤ | ㅤ | Sets the owning group of the Unix-domain socket.
Use together with unix_socket_permissions to control access |
unix_socket_permissions | 0777 | ㅤ | Sets access permissions for the Unix-domain socket
0777 ⇒ anyone can connect
0770 ⇒ only the user and group can connect (see unix_socket_group)
0700 ⇒ only the user can connect |
TCP settings
Parameter | Default Value | Range | Description |
client_connection_check_interval | 0ms | 0~2147483647ms | [Added in 14] Interval for checking the client connection while a query runs |
tcp_keepalives_count | 0 | 0~2147483647 | Maximum number of TCP keepalive retransmits |
tcp_keepalives_idle | 0s | 0~2147483647s | Time between issuing TCP keepalives. |
tcp_keepalives_interval | 0s | 0~2147483647s | Time between TCP keepalive retransmits. |
tcp_user_timeout | 0ms | 0~2147483647ms | TCP User timeout |
Authentication
Parameter | Default Value | Range | Description |
authentication_timeout | 60s | 1~600s | Maximum time allowed to complete client authentication |
db_user_namespace | off | ㅤ | Enables per-database user names |
krb_caseins_users | off | ㅤ | Whether Kerberos and GSSAPI user names are treated case-insensitively |
krb_server_keyfile | FILE:/etc/postgresql-common/krb5.keytab | ㅤ | Location of the Kerberos server key file |
password_encryption | scram-sha-256 | scram-sha-256
md5 | [Default changed in 14] Selects the password encryption algorithm |
SSL
Parameter | Default Value | Range | Description |
ssl | on | ㅤ | Enables SSL connections |
ssl_ca_file | ㅤ | ㅤ | Location of the SSL certificate authority file |
ssl_cert_file | /etc/ssl/certs/ssl-cert-snakeoil.pem | ㅤ | Location of the SSL server certificate file |
ssl_ciphers | HIGH:MEDIUM:+3DES:!aNULL | ㅤ | Sets the list of allowed SSL ciphers |
ssl_crl_dir | ㅤ | ㅤ | [Added in 14] Location of the SSL certificate revocation list directory |
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 | prime256v1 | ㅤ | Sets the curve to use for ECDH |
ssl_key_file | /etc/ssl/private/ssl-cert-snakeoil.key | ㅤ | Location of the SSL server private key file |
ssl_max_protocol_version | ㅤ | TLSv1, TLSv1.1, TLSv1.2, TLSv1.3 | Sets the maximum SSL/TLS protocol version to use |
ssl_min_protocol_version | TLSv1.2 | TLSv1, TLSv1.1, TLSv1.2, TLSv1.3 | Sets the minimum SSL/TLS protocol version to use |
ssl_passphrase_command | ㅤ | ㅤ | Command to obtain the SSL passphrase |
ssl_passphrase_command_supports_reload | off | ㅤ | Whether ssl_passphrase_command is also used during a server reload |
ssl_prefer_server_ciphers | on | ㅤ | Whether to use the server's SSL cipher preferences |
RESOURCE USAGE (except WAL)
Memory
Parameter | Default Value | Range | Description |
autovacuum_work_mem | -1 | -1~2147483647kB | Maximum memory used by each autovacuum worker.
-1 means use maintenance_work_mem.
• For collecting dead tuple identifiers, autovacuum can use at most 1GB of memory, so setting autovacuum_work_mem higher than that does not affect the number of dead tuples autovacuum can collect while scanning a table. |
dynamic_shared_memory_type | posix | posix, sysv, windows, mmap | Selects the dynamic shared memory implementation used |
hash_mem_multiplier | 1.0 | 1~1000.0 | Multiple of work_mem to use for hash tables |
huge_pages | try | on, off, try | Use huge pages (Linux and Windows only) |
huge_page_size | 0 | 0~2147483647kB | [Added in 14] Huge page size |
logical_decoding_work_mem | 65536 (64MB) | 64(1MB)
~ 2147483647kB | Maximum memory used for logical decoding |
maintenance_work_mem | 65536 (64MB) | 1024(1MB)
~ 2147493647kB | Maximum memory used for maintenance operations
• Specifies the maximum amount of memory used by maintenance operations such as VACUUM , CREATE INDEX and ALTER TABLE ADD FOREIGN KEY .
• A database session can run only one of these operations at a time, and an installation normally does not run many of them concurrently, so it is safe to set this value much larger than work_mem.
• A larger value can improve the performance of VACUUM and of restoring database dumps.
• During autovacuum, up to autovacuum_max_workers times this amount can be allocated, so set it with care. |
min_dynamic_shared_memory | 0MB | 0~2147483647MB | [Added in 14] Size of dynamic shared memory reserved at startup |
max_prepared_transactions | 0 | 0~252143 | Maximum number of simultaneously prepared transactions |
max_stack_depth | 100kB | 100~2147483647kB | Maximum stack depth (KB) |
shared_buffers | 1024 (8MB) | 16 (128kB)
~ 1073741823 (8589934584kB) | • Sets the number of shared memory buffers (amount of memory) used by the server.
• Must be at least 128KB; for good performance it usually needs to be set much higher than the minimum.
• This value can only be set at server start.
• On a dedicated database server with 1GB or more of RAM, a reasonable starting value for shared_buffer is 25% of system memory. |
shared_memory_type | mmap | mmap, sysv, windows | Selects the shared memory implementation used for the main shared memory region |
temp_buffers | 1024 (8MB) | 100 (800kB)
~ 1073741823 (8589934584kB) | • Sets the maximum number of temporary buffers (amount of memory) used by each session.
• These are session-local buffers used only for access to temporary tables.
• It can be changed within an individual session, but only before the first use of temporary tables in that session. (Later attempts have no effect on that session.) |
work_mem | 4096kB | 64~2147483647kB | • Sets the maximum memory to use for query workspaces.
• Sets the maximum amount of memory a query operation (e.g. a sort or hash table) uses before writing to temporary disk files.
• A complex query can run several sort or hash operations in parallel, so the total memory used can be many times the work_mem value. |
Disk
Kernel Resources
Parameter | Default Value | Range | Description |
temp_file_limit | -1 | -1~2147483647kB | • Limits the total size of all temporary files used by each process.
• Specifies the maximum disk space a process can use for temporary files (e.g. sort and hash temporary files, or the storage file for a held cursor); a transaction that exceeds this size is canceled.
• It limits the total space used by all temporary files of a PostgreSQL process, and only superusers can change this setting.
• Unlike temporary files used behind the scenes during query execution, disk space used by explicit temporary tables does not count against this limit. |
Parameter | Default Value | Range | Description |
max_files_per_process | 1000 | 64~2147483647 | Maximum number of files each server process can have open at the same time |
Cost-Based Vacuum Delay
Parameter | Default Value | Range | Description |
vacuum_cost_delay | 0 | 0~100 ms | Specifies the cost delay used for Vacuum operations.
0 ⇒ disable |
vacuum_cost_limit | 200 | 1~10000 | Accumulated cost that puts Vacuum to sleep |
vacuum_cost_page_dirty | 20 | 0~10000 | I/O cost of flushing a dirty page |
vacuum_cost_page_hit | 1 | 0~10000 | Cost of looking up a page in the buffer cache for Vacuum |
vacuum_cost_page_miss | 2 | 0~10000 | Cost of reading a page from disk for Vacuum |
Background Writer
Parameter | Default Value | Range | Description |
bgwriter_delay | 200ms | 10ms~10000ms | • Indicates the bgwriter run interval.
• This is how long it sleeps after cleaning up to bgwriter_lru_maxpages dirty pages. |
bgwriter_flush_after | 64 (512kB) | 0~256(2MB) | Number of pages after which previously performed writes are flushed to disk
0 ⇒ disable |
bgwriter_lru_maxpages | 100 | 0 ~1073741823 | • Indicates the maximum number of buffer pages that can be written to disk in one round.
• Setting it to 0 disables background writing, except for checkpoint activity. |
bgwriter_lru_multiplier | 2.0 | 0.0~10.0 | • Estimates how many buffers to write to disk in each round based on the number of buffers needed recently. (It is calculated by multiplying the average number of buffers needed by bgwriter_lru_multiplier.)
• Smaller bgwriter_lru_maxpages and bgwriter_lru_multiplier values reduce the extra I/O caused by bgwriter, but make it more likely that server processes must issue writes themselves, which delays interactive queries. |
Asynchronous Behavior
Parameter | Default Value | Range | Description |
backend_flush_after | 0kB | 0~256(2MB) | Number of pages after which previously performed writes are flushed to disk
0 ⇒ disable |
effective_io_concurrency | 1 | 0~1000 | Number of concurrent requests the disk subsystem can handle efficiently
0 ⇒ disable |
maintenance_io_concurrency | 10 | 0~1000 | Variant of effective_io_concurrency used for maintenance work
0 ⇒ disable |
max_parallel_maintenance_workers | 2 | 0~1024 | Maximum number of parallel processes per maintenance operation |
max_parallel_workers | 8 | 0~1024 | Maximum number of parallel workers that can be active at one time
Setting it higher than max_worker_processes has no effect. |
max_parallel_workers_per_gather | 2 | 0~1024 | Maximum number of parallel processes per executor node |
max_worker_processes | 8 | 0~262143 | Maximum number of concurrent worker processes |
old_snapshot_threshold | -1 | -1
~ 86400min(60d) | Time after which a snapshot can no longer read pages changed since it was taken
-1 ⇒ disable, 0 ⇒ immediate |
parallel_leader_participation | on | ㅤ | Whether Gather and Gather Merge also run subplans |
WRITE-AHEAD LOG
Settings
Parameter | Default Value | Range | Description |
commit_delay | 0 | 0~100000ms | Delay between a transaction commit and flushing WAL to disk |
commit_siblings | 5 | 1~1000 | Minimum number of concurrent open transactions required before performing commit_delay |
fsync | on | ㅤ | Forces synchronization of updates to disk |
full_page_writes | on | ㅤ | Whether to write full pages to WAL on the first modification after a checkpoint |
synchronous_commit | on | local, remote_write, remote_apply, on, off | Sets the synchronization level of the current transaction
• Sets whether "success" is returned on commit only after the WAL records have been written to disk.
• When off, there is a delay before the transaction is actually made durable. (The maximum delay is 3 times wal_writer_delay.)
• Off is recommended if database performance matters |
wal_buffers | -1 | -1~262143 (2097144kB) | Amount of shared memory used for WAL data not yet written to disk
Sets the number of disk-page buffers in shared memory for WAL
-1 ⇒ set based on shared_buffers
• A larger value improves write performance on busy servers where many clients commit at once |
wal_compression | off | ㅤ | Whether to compress full-page writes recorded in WAL files |
wal_init_zero | on | ㅤ | Whether to fill a new WAL file with 0s before first use |
wal_level | replica | minimal, replica, logical | Sets the level of information written to WAL |
wal_log_hints | off | ㅤ | Whether to write full pages to WAL on the first modification after a checkpoint, even for non-critical modifications |
wal_recycle | on | ㅤ | Whether to recycle WAL files by renaming them |
wal_skip_threshold | 2048(2MB) | 0~2147483647kB | Minimum size of a new file to fsync instead of writing WAL
(when wal_level=minimal) |
wal_sync_method | fdatasync | open_datasync, fdatasync, fsync, open_sync | Sets the method used to force WAL updates to disk |
wal_writer_delay | 200ms | 1ms ~ 10000ms(10s) | Interval at which the WAL writer flushes WAL
• This is the sleep time between WAL writer rounds; an efficient sleep delay setting is 10ms.
• Setting wal_writer_delay to a value that is not a multiple of 10 is the same as setting it to the next higher multiple of 10. |
wal_writer_flush_after | 128(1MB) | 0 ~ 2147283647 (17179869176kB) | Amount of WAL written by the WAL writer that triggers a flush
0 ⇒ disable |
Checkpoints
Parameter | Default Value | Range | Description |
checkpoint_completion_target | 0.9 | 0 ~ 1 | • During a checkpoint, dirty buffers are written out spread over a period of time; this parameter sets that period.
• With 0.5, each checkpoint is expected to finish after about half of the time until the next checkpoint starts has passed. |
checkpoint_flush_after | 32 (256kB) | 0 ~ 256(2MB) | • Number of pages after which previously performed writes are flushed to disk.
• During a checkpoint, whenever more data than this value has been written, the OS is made to issue the writes to the underlying storage.
• This limits the amount of dirty data in the kernel's page cache, reducing the chance of stalls when fsync is issued at the end of the checkpoint or when the OS writes data back in larger batches in the background. |
checkpoint_timeout | 300s | 30~86400s(1day) | Maximum time between automatic WAL checkpoints |
checkpoint_warning | 30s | 0 ~ 2147483647s | • A parameter that sets a threshold between checkpoints.
• If checkpoints occur closer together than this value, the system writes a warning to the server log recommending that the user increase max_wal_size. |
max_wal_size | 1024MB | 2 ~ 2147483647MB | Maximum WAL size at which a checkpoint is triggered. |
min_wal_size | 80MB | 2 ~ 2147483647MB | Minimum WAL size for checkpoints. As long as WAL stays below this size, old WAL files are not removed but are always recycled at checkpoints for future use. |
Archiving
Parameter | Default Value | Range | Description |
archive_command | ㅤ | ㅤ | • Command set to archive WAL files.
• %p : path name of the file to archive
• %f : file name of the file to archive
• When a WAL segment file is to be archived, the command specified in archive_command runs. If the archive command returns 0, PostgreSQL considers the file successfully archived and then deletes or recycles the WAL segment file. If it returns anything other than 0, it retries periodically until it succeeds. |
archive_mode | off | always, on, off | • Sets whether WAL files are archived using archive_command.
• When set to always, the archiver is also enabled during archive recovery or standby mode. |
archive_timeout | 0s | 0 ~ 1073741823s | • A parameter that makes the server switch to a new WAL segment file periodically
• When the time set by this parameter (N seconds) has elapsed, the WAL segment file is forcibly switched.
• Archived files closed early by a forced switch are still the same length as full files, so setting a very short time can make archive storage usage spike. (1 minute is generally recommended; set it with disk space and latency in mind. |
Archive Recovery
Parameter | Default Value | Range | Description |
archive_cleanup_command | ㅤ | ㅤ | Sets a shell command to run at every restartpoint |
recovery_end_command | ㅤ | ㅤ | Sets a shell command to run once at the end of recovery |
restore_command | ㅤ | ㅤ | Sets the shell command called to retrieve archived WAL files |
Recovery Target
Parameter | Default Value | Range | Description |
recovery_target | ㅤ | ㅤ | Set to "immediate" to end recovery as soon as a consistent state is reached |
recovery_target_action | pause | pause, promote, shutdown | Sets the action to take when the recovery target is reached |
recovery_target_inclusive | on | ㅤ | Whether to include or exclude transactions at the recovery target |
recovery_target_lsn | ㅤ | ㅤ | Sets the LSN of the write-ahead log location up to which recovery proceeds |
recovery_target_name | ㅤ | ㅤ | Sets the named restore point up to which recovery proceeds |
recovery_target_time | ㅤ | ㅤ | Sets the maximum timestamp up to which recovery proceeds |
recovery_target_timeline | latest | current, latest | Specifies the timeline to recover into |
recovery_target_xid | ㅤ | ㅤ | Sets the transaction ID up to which recovery proceeds |
REPLICATION
Sending Servers
Parameter | Default Value | Range | Description |
max_replication_slots | 10 | 0~262143 | Maximum number of replication slots defined at the same time |
max_slot_wal_keep_size | -1 | -1~2147483647MB | Maximum WAL size that replication slots can reserve
-1 ⇒ disable |
max_wal_senders | 10 | 0~262143 | Maximum number of concurrently running WAL sender processes |
track_commit_timestamp | off | ㅤ | Whether to collect transaction commit times |
wal_keep_size | 0 | 0~2147483647MB | Size of WAL files kept for standby servers
0 ⇒ disable |
wal_sender_timeout | 60000ms (1min) | 0~2147483647ms | Maximum time to wait for WAL replication
0 ⇒ disable |
Primary Server (under 13 : Master Server)
Parameter | Default Value | Range | Description |
synchronous_standby_names | ㅤ | ㅤ | Number of synchronous standbys and list of potential synchronous standby names |
vacuum_defer_cleanup_age | 0 | 0~1000000 | Number of transactions by which Vacuum and HOT cleanup should be deferred |
Standby Servers
Parameter | Default Value | Range | Description |
hot_standby | on | on, off | Whether to allow connections and queries during recovery |
hot_standby_feedback | off | ㅤ | Whether a hot standby sends feedback to the primary to avoid query conflicts |
max_standby_archive_delay | 30000ms | -1~2147483647ms | Maximum delay before a hot standby server cancels queries while processing archived WAL data |
max_standby_streaming_delay | 30000ms | -1~2147483647ms | Maximum delay before a hot standby server cancels queries while processing streamed WAL data |
primary_conninfo | ㅤ | ㅤ | Sets the connection string used to connect to the sending server |
primary_slot_name | ㅤ | ㅤ | Sets the name of the replication slot to use on the sending server |
promote_trigger_file | ㅤ | ㅤ | Sets the file name whose presence ends recovery in standby mode |
recovery_min_apply_delay | 0ms | 0~2147483647ms | Minimum delay for applying changes during recovery |
wal_receiver_create_temp_slot | off | ㅤ | Whether the WAL receiver creates a temporary replication slot when no permanent slot is configured |
wal_receiver_status_interval | 10s | 0~2147483s | Maximum interval between WAL receiver status reports to the sending server
0 ⇒ disable |
wal_receiver_timeout | 60000ms | 0~2147483647ms | Maximum time to wait to receive data from the sending server
0 ⇒ disable |
wal_retrieve_retry_interval | 5000ms | 1~2147483647ms | Time to wait before retrying WAL retrieval after a failed attempt |
Subscribers
Parameter | Default Value | Range | Description |
max_logical_replication_workers | 4 | 0~262143 | Maximum number of logical replication worker processes |
max_sync_workers_per_subscription | 2 | 0~262143 | Maximum number of table synchronization workers per subscription |
QUERY TUNING
Planner Method Configuration
Parameter | Default Value | Description |
enable_async_append | on | [Added in 14] Whether to use async append plans |
enable_bitmapscan | on | Whether to use bitmap-scan plans |
enable_gathermerge | on | [Added in 14] Whether to use gather merge plans |
enable_hashagg | on | Whether to use hash aggregation plans |
enable_hashjoin | on | Whether to use hash join plans |
enable_incremental_sort | on | Whether to use incremental sort plans |
enable_indexscan | on | Whether to use index scan plans |
enable_indexonlyscan | on | Whether to use index-only-scan plans |
enable_material | on | Whether to use materialization plans |
enable_memoize | on | [Added in 14] Whether to use memoization plans |
enable_mergejoin | on | Whether to use merge join plans |
enable_nestloop | on | Whether to use nested-loop join plans |
enable_parallel_append | on | Whether to use parallel append plans |
enable_parallel_hash | on | Whether to use parallel hash plans |
enable_partition_pruning | on | Whether to use plan-time and execution-time partition pruning |
enable_partitionwise_join | off | Whether to use partitionwise join |
enable_partitionwise_aggregate | off | Whether to use partitionwise aggregation and grouping |
enable_seqscan | on | Whether to use sequential-scan plans |
enable_sort | on | Whether to use explicit sort plans |
enable_tidscan | on | Whether to use TID scan plans |
Planner Cost Constants
Parameter | Default Value | Range | Description |
cpu_index_tuple_cost | 0.005 | 0~1.79769e+308 | Estimated cost of processing each index entry during an index scan |
cpu_operator_cost | 0.0025 | 0~1.79769e+308 | Estimated cost of processing each operator or function call |
cpu_tuple_cost | 0.01 | 0~1.79769e+308 | Estimated cost of processing each tuple (row) |
effective_cache_size | 524288 (4GB) | 1(8kB)~2147483647
(17179869176kB) | Assumption about the total size of the data cache |
jit_above_cost | 100000 | -1~1.79769e+308 | Perform JIT compilation when the query cost exceeds the specified value
-1 ⇒ disable |
jit_inline_above_cost | 500000 | -1~1.79769e+308 | Perform JIT inlining when the query cost exceeds the specified value
-1 ⇒ disable |
jit_optimize_above_cost | 500000 | -1~1.79769e+308 | Optimize JIT-compiled functions when the query cost exceeds the specified value
-1 ⇒ disable |
min_parallel_index_scan_size | 64(512kB) | 0~715827882 (5726623056kB) | Minimum amount of index data for a parallel scan |
min_parallel_table_scan_size | 1024(8MB) | 0~715827882 (5726623056kB) | Minimum amount of table data for a parallel scan |
parallel_setup_cost | 1000.0 | 0~1.79769e+308 | Estimated cost of starting worker processes for a parallel query |
parallel_tuple_cost | 0.1 | 0~1.79769e+308 | Estimated cost of passing each tuple (row) from a worker to the leader backend |
random_page_cost | 4.0 | 0~1.79769e+308 | Estimated cost of a non-sequentially fetched disk page |
seq_page_cost | 1.0 | 0~1.79769e+308 | Estimated cost of a sequentially fetched disk page |
Genetic Query Optimizer
Parameter | Default Value | Range | Description |
geqo | on | ㅤ | Whether to enable genetic query optimization |
geqo_effort | 5 | 1~10 | Used to set default values for the other GEQO parameters |
geqo_generations | 0 | 0~2147483647 | Number of iterations of the algorithm |
geqo_pool_size | 0 | 0~2147483647 | Number of individuals in the population |
geqo_seed | 0.0 | 0.0~1.0 | seed for random path selection |
geqo_selection_bias | 2.0 | 1.5~2.0 | Selective pressure within the population |
geqo_threshold | 12 | 2~2147483647 | Threshold of FROM items at which GEQO is used |
Other Planner Options
Parameter | Default Value | Range | Description |
constraint_exclusion | partition | on, off, partition | Whether to use constraints to optimize queries
• on : examine constraints for all tables |
cursor_tuple_fraction | 0.1 | 0.0~1.0 | Estimated fraction of a cursor's rows that will be retrieved |
default_statistics_target | 100 | 1~10000 | Sets the default statistics target
• A larger value increases the time ANALYZE takes but improves the estimates |
from_collapse_limit | 8 | 1~2147483647 | Size of the FROM list beyond which subqueries are not collapsed |
jit | on | ㅤ | Whether to use JIT compilation |
join_collapse_limit | 8 | 1~2147483647 | Size of the FROM list beyond which JOIN constructs are not flattened
1 ⇒ disable |
plan_cache_mode | auto | auto, force_generic_plan, force_custom_plan | Controls the choice between custom and generic plans |
REPORTING AND LOGGING
Where to Log
Parameter | Default Value | Range | Description |
event_source | PostgreSQL | ㅤ | Keyword that identifies messages in the event log (Windows) |
log_destination | stderr | stderr, csvlog, syslog, eventlog | • Sets the destination for server log output.
• PostgreSQL supports several methods for logging server messages, including stderr, csvlog and syslog. (On Windows, eventlog is also supported.) |
log_diretory | log | ㅤ | Determines the directory for log files when logging_collector is enabled. |
log_file_mode | 0600 | ㅤ | Permissions for log files |
log_filename | postgresql-%Y-%m-%d_%H%M%S.log | ㅤ | Log file name pattern |
logging_collector | off | ㅤ | A parameter that determines whether to start a background process that captures log messages sent to stderr or csvlog into files. |
log_rotation_age | 1440min (1d) | 0~35791394min | Rotate log files after the set value (time)
0 ⇒ disable |
log_rotation_size | 10240kB (10MB) | 0~2097151kB | Rotate log files after the set value (size)
0 ⇒ disable |
log_truncate_on_rotation | off | ㅤ | Whether to truncate an existing log file of the same name during rotation |
syslog_facility | local0 | local0~7 | Sets the syslog "facility" when syslog is used |
syslog_ident | postgres | ㅤ | Keyword that identifies messages in syslog |
syslog_sequence_numbers | on | ㅤ | Whether to add sequence numbers to syslog messages to avoid duplicate suppression |
syslog_split_messages | on | ㅤ | Whether to split syslog messages by line and at 1024byte |
When to Log
Parameter | Default Value | Range | Description |
log_min_duration_sample | -1ms | -1~2147483647ms | Minimum execution time at which a sample of statements is logged.
Sampling is determined by log_statement_sample_rate
-1 ⇒ disable , 0 ⇒ all |
log_min_duration_statement | -1ms | -1~2147483647ms | Minimum execution time at which all statements are logged
-1 ⇒ disable, 0 ⇒ all |
log_min_error_statement | error | debug1~5, info, notice, warning, error, log, fatal, panic | Logs all statements that cause an error at or above the set level |
log_min_messages | warning | debug1~5, info, notice, warning, error, log, fatal, panic | Sets the message levels that are logged |
log_statement_sample_rate | 1.0 | 0.0~1.0 | Fraction of statements whose execution time exceeds log_min_duration_sample that are logged
0 ⇒ disable, 1 ⇒ all, 0.5 ⇒ log one out of two |
log_transaction_sample_rate | 0.0 | 0.0~1.0 | Fraction of transactions whose statements are all logged
0 ⇒ disable, 1 ⇒ all |
What to Log
Parameter | Default Value | Range | Description |
debug_pretty_print | on | ㅤ | Whether to indent parse and plan tree displays |
debug_print_parse | off | ㅤ | Whether to log the parse tree of each query |
debug_print_plan | off | ㅤ | Whether to log the execution plan of each query |
debug_print_rewritten | off | ㅤ | Whether to log the rewritten parse tree of each query |
log_autovacuum_min_duration | -1ms | -1~2147483647ms | [Added in 14]
• Sets the minimum execution time at which autovacuum actions are logged.
• Runs that exceed log_autovacuum_min_duration are logged, so this parameter helps you check autovacuum activity.
• -1 : disable logging of autovacuum actions
• 0 : log all autovacuum actions |
log_checkpoints | off | ㅤ | Whether to log each checkpoint |
log_connections | off | ㅤ | Whether to log successful connections |
log_disconnections | off | ㅤ | Whether to log session end (including duration) |
log_duration | off | ㅤ | Whether to log the duration of completed SQL statements |
log_error_verbosity | default | terse, default, verbose | Sets the verbosity of logged messages |
log_hostname | off | ㅤ | Whether to log the host name in connection logs |
log_line_prefix | %m [%p] %q%u@%d | ㅤ | Sets the prefix added to each log line |
log_lock_waits | off | ㅤ | Whether to log long lock waits |
log_parameter_max_length | -1 | -1~1073741823B | When logging statements, limits logged parameter values to the first N bytes (set value)
0 ⇒ disable, -1 ⇒ full |
log_parameter_max_length_on_error | 0 | -1~1073741823B | When reporting errors, limits logged parameter values to the first N bytes (set value)
0 ⇒ disable, -1 ⇒ full |
log_recovery_conflict_waits | off | ㅤ | [Added in 14] Whether to log a message when the startup process waits longer than deadlock_timeout for recovery conflicts |
log_replication_commands | off | ㅤ | Whether to log each replication command |
log_statement | none | none, ddl, mod, all | Sets the type of statements logged |
log_temp_files | -1kB | -1~2147493647kB | Logs use of temporary files larger than the set value
-1 ⇒ disable, 0 ⇒ all |
log_timezone | Etc/UTC | ㅤ | ㅤ |
PROCESS TITLE
Parameter | Default Value | Range | Description |
cluster_name | 14/main | ㅤ | Sets the cluster name included in the process title |
update_process_title | on | ㅤ | Whether to update the process title to show the active SQL command |
STATISTICS
Query and Index Statistics Collector
Parameter | Default Value | Range | Description |
stats_temp_directory | /var/run/postgresql/14-main.pg_stat_tmp | ㅤ | • Indicates the temporary storage path for statistics. (Relative or absolute path)
• These files are updated hundreds of times per second, so placing them on a RAM-based file system can reduce physical I/O requirements and improve performance.
• When the server shuts down cleanly, a copy of the statistics data is stored permanently in the pg_stat subdirectory, so statistics are kept across server restarts. |
track_activities | on | ㅤ | • Indicates whether to collect statistics on sessions. (Only superusers can change this setting)
• Collects information about the command currently being executed by each session and when it started.
• Only superusers and the user who owns the session can see this information. |
track_activity_query_size | 1024B | 100~1048576B | • Sets the number of bytes used to collect the currently executing command (pg_stat_activity.query) of each active session.
• Used as the threshold for truncating queries in pg_stat_activity and pg_stat_statements. |
track_counts | on | ㅤ | • Sets whether to collect statistics on table and index access. (Only superusers can change this setting)
• The autovacuum process uses these statistics to perform vacuum, so it is on by default and the information is collected. |
track_functions | none | none, pl, all | • A parameter for collecting statistics on user-defined functions. (Only superusers can change this setting)
• Collects information such as the number of function calls and the time spent.
• pl : collect only procedural-language functions
• all : also collect SQL and C-language functions
• none : disabled |
track_io_timing | off | ㅤ | • Indicates whether to collect timing statistics for database I/O activity. (Only superusers can change this setting)
• When set to on, it repeatedly queries the operating system for the current time, which can add significant overhead, so the default is off. (You can measure the timing overhead with the pg_test_timing tool.) |
track_wal_io_timing | off | ㅤ | [Added in 14] Whether to collect timing statistics for WAL I/O activity |
Monitoring
Parameter | Default Value | Range | Description |
compute_query_id | auto | auto, on, off | [Added in 14] Computes query identifiers |
log_executor_stats | off | ㅤ | Whether to write executor performance statistics to the server log |
log_parser_stats | off | ㅤ | Whether to write parser performance statistics to the server log |
log_planner_stats | off | ㅤ | Whether to write planner performance statistics to the server log |
log_statement_stats | off | ㅤ | Whether to write cumulative performance statistics to the server log |
AUTOVACUUM
Parameter | Default Value | Range | Description |
autovacuum | on | ㅤ | • Sets whether to start the autovacuum subprocess (autovacuum launcher daemon). (The default of this parameter is on, but track_counts must also be enabled for autovacuum to work.)
• Even when set to off, the system starts autovacuum processes when needed.
• You can disable autovacuum for individual tables by changing table storage parameters. |
autovacuum_analyze_scale_factor | 0.1 | 0.0~100.0 | Fraction of the table size to add to autovacuum_analyze_threshold when deciding whether to trigger ANALYZE |
autovacuum_analyze_threshold | 50 | 0~2147483647 | Minimum number of inserted, updated or deleted tuples needed to trigger ANALYZE |
autovacuum_freeze_max_age | 200,000,000 | 100,000
~2,000,000,000 | • Sets the upper limit of transaction ID age at which autovacuum is forced to run. (You can lower it for individual tables by changing table storage parameters.)
• Even when autovacuum is disabled, autovacuum processes are started once this value is exceeded. |
autovacuum_max_workers | 3 | 1~262143 | • Sets the maximum number of autovacuum worker processes that can run at one time.
• Each autovacuum worker process uses its own CPU, memory and I/O. |
autovacuum_multixact_freeze_max_age | 400000000 | 10000~2000000000 | Maximum age that a table's pg_class.relminmxid field can reach before vacuum is forced to prevent multixact ID wraparound within the table |
autovacuum_naptime | 60s | 1~2147483s | • Specifies the sleep time between autovacuum runs.
• In each round, the daemon checks the databases and runs VACUUM & ANALYZE commands where needed. |
autovacuum_vacuum_cost_delay | 2ms | -1~100ms | • Specifies the cost delay used for autovacuum operations. (If -1, vacuum_cost_delay is used instead)
• When autovacuum_vacuum_cost_limit is exceeded, a delay of autovacuum_vacuum_cost_delay occurs. |
autovacuum_vacuum_cost_limit | -1 | -1~10000 | • Specifies the cost limit used for autovacuum operations. (If -1, vacuum_cost_limit is used instead)
• It is distributed proportionally among autovacuum workers, so the sum of the limits for each worker process does not exceed this parameter value. |
autovacuum_vacuum_insert_scale_factor | 0.2 | 0.0~100.0 | Fraction of the table size to add to autovacuum_vacuum_insert_threshold when deciding whether to trigger Vacuum |
autovacuum_vacuum_insert_threshold | 1000 | -1~2147483647 | Number of inserted tuples needed to trigger VACUUM
-1 ⇒ disable (vacuum is not triggered based on inserts) |
autovacuum_vacuum_scale_factor | 0.2 | 0~100 | • Number of updated or deleted tuples before autovacuum (retuple ratio)
• The default is 0.2, so a VACUUM is triggered when it exceeds 20% of the table size. |
autovacuum_vacuum_threshold | 50 | 0~2147483647 | Indicates the threshold number of updated or deleted tuples before autovacuum. (When this value is exceeded, autovacuum/analyze must run respectively.) |
CLIENT CONNECTION DEFAULTS
Statement Behavior
Parameter | Default Value | Range | Description |
bytea_output | hex | hex, escape | Sets the output format for the bytea type |
check_function_bodies | on | ㅤ | Whether to validate the body string during CREATE FUNCTION and CREATE PROCEDURE |
client_min_messages | notice | debug1~5, log, notice, warning, error | Message levels sent to the client |
default_table_access_method | heap | ㅤ | Sets the default table access method for new tables |
default_tablespace | ㅤ | ㅤ | Sets the default tablespace in which tables and indexes are created
NULL = default |
default_toast_compression | pglz | pglz, lz4 | [Added in 14] Data compression method (up to version 13, only pglz was supported)
To use the lz4 compression method, PostgreSQL must be built with --with-lz4 |
default_transaction_deferrable | off | ㅤ | Sets the default deferrable status of new transactions |
default_transaction_isolation | read committed | serializable, repeatable read,
read committed,
read uncommitted | Sets the transaction isolation level of new transactions |
default_transaction_read_only | off | ㅤ | Sets the default read-only status of new transactions |
gin_pending_list_limit | 4096kB | 64~2147493647kB | Maximum size of the pending list for GIN indexes |
idle_in_transaction_session_timeout | 0ms | 0~2147493647ms | Maximum idle time allowed between queries within a transaction
0 ⇒ disable |
idle_session_timeout | 0ms | 0~2147493647ms | [Added in 14] Maximum idle time allowed between queries when not in a transaction
0 ⇒ disable |
lock_timeout | 0ms | 0~2147483647ms | Maximum time allowed to wait for a lock
0 ⇒ disable |
row_security | on | ㅤ | Whether to use row security |
search_path | "$user", public | ㅤ | Order in which schemas are searched when an object is referenced by a simple name with no schema specified |
session_replication_role | origin | origin, replica, local | Sets the session behavior for triggers and rewrite rules |
statement_timeout | 0ms | 0~2147493647ms | Sets the maximum allowed duration of a statement
0 ⇒ disable |
temp_tablespaces | ㅤ | ㅤ | Sets the tablespace used for temp tables and sort files |
vacuum_failsafe_age | 1600000000 | 0~2100000000 | [Added in 14] Age that a table's pg_class.relfrozenxid field can reach before vacuum takes extraordinary measures to avoid system-wide transaction ID wraparound failure |
vacuum_freeze_min_age | 50,000,000 | 0~1,000,000,000 | Minimum age at which Vacuum should freeze a table row |
vacuum_freeze_table_age | 150,000,000 | 0~2,000,000,000 | Age at which Vacuum must scan the whole table to freeze tuples |
vacuum_multixact_failsafe_age | 1600000000 | 0~2100000000 | [Added in 14] Age that a table's pg_class.relminmxid field can reach before vacuum takes extraordinary measures to avoid system-wide multixact ID wraparound failure |
vacuum_multixact_freeze_min_age | 5000000 | 0~1000000000 | Minimum age at which Vacuum should freeze a MultiXactID in a table row |
vacuum_multixact_freeze_table_age | 150000000 | 0~2000000000 | Multixact age at which Vacuum must scan the whole table to freeze tuples |
xmlbinary | base64 | base64, hex | Sets how binary values are encoded in XML |
xmloption | content | content, document | Whether XML data in implicit parsing and serialization operations is treated as documents or content fragments |
Locale and Formatting
Parameter | Default Value | Range | Description |
client_encoding | sql_ascii | ㅤ | Sets the client-side character set encoding |
datestyle | iso, mdy | ㅤ | Sets the display format for date and time values |
default_text_search_config | pg_catalog.english | ㅤ | Sets the default text search configuration |
extra_float_digits | 1 | -15~3 | Sets the number of digits displayed for floating-point values |
intervalstyle | postgres | postgres, postgres_verbose, sql-standard, iso_8601 | Sets the display format for interval values |
lc_messages | en_US.UTF-8 | ㅤ | Sets the language in which messages are displayed |
lc_monetary | C | ㅤ | Sets the format for monetary amounts |
lc_numeric | en_US.UTF-8 | ㅤ | Sets the format for numbers |
lc_time | en_US.UTF-8 | ㅤ | Sets the format for dates and times |
timezone | GMT | ㅤ | Sets the time zone for displaying and interpreting timestamps |
timezone_abbreviations | Default | ㅤ | Selects the time zone abbreviations file |
Shared Library Preloading
Parameter | Default Value | Range | Description |
jit_provider | llvmjit | ㅤ | JIT provider to use |
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 the server |
Other Defaults
Parameter | Default Value | Range | Description |
dynamic_library_path | $libdir | ㅤ | Path for dynamically loadable modules |
extension_destdir | ㅤ | ㅤ | Additional path for loading extensions |
gin_fuzzy_search_limit | 0 | 0~2147483647 | Sets the maximum allowed result for exact search by GIN |
LOCK MANAGEMENT
Parameter | Default Value | Range | Description |
deadlock_timeout | 1000ms | 1~2147483647ms | Time to wait on a lock before checking for a deadlock |
max_locks_per_transaction | 64 | 10~2147483647 | Maximum number of locks per transaction
max_locks_per_transaction * (max_connections + max_prepared_transactions) |
max_pred_locks_per_page | 2 | 0~2147483647 | Maximum number of predicate-locked tuples per page |
max_pred_locks_per_relation | -2 | -2147483648
~2147483647 | Maximum number of predicate-locked pages and tuples per relation
0≤ ⇒ limit value, 0> ⇒ max_pred_locks_per_transaction / abs(set value) |
max_pred_locks_per_transaction | 64 | 10~2147483647 | Maximum number of predicate locks per transaction
max_pred_locks_per_transaction * (max_connections + max_prepared_transactions) |
VERSION AND PLATFORM COMPATIBILITY
Previous PostgreSQL Versions
Parameter | Default Value | Range | Description |
array_nulls | on | ㅤ | Whether to enable input of NULL elements in arrays |
backslash_quote | safe_encoding | on, off, safe_encoding | Whether to allow “\” in string literals |
escape_string_warning | on | ㅤ | Whether to warn about ”\” in ordinary string literals.
on ⇒ shown in literals, warning raised when standard_conforming_strings=off |
lo_compat_privileges | off | ㅤ | Whether to enable backward-compatibility mode for privilege checks on large objects |
quote_all_identifiers | off | ㅤ | Whether to quote all identifiers when generating SQL fragments |
standard_conforming_strings | on | ㅤ | Whether '...' strings treat “\” literally |
synchronize_seqscans | on | ㅤ | Whether to enable synchronized sequential scans |
Other Platforms and Clients
Parameter | Default Value | Range | Description |
transform_null_equals | off | ㅤ | Whether to treat “expr=NULL” as “expr IS NULL” |
ERROR HANDLING
Parameter | Default Value | Range | Description |
data_sync_retry | off | ㅤ | Whether to keep running after a failure to sync data files |
exit_on_error | off | ㅤ | Whether to terminate the session on any error |
recovery_init_sync_method | fsync | fsync, syncfs | [Added in 14] Sets how the data directory is synced before crash recovery |
restart_after_crash | on | ㅤ | Whether to reinitialize the server after a backend crash |
Related articles
