코드노예

PostgreSQL은 기본 설정이 매우 보수적이다. 서버 사양에 맞게 튜닝하지 않으면 하드웨어의 10%도 활용하지 못한다. 실무에서 효과가 큰 순서대로 정리했다.

1. 메모리 설정

가장 효과가 큰 튜닝 영역이다. 대부분의 성능 문제는 여기서 해결된다.

shared_buffers

PostgreSQL이 데이터를 캐시하는 공유 메모리 영역이다. 기본값은 128MB로 너무 작다.

# 권장: 전체 RAM의 25%
# 16GB RAM 서버 기준
shared_buffers = 4GB

effective_cache_size

OS 파일 캐시를 포함하여 PostgreSQL이 사용할 수 있는 총 캐시 크기의 추정치다. 쿼리 플래너가 인덱스 스캔 여부를 결정할 때 참고한다. 실제 메모리를 할당하지는 않는다.

# 권장: 전체 RAM의 50~75%
effective_cache_size = 12GB

work_mem

정렬, 해시 조인 등 쿼리 내부 연산에 사용하는 메모리다. 연결 x 쿼리 x 연산 수만큼 곱해지므로 너무 크게 잡으면 OOM이 발생한다.

# 공식: RAM / max_connections / 4 (대략적 기준)
# 16GB RAM, 200 connections 기준
work_mem = 20MB

maintenance_work_mem

VACUUM, CREATE INDEX, ALTER TABLE 등 유지보수 작업에 사용하는 메모리다.

# 권장: 512MB ~ 2GB
maintenance_work_mem = 1GB

16GB RAM 서버 기준 요약:

2. WAL (Write-Ahead Log) 설정

쓰기 성능에 직접적인 영향을 주는 설정이다.

wal_buffers

# shared_buffers의 1/32, 최소 64MB
wal_buffers = 128MB

checkpoint 관련

체크포인트가 너무 자주 발생하면 I/O 스파이크가 생긴다.

# 체크포인트 간격 늘리기 (기본 5min)
checkpoint_timeout = 15min

# WAL 최대 크기 (기본 1GB)
max_wal_size = 4GB
min_wal_size = 1GB

# 체크포인트 I/O를 분산 (기본 0.9)
checkpoint_completion_target = 0.9

synchronous_commit

트랜잭션 커밋 시 WAL이 디스크에 기록될 때까지 기다릴지 결정한다.

# 쓰기 성능 향상 (약간의 데이터 손실 가능성 감수)
synchronous_commit = off

주의: off로 설정하면 크래시 시 최근 수백 ms의 트랜잭션이 유실될 수 있다. 로그성 데이터에는 괜찮지만, 결제 등 중요 데이터에는 on을 유지해야 한다.

3. 커넥션 관리

max_connections

PostgreSQL은 커넥션당 프로세스를 fork한다. 커넥션이 많을수록 컨텍스트 스위칭 비용이 증가한다.

# 직접 연결: 실제 필요한 수 + 여유분
max_connections = 200

Connection Pooler 사용 (권장)

애플리케이션이 수백~수천 커넥션을 사용한다면 PostgreSQL에 직접 연결하지 말고 PgBouncer를 사용한다.

# pgbouncer.ini
[databases]
myapp = host=127.0.0.1 port=5432 dbname=myapp

[pgbouncer]
listen_port = 6432
pool_mode = transaction    # 트랜잭션 단위로 커넥션 재사용
max_client_conn = 1000     # 클라이언트 최대 연결
default_pool_size = 50     # DB 실제 연결 수

이렇게 하면 PostgreSQL의 max_connections는 100 이하로 낮추고, 애플리케이션은 PgBouncer에 1000개까지 연결할 수 있다.

4. VACUUM과 Autovacuum

PostgreSQL은 MVCC 구조상 UPDATE/DELETE 후 죽은 튜플(dead tuple)이 쌓인다. VACUUM이 이를 정리한다. Autovacuum이 꺼져 있거나 너무 느리면 테이블 bloat과 성능 저하가 발생한다.

# autovacuum 기본 활성화 (절대 끄지 말 것)
autovacuum = on

# 워커 수 (대규모 DB에서 증가)
autovacuum_max_workers = 5

# 더 자주, 더 빠르게 실행
autovacuum_vacuum_cost_delay = 2ms      # 기본 2ms (이전 버전은 20ms)
autovacuum_vacuum_cost_limit = 1000     # 기본 -1 (vacuum_cost_limit 사용)

# 트리거 임계값 조정
autovacuum_vacuum_threshold = 50        # 기본 50
autovacuum_vacuum_scale_factor = 0.05   # 기본 0.2 (5%로 낮추면 더 자주 실행)

큰 테이블에 대한 개별 설정:

-- 1억 행 테이블은 scale_factor 0.01 (1%)로 설정
ALTER TABLE huge_table SET (
    autovacuum_vacuum_scale_factor = 0.01,
    autovacuum_analyze_scale_factor = 0.005
);

dead tuple 확인:

SELECT relname, n_dead_tup, n_live_tup,
       round(n_dead_tup::numeric / NULLIF(n_live_tup, 0) * 100, 2) AS dead_pct,
       last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;

5. 쿼리 분석 (EXPLAIN ANALYZE)

튜닝의 핵심은 느린 쿼리를 찾아서 고치는 것이다.

느린 쿼리 로깅 활성화:

# 200ms 이상 걸리는 쿼리 로깅
log_min_duration_statement = 200ms

EXPLAIN ANALYZE 사용법:

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM orders
WHERE user_id = 12345
  AND created_at > '2025-01-01';

실행 계획에서 주의할 키워드:

6. 인덱스 전략

기본 B-tree 인덱스:

-- 단일 컬럼
CREATE INDEX idx_orders_user_id ON orders (user_id);

-- 복합 인덱스 (선택도 높은 컬럼을 앞에)
CREATE INDEX idx_orders_user_created ON orders (user_id, created_at DESC);

Partial Index (조건부 인덱스):

특정 조건의 행만 인덱싱하여 크기를 줄이고 성능을 높인다.

-- 활성 주문만 인덱싱
CREATE INDEX idx_orders_active ON orders (created_at)
    WHERE status = 'active';

-- NULL이 아닌 행만
CREATE INDEX idx_users_email ON users (email)
    WHERE email IS NOT NULL;

Covering Index (INCLUDE):

인덱스만으로 쿼리를 완료하여 테이블 접근을 제거한다 (Index-Only Scan).

-- user_id로 검색하고 name, email을 반환하는 쿼리에 최적
CREATE INDEX idx_users_covering ON users (user_id) INCLUDE (name, email);

사용하지 않는 인덱스 찾기:

SELECT indexrelname, idx_scan, pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

사용되지 않는 인덱스는 쓰기 성능만 떨어뜨린다. 과감히 삭제한다.

7. 디스크 I/O 설정

# SSD 사용 시 (random I/O가 빠르므로 낮게)
random_page_cost = 1.1        # 기본 4.0 (HDD 기준)
seq_page_cost = 1.0

# 동시 I/O 워커
effective_io_concurrency = 200   # SSD: 200, HDD: 2

random_page_cost를 낮추면 플래너가 인덱스 스캔을 더 적극적으로 선택한다. SSD를 사용하는데 기본값(4.0)을 두면 불필요한 Seq Scan이 발생할 수 있다.

8. pg_stat_statements로 모니터링

실제 운영에서 느린 쿼리를 찾는 가장 효과적인 방법이다.

# postgresql.conf
shared_preload_libraries = 'pg_stat_statements'

# DB에서 확장 활성화
CREATE EXTENSION pg_stat_statements;

총 실행 시간 기준 상위 쿼리:

SELECT query,
       calls,
       round(total_exec_time::numeric, 2) AS total_ms,
       round(mean_exec_time::numeric, 2) AS avg_ms,
       rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

I/O가 많은 쿼리:

SELECT query,
       shared_blks_read + shared_blks_hit AS total_blks,
       round(100.0 * shared_blks_hit / NULLIF(shared_blks_read + shared_blks_hit, 0), 2) AS hit_pct
FROM pg_stat_statements
ORDER BY shared_blks_read DESC
LIMIT 10;

hit_pct가 낮은 쿼리는 캐시 미스가 잦다는 뜻이므로 인덱스나 쿼리 최적화가 필요하다.

9. 전체 설정 요약 (postgresql.conf)

16GB RAM, SSD, 200 커넥션 기준 템플릿이다.

# Memory
shared_buffers = 4GB
effective_cache_size = 12GB
work_mem = 20MB
maintenance_work_mem = 1GB

# WAL
wal_buffers = 128MB
checkpoint_timeout = 15min
max_wal_size = 4GB
min_wal_size = 1GB
checkpoint_completion_target = 0.9

# Disk I/O (SSD)
random_page_cost = 1.1
effective_io_concurrency = 200

# Connections
max_connections = 200

# Autovacuum
autovacuum_max_workers = 5
autovacuum_vacuum_scale_factor = 0.05

# Logging
log_min_duration_statement = 200ms
shared_preload_libraries = 'pg_stat_statements'

정리: 튜닝 우선순위

설정을 변경하기 전에 pg_stat_statements로 현재 병목을 먼저 파악하는 것이 가장 중요하다. 병목을 모르고 설정만 바꾸는 것은 효과가 제한적이다.