PostgreSQL pg_stat_statements로 최근 느린 쿼리 찾기: 스냅샷 차이 계산

데이터베이스 PostgreSQL 2026년 9월 8일

pg_stat_statements를 실행했는데 매번 오래된 배치 쿼리만 상위에 보인다면, 지금 보고 있는 값이 어느 기간의 합계인지 먼저 확인해야 한다. 누적 실행 시간으로 정렬한 결과와 최근 측정 구간의 부담은 같은 질문이 아니다.

최근 구간을 보려면 시작과 끝의 통계를 저장하고, 비교 가능한 행끼리 실행 시간과 호출 수의 차이를 계산한다. 이를 위해 기존 통계를 지울 필요는 없다.

이 글은 PostgreSQL 18 공식 문서를 2026-09-08 확인해 작성한 진단 예제다. 공식 문서 대조와 합성 자료의 비교 로직 확인을 거쳤지만, 실제 PostgreSQL 서버에서의 실행 검증은 하지 않았다. 특정 운영 DB에서 실행한 복구 기록이나 성능 개선 보고서는 아니다. 예제는 현재 데이터베이스, 하나의 서버, 같은 접속 세션을 대상으로 한다. 이전 버전과 관리형 서비스는 확장 컬럼·권한·접속 방식을 먼저 확인해야 한다.

지금 막힌 쿼리와 최근 끝난 쿼리를 구분한다

지금 요청이 계속 멈춰 있다면 구간 통계 수집부터 시작하지 않는다. 현재 실행 상태와 차단 세션을 보는 것이 우선이다. PostgreSQL 모니터링 쿼리 모음에서 장기 실행·락·커넥션 점검 순서를 먼저 확인할 수 있다.

현재 활동을 보여주는 pg_stat_activity와 누적 실행 통계를 같은 시계로 읽지 않는 것이 중요하다. 실행 중인 긴 문장의 비용이 이번 수집에 아직 반영되지 않았을 수 있다. 실행 통계는 성공한 실행 단계가 끝날 때 갱신된다. pg_stat_statements 공식 문서

이번 절차가 답하려는 질문은 좁다. “두 번 관측하는 동안 통계에 반영된 SQL 중 어느 것이 많은 실행 시간을 썼는가?”다. API 응답시간, 커넥션 풀 대기, 실패한 모든 요청의 이력까지 한 번에 알려주는 도구로 사용하지 않는다.

누적 평균을 빼면 구간 평균이 되지 않는다

구간 계산은 다음 세 식으로 정리할 수 있다.

지표	계산
구간 호출 수	종료 `calls` − 시작 `calls`
구간 총 실행 시간	종료 `total_exec_time` − 시작 `total_exec_time`
구간 평균 실행 시간	구간 총 실행 시간 ÷ 구간 호출 수

종료 mean_exec_time - 시작 mean_exec_time은 구간 평균이 아니다. 평균은 그때까지의 호출 수에 따라 가중치가 바뀌므로 합계와 개수로 다시 계산해야 한다. 구간 호출 수가 0이면 평균을 0으로 만들지 않고 계산 대상에서 제외한다.

Google Cloud의 AlloyDB 성능 스냅샷 안내도 두 시점 실행 시간 차이를 부하 분석에 사용한다. 여기서는 이 원리를 일반 PostgreSQL용 SQL 예제로 구성한다. AlloyDB의 perfsnap 함수는 사용하지 않는다. Google Cloud 스냅샷 비교

단, 이 값은 두 벽시계 시점 사이의 모든 실행을 정확히 잘라낸 트레이스가 아니다. 수집 전에 시작해 수집 사이에 끝난 문장의 비용이 포함되거나, 아직 끝나지 않은 문장이 빠질 수 있다. 짧은 장애 순간을 분석할 때는 애플리케이션 로그와 현재 활동도 같이 본다.

1. 버전·설정·설치 위치를 먼저 확인한다

다음은 조회용 준비 SQL이다.

SELECT current_database(), current_user,
       current_setting('server_version') AS server_version;

