PostgreSQL 쿼리 폭주? pg_stat_statements로 병목 추적

서버가 갑자기 느려지고 CPU가 치솟거나 커넥션이 쌓일 때, 많은 팀이 pg_stat_activity만 들여다보다가 “지금 뭐가 문제인지”를 놓칩니다. pg_stat_activity는 현재 실행 중인 쿼리를 보여주지만, 폭주 상황의 핵심은 보통 다음 중 하나입니다.

• 특정 SQL이 짧게 많이 호출되어 총량을 잡아먹는 경우
• 특정 SQL이 느려져서 커넥션을 오래 점유하는 경우
• 락이나 I/O 대기 때문에 쿼리들이 줄줄이 병목에 걸리는 경우

이때 pg_stat_statements는 쿼리 패턴(정규화된 SQL) 단위로 호출 횟수, 총 시간, 평균 시간, I/O 비용 등을 누적해 보여주므로 “어떤 SQL이 DB를 먹고 있는지”를 가장 빨리 좁힐 수 있습니다.

아래는 운영에서 쿼리 폭주를 추적할 때의 표준적인 흐름입니다.

pg_stat_statements가 해결해주는 것

pg_stat_statements는 실행된 SQL을 “문장 단위 통계”로 집계합니다.

• calls: 호출 횟수
• total_exec_time: 총 실행 시간(ms)
• mean_exec_time: 평균 실행 시간(ms)
• rows: 반환/처리 행 수
• shared_blks_hit/read/dirtied/written: 버퍼 히트/디스크 읽기/더티/쓰기 블록
• temp_blks_read/written: 정렬/해시 등으로 임시 파일이 발생했는지

즉, 폭주의 원인이 ‘많이 호출’인지, ‘느린 실행’인지, ‘디스크 I/O’인지를 한 번에 분류할 수 있습니다.

참고: VACUUM 지연이나 bloat가 원인이라면 쿼리가 전반적으로 느려질 수 있습니다. 이런 케이스는 별도로 점검이 필요합니다. 관련 글: PostgreSQL VACUUM 안끝남 - bloat·wraparound 해결

설치 및 활성화(운영 체크포인트)

1) extension 설치

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

2) shared_preload_libraries 설정

pg_stat_statements는 보통 shared_preload_libraries에 로드해야 합니다. postgresql.conf에 다음을 설정하고 재시작이 필요합니다.

shared_preload_libraries = 'pg_stat_statements'

이미 다른 라이브러리가 있다면 콤마로 추가합니다.

shared_preload_libraries = 'pgaudit,pg_stat_statements'

3) 주요 파라미터

pg_stat_statements.max = 10000
pg_stat_statements.track = all
pg_stat_statements.track_utility = off
pg_stat_statements.save = on

• max: 집계할 쿼리 패턴 수. 너무 작으면 중요한 쿼리가 밀려나고, 너무 크면 메모리 사용이 늘어납니다.
• track: top(기본) 또는 all. 함수 내부 쿼리까지 보고 싶다면 all이 유용하지만 오버헤드가 증가할 수 있습니다.
• save: 재시작 후에도 통계를 유지할지.

운영에서는 오버헤드가 걱정될 수 있지만, 일반적으로 pg_stat_statements는 “문제 상황을 빨리 끝내는 비용” 대비 충분히 가치가 있습니다. 다만 초고트래픽에서 track = all은 신중히 적용하세요.

폭주 상황에서 10분 안에 원인 SQL 찾기

1) 지금 가장 “총 시간”을 잡아먹는 쿼리

SELECT
  calls,
  round(total_exec_time::numeric, 2) AS total_ms,
  round(mean_exec_time::numeric, 2) AS mean_ms,
  rows,
  query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

• total_exec_time 상위는 “DB 시간을 가장 많이 소비한 쿼리”입니다.
• 폭주 때는 보통 이 리스트 상위 몇 개가 전체 부하의 대부분을 차지합니다.

