PostgreSQL 모니터링 쿼리 모음: 장기 쿼리·락·세션·VACUUM·EXPLAIN 실전 가이드

서비스가 느려졌을 때 무작정 인덱스를 추가하거나 PostgreSQL을 재시작하면 원인을 놓치기 쉽습니다. 먼저 현재 실행 중인 SQL을 확인하고, 오래 걸리는 쿼리인지 락을 기다리는 쿼리인지 구분해야 합니다. 그다음 커넥션 고갈, 테이블과 인덱스 용량, VACUUM 상태, 반복적으로 많은 부하를 만드는 SQL, 실행 계획 순으로 범위를 좁히는 편이 안전합니다.

이 글에서는 다음 흐름으로 PostgreSQL 상태를 진단합니다.

1. 현재 무엇이 실행 중인지 확인한다.
2. 오래 실행되는 쿼리와 대기 이벤트를 찾는다.
3. 누가 누구의 락을 막고 있는지 추적한다.
4. 커넥션과 idle in transaction 세션을 확인한다.
5. 데이터베이스·테이블·인덱스 용량을 점검한다.
6. Dead Tuple과 VACUUM 상태를 확인한다.
7. pg_stat_statements로 누적 부하가 큰 SQL을 찾는다.
8. EXPLAIN과 EXPLAIN ANALYZE로 원인을 분석한다.
9. Spring Boot에 모니터링 기능을 안전하게 연동한다.

예시는 PostgreSQL 14 이상을 기준으로 작성했습니다. 버전에 따라 제공되는 컬럼과 EXPLAIN 옵션이 조금씩 다를 수 있으므로 운영 중인 버전의 공식 문서도 함께 확인하는 것이 좋습니다.

운영 환경 주의사항
pg_stat_activity 같은 조회는 비교적 안전하지만 pg_cancel_backend, pg_terminate_backend, VACUUM FULL, EXPLAIN ANALYZE는 실제 서비스에 영향을 줄 수 있습니다. 조회 결과만 보고 자동 실행하지 말고, 대상 세션과 트랜잭션을 다시 확인한 뒤 단계적으로 사용해야 합니다.

1. 점검 전에 알아둘 권한과 시간 계산 방식

일반 사용자는 자신의 세션 정보는 볼 수 있지만, 다른 사용자의 쿼리 전문이나 일부 상세 정보는 제한될 수 있습니다. 전체 활동을 봐야 한다면 DBA가 최소 범위로 pg_read_all_stats 또는 목적에 맞는 모니터링 권한을 검토해야 합니다. 웹 애플리케이션 계정에 슈퍼유저 권한을 주는 방식은 피해야 합니다.

다른 세션을 취소하거나 종료하는 권한도 별개입니다. 기본적으로 슈퍼유저, 대상 역할의 멤버, 또는 pg_signal_backend 권한을 가진 역할만 가능하며 슈퍼유저 세션은 슈퍼유저만 종료할 수 있습니다. 자세한 제한은 PostgreSQL 서버 시그널 함수 문서를 참고할 수 있습니다.

시간 계산에는 now()보다 clock_timestamp()가 더 안전한 경우가 있습니다. PostgreSQL의 now()는 현재 트랜잭션 시작 시각으로 고정됩니다. 모니터링 SQL이 이미 오래 열린 트랜잭션 안에서 실행되면 실제 현재 시각과 차이가 날 수 있기 때문에, 이 글의 쿼리에서는 벽시계 기준 현재 시각을 반환하는 clock_timestamp()를 사용합니다.

2. 현재 실행 중인 쿼리 확인

현재 실행 중인 클라이언트 쿼리를 실행 시간 순으로 확인하는 기본 쿼리입니다.

SELECT
    a.pid,
    a.usename AS user_name,
    a.datname AS database_name,
    a.application_name,
    a.client_addr,
    a.state,
    a.wait_event_type,
    a.wait_event,
    a.query_start,
    clock_timestamp() - a.query_start AS query_age,
    a.xact_start,
    clock_timestamp() - a.xact_start AS transaction_age,
    left(a.query, 1000) AS query
FROM pg_stat_activity AS a
WHERE a.state = 'active'
  AND a.backend_type = 'client backend'
  AND a.datname = current_database()
  AND a.pid <> pg_backend_pid()
ORDER BY query_age DESC;

결과에서 먼저 볼 값은 다음과 같습니다.

• query_age: 현재 SQL이 시작된 뒤 흐른 시간입니다.
• transaction_age: 현재 트랜잭션이 열린 뒤 흐른 시간입니다.
• wait_event_type, wait_event: 쿼리가 CPU를 사용해 실행 중인지, 락·I/O·클라이언트 응답 등을 기다리는지 판단하는 단서입니다.
• application_name, client_addr: 어느 애플리케이션과 서버가 실행했는지 추적할 때 유용합니다.

state = 'active'라고 해서 항상 CPU를 사용하고 있다는 뜻은 아닙니다. wait_event가 함께 표시된다면 쿼리는 실행 상태이지만 내부적으로 어떤 이벤트를 기다리고 있을 수 있습니다. pg_stat_activity의 각 컬럼과 상태 정의는 PostgreSQL 통계 모니터링 문서에 정리되어 있습니다.

또한 query_age는 순수 CPU 수행 시간이 아닙니다. SQL이 시작된 뒤 락, I/O, 네트워크 등을 기다린 시간까지 포함한 전체 경과 시간입니다.

수행 시간을 초 단위 숫자로 받기

Java나 JavaScript에서 직접 표시할 예정이라면 PostgreSQL의 interval을 문자열로 가공하기보다 초 또는 밀리초로 반환한 뒤 애플리케이션에서 포맷하는 편이 안전합니다.

