PostgreSQL: DBMS 성능 튜닝

※ PostgreSQL: DBMS Performance Tuning.

 

안녕하세요. 듀스트림입니다.

 

오늘 포스팅은 DBMS 성능 튜닝에 대해 이야기해보려고 합니다.

 

성능 튜닝은 시스템 레벨, 스키마 레벨, 쿼리 레벨이 있으며 쿼리 레벨부터 하나씩 정리해보겠습니다.

---

1. 성능 튜닝의 목표

성능 튜닝의 핵심 목표는 응답 속도 단축, 리소스 효율화, 데이터 처리량 증대입니다.

성능 튜닝은 진단 → 분석 → 최적화 → 검증 순서로 진행하며, 아래 세 가지 측면에서 접근합니다.

1. System-level (DB 서버/환경 튜닝)
   • 메모리(work_mem, shared_buffers)
   • I/O, CPU, 병렬 처리
   • VACUUM, ANALYZE, 통계 유지
2. Schema-level (데이터 구조 최적화)
   
   • 테이블 구조, 컬럼 타입 최적화
   • 파티셔닝, 정규화/비정규화 전략
3. Query-level (쿼리 최적화)
   
   • 불필요한 계산/조인 제거
   • 인덱스 활용 최적화
   • 실행 계획(plan) 개선

성능 튜닝은 단순히 쿼리만 바꾸는 것이 아니라, 데이터 구조와 환경까지 통합적으로 진단하고 개선하는 과정입니다.

---

2. SQL 튜닝 접근 방법

	단계	목적	방법
1	진단 (Identify)	성능 문제 확인	slow query 로그, pg_stat_statements, EXPLAIN 분석
2	분석 (Analyze)	문제 원인 파악	실행 계획(EXPLAIN, EXPLAIN ANALYZE), 인덱스 확인, 통계 확인
3	최적화 (Optimize)	개선 적용	인덱스 추가/변경, 쿼리 구조 변경, 파라미터 튜닝
4	검증 (Verify)	성능 비교	실행 시간, I/O, CPU, row scan 비교
5	모니터링 (Monitor)	지속적 관리	pg_stat_activity, pg_stat_user_tables, 모니터링 툴 활용

---

3. PostgreSQL에서 SQL 튜닝을 위한 도구와 방법

3.1 EXPLAIN & EXPLAIN ANALYZE

• EXPLAIN: 쿼리 실행 계획 확인
• EXPLAIN ANALYZE: 실제 실행 시간을 포함한 계획 확인
• 주요 확인 포인트:
  
  • Seq Scan vs Index Scan
  • Nested Loop vs Hash Join vs Merge Join
  • 예상 row 수(rows)와 실제 row 수(actual rows) 차이 (예상 row 수와 실제 row 수가 크게 차이 나면 통계를 의심)
  • 비용(cost) 확인

PostgreSQL: EXPLAIN

---

3.2 통계와 ANALYZE

PostgreSQL은 통계 기반 옵티마이저(CBO)를 사용합니다.

• 테이블과 컬럼의 통계가 부정확하면 잘못된 실행 계획이 생성됩니다.

-- 테이블 통계 업데이트
ANALYZE TABLE_NAME;

 

• pg_stats 조회

SELECT attname, n_distinct, null_frac, most_common_vals
FROM pg_stats
WHERE tablename = 'TABLE_NAME';

---

3.3 인덱스 전략

적절한 인덱스 하나로 쿼리 성능이 놀랍게 개선되는 케이스가 많은 만큼 인덱스 전략은 매우 중요합니다.

1. B-tree: =, <, >, <=, >=, BETWEEN, ORDER BY
2. GIN: JSONB, Full Text Search
3. BRIN: 매우 큰 범위 데이터
4. Hash: 동등 비교(=) 전용, 특별한 경우

※ 인덱스 생성 시 고려: WHERE 조건 컬럼, JOIN 키 컬럼, ORDER BY / GROUP BY, 자주 사용하는 집합 함수(aggregation) 컬럼

---

3.4 조인 최적화

조인은 비용이 큰 연산이므로 구조와 순서가 중요합니다.

• Nested Loop Join: 작은 테이블 → 큰 테이블
• Hash Join: 중간 규모 테이블 동시 처리
• Merge Join: 정렬된 데이터 조인

-- 잘못된 조인
SELECT *
FROM a
JOIN b ON a.id = b.id
WHERE b.status = 'ACTIVE';

-- 튜닝 예시: b에서 미리 필터링 후 조인
WITH b_filtered AS (
  SELECT * FROM b WHERE status='ACTIVE'
)
SELECT *
FROM a
JOIN b_filtered bf ON a.id = bf.id;

PostgreSQL: 조인 알고리즘과 유형

---

3.5 서브쿼리 vs CTE vs LATERAL

• 서브쿼리: 옵티마이저가 inline 가능 → 성능 좋음
• CTE: PostgreSQL 12 이전은 optimization fence 주의
• LATERAL: 각 row 기준 계산 필요 시 유용

PostgreSQL(ANSI SQL): CTE와 서브쿼리 비교

ANSI SQL: LATERAL

---

3.6 파티셔닝 & 테이블 설계

매우 큰 테이블은 파티셔닝으로 성능 개선 가능합니다.

• PostgreSQL 10+: declarative partitioning 지원
• 전략:
  
  • 범위(RANGE): 날짜, ID 범위
  • 목록(LIST): 특정 값 목록

PostgreSQL: Partition

---

4. 튜닝 절차

1. 문제 발견
   • 느린 쿼리 추적
2. 실행계획 분석
   • EXPLAIN ANALYZE 실행
   • Seq Scan, Nested Loop, Hash Join 여부 확인
   • 비용(cost)과 실제 row 차이 확인
3. 튜닝 및 적용
   • 인덱스 추가
   • 통계 업데이트
   • 쿼리 구조 변경
   • 불필요한 컬럼 제거
   • JOIN 순서 조정
   • LIMIT 사용
   • 필요 시 파티셔닝
4. 성능 검증
   • 이전 실행 시간과 비교
   • I/O, CPU, Memory 확인
5. 모니터링
   • pg_stat_activity, pg_stat_user_tables, pg_locks 등
   • 지속적 쿼리 모니터링 → 통계 기반 계획 재조정

---

5. 주요 포인트

아래 리스트를 고려하여 튜닝하시면 됩니다.

• DISTINCT vs GROUP BY vs ROW_NUMBER()
• 중복 제거 시, row 수에 따라 성능 달라짐
• 인덱스와 조합해서 성능 최적화
• LIMIT + ORDER BY 최적화
• 인덱스 순서와 맞추면 파일스캔 최소화
• JOIN LATERAL + Index 활용
• 최근 데이터 조회 시 매우 유용
• VACUUM & Autovacuum 관리
• 테이블 bloat 방지 → I/O 최적화
• Memory 파라미터
• work_mem : 정렬/해시 연산 메모리
• shared_buffers : 페이지 캐시 메모리
• effective_cache_size : OS cache 고려
• 병렬 처리
• PostgreSQL 9.6+ : parallel query 지원
• 큰 테이블의 aggregation/scan 최적화 가능

---

언젠가 MySQL 관점에서도 작성해보겠습니다.

 

오늘은 여기까지~