클러스터 규모별 PostgreSQL 튜닝 — CDP 메타스토어 postgresql.conf 완전 가이드

PostgreSQL을 설치하면 따라오는 postgresql.conf 기본값은 프리사이즈 티셔츠 같습니다. 노트북에서 돌리든 1,000노드 클러스터의 메타스토어로 쓰든 똑같이 입혀지죠. 작은 실습 환경에서는 그럭저럭 맞지만, CDP(Cloudera Data Platform)처럼 수많은 서비스가 한 데이터베이스에 매달리는 환경에서는 맞춤 정장이 필요합니다.

Cloudera 공식 문서에도 규모별 권장값이 있긴 합니다. 그런데 막상 읽어보면 뭔가 허전합니다. shared_buffers와 체크포인트 몇 개만 있고, 실제 튜닝의 절반을 차지하는 work_mem·effective_cache_size·autovacuum 이야기가 통째로 빠져 있거든요. 게다가 숫자도 shared_buffers=256MB처럼 요즘 기준으론 너무 작습니다.

한 문장으로: Cloudera의 표는 "최소 동작선"이지 "튜닝"이 아닙니다. 이 글은 그 표를 출발점으로 삼아, 규모(=DB 서버 RAM)와 CDP 서비스 구성에 맞춰 빠진 파라미터까지 한 번에 잡습니다.

이 글은 PostgreSQL을 깊게 다뤄본 적 없는 분도 따라올 수 있도록, 각 파라미터가 무슨 일을 하는지 비유로 먼저 풀고 → 규모별 권장값 → 복붙용 설정까지 단계적으로 갑니다. 기준 버전은 PostgreSQL 14(CDP 7.1.9가 지원하는 최신 구간), 디스크는 SSD, DB는 메타스토어 전용 서버로 가정합니다.

이 글에서 배우는 것

• CDP에서 PostgreSQL이 멈추면 왜 클러스터 전체가 멈추는지
• 규모를 "호스트 수"가 아니라 "DB 서버 RAM"으로 봐야 하는 이유
• Cloudera가 빠뜨린 메모리 4종·autovacuum·로깅·HA까지 규모별로 잡는 법
• 그대로 붙여 넣을 수 있는 규모별 postgresql.conf 4종 세트
• 적용 후 "잘 먹혔는지" 확인하는 쿼리

1. 왜 기본값으론 안 되는가 — CDP에서 PostgreSQL의 위치

CDP에서 PostgreSQL은 그냥 "DB 하나"가 아닙니다. 여러 핵심 서비스의 메타데이터 저장소입니다. Hive Metastore(HMS)가 테이블·파티션 정보를, Ranger가 권한 정책을, Oozie가 워크플로 상태를, Cloudera Manager가 클러스터 구성을 전부 이 한 곳(또는 한 서버의 여러 DB)에 둡니다.

그래서 이 DB가 느려지면 증상이 엉뚱한 곳에서 터집니다. "Hive 쿼리가 갑자기 느려졌다", "잡 제출이 타임아웃 난다" 같은 신고가 들어오는데, 범인은 메타스토어 PostgreSQL의 autovacuum이 밀려서 테이블이 부어 있는 경우가 흔하죠. 기본값으로 방치하면 안 되는 이유가 여기 있습니다.

직관: HMS는 파티션을 수시로 추가·삭제합니다. 즉 PostgreSQL 입장에서 메타스토어는 "조회만 하는 얌전한 DB"가 아니라 변경이 폭주하는 DB예요. 이 성격이 뒤에 나올 autovacuum 튜닝(7장)의 핵심 근거가 됩니다.

2. Cloudera 공식 권장값 — 출발점이자 "최소 동작선"

먼저 원문이 권장하는 값을 그대로 정리합니다. 정직하게 말하면, 이건 "여기서 시작하라"는 바닥값입니다.

모든 규모 공통 (필수 호환 설정)

규모별 (원문 표)

버전 관련 주석

