pg_stat_statements 해설
pg_stat_statements는 컬럼이 스무 개가 넘지만, DBA가 매일 보는 건 사실 대여섯 개뿐이에요. 나머지는 그 대여섯 개를 해석할 때 보조로 쓰이며, 이 글에서는 핵심 컬럼을 읽는 법과 이 확장의 내부 동작 때문에 가끔 데이터가 비어 보이는 이유를 운영 관점에서 정리해요.
같은 주에 boringsql와 pganalyze가 이 주제를 각각 깊게 다뤘습니다. 두 글을 PostgreSQL 공식 문서로 교차 확인하면서 한국어 실무 가이드로 다시 엮었습니다.
pg_stat_statements가 수집하는 것
pg_stat_statements는 공유 메모리에 있는 고정 크기 해시 테이블입니다. 개별 실행을 하나하나 기록하는 게 아니라, 같은 모양의 쿼리를 하나의 항목으로 묶어 누적 카운터를 갱신합니다.
여기서 "같은 모양"의 기준은 정규화(normalization)입니다. 상수를 떼어내고 $1, $2 같은 자리표시자로 바꾼 뒤, 파싱된 쿼리 트리의 해시값을 queryid로 삼습니다. 그래서 아래 두 쿼리는 하나의 항목으로 집계됩니다.
SELECT * FROM users WHERE id = 42;
SELECT * FROM users WHERE id = 99;
여기서 DBA가 미리 알아둬야 할 정규화의 성질이 몇 가지 있습니다.
queryid는 메이저 버전 사이에서 안정적이지 않습니다. 같은 SQL이라도 메이저 업그레이드 후, 혹은 다른 CPU 아키텍처에서 다른 값으로 해시될 수 있습니다. 여러 서버의 통계를 묶을 때queryid를 영구 조인 키로 쓰면 안 됩니다.- OID 기반이라 이름 기반이 아닙니다. 테이블을 drop하고 다시 만들면 새 OID가 붙고, 그 결과 새
queryid가 생깁니다. 이전 통계는 고아로 남습니다. - 구조에 민감합니다. 테이블 alias가 다르거나, 컬럼 목록이 바뀌거나,
LIMIT이 붙거나, 조건 순서가 달라지면 논리적으로 같은 쿼리가 여러 항목으로 쪼개집니다. ORM이 이런 변형을 대량으로 쏟아내면 항목이 수백 개로 흩어지기도 합니다.
또 하나, 중요한 동작상의 제약이 있습니다. pg_stat_statements는 ExecutorEnd 단계에서 통계를 기록합니다. 즉 정상적으로 끝난 실행만 집계됩니다. 타임아웃으로 끊기거나 중간에 abort된 쿼리는 흔적을 남기지 않습니다. 장애 시점에 문제를 일으킨 쿼리가 정작 이 뷰에 안 보이는 경우가 여기서 나옵니다.
DBA가 봐야 할 핵심 컬럼
전체 컬럼은 공식 문서에 다 있습니다. 여기서는 운영 중에 실제로 손이 가는 것만 추립니다.
| 컬럼 | 의미 | 언제 보나 |
|---|---|---|
calls | 실행 횟수 | 빈도. 한 번 느린지, 자주 느린지 구분 |
total_exec_time | 누적 실행 시간(ms) | 서버 부하에 가장 크게 기여하는 쿼리 찾기 |
mean_exec_time | 평균 실행 시간(ms) | 한 번 실행이 비싼 쿼리 찾기 |
min/max/stddev_exec_time | 최소, 최대, 표준편차(ms) | 편차가 큰 쿼리 식별 |
rows | 반환/영향 행 수 | 호출당 결과 규모 추정 |
shared_blks_hit | buffer cache 적중 블록 수 | 캐시 효율 |
shared_blks_read | 디스크에서 읽은 공유 블록 수 | 실제 I/O 부담 |
wal_bytes | 생성한 WAL 양(bytes) | 쓰기 부하/복제 부담 |
느린 쿼리를 찾을 때 mean과 total 구분하기
가장 먼저 정해야 할 건 정렬 기준입니다. mean_exec_time과 total_exec_time은 전혀 다른 질문에 답합니다.
total_exec_time이 큰 쿼리는 서버 전체 부하에 가장 많이 기여하는 쿼리입니다. 한 번은 1ms로 빨라도 하루에 수천만 번 실행되면 누적 시간이 1위가 됩니다. 서버가 바쁜 원인을 찾을 때는 이 값을 봅니다.mean_exec_time이 큰 쿼리는 한 번 실행이 비싼 쿼리입니다. 빈도는 낮아도 실행할 때마다 사용자를 기다리게 만듭니다. 특정 화면이 느리다는 제보를 추적할 때 유용합니다.
-- 누적 부하 상위 10개
SELECT
queryid,
calls,
round(total_exec_time::numeric, 1) 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 10;
calls를 항상 함께 봐야 둘을 헷갈리지 않습니다. total_exec_time이 높은데 calls도 높으면 "자주 불려서 누적된" 것이고, calls가 낮은데 total이 높으면 "한 번이 무거운" 것입니다.
평균에서 사라지는 분포
pg_stat_statements는 평균(mean)과 표준편차(stddev)만 유지하고, 개별 실행 시간은 즉시 버립니다. 그래서 분포의 모양을 알 수 없습니다.
예를 들어 99%는 1ms에 끝나지만 1%는 2초가 걸리는 쿼리가 있다면, 평균은 약 21ms 근처로 찍힙니다. 이 21ms는 어느 실행도 대표하지 못하는 숫자입니다. 정작 문제가 되는 2초짜리 1%는 나머지 99%에 희석돼 평균 뒤로 묻힙니다. 게다가 pg_stat_statements는 p99(실행의 99%가 그 안에 들어오는 시간, 상위 1%를 잘라낸 경계) 같은 percentile 값을 주지 않으니, 평균만 봐서는 이런 느린 실행을 따로 짚을 길이 없습니다. stddev_exec_time이 평균에 비해 유난히 크거나 max_exec_time이 평균과 크게 벌어져 있다면, 그 쿼리는 분포가 넓다는 신호이니 별도로 들여다볼 가치가 있습니다.
I/O 컬럼의 hit와 read 읽기
shared_blks_hit과 shared_blks_read는 짝으로 읽습니다.
shared_blks_hit은 buffer cache에서 바로 찾은 블록 수입니다. 빠릅니다.shared_blks_read는 buffer에 없어서 디스크(또는 OS page cache)에서 읽어 온 블록 수입니다. 느립니다.
shared_blks_read가 크다는 건 그 쿼리가 실제 I/O를 많이 일으킨다는 뜻입니다. 작업 집합이 buffer pool보다 크거나, 인덱스 없이 큰 테이블을 스캔하고 있을 가능성이 있습니다. 두 값을 합친 대비 적중률을 보면 캐시 효율을 가늠할 수 있습니다.
읽기/쓰기에 걸린 실제 시간까지 보려면 shared_blk_read_time, shared_blk_write_time 컬럼을 봅니다. 단, 이 값은 track_io_timing이 켜져 있어야 채워지고, 꺼져 있으면 전부 0입니다.
temp_blks_read, temp_blks_written은 임시 파일 I/O입니다. 정렬이나 해시 조인이 work_mem을 넘겨서 디스크로 흘러넘쳤다는 신호이므로, 이 값이 큰 쿼리는 work_mem 조정이나 쿼리 재작성 후보입니다.
쓰기 부하를 보여주는 WAL 컬럼
wal_bytes, wal_records, wal_fpi는 그 쿼리가 만든 WAL 양을 보여줍니다. 쓰기가 많은 쿼리, 즉 replication 지연이나 디스크 쓰기 부하의 원인을 찾을 때 봅니다. wal_fpi(full page image)가 유난히 크면 checkpoint 직후의 첫 쓰기에서 페이지 전체가 WAL에 기록되는 패턴일 수 있습니다.
버전에 따른 total_time 분리
PostgreSQL 13에서 타이밍 컬럼 이름이 바뀌었습니다. 12 이하에서는 total_time, mean_time 한 묶음이었지만, 13부터 계획(planning)과 실행(execution)이 분리되어 total_plan_time/total_exec_time, mean_plan_time/mean_exec_time으로 나뉘었습니다. 12 이하를 대상으로 한 옛 쿼리는 13 이상에서 컬럼명을 바꿔줘야 동작합니다.
단, 계획 시간 컬럼(total_plan_time 등)은 pg_stat_statements.track_planning이 켜져 있어야 채워집니다. 이 설정의 기본값은 off입니다. 계획 시간이 전부 0으로 보인다면 이 설정부터 확인합니다.
PostgreSQL 17부터는 stats_since(항목 통계 수집이 시작된 시각)와 minmax_stats_since(min/max 통계 수집 시작 시각) 컬럼이 추가됐습니다. 어떤 구간을 측정할 때 그 사이에 eviction이나 리셋이 끼었는지 판단하는 데 씁니다.
해시 테이블이 가득 찰 때의 eviction과 dealloc
pg_stat_statements가 추적하는 서로 다른 쿼리 수에는 상한이 있습니다. pg_stat_statements.max이고 기본값은 5000입니다. 이 값은 서버 시작 시에만 설정할 수 있습니다.
서로 다른 쿼리 수가 이 상한을 넘으면 eviction(축출)이 일어납니다. 공식 문서는 "가장 적게 실행된(least-executed) 항목 정보를 버려서 새 항목 자리를 만든다"고 설명합니다. 내부적으로는 각 항목에 겉으로 드러나지 않는 usage 카운터가 있고, deallocation 시점에 모든 usage 값을 일정 비율 줄인 뒤 정렬해서 하위 일부를 버립니다. 이 정렬은 항목 수에 비례하는 작업이며, exclusive lock을 잡은 채로 진행됩니다.
그래서 pg_stat_statements.max를 무작정 키우는 건 공짜가 아닙니다. max가 클수록 deallocation 시점의 정렬 비용이 커지고, 그동안 lock 경합도 길어집니다.
축출이 일어났는지는 별도 뷰인 pg_stat_statements_info로 확인합니다. 이 뷰는 단 한 행이고 컬럼 두 개를 가집니다.
| 컬럼 | 의미 |
|---|---|
dealloc | max를 초과해 항목이 축출된 누적 횟수 |
stats_reset | 전체 통계가 마지막으로 리셋된 시각 |
SELECT dealloc, stats_reset FROM pg_stat_statements_info;
dealloc이 계속 올라간다면, 테이블이 쉼 없이 가득 차고 비워지는 중입니다. 이 상태에서는 자주 실행되지 않는 쿼리의 통계가 수시로 사라지므로 데이터를 신뢰하기 어렵습니다. ORM이 구조가 조금씩 다른 쿼리를 대량으로 흘려보내 해시 테이블을 가득 채우는 경우가 대표적입니다. 이때 실행 빈도가 낮은 항목이 먼저 축출되면서, 나중에 그 쿼리를 되짚어볼 단서 자체가 남지 않습니다.
대응은 두 갈래입니다. max를 적당히 늘리거나(정렬 비용 증가를 감수하고), 애초에 쿼리 변형 수를 줄이는 것입니다. 예컨대 길이가 제각각인 IN 목록은 PostgreSQL 18 이전까지 길이마다 별도 항목을 만들었는데, = ANY($1) 배열 바인딩으로 바꾸면 버전과 무관하게 하나의 항목으로 묶입니다.
아래 도식은 새 쿼리가 들어왔을 때 항목이 잡히고 축출되기까지의 흐름입니다.
운영 관점의 리셋 전략과 함정
단조 증가하는 누적 통계
뷰의 카운터는 마지막 리셋 이후로 계속 쌓입니다. 그래서 "지금 어떤 쿼리가 부하를 일으키는가"를 보려면 두 시점의 스냅샷을 떠서 빼는 수밖에 없습니다. 한 번 조회한 절댓값만으로는 한 달 전 배치 작업과 방금 들어온 트래픽이 뒤섞여 보입니다.
모니터링 도구들이 일정 간격으로 뷰를 떠다가 차분을 계산하는 이유가 이것입니다. 직접 차분을 계산할 때는 그 사이에 eviction이나 리셋이 끼면 음수가 나올 수 있으니, stats_since(17 이상)나 pg_stat_statements_info의 dealloc/stats_reset을 함께 확인해야 합니다.
리셋 함수
pg_stat_statements_reset()으로 통계를 비웁니다. 인자 없이 호출하면 전체를 리셋하고, 특정 대상만 비울 수도 있습니다.
-- 전체 리셋
SELECT pg_stat_statements_reset();
-- 특정 user / db / queryid만 리셋 (0은 "전체" 의미)
SELECT pg_stat_statements_reset(0, 0, :queryid);
minmax_only 인자를 true로 주면 min/max_exec_time, min/max_plan_time만 비웁니다. 평균과 누적은 살려두고 최소/최대만 다시 측정하고 싶을 때 씁니다. 기본값은 superuser만 실행할 수 있고, 필요하면 GRANT로 권한을 넘깁니다.
리셋 전략에는 정답이 없습니다. 흔한 선택은 두 가지입니다.
- 리셋하지 않고 누적 + 차분. 모니터링 도구에 맡기는 방식. 장기 추세를 잃지 않습니다.
- 정기 리셋(예: 배포/점검 전후). 특정 구간만 깨끗하게 보고 싶을 때. 단, 리셋 순간 이전 데이터는 사라지니 추세 분석은 포기합니다.
자주 밟는 함정 정리
- 타임아웃/abort된 쿼리는 집계되지 않습니다. 장애 원인 쿼리가 뷰에 없을 수 있습니다.
- 평균만으로 판단하면 드물게 튀는 느린 실행을 놓칩니다.
max_exec_time/stddev로 분포를 함께 의심합니다. dealloc이 오르는 상태에서는 통계가 불완전합니다. 먼저 그것부터 해결합니다.- 계획 시간(
*_plan_time)이 0이면track_planning이 꺼진 것입니다. - I/O 시간(
shared_blk_read_time등)이 0이면track_io_timing이 꺼진 것입니다. queryid는 메이저 버전/아키텍처 간에 달라질 수 있어 영구 키로 부적합합니다.
정리
pg_stat_statements는 컬럼이 많아 보여도 DBA의 시선은 결국 몇 갈래로 좁혀져요. 부하의 원인은 total_exec_time, 느린 한 방은 mean_exec_time으로 찾고 둘 다 calls로 맥락을 잡으며, I/O가 의심되면 shared_blks_read와 temp 블록을, 쓰기가 의심되면 wal_bytes를 봐요. 데이터가 비어 보이거나 음수가 나오면 pg_stat_statements_info의 dealloc과 stats_reset을 먼저 확인하면 되고, 이 정도만 손에 익히면 대부분의 쿼리 튜닝은 이 뷰 하나에서 출발할 수 있어요.
참고한 출처: