백엔드 데이터 인프라 41편 — 성능 팁 종합

이 글은 백엔드 데이터 인프라 시리즈 70편 중 41편이에요. Part 2 SQL Language 깊이의 마지막. 운영 성능을 결정하는 5대 영역 종합.

성능 5대 영역

순서대로 풀어볼게요.

1. 메모리 — postgresql.conf

# 호스트 8GB 가정
shared_buffers = 2GB                  # PG 캐시 (25%)
effective_cache_size = 6GB             # OS 캐시 추정치
work_mem = 16MB                        # 정렬·해시당
maintenance_work_mem = 512MB           # VACUUM·CREATE INDEX

shared_buffers

PG 자체 메모리 캐시로, 자주 읽는 페이지를 보관해요. - 권장 = 호스트 메모리 25% - 너무 크면 = OS 캐시 줄어 효율 X - 너무 작으면 = 디스크 I/O 잦음

effective_cache_size

"OS 캐시 포함 가용 메모리 추정". 실제 메모리 할당 X — 계획자에 "이 정도 캐시 사용 가능" 힌트. - 권장 = 호스트 메모리 75%

work_mem

한 정렬·해시 작업당 메모리로, ORDER BY·GROUP BY·Hash Join 에 영향을 줘요. - 권장 = 16~64MB - 너무 작으면 = Sort Method: external merge (디스크 정렬 — 느림) - 너무 크면 = N 개 동시 쿼리 × work_mem 메모리 폭발

세션별로 동적:

SET work_mem = '256MB';   -- 큰 정렬용

maintenance_work_mem

VACUUM·CREATE INDEX·ANALYZE 메모리. 운영은 큰 값으로 잡아요. - 권장 = 512MB ~ 2GB

2. 디스크 — WAL·체크포인트

wal_buffers = 16MB
checkpoint_timeout = 15min
max_wal_size = 4GB
checkpoint_completion_target = 0.9

체크포인트

WAL(Write-Ahead Log, 변경 선기록 로그)을 데이터 파일에 "동기화하는 시점". 너무 잦으면 디스크 폭주, 너무 드물면 복구가 느려져요.

• checkpoint_timeout = 15min = 15분마다
• max_wal_size = 4GB = 또는 WAL 4GB 차면
• checkpoint_completion_target = 0.9 = 90% 시간 동안 천천히 (스파이크 방지)

WAL 압축

wal_compression = on

PG 14+ — WAL 압축으로 디스크·복제 대역폭을 절약해요.

3. autovacuum 튜닝

대용량 = autovacuum 기본값이 부족해요.

autovacuum = on
autovacuum_max_workers = 4
autovacuum_naptime = 30s

테이블별:

ALTER TABLE big_table SET (
    autovacuum_vacuum_scale_factor = 0.1,
    autovacuum_vacuum_threshold = 50000,
    autovacuum_analyze_scale_factor = 0.05
);

기본은 "20% 변경 시 vacuum" 이라 큰 테이블엔 늦어요. scale_factor 0.05~0.1 로 줄여줘요.

27편 DELETE 깊이 의 bloat·VACUUM 깊이.

4. 연결 풀 — 운영 필수

3편 아키텍처 의 프로세스 모델.

HikariCP — 앱 단 풀

spring:
  datasource:
    hikari:
      maximum-pool-size: 20
      minimum-idle: 5
      connection-timeout: 30000
      idle-timeout: 600000
      max-lifetime: 1800000

권장 풀 사이즈 = (코어 수 × 2) + 디스크 수 정도.

PgBouncer — 서버 단 풀

[databases]
mydb = host=localhost dbname=mydb

[pgbouncer]
pool_mode = transaction
default_pool_size = 25
max_client_conn = 1000

• pool_mode = transaction = 트랜잭션 단위 풀 (가장 흔함)
• default_pool_size = 25 = PG 연결 수
• max_client_conn = 1000 = 앱이 1000개 연결까지 수용

앱 1000 연결 ↔ PgBouncer 25 PG 연결로 다중화, 시스템 부하가 1/40로 떨어져요.

한국 표준 — HikariCP + PgBouncer

[Spring App × N] → [HikariCP 풀] → [PgBouncer] → [PG 25 연결]

이 조합이 한국 회사 백엔드 표준이에요.

5. 쿼리 튜닝

40편 EXPLAIN 의 진단 흐름.

운영 표준 흐름

1. pg_stat_statements 느린 쿼리 TOP
2. EXPLAIN (ANALYZE, BUFFERS)
3. 진단 (Seq Scan·Sort·loops 등)
4. 인덱스·쿼리·통계 조정
5. 재검증

자주 만나는 함정

• Seq Scan + 큰 결과 = 인덱스 박기
• external merge sort = work_mem 늘리기
• Nested Loop + 큰 loops = JOIN 인덱스
• 추정 vs 실측 크게 다름 = ANALYZE
• read 대비 hit 낮음 = shared_buffers 늘리기 또는 자주 쓰는 데이터 분리

모니터링 — pg_stat_*

-- 느린 쿼리
SELECT query, calls, total_exec_time / calls AS avg_ms, rows / calls AS avg_rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC LIMIT 20;

-- 큰 테이블
SELECT schemaname, relname,
    pg_size_pretty(pg_total_relation_size(relid)) AS size,
    n_live_tup, n_dead_tup
