[DB 내부]  pg_stat_* 뷰가 연결수·커밋·락·버퍼 통계를 실시간 노출
   │
   ① 노출         DB가 pg_stat_database·pg_stat_activity·pg_locks 등으로 지표 제공
   │
   ② 수집         postgres_exporter가 그 뷰를 조회해 Prometheus 형식으로 변환(:9187)
   │
   ③ 저장·집계     Prometheus가 scrape_interval마다 긁어 시계열로 저장(rate로 추세 계산)
   │
   ④ 시각화·경보   Grafana가 PromQL로 패널을 그리고, 규칙이 임계 초과 시 알람 발사
   │
   ⑤ 추적         경보를 받은 담당자가 pg_stat_statements·pg_locks로 범인 쿼리·락 확인
   ▼
[대응]  느린 쿼리 최적화 / 락을 쥔 세션 정리

# postgresql.conf 수정
# Docker 환경이면 postgres 컨테이너 셸 진입 후 또는 environment 변수로 설정
echo "shared_preload_libraries = 'pg_stat_statements'" >> /etc/postgresql/15/main/postgresql.conf
echo "pg_stat_statements.track = all" >> /etc/postgresql/15/main/postgresql.conf

# PostgreSQL 재시작
sudo systemctl restart postgresql

# 또는 Docker에서
docker run -e POSTGRES_PASSWORD=secret \
  -e POSTGRES_INITDB_ARGS="--encoding=UTF8" \
  postgres:15 \
  -c shared_preload_libraries=pg_stat_statements \
  -c pg_stat_statements.track=all

-- 데이터베이스에 확장 설치 (한 번만)
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- 설치 확인
SELECT * FROM pg_extension WHERE extname = 'pg_stat_statements';

-- 총 실행 시간 기준 (DB 자원을 가장 많이 소비한 쿼리)
SELECT
  LEFT(query, 80)           AS query_snippet,
  calls,
  ROUND(total_exec_time)    AS total_ms,
  ROUND(total_exec_time / calls) AS avg_ms,
  ROUND(rows::numeric / calls, 1) AS avg_rows,
  ROUND(100.0 * total_exec_time /
    SUM(total_exec_time) OVER (), 2) AS pct_total
FROM pg_stat_statements
WHERE calls > 100
ORDER BY total_exec_time DESC
LIMIT 15;

-- 통계 초기화 (분석 사이클을 새로 시작할 때)
SELECT pg_stat_statements_reset();

# 초당 쿼리 수 (커밋+롤백 기준)
rate(pg_stat_database_xact_commit{datname="appdb"}[5m])
+ rate(pg_stat_database_xact_rollback{datname="appdb"}[5m])

# 현재 활성 연결 수
pg_stat_activity_count{datname="appdb", state="active"}

# 최대 연결 수 대비 사용률 (%)
100 * pg_stat_activity_count{datname="appdb"}
    / pg_settings_max_connections

# 캐시 히트율 (%) — 데이터를 디스크가 아닌 메모리에서 읽은 비율
100 * pg_stat_database_blks_hit{datname="appdb"}
    / (pg_stat_database_blks_hit{datname="appdb"}
       + pg_stat_database_blks_read{datname="appdb"})

# prometheus/rules/postgres.yml
groups:
  - name: postgres_alerts
    rules:
      # 커넥션 포화 경고
      - alert: PostgresHighConnections
        expr: |
          pg_stat_activity_count{datname="appdb"}
          / pg_settings_max_connections > 0.7
        for: 2m
        labels:
          severity: warning
        annotations:
          summary: "PostgreSQL 커넥션 사용률 70% 초과"
          description: "현재 커넥션 수가 max_connections의 {{ $value | humanizePercentage }} 수준입니다."

      # 캐시 히트율 저하
      - alert: PostgresLowCacheHitRate
        expr: |
          100 * pg_stat_database_blks_hit{datname="appdb"}
              / (pg_stat_database_blks_hit{datname="appdb"}
                 + pg_stat_database_blks_read{datname="appdb"} + 1) < 90
        for: 5m
        labels:
          severity: warning
        annotations:
          summary: "PostgreSQL 캐시 히트율 90% 미만"
          description: "캐시 히트율이 {{ $value | humanize }}%입니다. shared_buffers 증가 또는 쿼리 최적화가 필요합니다."

      # 데드락 발생
      - alert: PostgresDeadlockDetected
        expr: |
          rate(pg_stat_database_deadlocks{datname="appdb"}[5m]) > 0
        for: 1m
        labels:
          severity: critical
        annotations:
          summary: "PostgreSQL 데드락 감지"
          description: "분당 {{ $value | humanize }}건의 데드락이 발생하고 있습니다."