PostgreSQL 급증한 슬로우쿼리와 인덱스 잠금 해결법: 진단·응급조치·근본대책
문제 정의 — 슬로우쿼리와 인덱스 잠금이 서비스에 미치는 영향
증상: 특정 쿼리의 응답 시간이 갑자기 길어지고 psql의 pg_stat_activity에서 'waiting' 상태가 증가합니다. 인덱스 관련 뮤텍스/lock과 VACUUM 대기가 함께 나타나는 경우가 많습니다. 이로 인해 커넥션 타임아웃, 장기 트랜잭션 유지로 인한 WAL 증가 및 디스크 IO 급증이 동반될 수 있습니다. 진단과 대응 절차는 PostgreSQL 급증한 슬로우쿼리와 인덱스 잠금 해결법을 참고하면 도움이 됩니다.
- 비즈니스 영향: API 응답 지연·타임아웃으로 사용자 경험이 악화되고 배치·보고 파이프라인이 지연됩니다. SLA/SLO 위반 위험이 커지며, 결과적으로 매출 손실과 운영 비용 증가로 이어질 수 있습니다.
- 재현 빈도 및 피크 패턴: 업무 시간(특히 트래픽 피크), 야간 리포트나 배치 실행, 정기 백업·마이그레이션 직후에 재현이 잦습니다. 또한 특정 쿼리나 스키마 변경 이후에 계속 발생하는 경우가 많습니다. 실무 체크리스트 예시 — 문제 발생 시: 1) 문제 쿼리 식별, 2) 인덱스와 통계 확인, 3) VACUUM/ANALYZE 상태 점검, 4) 잠금 원인 확인 및 쿼리 튜닝 순으로 진행하세요.
초기 진단 — 어떤 지표와 로그를 우선 확인할까
초기 대응은 무엇이 대기 중인지, 무엇이 가장 늦게 끝나는지 파악하는 것에서 출발한다. 우선 아래 항목들을 확인하라.- pg_stat_activity — state, wait_event, query_start와 query 텍스트로 오래된 트랜잭션과 대기 중인 백엔드를 찾는다
- pg_locks — lockmode와 granted 여부, relation/transaction ID를 확인해 인덱스나 테이블 잠금 원인을 추적한다
- pg_stat_statements — total_time, mean_time, calls를 분석해 비용이 큰 쿼리와 호출 패턴을 파악한다
- 슬로우 쿼리 로그 — log_min_duration_statement 설정, 타임스탬프, 쿼리 텍스트와 실행 계획(plan)을 확보해 원인을 추적한다
- 시스템 리소스 — CPU 사용률, iowait, 디스크 지연(latency), 메모리와 스와핑, 파일시스템 여유 공간 및 OS 로그를 점검한다
원인별 분석 체크리스트 — 쿼리·인덱스·통계·동시성 문제를 빠르게 구분하기
- 비효율 쿼리
- 슬로우쿼리 수집:
pg_stat_statements에서 지연 시간이 큰 상위 쿼리 식별 - 실행계획 확인:
EXPLAIN (ANALYZE, BUFFERS)로 seq scan, 임시 파일 생성 및 정렬 비용 점검 - 파라미터 바인딩 문제와 불필요한 쿼리 반복 실행 여부 점검
- 슬로우쿼리 수집:
- 잘못된/누락된 인덱스
- 인덱스 사용률:
pg_stat_user_indexes의idx_scan값을 확인(0이면 사용되지 않음) - 복합·부분 인덱스 검토, 중복 인덱스 및 bloat 여부 확인
- 인덱스 사용률:
- 오래된 통계
- 테이블 통계:
pg_stat_all_tables의last_analyze와 행 수 확인 - ANALYZE 필요성 판단 — 카디널리티가 틀어지면 잘못된 실행 계획이 선택될 수 있음
- 테이블 통계:
- 트랜잭션 롱런·동시성
- 현재 활동:
pg_stat_activity에서 오래 실행 중인 트랜잭션과wait_event확인 - 락 확인:
pg_locks로 인덱스 및 행 레벨의 락 대기 상황 점검 - HOT update 빈도나 인덱스 컨텐션 발생 여부 확인
- 실무 체크리스트 예: 우선 지연 상위 3개 쿼리를 찾고, 해당 테이블의
last_analyze·인덱스 사용률·pg_locks상태를 순서대로 점검해 원인을 좁혀라.
- 현재 활동:
긴급 대응 전략 — 서비스 가용성 확보를 위한 단기 조치
가용성을 최우선으로 두고 즉시 적용할 수 있는 조치만 모았습니다. 이 가이드는 PostgreSQL 급증한 슬로우쿼리와 인덱스 잠금 해결법 관점에서, 장애 확산을 억제하고 서비스 중단을 최소화하는 단기 대책을 제시합니다. 원격에서 빠르게 적용 가능한 세션 제어, 타임아웃 설정, 비차단 인덱스 처리와 리소스 제한을 중심으로 설명합니다.
탐지 즉시 문제 세션을 중단·격리하고, 인덱스 재구성은 비차단 모드로 진행하세요. 애플리케이션 쪽에서 쓰기 일시중단이나 워커 스로틀링을 병행하면 복구 속도가 빨라집니다. 커넥션 풀 제한과 동시 작업 수 조정으로 추가 악화를 방지하세요. 실무 체크리스트 예: 탐지 → 세션 중단 → 비차단 인덱스 재구성 → 리소스 제한 → 애플리케이션 쓰기 조절.
우선 적용 순서(권장)
- 문제 세션 탐지 및 즉시 중단
SELECT pid, usename, query, state FROM pg_stat_activity WHERE state <> 'idle'; -- pg_cancel_backend(pid) / pg_terminate_backend(pid) - 세션/쿼리 타임아웃 적용: lock_timeout·statement_timeout을 세션 또는 롤 레벨에서 즉시 설정
- 인덱스 작업은 가능하면 CREATE INDEX CONCURRENTLY나 pg_repack, REINDEX CONCURRENTLY로 비차단 수행
- 리소스 제한: max_parallel_workers_per_gather와 작업자 수, 커넥션 수를 줄여 부하를 완화
- 애플리케이션 차원에서 쓰기 억제 또는 읽기 전용 모드로 전환하여 추가 잠금을 방지
근본적 해결책 — 쿼리 리팩토링, 인덱스 재설계 및 옵티마이저·파라미터 튜닝
실행계획(EXPLAIN ANALYZE, BUFFERS)으로 병목 지점(순차 스캔·중첩 루프·디스크 I/O)을 찾아낸 뒤 리팩토링합니다. 조인 순서와 필터 위치를 조정해 Predicate Pushdown을 유도하고, 불필요한 재계산을 줄이기 위해 CTE를 인라인하거나 재작성하세요.
- 복합 인덱스: 자주 사용하는 WHERE·JOIN 순서대로 컬럼을 배치하고, leading equality 조건을 우선시합니다.
- 부분 인덱스: 고선택성 조건(예: status = 'active')에 WHERE 절을 추가해 인덱스 크기와 유지 비용을 줄입니다.
- INCLUDE 인덱스: 필요한 컬럼을 포함해 커버링 쿼리를 만들면 heap 접근을 최소화할 수 있습니다.
ANALYZE·VACUUM 전략은 테이블 특성에 맞춰 autovacuum 임계값과 scale_factor를 조정하세요. 업데이트가 잦은 테이블은 더 공격적인 vacuum 설정이나 pinned tuple 관리가 필요할 수 있습니다. 심각한 bloat는 REINDEX나 pg_repack으로 해결하는 것이 안전합니다. 체크리스트: autovacuum 로그 확인 → bloat 비율 점검 → 최근 VACUUM/ANALYZE 시점 검토.
파라미터는 균형이 중요합니다. 정렬·해시 조인을 위해 충분한 work_mem을 할당하되, 동시 세션 수를 고려해 세션당 과도한 할당은 피하세요. 병렬 실행은 max_parallel_workers_per_gather로 제어하고, 인덱스 빌드·정비는 maintenance_work_mem으로 가속화합니다. 모든 변경 전후에는 대표 워크로드로 성능을 검증해야 합니다. 이 접근법은 PostgreSQL 급증한 슬로우쿼리와 인덱스 잠금 해결법의 핵심입니다.
예방과 운영화 — 모니터링·알림·자동화로 재발 방지하기
지속적인 모니터링은 문제 재발을 막는 핵심 수단이다. PostgreSQL 급증한 슬로우쿼리와 인덱스 잠금 해결법 사례에서도, pg_stat_activity, pg_locks, pg_stat_user_indexes(행·블로트), slow query 로그, 쿼리 플랜 변화까지 수집해 시계열 DB에 보관한다.
- SLO 기반 알림: 운영 현실에 맞는 임계값을 정의한다. 예를 들어 p95·p99 지연, 락 대기시간, 재시도 비율, 인덱스 블로트 비율 등을 모니터링하고, 적절한 라우팅과 에스컬레이션을 설정한다.
- CI 관제: PR 파이프라인에서 EXPLAIN/EXPLAIN ANALYZE로 성능을 검증하고 인덱스 존재와 사용성을 점검한다. 대용량 DDL(데이터 변환)은 배포 금지 규칙을 적용하라.
- 자동화·응급대응: 과도한 연결은 커넥션 풀러로 제어하고, 장기 락 감지 시 알림을 보내 정책에 따라 자동으로 취소하거나 오프라인 리인덱스를 예약 실행한다.
- 용량·스케일링 정책: 읽기 복제, 파티셔닝, 스토리지 자동 확장 등으로 스케일 전략을 수립하고, 리소스 쿼터와 비용 임계값을 문서화한다.
운영플레이북과 정기 복기(RCA)를 통해 모니터링·CI·스케일 정책을 지속 개선한다. 실무 체크리스트 예: 경보 정책 검토 → EXPLAIN 검증 → 인덱스 블로트 점검 → 재해복구 시나리오 테스트.
경험에서 배운 점
급증한 슬로우쿼리와 인덱스 잠금은 대개 진단이 늦어지거나 잘못된 긴급 대처로 상황이 악화됩니다. 응급 상황에서는 먼저 실행 중인 세션과 락 상태를 신속히 확인하세요(예: pg_stat_activity, pg_locks 조회). 이후 비차단 우선으로 아래 체크리스트를 단계별로 따라 실행하면 빠르게 위험을 줄일 수 있습니다. 이 접근법은 PostgreSQL 급증한 슬로우쿼리와 인덱스 잠금 해결법에 바로 적용됩니다. 권장 응급 체크리스트:
- 실행 중 쿼리 확인: SELECT pid, state, query, query_start, wait_event_type, wait_event FROM pg_stat_activity WHERE state <> 'idle' ORDER BY query_start;
- 락 확인: SELECT pid, mode, relation::regclass, granted FROM pg_locks JOIN pg_class ON pg_locks.relation = pg_class.oid WHERE NOT granted;
- 비교적 안전한 제한 조치: 세션 단위 statement_timeout 설정 또는 lock_timeout을 단기간 적용(신중히). 장시간 실행 세션은 우선 pg_cancel_backend(pid)로 시도하고, 취소가 되지 않으면 비즈니스 영향도를 고려해 pg_terminate_backend(pid)로 종료합니다. (사례: 대량 업데이트로 인덱스 잠금이 발생했을 때 일부 세션을 취소해 응답성을 회복한 적이 있습니다.)
- 인덱스 작업 시 즉시 전체 테이블 락을 유발하는 명령(CREATE INDEX, REINDEX 등)은 피하고, 가능하면 CONCURRENTLY 옵션을 사용하세요(버전별 지원 여부 확인). 긴급 재구성이 필요하면 pg_repack 같은 도구를 고려합니다.
근본 대책은 모니터링·예방·운영 절차의 정착으로 귀결됩니다. 현장에서 자주 반복되는 실수는 ANALYZE·autovacuum 튜닝을 미루거나, 마이그레이션 중 비동시 인덱스 작업을 검증 없이 실행하는 것입니다. 재발 방지 체크리스트:
- 지표 수집: log_min_duration_statement, log_lock_waits, pg_stat_statements, 인덱스 사용률과 블로트 모니터링(예: pg_stat_user_indexes, pg_relation_size 관련 지표)을 기본으로 수집하고 경보 기준을 설정하세요.
- 운영 규칙: 프로덕션에서 인덱스 생성/재구성은 가능하면 CONCURRENTLY로 수행하고, 마이그레이션 스크립트에 타임아웃·lock_timeout을 포함합니다. 또한 메이저 버전별로 REINDEX/REINDEX CONCURRENTLY 지원 여부를 사전에 확인하세요.
- 정기 유지보수: autovacuum 파라미터와 maintenance_work_mem을 주기적으로 검토하고, 정기 ANALYZE로 통계를 최신 상태로 유지합니다. 스테이징 환경에서 쿼리 플랜과 인덱스 변경을 검증하고, 긴 트랜잭션을 모니터링해 준비 트랜잭션(long-prepared) 생성을 막는 정책을 수립하세요. (실무 팁: 주간 점검 항목에 오래된 트랜잭션과 autovacuum 로그 확인을 포함하면 문제를 조기에 발견하기 쉽습니다.)
댓글
댓글 쓰기