SELECT
    pid,
    round(
        extract(epoch FROM (clock_timestamp() - query_start))::numeric,
        6
    ) AS query_age_seconds,
    query
FROM pg_stat_activity
WHERE state = 'active'
  AND pid <> pg_backend_pid()
ORDER BY query_age_seconds DESC;

예를 들어 112.698363이 반환되면 애플리케이션에서 1분 52.698363초로 바꿀 수 있습니다. to_char(..., 'FMMI분 FMSS.US초')처럼 분과 초만 출력하는 형식은 한 시간을 넘는 실행 시간에서 시간 정보가 누락될 수 있으므로 장기 쿼리 모니터링에는 주의해야 합니다.

3. 특정 시간 이상 실행된 장기 쿼리 찾기

다음은 3분 이상 실행 중인 쿼리만 조회합니다.

SELECT
    a.pid,
    a.usename,
    a.application_name,
    a.client_addr,
    a.wait_event_type,
    a.wait_event,
    a.query_start,
    clock_timestamp() - a.query_start AS query_age,
    clock_timestamp() - a.xact_start AS transaction_age,
    left(a.query, 1000) AS query
FROM pg_stat_activity AS a
WHERE a.state = 'active'
  AND a.backend_type = 'client backend'
  AND a.datname = current_database()
  AND a.pid <> pg_backend_pid()
  AND a.query_start < clock_timestamp() - interval '3 minutes'
ORDER BY query_age DESC;

3 minutes는 서비스 특성에 맞춰 바꿔야 합니다. 온라인 API에서는 10초도 비정상일 수 있지만, 정산·통계·배치 작업에서는 몇 분이 정상일 수도 있습니다. 따라서 “몇 분 이상이면 무조건 종료” 같은 전역 기준보다 application_name, 사용자, 업무 종류별 기준을 두는 편이 좋습니다.

장기 쿼리를 발견했다면 바로 종료하기 전에 다음을 확인합니다.

• 락을 기다리고 있는가?
• 디스크 I/O를 기다리고 있는가?
• 정상적인 배치나 백업 작업인가?
• 같은 SQL이 반복 실행되고 있는가?
• 트랜잭션이 지나치게 오래 열려 있는가?

4. 락에 막힌 쿼리와 차단 세션 추적

pg_blocking_pids()는 기다리는 세션을 실제로 막고 있는 PID를 찾는 가장 간단하고 신뢰할 만한 방법입니다. 여기에 pg_locks.waitstart를 결합하면 쿼리 전체 경과 시간이 아니라 실제 락 대기 시작 시각도 확인할 수 있습니다.

WITH waiting_lock AS (
    SELECT
        pid,
        min(waitstart) AS wait_start
    FROM pg_locks
    WHERE granted = false
    GROUP BY pid
)
SELECT DISTINCT
    blocked.pid AS blocked_pid,
    blocked.usename AS blocked_user,
    blocked.application_name AS blocked_application,
    waiting_lock.wait_start,
    clock_timestamp() - waiting_lock.wait_start AS lock_wait_age,
    blocked.query_start,
    clock_timestamp() - blocked.query_start AS blocked_query_age,
    blocked.wait_event_type,
    blocked.wait_event,
    blocker_pid.pid AS blocking_pid,
    blocker.usename AS blocking_user,
    blocker.application_name AS blocking_application,
    blocker.state AS blocking_state,
    blocker.xact_start AS blocking_xact_start,
    clock_timestamp() - blocker.xact_start AS blocking_xact_age,
    left(blocked.query, 1000) AS blocked_query,
    left(blocker.query, 1000) AS blocking_query
FROM pg_stat_activity AS blocked
JOIN waiting_lock
  ON waiting_lock.pid = blocked.pid
CROSS JOIN LATERAL
    unnest(pg_blocking_pids(blocked.pid)) AS blocker_pid(pid)
LEFT JOIN pg_stat_activity AS blocker
  ON blocker.pid = blocker_pid.pid
ORDER BY lock_wait_age DESC;

이 쿼리에서 가장 중요한 값은 다음과 같습니다.

• blocked_pid: 락을 얻지 못해 기다리는 세션입니다.
• blocking_pid: 앞의 세션을 막고 있는 세션입니다.
• lock_wait_age: 현재 락을 기다린 실제 시간입니다.
• blocking_xact_age: 차단 세션의 트랜잭션이 열린 시간입니다.
• blocking_state: 차단 세션이 현재 실행 중인지, 트랜잭션을 연 채 쉬고 있는지 보여줍니다.

기존에 자주 쓰이는 clock_timestamp() - blocked.query_start는 “락 대기 시간”이 아니라 해당 쿼리의 전체 경과 시간입니다. 정확한 락 대기 시작 시각은 pg_locks.waitstart를 사용해야 합니다. 다만 대기가 막 시작된 아주 짧은 순간에는 granted = false여도 waitstart가 잠시 NULL일 수 있습니다. 자세한 내용은 pg_locks 공식 문서와 pg_blocking_pids 공식 문서에서 확인할 수 있습니다.

blocking_pid가 0이면 일반 백엔드가 아니라 준비된 트랜잭션이 락을 보유한 경우일 수 있습니다. 이때는 pg_prepared_xacts까지 확인해야 합니다.

5. 문제 쿼리를 취소하거나 세션 종료하기

먼저 실행 중인 SQL만 취소합니다.

SELECT pg_cancel_backend(12345);

이 함수는 대상 연결을 유지한 채 현재 쿼리에 취소 신호를 보냅니다. 애플리케이션은 쿼리 취소 예외를 받습니다. 명시적 트랜잭션 안에서 취소됐다면 트랜잭션이 오류 상태가 될 수 있으므로 ROLLBACK 또는 적절한 savepoint 복구가 필요합니다. 연결이 살아 있다는 이유만으로 곧바로 풀에 정상 반환해서는 안 됩니다.