• PostgreSQL 9.5+에서 checkpoint_segments는 사라지고 min_wal_size·max_wal_size로 대체됩니다. 환산식: max_wal_size = (3 × checkpoint_segments) × 16MB.
• PostgreSQL 12+에서는 jit=off 권장.
• Hive·Oozie 데이터베이스에는 standard_conforming_strings=off.

⚠️ 이 표의 두 가지 함정. ① shared_buffers=256MB는 RAM이 16GB만 돼도 너무 작습니다(권장은 RAM의 25%). ② checkpoint_segments는 PostgreSQL 14에 아예 없는 옛 파라미터입니다. 그대로 적어 넣으면 기동조차 안 됩니다. 아래에서 현행 표기로 번역해 드립니다.

3. 규모를 다시 정의하자 — "호스트 수"가 아니라 "DB 서버 RAM"

Cloudera 표는 규모를 호스트 수로 나눕니다. 그런데 메모리 파라미터(shared_buffers, work_mem 등)를 실제로 결정하는 건 호스트 수가 아니라 DB 서버에 꽂힌 RAM입니다. 호스트 수는 "커넥션이 얼마나 몰리는가"에만 영향을 주죠.

그래서 두 축을 이렇게 매핑해 4단계로 봅니다.

한 문장으로: 메모리 파라미터는 RAM으로, 커넥션 파라미터는 호스트 수(=서비스 부하)로 정한다 — 이게 이 글이 Cloudera 표를 다시 짠 방식입니다.

4. 메모리 4종 세트 — Cloudera가 빠뜨린 절반

PostgreSQL 메모리 튜닝의 핵심은 네 개입니다. Cloudera 표에는 첫 번째(shared_buffers)만 있습니다. 나머지 셋이 없으면 절반만 한 셈이에요.

• shared_buffers — 공용 작업 책상. PostgreSQL이 디스크 데이터를 올려두고 함께 쓰는 공유 캐시입니다. 통념대로 **RAM의 25%**가 출발점. 무작정 키우면 OS 캐시와 이중으로 메모리를 먹어 오히려 손해라, 25%선을 지키는 게 좋습니다.
• effective_cache_size — "사실 캐시가 이만큼 있어요"라는 귀띔. 실제로 메모리를 잡는 값이 아닙니다. 플래너가 "인덱스를 쓸까 풀스캔할까" 판단할 때 참고하는 추정치예요. 보통 **RAM의 50~75%**로 적어주면 플래너가 인덱스를 더 적극적으로 씁니다.
• work_mem — 연산 한 건당 받는 메모장. 정렬·해시 조인 한 건이 쓰는 메모리입니다. 함정은 곱셈이에요. work_mem × 동시 연산 수만큼 쓰일 수 있어서, 커넥션이 수백 개인 메타스토어에서 이 값을 크게 잡으면 메모리가 순식간에 터집니다. 그래서 일부러 보수적으로 잡습니다.
• maintenance_work_mem — 청소·인덱스 전용 큰 책상. VACUUM, 인덱스 생성처럼 무거운 유지보수 작업이 쓰는 메모리입니다. autovacuum이 빠르게 끝나려면 넉넉해야 하지만 보통 2GB에서 효용이 포화됩니다.

규모별 권장값:

팁: work_mem는 전역값을 낮게 두고, 무거운 배치 세션에서만 SET work_mem='256MB';로 올려 쓰는 게 안전합니다. 전역으로 키우면 평범한 커넥션 수백 개가 동시에 그 메모리를 요구할 수 있어요.

5. 커넥션 사이징 — CDP는 "멀티-DB"라는 점을 기억하라

Cloudera는 max_connections = DB당 100 + 50이라고만 합니다. 그런데 어떤 DB가 몇 개냐가 핵심이에요. CDP는 서비스마다 DB가 따로고, 각 서비스가 자기 커넥션 풀을 잡습니다.

이걸 다 더하고 여유분을 얹는 식으로 역산해야 실제 max_connections가 나옵니다. 단, 커넥션 하나하나가 메모리(백엔드 프로세스 + 최악의 경우 work_mem)를 먹으므로 무작정 키우면 안 됩니다.

