๋ณธ๋ฌธ ๋ฐ”๋กœ๊ฐ€๊ธฐ
์—‘์…ˆ ๊ฒฝ์Ÿ๋ ฅ/DB ์ธ์‚ฌ์ด๋“œ

DB ์ธ์‚ฌ์ด๋“œ | PostgreSQL ์ฐธ๊ณ ์ž๋ฃŒ - Configuration File : postgresql.conf

by EXEM 2022. 3. 30.

๐Ÿ“Œ Ubuntu์—์„œ apt-get(Advanced Packaging Tool)์„ ์‚ฌ์šฉํ•˜์—ฌ ์„ค์น˜ํ•œ PostgreSQL 14 ๋ฒ„์ „ ๊ธฐ์ค€์œผ๋กœ ์ •๋ฆฌ
     ํŒŒ๋ผ๋ฏธํ„ฐ ๋ช…์— ๋…ธ๋ž€์ƒ‰ ์Œ์˜์ฒ˜๋ฆฌ๋Š” ๋ณ€๊ฒฝ ์‹œ ์žฌ๊ธฐ๋™ํ•„์š”

 

FILE LOCATIONS

Parameter Default Value Description
data_directory /var/lib/postgresql/14/main Data Directory ๊ฒฝ๋กœ
external_pid_file /var/run/postgresql/14-main.pid postmaster PID ์ €์žฅ ํŒŒ์ผ
hba_file /etc/postgresql/14/main/pg_hba.conf hba ์„ค์ •ํŒŒ์ผ ๊ฒฝ๋กœ
ident_file /etc/postgresql/14/main/pg_ident.conf ident ์„ค์ •ํŒŒ์ผ ๊ฒฝ๋กœ

 


CONNECTIONS AND AUTHENTICATION

Connection Settings

Parameter Default Value Range Description
bonjour off   Bonjour๋ฅผ ํ†ตํ•œ ์„œ๋ฒ„ ๊ด‘๊ณ  ํ™œ์„ฑํ™”
bonjour_name     Bonjour ์„œ๋น„์Šค ์ด๋ฆ„ ์„ค์ •
listen_addresses localhost   Listenํ•  host-name ๋˜๋Š” IP์ฃผ์†Œ
* ⇒ ์‚ฌ์šฉ ๊ฐ€๋Šฅํ•œ ๋ชจ๋“  IP
0.0.0.0 ⇒ IPv4 ์ฃผ์†Œ ์ˆ˜์‹  ํ—ˆ์šฉ
:: ⇒ IPv6 ์ฃผ์†Œ ์ˆ˜์‹  ํ—ˆ์šฉ
max_connections 1~262143   ์ตœ๋Œ€ ๋™์‹œ ์—ฐ๊ฒฐ ์ˆ˜
port 5432 1~65535 ์„œ๋ฒ„๊ฐ€ ์ˆ˜์‹ ํ•˜๋Š” TCP Port ์„ค์ •
superuser_reserved_connections 3 0~262143 Superuser๋กœ ์˜ˆ์•ฝ๋œ ์—ฐ๊ฒฐ ์Šฌ๋กฏ ์ˆ˜
unix_socket_directories /var/run/postgresql   Unix ๋„๋ฉ”์ธ ์†Œ์ผ“์ด ์ƒ์„ฑ๋  ๋””๋ ‰ํ† ๋ฆฌ
unix_socket_group     Unix ๋„๋ฉ”์ธ ์†Œ์ผ“์˜ ์†Œ์œ  ๊ทธ๋ฃน์„ค์ •
unix_socket_permissions ํŒŒ๋ผ๋ฏธํ„ฐ์™€ ๊ฐ™์ด ์‚ฌ์šฉํ•˜์—ฌ ์•ก์„ธ์„œ ์ œ์–ด ์„ค์ • ๊ฐ€๋Šฅ
unix_socket_permissions 0777   Unix ๋„๋ฉ”์ธ ์†Œ์ผ“์˜ ์•ก์„ธ์Šค ๊ถŒํ•œ ์„ค์ •
0777 ⇒ ๋ชจ๋“  ์ ‘์†
0770 ⇒ ์‚ฌ์šฉ์ž ๋ฐ ๊ทธ๋ฃน๋งŒ ์ ‘์†(unix_socket_group ์ฐธ์กฐ)
0700 ⇒ ์‚ฌ์šฉ์ž๋งŒ ์ ‘์†

 

TCP settings

Parameter Default Value Range Description
client_connection_check_interval 0ms 0~2147483647ms [14๋ฒ„์ „์ถ”๊ฐ€] ์ฟผ๋ฆฌ ์‹คํ–‰ ๋™์•ˆ ์—ฐ๊ฒฐ ์ƒํƒœ ํ™•์ธ ์‹œ๊ฐ„ ๊ฐ„๊ฒฉ
tcp_keepalives_count 0 0~2147483647 ์ตœ๋Œ€ TCP ์—ฐ๊ฒฐ ์œ ์ง€ ์žฌ์ „์†ก ์ˆ˜
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 ํด๋ผ์ด์–ธํŠธ ์ธ์ฆ์„ ์™„๋ฃŒํ•˜๋Š”๋ฐ ํ—ˆ์šฉ๋˜๋Š” ์ตœ๋Œ€ ์‹œ๊ฐ„
db_user_namespace off   DB๋ณ„ ์‚ฌ์šฉ์ž ์ด๋ฆ„ ํ™œ์„ฑํ™”
krb_caseins_users off   Kerberos, GSSAPI ์‚ฌ์šฉ์ž ์ด๋ฆ„์„ ๋Œ€์†Œ๋ฌธ์ž ๊ตฌ๋ถ„ํ• ์ง€ ์—ฌ๋ถ€
krb_server_keyfile FILE:/etc/postgresql-common/krb5.keytab   Kerberos ์„œ๋ฒ„ ํ‚ค ํŒŒ์ผ์˜ ์œ„์น˜
password_encryption scram-sha-256 scram-sha-256 md5 [14๋ฒ„์ „Default๋ณ€๊ฒฝ] ๋น„๋ฐ€๋ฒˆํ˜ธ ์•”ํ˜ธํ™” ์•Œ๊ณ ๋ฆฌ์ฆ˜ ์„ ํƒ

 

SSL

Parameter Default Value Range Description
ssl on   SSL ์—ฐ๊ฒฐ ํ™œ์„ฑํ™”
ssl_ca_file     SSL ์ธ์ฆ ๊ธฐ๊ด€ ํŒŒ์ผ์˜์œ„์น˜
ssl_cert_file /etc/ssl/certs/ssl-cert-snakeoil.pem   SSL ์„œ๋ฒ„ ์ธ์ฆ์„œ ํŒŒ์ผ ์œ„์น˜
ssl_ciphers HIGH:MEDIUM:+3DES:!aNULL   ํ—ˆ์šฉ๋œ SSL ์•”ํ˜ธ ๋ชฉ๋ก ์„ค์ •
ssl_crl_dir     [14๋ฒ„์ „์ถ”๊ฐ€] SSL ์ธ์ฆ์„œ ํ•ด์ง€ ๋ชฉ๋ก ๋””๋ ‰ํ† ๋ฆฌ ์œ„์น˜
ssl_crl_file     SSL ์ธ์ฆ์„œ ํ•ด์ง€ ๋ชฉ๋ก ํŒŒ์ผ ์œ„์น˜
ssl_dh_params_file     SSL DH ํŒŒ๋ผ๋ฏธํ„ฐ ํŒŒ์ผ ์œ„์น˜
ssl_ecdh_curve prime256v1   ECDH์— ์‚ฌ์šฉํ•  ๊ณก์„  ์„ค์ •
ssl_key_file /etc/ssl/private/ssl-cert-snakeoil.key   SSL ์„œ๋ฒ„ ๊ฐœ์ธ ํ‚ค ํŒŒ์ผ ์œ„์น˜
ssl_max_protocol_version   TLSv1, TLSv1.1, TLSv1.2, TLSv1.3 ์‚ฌ์šฉํ•  ์ตœ๋Œ€ SSL/TLS ํ”„๋กœํ† ์ฝœ ๋ฒ„์ „ ์„ค์ •
ssl_min_protocol_version TLSv1.2 TLSv1, TLSv1.1, TLSv1.2, TLSv1.3 ์‚ฌ์šฉํ•  ์ตœ์†Œ SSL/TLS ํ”„๋กœํ† ์ฝœ ๋ฒ„์ „ ์„ค์ •
ssl_passphrase_command     SSL ์•”ํ˜ธ๋ฅผ ๊ฐ€์ ธ์˜ค๋Š” ๋ช…๋ น์–ด
ssl_passphrase_command_supports_reload off   ์„œ๋ฒ„ reload ๋™์•ˆ ssl_passphrase_command ์‚ฌ์šฉ์—ฌ๋ถ€
ssl_prefer_server_ciphers on   ์„œ๋ฒ„์˜ SSL ์•”ํ˜ธ ๊ธฐ๋ณธ ์„ค์ •์„ ์‚ฌ์šฉํ• ์ง€ ์—ฌ๋ถ€

 


RESOURCE USAGE (except WAL)

Memory

Parameter Default Value Range Description
autovacuum_work_mem -1 -1~2147483647kB autovacuum worker์—์„œ ์‚ฌ์šฉํ•  ์ตœ๋Œ€ ๋ฉ”๋ชจ๋ฆฌ ํฌ๊ธฐ
-1์˜ ๊ฒฝ์šฐ maintenance_work_mem ๊ฐ’์„ ์‚ฌ์šฉ
• dead tuple ์‹๋ณ„์ž ์ˆ˜์ง‘์˜ ๊ฒฝ์šฐ autovacuum์€ ์ตœ๋Œ€ 1GB์˜ ๋ฉ”๋ชจ๋ฆฌ๋งŒ ์‚ฌ์šฉํ•  ์ˆ˜ ์žˆ์œผ๋ฏ€๋กœ autovacuum_work_mem์„ ์ด ๊ฐ’ ์ด์ƒ์œผ๋กœ ์„ค์ •ํ•˜๋ฉด autovacuum์ด ํ…Œ์ด๋ธ”์„ ๊ฒ€์ƒ‰ํ•˜๋Š” ๋™์•ˆ ์ˆ˜์ง‘ํ•  ์ˆ˜ ์žˆ๋Š” dead tuple ์ˆ˜์— ์˜ํ–ฅ์„ ๋ฏธ์น˜์ง€ ์•Š์Šต๋‹ˆ๋‹ค.
dynamic_shared_memory_type posix posix, sysv, windows, mmap ์‚ฌ์šฉ๋œ ๋™์  ๊ณต์œ  ๋ฉ”๋ชจ๋ฆฌ ๊ตฌํ˜„ ์„ ํƒ
hash_mem_multiplier 1.0 1~1000.0 Hash Table์— ์‚ฌ์šฉํ•  work_mem์˜ ๋ฐฐ์ˆ˜
huge_pages try on, off, try huge page ์‚ฌ์šฉ (Linux, Windows ์ „์šฉ)
huge_page_size 0 0~2147483647kB [14๋ฒ„์ „์ถ”๊ฐ€] huge page ํฌ๊ธฐ
logical_decoding_work_mem 65536 (64MB) 64(1MB)~ 2147483647kB Logical decoding์— ์‚ฌ์šฉํ•  ์ตœ๋Œ€ ๋ฉ”๋ชจ๋ฆฌ ํฌ๊ธฐ
maintenance_work_mem 65536 (64MB) 1024(1MB)~ 2147493647kB ์œ ์ง€ ๊ด€๋ฆฌ ์ž‘์—…์— ์‚ฌ์šฉํ•  ์ตœ๋Œ€ ๋ฉ”๋ชจ๋ฆฌ ํฌ๊ธฐ
• VACUUM , CREATE INDEX ๋ฐ ALTER TABLE ADD FOREIGN KEY ์™€ ๊ฐ™์€ ์œ ์ง€ ๊ด€๋ฆฌ ์ž‘์—…์—์„œ ์‚ฌ์šฉํ•  ์ตœ๋Œ€ ๋ฉ”๋ชจ๋ฆฌ ์–‘์„ ์ง€์ •ํ•ฉ๋‹ˆ๋‹ค.
• ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค ์„ธ์…˜์€ ์ด๋Ÿฌํ•œ ์ž‘์—… ์ค‘ ํ•˜๋‚˜๋งŒ ์‹คํ–‰ํ•  ์ˆ˜ ์žˆ์œผ๋ฉฐ ์„ค์น˜ ์‹œ ์ผ๋ฐ˜์ ์œผ๋กœ ๋งŽ์€ ์ž‘์—…์ด ๋™์‹œ์— ์‹คํ–‰๋˜์ง€ ์•Š์œผ๋ฏ€๋กœ work_mem๋ณด๋‹ค ์ด ๊ฐ’์„ ํ›จ์”ฌ ํฌ๊ฒŒ ์„ค์ •ํ•˜๋Š” ๊ฒƒ์ด ์•ˆ์ „ํ•ฉ๋‹ˆ๋‹ค.
• autovacuum ์‹œ autovacuum_max_workers ๋งŒํผ์˜ ๋ฐฐ์ˆ˜๊นŒ์ง€ ํ• ๋‹น๋  ์ˆ˜ ์žˆ์œผ๋ฏ€๋กœ ์„ค์ •์— ์ฃผ์˜๊ฐ€ ํ•„์š”ํ•ฉ๋‹ˆ๋‹ค.
min_dynamic_shared_memory 0MB 0~2147483647MB [14๋ฒ„์ „์ถ”๊ฐ€] ์‹œ์ž‘ ์‹œ ์˜ˆ์•ฝ๋œ ๋™์  ๊ณต์œ  ๋ฉ”๋ชจ๋ฆฌ ํฌ๊ธฐ
max_prepared_transactions 0 0~252143 ๋™์‹œ์— ์ค€๋น„๋œ ํŠธ๋žœ์žญ์…˜์˜ ์ตœ๋Œ€ ์ˆ˜
max_stack_depth 100kB 100~2147483647kB ์ตœ๋Œ€ stack ๊นŠ์ด(KB)
shared_buffers 1024 (8MB) 16 (128kB)~ 1073741823 (8589934584kB) • ์„œ๋ฒ„์—์„œ ์‚ฌ์šฉํ•˜๋Š” ๊ณต์œ  ๋ฉ”๋ชจ๋ฆฌ ๋ฒ„ํผ์˜ ์ˆ˜(๋ฉ”๋ชจ๋ฆฌ ์–‘)๋ฅผ ์„ค์ •ํ•ฉ๋‹ˆ๋‹ค.
• 128KB ์ด์ƒ์ด์–ด์•ผ ํ•˜๋ฉฐ, ์ผ๋ฐ˜์ ์œผ๋กœ ์ข‹์€ ์„ฑ๋Šฅ์„ ์œ„ํ•ด์„œ๋Š” ์ตœ์†Œ๊ฐ’๋ณด๋‹ค๋Š” ํ›จ์”ฌ ๋†’์€ ์„ค์ •์ด ํ•„์š”ํ•ฉ๋‹ˆ๋‹ค.
• ์ด ๊ฐ’์€ ์„œ๋ฒ„ ์‹œ์ž‘์‹œ์—๋งŒ ์„ค์ •ํ•  ์ˆ˜ ์žˆ์Šต๋‹ˆ๋‹ค.
• 1GB ์ด์ƒ์˜ RAM์ด ์žˆ๋Š” ์ „์šฉ ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค ์„œ๋ฒ„๊ฐ€ ์žˆ๋Š” ๊ฒฝ์šฐ shared_buffer์˜ ํ•ฉ๋ฆฌ์ ์ธ ์‹œ์ž‘ ๊ฐ’์€ ์‹œ์Šคํ…œ ๋ฉ”๋ชจ๋ฆฌ์˜ 25%์ž…๋‹ˆ๋‹ค.
shared_memory_type mmap mmap, sysv, windows Main ๊ณต์œ  ๋ฉ”๋ชจ๋ฆฌ ์˜์—ญ์— ์‚ฌ์šฉ๋˜๋Š” ๊ณต์œ  ๋ฉ”๋ชจ๋ฆฌ ๊ตฌํ˜„ ์„ ํƒ
temp_buffers 1024 (8MB) 100 (800kB)~ 1073741823 (8589934584kB) • ๊ฐ ์„ธ์…˜์—์„œ ์‚ฌ์šฉํ•˜๋Š” ์ž„์‹œ ๋ฒ„ํผ์˜ ์ตœ๋Œ€ ์ˆ˜(๋ฉ”๋ชจ๋ฆฌ ์–‘)๋ฅผ ์„ค์ •ํ•ฉ๋‹ˆ๋‹ค.
• ์ž„์‹œ ํ…Œ์ด๋ธ”์— ๋Œ€ํ•œ ์•ก์„ธ์Šค์—๋งŒ ์‚ฌ์šฉ๋˜๋Š” ์„ธ์…˜ ๋กœ์ปฌ ๋ฒ„ํผ์ž…๋‹ˆ๋‹ค.
• ๊ฐœ๋ณ„ ์„ธ์…˜ ๋‚ด์—์„œ ๋ณ€๊ฒฝํ•  ์ˆ˜ ์žˆ์ง€๋งŒ ์„ธ์…˜ ๋‚ด์—์„œ ์ž„์‹œ ํ…Œ์ด๋ธ”์„ ์ฒ˜์Œ ์‚ฌ์šฉํ•˜๊ธฐ ์ „์—๋งŒ ๋ณ€๊ฒฝํ•  ์ˆ˜ ์žˆ์Šต๋‹ˆ๋‹ค.(์ดํ›„ ์‹œ๋„๋Š” ํ•ด๋‹น ์„ธ์…˜์— ์˜ํ–ฅ์„ ๋ฏธ์น˜์ง€ ์•Š์Šต๋‹ˆ๋‹ค.)
work_mem 4096kB 64~2147483647kB • ์ฟผ๋ฆฌ ์ž‘์—… ๊ณต๊ฐ„์— ์‚ฌ์šฉํ•  ์ตœ๋Œ€ ๋ฉ”๋ชจ๋ฆฌ๋ฅผ ์„ค์ •ํ•ฉ๋‹ˆ๋‹ค.
• ์ž„์‹œ ๋””์Šคํฌ ํŒŒ์ผ์— ์“ฐ๊ธฐ ์ „์— ์ฟผ๋ฆฌ ์ž‘์—…(ex. ์ •๋ ฌ ๋˜๋Š” ํ•ด์‹œ ํ…Œ์ด๋ธ”)์—์„œ ์‚ฌ์šฉํ•  ์ตœ๋Œ€ ๋ฉ”๋ชจ๋ฆฌ ์–‘์„ ์„ค์ •ํ•ฉ๋‹ˆ๋‹ค.
• ๋ณต์žกํ•œ ์ฟผ๋ฆฌ์˜ ๊ฒฝ์šฐ ์—ฌ๋Ÿฌ ์ •๋ ฌ ๋˜๋Š” ํ•ด์‹œ ์ž‘์—…์ด ๋ณ‘๋ ฌ๋กœ ์‹คํ–‰๋  ์ˆ˜ ์žˆ์œผ๋ฏ€๋กœ ์‚ฌ์šฉ๋œ ์ด ๋ฉ”๋ชจ๋ฆฌ๋Š” ๊ฐ’์˜ work_mem ๊ฐ’์˜ ์—ฌ๋Ÿฌ ๋ฐฐ๊ฐ€ ๋  ์ˆ˜ ์žˆ์Šต๋‹ˆ๋‹ค.

 