취소가 듣지 않고 서비스 장애가 계속될 때만 세션 종료를 검토합니다.

SELECT pg_terminate_backend(12345);

세션이 종료되면 열려 있던 트랜잭션은 롤백되고 보유 중인 락이 풀립니다. 반면 애플리케이션에는 연결 종료 오류가 발생하며, 큰 트랜잭션은 롤백에도 시간이 걸릴 수 있습니다.

종료 전 재확인
PID는 시간이 지나면 다른 세션에 재사용될 수 있습니다. 오래전에 복사한 PID를 바로 종료하지 말고 usename, application_name, client_addr, xact_start, query를 다시 조회한 뒤 조치해야 합니다. 자동화할 때도 PID 하나만 신뢰해서는 안 됩니다.

권한은 모니터링 계정과 종료 계정을 분리하는 것이 좋습니다. 웹 애플리케이션에는 pg_signal_backend를 주지 않고, 인증·감사 로그가 적용된 운영자 전용 경로에서만 취소와 종료를 허용하는 방식이 안전합니다.

6. 커넥션 수와 idle in transaction 점검

현재 커넥션 상태 요약

SELECT
    datname,
    usename,
    application_name,
    state,
    count(*) AS connection_count
FROM pg_stat_activity
WHERE backend_type = 'client backend'
GROUP BY
    datname,
    usename,
    application_name,
    state
ORDER BY connection_count DESC;

전체 클라이언트 커넥션 수와 설정된 최대값도 함께 볼 수 있습니다.

SELECT
    count(*) FILTER (
        WHERE backend_type = 'client backend'
    ) AS client_connections,
    current_setting('max_connections')::integer AS max_connections
FROM pg_stat_activity;

idle은 커넥션 풀에서 다음 요청을 기다리는 정상 연결일 수 있으므로 많다는 이유만으로 종료하면 안 됩니다. 애플리케이션 인스턴스 수, HikariCP의 풀 크기, 관리자·배치 연결, 예약 커넥션까지 함께 계산해야 합니다.

오래 열린 idle in transaction 세션 찾기

SELECT
    pid,
    usename,
    application_name,
    client_addr,
    state,
    xact_start,
    clock_timestamp() - xact_start AS transaction_age,
    state_change,
    clock_timestamp() - state_change AS idle_age,
    left(query, 1000) AS last_query
FROM pg_stat_activity
WHERE state LIKE 'idle in transaction%'
ORDER BY transaction_age DESC;

여기서 query는 현재 실행 중인 쿼리가 아니라 해당 세션이 마지막으로 실행한 SQL입니다. 실제 문제는 애플리케이션이 COMMIT이나 ROLLBACK을 하지 않은 채 트랜잭션을 열어 둔 것입니다. 이런 세션은 락을 오래 보유하고 VACUUM이 오래된 행 버전을 정리하지 못하게 만들 수 있습니다.

수동 종료만 반복하기보다 애플리케이션의 트랜잭션 경계를 수정하고 역할 단위 타임아웃을 검토하는 편이 좋습니다.

ALTER ROLE app_user
    SET statement_timeout = '30s';

ALTER ROLE app_user
    SET lock_timeout = '3s';

ALTER ROLE app_user
    SET idle_in_transaction_session_timeout = '2min';

값은 예시일 뿐입니다. 배치와 온라인 요청에 같은 값을 적용하지 말고 역할을 나눠 설정해야 합니다. 커넥션 풀이 예상치 못한 연결 종료를 올바르게 복구하는지도 먼저 테스트해야 합니다. 각 타임아웃의 동작은 PostgreSQL 클라이언트 연결 기본값 문서에 설명되어 있습니다.

7. 데이터베이스·테이블·인덱스 용량 확인

데이터베이스별 용량

SELECT
    datname AS database_name,
    pg_size_pretty(pg_database_size(datname)) AS database_size,
    pg_database_size(datname) AS database_size_bytes
FROM pg_database
WHERE datallowconn = true
ORDER BY database_size_bytes DESC;

테이블과 인덱스 용량 분리

SELECT
    schemaname,
    relname AS table_name,
    pg_size_pretty(pg_table_size(relid)) AS table_size,
    pg_size_pretty(pg_indexes_size(relid)) AS index_size,
    pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
    pg_total_relation_size(relid) AS total_size_bytes
FROM pg_stat_user_tables
ORDER BY total_size_bytes DESC;

pg_table_size()는 테이블 본체뿐 아니라 TOAST, Free Space Map, Visibility Map을 포함하고 인덱스는 제외합니다. pg_total_relation_size()는 여기에 인덱스까지 더한 값입니다. 반면 pg_relation_size()는 기본적으로 관계의 main fork만 계산하므로 “테이블 전체 데이터 용량”으로 설명하면 누락이 생길 수 있습니다. 함수별 정확한 범위는 PostgreSQL 데이터베이스 객체 크기 함수 문서에서 확인할 수 있습니다.

8. 사용되지 않는 것처럼 보이는 인덱스 찾기

다음 쿼리는 통계상 스캔 횟수가 0인 인덱스를 찾되, 기본 키·고유 인덱스·제약조건 여부도 같이 보여줍니다.

SELECT
    s.schemaname,
    s.relname AS table_name,
    s.indexrelname AS index_name,
    s.idx_scan,
    pg_size_pretty(
        pg_relation_size(s.indexrelid)
    ) AS index_size,
    pg_relation_size(s.indexrelid) AS index_size_bytes,
    i.indisprimary AS is_primary,
    i.indisunique AS is_unique,
    i.indisexclusion AS is_exclusion,
    i.indisreplident AS is_replica_identity,
    i.indisvalid AS is_valid,
    c.conname AS constraint_name,
    c.contype AS constraint_type