5b. Large 이상은 PgBouncer를 권합니다

max_connections를 1,000, 2,000으로 올리는 건 답이 아닙니다. 유휴 커넥션도 메모리와 스케줄링 비용을 먹으니까요. 대신 PgBouncer(커넥션 풀러)를 앞에 두고 transaction 풀링을 쓰면, 서비스들이 요구하는 수천 개의 논리 커넥션을 실제 수백 개의 물리 커넥션으로 압축할 수 있습니다.

한 문장으로: 커넥션이 부족하면 max_connections를 올리기 전에 PgBouncer부터 고려하세요. 특히 Large(64GB)·XL(128GB) 구간에서는 사실상 필수입니다.

6. WAL와 체크포인트 — 옛 표기를 현행으로 번역하기

WAL(Write-Ahead Log)은 변경을 디스크에 반영하기 전에 먼저 적어두는 일기장입니다. 체크포인트는 그 일기 내용을 본 데이터 파일에 정식으로 옮겨 적는 순간이고요. 체크포인트가 너무 자주 일어나면 디스크가 출렁이고, 너무 뜸하면 복구가 느려집니다.

여기서 Cloudera 표의 checkpoint_segments를 PostgreSQL 14 현행 표기로 번역해야 합니다.

원문 환산값보다 우리가 크게 잡는 이유는, 메타스토어처럼 쓰기가 잦은 DB에서 max_wal_size가 작으면 체크포인트가 강제로 자주 터져 성능이 출렁이기 때문입니다.

참고: checkpoint_completion_target는 PostgreSQL 14부터 기본값이 0.9입니다(과거엔 0.5). 즉 Cloudera가 권장하던 값이 이제 디폴트가 됐죠. wal_buffers도 -1로 두면 자동(shared_buffers의 1/32, 최대 16MB)으로 잡혀서, 사실상 16MB와 같습니다.

7. autovacuum — 메타스토어의 청소부

PostgreSQL은 행을 수정·삭제해도 즉시 지우지 않고 "죽은 행(dead tuple)"으로 남겨둡니다. 이걸 주기적으로 치우는 청소부가 autovacuum이에요. 청소가 밀리면 테이블이 붓고(bloat), 통계가 낡아 플래너가 엉뚱한 실행 계획을 고릅니다 — 1장에서 말한 "쿼리가 갑자기 느려졌어요"의 단골 원인입니다.

HMS의 PARTITIONS, TBLS, SDS 같은 테이블은 변경이 폭주하므로 기본 autovacuum으로는 청소가 못 따라갑니다. 그래서 두 가지를 합니다.

① 전역 설정을 조금 더 공격적으로:

② 변경 폭주 테이블은 테이블 단위로 — 이게 Cloudera 문서엔 절대 안 나오는 실무 포인트입니다. 큰 테이블은 기본 scale_factor(20%)로는 너무 늦게 청소되므로 비율을 확 낮춥니다.

-- HMS의 변경 폭주 테이블만 더 자주 청소하도록 (예시)
ALTER TABLE "PARTITIONS"
  SET (autovacuum_vacuum_scale_factor = 0.02,
       autovacuum_analyze_scale_factor = 0.01);
 
ALTER TABLE "PARTITION_KEY_VALS"
  SET (autovacuum_vacuum_scale_factor = 0.02);

⚠️ autovacuum을 끄는 것은 거의 항상 나쁜 선택입니다. "잠깐 부하를 줄이려고" 끄면 dead tuple이 쌓여 나중에 훨씬 큰 비용으로 돌아옵니다. 느리면 끄는 게 아니라 워커·비용 한도를 올려 더 빠르게 돌게 하세요.

8. 플래너·병렬·I/O — SSD를 SSD답게

기본값 일부는 HDD 시절 가정에 맞춰져 있습니다. SSD를 쓰면서 그대로 두면 플래너가 손해를 봅니다.