SELECT e.extname, e.extversion, n.nspname AS extension_schema
FROM pg_extension AS e
JOIN pg_namespace AS n ON n.oid = e.extnamespace
WHERE e.extname = 'pg_stat_statements';

SELECT name, setting, pending_restart
FROM pg_settings
WHERE name IN (
    'shared_preload_libraries', 'compute_query_id',
    'pg_stat_statements.track', 'pg_stat_statements.track_planning',
    'track_io_timing'
)
ORDER BY name;

pending_restart는 설정 파일의 변경을 적용하는 데 재시작이 필요한지 보여준다. 설정이 보이지 않거나 확장 조회 결과가 없으면 수집 SQL로 넘어가지 말고 설치·권한·현재 DB를 먼저 확인한다. pg_settings

모듈을 새로 로드하려면 기존 shared_preload_libraries 항목을 보존하고 유지보수 절차를 잡아야 한다. 단순히 확장 생성만으로 모든 준비가 끝나는 것은 아니다. 다른 사용자의 SQL·식별자를 조회할 권한도 필요하므로 모니터링 계정의 접근 범위를 별도로 검토한다. 통계 행은 데이터베이스·사용자·쿼리 식별자·최상위 실행 여부의 조합으로 구분된다. pg_stat_statements 공식 문서

아래 예제는 확장이 public 스키마에 있고 stats_since 컬럼을 제공한다는 전제다. 위 조회에서 다른 스키마가 나왔다면 예제의 public.pg_stat_statements와 public.pg_stat_statements_info를 실제 위치로 바꾼다. 오래된 확장에서 컬럼이 없으면 검사를 지워 실행하지 말고 해당 버전에 맞는 수집기를 검토한다.

2. 원문 없이 관측용 뷰를 준비한다

진단 전용 세션에서 다음 임시 뷰를 만든다. 애플리케이션 테이블이나 영구 스키마를 바꾸지 않지만 임시 객체 생성 권한과 자원을 사용한다. 운영 정책상 임시 객체도 금지라면 승인된 모니터링 도구에서 같은 필드를 저장한다.

CREATE TEMP VIEW mlog_pgss_capture AS
SELECT
    statement_timestamp() AS sampled_at,
    pg_postmaster_start_time() AS server_started_at,
    i.stats_reset,
    i.dealloc,
    s.dbid,
    s.userid,
    s.queryid,
    s.toplevel,
    s.stats_since,
    s.calls,
    s.total_exec_time,
    s.shared_blks_read,
    s.temp_blks_written
FROM public.pg_stat_statements(false) AS s
CROSS JOIN public.pg_stat_statements_info AS i
WHERE s.dbid = (
    SELECT oid FROM pg_database WHERE datname = current_database()
)
  AND s.toplevel
  AND s.queryid IS NOT NULL;

false 인자는 SQL 원문을 가져오지 않도록 한다. 수집 파일이나 보고서에 원문이 불필요하게 퍼지는 것을 줄이려는 선택이다. 이것만으로 통계가 비민감 데이터가 되는 것은 아니다. pg_stat_statements 함수

최상위 문장만 수집해 바깥 함수 호출과 내부 SQL의 비용을 섞어 합산하는 일을 피한다. 내부 문장까지 분석하려면 별도 범위로 설계한다. 이 예제의 필터가 애플리케이션 업무 쿼리만 골라주는 것은 아니므로 진단 계정과 배치 계정도 구분해 해석해야 한다.

3. 같은 세션에서 T0와 T1을 저장한다

먼저 시작 스냅샷을 한 번 저장한다.

CREATE TEMP TABLE mlog_pgss_t0 AS
SELECT * FROM pg_temp.mlog_pgss_capture;

계획한 관측 구간 동안 평소 트래픽을 관찰한 뒤, 같은 접속에서 종료 스냅샷을 저장한다. 테스트를 위해 운영 서비스에 대량 요청을 만들 필요는 없다.