FROM pg_stat_user_indexes AS s
JOIN pg_index AS i
  ON i.indexrelid = s.indexrelid
LEFT JOIN pg_constraint AS c
  ON c.conindid = s.indexrelid
WHERE s.idx_scan = 0
ORDER BY index_size_bytes DESC;

idx_scan = 0은 “만든 뒤 한 번도 사용되지 않았다”는 뜻이 아닙니다. 통계가 마지막으로 초기화된 뒤 관측된 인덱스 스캔이 없었다는 의미입니다. 관측 기간은 다음 쿼리로 확인합니다.

SELECT stats_reset
FROM pg_stat_database
WHERE datname = current_database();

이 시각은 데이터베이스 전체 통계의 마지막 초기화 시점을 보여주는 참고값입니다. 개별 객체 통계만 별도로 초기화했을 가능성까지 증명하지는 못하므로, 중요한 삭제 판단에는 외부 모니터링으로 장기간 수집한 시계열을 사용하는 편이 안전합니다. 관측 기간을 새로 만들겠다는 이유로 운영 DB에서 pg_stat_reset()을 실행하면 autovacuum과 ANALYZE가 참고하는 카운터까지 초기화될 수 있으므로 피해야 합니다.

인덱스를 삭제하기 전에 최소한 다음을 확인해야 합니다.

• 통계 수집 기간이 충분히 긴가?
• 월말·분기·연말 배치에서만 사용하는 인덱스는 아닌가?
• 기본 키, UNIQUE, EXCLUDE 같은 제약조건을 뒷받침하는가?
• 읽기 복제본에서만 주로 사용하는 인덱스는 아닌가?
• 비슷한 다른 인덱스와 실제로 중복되는가?
• 삭제 후 쓰기 비용과 읽기 성능을 모두 재측정했는가?

인덱스 사용 여부는 누적 통계만 보지 말고 실제 쿼리의 EXPLAIN 결과와 함께 판단해야 합니다. PostgreSQL도 인덱스 사용 점검 문서에서 최신 통계와 실제 워크로드를 함께 보도록 안내합니다.

9. Dead Tuple과 VACUUM 상태 확인

SELECT
    schemaname,
    relname AS table_name,
    n_live_tup,
    n_dead_tup,
    round(
        100.0 * n_dead_tup
        / nullif(n_live_tup + n_dead_tup, 0),
        2
    ) AS estimated_dead_tuple_ratio_pct,
    n_mod_since_analyze,
    last_vacuum,
    last_autovacuum,
    last_analyze,
    last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;

n_live_tup과 n_dead_tup은 정확한 실시간 행 수가 아니라 추정치입니다. Dead Tuple 비율이 10% 또는 20%를 넘었다는 이유만으로 무조건 수동 VACUUM을 실행하는 고정 규칙은 권장하기 어렵습니다. 테이블 크기, 변경량, autovacuum 임계값, 오래 열린 트랜잭션, 마지막 VACUUM 시각을 함께 봐야 합니다.

일반 VACUUM은 정리된 공간을 테이블 내부에서 다시 사용할 수 있게 하지만 대부분 운영체제에 즉시 반환하지 않습니다. VACUUM FULL은 테이블을 다시 써서 파일을 줄일 수 있지만 ACCESS EXCLUSIVE 락과 추가 디스크 공간이 필요하므로 일상적인 청소 수단으로 사용하면 안 됩니다. 자세한 차이는 PostgreSQL 정기 VACUUM 문서에 잘 설명되어 있습니다.

수동 조치가 필요하다고 판단했다면 먼저 일반 VACUUM과 통계 갱신을 검토합니다.

VACUUM (ANALYZE) app.orders;

이 명령도 상당한 I/O를 만들 수 있으므로 큰 테이블에서는 부하가 낮은 시간과 autovacuum 상태를 확인한 뒤 실행해야 합니다.

10. 캐시 적중률은 참고 지표로만 보기

SELECT
    datname,
    blks_read,
    blks_hit,
    round(
        100.0 * blks_hit
        / nullif(blks_hit + blks_read, 0),
        2
    ) AS shared_buffer_hit_ratio_pct,
    stats_reset
FROM pg_stat_database
WHERE datname = current_database();

이 값은 PostgreSQL의 shared buffer에서 읽기를 피한 비율입니다. 운영체제 페이지 캐시에 이미 있던 데이터와 실제 물리 디스크 읽기를 구분하지 못합니다. 따라서 “무조건 99% 이상이어야 정상” 같은 절대 기준으로 판단하면 안 됩니다. 통계 초기화 시각, 실제 디스크 지연, pg_stat_io, 운영체제 I/O 지표를 함께 봐야 합니다. PostgreSQL 공식 문서도 통계 뷰와 운영체제 도구를 조합해 보도록 안내합니다.

11. pg_stat_statements로 누적 부하 SQL 찾기

pg_stat_activity는 지금 실행 중인 SQL을 보여주지만, 이미 끝난 쿼리의 누적 통계는 보여주지 않습니다. 반복적으로 서버 자원을 많이 쓰는 SQL을 찾으려면 pg_stat_statements가 유용합니다.

이 모듈은 서버 시작 전에 shared_preload_libraries에 추가해야 하므로 일반적으로 설정 변경과 재시작이 필요합니다. 쿼리 식별자 계산도 활성화되어야 하며, 현재 버전에서는 compute_query_id = auto 또는 on을 사용할 수 있습니다. 그다음 사용할 데이터베이스마다 확장을 생성합니다.

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

관리형 PostgreSQL에서는 콘솔의 파라미터 그룹이나 제공업체별 확장 활성화 절차를 따라야 합니다. 기존 shared_preload_libraries 값을 통째로 덮어쓰지 않도록 주의합니다. 설치 방식과 필수 설정은 pg_stat_statements 공식 문서를 확인하는 것이 가장 정확합니다.

