13.1 튜닝 워크플로우

PostgreSQL 튜닝은 감으로 하지 않습니다. 측정 → 가설 → 검증의 사이클을 반복합니다. 운영자가 실제 사고나 정기 점검 때 따르는 워크플로우와, 순서 잘못 지키면 시간 낭비가 되는 함정을 정리합니다.

4단계 워크플로우

    flowchart LR
  M["측정<br/>(pg_stat_statements,<br/>EXPLAIN, OS metric)"]
  H["가설 수립<br/>('이 쿼리가 이 인덱스를 못 쓰고 있어서')"]
  C["변경<br/>(인덱스 추가 / 통계 갱신 /<br/> 파라미터 조정)"]
  V["검증<br/>(다시 측정해 효과 확인)"]

  M --> H --> C --> V --> M

  classDef step fill:#dbeafe,stroke:#1d4ed8,color:#1e3a8a
  class M,H,C,V step
  

각 단계의 도구:

우선순위 ladder

운영자가 사고를 마주쳤을 때 싼 도구부터:

대부분 사고는 14에서 해결합니다. 68을 먼저 만지면 원인 파악 못 한 채 부수 효과만 봅니다.

측정 — “느린 쿼리가 무엇인가”

pg_stat_statements TOP

SELECT round(total_exec_time::numeric / 1000, 1) AS total_sec,
       calls,
       round(mean_exec_time::numeric, 2) AS mean_ms,
       LEFT(query, 80) AS query
  FROM pg_stat_statements
 ORDER BY total_exec_time DESC
 LIMIT 20;

total_sec이 전체 운영 시간 대비 비율로. 1시간 운영 중 한 쿼리가 600초 차지 = 전체 1 cpu의 16%.

percentile 보기

-- p95, p99 슬로우
SELECT round(percentile_cont(0.95) WITHIN GROUP (ORDER BY mean_exec_time)::numeric, 1) AS p95_ms,
       round(percentile_cont(0.99) WITHIN GROUP (ORDER BY mean_exec_time)::numeric, 1) AS p99_ms
  FROM pg_stat_statements
 WHERE calls > 100;

평균보다 percentile이 사용자 체감과 가깝습니다.

auto_explain plan

특정 쿼리의 실제 plan을 잡으려면 auto_explain(10.6) 활성 + 로그 검색.

가설 — “왜 느린가”

수집한 plan을 보고 하나의 원인을 가설.

변경 — 한 번에 하나

같이 여러 변경을 적용하면 무엇이 효과 냈는지 알 수 없습니다. 변경의 황금률:

1. 변경 사유 문서화
2. 변경 전 측정값 기록
3. 한 가지만 변경
4. 변경 후 같은 측정
5. 효과 없거나 부정 → 원복

운영 환경에서는 staging에서 먼저 검증 + 운영 적용합니다.

검증 — 효과 측정

같은 쿼리를 같은 데이터로:

EXPLAIN (ANALYZE, BUFFERS) <쿼리>;

PG 13+ pg_stat_statements_reset()으로 변경 전후 별 카운터:

-- 변경 전
SELECT pg_stat_statements_reset();
-- (운영 1시간)
-- snapshot 저장

-- 변경 적용

SELECT pg_stat_statements_reset();
-- (운영 1시간)
-- snapshot 비교

A/B로 같은 워크로드로 비교가 가장 정확합니다.

흔한 함정

1. 통계 안 갱신 후 인덱스 추가

"인덱스 만들었는데 옵티마이저가 안 쓰네요"

ANALYZE 안 한 인덱스는 통계가 없어 옵티마이저가 비용 추정 부정확. 인덱스 만들고 꼭 ANALYZE.

2. work_mem을 무리하게 올림

"work_mem을 1GB로 올렸더니 OOM Killer가..."

work_mem × 노드 × connections × parallel = 최악 메모리. 1.4 참고합니다.

3. 인덱스 폭증

"느린 쿼리마다 인덱스 추가했더니 INSERT가 느려졌어요"

인덱스 N개 = INSERT/UPDATE 비용 N배. 미사용 인덱스 정기 정리(6.7).

4. shared_buffers를 50%로

"shared_buffers를 호스트 RAM 50%로 했는데 더 느려졌어요"

OS page cache와 이중 캐싱 손해(1.4). 25% 출발 권장합니다.

5. EXPLAIN 한 번 본 뒤 결론

"이 쿼리가 안 느린데요?"

운영 시간대마다 plan이 다를 수 있습니다. cache warmth·동시 부하·통계 신선도가 영향. 시간대별 측정합니다.

운영 워크로드 vs 분석 워크로드

같은 클러스터에 두 워크로드가 섞이면 튜닝이 정반대.

권장: read standby에 분석 분리 + 워크로드별 튜닝.

시계열 — 추세를 본다

운영 사고는 급격한 변화에서 자주 발생합니다. 시계열 그래프(Grafana 등)에서:

이 추세를 월간 회고에서 정기 점검 → 튜닝 우선순위 결정합니다.

정리

• 튜닝 = 측정 → 가설 → 변경 → 검증의 사이클
• 우선순위: 통계 → EXPLAIN → 인덱스 → 쿼리 → 파티션 → 파라미터 → OS·HW
• 한 번에 하나 변경, 변경 전후 측정
• OLTP와 분석은 다른 튜닝
• 추세 시계열로 사고 예방

다음 절(13.2)에서는 가장 효과 큰 영역 — 인덱스 전략을 봅니다.