CREATE TEMP TABLE mlog_pgss_t1 AS
SELECT * FROM pg_temp.mlog_pgss_capture;

두 명령은 한 번에 붙여 실행하지 않는다. 두 시점을 장기 BEGIN 트랜잭션으로 묶어 기다리지도 않는다. 자동 커밋 상태의 전용 세션에서 각각 실행하는 구성을 권장한다.

임시 테이블은 세션에 속한다. GUI가 실행마다 새 연결을 만들거나 트랜잭션 풀링을 사용하면 이 방식에 맞지 않을 수 있다. 같은 실제 연결을 유지할 수 있는지 먼저 확인한다. CREATE TABLE AS는 그 시점 조회 결과를 저장하며 이후 원본 뷰의 변화가 자동 반영되는 구조가 아니다. 임시 테이블 · CREATE TABLE AS

다시 측정할 때는 새 전용 세션을 사용하거나 이번 세션의 임시 객체만 명확하게 정리한다. 이름 충돌이 났다는 이유로 public의 테이블을 삭제하지 않는다.

4. 계산 전에 비교 불가 행을 분류한다

다음 임시 뷰는 양쪽에 존재하는 동일 식별키를 맞추고, 비교에 사용할 수 없는 경우를 이유별로 구분한다.

CREATE TEMP VIEW mlog_pgss_delta AS
SELECT
    COALESCE(b.dbid, a.dbid) AS dbid,
    COALESCE(b.userid, a.userid) AS userid,
    COALESCE(b.queryid, a.queryid) AS queryid,
    COALESCE(b.toplevel, a.toplevel) AS toplevel,
    a.sampled_at AS window_start,
    b.sampled_at AS window_end,
    b.calls - a.calls AS delta_calls,
    b.total_exec_time - a.total_exec_time AS delta_exec_ms,
    b.shared_blks_read - a.shared_blks_read AS delta_shared_reads,
    b.temp_blks_written - a.temp_blks_written AS delta_temp_writes,
    CASE
        WHEN a.queryid IS NULL THEN 'missing_t0'
        WHEN b.queryid IS NULL THEN 'missing_t1'
        WHEN b.sampled_at <= a.sampled_at THEN 'invalid_time'
        WHEN b.server_started_at IS DISTINCT FROM a.server_started_at
            THEN 'server_changed'
        WHEN b.stats_reset IS DISTINCT FROM a.stats_reset
            THEN 'global_reset'
        WHEN b.dealloc IS DISTINCT FROM a.dealloc
            THEN 'entries_deallocated'
        WHEN b.stats_since IS DISTINCT FROM a.stats_since
            THEN 'entry_epoch_changed'
        WHEN b.calls < a.calls
          OR b.total_exec_time < a.total_exec_time
          OR b.shared_blks_read < a.shared_blks_read
          OR b.temp_blks_written < a.temp_blks_written
            THEN 'counter_decreased'
        WHEN b.calls = a.calls THEN 'no_completed_calls'
        ELSE 'comparable'
    END AS comparison_status
FROM pg_temp.mlog_pgss_t0 AS a
FULL OUTER JOIN pg_temp.mlog_pgss_t1 AS b
USING (dbid, userid, queryid, toplevel);

SELECT comparison_status, count(*) AS statement_groups
FROM pg_temp.mlog_pgss_delta
GROUP BY comparison_status
ORDER BY comparison_status;

여기서의 제외 정책은 공식 제품 기능이 아니라 잘못된 차이 계산을 피하기 위한 보수적인 예제 설계다.

상태	이 예제의 처리
`missing_t0` / `missing_t1`	한쪽 관측이 없으므로 0으로 채워 비교하지 않음
`server_changed` / `global_reset`	같은 연속 구간으로 취급하지 않고 다시 수집
`entries_deallocated`	비교 중 추적 엔트리 교체가 있어 구간을 엄격하게 제외
`entry_epoch_changed` / `counter_decreased`	개별 통계가 이어졌다는 가정이 깨져 제외
`no_completed_calls`	구간 평균을 계산하지 않음
`comparable`	아래 순위 계산 후보로 사용