전체 누적 부하가 큰 SQL 상위 20개

SELECT
    queryid,
    calls,
    round(total_exec_time::numeric, 2) AS total_exec_time_ms,
    round(mean_exec_time::numeric, 2) AS mean_exec_time_ms,
    rows,
    shared_blks_hit,
    shared_blks_read,
    temp_blks_written,
    left(query, 1000) AS query
FROM pg_stat_statements
WHERE dbid = (
    SELECT oid
    FROM pg_database
    WHERE datname = current_database()
)
ORDER BY total_exec_time DESC
LIMIT 20;

mean_exec_time만 보면 드물게 실행되는 매우 느린 쿼리는 잘 찾을 수 있지만, 짧아도 초당 수천 번 호출되는 SQL의 총부하는 놓칠 수 있습니다. 운영 최적화 우선순위는 보통 total_exec_time, calls, 평균 시간, 읽은 블록, 임시 파일 사용량을 함께 비교해서 정합니다.

pg_stat_statements는 개별 실행 이력을 시간순으로 저장하는 로그가 아닙니다. 정규화된 SQL별 누적 통계이며, 통계가 초기화되거나 최대 엔트리를 넘어가면 정보가 사라질 수 있습니다.

SELECT
    stats_reset,
    dealloc
FROM pg_stat_statements_info;

“지난 10분”이나 “어제 오후 3시부터 4시”처럼 기간별 분석이 필요하면 모니터링 시스템이 일정 주기로 이 값을 수집해 차이를 계산하거나, 느린 쿼리 로그를 별도로 보관해야 합니다.

12. EXPLAIN과 EXPLAIN ANALYZE의 차이

실행하지 않고 예상 계획만 확인하려면 다음처럼 사용합니다.

EXPLAIN (FORMAT JSON)
SELECT *
FROM app.orders
WHERE customer_id = 100;

실제 실행 통계와 버퍼 사용량까지 확인하려면 통제된 환경에서 다음과 같이 실행합니다.

EXPLAIN (
    ANALYZE,
    BUFFERS,
    SETTINGS,
    FORMAT JSON
)
SELECT *
FROM app.orders
WHERE customer_id = 100;

FORMAT JSON은 텍스트 들여쓰기를 직접 파싱할 필요 없이 실행 계획을 트리 구조로 다룰 수 있게 합니다. Plans 배열에 하위 노드가 중첩되므로 프론트엔드에서 트리 UI를 만들거나 pev2 같은 뷰어에 연결하기도 좋습니다.

EXPLAIN ANALYZE는 실제 SQL을 실행한다

이 차이가 가장 중요합니다.

• EXPLAIN: 계획만 생성합니다.
• EXPLAIN ANALYZE: SQL을 실제로 실행한 뒤 측정값을 보여줍니다.

PostgreSQL 공식 문서도 ANALYZE 옵션을 사용하면 데이터 변경과 기타 부작용이 그대로 발생한다고 경고합니다. DML을 BEGIN과 ROLLBACK으로 감싸는 방법이 소개되어 있지만, 시퀀스나 외부 시스템을 호출하는 함수처럼 롤백되지 않는 부작용도 있을 수 있습니다. 운영 환경에서는 먼저 일반 EXPLAIN을 사용하고, 필요한 경우 읽기 전용 역할·읽기 전용 트랜잭션·짧은 타임아웃이 적용된 통제된 경로에서만 EXPLAIN ANALYZE를 허용해야 합니다. 자세한 옵션과 경고는 EXPLAIN 공식 문서에 나와 있습니다.

실행 계획을 읽을 때 볼 항목

1. 예상 행 수와 실제 행 수

Plan Rows와 Actual Rows × Actual Loops의 차이가 크면 통계가 오래됐거나, 컬럼 간 상관관계 때문에 플래너가 선택도를 잘못 추정했을 수 있습니다. 무조건 VACUUM ANALYZE부터 실행하기보다 마지막 ANALYZE 시각과 데이터 분포를 확인하고, 필요하면 컬럼 통계 수준이나 확장 통계를 검토합니다.

2. loops

반복 실행되는 하위 노드의 actual time과 rows는 루프 한 번당 평균입니다. 전체 작업량을 이해하려면 loops를 곱해 봐야 합니다. 특히 Nested Loop 안쪽에서 큰 스캔이 수천 번 반복되는 패턴을 주의합니다.

3. Rows Removed by Filter

많은 행을 읽은 뒤 대부분을 버린다면 인덱스 조건이 충분히 적용되지 않았거나 조건식이 인덱스를 사용하기 어려운 형태일 수 있습니다. 다만 작은 테이블이나 결과 대부분을 읽는 쿼리에서는 순차 스캔이 더 효율적일 수 있습니다.

4. 버퍼와 임시 파일

Shared Read Blocks가 크면 PostgreSQL 버퍼에 없던 블록을 많이 요청한 것입니다. Temp Read Blocks, Temp Written Blocks가 크면 정렬이나 해시가 메모리를 넘겨 임시 파일을 사용했을 가능성이 있습니다.

5. 스캔 종류를 절대 등급으로 판단하지 않기

Seq Scan이 항상 나쁘고 Index Only Scan이 항상 최선인 것은 아닙니다. 테이블이 작거나 결과 비율이 높으면 Seq Scan이 더 빠를 수 있습니다. Index Only Scan도 Visibility Map 상태에 따라 실제 테이블을 다시 읽는 Heap Fetches가 많아질 수 있습니다. 핵심은 스캔 이름이 아니라 실제 행 수, 반복 횟수, 버퍼 읽기, 전체 실행 시간입니다.

6. 실행 순서를 지나치게 단순화하지 않기

