PostgreSQL 운영 대시보드: 핵심 지표와 알림 임계값

무엇을 측정할 것인가

PostgreSQL은 운영 상태를 pg_stat_* 뷰 그룹에 상세히 노출한다. 뷰를 읽는 것 자체는 어렵지 않다. 어려운 점은 수십 개의 뷰와 수백 개의 컬럼 중에서 지금 이 순간 중요한 신호를 빠르게 찾는 것이다.

운영 대시보드는 다섯 가지 질문에 답해야 한다.

1. 커넥션이 고갈될 위험이 있는가?
2. 캐시가 효율적으로 동작하는가?
3. 체크포인트와 WAL 쓰기 압력이 정상인가?
4. 복제가 지연되고 있는가?
5. 잠금 대기와 bloat가 쌓이고 있는가?

이 챕터는 각 질문에 대응하는 뷰, SQL, prometheus-community/postgres_exporter 메트릭, 그리고 현장에서 검증된 알림 임계값을 정리한다.

커넥션과 활성도: pg_stat_activity

pg_stat_activity는 현재 연결된 모든 백엔드 프로세스의 상태를 보여준다.

SELECT
  datname,
  state,
  count(*) AS cnt,
  max(EXTRACT(EPOCH FROM (now() - query_start)))::int AS longest_query_sec,
  max(EXTRACT(EPOCH FROM (now() - xact_start)))::int  AS longest_xact_sec
FROM pg_stat_activity
WHERE datname IS NOT NULL
GROUP BY datname, state
ORDER BY cnt DESC;

상태별 의미:

idle in transaction 상태가 5분 이상 지속되는 세션은 VACUUM을 막고 잠금을 보유하며 커넥션 슬롯을 낭비한다. 즉각 알림 대상이다. idle_in_transaction_session_timeout을 설정하면 PostgreSQL이 자동으로 해당 세션을 종료한다.

커넥션 포화도는 다음으로 계산한다.

SELECT
  count(*) AS current_connections,
  (SELECT setting::int FROM pg_settings WHERE name = 'max_connections') AS max_connections,
  round(100.0 * count(*) /
    (SELECT setting::int FROM pg_settings WHERE name = 'max_connections'), 1) AS utilization_pct
FROM pg_stat_activity;

캐시 효율: pg_statio_user_tables

캐시 히트율(Cache Hit Ratio)은 데이터를 디스크가 아닌 shared_buffers에서 얼마나 읽었는지를 나타낸다. OLTP 환경에서 95% 미만이면 shared_buffers 부족이거나 특정 쿼리가 캐시를 우회하는 것이다.

SELECT
  sum(heap_blks_hit) AS heap_hit,
  sum(heap_blks_read) AS heap_read,
  round(100.0 * sum(heap_blks_hit) /
    NULLIF(sum(heap_blks_hit) + sum(heap_blks_read), 0), 2) AS cache_hit_ratio,
  round(100.0 * sum(idx_blks_hit) /
    NULLIF(sum(idx_blks_hit) + sum(idx_blks_read), 0), 2) AS index_cache_hit_ratio
FROM pg_statio_user_tables;

캐시 히트율이 낮을 때 shared_buffers를 올리기 전에 pg_stat_statements로 대량의 shared_blks_read를 발생시키는 쿼리가 있는지 먼저 확인한다. 테이블 풀스캔이 캐시를 오염시키는 경우가 흔하다.

체크포인트와 WAL 쓰기: pg_stat_bgwriter / pg_stat_checkpointer

PostgreSQL 15까지는 pg_stat_bgwriter에 체크포인트 통계가 포함된다. PostgreSQL 16부터는 pg_stat_checkpointer 뷰가 분리됐다.

-- PostgreSQL 15 이하
SELECT
  checkpoints_timed,
  checkpoints_req,
  round(100.0 * checkpoints_req /
    NULLIF(checkpoints_timed + checkpoints_req, 0), 1) AS forced_pct,
  pg_size_pretty(buffers_checkpoint * 8192) AS checkpoint_io,
  round(checkpoint_write_time / 1000.0, 1) AS write_sec,
  round(checkpoint_sync_time / 1000.0, 1) AS sync_sec
FROM pg_stat_bgwriter;

-- PostgreSQL 16+
SELECT
  num_timed,
  num_requested,
  round(100.0 * num_requested /
    NULLIF(num_timed + num_requested, 0), 1) AS forced_pct,
  round(write_time / 1000.0, 1) AS write_sec,
  round(sync_time / 1000.0, 1) AS sync_sec
FROM pg_stat_checkpointer;

핵심 해석:

• checkpoints_req 비율이 10% 초과 → WAL 생성 속도가 checkpoint_timeout 안에 소화되지 않는다. max_wal_size를 늘리거나 checkpoint_timeout을 줄인다.
• checkpoint_write_time 30초 초과 → shared_buffers I/O 병목 가능성. dirty page가 너무 많이 쌓인 뒤 일시에 플러시된다.
• buffers_backend_fsync > 0 → 백엔드 프로세스가 직접 fsync를 수행한다. bgwriter 설정을 점검한다.

복제 지연: pg_stat_replication

SELECT
  application_name,
  client_addr,
  state,
  sync_state,
  EXTRACT(EPOCH FROM write_lag)::int  AS write_lag_sec,
  EXTRACT(EPOCH FROM flush_lag)::int  AS flush_lag_sec,
  EXTRACT(EPOCH FROM replay_lag)::int AS replay_lag_sec,
  pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) / 1024 / 1024 AS lag_mb
FROM pg_stat_replication
ORDER BY replay_lag_sec DESC NULLS LAST;

write_lag, flush_lag, replay_lag는 PostgreSQL 10에서 추가됐다. 세 값 중 어디서 지연이 큰지에 따라 원인이 다르다.

복제 슬롯 보존량도 별도로 확인한다. 슬롯 소비자가 오래 끊겨 있으면 pg_wal/ 디렉터리가 무한 증가한다.

SELECT
  slot_name,
  active,
  pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained_wal,
  wal_status   -- 'reserved' | 'extended' | 'unreserved' | 'lost'
FROM pg_replication_slots;

wal_status = 'lost'가 되면 해당 슬롯이 필요한 WAL을 이미 잃었다는 뜻이다. 슬롯을 삭제하고 스탠바이를 재동기화해야 한다.

잠금과 데드락

블로킹 쿼리 탐지 — pg_blocking_pids() 활용 (PostgreSQL 9.6+):

SELECT
  blocked_act.pid AS blocked_pid,
  LEFT(blocked_act.query, 80) AS blocked_query,
  blocking_act.pid AS blocking_pid,
  LEFT(blocking_act.query, 80) AS blocking_query,
  EXTRACT(EPOCH FROM (now() - blocked_act.query_start))::int AS waiting_sec
FROM pg_stat_activity blocked_act
JOIN pg_stat_activity blocking_act
  ON blocking_act.pid = ANY(pg_blocking_pids(blocked_act.pid))
WHERE blocked_act.wait_event_type = 'Lock'
ORDER BY waiting_sec DESC;

pg_blocking_pids() 함수는 특정 프로세스를 블로킹하는 PID 배열을 반환한다. 이 JOIN 패턴으로 블로킹 체인 전체를 한 번에 파악할 수 있다.

데드락은 pg_stat_database.deadlocks 컬럼으로 추적한다. 이 카운터는 누적값이므로 이전 측정값과의 차이를 시계열로 기록해야 의미가 있다.

SELECT datname, deadlocks, conflicts
FROM pg_stat_database
WHERE datname = current_database();

Autovacuum과 Bloat: pg_stat_user_tables

Autovacuum이 제때 실행되지 않으면 dead tuple이 쌓여 테이블이 부풀어 오른다. n_dead_tup / (n_live_tup + n_dead_tup)로 bloat을 근사한다.

SELECT
  schemaname,
  tablename,
  n_live_tup,
  n_dead_tup,
  round(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 1) AS dead_pct,
  last_autovacuum,
  autovacuum_count
FROM pg_stat_user_tables
WHERE n_live_tup > 10000
ORDER BY n_dead_tup DESC
LIMIT 20;

dead_pct > 10%인 테이블이 있으면 autovacuum이 따라잡지 못하는 것이다. autovacuum_vacuum_scale_factor를 낮추거나 autovacuum_vacuum_cost_delay를 줄인다.

인덱스가 전혀 사용되지 않는지도 확인한다. 사용되지 않는 인덱스는 DML 성능을 떨어뜨리고 bloat만 만든다.

SELECT
  schemaname,
  tablename,
  indexname,
  idx_scan,
  pg_size_pretty(pg_relation_size(indexrelid)) AS idx_size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND schemaname NOT IN ('pg_catalog', 'information_schema')
ORDER BY pg_relation_size(indexrelid) DESC;

워크로드 분석: pg_stat_statements

pg_stat_statements는 전체 쿼리 워크로드를 집계한다. 반드시 postgresql.conf에서 미리 활성화해야 한다.

shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.max = 10000
pg_stat_statements.track = 'all'

총 실행 시간 기준 상위 쿼리:

SELECT
  LEFT(query, 80) AS query_snippet,
  calls,
  round(mean_exec_time::numeric, 2) AS mean_ms,
  round((mean_exec_time * calls)::numeric, 0) AS total_ms,
  round(100.0 * shared_blks_hit /
    NULLIF(shared_blks_hit + shared_blks_read, 0), 1) AS cache_hit_pct
FROM pg_stat_statements
WHERE query NOT LIKE '%pg_stat_statements%'
ORDER BY mean_exec_time * calls DESC
LIMIT 20;

캐시 히트율이 낮은 쿼리를 특히 주목한다. 인덱스 미사용 풀스캔인지, 아니면 실제로 필요한 대용량 스캔인지 실행계획으로 확인한다. PostgreSQL 17부터는 pg_stat_statements에 jit_functions, temp_blk_read_time 등 추가 컬럼이 생겼다.

모니터링 아키텍처와 Prometheus 메트릭

postgres_exporter 주요 메트릭:

pg_up                                       # 연결 가능 여부
pg_stat_database_blks_hit                   # 캐시 히트 블록 수
pg_stat_database_blks_read                  # 디스크 읽기 블록 수
pg_stat_database_xact_commit                # 커밋 트랜잭션 누적 수
pg_stat_database_xact_rollback              # 롤백 트랜잭션 누적 수
pg_stat_database_deadlocks                  # 데드락 누적 수
pg_stat_bgwriter_checkpoints_timed          # 시간 기반 체크포인트 누적
pg_stat_bgwriter_checkpoints_req            # 강제 체크포인트 누적
pg_stat_replication_replay_lag_seconds      # 복제 재생 지연 (초)
pg_stat_activity_count                      # 상태별 커넥션 수
pg_database_size_bytes                      # 데이터베이스 크기

PromQL 알림 규칙 예시:

groups:
  - name: postgresql
    rules:
      - alert: PostgreSQLHighConnectionUtilization
        expr: |
          pg_stat_activity_count{state!=""}
            / on() pg_settings_max_connections > 0.80
        for: 2m
        labels:
          severity: warning

      - alert: PostgreSQLReplicationLagHigh
        expr: pg_stat_replication_replay_lag_seconds > 30
        for: 1m
        labels:
          severity: critical

      - alert: PostgreSQLHighForcedCheckpoints
        expr: |
          rate(pg_stat_bgwriter_checkpoints_req[10m]) /
          (rate(pg_stat_bgwriter_checkpoints_timed[10m]) +
           rate(pg_stat_bgwriter_checkpoints_req[10m])) > 0.50
        for: 10m
        labels:
          severity: warning

권장 알림 임계값

OLAP 환경 주의: 캐시 히트율 임계값은 OLAP 워크로드에서 90% 이하도 정상일 수 있다. 임계값을 적용하기 전에 워크로드 패턴을 먼저 파악한다.

운영 체크리스트

• pg_stat_statements가 활성화되어 있어 느린 쿼리를 쿼리 단위로 분석할 수 있는가?
• pg_stat_archiver.failed_count와 복제 지연에 알림이 설정되어 있는가?
• idle_in_transaction_session_timeout이 설정되어 방치된 트랜잭션을 자동으로 종료하는가?
• 슬롯 retained_wal이 디스크 여유 공간의 일정 비율을 초과하면 알림이 발생하는가?
• 체크포인트 강제 비율(checkpoints_req / 전체) 알림이 있는가?
• 대형 테이블의 dead tuple 비율을 일일 점검 쿼리나 대시보드로 확인하고 있는가?
• Grafana 대시보드가 프라이머리와 스탠바이를 구분해서 보여주는가?
• 통계가 초기화된 마지막 시점(stats_reset)을 알림 임계값 평가 시 고려하는가?
• PostgreSQL 버전을 올렸을 때 pg_stat_checkpointer 뷰 변경을 대시보드에 반영했는가?

References

• PostgreSQL Documentation, "Monitoring Database Activity" — https://www.postgresql.org/docs/current/monitoring-stats.html
• PostgreSQL Documentation, "pg_stat_activity" — https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-PG-STAT-ACTIVITY-VIEW
• PostgreSQL Documentation, "pg_stat_bgwriter / pg_stat_checkpointer" — https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-PG-STAT-BGWRITER-VIEW
• PostgreSQL Documentation, "pg_stat_replication" — https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-PG-STAT-REPLICATION-VIEW
• PostgreSQL Documentation, "pg_stat_statements" — https://www.postgresql.org/docs/current/pgstatstatements.html
• prometheus-community/postgres_exporter GitHub — https://github.com/prometheus-community/postgres_exporter
• Cybertec, "A Guide to PostgreSQL Monitoring" — https://www.cybertec-postgresql.com/en/a-guide-to-postgresql-monitoring/
• pganalyze Blog, "PostgreSQL Connection Monitoring" — https://pganalyze.com/blog/analyzing-and-improving-postgresql-connection-pool
• Percona Blog, "PostgreSQL Monitoring Best Practices" — https://www.percona.com/blog/postgresql-monitoring-best-practices/
• Grafana Labs, "PostgreSQL Dashboard" — https://grafana.com/grafana/dashboards/9628-postgresql-database/