pg_stat_statements로 PostgreSQL 느린 쿼리 찾기

개요

PostgreSQL에서 성능 문제를 해결할 때 가장 먼저 살펴볼 항목은 느린 쿼리다. pg_stat_statements는 쿼리 실행 통계를 축적해 어떤 SQL이 자원을 많이 소비하는지 파악하는 핵심 확장이다. 초보자도 이해하기 쉽게 설정 절차와 실무에서 유용한 조회 방법을 정리한다.

pg_stat_statements란 무엇인가

pg_stat_statements는 데이터베이스 서버 차원에서 쿼리별 통계를 수집하는 확장이다. 총 실행 시간, 평균 시간, 호출 횟수, 반환된 행 수 등을 집계해 느린 쿼리 찾기에 유용한 지표를 제공한다. SQL 텍스트는 정규화되어 유사한 쿼리를 하나로 묶어 분석이 용이하다.

설치와 주요 설정

pg_stat_statements는 서버 시작 시 로드되어야 하므로 shared_preload_libraries에 추가가 필요하다. 다음 항목들을 확인하면 기본적인 준비가 완료된다.

postgresql.conf 설정

• shared_preload_libraries에 pg_stat_statements 추가
• track_activity_query_size는 쿼리 텍스트를 저장할 크기 지정(기본값 확인 권장)

shared_preload_libraries = 'pg_stat_statements'
# 필요시 쿼리 길이 증가
track_activity_query_size = 2048

위 설정 변경 후에는 PostgreSQL 서버 재시작이 필요하다. 재시작 없이 확장을 활성화하면 통계가 수집되지 않는다.

데이터베이스에서 확장 생성

서버 재시작 후에는 각 데이터베이스에서 확장을 생성한다. 일반적으로 다음 SQL을 실행하면 된다.

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

기본 사용법

설치와 활성화가 완료되면 pg_stat_statements 뷰에서 쿼리 통계를 조회할 수 있다. 몇 가지 핵심 열은 다음과 같다.

• query: 정규화된 쿼리 텍스트
• calls: 호출 횟수
• total_time: 총 실행 시간(ms)
• mean_time: 평균 실행 시간(ms)
• rows: 총 반환 행 수

느린 쿼리 우선순위 조회 예시

전체적인 비용 관점에서 우선 추적해야 할 쿼리를 찾는 간단한 쿼리다. total_time 기준 상위 결과를 확인한다.

SELECT
  query,
  calls,
  total_time,
  mean_time,
  rows
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 20;

평균 실행 시간이 높은 쿼리를 찾을 때는 mean_time으로 정렬한다. 호출 수가 많으면서 평균 시간이 조금만 길어도 전체 비용이 커질 수 있으니 calls와 mean_time을 함께 확인하는 것이 중요하다.

실무에 유용한 조회 패턴

다음은 실제 운영에서 자주 사용하는 조회 패턴들이다. 각 패턴은 다른 관점을 제공하므로 병행해서 확인하는 것을 권장한다.

1) 호출 횟수가 많아 누적 비용이 큰 쿼리

SELECT query, calls, total_time, mean_time
FROM pg_stat_statements
WHERE calls > 100
ORDER BY total_time DESC
LIMIT 50;

2) 평균 응답시간이 높은 쿼리

SELECT query, calls, mean_time, rows
FROM pg_stat_statements
WHERE mean_time > 100 -- ms 기준
ORDER BY mean_time DESC
LIMIT 50;

3) 최근 실행 추적 및 활성 쿼리 비교

pg_stat_activity와 결합하면 현재 실행 중인 쿼리와 통계 정보를 비교할 수 있다. 이로써 즉시 영향을 주는 쿼리를 파악할 수 있다.

SELECT a.pid, a.state, a.query AS running_query, s.calls, s.mean_time
FROM pg_stat_activity a
LEFT JOIN pg_stat_statements s ON s.query = normalize(a.query)
WHERE a.state = 'active';

주의: 위 예시는 쿼리 정규화 방식에 따라 직접 조인되지 않을 수 있다. 실제 환경에서는 텍스트 매칭 대신 hash 또는 queryid 컬럼 사용을 검토한다.

통계 초기화와 보존

pg_stat_statements는 누적 통계를 유지한다. 필요에 따라 통계를 초기화하거나 주기적으로 스냅샷을 남기는 방식으로 변화량을 모니터링하는 것이 유용하다.

-- 통계 초기화
SELECT pg_stat_statements_reset();

운영 환경에서는 재현 가능한 성능 문제 분석을 위해 초기화 시점을 기록하고, 변경 전후의 통계를 비교하는 절차가 권장된다.

해석 시 유의사항

• 쿼리 텍스트 정규화로 인해 파라미터 차이를 무시한 집계가 이뤄진다.
• short-lived 쿼리는 집계에 잘 드러나지 않을 수 있다.
• 플랜 변화가 성능 영향을 줄 수 있으므로 pg_stat_statements의 수치만으로 결론을 내리면 위험하다.

요약과 권장 흐름

pg_stat_statements 설치(postgres 설정 변경 및 확장 생성) 후에는 다음 순서로 진행하면 효과적이다.

• 전체 통계에서 total_time 상위 항목 확인
• mean_time과 calls를 함께 분석해 우선순위 결정
• 필요 시 pg_stat_activity와 연계해 현재 문제 원인 확인
• 변경 전후 통계 비교를 위해 pg_stat_statements_reset()로 초기화하거나 스냅샷 수집

pg_stat_statements 설치 postgres, slow query 찾기 postgres, pg_stat_statements 사용법 같은 키워드는 실제 설정과 조회 절차 검색에 유용한 관점이다. 확장을 통해 수집된 수치들을 기반으로 인덱스, 쿼리 리팩토링, 계획 변경 등을 단계적으로 적용하면 성능 개선에 도움이 된다.