들여쓰기는 부모와 자식 관계를 보여줍니다. 자식 노드가 부모에 행을 공급하지만, 실행기는 필요할 때 하위 노드에서 행을 끌어오는 방식으로 동작하므로 “가장 안쪽부터 아래에서 위로 한 번씩 실행된다”는 고정 순서로 이해하면 틀릴 수 있습니다. 루프와 병렬 실행도 함께 봐야 합니다.

또한 상위 노드의 시간과 버퍼 값은 하위 노드 작업을 포함할 수 있으므로 모든 노드의 시간을 단순 합산하면 중복 계산됩니다.

13. Spring Boot에서 EXPLAIN JSON 받기

JdbcTemplate.queryForList()로도 결과를 받을 수 있지만 EXPLAIN (FORMAT JSON)은 한 행·한 컬럼을 반환합니다. 서버의 컬럼 타입은 json이므로 pgJDBC에서 getObject()를 호출하면 String이 아니라 PGobject가 나올 수 있습니다. 명시적으로 getString(1)을 사용하면 이 차이에 덜 의존합니다.

가장 안전한 형태는 외부에서 SQL 원문을 받지 않고, 서버가 소유한 고정 SQL 템플릿과 바인드 값만 사용하는 것입니다.

private static final String ORDERS_BY_CUSTOMER = """
    SELECT id, created_at, total_amount
    FROM app.orders
    WHERE customer_id = ?
    ORDER BY created_at DESC
    LIMIT 500
    """;

String explainSql =
    "EXPLAIN (FORMAT JSON) " + ORDERS_BY_CUSTOMER;

String planJson = jdbcTemplate.query(
    connection -> {
        PreparedStatement statement =
            connection.prepareStatement(explainSql);
        statement.setLong(1, customerId);
        return statement;
    },
    resultSet -> {
        if (!resultSet.next()) {
            throw new IllegalStateException(
                "EXPLAIN 결과가 없습니다."
            );
        }

        String json = resultSet.getString(1);

        if (resultSet.next()) {
            throw new IllegalStateException(
                "EXPLAIN 결과가 한 행을 초과했습니다."
            );
        }

        return json;
    }
);

JsonNode plan = objectMapper.readTree(planJson);

컬럼 이름인 QUERY PLAN을 문자열로 직접 참조하는 것보다 첫 번째 컬럼을 읽으면 대소문자 매핑이나 Map 구현 차이에 덜 의존합니다. 최상위 JSON은 객체가 아니라 배열이므로 실제 루트 계획은 보통 plan.path(0).path("Plan")에서 시작합니다.

하지만 validatedSql을 만드는 과정이 가장 중요합니다.

14. SELECT·WITH·EXPLAIN 문자열 검사만으로는 안전하지 않다

다음 같은 코드는 입력값 검증에는 편해 보여도 보안 경계로 사용할 수 없습니다.

String normalized = sql.strip().toUpperCase(Locale.ROOT);

boolean allowed = normalized.startsWith("SELECT")
    || normalized.startsWith("WITH")
    || normalized.startsWith("EXPLAIN");

정규식을 조금 더 복잡하게 만들어도 근본적인 문제는 남습니다.

• WITH 안에는 INSERT, UPDATE, DELETE가 들어갈 수 있습니다.
• SELECT는 데이터를 변경하거나 외부 부작용을 만드는 함수를 호출할 수 있습니다.
• SELECT INTO는 새 테이블을 만들 수 있습니다.
• SELECT ... FOR UPDATE는 행 락을 획득합니다.
• 주석과 여러 SQL 문장으로 문자열 검사를 우회할 수 있습니다.
• pg_sleep, 거대한 재귀 CTE, 카테시안 조인 등은 데이터를 변경하지 않아도 서비스 거부를 일으킬 수 있습니다.
• EXPLAIN ANALYZE 뒤에는 SELECT뿐 아니라 DML과 여러 실행 가능한 문장이 올 수 있습니다.

따라서 안전성은 한 겹이 아니라 여러 계층으로 만들어야 합니다.

가장 안전한 순서

1. 가능하면 사용자가 임의 SQL을 보내지 못하게 하고, 서버에 등록된 쿼리 ID만 선택하게 합니다.
2. 임의 SQL이 꼭 필요하면 PostgreSQL 문법을 이해하는 파서로 정확히 한 문장인지 확인합니다.
3. 최상위 문장뿐 아니라 모든 CTE와 락 절을 검사합니다.
4. 전용 읽기 계정에 필요한 스키마·뷰의 SELECT 권한만 줍니다.
5. 읽기 전용 트랜잭션을 데이터베이스에서 강제로 적용합니다.
6. statement·lock·transaction 타임아웃과 최대 반환 행 수를 제한합니다.
7. 일반 사용자가 EXPLAIN ANALYZE를 켤 수 없게 하고 운영자 전용 기능으로 분리합니다.
8. 요청자, SQL 해시, 실행 시간, 반환 행 수, 실패 사유를 감사 로그로 남깁니다.

파서는 좋은 사전 검증 수단이지만 최종 보안 경계는 아닙니다. 사용자 정의 함수와 권한까지 완벽하게 판정하기 어렵기 때문에 데이터베이스 역할과 서버 측 읽기 전용 설정이 반드시 함께 있어야 합니다.

15. 전용 PostgreSQL 역할에 방어선 만들기

실제 객체명과 권한 범위는 환경에 맞춰야 하지만 방향은 다음과 같습니다.

GRANT CONNECT ON DATABASE appdb TO monitor_reader;
GRANT USAGE ON SCHEMA app TO monitor_reader;

-- 전체 테이블보다 허용된 테이블이나 뷰만 명시하는 편이 안전하다.
GRANT SELECT ON
    app.orders,
    app.customers
TO monitor_reader;

ALTER ROLE monitor_reader
    SET default_transaction_read_only = on;