dealloc은 서버 전체의 추적 엔트리 교체 신호다. 다른 데이터베이스의 활동 때문에 바뀔 수도 있어 이 규칙은 쓸 수 있는 행까지 제외할 수 있다. 이 예제에서는 결과를 많이 보여주는 것보다 모호한 구간을 숨기지 않는 쪽을 택했다. 신규·퇴출 행을 다루는 정교한 수집기가 있다면 별도 정책으로 운영한다.

서버 시작 시각은 연속성 확인을 돕지만 고유 서버 ID를 대신하지 않는다. 모니터링 시스템으로 확장한다면 인스턴스 ID·버전·역할을 별도 키로 저장하고 서로 다른 서버의 값을 섞지 않는다. 서버 시작시각 함수

통계 읽기와 메타정보 읽기가 모든 엔트리에 대해 하나의 원자적 스냅샷을 보장하는 것은 아니다. 수집 중 초기화·설정 변경·재시작이 있었다면 검사를 통과하더라도 그 구간을 사용하지 않는다. 무중단 수집기에서는 각 수집 전후의 초기화·교체 표식도 기록하는 추가 검증이 필요하다.

5. 구간 부하 순위를 조회한다

비교 상태를 확인한 뒤 사용할 행만 조회한다.

SELECT
    dbid, userid, queryid,
    window_start, window_end,
    delta_calls,
    round(delta_exec_ms::numeric, 2) AS interval_exec_ms,
    round(
        delta_exec_ms::numeric / NULLIF(delta_calls, 0),
        2
    ) AS interval_mean_ms,
    delta_shared_reads,
    delta_temp_writes
FROM pg_temp.mlog_pgss_delta
WHERE comparison_status = 'comparable'
ORDER BY delta_exec_ms DESC, delta_calls DESC
LIMIT 20;

LIMIT 20은 표시 개수일 뿐 정상 기준이 아니다. 평균 중심으로 보고 싶다면 마지막 정렬을 delta_exec_ms / NULLIF(delta_calls, 0) DESC로 바꾸되 호출 수를 함께 본다. 극히 적게 호출된 쿼리를 반복 업무 쿼리와 같은 중요도로 단정하지 않는다.

결과가 비어 있으면 “문제가 없다”가 아니다. 비교 상태가 모두 제외됐는지, 업무 트래픽이 있었는지, 권한 때문에 식별자를 보지 못했는지, 다른 DB를 보고 있는지 확인한다.

이 순위는 구간 전체의 완전한 부하 순위도 아니다. 시작에 없었던 새 쿼리와 끝에 사라진 쿼리는 제외했기 때문이다. 보고할 때 비교 가능한 기존 엔트리 기준이라는 범위를 함께 적는다. 누락이 많은 시기에 이 표만으로 최적화 우선순위를 확정하지 않는다.

숫자를 읽을 때 지킬 경계

총 실행 시간은 CPU 사용률이 아니다

여러 문장이 동시에 실행되면 실행 시간 합계가 관측 구간의 벽시계 길이보다 커질 수 있다. 이를 구간 초로 나눈 값을 서버 CPU 사용률이나 특정 API의 지연으로 표시하면 안 된다. CPU·I/O·락 지표를 따로 대조한다.

평균은 p95·p99가 아니다

합계와 호출 수로 구한 평균만으로 느린 일부 요청의 분포를 복원할 수 없다. 지연 상위 백분위가 필요하면 애플리케이션 계측 또는 해당 목적의 모니터링 데이터를 수집한다. 두 누적 최댓값을 빼서 구간 최댓값으로 사용하는 것도 피한다.

calls는 JDBC 배치 실행 횟수가 아니다

서버 문장 실행 통계와 JDBC 배치·네트워크 왕복·트랜잭션 커밋은 집계 단위가 다르다. 배치 쓰기 비교라면 JPA saveAll 배치 INSERT 검증의 관측 지표 구분과 같이 읽는다.