FROM pg_stat_user_tables
ORDER BY pg_total_relation_size(relid) DESC LIMIT 10;

-- 인덱스 사용
SELECT schemaname, indexrelname, idx_scan,
    pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE schemaname = 'public'
ORDER BY idx_scan ASC LIMIT 10;

-- 캐시 hit
SELECT
    sum(blks_hit) AS hit,
    sum(blks_read) AS read,
    sum(blks_hit) * 100.0 / NULLIF(sum(blks_hit) + sum(blks_read), 0) AS hit_pct
FROM pg_stat_database;

hit_pct 가 95% 아래로 떨어지면 메모리 부족 신호예요.

Slow Query Log

log_min_duration_statement = 1000   # 1초 넘는 쿼리 로깅
log_lock_waits = on                  # 락 대기 로깅
log_temp_files = 0                   # 임시 파일 로깅 (work_mem 부족 추적)

운영 환경 표준. 로그 분석 도구인 pgBadger(PG 로그 리포트 도구)·PoWA(쿼리 성능 분석 대시보드) 와 함께 써요.

파티셔닝 — 대용량

22편 파티션 의 RANGE 파티션 = 대용량 운영 핵심.

-- 월별 파티션
CREATE TABLE orders (
    id BIGSERIAL,
    created_at TIMESTAMPTZ,
    ...
) PARTITION BY RANGE (created_at);

장점: - 쿼리 필터 = 적절한 파티션만 - 옛 파티션 = DROP 한 줄 - 인덱스·VACUUM 파티션별

수억 행+ 테이블은 파티션 무조건이에요.

일반 운영 룰 10

1. shared_buffers = 메모리 25%
2. effective_cache_size = 메모리 75%
3. work_mem = 16~64MB (큰 정렬은 SET 동적)
4. autovacuum scale_factor = 0.05~0.1 (큰 테이블)
5. HikariCP + PgBouncer 조합
6. 외래 키 = 인덱스 무조건
7. 자주 SELECT·ORDER BY·JOIN 컬럼 = 인덱스
8. CONCURRENTLY 무조건 (인덱스 생성·삭제)
9. pg_stat_statements + slow query log 운영 모니터
10. 수억 행+ = 파티션

함정 5가지

(1) 기본값 그대로

PG 기본 = 1GB 호스트 가정. 8GB 이상 호스트 = 반드시 튜닝.

(2) 연결 풀 없음

스레드마다 새 연결 = 메모리·CPU 폭주. 풀 무조건.

(3) work_mem 너무 크게

100MB × 100 동시 쿼리 = 10GB 메모리. OOM 위험. 균형.

(4) 모니터링 X

운영 환경에 pg_stat_statements 없음 = 느린 쿼리 발견 못 함. 표준 설치.

(5) 파티션 안 만들고 수억 행

쿼리 시간·VACUUM 시간 폭주. 시작 시점부터 파티션.

한 줄 정리 — 성능 5대 영역 = 메모리·디스크·연결·쿼리·모니터링. shared_buffers 25%·HikariCP+PgBouncer·autovacuum 튜닝·EXPLAIN+pg_stat_statements·파티셔닝 5가지가 운영 표준. 기본값 그대로 X — 무조건 튜닝.

시험 직전 한 번 더 — 성능 종합 입문자가 매번 헷갈리는 것

• shared_buffers = PG 캐시 (호스트 메모리 25%)
• effective_cache_size = OS 캐시 추정 (75%)
• work_mem = 정렬·해시당 (16~64MB)
• 큰 정렬 = SET work_mem 동적
• maintenance_work_mem = VACUUM·CREATE INDEX (512MB~)
• WAL = 영속성 보장 (체크포인트)
• checkpoint_timeout 15min
• max_wal_size 4GB
• wal_compression on (PG 14+)
• autovacuum = 큰 테이블 scale_factor 0.05~0.1
• HikariCP = 앱 단 풀
• PgBouncer = 서버 단 풀 (transaction mode)
• 풀 사이즈 = (코어 × 2) + 디스크
• 한국 표준 = HikariCP + PgBouncer
• pg_stat_statements = 느린 쿼리 통계
• log_min_duration_statement = slow query log
• pgBadger·PoWA = 로그 분석
• 캐시 hit_pct < 95% = 메모리 부족
• 외래 키 = 인덱스 무조건
• CONCURRENTLY 무조건
• 수억 행+ = 파티션
• pg_stat_user_tables·pg_stat_user_indexes = 모니터링
• Slow Query 진단 = EXPLAIN BUFFERS
• 운영 첫 셋업 = 5가지 체크리스트
• 기본값 그대로 = 운영 위험

시리즈 다른 편 (앞뒤 글 모음)

이전 글:

• 36편 — 인덱스 종합 운영 전략과 튜닝
• 37편 — MVCC 소개
• 38편 — MVCC 격리 수준과 동시성 함정
• 39편 — 전문 검색 to_tsvector to_tsquery
• 40편 — EXPLAIN 쿼리 계획 읽기

다음 글:

• 42편 — 운영 설치 바이너리·Docker·관리 서비스
• 43편 — postgresql.conf 핵심 설정
• 44편 — 사용자·역할 관리 CREATE ROLE GRANT
• 45편 — 데이터베이스 관리 생성·복제·드롭
• 46편 — 백업과 복구 pg_dump·PITR