ALTER ROLE monitor_reader
    SET statement_timeout = '15s';

ALTER ROLE monitor_reader
    SET lock_timeout = '2s';

ALTER ROLE monitor_reader
    SET idle_in_transaction_session_timeout = '30s';

READ ONLY는 일반 테이블에 대한 DML과 많은 변경 명령을 차단하지만 모든 디스크 쓰기나 모든 외부 부작용을 막는 절대 샌드박스는 아닙니다. 전용 역할의 객체 권한, 함수 EXECUTE 권한, search_path, 스키마 생성 권한도 함께 점검해야 합니다. PostgreSQL은 새 함수에 대한 EXECUTE를 기본적으로 PUBLIC에 부여하므로, 민감한 함수가 있는 환경에서는 기존 권한과 기본 권한 정책을 감사해야 합니다. 이 기본 권한과 회수 방법은 CREATE FUNCTION 공식 문서에 설명되어 있습니다.

다른 사용자의 SQL 전문을 보기 위해 pg_read_all_stats를 부여하면 쿼리에 포함된 개인정보나 토큰까지 노출될 수 있습니다. 모니터링 화면의 접근 권한과 마스킹 정책도 필요합니다. 세션 종료용 pg_signal_backend는 별도의 운영자 역할에만 부여합니다.

16. Spring의 읽기 전용 트랜잭션을 실제 DB까지 강제하기

@Transactional(readOnly = true)는 프레임워크와 드라이버에 읽기 전용 의도를 전달하지만, 구성에 따라 데이터베이스가 강제하는 SET TRANSACTION READ ONLY까지 실행되지 않을 수 있습니다. DataSourceTransactionManager를 사용할 때 enforceReadOnly를 활성화하면 PostgreSQL 연결에 읽기 전용 트랜잭션을 명시적으로 적용할 수 있습니다.

@Configuration
public class MonitoringDatabaseConfig {

    @Bean
    public DataSourceTransactionManager monitoringTxManager(
            @Qualifier("monitoringDataSource") DataSource dataSource) {

        DataSourceTransactionManager manager =
            new DataSourceTransactionManager(dataSource);

        manager.setEnforceReadOnly(true);
        manager.setValidateExistingTransaction(true);
        return manager;
    }

    @Bean
    public JdbcTemplate monitoringJdbcTemplate(
            @Qualifier("monitoringDataSource") DataSource dataSource) {

        JdbcTemplate template = new JdbcTemplate(dataSource);
        template.setQueryTimeout(15);
        template.setMaxRows(1000);
        template.setFetchSize(200);
        return template;
    }
}

Spring의 JdbcTemplate은 setQueryTimeout, setMaxRows, setFetchSize를 제공하며 트랜잭션 타임아웃이 있으면 남은 트랜잭션 시간이 쿼리 타임아웃에 반영됩니다. 자세한 동작은 JdbcTemplate 공식 API와 DataSourceTransactionManager 공식 API를 참고할 수 있습니다.

서비스 코드는 일반 EXPLAIN과 실제 실행을 포함하는 분석 기능을 분리하는 편이 좋습니다.

@Service
@RequiredArgsConstructor
public class ExplainPlanService {

    private final JdbcTemplate monitoringJdbcTemplate;
    private final ObjectMapper objectMapper;
    private final PostgreSqlReadOnlyValidator sqlValidator;

    @Transactional(
        transactionManager = "monitoringTxManager",
        readOnly = true,
        timeout = 20
    )
    public JsonNode explain(String originalSql)
            throws JsonProcessingException {

        String sql = originalSql.strip();

        // 문법 인식 파서로 단일 읽기 문장과 전체 CTE를 검사한다.
        // 이 검사는 DB 역할·READ ONLY·타임아웃을 대체하지 않는다.
        sqlValidator.requireSingleReadOnlySelect(sql);

        String explainSql =
            "EXPLAIN (FORMAT JSON) " + sql;

        String planJson = monitoringJdbcTemplate.query(
            connection -> connection.prepareStatement(explainSql),
            resultSet -> {
                if (!resultSet.next()) {
                    throw new IllegalStateException(
                        "EXPLAIN 결과가 없습니다."
                    );
                }

                String json = resultSet.getString(1);

                if (resultSet.next()) {
                    throw new IllegalStateException(
                        "EXPLAIN 결과가 한 행을 초과했습니다."
                    );
                }

                return json;
            }
        );

        return objectMapper.readTree(planJson);
    }
}

PostgreSqlReadOnlyValidator는 단순 정규식이 아니라 PostgreSQL 문법을 이해하는 파서 기반 구성 요소를 뜻합니다. 사용하는 SQL 기능과 라이브러리가 다르므로 특정 구현 API를 그대로 가정하기보다 다음 조건을 테스트로 고정하는 것이 중요합니다.

• 앞뒤 공백과 주석이 있어도 정확히 처리한다.
• 여러 문장을 거부한다.
• 최상위 SELECT 한 문장만 허용한다.
• 데이터 변경 CTE를 거부한다.
• SELECT INTO를 거부한다.
• FOR UPDATE, FOR NO KEY UPDATE, FOR SHARE, FOR KEY SHARE를 거부한다.
• 허용하지 않은 함수와 스키마를 거부하거나 데이터베이스 권한으로 실행을 막는다.
• 파서가 이해하지 못하는 문장은 허용하지 않는 방향으로 실패한다.

EXPLAIN ANALYZE 기능이 꼭 필요하다면 별도의 운영자 전용 메서드와 엔드포인트로 분리하고, 같은 읽기 전용 역할과 짧은 타임아웃을 적용합니다.

String explainAnalyzeSql = """
    EXPLAIN (
        ANALYZE,
        BUFFERS,
        SETTINGS,
        FORMAT JSON
    )
    """ + validatedSql;