2) 지금 가장 “많이 호출”되는 쿼리

짧은 쿼리가 초당 수천 번 호출되면 평균은 낮아도 DB를 가득 채웁니다.

SELECT
  calls,
  round((total_exec_time / NULLIF(calls, 0))::numeric, 3) AS avg_ms,
  round(total_exec_time::numeric, 2) AS total_ms,
  query
FROM pg_stat_statements
ORDER BY calls DESC
LIMIT 20;

여기서 calls 상위인데 avg_ms도 의미 있게 높으면 “핫 쿼리 + 느림” 조합이라 우선순위가 더 높습니다.

3) I/O 병목(버퍼 미스) 의심 쿼리

캐시 히트가 낮고 shared_blks_read가 높으면 디스크 읽기 병목일 가능성이 큽니다.

SELECT
  calls,
  shared_blks_hit,
  shared_blks_read,
  round(
    (shared_blks_hit::numeric / NULLIF(shared_blks_hit + shared_blks_read, 0)) * 100,
    2
  ) AS hit_ratio_pct,
  round(total_exec_time::numeric, 2) AS total_ms,
  query
FROM pg_stat_statements
WHERE (shared_blks_hit + shared_blks_read) > 0
ORDER BY shared_blks_read DESC
LIMIT 20;

• hit_ratio_pct가 낮고 shared_blks_read가 높다면
  • 인덱스 부재로 테이블/인덱스 스캔이 커졌거나
  • bloat로 인해 읽기량이 늘었거나
  • 워킹셋이 메모리를 초과했거나
  • 쿼리 패턴이 랜덤 I/O를 유발할 수 있습니다.

4) 임시 파일(정렬/해시) 폭발 의심 쿼리

대용량 정렬/해시 조인이 work_mem을 넘기면 temp I/O가 발생합니다.

SELECT
  calls,
  temp_blks_read,
  temp_blks_written,
  round(total_exec_time::numeric, 2) AS total_ms,
  query
FROM pg_stat_statements
ORDER BY (temp_blks_read + temp_blks_written) DESC
LIMIT 20;

이 리스트 상위 쿼리는 ORDER BY, GROUP BY, DISTINCT, 해시 조인에서 메모리 부족이 발생했을 가능성이 큽니다.

“원인 SQL을 찾은 다음” 바로 할 일

원인 후보 SQL을 찾았으면, 다음 순서로 좁히면 시행착오가 줄어듭니다.

1) 실행 계획 확인: EXPLAIN (ANALYZE, BUFFERS)

EXPLAIN (ANALYZE, BUFFERS)
SELECT ...;

여기서 보는 핵심은 다음입니다.

• Seq Scan이 큰 테이블에서 발생하는지
• 조인 순서/조인 방식이 적절한지
• BUFFERS에서 read가 과도한지
• 예상 행 수(rows)와 실제 행 수가 크게 다른지(통계 문제)

2) 인덱스 관점: “필터 + 조인 키”부터

폭주 쿼리는 대개 다음 형태입니다.

• WHERE status = 'ACTIVE' AND created_at >= ...
• WHERE user_id = ? ORDER BY created_at DESC LIMIT 20
• JOIN의 FK 컬럼 누락

예시 인덱스:

CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_orders_user_created_at
ON orders (user_id, created_at DESC);

운영에서는 CONCURRENTLY로 생성해 락 영향을 줄입니다(시간은 더 걸릴 수 있음).

3) 락/대기 병목 분리: pg_stat_activity와 함께 보기

pg_stat_statements는 “어떤 쿼리가 무거운지”를 말해주고, 락 때문에 느린지 확인하려면 pg_stat_activity가 필요합니다.

SELECT
  pid,
  wait_event_type,
  wait_event,
  state,
  now() - query_start AS running_for,
  query
FROM pg_stat_activity
WHERE state <> 'idle'
ORDER BY running_for DESC;