Disk

Parameter Default Value Range Description
temp_file_limit -1 -1~2147483647kB • ๊ฐ ํ”„๋กœ์„ธ์Šค์—์„œ ์‚ฌ์šฉํ•˜๋Š” ๋ชจ๋“  ์ž„์‹œ ํŒŒ์ผ์˜ ์ด ํฌ๊ธฐ๋ฅผ ์ œํ•œํ•ฉ๋‹ˆ๋‹ค.
• ํ”„๋กœ์„ธ์Šค๊ฐ€ ์ž„์‹œ ํŒŒ์ผ(ex. ์ •๋ ฌ ๋ฐ ํ•ด์‹œ ์ž„์‹œํŒŒ์ผ ๋˜๋Š” ๋ณด์œ ํ•œ ์ปค์„œ์˜ ์ €์žฅ ํŒŒ์ผ)์— ์‚ฌ์šฉํ•  ์ˆ˜ ์žˆ๋Š” ์ตœ๋Œ€ ๋””์Šคํฌ ๊ณต๊ฐ„์„ ์ง€์ •ํ•˜๋ฉฐ, ์ด ํฌ๊ธฐ๋ฅผ ์ดˆ๊ณผํ•˜๋Š” ํŠธ๋žœ์žญ์…˜์€ ์ทจ์†Œ๋ฉ๋‹ˆ๋‹ค.
• PostgreSQL ํ”„๋กœ์„ธ์Šค์—์„œ ์‚ฌ์šฉํ•˜๋Š” ๋ชจ๋“  ์ž„์‹œ ํŒŒ์ผ์ด ์‚ฌ์šฉํ•˜๋Š” ์ „์ฒด ๊ณต๊ฐ„์„ ์ œํ•œํ•˜๋ฉฐ, ์Šˆํผ์œ ์ €๋งŒ ์ด ์„ค์ •์„ ๋ณ€๊ฒฝํ•  ์ˆ˜ ์žˆ์Šต๋‹ˆ๋‹ค.
• ์ฟผ๋ฆฌ ์‹คํ–‰์—์„œ ๋ฐฑ๊ทธ๋ผ์šด๋“œ์—์„œ ์‚ฌ์šฉ๋˜๋Š” ์ž„์‹œ ํŒŒ์ผ๊ณผ ๋‹ฌ๋ฆฌ ๋ช…์‹œ์  ์ž„์‹œ ํ…Œ์ด๋ธ”์—์„œ ์‚ฌ์šฉ๋˜๋Š” ๋””์Šคํฌ ๊ณต๊ฐ„์€ ์ด ์ œํ•œ์— ํฌํ•จ๋˜์ง€ ์•Š์Šต๋‹ˆ๋‹ค.

 

Kernel Resources

Parameter Default Value Range Description
max_files_per_process 1000 64~2147483647 ๊ฐ ์„œ๋ฒ„ ํ”„๋กœ์„ธ์Šค์—์„œ ๋™์‹œ์— ์—ด ์ˆ˜ ์žˆ๋Š” ์ตœ๋Œ€ ํŒŒ์ผ ์ˆ˜

 

Cost-Based Vacuum Delay

Parameter Default Value Range Description
vacuum_cost_delay 0 0~100 ms Vacuum ์ž‘์—…์— ์‚ฌ์šฉ๋  cost ์ง€์—ฐ ๊ฐ’์„ ์ง€์ •ํ•ฉ๋‹ˆ๋‹ค.
0 ⇒ disable
vacuum_cost_limit 200 1~10000 Vacuum์„ ์ ˆ์ „ ๋ชจ๋“œ๋กœ ์ „ํ™˜ํ•˜๋Š” ๋น„์šฉ
vacuum_cost_page_dirty 20 0~10000 dirty page๋ฅผ flush ํ•˜๋Š”๋ฐ ํ•„์š”ํ•œ I/O ๋น„์šฉ
vacuum_cost_page_hit 1 0~10000 Vacuum์„ ์œ„ํ•ด buffer cache๋ฅผ ํƒ์ƒ‰ํ•˜๋Š” ๋น„์šฉ
vacuum_cost_page_miss 2 0~10000 Vacuum์„ ์œ„ํ•ด disk๋ฅผ ํƒ์ƒ‰ํ•˜๋Š” ๋น„์šฉ

 

Background Writer

Parameter Default Value Range Description
bgwriter_delay 200ms 10ms~10000ms • bgwriter ์ˆ˜ํ–‰ ์ฃผ๊ธฐ๋ฅผ ๋‚˜ํƒ€๋ƒ…๋‹ˆ๋‹ค.
• bgwriter_lru_maxpages๋งŒํผ์˜ dirty pages๋ฅผ ์ •๋ฆฌํ•œ ํ›„ sleepํ•˜๋Š” ์‹œ๊ฐ„์ž…๋‹ˆ๋‹ค.
bgwriter_flush_after 64 (512kB) 0~256(2MB) ์ด์ „์— ์ˆ˜ํ–‰๋œ ์“ฐ๊ธฐ๊ฐ€ ๋””์Šคํฌ์— flush๋œ ํ›„ page ์ˆ˜
0 ⇒ disable
bgwriter_lru_maxpages 100 0 ~1073741823 • ํ•œ๋ฒˆ write์‹œ ๋””์Šคํฌ๋กœ ๋‚ด๋ฆด ์ˆ˜ ์žˆ๋Š” ์ตœ๋Œ€ buffer page ์ˆ˜๋ฅผ ๋‚˜ํƒ€๋ƒ…๋‹ˆ๋‹ค.
• 0์œผ๋กœ ์„ค์ •ํ•˜๋ฉด checkpoint activity๋ฅผ ์ œ์™ธํ•œ background ์“ฐ๊ธฐ๊ฐ€ ๋น„ํ™œ์„ฑํ™”๋ฉ๋‹ˆ๋‹ค.
bgwriter_lru_multiplier 2.0 0.0~10.0 • ์ตœ๊ทผ ์ˆ˜ํ–‰์— ํ•„์š”ํ–ˆ๋˜ buffer ์–‘ ๊ธฐ๋ฐ˜์œผ๋กœ ๊ฐ ์ฃผ๊ธฐ์— ๋””์Šคํฌ์— ์ €์žฅํ•˜๋Š” buffer์˜ ์–‘์„ ์˜ˆ์ธกํ•ฉ๋‹ˆ๋‹ค.(ํ•„์š” buffer ์–‘์˜ ํ‰๊ท ์— bgwriter_lru_multiplier๋ฅผ ๊ณฑํ•ด์„œ ๊ณ„์‚ฐํ•ฉ๋‹ˆ๋‹ค.)
• bgwriter_lru_maxpages ๋ฐ bgwriter_lru_multiplier ๊ฐ’์ด ์ž‘์„์ˆ˜๋ก bgwriter๋กœ ์ธํ•œ ์ถ”๊ฐ€ I/O๊ฐ€ ์ค„์–ด๋“ค์ง€๋งŒ ์„œ๋ฒ„ ํ”„๋กœ์„ธ์Šค๊ฐ€ ์ž์ฒด์ ์œผ๋กœ ์“ฐ๊ธฐ๋ฅผ ์‹คํ–‰ํ•ด์•ผ ํ•  ๊ฐ€๋Šฅ์„ฑ์ด ๋†’์•„์ ธ interactive queries๊ฐ€ ์ง€์—ฐ๋ฉ๋‹ˆ๋‹ค.

 

Asynchronous Behavior

Parameter Default Value Range Description
backend_flush_after 0kB 0~256(2MB) ์ด์ „์— ์ˆ˜ํ–‰๋œ ์“ฐ๊ธฐ๊ฐ€ ๋””์Šคํฌ์— flush๋œ ํ›„ page ์ˆ˜
0 ⇒ disable
effective_io_concurrency 1 0~1000 Disk subsystem์—์„œ ํšจ์œจ์ ์œผ๋กœ ์ฒ˜๋ฆฌํ•  ์ˆ˜ ์žˆ๋Š” ๋™์‹œ ์š”์ฒญ ์ˆ˜
0 ⇒ disable
maintenance_io_concurrency 10 0~1000 ์œ ์ง€ ๋ณด์ˆ˜ ์ž‘์—…์— ์‚ฌ์šฉ๋˜๋Š” effective_io_concurrency์˜ ๋ณ€ํ˜•
0 ⇒ disable
max_parallel_maintenance_workers 2 0~1024 ์œ ์ง€ ๊ด€๋ฆฌ ์ž‘์—…๋‹น ์ตœ๋Œ€ ๋ณ‘๋ ฌ ํ”„๋กœ์„ธ์Šค ์ˆ˜
max_parallel_workers 8 0~1024 ํ•œ๋ฒˆ์— ํ™œ์„ฑํ™”ํ•  ์ˆ˜ ์žˆ๋Š” ์ตœ๋Œ€ ๋ณ‘๋ ฌ ์ž‘์—…์ž ์ˆ˜
max_parallel_workers_per_gather 2 0~1024 Executor ๋…ธ๋“œ ๋‹น ์ตœ๋Œ€ ๋ณ‘๋ ฌ ํ”„๋กœ์„ธ์Šค ์ˆ˜
max_worker_processes 8 0~262143 ์ตœ๋Œ€ ๋™์‹œ ์ž‘์—… ํ”„๋กœ์„ธ์Šค ์ˆ˜
old_snapshot_threshold -1 -1~ 86400min(60d) Snapshot์„ ๋งŒ๋“  ํ›„ ๋ณ€๊ฒฝ๋œ ํŽ˜์ด์ง€๋ฅผ ์ฝ์„ ์ˆ˜ ์—†์„ ๋•Œ๊นŒ์ง€์˜ ์‹œ๊ฐ„
-1 ⇒ disable, 0 ⇒ immeidate
parallel_leader_participation on   Gather๋‚˜ Gather Merge๋„ ํ•˜์œ„ ๊ณ„ํš์„ ์‹คํ–‰ํ• ์ง€ ์—ฌ๋ถ€

 


WRITE-AHEAD LOG

Settings