직관: 메타스토어 쿼리는 대체로 작고 가볍습니다(인덱스 조회 위주). 그래서 병렬 워커를 과하게 키울 이유는 없지만, random_page_cost만큼은 SSD라면 반드시 낮추세요. 체감 효과가 가장 확실한 한 줄입니다.

9. 로깅 — "느려졌다"는 신고를 데이터로 바꾸기

문제가 터졌을 때 추측 대신 로그를 읽으려면 미리 켜둬야 합니다. 모든 규모 공통으로 권장합니다.

log_temp_files=0은 특히 유용합니다. 임시 파일이 자주 찍히면 "work_mem이 부족하다"는 신호거든요 — 4장으로 돌아가 조정할 근거가 됩니다.

10. CDP 필수 호환 설정 — 빠뜨리면 서비스가 안 붙는다

규모와 무관하게 반드시 들어가야 하는 호환 설정입니다.

# 인증 — PG14에선 password_encryption 기본값이 이미 scram-sha-256
password_encryption = scram-sha-256
listen_addresses = '*'          # 원격 접속 허용(방화벽/네트워크는 별도로 통제)
 
# PostgreSQL 12+ 권장
jit = off

여기에 DB 단위로 다음을 적용합니다(Hive·Oozie 호환).

ALTER DATABASE metastore  SET standard_conforming_strings = off;
ALTER DATABASE oozie      SET standard_conforming_strings = off;

그리고 pg_hba.conf에서 접근을 SCRAM으로 강제합니다.

# TYPE  DATABASE  USER  ADDRESS          METHOD
host    all       all   10.0.0.0/8       scram-sha-256

⚠️ 인코딩 함정: DB는 반드시 UTF-8이어야 합니다. initdb 시점에 LC_ALL="en_US.UTF-8"로 초기화하지 않으면, 나중에 한글·다국어 메타데이터에서 깨짐이 발생합니다. 이건 기동 후 파라미터로 못 고치고 초기화 단계에서만 바로잡을 수 있으니 처음에 확인하세요.

11. OS 레이어 — postgresql.conf 밖의 튜닝

DB만 잘 잡아도 OS가 발목을 잡으면 소용없습니다. 최소한 이 정도는 맞춰두세요.

• THP(Transparent Huge Pages) 비활성화 — 지연 스파이크의 단골 원인. never로.
• vm.swappiness = 1 — DB 서버가 스왑으로 새는 걸 막습니다.
• Huge Pages — shared_buffers가 크면(Large/XL) huge pages로 TLB 효율을 올립니다. huge_pages = try.
• WAL 전용 디스크 — 가능하면 WAL(pg_wal)을 데이터와 다른 빠른 디스크에 두면 체크포인트 출렁임이 줄어듭니다. 파일시스템은 noatime.

# THP 끄기 (예: 부팅 시 적용)
echo never > /sys/kernel/mm/transparent_hugepage/enabled
echo never > /sys/kernel/mm/transparent_hugepage/defrag
 
# 스왑 성향 최소화
sysctl -w vm.swappiness=1

12. HA와 백업 — 메타스토어는 단일 장애점이다

1장에서 봤듯 이 DB가 죽으면 클러스터가 멈춥니다. 그러니 백업과 이중화는 선택이 아니라 필수입니다. Cloudera 문서는 여기까지 다루지 않으니, 최소 골격만 짚습니다.

# 복제·PITR의 토대
wal_level = replica            # 물리 복제 + 시점 복구(PITR) 가능
archive_mode = on
archive_command = 'test ! -f /backup/wal/%f && cp %p /backup/wal/%f'
max_wal_senders = 10

• 백업: pg_basebackup로 베이스 백업 + WAL 아카이브 → PITR(특정 시점 복구) 가능. 논리 백업(pg_dump)도 정기적으로 병행하면 좋습니다.
• HA: 자동 장애 조치가 필요하면 Patroni(+etcd) 또는 repmgr로 스탠바이를 운영합니다. 메타스토어가 잠깐이라도 멈추면 비용이 크므로, Large 이상에서는 적극 권장합니다.

