성능 튜닝 — shared_buffers·work_mem·VACUUM

"느린 SELECT 가 있다" 면 EXPLAIN(17편)·인덱스(16편) 가 먼저. "전반적으로 느린다" 면 설정 파라미터·autovacuum·bloat 가 의심. 23편은 운영 DB 의 건강을 유지하는 설정·청소 작업.

핵심 메모리 파라미터 — 5개

# postgresql.conf 예시 (16GB RAM 서버)
shared_buffers = 4GB
effective_cache_size = 12GB
work_mem = 16MB
maintenance_work_mem = 1GB
wal_buffers = 16MB

커넥션 관련

max_connections = 100              # 동시 커넥션 최대
# 너무 크면 컨텍스트 스위치·메모리↑

# PgBouncer 같은 풀러 사용 권장 — 앱이 1000 커넥션이어도
# PG 에는 100 만 도달

WAL·체크포인트

wal_level = replica              # 또는 logical
max_wal_size = 1GB               # 체크포인트 트리거 크기
min_wal_size = 80MB
checkpoint_timeout = 5min        # 강제 체크포인트 주기
checkpoint_completion_target = 0.9   # IO 분산

autovacuum — DB 의 자동 청소

# autovacuum 기본 켜져있음 — 보통 그대로 두면 됨
autovacuum = on
autovacuum_max_workers = 3
autovacuum_naptime = 1min

# 트리거 임계값 (테이블 단위)
autovacuum_vacuum_threshold = 50
autovacuum_vacuum_scale_factor = 0.2   # 20% 변경되면 VACUUM
autovacuum_analyze_scale_factor = 0.1  # 10% 변경되면 ANALYZE

큰 테이블의 autovacuum 조정

-- 1억 행 테이블에서 20% = 2천만 행 변경 후에야 VACUUM
-- 너무 늦음 → 테이블별로 더 자주
ALTER TABLE big_table SET (
  autovacuum_vacuum_scale_factor = 0.02,   -- 2% 만 변경돼도
  autovacuum_analyze_scale_factor = 0.01
);

-- 반대로 거의 INSERT 만 되는 로그 테이블 — VACUUM 부담 줄이기
ALTER TABLE logs SET (autovacuum_vacuum_scale_factor = 0.5);

bloat 확인 — 죽은 행이 얼마나

-- 테이블의 dead row 수
SELECT relname,
       n_live_tup,
       n_dead_tup,
       round(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 1) AS dead_pct,
       last_autovacuum
FROM   pg_stat_user_tables
ORDER BY dead_pct DESC NULLS LAST
LIMIT 20;

-- dead_pct > 20% 면 VACUUM 부족 신호

수동 VACUUM

-- 일반 VACUUM (락 가벼움 — 운영 OK)
VACUUM users;
VACUUM ANALYZE users;       -- + 통계 갱신

-- VACUUM FULL (전체 재작성 — ACCESS EXCLUSIVE 락, 운영 시 위험)
VACUUM FULL users;

-- 대안 — pg_repack 확장 (락 없이 bloat 정리)
pg_repack -d mydb -t users

ANALYZE — 통계 갱신

-- 옵티마이저는 통계 정보로 계획 결정
-- 통계가 오래되면 잘못된 계획 (17편)
ANALYZE big_table;

-- 컬럼별 (큰 테이블에서 일부만)
ANALYZE big_table (created_at);

-- 통계 샘플 크기 조정
ALTER TABLE big_table ALTER COLUMN status SET STATISTICS 500;
-- 기본 100 → 500 (더 정확)

connection·query 별 진단

-- 현재 활성 쿼리 (15편 다시)
SELECT pid, usename, state, query_start, query
FROM   pg_stat_activity
WHERE  state != 'idle'
ORDER BY query_start;

-- 가장 느린 쿼리 (pg_stat_statements 필요)
SELECT round(total_exec_time::numeric, 0) AS total_ms,
       calls,
       round(mean_exec_time::numeric, 2)  AS mean_ms,
       query
FROM   pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

-- 캐시 히트율 (90% 미만이면 shared_buffers 부족)
SELECT
  sum(heap_blks_hit) * 100.0 / NULLIF(sum(heap_blks_hit + heap_blks_read), 0) AS hit_ratio
FROM pg_statio_user_tables;

실전 체크리스트 — 새 서버 셋업

1. shared_buffers = RAM × 25%.
2. effective_cache_size = RAM × 60%.
3. work_mem = 16MB 시작, EXPLAIN ANALYZE 로 조정.
4. maintenance_work_mem = 1GB (CREATE INDEX 빠름).
5. max_connections = 100 + PgBouncer 풀러.
6. checkpoint_timeout = 15min(대량 쓰기) ~ 5min(기본).
7. autovacuum on, 큰 테이블은 scale_factor 0.02~0.05.
8. pg_stat_statements 확장 + 정기 점검.
9. 주기적 ANALYZE (autovacuum 보완).
10. 모니터링 — Prometheus + postgres_exporter + Grafana.