Parameter Default Value Range Description
commit_delay 0 0~100000ms ํŠธ๋žœ์žญ์…˜ commit๊ณผ Disk์— WAL flush ์‚ฌ์ด์˜ ์ง€์—ฐ์‹œ๊ฐ„
commit_siblings 5 1~1000 commit_delay๋ฅผ ์ˆ˜ํ–‰ํ•˜๊ธฐ ์ „์— ์—ด๋ ค ์žˆ๋Š” ์ตœ์†Œ ๋™์‹œ ํŠธ๋žœ์žญ์…˜ ์ˆ˜
fsync on   Disk์— update๋ฅผ ๊ฐ•์ œ๋กœ ๋™๊ธฐํ™”
full_page_writes on   Checkpoint ํ›„ ์ฒ˜์Œ ์ˆ˜์ •ํ•  ๋•Œ ์ „์ฒด ํŽ˜์ด์ง€๋ฅผ WAL์— ์“ธ์ง€ ์—ฌ๋ถ€
synchronous_commit on local, remote_write, remote_apply, on, off ํ˜„์žฌ ํŠธ๋žœ์žญ์…˜์˜ ๋™๊ธฐํ™” ์ˆ˜์ค€ ์„ค์ •
• commit ์ˆ˜ํ–‰์‹œ WAL ๋ ˆ์ฝ”๋“œ๋ฅผ ๋””์Šคํฌ๋กœ ์ €์žฅํ–ˆ๋‹ค๋Š” ์˜๋ฏธ์˜ "success" ๋ฆฌํ„ด์—ฌ๋ถ€๋ฅผ ์„ค์ •ํ•ฉ๋‹ˆ๋‹ค.
• off ์ธ ๊ฒฝ์šฐ ์‹ค์ œ ํŠธ๋žœ์žญ์…˜์ด ์•ˆ์ •ํ•˜๊ฒŒ ๋ฐ˜์˜๋œ ์‹œ์ ๊นŒ์ง€ delay๊ฐ€ ์กด์žฌํ•ฉ๋‹ˆ๋‹ค.(์ตœ๋Œ€ delay๋Š” wal_writer_delay์˜ 3๋ฐฐ์ž…๋‹ˆ๋‹ค.)
• DB์„ฑ๋Šฅ์ด ์ค‘์š”ํ•˜๋‹ค๋ฉด off ๊ถŒ์žฅ
wal_buffers -1 -1~262143 (2097144kB) Disk์— ์•„์ง ๊ธฐ๋ก๋˜์ง€ ์•Š์€ WAL ๋ฐ์ดํ„ฐ์— ์‚ฌ์šฉ๋˜๋Š” ๊ณต์œ  ๋ฉ”๋ชจ๋ฆฌ ์–‘ WAL์— ๋Œ€ํ•œ ๊ณต์œ  ๋ฉ”๋ชจ๋ฆฌ์˜ Disk-page buffer ์ˆ˜ ์„ค์ •
-1 ⇒ shared_buffers๋กœ ์„ค์ •
• ๊ฐ’์ด ํด ์ˆ˜๋ก, ํด๋ผ์ด์–ธํŠธ๊ฐ€ ํ•œ ๋ฒˆ์— ์ปค๋ฐ‹ํ•˜๋Š” ์‚ฌ์šฉ๋Ÿ‰์ด ๋งŽ์€ ์„œ๋ฒ„์—์„œ ์“ฐ๊ธฐ ์„ฑ๋Šฅ์ด ํ–ฅ์ƒ๋จ
wal_compression off   WAL ํŒŒ์ผ์— ๊ธฐ๋ก๋œ ์ „์ฒด ํŽ˜์ด์ง€ ์“ฐ๊ธฐ๋ฅผ ์••์ถ•ํ• ์ง€ ์—ฌ๋ถ€
wal_init_zero on   ์ฒ˜์Œ ์‚ฌ์šฉํ•˜๊ธฐ ์ „์— ์ƒˆ๋กœ์šด WAL ํŒŒ์ผ์— 0์„ ๊ธฐ๋กํ• ์ง€ ์—ฌ๋ถ€
wal_level replica minimal, replica, logical WAL์— ๊ธฐ๋ก๋˜๋Š” ์ •๋ณด์˜ ์ˆ˜์ค€ ์„ค์ •
wal_log_hints off   ์ค‘์š”ํ•˜์ง€ ์•Š์€ ์ˆ˜์ •์—๋„ Checkpoint ํ›„ ์ฒ˜์Œ ์ˆ˜์ •ํ•  ๋•Œ ์ „์ฒด ํŽ˜์ด์ง€๋ฅผ WAL์— ์“ธ์ง€ ์—ฌ๋ถ€
wal_recycle on   WAL ํŒŒ์ผ์˜ ์ด๋ฆ„์„ ๋ณ€๊ฒฝํ•˜์—ฌ ์žฌํ™œ์šฉ ํ• ์ง€ ์—ฌ๋ถ€
wal_skip_threshold 2048(2MB) 0~2147483647kB WAL์„ ์“ฐ๋Š” ๋Œ€์‹  fsyncํ•  ์ƒˆ๋กœ์šด ํŒŒ์ผ์˜ ์ตœ์†Œ ํฌ๊ธฐ
(wal_level=minimal ์ผ ๊ฒฝ์šฐ)
wal_sync_method fdatasync open_datasync, fdatasync, fsync, open_sync Disk์— WAL ์—…๋ฐ์ดํŠธ๋ฅผ ๊ฐ•์ œ๋กœ ์ ์šฉํ•˜๋Š” ๋ฐ ์‚ฌ์šฉ๋˜๋Š” Method ์„ค์ •
wal_writer_delay 200ms 1ms ~ 10000ms(10s) WAL Writer์—์„œ WAL Flush๋ฅผ ์ˆ˜ํ–‰ํ•˜๋Š” ๊ฐ„๊ฒฉ
• wal writer ๊ธฐ๋ก ๊ฐ„ sleep ์‹œ๊ฐ„๋ฅผ ์˜๋ฏธํ•˜๋ฉฐ, sleep delay์˜ ํšจ์œจ์ ์ธ ์„ค์ •์€ 10ms ์ž…๋‹ˆ๋‹ค.
• wal_writer_delay๋ฅผ 10์˜ ๋ฐฐ์ˆ˜๊ฐ€ ์•„๋‹Œ ๊ฐ’์œผ๋กœ ์„ค์ •ํ•˜๋ฉด ๋‹ค์Œ์œผ๋กœ ๋†’์€ 10์˜ ๋ฐฐ์ˆ˜๋กœ ์„ค์ •ํ•˜๋Š” ๊ฒƒ๊ณผ ๋™์ผํ•ฉ๋‹ˆ๋‹ค.
wal_writer_flush_after 128(1MB) 0 ~ 2147283647 (17179869176kB) Flush๋ฅผ ์œ ๋ฐœํ•˜๋Š” WAL Writer๊ฐ€ ๊ธฐ๋กํ•œ WAL์˜ ์–‘
0 ⇒ disable

 

Checkpoints

Parameter Default Value Range Description
checkpoint_completion_target 0.9 0 ~ 1 • checkpoint ์ค‘ dirty buffer๋Š” ๊ธฐ๊ฐ„์„ ๋‚˜๋ˆ ์„œ writeํ•˜๋Š”๋ฐ, ๊ทธ ๊ธฐ๊ฐ„์„ ์„ค์ •ํ•˜๋Š” ํŒŒ๋ผ๋ฏธํ„ฐ์ž…๋‹ˆ๋‹ค.
• 0.5์ธ ๊ฒฝ์šฐ, ๋‹ค์Œ checkpoint๊ฐ€ ์‹œ์ž‘๋˜๊ธฐ ์ „ ์‹œ๊ฐ„์˜ ์•ฝ ์ ˆ๋ฐ˜์ด ์ง€๋‚œ ํ›„ ๊ฐ checkpoint๋ฅผ ์™„๋ฃŒํ•˜๋Š” ๊ฒƒ์œผ๋กœ ์˜ˆ์ƒํ•ฉ๋‹ˆ๋‹ค.
checkpoint_flush_after 32 (256kB) 0 ~ 256(2MB) • ์ด์ „์— ์ˆ˜ํ–‰๋œ write์— ๋Œ€ํ•ด ๋””์Šคํฌ๋กœ flush ๋œ page ์ˆ˜ ์ž…๋‹ˆ๋‹ค.
• checkpoint๋ฅผ ์ˆ˜ํ–‰ํ•˜๋Š” ๋™์•ˆ ํ•ด๋‹น ํŒŒ๋ผ๋ฏธํ„ฐ ๊ฐ’๋ณด๋‹ค ๋งŽ์€ ๋ฐ์ดํ„ฐ๊ฐ€ ๊ธฐ๋ก๋ ๋•Œ๋งˆ๋‹ค OS๋Š” ๊ธฐ๋ณธ ์Šคํ† ๋ฆฌ์ง€์— ์“ฐ๊ธฐ๋ฅผ ์‹คํ–‰ํ•ด์•ผ ํ•ฉ๋‹ˆ๋‹ค.
• ์ปค๋„์˜ page cache์— ์žˆ๋Š” dirty ๋ฐ์ดํ„ฐ์˜ ์–‘์ด ์ œํ•œ๋˜์–ด fsync๊ฐ€ checkpoint ๋์—์„œ ์‹คํ–‰๋˜๊ฑฐ๋‚˜ OS๊ฐ€ ๋ฐฑ๊ทธ๋ผ์šด๋“œ์—์„œ ๋” ํฐ ๋ฐฐ์น˜๋กœ ๋ฐ์ดํ„ฐ๋ฅผ ๋‹ค์‹œ ์“ธ๋•Œ ๋ฉˆ์ถœ ๊ฐ€๋Šฅ์„ฑ์ด ๊ฐ์†Œํ•ฉ๋‹ˆ๋‹ค.
checkpoint_timeout 300s 30~86400s(1day) ์ž๋™ WAL checkpoint ๊ฐ„์˜ ์ตœ๋Œ€ ์‹œ๊ฐ„
checkpoint_warning 30s 0 ~ 2147483647s • checkpoint ์‚ฌ์ด์˜ ์ž„๊ณ„๊ฐ’์„ ๊ตฌ์„ฑํ•˜๋Š” ํŒŒ๋ผ๋ฏธํ„ฐ์ž…๋‹ˆ๋‹ค.
• checkpoint๊ฐ€ ํ•ด๋‹น ํŒŒ๋ผ๋ฏธํ„ฐ ๊ฐ’์— ๊ฐ€๊น๊ฒŒ ๋ฐœ์ƒํ•  ๊ฒฝ์šฐ, ์‹œ์Šคํ…œ์ด ์„œ๋ฒ„ ๋กœ๊ทธ์— ๊ฒฝ๊ณ ํ•˜์—ฌ ์‚ฌ์šฉ์ž๊ฐ€ max_wal_size๋ฅผ ๋Š˜๋ฆด ๊ฒƒ์„ ๊ถŒ์žฅํ•ฉ๋‹ˆ๋‹ค.
max_wal_size 1024MB 2 ~ 2147483647MB checkpoint๊ฐ€ ๋ฐœ์ƒํ•˜๋Š” ์ตœ๋Œ€ WAL ์‚ฌ์ด์ฆˆ์ž…๋‹ˆ๋‹ค.
min_wal_size 80MB 2 ~ 2147483647MB checkpoint๊ฐ€ ๋ฐœ์ƒํ•˜๋Š” ์ตœ์†Œ WAL ์‚ฌ์ด์ฆˆ์ž…๋‹ˆ๋‹ค. ํ•ด๋‹น ํฌ๊ธฐ ์•„๋ž˜๋กœ ์œ ์ง€๋˜๋Š” ํ•œ ์˜ค๋ž˜๋œ WAL ํŒŒ์ผ์€ ์ œ๊ฑฐ๋˜์ง€ ์•Š๊ณ  ์ฒดํฌํฌ์ธํŠธ์—์„œ ํ–ฅํ›„ ์‚ฌ์šฉ์„ ์œ„ํ•ด ํ•ญ์ƒ ์žฌํ™œ์šฉ๋ฉ๋‹ˆ๋‹ค.

 

Archiving

