pg_stat_statements가 알려주는 모든 것 — DBA가 실제로 봐야 할 컬럼과 해석

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가 봐야 할 핵심 컬럼

전체 컬럼은 공식 문서에 다 있다. 여기서는 운영 중에 실제로 손이 가는 것만 추린다.

느린 쿼리 찾기 — 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로 확인한다. 이 뷰는 단 한 행이고 컬럼 두 개를 가진다.

SELECT dealloc, stats_reset FROM pg_stat_statements_info;

dealloc이 계속 올라간다면, 테이블이 쉼 없이 가득 차고 비워지는 중이다. 이 상태에서는 자주 실행되지 않는 쿼리의 통계가 수시로 사라지므로 데이터를 신뢰하기 어렵다. ORM이 구조가 조금씩 다른 쿼리를 대량으로 흘려보내 해시 테이블을 가득 채우는 경우가 대표적이다. 이때 실행 빈도가 낮은 항목이 먼저 축출되면서, 나중에 그 쿼리를 되짚어볼 단서 자체가 남지 않는다.

대응은 두 갈래다. max를 적당히 늘리거나(정렬 비용 증가를 감수하고), 애초에 쿼리 변형 수를 줄이는 것이다. 예컨대 길이가 제각각인 IN 목록은 PostgreSQL 18 이전까지 길이마다 별도 항목을 만들었는데, = ANY($1) 배열 바인딩으로 바꾸면 버전과 무관하게 하나의 항목으로 묶인다.

아래 도식은 새 쿼리가 들어왔을 때 항목이 잡히고 축출되기까지의 흐름이다.

flowchart TD
    A[쿼리 실행 완료] --> B{같은 queryid
항목 존재?}
    B -- 있음 --> C[기존 항목
카운터 누적]
    B -- 없음 --> D{테이블
가득 찼나?}
    D -- 여유 있음 --> E[새 항목 생성]
    D -- 가득 참 --> F[usage 낮은 항목 축출]
    F --> G[dealloc 1 증가]
    G --> E

운영 관점 — 리셋 전략과 함정

통계는 단조 증가하는 누적값이다

뷰의 카운터는 마지막 리셋 이후로 계속 쌓인다. 그래서 “지금 어떤 쿼리가 부하를 일으키는가"를 보려면 두 시점의 스냅샷을 떠서 빼는 수밖에 없다. 한 번 조회한 절댓값만으로는 한 달 전 배치 작업과 방금 들어온 트래픽이 뒤섞여 보인다.

모니터링 도구들이 일정 간격으로 뷰를 떠다가 차분을 계산하는 이유가 이것이다. 직접 차분을 계산할 때는 그 사이에 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을 먼저 확인한다. 이 정도만 손에 익으면 대부분의 쿼리 튜닝 출발점은 이 뷰 하나로 충분하다.

참고한 출처:

• pg_stat_statements: the good, the bad and the ugly — boringsql
• Postgres in Production: pg_stat_statements Deep Dive (Part 4) — pganalyze
• PostgreSQL 공식 문서 — F.32. pg_stat_statements
• Queries for pg_stat_statements — pgMustard