한 문장으로: wal_level=replica + WAL 아카이브부터 켜두면, 나중에 복제든 PITR이든 갈 길이 열립니다. 반대로 이게 없으면 사고가 났을 때 선택지가 없습니다.

13. 규모별 postgresql.conf — 복붙용 4종 세트

앞의 값을 한 덩어리로 모았습니다. SSD·PostgreSQL 14·메타스토어 전용 서버 가정입니다. 그대로 시작점으로 쓰되, 실제 워크로드를 보고 미세 조정하세요.

# ===== Small (호스트 < 50, RAM 16GB) =====
shared_buffers = 4GB
effective_cache_size = 12GB
work_mem = 16MB
maintenance_work_mem = 512MB
max_connections = 200
wal_buffers = 16MB
min_wal_size = 1GB
max_wal_size = 4GB
checkpoint_completion_target = 0.9
checkpoint_timeout = 15min
wal_compression = on
random_page_cost = 1.1
effective_io_concurrency = 200
max_worker_processes = 8
max_parallel_workers = 4
max_parallel_workers_per_gather = 2
autovacuum_max_workers = 3
autovacuum_vacuum_cost_limit = 1000
autovacuum_naptime = 30s
password_encryption = scram-sha-256
jit = off
listen_addresses = '*'
log_min_duration_statement = 1000
log_checkpoints = on
log_lock_waits = on
log_autovacuum_min_duration = 0
log_temp_files = 0

# ===== Medium (호스트 50~200, RAM 32GB) =====
shared_buffers = 8GB
effective_cache_size = 24GB
work_mem = 24MB
maintenance_work_mem = 1GB
max_connections = 400
wal_buffers = 16MB
min_wal_size = 2GB
max_wal_size = 8GB
checkpoint_completion_target = 0.9
checkpoint_timeout = 15min
wal_compression = on
random_page_cost = 1.1
effective_io_concurrency = 200
max_worker_processes = 16
max_parallel_workers = 8
max_parallel_workers_per_gather = 4
autovacuum_max_workers = 4
autovacuum_vacuum_cost_limit = 2000
autovacuum_naptime = 20s
password_encryption = scram-sha-256
jit = off
listen_addresses = '*'
log_min_duration_statement = 1000
log_checkpoints = on
log_lock_waits = on
log_autovacuum_min_duration = 0
log_temp_files = 0

# ===== Large (호스트 200~500, RAM 64GB, PgBouncer 권장) =====
shared_buffers = 16GB
effective_cache_size = 48GB
work_mem = 32MB
maintenance_work_mem = 2GB
max_connections = 600
wal_buffers = 16MB
min_wal_size = 4GB
max_wal_size = 16GB
checkpoint_completion_target = 0.9
checkpoint_timeout = 15min
wal_compression = on
random_page_cost = 1.1
effective_io_concurrency = 200
max_worker_processes = 32
max_parallel_workers = 16
max_parallel_workers_per_gather = 4
autovacuum_max_workers = 6
autovacuum_vacuum_cost_limit = 3000
autovacuum_naptime = 15s
huge_pages = try
password_encryption = scram-sha-256
jit = off
listen_addresses = '*'
wal_level = replica
archive_mode = on
log_min_duration_statement = 1000
log_checkpoints = on
log_lock_waits = on
log_autovacuum_min_duration = 0
log_temp_files = 0

# ===== XL (호스트 500~1000, RAM 128GB, PgBouncer 필수) =====
shared_buffers = 32GB
effective_cache_size = 96GB
work_mem = 48MB
maintenance_work_mem = 2GB
max_connections = 800
wal_buffers = 16MB
min_wal_size = 8GB
max_wal_size = 32GB
checkpoint_completion_target = 0.9
checkpoint_timeout = 15min
wal_compression = on
random_page_cost = 1.1
effective_io_concurrency = 200
max_worker_processes = 48
max_parallel_workers = 24
max_parallel_workers_per_gather = 6
autovacuum_max_workers = 6
autovacuum_vacuum_cost_limit = 4000
autovacuum_naptime = 10s
huge_pages = try
password_encryption = scram-sha-256
jit = off
listen_addresses = '*'
wal_level = replica
archive_mode = on
log_min_duration_statement = 1000
log_checkpoints = on
log_lock_waits = on
log_autovacuum_min_duration = 0
log_temp_files = 0

14. 적용과 검증 — "정말 먹혔나" 확인하기

설정을 바꿨다고 끝이 아닙니다. 일부는 재시작이 필요합니다.

• 재시작(restart) 필요: shared_buffers, max_connections, wal_buffers, huge_pages, max_worker_processes 등.
• 재적용(reload)만으로 충분: work_mem, effective_cache_size, random_page_cost, autovacuum 관련, 로깅 관련 등.

# reload (무중단)
sudo systemctl reload postgresql-14
# 또는
psql -c "SELECT pg_reload_conf();"

적용됐는지, 그리고 효과가 나는지 확인하는 쿼리:

-- 1) 값이 실제로 적용됐나
SELECT name, setting, unit, source
FROM pg_settings
WHERE name IN ('shared_buffers','work_mem','max_connections',
               'max_wal_size','random_page_cost','jit');
 
-- 2) 체크포인트가 너무 잦지 않나 (요청형이 많으면 max_wal_size를 키울 신호)
SELECT checkpoints_timed, checkpoints_req, buffers_checkpoint
FROM pg_stat_bgwriter;
 
-- 3) 테이블 bloat / autovacuum이 따라오나
SELECT relname, n_live_tup, n_dead_tup, last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;
 
-- 4) 현재 커넥션이 max_connections에 근접하나
SELECT count(*) AS conns,
       current_setting('max_connections') AS max_conns
FROM pg_stat_activity;

pg_stat_bgwriter에서 checkpoints_req(요청형)가 checkpoints_timed(주기형)보다 크게 많다면, WAL이 자주 차서 체크포인트가 강제로 터지는 것 — max_wal_size를 키울 때입니다.

15. 마치며 — 핵심 요약

길었지만 뼈대는 단순합니다.

• Cloudera 표는 출발점일 뿐입니다. shared_buffers와 체크포인트 몇 개로는 튜닝이 끝나지 않습니다.
• 규모는 RAM으로 봅니다. 메모리 파라미터는 DB 서버 RAM, 커넥션은 호스트 수(서비스 부하)로 정합니다.
• 메모리는 4종 세트(shared_buffers·effective_cache_size·work_mem·maintenance_work_mem)로 함께 잡아야 완성됩니다.
• 메타스토어는 변경이 폭주하는 DB라, autovacuum을 공격적으로 — 특히 HMS 대형 테이블은 테이블 단위로 — 잡는 게 핵심입니다.
• 커넥션은 무작정 키우지 말고, Large 이상은 PgBouncer로 압축하세요.
• checkpoint_segments는 PG14에 없습니다. min/max_wal_size로 번역해 넣으세요.
• 메타스토어는 단일 장애점입니다. wal_level=replica+WAL 아카이브로 백업·복구의 길을 미리 열어두세요.

처음이라면 욕심내지 말고, 자기 규모의 13장 스니펫을 시작점으로 올린 뒤 14장 검증 쿼리로 일주일쯤 지켜보세요. 체크포인트 빈도와 dead tuple 추이를 보며 max_wal_size와 autovacuum만 손봐도 체감 차이가 크게 납니다. 천천히 가도 괜찮습니다.

References

• Cloudera, "Configuring and Starting the PostgreSQL Server" (CDP Private Cloud Base 7.1.9) — https://docs.cloudera.com/cdp-private-cloud-base/7.1.9/installation/topics/cdpdc-configuring-starting-postgresql-server.html
• PostgreSQL 14 Documentation — Server Configuration — https://www.postgresql.org/docs/14/runtime-config.html
• PostgreSQL Wiki — Tuning Your PostgreSQL Server — https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server
• PgBouncer Documentation — https://www.pgbouncer.org/
• Patroni Documentation — https://patroni.readthedocs.io/

— Data Dynamics 엔지니어링 팀