• wait_event_type = 'Lock'가 많으면 락 경합이 핵심일 수 있습니다.
• 이때는 장시간 트랜잭션, 대량 업데이트, DDL, 인덱스 생성 등이 원인일 수 있습니다.

4) 애플리케이션 레벨: “재시도 폭풍” 확인

DB가 느려지면 애플리케이션이 타임아웃 후 재시도하고, 재시도가 다시 DB를 더 느리게 만드는 루프가 발생합니다.

• 커넥션 풀에서 대기 증가
• 동일 요청이 중복 실행
• 타임아웃 짧음 + 즉시 재시도

이 패턴은 외부 API에서도 흔합니다. 재시도/백오프 설계 원칙은 동일하므로 함께 참고할 만합니다: OpenAI 429 RateLimitError 재시도·백오프 설계

운영에서 자주 하는 실수와 팁

통계를 안 지우고 “옛날 쿼리”에 속기

pg_stat_statements는 누적 통계라서, 특정 장애 구간만 보려면 리셋이 필요합니다.

SELECT pg_stat_statements_reset();

권장 운영 패턴:

• 장애 재현/점검 시작 직전에 리셋
• 5~15분 관찰 후 상위 쿼리 확인
• 조치 후 다시 리셋하고 개선 여부 비교

쿼리 문자열이 길어서 분석이 어려움

프레임워크가 바인딩 값을 인라인으로 넣지 않는다면(보통은 그렇습니다), pg_stat_statements는 정규화된 형태로 집계합니다. 이는 오히려 장점입니다.

다만 쿼리 텍스트가 너무 길면 일부가 잘릴 수 있습니다. 이때는 queryid를 중심으로 추적하고, 애플리케이션 로그에 queryid를 남기는 방식도 고려할 수 있습니다(버전/드라이버에 따라 지원 방식이 다름).

“평균 시간”만 보고 판단

mean_exec_time이 낮아도 calls가 압도적으로 많으면 문제가 됩니다. 반대로 calls가 적어도 total_exec_time이 높으면 배치/리포트성 쿼리가 DB 시간을 독점할 수 있습니다.

실전에서는 보통 다음 3개를 같이 봅니다.

• total_exec_time 상위
• calls 상위
• shared_blks_read 또는 temp_blks_written 상위

실전 시나리오: 폭주 원인 좁히기 예시

1. 알람: CPU 90% 지속, 응답 지연 증가
2. pg_stat_statements에서 total_exec_time 상위 확인
3. 상위 쿼리가 calls도 높고, shared_blks_read도 높음
4. EXPLAIN (ANALYZE, BUFFERS)로 확인하니 큰 테이블 Seq Scan 발생
5. WHERE user_id = ? AND created_at >= ? 패턴인데 (user_id, created_at) 인덱스 없음
6. CREATE INDEX CONCURRENTLY로 인덱스 추가
7. 통계 리셋 후 10분 관찰: shared_blks_read 감소, 평균 지연 감소

이 흐름이 가장 흔하고, 가장 빠르게 효과가 납니다.

마무리

쿼리 폭주를 “감”으로 잡으려 하면 pg_stat_activity에서 보이는 몇 개 쿼리만 보고 잘못된 결론을 내리기 쉽습니다. pg_stat_statements로 총량 기준 상위 쿼리를 먼저 특정하고, 그 다음에 실행 계획과 I/O/락/재시도 패턴을 분리하면 원인 규명이 훨씬 빨라집니다.

정리하면,

• 폭주 원인 SQL 후보는 total_exec_time, calls, shared_blks_read, temp_blks_* 상위에서 나온다
• 후보 SQL은 EXPLAIN (ANALYZE, BUFFERS)로 확정한다
• 인덱스/통계/VACUUM/락/재시도 폭풍을 분리해서 해결한다

장애 대응용으로는 pg_stat_statements만큼 “투입 대비 효과”가 큰 도구가 드뭅니다. 운영 DB에 아직 켜져 있지 않다면, 다음 장애가 오기 전에 활성화해 두는 편이 안전합니다.