읽은 블록은 곧바로 물리 디스크 읽기가 아니다

PostgreSQL 밖의 운영체제 캐시까지 이 표가 구분해 주지는 않는다. track_io_timing이 꺼진 상태에서 I/O 시간 0을 보고 I/O가 없었다고 판단하지도 않는다. 이 설정을 켜는 데도 플랫폼별 계측 부담 검토가 필요하다. 통계 설정

발견한 SQL을 실제 튜닝으로 연결한다

먼저 부하가 증가한 시간대의 기능·배치·배포를 맞춘다. 예를 들어 집계 기능이 후보라면 PostgreSQL DAU·MAU 집계 쿼리 최적화처럼 처리 행이 늘어나는 지점을 검토할 수 있다. 순위에 올랐다는 이유만으로 해당 SQL의 인덱스가 부족하다고 확정하지 않는다.

SQL 원문은 권한 있는 내부 화면에서 필요한 후보만 조회한다. 원문은 정규화되더라도 완전한 익명화가 아니며, queryid도 모든 서버·메이저 버전에서 유지되는 전역 ID가 아니다. 스크린샷·CSV를 외부에 공유하기 전에 식별자와 업무 정보의 공개 범위를 검토한다.

다음 순서로 검증하면 이번 글의 범위를 넘어선 조치를 분리할 수 있다.

쿼리의 호출 맥락과 실제 바인딩 조건을 확인한다.
먼저 일반 EXPLAIN으로 예상 계획을 읽는다.
실행이 허용된 격리 환경에서만 실제 계획을 측정한다.
결과 집합의 정확성을 먼저 비교한다.
비슷한 트래픽·데이터·캐시 조건에서 새 T0/T1 구간을 수집한다.

EXPLAIN ANALYZE는 SQL을 실제 실행한다. 데이터 변경과 함수 부작용이 일어날 수 있으며 단순한 조회형 진단 명령이 아니다. EXPLAIN 공식 경고

실행계획에서 행 증폭이나 반복 스캔이 확인됐다면 JOIN 행 증폭·선집계·부분 인덱스를 후속으로 볼 수 있다. 이 글의 구간 순위는 고칠 후보를 고르는 도구이고, 해결책 자체는 아니다.

측정을 위해 통계를 지우지 않는다

새 구간을 보고 싶다는 이유로 pg_stat_statements_reset()을 실행하면 다른 운영자가 참고하던 관측값도 잃을 수 있다. 이 예제는 초기화 대신 두 관측값을 보존한다. 현재 통계만 가지고 이미 지나간 임의 시간대의 값을 복원할 수 없다는 한계도 함께 받아들여야 한다.

이번 절차를 끝낼 때 남길 것은 “상위 SQL 20개” 하나가 아니다. 관측 시작·종료 시각, 대상 DB와 서버, 제외 사유별 행 수, 비교 가능한 SQL의 구간 호출 수·총 시간·평균, 확인하지 못한 범위를 함께 기록한다. 그래야 다음 배포나 튜닝 뒤에도 같은 질문으로 비교할 수 있다.

참고 문서

공식 문서 확인일: 2026-09-08.

Windows에서 Neon PostgreSQL 5432 연결 실패 해결: 회사망 진단부터 OCI SSH 터널까지

4일 전 • 39 분 길이

mLog 유령 캐릭터가 단건 데이터와 묶음 데이터를 데이터베이스로 전송하며 JPA 배치 INSERT를 최적화하는 3D 일러스트

개발 Spring Boot

JPA saveAll 1만 5천 건이 2분 걸린 이유: PostgreSQL 배치 INSERT 최적화와 MyBatis 비교

5일 전 • 39 분 길이

Vue 3와 Spring Boot 운영 SQL 콘솔 앞의 데이터베이스를 방패로 보호하는 mLog 유령 캐릭터

개발 vue

Vue 3·Spring Boot 운영 SQL 콘솔 실전 설계: CRUD에서 보안·감사·Excel까지

5일 전 • 38 분 길이