pg_stat_statements란? — 쿼리 성능의 블랙박스를 여는 열쇠
PostgreSQL 운영에서 가장 흔한 질문은 “어떤 쿼리가 느린가?”입니다. pg_stat_statements는 PostgreSQL에 내장된 확장(extension)으로, 실행된 모든 SQL 문의 통계를 자동 수집합니다. 실행 횟수, 총 소요 시간, 읽은 블록 수, 임시 파일 사용량까지 — 쿼리 튜닝에 필요한 모든 지표를 한 곳에서 볼 수 있습니다.
EXPLAIN ANALYZE가 개별 쿼리의 실행 계획을 보여준다면, pg_stat_statements는 전체 워크로드의 거시적 패턴을 보여줍니다. 느린 쿼리 하나보다 초당 10만 번 실행되는 “빠른” 쿼리가 실제로 더 큰 부하를 줄 수 있고, 이런 패턴은 pg_stat_statements 없이는 발견하기 어렵습니다.
설치와 설정
확장 활성화
-- postgresql.conf에 추가
shared_preload_libraries = 'pg_stat_statements'
-- PostgreSQL 재시작 후 확장 생성
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
-- 설치 확인
SELECT * FROM pg_stat_statements LIMIT 1;
shared_preload_libraries에 등록해야 하므로 PostgreSQL 재시작이 필수입니다. 이 점이 운영 중 활성화할 때 유일한 허들입니다.
핵심 설정 파라미터
# postgresql.conf 권장 설정
# 추적할 고유 쿼리 수 (기본 5000, 프로덕션은 10000 권장)
pg_stat_statements.max = 10000
# 추적 대상 (top = 최상위 SQL만, all = 함수 내부 SQL도 포함)
pg_stat_statements.track = top
# 유틸리티 명령(CREATE, ALTER 등) 추적 여부
pg_stat_statements.track_utility = off
# I/O 타이밍 추적 (약간의 오버헤드, 하지만 매우 유용)
pg_stat_statements.track_planning = on
# 쿼리 텍스트 저장 (off로 하면 통계만 수집)
pg_stat_statements.save = on
track_planning = on은 PostgreSQL 13+에서 사용 가능하며, 쿼리의 플래닝 시간과 실행 시간을 분리해서 볼 수 있어 매우 유용합니다.
핵심 컬럼 완전 해부
pg_stat_statements 뷰는 수십 개의 컬럼을 제공합니다. 실전에서 중요한 컬럼을 그룹별로 정리합니다.
식별 컬럼
| 컬럼 | 설명 |
|---|---|
userid |
쿼리 실행 사용자 OID |
dbid |
데이터베이스 OID |
queryid |
쿼리의 해시 ID (정규화된 쿼리 식별자) |
query |
정규화된 쿼리 텍스트 (리터럴 값이 $1, $2로 치환됨) |
queryid가 핵심입니다. SELECT * FROM users WHERE id = 1과 SELECT * FROM users WHERE id = 42는 동일한 queryid를 공유합니다. 리터럴 값이 다른 동일 패턴의 쿼리를 하나로 묶어 통계를 수집합니다.
시간 관련 컬럼 (PostgreSQL 13+)
| 컬럼 | 설명 |
|---|---|
calls |
총 실행 횟수 |
total_exec_time |
총 실행 시간 (ms) |
mean_exec_time |
평균 실행 시간 (ms) |
min_exec_time |
최소 실행 시간 |
max_exec_time |
최대 실행 시간 |
stddev_exec_time |
실행 시간 표준편차 |
total_plan_time |
총 플래닝 시간 (ms) |
mean_plan_time |
평균 플래닝 시간 (ms) |
I/O 관련 컬럼
| 컬럼 | 설명 |
|---|---|
shared_blks_hit |
공유 버퍼 캐시 히트 블록 수 |
shared_blks_read |
디스크에서 읽은 블록 수 |
shared_blks_dirtied |
더티 블록 수 (변경된 블록) |
shared_blks_written |
디스크에 쓴 블록 수 |
temp_blks_read |
임시 파일 읽기 블록 수 |
temp_blks_written |
임시 파일 쓰기 블록 수 |
temp_blks가 높으면 work_mem이 부족하여 디스크 소트가 발생하고 있다는 신호입니다.
행·WAL 관련 컬럼 (PostgreSQL 13+)
| 컬럼 | 설명 |
|---|---|
rows |
반환/영향받은 총 행 수 |
wal_records |
생성된 WAL 레코드 수 |
wal_fpi |
Full Page Image WAL 수 |
wal_bytes |
생성된 WAL 바이트 |
실전 쿼리 레시피 7가지
1. 총 실행 시간 Top 10 (가장 비싼 쿼리)
SELECT
queryid,
LEFT(query, 80) AS query_preview,
calls,
ROUND(total_exec_time::numeric, 2) AS total_ms,
ROUND(mean_exec_time::numeric, 2) AS avg_ms,
ROUND(max_exec_time::numeric, 2) AS max_ms,
rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;
이 쿼리가 가장 중요합니다. “총 시간 = 호출 횟수 × 평균 시간”이므로, 한 번은 빠르지만 수십만 번 호출되는 쿼리가 1위를 차지하는 경우가 많습니다.
2. 캐시 히트율 낮은 쿼리 (디스크 I/O 병목)
SELECT
queryid,
LEFT(query, 80) AS query_preview,
calls,
shared_blks_hit,
shared_blks_read,
ROUND(
100.0 * shared_blks_hit /
NULLIF(shared_blks_hit + shared_blks_read, 0), 2
) AS cache_hit_pct,
ROUND(total_exec_time::numeric, 2) AS total_ms
FROM pg_stat_statements
WHERE shared_blks_hit + shared_blks_read > 100
ORDER BY cache_hit_pct ASC
LIMIT 10;
캐시 히트율이 95% 미만인 쿼리는 인덱스 누락이나 대량 시퀀셜 스캔을 의심해야 합니다.
3. 임시 파일 사용량 Top 10 (work_mem 부족 후보)
SELECT
queryid,
LEFT(query, 80) AS query_preview,
calls,
temp_blks_read + temp_blks_written AS temp_blks_total,
ROUND(mean_exec_time::numeric, 2) AS avg_ms,
rows / NULLIF(calls, 0) AS avg_rows
FROM pg_stat_statements
WHERE temp_blks_read + temp_blks_written > 0
ORDER BY temp_blks_total DESC
LIMIT 10;
4. 플래닝 시간이 비정상적으로 긴 쿼리
SELECT
queryid,
LEFT(query, 80) AS query_preview,
calls,
ROUND(mean_plan_time::numeric, 4) AS avg_plan_ms,
ROUND(mean_exec_time::numeric, 4) AS avg_exec_ms,
ROUND(
100.0 * mean_plan_time /
NULLIF(mean_plan_time + mean_exec_time, 0), 1
) AS plan_pct
FROM pg_stat_statements
WHERE calls > 100
AND mean_plan_time > mean_exec_time
ORDER BY mean_plan_time DESC
LIMIT 10;
플래닝 시간이 실행 시간보다 긴 경우는 파티션 테이블의 파티션이 너무 많거나, 복잡한 상속 관계가 있을 때 발생합니다. Prepared Statement 사용을 고려하세요.
5. 실행 시간 편차가 큰 쿼리 (불안정한 쿼리)
SELECT
queryid,
LEFT(query, 80) AS query_preview,
calls,
ROUND(mean_exec_time::numeric, 2) AS avg_ms,
ROUND(stddev_exec_time::numeric, 2) AS stddev_ms,
ROUND(max_exec_time::numeric, 2) AS max_ms,
ROUND(stddev_exec_time / NULLIF(mean_exec_time, 0), 2) AS cv
FROM pg_stat_statements
WHERE calls > 50
AND stddev_exec_time > mean_exec_time
ORDER BY stddev_exec_time DESC
LIMIT 10;
변동계수(CV)가 1 이상이면, 동일한 쿼리인데도 실행 시간이 들쑥날쑥하다는 의미입니다. 파라미터 값에 따라 실행 계획이 달라지는 plan flip 문제일 가능성이 높습니다.
6. WAL 생성량 Top 10 (복제 지연 원인)
SELECT
queryid,
LEFT(query, 80) AS query_preview,
calls,
wal_records,
pg_size_pretty(wal_bytes) AS wal_size,
wal_fpi,
ROUND(total_exec_time::numeric, 2) AS total_ms
FROM pg_stat_statements
WHERE wal_bytes > 0
ORDER BY wal_bytes DESC
LIMIT 10;
리플리카 지연의 원인을 찾을 때 유용합니다. 대량 UPDATE/DELETE가 WAL을 과도하게 생성하면 복제 지연이 발생합니다.
7. 데이터베이스별 전체 워크로드 요약
SELECT
d.datname,
COUNT(*) AS unique_queries,
SUM(s.calls) AS total_calls,
ROUND(SUM(s.total_exec_time)::numeric, 2) AS total_exec_ms,
ROUND(
100.0 * SUM(s.shared_blks_hit) /
NULLIF(SUM(s.shared_blks_hit + s.shared_blks_read), 0), 2
) AS overall_cache_hit_pct,
pg_size_pretty(SUM(s.wal_bytes)::bigint) AS total_wal
FROM pg_stat_statements s
JOIN pg_database d ON s.dbid = d.oid
GROUP BY d.datname
ORDER BY total_exec_ms DESC;
통계 리셋과 스냅샷 전략
수동 리셋
-- 전체 리셋
SELECT pg_stat_statements_reset();
-- 특정 사용자 + DB만 리셋 (PostgreSQL 14+)
SELECT pg_stat_statements_reset(
userid := (SELECT usesysid FROM pg_user WHERE usename = 'app_user'),
dbid := (SELECT oid FROM pg_database WHERE datname = 'mydb'),
queryid := 0 -- 0이면 해당 user+db의 전체
);
자동 스냅샷 수집
통계는 누적값이므로, 주기적으로 스냅샷을 저장해야 시간대별 비교가 가능합니다.
-- 스냅샷 저장 테이블
CREATE TABLE pgss_snapshots (
snapshot_id BIGSERIAL PRIMARY KEY,
snapshot_time TIMESTAMPTZ NOT NULL DEFAULT now(),
queryid BIGINT NOT NULL,
dbid OID,
userid OID,
query TEXT,
calls BIGINT,
total_exec_time DOUBLE PRECISION,
mean_exec_time DOUBLE PRECISION,
rows BIGINT,
shared_blks_hit BIGINT,
shared_blks_read BIGINT,
temp_blks_written BIGINT,
wal_bytes NUMERIC
);
-- Cron으로 매시간 스냅샷 수집
-- pg_cron 또는 시스템 crontab 사용
SELECT cron.schedule('pgss-snapshot', '0 * * * *', $$
INSERT INTO pgss_snapshots
(queryid, dbid, userid, query, calls, total_exec_time,
mean_exec_time, rows, shared_blks_hit, shared_blks_read,
temp_blks_written, wal_bytes)
SELECT queryid, dbid, userid, query, calls, total_exec_time,
mean_exec_time, rows, shared_blks_hit, shared_blks_read,
temp_blks_written, wal_bytes
FROM pg_stat_statements;
$$);
시간대별 비교 쿼리
-- 최근 1시간 vs 이전 1시간 비교
WITH recent AS (
SELECT queryid, calls, total_exec_time
FROM pgss_snapshots
WHERE snapshot_time >= now() - INTERVAL '1 hour'
AND snapshot_time < now()
), previous AS (
SELECT queryid, calls, total_exec_time
FROM pgss_snapshots
WHERE snapshot_time >= now() - INTERVAL '2 hours'
AND snapshot_time < now() - INTERVAL '1 hour'
)
SELECT
r.queryid,
r.calls - COALESCE(p.calls, 0) AS delta_calls,
ROUND((r.total_exec_time - COALESCE(p.total_exec_time, 0))::numeric, 2)
AS delta_exec_ms,
LEFT(s.query, 60) AS query_preview
FROM recent r
LEFT JOIN previous p USING (queryid)
JOIN pg_stat_statements s USING (queryid)
ORDER BY delta_exec_ms DESC
LIMIT 10;
모니터링 통합: Prometheus + Grafana
pg_stat_statements를 Prometheus로 수집하면 시계열 모니터링이 가능합니다.
postgres_exporter 설정
# queries.yaml (postgres_exporter 커스텀 쿼리)
pg_stat_statements:
query: |
SELECT
queryid,
LEFT(query, 100) AS query,
calls,
total_exec_time,
mean_exec_time,
rows,
shared_blks_hit,
shared_blks_read,
temp_blks_written
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20
metrics:
- queryid:
usage: "LABEL"
description: "Query ID"
- query:
usage: "LABEL"
description: "Query text"
- calls:
usage: "COUNTER"
description: "Number of calls"
- total_exec_time:
usage: "COUNTER"
description: "Total execution time in ms"
- mean_exec_time:
usage: "GAUGE"
description: "Mean execution time in ms"
- rows:
usage: "COUNTER"
description: "Total rows returned"
- shared_blks_hit:
usage: "COUNTER"
description: "Shared buffer hits"
- shared_blks_read:
usage: "COUNTER"
description: "Shared blocks read from disk"
- temp_blks_written:
usage: "COUNTER"
description: "Temp blocks written"
핵심 Grafana 대시보드 패널
- Top Queries by Total Time:
rate(pg_stat_statements_total_exec_time[5m]) - Query Calls Rate:
rate(pg_stat_statements_calls[5m]) - Cache Hit Ratio per Query:
rate(shared_blks_hit[5m]) / (rate(shared_blks_hit[5m]) + rate(shared_blks_read[5m])) - Temp Blocks Alert:
rate(pg_stat_statements_temp_blks_written[5m]) > 0
pg_stat_statements와 함께 쓰면 좋은 도구들
- pgBadger: PostgreSQL 로그를 분석하여 HTML 리포트를 생성합니다. pg_stat_statements의 통계와 로그 기반 분석을 함께 보면 전체 그림이 완성됩니다.
- pg_stat_kcache: CPU 사용량과 실제 파일시스템 I/O를 쿼리별로 추적합니다. pg_stat_statements의 블록 수 통계를 실제 I/O 시간으로 보완합니다.
- auto_explain: 설정된 임계값을 초과하는 쿼리의 실행 계획을 자동으로 로그에 기록합니다. pg_stat_statements에서 느린 쿼리를 찾고, auto_explain으로 원인을 파악하는 워크플로우가 효과적입니다.
- pg_qualstats: WHERE 절의 조건별 선택도(selectivity)를 추적합니다. 인덱스 추천에 활용됩니다.
운영 주의사항과 팁
메모리 사용량
pg_stat_statements.max에 비례하여 공유 메모리를 사용합니다. 10,000개 기준 약 10~20MB 정도로, 대부분의 환경에서 무시할 수 있는 수준입니다.
성능 오버헤드
일반적으로 1~3% 미만의 오버헤드입니다. track_planning = on과 track_io_timing = on을 함께 켜면 약간 더 증가하지만, 프로덕션에서도 충분히 사용할 수 있는 수준입니다.
쿼리 정규화의 함정
-- 이 두 쿼리는 같은 queryid를 공유
SELECT * FROM orders WHERE status = 'pending' -- $1
SELECT * FROM orders WHERE status = 'completed' -- $1
-- 하지만 실행 계획이 완전히 다를 수 있음!
-- status = 'pending'은 1%의 행 → Index Scan
-- status = 'completed'는 80%의 행 → Seq Scan
평균 실행 시간만 보면 이런 차이를 놓칩니다. stddev_exec_time이 높은 쿼리는 이런 패턴을 의심하세요.
PostgreSQL 버전별 차이
- PG 12 이하:
total_time단일 컬럼 (plan + exec 합산) - PG 13:
total_exec_time/total_plan_time분리, WAL 통계 추가 - PG 14:
toplevel컬럼 추가, 특정 user/db/query만 리셋 가능 - PG 15:
temp_blk_read_time/temp_blk_write_time추가 - PG 16:
jit_*컬럼 추가 (JIT 컴파일 통계)
실전 워크플로우: 느린 쿼리 튜닝 사이클
┌─────────────────────────────────────────────────┐
│ 1. pg_stat_statements에서 Top 10 비용 쿼리 확인 │
│ → total_exec_time 기준 정렬 │
├─────────────────────────────────────────────────┤
│ 2. 문제 쿼리의 queryid로 상세 분석 │
│ → 캐시 히트율, temp 블록, 행 수 확인 │
├─────────────────────────────────────────────────┤
│ 3. EXPLAIN (ANALYZE, BUFFERS) 로 실행 계획 확인 │
│ → Seq Scan, Hash Join, Sort 병목 식별 │
├─────────────────────────────────────────────────┤
│ 4. 인덱스 추가 / 쿼리 리팩토링 / work_mem 조정 │
├─────────────────────────────────────────────────┤
│ 5. pg_stat_statements_reset() 후 재측정 │
│ → before/after 비교 │
└─────────────────────────────────────────────────┘
마무리
pg_stat_statements는 PostgreSQL 성능 분석의 필수 도구입니다. 오버헤드가 거의 없으면서 쿼리 워크로드의 전체 그림을 보여줍니다. 프로덕션 PostgreSQL을 운영한다면, pg_stat_statements가 활성화되어 있지 않은 것 자체가 리스크입니다.
핵심을 정리하면: total_exec_time으로 가장 비용이 큰 쿼리를 찾고, cache_hit_pct로 인덱스 문제를 발견하고, stddev_exec_time으로 불안정한 쿼리를 탐지하세요. 이 세 가지 축만으로도 대부분의 성능 문제를 진단할 수 있습니다.
관련 글로 PostgreSQL EXPLAIN ANALYZE 심화와 PostgreSQL VACUUM·MVCC 심화도 함께 참고하시면 PostgreSQL 성능 튜닝의 전체 그림을 잡는 데 도움이 됩니다.