PostgreSQL 성능 문제 진단 체크리스트

개요

이 문서는 PostgreSQL 성능 문제를 체계적으로 진단하기 위한 점검 항목을 모아둔 체크리스트다. 처음 접하는 운영자도 따라할 수 있도록 시스템, 설정, 쿼리, 인덱스, VACUUM 관련 검사항목과 대표 명령을 함께 제시한다.

1. 전체 흐름과 우선순위

문제 파악의 순서

• 문제 재현 및 영향 범위 확인
• 시스템 자원(CPU, 메모리, 디스크, 네트워크) 확인
• Postgres 내부 상태 확인(pg_stat_activity, locks 등)
• 느린 쿼리와 인덱스 상태 분석
• 자동 VACUUM·통계·설정 점검

2. 시스템 자원 점검

CPU·메모리·I/O 확인

OS 레벨에서 병목이 있는지 우선 확인한다. CPU 대기 상태, 메모리 스와핑, I/O 대기 시간이 핵심 지표다.

top -b -n 1
iostat -xz 1 3
vmstat 1 5
free -m
df -h

CPU가 포화면 쿼리 병목, 스왑 발생 시 메모리 부족, 높은 await는 디스크 I/O 병목을 의심한다.

3. PostgreSQL 프로세스와 연결 상태

활성 세션과 쿼리 확인

장기 실행 쿼리나 대기 세션을 우선 찾는다.

psql -c "SELECT pid, state, query_start, wait_event_type, wait_event, query
FROM pg_stat_activity
WHERE state  'idle'
ORDER BY query_start DESC;"

잠금 대기(lock)로 인한 지연은 wait_event 열을 확인하면 원인을 좁힐 수 있다.

4. 잠금과 트랜잭션 상태

잠금 소스와 오래된 트랜잭션

오래 열린 트랜잭션은 VACUUM과 인덱스 재사용을 방해한다. 잠금 보유자와 대기자를 확인한다.

psql -c "SELECT pid, age(clock_timestamp(), xact_start) AS tx_age, query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY tx_age DESC LIMIT 10;"
psql -c "SELECT locktype, relation::regclass, mode, granted, pid FROM pg_locks JOIN pg_stat_activity USING (pid) WHERE NOT granted;"

5. 느린 쿼리 및 실행 계획

EXPLAIN ANALYZE로 비용과 행수 비교

실행 계획이 옵티마이저 예상과 다르면 통계 문제 또는 잘못된 인덱스 선택을 의심한다.

psql -c "EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) SELECT ... ;"

pg_stat_statements를 사용하면 자주 느린 쿼리 집합을 파악할 수 있다.

6. 인덱스와 통계 점검

인덱스 적합성 및 통계 최신화

인덱스가 존재해도 불완전한 통계로 인해 사용되지 않을 수 있다. 작은 테이블은 sequential scan이 더 나을 때도 있다.

psql -c "SELECT relname, n_live_tup, n_dead_tup FROM pg_stat_all_tables WHERE schemaname='public' ORDER BY n_dead_tup DESC LIMIT 20;"
psql -c "ANALYZE VERBOSE schema.table;"

7. VACUUM 및 테이블 부패(BLOAT)

BLOAT 확인과 VACUUM 정책

죽은 튜플(Dead tuples)이 많으면 테이블/인덱스가 비대해진다. autovacuum 설정과 로그를 확인한다.

psql -c "SELECT relname, pg_total_relation_size(relid) AS total_bytes, pg_relation_size(relid) AS table_bytes, (pg_total_relation_size(relid)-pg_relation_size(relid)) AS index_bytes FROM pg_catalog.pg_statio_user_tables ORDER BY total_bytes DESC LIMIT 10;"
vacuumdb --analyze --verbose dbname

8. 설정 파라미터 점검

공통 설정 항목

shared_buffers, work_mem, maintenance_work_mem, effective_cache_size, max_wal_size, checkpoint_timeout를 상황에 맞게 조정한다. 변경 전 값과 목적을 기록한다.

psql -c "SHOW shared_buffers; SHOW work_mem; SHOW maintenance_work_mem; SHOW effective_cache_size;"

9. WAL과 체크포인트

디스크 쓰기 패턴과 체크포인트 영향

잦은 체크포인트는 I/O 스파이크를 유발한다. checkpoint timeout과 wal 설정을 점검하고, checkpoint 작성 속도 확인이 필요하다.

10. 모니터링과 장기 대책

로그·메트릭 수집과 알람 설정

문제 원인을 반복적으로 추적하려면 모니터링 도구를 도입한다. pg_stat_statements, Prometheus + PG exporter, Grafana 패널을 제외하지 않는다.

마무리 점검 목록

• 시스템 자원(CPU, 메모리, I/O)
• 활성 세션과 잠금
• 느린 쿼리와 실행 계획
• 인덱스 적합성 및 통계(ANALYZE)
• VACUUM 빈도와 테이블 BLOAT
• Postgres 설정값과 체크포인트 정책
• 모니터링 체계와 로그 보관 주기

각 항목을 순차적으로 점검하면 원인 범위를 좁히기 쉽다. 필요 시 각 단계에서 수집한 출력과 메트릭을 기반으로 개선안을 적용하고 재검증한다.