Parameter Default Value Range Description
archive_command     • WALํŒŒ์ผ์„ ์•„์นด์ด๋ธŒํ•˜๊ธฐ ์œ„ํ•ด ์„ค์ •ํ•œ ๋ช…๋ น์–ด์ž…๋‹ˆ๋‹ค.
• %p : ์•„์นด์ด๋ธŒ ํŒŒ์ผ์˜ ๊ฒฝ๋กœ ์ด๋ฆ„
• %f : ์•„์นด์ด๋ธŒ ํŒŒ์ผ๋ช…
• WAL ์„ธ๊ทธ๋จผํŠธ ํŒŒ์ผ์ด ์•„์นด์ด๋ธŒ๋˜๋ฉด archive_command์—์„œ ์ง€์ •ํ•œ ๋ช…๋ น์ด ์‹คํ–‰๋ฉ๋‹ˆ๋‹ค. ๋ณด๊ด€ ๋ช…๋ น์ด 0์„ ๋ฆฌํ„ดํ•˜๋ฉด PostgreSQL์€ ํŒŒ์ผ์ด ์„ฑ๊ณต์ ์œผ๋กœ ์•„์นด์ด๋ธŒ๋œ ๊ฒƒ์œผ๋กœ ๊ฐ„์ฃผํ•œ ๋‹ค์Œ WAL ์„ธ๊ทธ๋จผํŠธ ํŒŒ์ผ์„ ์‚ญ์ œํ•˜๊ฑฐ๋‚˜ ์žฌํ™œ์šฉํ•ฉ๋‹ˆ๋‹ค. 0์ด ์•„๋‹Œ ๋‹ค๋ฅธ๊ฐ’์ด ๋ฆฌํ„ด๋˜๋ฉด ์„ฑ๊ณตํ• ๋•Œ๊นŒ์ง€ ์ฃผ๊ธฐ์ ์œผ๋กœ ์žฌ์‹œ๋„๋ฅผ ํ•ฉ๋‹ˆ๋‹ค.
archive_mode off always, on, off • archive_command๋ฅผ ์‚ฌ์šฉํ•˜์—ฌ WALํŒŒ์ผ์„ ๋ณด๊ด€ํ• ์ง€ ์„ค์ • ๊ฐ€๋Šฅํ•ฉ๋‹ˆ๋‹ค.
• always๋กœ ์„ค์ •ํ•˜๋ฉด ์•„์นด์ด๋ธŒ ๋ณต๊ตฌ ๋˜๋Š” ๋Œ€๊ธฐ ๋ชจ๋“œ์—์„œ๋„ Archiver๊ฐ€ ํ™œ์„ฑํ™”๋ฉ๋‹ˆ๋‹ค.
archive_timeout 0s 0 ~ 1073741823s • ์„œ๋ฒ„๊ฐ€ ์ฃผ๊ธฐ์ ์œผ๋กœ ์ƒˆ WAL ์„ธ๊ทธ๋จผํŠธ ํŒŒ์ผ๋กœ ์ „ํ™˜ํ•˜๋„๋ก ํ•˜๋Š” ํŒŒ๋ผ๋ฏธํ„ฐ
• ํ•ด๋‹น ํŒŒ๋ผ๋ฏธํ„ฐ์˜ ๊ฐ’๋งŒํผ์˜ ์‹œ๊ฐ„(N์ดˆ)์ด ์ดˆ๊ณผ๋˜๋ฉด WAL ์„ธ๊ทธ๋จผํŠธ ํŒŒ์ผ์ด ๊ฐ•์ œ๋กœ ์ „ํ™˜๋ฉ๋‹ˆ๋‹ค.
• ๊ฐ•์ œ์Šค์œ„์น˜๋กœ ์ธํ•ด ์ผ์ฐ ๋‹ซํžˆ๋Š” ์•„์นด์ด๋ธŒ ํŒŒ์ผ์˜ ๊ธธ์ด๋Š” ์ „์ฒด ํŒŒ์ผ๊ณผ ๋™์ผํ•˜๊ธฐ ๋•Œ๋ฌธ์— ๋„ˆ๋ฌด ์งง์€ ์‹œ๊ฐ„์œผ๋กœ ์„ค์ •ํ•˜๋ฉด ์•„์นด์ด๋ธŒ ์Šคํ† ๋ฆฌ์ง€๊ฐ€ ์‚ฌ์šฉ๋Ÿ‰์ด ๊ธ‰์ฆํ•  ์ˆ˜ ์žˆ์Šต๋‹ˆ๋‹ค.(์ผ๋ฐ˜์ ์œผ๋กœ 1๋ถ„์„ ๊ถŒ์žฅํ•˜๋ฉฐ ๋””์Šคํฌ ๊ณต๊ฐ„๊ณผ ๋Œ€๊ธฐ์‹œ๊ฐ„์„ ๊ณ ๋ คํ•˜์—ฌ ์„ค์ •ํ•ฉ๋‹ˆ๋‹ค.

 

Archive Recovery

Parameter Default Value Range Description
archive_cleanup_command     ์žฌ์‹œ์ž‘ ์ง€์ ์— ์‹คํ–‰๋  ์‰˜ ๋ช…๋ น ์„ค์ •
recovery_end_command     ๋ณต๊ตฌ๊ฐ€ ๋๋‚  ๋•Œ ํ•œ ๋ฒˆ ์‹คํ–‰๋  ์‰˜ ๋ช…๋ น ์„ค์ •
restore_command     ๋ณด๊ด€๋œ WAL ํŒŒ์ผ์„ ๊ฒ€์ƒ‰ํ•˜๊ธฐ ์œ„ํ•ด ํ˜ธ์ถœํ•  ์ˆ˜๋ ˆ ๋ช…๋ น ์„ค์ •

 

Recovery Target

Parameter Default Value Range Description
recovery_target     ์ผ๊ด€๋œ ์ƒํƒœ์— ๋„๋‹ฌํ•˜๋Š” ์ฆ‰์‹œ ๋ณต๊ตฌ๋ฅผ ์ข…๋ฃŒํ•˜๋ ค๋ฉด “immediate”๋กœ ์„ค์ •
recovery_target_action pause pause, promote, shutdown ๋ณต๊ตฌ ๋Œ€์ƒ์— ๋„๋‹ฌํ•  ๋•Œ ์ˆ˜ํ–‰ํ•  ์ž‘์—… ์„ค์ •
recovery_target_inclusive on   ๋ณต๊ตฌ ๋Œ€์ƒ๊ณผ์˜ ํŠธ๋žœ์žญ์…˜์„ ํฌํ•จํ• ์ง€ ์ œ์™ธํ• ์ง€ ์—ฌ๋ถ€
recovery_target_lsn     ๋ณต๊ตฌ๊ฐ€ ์ง„ํ–‰๋  write-ahead ๋กœ๊ทธ ์œ„์น˜์˜ LSN์„ ์„ค์ •
recovery_target_name     ๋ณต๊ตฌ๊ฐ€ ์ง„ํ–‰๋  ์ง€์ •๋œ ๋ณต์› ์ง€์  ์„ค์ •
recovery_target_time     ๋ณต๊ตฌ๊ฐ€ ์ง„ํ–‰๋  ์ตœ๋Œ€ timestamp ์„ค์ •
recovery_target_timeline latest current, latest ๋ณต๊ตฌํ•  ์‹œ๊ฐ„๋Œ€ ์ง€์ •
recovery_target_xid     ๋ณต๊ตฌ๊ฐ€ ์ง„ํ–‰๋  ํŠธ๋žœ์žญ์…˜ ID ์„ค์ •

 


REPLICATION

Sending Servers

Parameter Default Value Range Description
max_replication_slots 10 0~262143 ๋™์‹œ์— ์ •์˜๋œ ์ตœ๋Œ€ ๋ณต์ œ ์Šฌ๋กฏ ์ˆ˜
max_slot_wal_keep_size -1 -1~2147483647MB ๋ณต์ œ ์Šฌ๋กฏ์œผ๋กœ ์˜ˆ์•ฝํ•˜ ์ˆ˜ ์žˆ๋Š” ์ตœ๋Œ€ WAL ํฌ๊ธฐ
-1 ⇒ disable
max_wal_senders 10 0~262143 ๋™์‹œ์— ์‹คํ–‰๋˜๋Š” WAL ์†ก์‹ ์ž ํ”„๋กœ์„ธ์Šค์˜ ์ตœ๋Œ€ ์ˆ˜
track_commit_timestamp off   ํŠธ๋žœ์žญ์…˜ Commit ์‹œ๊ฐ„ ์ˆ˜์ง‘์—ฌ๋ถ€
wal_keep_size 0 0~2147483647MB Standby ์„œ๋ฒ„์— ๋Œ€ํ•ด ์œ ์ง€๋˜๋Š” WAL ํŒŒ์ผ์˜ ํฌ๊ธฐ
0 ⇒ disable
wal_sender_timeout 60000ms (1min) 0~2147483647ms WAL ๋ณต์ œ๋ฅผ ๋Œ€๊ธฐํ•  ์ตœ๋Œ€ ์‹œ๊ฐ„
0 ⇒ disable

 

Primary Server (under 13 : Master Server)

Parameter Default Value Range Description
synchronous_standby_names     ๋™๊ธฐ Standby ์ˆ˜์™€ ์ž ์žฌ์ ์ธ ๋™๊ธฐ Standby ์ด๋ฆ„ ๋ชฉ๋ก
vacuum_defer_cleanup_age 0 0~1000000 Vacuum๊ณผ HOT Cleanup์„ ์—ฐ๊ธฐํ•ด์•ผ ํ•˜๋Š” ํŠธ๋žœ์žญ์…˜ ์ˆ˜

 

Standby Servers

Parameter Default Value Range Description
hot_standby on on, off ๋ณต๊ตฌ ์ค‘์— ์—ฐ๊ฒฐ ๋ฐ ์ฟผ๋ฆฌ ํ—ˆ์šฉ ์—ฌ๋ถ€
hot_standby_feedback off   ์ฟผ๋ฆฌ ์ถฉ๋Œ์„ ํ”ผํ•  ์ˆ˜ ์žˆ๋„๋ก Hot standby์—์„œ Primary๋กœ ํ”ผ๋“œ๋ฐฑ ํ—ˆ์šฉ ์—ฌ๋ถ€
max_standby_archive_delay 30000ms -1~2147483647ms Hot standby ์„œ๋ฒ„๊ฐ€ ๋ณด๊ด€๋œ WAL ๋ฐ์ดํ„ฐ ์ฒ˜๋ฆฌ์‹œ ์ฟผ๋ฆฌ๋ฅผ ์ทจ์†Œํ•˜๊ธฐ ์ „ ์ตœ๋Œ€ ์ง€์—ฐ์‹œ๊ฐ„
max_standby_streaming_delay 30000ms -1~2147483647ms Hot standby ์„œ๋ฒ„๊ฐ€ ์ŠคํŠธ๋ฆฌ๋ฐ๋œ WAL ๋ฐ์ดํ„ฐ ์ฒ˜๋ฆฌ์‹œ ์ฟผ๋ฆฌ๋ฅผ ์ทจ์†Œํ•˜๊ธฐ ์ „ ์ตœ๋Œ€ ์ง€์—ฐ์‹œ๊ฐ„
primary_conninfo     Sending Server์— ์—ฐ๊ฒฐํ•˜๋Š” ๋ฐ ์‚ฌ์šฉํ•  ์—ฐ๊ฒฐ ๋ฌธ์ž์—ด ์„ค์ •
primary_slot_name     Sending Server์—์„œ ์‚ฌ์šฉํ•  ๋ณต์ œ ์Šฌ๋กฏ ์ด๋ฆ„ ์„ค์ •
promote_trigger_file     ๋Œ€๊ธฐ ์ƒํƒœ์—์„œ ๋ณต๊ตฌ๊ฐ€ ๋๋‚˜๋Š” ํŒŒ์ผ ์ด๋ฆ„ ์„ค์ •
recovery_min_apply_delay 0ms 0~2147483647ms ๋ณต๊ตฌ ์ค‘ ๋ณ€๊ฒฝ ์‚ฌํ•ญ์„ ์ ์šฉํ•˜๊ธฐ ์œ„ํ•œ ์ตœ์†Œ ์ง€์—ฐ ์‹œ๊ฐ„
wal_receiver_create_temp_slot off   ์˜๊ตฌ์ ์ธ ์Šฌ๋กฏ์ด ๊ตฌ์„ฑ๋˜์ง€ ์•Š์€ ๊ฒฝ์šฐ, WAL Receiver๊ฐ€ ์ž„์‹œ ๋ณต์ œ ์Šฌ๋กฏ ์ƒ์„ฑ ์—ฌ๋ถ€
wal_receiver_status_interval 10s 0~2147483s WAL Receiver ์ƒํƒœ ๋ณด๊ณ ์„œ๋ฅผ Sending Server์— ์ „๋‹ฌํ•˜๋Š” ์ตœ๋Œ€ ์‹œ๊ฐ„ ๊ฐ„๊ฒฉ
0 ⇒ disable
wal_receiver_timeout 60000ms 0~2147483647ms Seding Server์—์„œ ๋ฐ์ดํ„ฐ๋ฅผ ์ˆ˜์‹ ํ•˜๊ธฐ ์œ„ํ•œ ์ตœ๋Œ€ ๋Œ€๊ธฐ ์‹œ๊ฐ„
0 ⇒ disable
wal_retrieve_retry_interval 5000ms 1~2147483647ms ์‹คํŒจํ•œ ์‹œ๋„ ํ›„ WAL ๊ฒ€์ƒ‰์„ ๋‹ค์‹œ ์‹œ๋„ํ•˜๊ธฐ๊นŒ์ง€ ๋Œ€๊ธฐ ์‹œ๊ฐ„

 

Subscribers

Parameter Default Value Range Description
max_logical_replication_workers 4 0~262143 ๋…ผ๋ฆฌ์  ๋ณต์ œ ์ž‘์—… ํ”„๋กœ์„ธ์Šค์˜ ์ตœ๋Œ€ ์ˆ˜
max_sync_workers_per_subscription 2 0~262143 Subscription ๋‹น ํ…Œ์ด๋ธ” ๋™๊ธฐํ™” ์ž‘์—…์ž์˜ ์ตœ๋Œ€ ์ˆ˜

 


QUERY TUNING

Planner Method Configuration

Parameter Default Value Description
enable_async_append on [14๋ฒ„์ „์ถ”๊ฐ€] async append plan ์‚ฌ์šฉ ์—ฌ๋ถ€
enable_bitmapscan on bitmap-scan plan ์‚ฌ์šฉ ์—ฌ๋ถ€
enable_gathermerge on [14๋ฒ„์ „์ถ”๊ฐ€] gather merge plan ์‚ฌ์šฉ ์—ฌ๋ถ€
enable_hashagg on hash aggregation plan ์‚ฌ์šฉ ์—ฌ๋ถ€
enable_hashjoin on hash join plan ์‚ฌ์šฉ ์—ฌ๋ถ€
enable_incremental_sort on incremental sort plan ์‚ฌ์šฉ ์—ฌ๋ถ€
enable_indexscan on index scan plan ์‚ฌ์šฉ ์—ฌ๋ถ€
enable_indexonlyscan on index-only-scan plan ์‚ฌ์šฉ ์—ฌ๋ถ€
enable_material on materialization plan ์‚ฌ์šฉ ์—ฌ๋ถ€
enable_memoize on [14๋ฒ„์ „์ถ”๊ฐ€] memoization plan ์‚ฌ์šฉ ์—ฌ๋ถ€
enable_mergejoin on merge join plan ์‚ฌ์šฉ ์—ฌ๋ถ€
enable_nestloop on nested-loop join plan ์‚ฌ์šฉ ์—ฌ๋ถ€
enable_parallel_append on parallel append plan ์‚ฌ์šฉ ์—ฌ๋ถ€
enable_parallel_hash on parallel hash plan ์‚ฌ์šฉ ์—ฌ๋ถ€
enable_partition_pruning on plan-time๊ณผ execution-time partition pruning ์‚ฌ์šฉ ์—ฌ๋ถ€
enable_partitionwise_join off partitionwise join ์‚ฌ์šฉ ์—ฌ๋ถ€
enable_partitionwise_aggregate off partitionwise aggregation๊ณผ grouping ์‚ฌ์šฉ ์—ฌ๋ถ€
enable_seqscan on sequential-scan plan ์‚ฌ์šฉ ์—ฌ๋ถ€
enable_sort on ๋ช…์‹œ์  sort plan ์‚ฌ์šฉ ์—ฌ๋ถ€
enable_tidscan on TID scan plan ์‚ฌ์šฉ ์—ฌ๋ถ€

 

Planner Cost Constants

Parameter Default Value Range Description
cpu_index_tuple_cost 0.005 0~1.79769e+308 Index Scan ๋™์•ˆ ๊ฐ Index ํ•ญ๋ชฉ์„ ์ฒ˜๋ฆฌํ•˜๋Š” ๋น„์šฉ์— ๋Œ€ํ•œ ์ถ”์ •์น˜
cpu_operator_cost 0.0025 0~1.79769e+308 ๊ฐ ์—ฐ์‚ฐ์ž๋‚˜ ํ•จ์ˆ˜ ํ˜ธ์ถœ์„ ์ฒ˜๋ฆฌํ•˜๋Š” ๋น„์šฉ์— ๋Œ€ํ•œ ์ถ”์ •์น˜
cpu_tuple_cost 0.01 0~1.79769e+308 ๊ฐ tuple(row)๋ฅผ ์ฒ˜๋ฆฌํ•˜๋Š” ๋น„์šฉ์— ๋Œ€ํ•œ ์ถ”์ •์น˜
effective_cache_size 524288 (4GB) 1(8kB)~2147483647(17179869176kB) Data cache์˜ ์ด ํฌ๊ธฐ์— ๋Œ€ํ•œ ๊ฐ€์ •(assumption)
jit_above_cost 100000 -1~1.79769e+308 ์ง€์ •๊ฐ’ ๋ณด๋‹ค ์ฟผ๋ฆฌ ๋น„์šฉ์ด ๋” ๋งŽ์ด ๋ฐœ์ƒํ•  ๊ฒฝ์šฐ JIT ์ปดํŒŒ์ผ ์ˆ˜ํ–‰
-1 ⇒ disable
jit_inline_above_cost 500000 -1~1.79769e+308 ์ง€์ •๊ฐ’ ๋ณด๋‹ค ์ฟผ๋ฆฌ ๋น„์šฉ์ด ๋” ๋งŽ์ด ๋ฐœ์ƒํ•  ๊ฒฝ์šฐ JIT inlining ์ˆ˜ํ–‰
-1 ⇒ disable
jit_optimize_above_cost 500000 -1~1.79769e+308 ์ง€์ •๊ฐ’ ๋ณด๋‹ค ์ฟผ๋ฆฌ ๋น„์šฉ์ด ๋” ๋งŽ์ด ๋ฐœ์ƒํ•  ๊ฒฝ์šฐ JIT ์ปดํŒŒ์ผ ํ•จ์ˆ˜๋ฅผ ์ตœ์ ํ™”
-1 ⇒ disable
min_parallel_index_scan_size 64(512kB) 0~715827882 (5726623056kB) parallel scan์„ ์œ„ํ•œ ์ธ๋ฑ์Šค ๋ฐ์ดํ„ฐ ์ตœ์†Œ ์–‘
min_parallel_table_scan_size 1024(8MB) 0~715827882 (5726623056kB) parallel scan์„ ์œ„ํ•œ ํ…Œ์ด๋ธ” ๋ฐ์ดํ„ฐ ์ตœ์†Œ ์–‘
parallel_setup_cost 1000.0 0~1.79769e+308 paralle query๋ฅผ ์œ„ํ•œ ์ž‘์—… ํ”„๋กœ์„ธ์Šค ์‹œ์ž‘ ๋น„์šฉ์— ๋Œ€ํ•œ ์ถ”์ •์น˜
parallel_tuple_cost 0.1 0~1.79769e+308 ๊ฐ tuple(row)๋ฅผ worker์—์„œ leader backend๋กœ ์ „๋‹ฌํ•˜๋Š” ๋น„์šฉ์— ๋Œ€ํ•œ ์ถ”์ •์น˜
random_page_cost 4.0 0~1.79769e+308 ์ˆœ์ฐจ์ ์œผ๋กœ ์ฝ์ง€ ์•Š์€ Disk page ๋น„์šฉ์— ๋Œ€ํ•œ ์ถ”์ •์น˜
seq_page_cost 1.0 0~1.79769e+308 ์ˆœ์ฐจ์ ์œผ๋กœ ์ฝ์€ Disk page ๋น„์šฉ์— ๋Œ€ํ•œ ์ถ”์ •์น˜

 

Genetic Query Optimizer

Parameter Default Value Range Description
geqo on   genetic query optimization ํ™œ์„ฑํ™” ์—ฌ๋ถ€
geqo_effort 5 1~10 ๋‹ค๋ฅธ GEQO ํŒŒ๋ผ๋ฏธํ„ฐ์— ๋Œ€ํ•œ ๊ธฐ๋ณธ๊ฐ’์„ ์„ค์ •ํ•˜๋Š”๋ฐ ์‚ฌ์šฉ
geqo_generations 0 0~2147483647 ์•Œ๊ณ ๋ฆฌ์ฆ˜์˜ ๋ฐ˜๋ณต ํšŸ์ˆ˜
geqo_pool_size 0 0~2147483647 ์ง‘๋‹จ์˜ ๊ฐœ์ฒด ์ˆ˜
geqo_seed 0.0 0.0~1.0 seed for random path selection
geqo_selection_bias 2.0 1.5~2.0 ์ง‘๋‹จ ๋‚ด์— ์„ ํƒ์••๋ ฅ(selective pressure)
geqo_threshold 12 2~2147483647 GEQO๊ฐ€ ์‚ฌ์šฉ๋˜๋Š” FROM ํ•ญ๋ชฉ์˜ ์ž„๊ณ„๊ฐ’

 

Other Planner Options

Parameter Default Value Range Description
constraint_exclusion partition on, off, partition ์ œ์•ฝ์กฐ๊ฑด์„ ์‚ฌ์šฉํ•˜์—ฌ ์ฟผ๋ฆฌ ์ตœ์ ํ™” ํ• ์ง€ ์—ฌ๋ถ€
• on : ๋ชจ๋“  ํ…Œ์ด๋ธ” ์ œ์•ฝ ์กฐ๊ฑด ๊ฒ€์‚ฌ
cursor_tuple_fraction 0.1 0.0~1.0 ๊ฒ€์ƒ‰ํ•œ cursor’s rows์˜ ๋น„์œจ ์ถ”์ •์น˜
default_statistics_target 100 1~10000 ๊ธฐ๋ณธ ํ†ต๊ณ„ ๋Œ€์ƒ ์„ค์ •
• ๊ฐ’์ด ํด์ˆ˜๋ก Analyze๋ฅผ ์ˆ˜ํ–‰ํ•˜๋Š” ์‹œ๊ฐ„์€ ๋Š˜์ง€๋งŒ, ์˜ˆ์ƒ ๊ฐ’์„ ํ–ฅ์ƒ ๋จ
from_collapse_limit 8 1~2147483647 subquery๊ฐ€ ์ถ•์†Œ(collapsed)๋˜์ง€ ์•Š๋Š” FROM ๋ชฉ๋ก์˜ ํฌ๊ธฐ
jit on   JIT ์ปดํŒŒ์ผ ์‚ฌ์šฉ ์—ฌ๋ถ€
join_collapse_limit 8 1~2147483647 JOIN ๊ตฌ์„ฑ์ด ํ‰๋ช…ํ™” ๋˜์ง€ ์•Š๋Š” FROM ๋ชฉ๋ก์˜ ํฌ๊ธฐ
1 ⇒ disable
plan_cache_mode auto auto, force_generic_plan, force_custom_plan ์‚ฌ์šฉ์ž ๋˜๋Š” ์ผ๋ฐ˜ Plan ์„ ํƒ์„ ์ œ์–ด

 


REPORTING AND LOGGING

Where to Log

Parameter Default Value Range Description
event_source PostgreSQL   ์ด๋ฒคํŠธ ๋กœ๊ทธ์—์„œ ๋ฉ”์‹œ์ง€ ์‹๋ณ„์ž ํ‚ค์›Œ๋“œ(Windows์šฉ)
log_destination stderr stderr, csvlog, syslog, eventlog • ์„œ๋ฒ„ ๋กœ๊ทธ ์ถœ๋ ฅ ๋Œ€์ƒ์„ ์„ค์ •ํ•ฉ๋‹ˆ๋‹ค.
• PostgreSQL์€ stderr, csvlog ๋ฐ syslog๋ฅผ ํฌํ•จํ•˜์—ฌ ์„œ๋ฒ„ ๋ฉ”์‹œ์ง€๋ฅผ ๊ธฐ๋กํ•˜๋Š” ์—ฌ๋Ÿฌ ๋ฐฉ๋ฒ•์„ ์ง€์›ํ•ฉ๋‹ˆ๋‹ค. (Windows์—์„œ๋Š” ์ด๋ฒคํŠธ ๋กœ๊ทธ๋„ ์ง€์›๋ฉ๋‹ˆ๋‹ค.)
log_diretory log   logging_collector ํ™œ์„ฑํ™”์ธ ๊ฒฝ์šฐ ๋กœ๊ทธ ํŒŒ์ผ์˜ ๋””๋ ‰ํ† ๋ฆฌ๋ฅผ ๊ฒฐ์ •ํ•ฉ๋‹ˆ๋‹ค.
log_file_mode 0600   ๋กœ๊ทธ ํŒŒ์ผ์— ๊ถŒํ•œ
log_filename postgresql-%Y-%m-%d_%H%M%S.log   ๋กœ๊ทธ ํŒŒ์ผ ์ด๋ฆ„ ํŒจํ„ด
logging_collector off   stderr ๋˜๋Š” csvlog๋กœ ์ „์†ก๋œ ๋กœ๊ทธ ๋ฉ”์‹œ์ง€๋ฅผ ํŒŒ์ผ๋กœ ์บก์ฒ˜ํ•˜๋Š” ํ•˜์œ„ ํ”„๋กœ์„ธ์Šค ์‹œ์ž‘์—ฌ๋ถ€๋ฅผ ๊ฒฐ์ •ํ•˜๋Š” ํŒŒ๋ผ๋ฏธํ„ฐ์ž…๋‹ˆ๋‹ค.
log_rotation_age 1440min (1d) 0~35791394min ์„ค์ •๊ฐ’(๊ธฐ๊ฐ„) ํ›„ ๋กœ๊ทธํŒŒ์ผ ์ˆœํ™˜
0 ⇒ disable
log_rotation_size 10240kB (10MB) 0~2097151kB ์„ค์ •๊ฐ’(ํฌ๊ธฐ) ํ›„ ๋กœ๊ทธํŒŒ์ผ ์ˆœํ™˜
0 ⇒ disable
log_truncate_on_rotation off   ๋กœ๊ทธํŒŒ์ผ ์ˆœํ™˜ ์ค‘ ๋™์ผ ์ด๋ฆ„์˜ ๋กœ๊ทธ ์‚ญ์ œ์—ฌ๋ถ€
syslog_facility local0 local0~7 syslog ์„ค์ • ์‹œ syslog “facility” ์„ค์ •
syslog_ident postgres   syslog์—์„œ ๋ฉ”์‹œ์ง€ ์‹๋ณ„์ž ํ‚ค์›Œ๋“œ
syslog_sequence_numbers on   ์ค‘๋ณต ๋ฐฉ์ง€๋ฅผ ์œ„ํ•ด syslog ๋ฉ”์‹œ์ง€์— ์‹œํ€€์Šค ๋ฒˆํ˜ธ ์ถ”๊ฐ€์—ฌ๋ถ€
syslog_split_messages on   syslog๋ฅผ ์ค„๋ณ„๋กœ ๋‚˜๋ˆ„๊ณ  1024byte์— ๋ถ„ํ•  ํ• ์ง€ ์—ฌ๋ถ€

 

When to Log

Parameter Default Value Range Description
log_min_duration_sample -1ms -1~2147483647ms ๋ฌธ์žฅ ์ƒ˜ํ”Œ์ด ๊ธฐ๋ก๋˜๋Š” ์ตœ์†Œ ์‹คํ–‰ ์‹œ๊ฐ„.
์ƒ˜ํ”Œ๋ง์€ log_statement_sample_rate์— ์˜ํ•ด ๊ฒฐ์ •
-1 ⇒ disable , 0 ⇒ all
log_min_duration_statement -1ms -1~2147483647ms ๋ชจ๋“  ๋ฌธ์žฅ์ด ๊ธฐ๋ก๋˜๋Š” ์ตœ์†Œ ์‹คํ–‰ ์‹œ๊ฐ„
-1 ⇒ disable, 0 ⇒ all
log_min_error_statement error debug1~5, info, notice, warning, error, log, fatal, panic ์„ค์ •๋œ ๋ ˆ๋ฒจ ์ด์ƒ์—์„œ ์˜ค๋ฅ˜๋ฅผ ์œ ๋ฐœํ•˜๋Š” ๋ชจ๋“  ๋ฌธ์žฅ ๊ธฐ๋ก
log_min_messages warning debug1~5, info, notice, warning, error, log, fatal, panic ๊ธฐ๋ก๋˜๋Š” ๋ฉ”์‹œ์ง€ ๋ ˆ๋ฒจ ์„ค์ •
log_statement_sample_rate 1.0 0.0~1.0 ์‹คํ–‰์‹œ๊ฐ„์ด log_min_duration_sample์„ ์ดˆ๊ณผํ•˜๋Š” ๋ฌธ์žฅ์„ ๊ธฐ๋กํ•˜๊ธฐ ์œ„ํ•œ ๋น„์œจ
0 ⇒ disable, 1 ⇒ all, 0.5 ⇒ ๋‘˜ ์ค‘ ํ•˜๋‚˜๋งŒ ๊ธฐ๋ก
log_transaction_sample_rate 0.0 0.0~1.0 ๋ชจ๋“  ๋ฌธ์žฅ์„ ๊ธฐ๋กํ•  ํŠธ๋žœ์žญ์…˜์˜ ๋น„์œจ
0 ⇒ disable, 1 ⇒ all

 

What to Log

Parameter Default Value Range Description
debug_pretty_print on   ๋“ค์—ฌ์“ฐ๊ธฐ ๊ตฌ๋ฌธ ๋ถ„์„ ๋ฐ plan ํŠธ๋ฆฌ ํ‘œ์‹œ ์—ฌ๋ถ€
debug_print_parse off   ๊ฐ ์ฟผ๋ฆฌ์˜ ๊ตฌ๋ฌธ ๋ถ„์„ ํŠธ๋ฆฌ ๊ธฐ๋ก ์—ฌ๋ถ€
debug_print_plan off   ๊ฐ ์ฟผ๋ฆฌ์˜ ์‹คํ–‰๊ณ„ํš ๊ธฐ๋ก ์—ฌ๋ถ€
debug_print_rewritten off   ๊ฐ ์ฟผ๋ฆฌ์˜ ์žฌ์ž‘์„ฑ๋œ ๊ตฌ๋ฌธ ๋ถ„์„ ํŠธ๋ฆฌ ๊ธฐ๋ก ์—ฌ๋ถ€
log_autovacuum_min_duration -1ms -1~2147483647ms [14๋ฒ„์ „์ถ”๊ฐ€]
• autovacuum ์ž‘์—…์„ ๋กœ๊ทธ์— ๊ธฐ๋กํ•  ์ตœ์†Œ ์‹คํ–‰์‹œ๊ฐ„์„ ์„ค์ •ํ•ฉ๋‹ˆ๋‹ค.
• log_autovacuum_min_duration๋ฅผ ์ดˆ๊ณผํ•˜์—ฌ ์‹คํ–‰๋˜๋Š” ๊ฒฝ์šฐ ๋กœ๊ทธ์— incident๋กœ ๊ธฐ๋ก๋˜๋ฏ€๋กœ autovauum ์ž‘์—… ํ™•์ธ์— ๋„์›€์ด ๋˜๋Š” ํŒŒ๋ผ๋ฏธํ„ฐ์ž…๋‹ˆ๋‹ค.
• -1 : autovauum ์ž‘์—…์— ๋Œ€ํ•˜์—ฌ ๋กœ๊ทธ ๋น„ํ™œ์„ฑํ™”
• 0 : ๋ชจ๋“  autovauum ์ž‘์—…์— ๋Œ€ํ•˜์—ฌ ๋กœ๊ทธ ๊ธฐ๋ก
log_checkpoints off   ๊ฐ Checkpoint ๊ธฐ๋ก ์—ฌ๋ถ€
log_connections off   ์„ฑ๊ณตํ•œ Connection ๋กœ๊ทธ ๊ธฐ๋ก ์—ฌ๋ถ€
log_disconnections off   ์„ธ์…˜ ์ข…๋ฃŒ ๋กœ๊ทธ ๊ธฐ๋ก(์ง€์†์‹œ๊ฐ„ ํฌํ•จ) ์—ฌ๋ถ€
log_duration off   ์™„๋ฃŒ๋œ SQL ๋ฌธ์žฅ์˜ ์ง€์†์‹œ๊ฐ„ ๊ธฐ๋ก ์—ฌ๋ถ€
log_error_verbosity default terse, default, verbose ๊ธฐ๋ก๋œ ๋ฉ”์‹œ์ง€์˜ ์ƒ์„ธ ๋‚ด์šฉ ์„ค์ •
log_hostname off   Connection ๋กœ๊ทธ์— host-name ๊ธฐ๋ก ์—ฌ๋ถ€
log_line_prefix %m [%p] %q%u@%d   ๊ฐ ๋กœ๊ทธ ์ค„์— ๋ถ™๋Š” ์ ‘๋‘์‚ฌ ์„ค์ •
log_lock_waits off   Long lock wait ์‹œ๊ฐ„ ๊ธฐ๋ก ์—ฌ๋ถ€
log_parameter_max_length -1 -1~1073741823B ๋ฌธ์žฅ์„ ๊ธฐ๋กํ• ๋•Œ, ๊ธฐ๋ก๋œ ํŒŒ๋ผ๋ฏธํ„ฐ ๊ฐ’์„ ์ฒ˜์Œ ์„ค์ •๊ฐ’(Byte)๋กœ ์ œํ•œ
0 ⇒ disable, -1 ⇒ full
log_parameter_max_length_on_error 0 -1~1073741823B ์˜ค๋ฅ˜ ๋ณด๊ณ  ์‹œ, ๊ธฐ๋ก๋œ ํŒŒ๋ผ๋ฏธํ„ฐ ๊ฐ’์„ ์ฒ˜์Œ ์„ค์ •๊ฐ’(Byte)๋กœ ์ œํ•œ
0 ⇒ disable, -1 ⇒ full
log_recovery_conflict_waits off   [14๋ฒ„์ „์ถ”๊ฐ€] Recovery conflicts์— ๋Œ€ํ•ด ์‹œ์ž‘ ํ”„๋กœ์„ธ์Šค๊ฐ€ deadlock_timeout ๋ณด๋‹ค ์˜ค๋ž˜ ๋Œ€๊ธฐ ํ•  ๋•Œ ๋ฉ”์‹œ์ง€๋ฅผ ์ƒ์„ฑํ• ์ง€ ์—ฌ๋ถ€
log_replication_commands off   ๊ฐ ๋ณต์ œ ๋ช…๋ น์–ด ๊ธฐ๋ก ์—ฌ๋ถ€
log_statement none none, ddl, mod, all ๊ธฐ๋ก๋˜๋Š” ๋ฌธ์žฅ์˜ ์œ ํ˜• ์„ค์ •
log_temp_files -1kB -1~2147493647kB ์„ค์ • ๊ฐ’๋ณด๋‹ค ํฐ ์ž„์‹œ ํŒŒ์ผ์˜ ์‚ฌ์šฉํ•˜์—ฌ ๊ธฐ๋ก
-1 ⇒ disable, 0 ⇒ all
log_timezone Etc/UTC    

 


PROCESS TITLE

Parameter Default Value Range Description
cluster_name 14/main   ํ”„๋กœ์„ธ์Šค์— ํฌํ•จ๋œ ํด๋Ÿฌ์Šคํ„ฐ์˜ ์ด๋ฆ„ ์„ค์ •
update_process_title on   Active SQL ๋ช…๋ น๋ฌธ์„ ํ‘œ์‹œํ•˜๋„๋ก ํ”„๋กœ์„ธ์Šค ์ œ๋ชฉ ์—…๋ฐ์ดํŠธ ์—ฌ๋ถ€

 


STATISTICS

Query and Index Statistics Collector

Parameter Default Value Range Description
stats_temp_directory /var/run/postgresql/14-main.pg_stat_tmp   • ํ†ต๊ณ„์ •๋ณด๋ฅผ ์œ„ํ•œ ์ž„์‹œ ์ €์žฅ ๊ฒฝ๋กœ๋ฅผ ๋‚˜ํƒ€๋ƒ…๋‹ˆ๋‹ค.(์ƒ๋Œ€ ๋˜๋Š” ์ ˆ๋Œ€ ๊ฒฝ๋กœ)
• ์ด ํŒŒ์ผ์€ ์ดˆ๋‹น ์ˆ˜๋ฐฑ๋ฒˆ ์—…๋ฐ์ดํŠธ๋˜๊ธฐ ๋•Œ๋ฌธ์— RAM ๊ธฐ๋ฐ˜์˜ ํŒŒ์ผ ์‹œ์Šคํ…œ์œผ๋กœ ์„ค์ •ํ•˜๋ฉด ๋ฌผ๋ฆฌ์  IO ์š”๊ตฌ์‚ฌํ•ญ์„ ์ค„์—ฌ์„œ ์„ฑ๋Šฅ์„ ํ–ฅ์ƒ์‹œํ‚ฌ ์ˆ˜ ์žˆ์Šต๋‹ˆ๋‹ค.
• ์„œ๋ฒ„๊ฐ€ ์™„์ „ํžˆ ์ข…๋ฃŒ๋˜๋ฉด ํ†ต๊ณ„ ๋ฐ์ดํ„ฐ์˜ ๋ณต์‚ฌ๋ณธ์ด pg_stat ํ•˜์œ„ ๋””๋ ‰ํ† ๋ฆฌ์— ์˜๊ตฌ์ ์œผ๋กœ ์ €์žฅ๋˜๋ฏ€๋กœ ์„œ๋ฒ„๋ฅผ ๋‹ค์‹œ ์‹œ์ž‘ํ•ด๋„ ํ†ต๊ณ„๋Š” ์œ ์ง€๋ฉ๋‹ˆ๋‹ค.
track_activities on   • ์„ธ์…˜์— ๋Œ€ํ•œ ํ†ต๊ณ„์ •๋ณด ์ˆ˜์ง‘ ๊ธฐ๋Šฅ์„ ์‚ฌ์šฉํ• ์ง€ ์—ฌ๋ถ€๋ฅผ ๋‚˜ํƒ€๋ƒ…๋‹ˆ๋‹ค.(์ˆ˜ํผ์œ ์ €๋งŒ ์„ค์ • ๋ณ€๊ฒฝ ๊ฐ€๋Šฅ)
• ๊ฐ ์„ธ์…˜์˜ ํ˜„์žฌ ์‹คํ–‰ ์ค‘์ธ command์— ๋Œ€ํ•œ ์ •๋ณด์™€ ์‹คํ–‰ ์‹œ์ž‘ ์‹œ๊ฐ„์„ ์ˆ˜์ง‘ํ•ฉ๋‹ˆ๋‹ค.
• ์ˆ˜ํผ์œ ์ € ๋ฐ ์„ธ์…˜์„ ์†Œ์œ ํ•œ ์‚ฌ์šฉ์ž๋งŒ ํ•ด๋‹น ์ •๋ณด๋ฅผ ํ™•์ธํ•  ์ˆ˜ ์žˆ์Šต๋‹ˆ๋‹ค.
track_activity_query_size 1024B 100~1048576B • ๊ฐ active session์—์„œ ํ˜„์žฌ ์‹คํ–‰๋œ command(pg_stat_activity.query)๋ฅผ ์ˆ˜์ง‘์— ์‚ฌ์šฉ๋˜๋Š” ๋ฐ”์ดํŠธ ์ˆ˜๋ฅผ ์„ค์ •ํ•ฉ๋‹ˆ๋‹ค.
• pg_stat_activity, pg_stat_statements ์—์„œ ์ฟผ๋ฆฌ๋ฅผ ์ž˜๋ผ๋‚ด๊ธฐ ์œ„ํ•œ ์ž„๊ณ„์น˜๋กœ ์‚ฌ์šฉํ•ฉ๋‹ˆ๋‹ค.
track_counts on   • ํ…Œ์ด๋ธ” ๋ฐ ์ธ๋ฑ์Šค ์•ก์„ธ์Šค์— ๋Œ€ํ•œ ํ†ต๊ณ„ ์ˆ˜์ง‘์—ฌ๋ถ€๋ฅผ ์„ค์ •ํ•ฉ๋‹ˆ๋‹ค.(์ˆ˜ํผ์œ ์ €๋งŒ ์„ค์ • ๋ณ€๊ฒฝ ๊ฐ€๋Šฅ)
• autovacuum ํ”„๋กœ์„ธ์Šค์—์„œ ์ด ํ†ต๊ณ„์ •๋ณด๋ฅผ ํ™œ์šฉํ•ด์„œ vacuum์„ ์ˆ˜ํ–‰ํ•˜๋ฏ€๋กœ ๊ธฐ๋ณธ์ ์œผ๋กœ ์„ค์ • on์œผ๋กœ ํ•ด๋‹น ์ •๋ณด๋ฅผ ์ˆ˜์ง‘ํ•ฉ๋‹ˆ๋‹ค.
track_functions none none, pl, all • ์‚ฌ์šฉ์ž ์ •์˜ function์— ๋Œ€ํ•œ ํ†ต๊ณ„ ์ •๋ณด ์ˆ˜์ง‘ ํŒŒ๋ผ๋ฏธํ„ฐ์ž…๋‹ˆ๋‹ค.(์ˆ˜ํผ์œ ์ €๋งŒ ์„ค์ • ๋ณ€๊ฒฝ ๊ฐ€๋Šฅ)
• function ํ˜ธ์ถœ ํšŸ์ˆ˜์™€ ์‚ฌ์šฉ์‹œ๊ฐ„ ๋“ฑ์˜ ์ •๋ณด๋ฅผ ์ˆ˜์ง‘ํ•ฉ๋‹ˆ๋‹ค.
• pl : procedure๋กœ ์ž‘์„ฑ๋œ ์–ธ์–ด function๋งŒ ์ˆ˜์ง‘
• all : ๋ชจ๋“  SQL ๋ฐ C์–ธ์–ด function๋„ ์ˆ˜์ง‘
• none : ๋น„ํ™œ์„ฑํ™”
track_io_timing off   • ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค I/O ํ™œ๋™์— ๋Œ€ํ•œ ํƒ€์ด๋ฐ ํ†ต๊ณ„ ์ˆ˜์ง‘ ๊ธฐ๋Šฅ์„ ์‚ฌ์šฉํ• ์ง€ ์—ฌ๋ถ€๋ฅผ ๋‚˜ํƒ€๋ƒ…๋‹ˆ๋‹ค.(์ˆ˜ํผ์œ ์ €๋งŒ ์„ค์ • ๋ณ€๊ฒฝ ๊ฐ€๋Šฅ)
• on์œผ๋กœ ์„ค์ •ํ•˜๋ฉด ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค ์‹œ๊ฐ„์ด ๋ฐ˜๋ณต์ ์œผ๋กœ ํ˜ธ์ถœ๋˜์–ด ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค์— ๋งŽ์€ ์˜ค๋ฒ„ํ—ค๋“œ๊ฐ€ ์ถ”๊ฐ€๋˜๊ธฐ ๋•Œ๋ฌธ์— ๊ธฐ๋ณธ๊ฐ’์€ off์ž…๋‹ˆ๋‹ค.(pg_test_timing ๋„๊ตฌ๋ฅผ ์‚ฌ์šฉํ•˜์—ฌ ํƒ€์ด๋ฐ ์˜ค๋ฒ„ํ—ค๋“œ๋ฅผ ์ธก์ •ํ•  ์ˆ˜ ์žˆ์Šต๋‹ˆ๋‹ค.)
track_wal_io_timing off   [14๋ฒ„์ „์ถ”๊ฐ€] WAL I/O ํ™œ๋™์— ๋Œ€ํ•œ ์‹œ๊ฐ„ํ†ต๊ณ„ ์ˆ˜์ง‘ ์—ฌ๋ถ€

 

Monitoring

Parameter Default Value Range Description
compute_query_id auto auto, on, off [14๋ฒ„์ „์ถ”๊ฐ€] ์ฟผ๋ฆฌ ์‹๋ณ„์ž ๊ณ„์‚ฐ
log_executor_stats off   Executor ์„ฑ๋Šฅ ํ†ต๊ณ„๋ฅผ ์„œ๋ฒ„ ๋กœ๊ทธ์— ๊ธฐ๋กํ• ์ง€ ์—ฌ๋ถ€
log_parser_stats off   Parser ์„ฑ๋Šฅ ํ†ต๊ณ„๋ฅผ ์„œ๋ฒ„ ๋กœ๊ทธ์— ๊ธฐ๋กํ• ์ง€ ์—ฌ๋ถ€
log_planner_stats off   Planner ์„ฑ๋Šฅ ํ†ต๊ณ„๋ฅผ ์„œ๋ฒ„ ๋กœ๊ทธ์— ๊ธฐ๋กํ• ์ง€ ์—ฌ๋ถ€
log_statement_stats off   ์„œ๋ฒ„ ๋กœ๊ทธ์— ๋ˆ„์  ์„ฑ๋Šฅ ํ†ต๊ณ„๋ฅผ ๊ธฐ๋กํ• ์ง€ ์—ฌ๋ถ€

 


AUTOVACUUM

Parameter Default Value Range Description
autovacuum on   • autovacuum subprocess(autovacuum launcher daemon)๋ฅผ ์‹œ์ž‘ํ• ์ง€ ์„ค์ •ํ•ฉ๋‹ˆ๋‹ค.(ํ•ด๋‹น ํŒŒ๋ผ๋ฏธํ„ฐ์˜ default ๊ฐ’์€ on์ด์ง€๋งŒ autovauum๋ฅผ ์ž‘๋™ํ•˜๋ ค๋ฉด track_counts๋„ ํ™œ์„ฑํ™”ํ•ด์•ผ ํ•ฉ๋‹ˆ๋‹ค.)
• off ๋กœ ์„ค์ •ํ•ด๋„ ํ•„์š”ํ•œ ๊ฒฝ์šฐ ์‹œ์Šคํ…œ์ด autovauum ํ”„๋กœ์„ธ์Šค๋ฅผ ์‹œ์ž‘ํ•ฉ๋‹ˆ๋‹ค.
• table storage ํŒŒ๋ผ๋ฏธํ„ฐ๋ฅผ ๋ณ€๊ฒฝํ•˜์—ฌ ๊ฐœ๋ณ„ ํ…Œ์ด๋ธ”์— ๋Œ€ํ•ด autovacuum ๊ธฐ๋Šฅ์„ ์‚ฌ์šฉํ•˜์ง€ ์•Š๋„๋ก ์„ค์ •ํ•  ์ˆ˜ ์žˆ์Šต๋‹ˆ๋‹ค.
autovacuum_analyze_scale_factor 0.1 0.0~100.0 ANALYZE ํŠธ๋ฆฌ๊ฑฐ ์—ฌ๋ถ€๋ฅผ ๊ฒฐ์ •ํ•  ๋•Œ, autovacuum_analyze_threshold์— ์ถ”๊ฐ€ํ•  ํ…Œ์ด๋ธ” ํฌ๊ธฐ์˜ ๋น„์œจ
autovacuum_analyze_threshold 50 0~2147483647 ANALYZE ํŠธ๋ฆฌ๊ฑฐํ•˜๋Š”๋ฐ ํ•„์š”ํ•œ insert, update, delete tuple์˜ ์ตœ์†Œ ์ˆ˜
autovacuum_freeze_max_age 200000000 100000~2000000000 • autovacuum ์ด ๊ฐ•์ œ๋กœ ์‹คํ–‰๋˜์–ด์•ผ ํ•˜๋Š” ํŠธ๋žœ์žญ์…˜ID์˜ ์ƒํ•œ๊ฐ’์„ ์„ค์ •ํ•ฉ๋‹ˆ๋‹ค.(table storage ํŒŒ๋ผ๋ฏธํ„ฐ๋ฅผ ๋ณ€๊ฒฝํ•˜์—ฌ ๊ฐœ๋ณ„ ํ…Œ์ด๋ธ”์— ๋Œ€ํ•ด ์„ค์ •์„ ์ค„์ผ ์ˆ˜ ์žˆ์Šต๋‹ˆ๋‹ค.)
• autovacuum์ด ๋น„ํ™œ์„ฑํ™”๋œ ๊ฒฝ์šฐ์—๋„ ํ•ด๋‹น ๊ฐ’์„ ์ดˆ๊ณผํ•˜๋ฉด autovacuum ํ”„๋กœ์„ธ์Šค๋ฅผ ์‹œ์ž‘ํ•ฉ๋‹ˆ๋‹ค.
autovacuum_max_workers 3 1~262143 • ํ•œ๋ฒˆ์— ์‹คํ–‰ํ•  ์ˆ˜ ์žˆ๋Š” autovacuum worker ํ”„๋กœ์„ธ์Šค์˜ ์ตœ๋Œ€ ์ˆ˜๋ฅผ ์„ค์ •ํ•ฉ๋‹ˆ๋‹ค.
• autovacuum worker ํ”„๋กœ์„ธ์Šค๋Š” ๊ฐœ๋ณ„๋กœ cpu, memory, I/O๋ฅผ ์‚ฌ์šฉํ•ฉ๋‹ˆ๋‹ค.
autovacuum_multixact_freeze_max_age 400000000 10000~2000000000 ํ…Œ์ด๋ธ” ๋‚ด์—์„œ multixact ID wraparound๋ฅผ ๋ฐฉ์ง€ํ•˜๊ธฐ ์œ„ํ•ด vacuum์ด ๊ฐ•์ œ ์‹คํ–‰๋˜๊ธฐ ์ „ ํ…Œ์ด๋ธ”์˜ pg_class.relminmxid ํ•„๋“œ๊ฐ€ ๋„๋‹ฌํ•  ์ˆ˜ ์žˆ๋Š” ์ตœ๋Œ€ ์ˆ˜
autovacuum_naptime 60s 1~2147483s • autovacuum ์‹คํ–‰ ์‚ฌ์ด์˜ sleep time ์„ ์ง€์ •ํ•ฉ๋‹ˆ๋‹ค.
• ์ฃผ๊ธฐ๋งˆ๋‹ค deamon์ด ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค๋ฅผ ํ™•์ธํ•ด์„œ VACUUM & ANALYZE๊ฐ€ ํ•„์š”ํ•œ ๊ฒฝ์šฐ ํ•ด๋‹น command๋ฅผ ์‹คํ–‰ํ•ฉ๋‹ˆ๋‹ค.
autovacuum_vacuum_cost_delay 2ms -1~100ms • autovacuum ์ž‘์—…์— ์‚ฌ์šฉ๋  cost ์ง€์—ฐ ๊ฐ’์„ ์ง€์ •ํ•ฉ๋‹ˆ๋‹ค.(-1์ด๋ฉฐ vacuum_cost_delay ๊ฐ’์„ ๋Œ€์‹  ์‚ฌ์šฉ)
• autovacuum_vacuum_cost_limit ๊ฐ’์„ ์ดˆ๊ณผํ•˜๋ฉด autovacuum_vacuum_cost_delay ์„ค์ •๋งŒํผ์˜ ์ง€์—ฐ์ด ๋ฐœ์ƒํ•ฉ๋‹ˆ๋‹ค.
autovacuum_vacuum_cost_limit -1 -1~10000 • autovacuum ์ž‘์—…์— ์‚ฌ์šฉ๋  ๋น„์šฉ ์ œํ•œ ๊ฐ’์„ ์ง€์ •ํ•ฉ๋‹ˆ๋‹ค.(-1์ด๋ฉด vacuum_cost_limit ๊ฐ’์„ ๋Œ€์‹  ์‚ฌ์šฉ)
• autovacuum workers ๊ฐ„์— ๋น„๋ก€์ ์œผ๋กœ ๋ถ„๋ฐฐ๋˜๋ฏ€๋กœ ๊ฐ worker ํ”„๋กœ์„ธ์Šค์— ๋Œ€ํ•œ limit ์˜ ํ•ฉ๊ณ„๊ฐ€ ์ด ํŒŒ๋ผ๋ฏธํ„ฐ ๊ฐ’์„ ์ดˆ๊ณผํ•˜์ง€ ์•Š์Šต๋‹ˆ๋‹ค.
autovacuum_vacuum_insert_scale_factor 0.2 0.0~100.0 Vacuum์„ ํŠธ๋ฆฌ๊ฑฐํ• ์ง€ ์—ฌ๋ถ€๋ฅผ ๊ฒฐ์ •ํ• ๋•Œ autovacuum_vacuum_insert_threshold์— ์ถ”๊ฐ€ํ•  ํ…Œ์ด๋ธ” ํฌ๊ธฐ์˜ ๋น„์œจ
autovacuum_vacuum_insert_threshold 1000 -1~2147483647 VACUUM์„ ํŠธ๋ฆฌ๊ฑฐํ•˜๋Š” ๋ฐ ํ•„์š”ํ•œ insert tuple์˜ ์ˆ˜
-1 ⇒ disable (insert ๊ธฐ๋ฐ˜์œผ๋กœ vacuum์„ ํŠธ๋ฆฌ๊ฑฐ ํ•˜์ง€ ์•Š์Œ)
autovacuum_vacuum_scale_factor 0.2 0~100 • autovacuum ์ด์ „์— ์—…๋ฐ์ดํŠธ ๋˜๋Š” ์‚ญ์ œ๋œ tuple ์˜ ์ˆ˜(retuple ๋น„์œจ)
• default๋Š” 0.2๋กœ ํ…Œ์ด๋ธ” ํฌ๊ธฐ์˜ 20%๋ฅผ ์ดˆ๊ณผํ•˜๋Š” ๊ฒฝ์šฐ VACUUM ํŠธ๋ฆฌ๊ฑฐ๋ฅผ ์‹คํ–‰ํ•ฉ๋‹ˆ๋‹ค.
autovacuum_vacuum_threshold 50 0~2147483647 autovacuum ์ด์ „์— ์—…๋ฐ์ดํŠธ ๋˜๋Š” ์‚ญ์ œํ•  tuple ์ˆ˜์˜ ์ž„๊ณ„๊ฐ’์„ ๋‚˜ํƒ€๋ƒ…๋‹ˆ๋‹ค.(์ด ๊ฐ’์„ ์ดˆ๊ณผํ•  ๊ฒฝ์šฐ autovauum/analyze๋ฅผ ๊ฐ๊ฐ ์ˆ˜ํ–‰ํ•ด์•ผ ํ•ฉ๋‹ˆ๋‹ค.)

 


CLIENT CONNECTION DEFAULTS

Statement Behavior

Parameter Default Value Range Description
bytea_output hex hex, escape bytea type์— ๋Œ€ํ•œ ์ถœ๋ ฅ ํ˜•์‹ ์„ค์ •
check_function_bodies on   CREATE FUNCTION, CREATE PROCEDURE ๋™์•ˆ ๋ณธ๋ฌธ ๋ฌธ์ž์—ด์˜ ์œ ํšจ์„ฑ ๊ฒ€์‚ฌ ์—ฌ๋ถ€
client_min_messages notice debug1~5, log, notice, warning, error ํด๋ผ์ด์–ธํŠธ์— ์ „์†ก๋˜๋Š” ๋ฉ”์‹œ์ง€ ๋ ˆ๋ฒจ
default_table_access_method heap   ์ƒˆ๋กœ์šด ํ…Œ์ด๋ธ”์— ๋Œ€ํ•œ ๊ธฐ๋ณธ ํ…Œ์ด๋ธ” ์•ก์„ธ์Šค ๋ฐฉ๋ฒ•์„ ์„ค์ •
default_tablespace     ํ…Œ์ด๋ธ”๊ณผ ์ธ๋ฑ์Šค๋ฅผ ์ƒ์„ฑํ•  ๊ธฐ๋ณธ ํ…Œ์ด๋ธ”์ŠคํŽ˜์ด์Šค ์„ค์ •
NULL = default
default_toast_compression pglz pglz, lz4 [14๋ฒ„์ „์ถ”๊ฐ€] ๋ฐ์ดํ„ฐ ์••์ถ• ๋ฐฉ๋ฒ• (13๋ฒ„์ „๊นŒ์ง€๋Š” pglz๋งŒ ์ง€์›)
lz4 ์••์ถ•๋ฐฉ๋ฒ•์„ ์‚ฌ์šฉํ•˜๊ธฐ ์œ„ํ•ด์„œ๋Š” PostgreSQL์„ --with-lz4๋กœ ๋นŒ๋“œํ•ด์•ผ ํ•จ
default_transaction_deferrable off   ์ƒˆ๋กœ์šด ํŠธ๋žœ์žญ์…˜์˜ default defferrable ์ƒํƒœ ์„ค์ •
default_transaction_isolation read committed serializable, repeatable read, read committed, read uncommitted ์ƒˆ๋กœ์šด ํŠธ๋žœ์žญ์…˜์˜ ํŠธ๋žœ์žญ์…˜ ๋ถ„๋ฆฌ ์ˆ˜์ค€ ์„ค์ •
default_transaction_read_only off   ์ƒˆ๋กœ์šด ํŠธ๋žœ์žญ์…˜์˜ ๊ธฐ๋ณธ ์ฝ๊ธฐ ์ „์šฉ ์ƒํƒœ ์„ค์ •
gin_pending_list_limit 4096kB 64~2147493647kB GIN ์ธ๋ฑ์Šค์— ๋Œ€ํ•œ ๋ณด๋ฅ˜ ์ค‘์ธ ๋ชฉ๋ก์˜ ์ตœ๋Œ€ ํฌ๊ธฐ
idle_in_transaction_session_timeout 0ms 0~2147493647ms ํŠธ๋žœ์žญ์…˜ ์ค‘ ์ฟผ๋ฆฌ ์‚ฌ์ด์— ํ—ˆ์šฉ๋˜๋Š” ์ตœ๋Œ€ idle ์‹œ๊ฐ„
0 ⇒ disable
idle_session_timeout 0ms 0~2147493647ms [14๋ฒ„์ „์ถ”๊ฐ€] ํŠธ๋žœ์žญ์…˜ ์ค‘์ด์ง€ ์•Š์„๋•Œ, ์ฟผ๋ฆฌ ์‚ฌ์ด์— ํ—ˆ์šฉ๋˜๋Š” ์ตœ๋Œ€ idle ์‹œ๊ฐ„
0 ⇒ disable
lock_timeout 0ms 0~2147483647ms Lock ๋Œ€๊ธฐ์˜ ์ตœ๋Œ€ ํ—ˆ์šฉ ์‹œ๊ฐ„
0 ⇒ disable
row_security on   Row security ์‚ฌ์šฉ ์—ฌ๋ถ€
search_path "$user", public   ์Šคํ‚ค๋งˆ๊ฐ€ ์ง€์ •๋˜์ง€ ์•Š์€ ๋‹จ์ˆœ ์ด๋ฆ„์œผ๋กœ Object๋ฅผ ์ฐธ์กฐํ•  ๋•Œ ์Šคํ‚ค๋งˆ๊ฐ€ ๊ฒ€์ƒ‰๋˜๋Š” ์ˆœ์„œ
session_replication_role origin origin, replica, local ํŠธ๋ฆฌ๊ฑฐ ๋ฐ ์žฌ์ž‘์„ฑ ๊ทœ์น™์— ๋Œ€ํ•œ ์„ธ์…˜ ๋™์ž‘ ์„ค์ •
statement_timeout 0ms 0~2147493647ms ๋ฌธ์žฅ์˜ ์ตœ๋Œ€ ํ—ˆ์šฉ ๊ธฐ๊ฐ„ ์„ค์ •
0 ⇒ disable
temp_tablespaces     Temp table๊ณผ sort file์— ์‚ฌ์šฉํ•  ํ…Œ์ด๋ธ”์ŠคํŽ˜์ด์Šค ์„ค์ •
vacuum_failsafe_age 1600000000 0~2100000000 [14๋ฒ„์ „์ถ”๊ฐ€] vacuum์ด ์‹œ์Šคํ…œ ์ „์ฒด์˜ ํŠธ๋žœ์žญ์…˜ID wraparound ์‹คํŒจ๋ฅผ ํ”ผํ•˜๊ธฐ ์œ„ํ•ด ํŠน๋ณ„ํ•œ ์กฐ์น˜๋ฅผ ์ทจํ•˜๊ธฐ ์ „์— ํ…Œ์ด๋ธ”์˜ pg_class.relfrozenxid ํ•„๋“œ๊ฐ€ ๋„๋‹ฌํ•  ์ˆ˜ ์žˆ๋Š” Age
vacuum_freeze_min_age 50000000 0~1000000000 Vacuum์ด table row๋ฅผ freezeํ•ด์•ผ ํ•˜๋Š” ์ตœ์†Œ Age
vacuum_freeze_table_age 150000000 0~2000000000 Vacuum์ด tuple์„ ๊ณ ์ •ํ•˜๊ธฐ ์œ„ํ•ด ์ „์ฒด ํ…Œ์ด๋ธ”์„ ์Šค์บ”ํ•ด์•ผ ํ•˜๋Š” Age
vacuum_multixact_failsafe_age 1600000000 0~2100000000 [14๋ฒ„์ „์ถ”๊ฐ€] vacuum์ด ์‹œ์Šคํ…œ ์ „์ฒด์˜ multixact ID wraparound ์‹คํŒจ๋ฅผ ํ”ผํ•˜๊ธฐ ์œ„ํ•ด ํŠน๋ณ„ํ•œ ์กฐ์น˜๋ฅผ ์ทจํ•˜๊ธฐ ์ „์— ํ…Œ์ด๋ธ”์˜ pg_class.relminmxidํ•„๋“œ๊ฐ€ ๋„๋‹ฌํ•  ์ˆ˜ ์žˆ๋Š” Age
vacuum_multixact_freeze_min_age 5000000 0~1000000000 Vacuum์ด table row์—์„œ MultiXactID๋ฅผ freezeํ•ด์•ผ ํ•˜๋Š” ์ตœ์†Œ Age
vacuum_multixact_freeze_table_age 150000000 0~2000000000 Vacuum์ด tuple์„ ๊ณ ์ •ํ•˜๊ธฐ ์œ„ํ•ด ์ „์ฒด ํ…Œ์ด๋ธ”์„ ์Šค์บ”ํ•ด์•ผ ํ•˜๋Š” Multixact Age
xmlbinary base64 base64, hex binary ๊ฐ’์„ XML๋กœ ์ธ์ฝ”๋”ฉํ•˜๋Š” ๋ฐฉ๋ฒ• ์„ค์ •
xmloption content content, document ์•”์‹œ์  ๊ตฌ๋ฌธ ๋ถ„์„ ๋ฐ ์ง๋ ฌํ™” ์ž‘์—…์˜ XML ๋ฐ์ดํ„ฐ๋ฅผ ๋ฌธ์„œ ๋˜๋Š” ์ฝ˜ํ…์ธ  ์กฐ๊ฐ์œผ๋กœ ๊ฐ„์ฃผํ• ์ง€ ์—ฌ๋ถ€

 

Locale and Formatting

Parameter Default Value Range Description
client_encoding sql_ascii   ํด๋ผ์ด์–ธํŠธ์˜ ๋ฌธ์ž ์ง‘ํ•ฉ ์ธ์ฝ”๋”ฉ ์„ค์ •
datestyle iso, mdy   ๋‚ ์งœ์™€ ์‹œ๊ฐ„ ๊ฐ’์˜ ํ‘œ์‹œ ํ˜•์‹ ์„ค์ •
default_text_search_config pg_catalog.english   ๊ธฐ๋ณธ ํ…์ŠคํŠธ ๊ฒ€์ƒ‰ ๊ตฌ์„ฑ ์„ค์ •
extra_float_digits 1 -15~3 ๋ถ€๋™ ์†Œ์ˆ˜์ ์œผ๋กœ ํ‘œ์‹œ๋˜๋Š” ์ž๋ฆฟ์ˆ˜ ์„ค์ •
intervalstyle postgres postgres, postgres_verbose, sql-standard, iso_8601 Interval Value์˜ ํ‘œ์‹œ ํ˜•์‹ ์„ค์ •
lc_messages en_US.UTF-8   ๋ฉ”์‹œ์ง€๊ฐ€ ํ‘œ์‹œ๋˜๋Š” ์–ธ์–ด ์„ค์ •
lc_monetary C   ํ™”ํ ๊ธˆ์•ก์˜ ํ˜•์‹ ์„ค์ •
lc_numeric en_US.UTF-8   ์ˆซ์ž ํ˜•์‹ ์„ค์ •
lc_time en_US.UTF-8   ๋‚ ์งœ์™€ ์‹œ๊ฐ„ ํ˜•์‹ ์„ค์ •
timezone GMT   Timestamp๋ฅผ ํ‘œ์‹œํ•˜๊ณ  ํ•ด์„ํ•˜๊ธฐ ์œ„ํ•œ ์‹œ๊ฐ„๋Œ€ ์„ค์ •
timezone_abbreviations Default   ์‹œ๊ฐ„๋Œ€ ์•ฝ์–ดํŒŒ์ผ ์„ ํƒ

 

Shared Library Preloading

Parameter Default Value Range Description
jit_provider llvmjit   ์‚ฌ์šฉํ•˜๊ณ ์ž ํ•˜๋Š” JIT Provider
local_preload_libraries     ๊ฐ backend์—์„œ ๋ฏธ๋ฆฌ ๋กœ๋“œํ•  ๊ถŒํ•œ์ด ์—†๋Š” ๊ณต์œ  ๋ผ์ด๋ธŒ๋Ÿฌ๋ฆฌ ๋‚˜์—ด
session_preload_libraries     ๊ฐ backend์— ๋ฏธ๋ฆฌ ๋กœ๋“œํ•  ๊ณต์œ ๋ผ์ด๋ธŒ๋Ÿฌ๋ฆฌ ๋‚˜์—ด
shared_preload_libraries     ์„œ๋ฒ„์— ๋ฏธ๋ฆฌ ๋กœ๋“œํ•  ๊ณต์œ  ๋ผ์ด๋ธŒ๋Ÿฌ๋ฆฌ ๋‚˜์—ด

 

Other Defaults

Parameter Default Value Range Description
dynamic_library_path $libdir   ๋™์ ์œผ๋กœ ๋กœ๋“œํ•  ์ˆ˜ ์žˆ๋Š” ๋ชจ๋“ˆ์˜ ๊ฒฝ๋กœ
extension_destdir     extension ๋กœ๋“œ๋ฅผ ์œ„ํ•ด ์ถ”๊ฐ€ํ•  ๊ฒฝ๋กœ
gin_fuzzy_search_limit 0 0~2147483647 GIN์— ์˜ํ•œ ์ •ํ™•ํ•œ ๊ฒ€์ƒ‰์„ ์œ„ํ•ด ํ—ˆ์šฉ๋˜๋Š” ์ตœ๋Œ€ ๊ฒฐ๊ณผ ์„ค์ •

 


LOCK MANAGEMENT

Parameter Default Value Range Description
deadlock_timeout 1000ms 1~2147483647ms deadlock ์ƒํƒœ๋ฅผ ํ™•์ธํ•˜๊ธฐ ์ „ Lock ๋Œ€๊ธฐํ•˜๋Š” ์‹œ๊ฐ„
max_locks_per_transaction 64 10~2147483647 ํŠธ๋žœ์žญ์…˜๋‹น ์ตœ๋Œ€ Lock ์ˆ˜
max_locks_per_transaction * (max_connections + max_prepared_transactions)
max_pred_locks_per_page 2 0~2147483647 Page๋‹น predicate-locked tuple ์ตœ๋Œ€ ์ˆ˜
max_pred_locks_per_relation -2 -2147483648~2147483647 Relation๋‹น predicate-locked page ์™€ tuple์˜ ์ตœ๋Œ€ ์ˆ˜
0≤ ⇒ ์ œํ•œ ๊ฐ’, 0> ⇒ max_pred_locks_per_transaction / abs(์„ค์ •๊ฐ’)
max_pred_locks_per_transaction 64 10~2147483647 ํŠธ๋žœ์žญ์…˜๋‹น ์ตœ๋Œ€ Predicate Lock ์ˆ˜
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   ๋ฐฐ์—ด์—์„œ NULL ์š”์†Œ ์ž…๋ ฅ ํ™œ์„ฑํ™” ์—ฌ๋ถ€
backslash_quote safe_encoding on, off, safe_encoding ๋ฌธ์ž์—ด Literal์—์„œ “\” ํ—ˆ์šฉ ์—ฌ๋ถ€
escape_string_warning on   ์ผ๋ฐ˜ ๋ฌธ์ž์—ด Literal์—์„œ ”\”์— ๋Œ€ํ•œ ๊ฒฝ๊ณ  ์—ฌ๋ถ€.
on ⇒ Literal์— ํ‘œ์‹œ, standard_conforming_strings=off ๊ฒฝ์šฐ ๊ฒฝ๊ณ  ๋ฐœ์ƒ
lo_compat_privileges off   ํฐ Object์— ๋Œ€ํ•œ ๊ถŒํ•œ ๊ฒ€์‚ฌ๋ฅผ ์œ„ํ•ด ์ด์ „ ๋ฒ„์ „๊ณผ์˜ ํ˜ธํ™˜์„ฑ ๋ชจ๋“œ ํ™œ์„ฑํ™” ์—ฌ๋ถ€
quote_all_identifiers off   SQL ์กฐ๊ฐ์„ ๋งŒ๋“ค๋•Œ, ๋ชจ๋“  ์‹๋ณ„์ž๋ฅผ ์ธ์šฉํ• ์ง€ ์—ฌ๋ถ€
standard_conforming_strings on   ‘...’ ๋ฌธ์ž์—ด์ด “\”๋ฅผ ๋ฌธ์ž ๊ทธ๋Œ€๋กœ ์ฒ˜๋ฆฌํ•˜๋„๋ก ํ• ์ง€ ์—ฌ๋ถ€
synchronize_seqscans on   ๋™๊ธฐํ™”๋œ sequential scan ํ™œ์„ฑํ™” ์—ฌ๋ถ€

 

Other Platforms and Clients

Parameter Default Value Range Description
transform_null_equals off   “expr=NULL”์„ “expr IS NULL”๋กœ ์ฒ˜๋ฆฌ ํ• ์ง€ ์—ฌ๋ถ€

 


ERROR HANDLING

Parameter Default Value Range Description
data_sync_retry off   ๋ฐ์ดํ„ฐ ํŒŒ์ผ ๋™๊ธฐํ™” ์‹คํŒจ ํ›„ ๊ณ„์† ์‹คํ–‰ํ• ์ง€ ์—ฌ๋ถ€
exit_on_error off   ์˜ค๋ฃจ ๋ฐœ์ƒ ์‹œ ์„ธ์…˜ ์ข…๋ฃŒ ์—ฌ๋ถ€
recovery_init_sync_method fsync fsync, syncfs [14๋ฒ„์ „์ถ”๊ฐ€] Crash Recovery์ „์— ๋ฐ์ดํ„ฐ ๋””๋ ‰ํ† ๋ฆฌ๋ฅผ ๋™๊ธฐํ™”ํ•˜๋Š” ๋ฐฉ๋ฒ• ์„ค์ •
restart_after_crash on   Backend crash ํ›„ ์„œ๋ฒ„ ์ดˆ๊ธฐํ™” ํ• ์ง€ ์—ฌ๋ถ€

 


Vacuum Series

Parameter Default Value Range Description
autovacuum ON   • autovacuum subprocess(autovacuum launcher daemon)๋ฅผ ์‹œ์ž‘ํ• ์ง€ ์„ค์ •ํ•ฉ๋‹ˆ๋‹ค.(ํ•ด๋‹น ํŒŒ๋ผ๋ฏธํ„ฐ์˜ default ๊ฐ’์€ on์ด์ง€๋งŒ autovauum๋ฅผ ์ž‘๋™ํ•˜๋ ค๋ฉด track_counts๋„ ํ™œ์„ฑํ™”ํ•ด์•ผ ํ•ฉ๋‹ˆ๋‹ค.) • off ๋กœ ์„ค์ •ํ•ด๋„ ํ•„์š”ํ•œ ๊ฒฝ์šฐ ์‹œ์Šคํ…œ์ด autovauum ํ”„๋กœ์„ธ์Šค๋ฅผ ์‹œ์ž‘ํ•ฉ๋‹ˆ๋‹ค.
• table storage ํŒŒ๋ผ๋ฏธํ„ฐ๋ฅผ ๋ณ€๊ฒฝํ•˜์—ฌ ๊ฐœ๋ณ„ ํ…Œ์ด๋ธ”์— ๋Œ€ํ•ด autovacuum ๊ธฐ๋Šฅ์„ ์‚ฌ์šฉํ•˜์ง€ ์•Š๋„๋ก ์„ค์ •ํ•  ์ˆ˜ ์žˆ์Šต๋‹ˆ๋‹ค.
autovacuum_freeze_max_age 200,000,000 -1~2,147,483,647 • autovacuum ์ด ๊ฐ•์ œ๋กœ ์‹คํ–‰๋˜์–ด์•ผ ํ•˜๋Š” ํŠธ๋žœ์žญ์…˜ID์˜ ์ƒํ•œ๊ฐ’์„ ์„ค์ •ํ•ฉ๋‹ˆ๋‹ค.(table storage ํŒŒ๋ผ๋ฏธํ„ฐ๋ฅผ ๋ณ€๊ฒฝํ•˜์—ฌ ๊ฐœ๋ณ„ ํ…Œ์ด๋ธ”์— ๋Œ€ํ•ด ์„ค์ •์„ ์ค„์ผ ์ˆ˜ ์žˆ์Šต๋‹ˆ๋‹ค.)
• autovacuum์ด ๋น„ํ™œ์„ฑํ™”๋œ ๊ฒฝ์šฐ์—๋„ ํ•ด๋‹น ๊ฐ’์„ ์ดˆ๊ณผํ•˜๋ฉด autovacuum ํ”„๋กœ์„ธ์Šค๋ฅผ ์‹œ์ž‘ํ•ฉ๋‹ˆ๋‹ค.
autovacuum_max_workers 3 1~262,143 • ํ•œ๋ฒˆ์— ์‹คํ–‰ํ•  ์ˆ˜ ์žˆ๋Š” autovacuum worker ํ”„๋กœ์„ธ์Šค์˜ ์ตœ๋Œ€ ์ˆ˜ ๋ฅผ ์„ค์ •ํ•ฉ๋‹ˆ๋‹ค.
• autovacuum worker ํ”„๋กœ์„ธ์Šค๋Š” ๊ฐœ๋ณ„๋กœ cpu, memory, I/O๋ฅผ ์‚ฌ์šฉํ•ฉ๋‹ˆ๋‹ค.
autovacuum_naptime 60s 1~2,147,483s • autovacuum ์‹คํ–‰ ์‚ฌ์ด์˜ sleep time ์„ ์ง€์ •ํ•ฉ๋‹ˆ๋‹ค.
• ์ฃผ๊ธฐ๋งˆ๋‹ค deamon์ด ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค๋ฅผ ํ™•์ธํ•ด์„œ VACUUM & ANALYZE๊ฐ€ ํ•„์š”ํ•œ ๊ฒฝ์šฐ ํ•ด๋‹น command๋ฅผ ์‹คํ–‰ํ•ฉ๋‹ˆ๋‹ค.
autovacuum_vacuum_insert_scale_factor 0.2 0~100.0 Vacuum์„ ํŠธ๋ฆฌ๊ฑฐํ• ์ง€ ์—ฌ๋ถ€๋ฅผ ๊ฒฐ์ •ํ• ๋•Œ autovacuum_vacuum_insert_threshold์— ์ถ”๊ฐ€ํ•  ํ…Œ์ด๋ธ” ํฌ๊ธฐ์˜ ๋น„์œจ
autovacuum_vacuum_insert_threshold 10,000 -1~2,147,483,647 VACUUM์„ ํŠธ๋ฆฌ๊ฑฐํ•˜๋Š” ๋ฐ ํ•„์š”ํ•œ insert tuple์˜ ์ˆ˜ -1 ⇒ disable (insert ๊ธฐ๋ฐ˜์œผ๋กœ vacuum์„ ํŠธ๋ฆฌ๊ฑฐ ํ•˜์ง€ ์•Š์Œ)
autovacuum_vacuum_scale_factor 0.2 0~100.0 • autovacuum ์ด์ „์— ์—…๋ฐ์ดํŠธ ๋˜๋Š” ์‚ญ์ œ๋œ tuple ์˜ ์ˆ˜(retuple ๋น„์œจ)
• default๋Š” 0.2๋กœ ํ…Œ์ด๋ธ” ํฌ๊ธฐ์˜ 20%๋ฅผ ์ดˆ๊ณผํ•˜๋Š” ๊ฒฝ์šฐ VACUUM ํŠธ๋ฆฌ๊ฑฐ๋ฅผ ์‹คํ–‰ํ•ฉ๋‹ˆ๋‹ค.
autovacuum_vacuum_threshold 50 0~2,147,483,647 autovacuum ์ด์ „์— ์—…๋ฐ์ดํŠธ ๋˜๋Š” ์‚ญ์ œํ•  tuple ์ˆ˜์˜ ์ž„๊ณ„๊ฐ’์„ ๋‚˜ํƒ€๋ƒ…๋‹ˆ๋‹ค.(์ด ๊ฐ’์„ ์ดˆ๊ณผํ•  ๊ฒฝ์šฐ autovauum/analyze๋ฅผ ๊ฐ๊ฐ ์ˆ˜ํ–‰ํ•ด์•ผ ํ•ฉ๋‹ˆ๋‹ค.)
vacuum_freeze_min_age 50,000,000 0~1,000,000,000 Vacuum์ด table row๋ฅผ freezeํ•ด์•ผ ํ•˜๋Š” ์ตœ์†Œ Age
vacuum_freeze_table_age 150,000,000 0~2,000,000,000 Vacuum์ด tuple์„ ๊ณ ์ •ํ•˜๊ธฐ ์œ„ํ•ด ์ „์ฒด ํ…Œ์ด๋ธ”์„ ์Šค์บ”ํ•ด์•ผ ํ•˜๋Š” Age
track_counts ON   • ํ…Œ์ด๋ธ” ๋ฐ ์ธ๋ฑ์Šค ์•ก์„ธ์Šค์— ๋Œ€ํ•œ ํ†ต๊ณ„ ์ˆ˜์ง‘์—ฌ๋ถ€๋ฅผ ์„ค์ •ํ•ฉ๋‹ˆ๋‹ค.(์ˆ˜ํผ์œ ์ €๋งŒ ์„ค์ • ๋ณ€๊ฒฝ ๊ฐ€๋Šฅ)
• autovacuum ํ”„๋กœ์„ธ์Šค์—์„œ ์ด ํ†ต๊ณ„์ •๋ณด๋ฅผ ํ™œ์šฉํ•ด์„œ vacuum์„ ์ˆ˜ํ–‰ํ•˜๋ฏ€๋กœ ๊ธฐ๋ณธ์ ์œผ๋กœ ์„ค์ • on์œผ๋กœ ํ•ด๋‹น ์ •๋ณด๋ฅผ ์ˆ˜์ง‘ํ•ฉ๋‹ˆ๋‹ค.

 

๋Œ“๊ธ€0