인터넷에 노출된 일반 사용자용 SQL 실행 화면에서 ANALYZE 옵션을 자유롭게 켜게 하는 설계는 피하는 것이 좋습니다.

노드별 시계 측정 오버헤드가 큰 환경에서 실제 행 수와 전체 실행 시간만 필요하다면 TIMING FALSE를 검토할 수 있습니다. 정밀한 노드별 시간이 필요할 때만 기본값인 TIMING TRUE로 측정합니다.

17. 장애 상황별 빠른 점검 순서

API 전체가 갑자기 느려졌을 때

1. pg_stat_activity에서 장기 쿼리와 wait_event를 확인합니다.
2. pg_blocking_pids()로 락 차단 관계를 확인합니다.
3. 현재 커넥션 수와 idle in transaction을 확인합니다.
4. 운영체제 CPU·메모리·디스크 지연과 PostgreSQL I/O를 함께 봅니다.
5. 최근 배포, 스키마 변경, 통계 변화 여부를 확인합니다.

특정 기능만 계속 느릴 때

1. 애플리케이션의 실제 바인딩 값과 SQL을 확인합니다.
2. pg_stat_statements에서 호출 수와 총 실행 시간을 확인합니다.
3. 일반 EXPLAIN으로 예상 계획을 확인합니다.
4. 재현 가능한 환경에서 EXPLAIN ANALYZE와 버퍼 정보를 확인합니다.
5. 인덱스 추가 전에 예상 행 수 오류, 필터 제거 행 수, 반복 횟수, 임시 파일을 먼저 봅니다.

디스크 사용량이 계속 증가할 때

1. 데이터베이스와 테이블별 총용량을 확인합니다.
2. Dead Tuple과 마지막 autovacuum 시각을 확인합니다.
3. 오래 열린 트랜잭션과 idle in transaction을 확인합니다.
4. replication slot과 WAL 보관량도 함께 확인합니다.
5. VACUUM FULL은 원인과 유지보수 시간을 확정한 뒤 마지막 수단으로 검토합니다.

18. 자주 묻는 질문

now() - query_start가 실제 수행 시간인가요?

쿼리 시작 뒤의 전체 경과 시간입니다. CPU 사용 시간뿐 아니라 락·I/O·네트워크 대기가 포함됩니다. 오래 열린 트랜잭션 안에서 조회할 가능성이 있다면 clock_timestamp()를 사용하는 편이 정확합니다.

락 대기 시간은 어떻게 구하나요?

query_start가 아니라 pg_locks에서 granted = false인 행의 waitstart를 사용합니다. 차단 PID 판별은 직접 복잡한 락 충돌 조인을 만들기보다 pg_blocking_pids()를 사용하는 것이 좋습니다.

idx_scan = 0이면 인덱스를 삭제해도 되나요?

아닙니다. 통계 초기화 이후 관측된 스캔이 없다는 뜻일 뿐입니다. 제약조건, 희귀 배치, 읽기 복제본 사용, 관측 기간, 실제 실행 계획을 모두 확인해야 합니다.

VACUUM을 실행하면 디스크가 바로 줄어드나요?

일반 VACUUM은 공간을 내부 재사용 가능 상태로 만들지만 보통 운영체제에 반환하지 않습니다. VACUUM FULL은 파일을 줄일 수 있지만 테이블 전체 잠금과 추가 공간이 필요합니다.

EXPLAIN ANALYZE SELECT는 항상 안전한가요?

아닙니다. SELECT 안에서도 데이터 변경 CTE나 부작용이 있는 함수를 실행할 수 있고, 큰 쿼리는 실제 부하를 발생시킵니다. 읽기 전용 역할·트랜잭션·타임아웃과 운영자 권한 분리가 필요합니다.

startsWith("SELECT") 검사면 충분하지 않나요?

충분하지 않습니다. 문법 구조, 모든 CTE, 락 절, 함수 호출, 여러 문장을 분석하지 못합니다. 파서 기반 검증을 사용하더라도 데이터베이스 권한과 읽기 전용 설정을 최종 방어선으로 둬야 합니다.

마무리

PostgreSQL 장애 대응의 핵심은 “느리다”는 현상을 바로 튜닝 문제로 단정하지 않는 것입니다. 같은 장기 쿼리라도 CPU를 쓰는 쿼리, 디스크를 기다리는 쿼리, 다른 트랜잭션의 락에 막힌 쿼리는 해결 방법이 전혀 다릅니다.

먼저 pg_stat_activity로 현재 상태를 보고, 락·커넥션·트랜잭션을 확인한 다음 누적 통계와 실행 계획으로 내려가야 합니다. 인덱스 삭제, 세션 종료, VACUUM FULL, EXPLAIN ANALYZE처럼 영향이 큰 조치는 관측과 원인 확인이 끝난 뒤에만 사용해야 합니다.

Spring Boot에 이 기능을 넣을 때도 문자열 검사를 보안 장치로 믿지 말고, 전용 읽기 계정, 데이터베이스 강제 읽기 전용 트랜잭션, 타임아웃, 최대 행 수, 문법 파서, 접근 제어와 감사 로그를 겹쳐 적용해야 운영 환경에서 사용할 수 있는 모니터링 도구가 됩니다.

참고한 공식 문서

• PostgreSQL 통계 모니터링과 pg_stat_activity
• PostgreSQL pg_locks
• PostgreSQL 시스템 정보 함수와 pg_blocking_pids
• PostgreSQL 관리 함수와 세션 취소·종료
• PostgreSQL 정기 VACUUM
• PostgreSQL pg_stat_statements
• PostgreSQL EXPLAIN
• PostgreSQL 트랜잭션 READ ONLY
• PostgreSQL CREATE FUNCTION 권한 주의사항
• Spring JdbcTemplate API
• Spring DataSourceTransactionManager API