PostgreSQL → postgres_exporter → Prometheus → Grafana

-- 현재 커넥션 상태
SELECT state, count(*)
FROM pg_stat_activity
GROUP BY state;

# PromQL - 커넥션 사용률
sum(pg_stat_activity_count) / pg_settings_max_connections * 100

-- 커밋/롤백 수
SELECT
    xact_commit as commits,
    xact_rollback as rollbacks
FROM pg_stat_database
WHERE datname = 'mydb';

# PromQL - TPS
rate(pg_stat_database_xact_commit{datname="mydb"}[5m])

-- 캐시 히트율
SELECT
    sum(heap_blks_hit) / nullif(sum(heap_blks_hit) + sum(heap_blks_read), 0) as ratio
FROM pg_statio_user_tables;

# PromQL - 캐시 히트율
sum(pg_statio_user_tables_heap_blks_hit) /
(sum(pg_statio_user_tables_heap_blks_hit) + sum(pg_statio_user_tables_heap_blks_read))

-- 테이블별 튜플 활동
SELECT
    relname,
    n_tup_ins as inserts,
    n_tup_upd as updates,
    n_tup_del as deletes
FROM pg_stat_user_tables
ORDER BY n_tup_ins + n_tup_upd + n_tup_del DESC
LIMIT 10;

-- Dead tuple 비율이 높은 테이블
SELECT
    relname,
    n_live_tup,
    n_dead_tup,
    round(n_dead_tup::numeric / nullif(n_live_tup + n_dead_tup, 0) * 100, 2) as dead_ratio
FROM pg_stat_user_tables
WHERE n_dead_tup > 0
ORDER BY dead_ratio DESC;

-- 현재 락 대기 상황
SELECT
    blocked_locks.pid AS blocked_pid,
    blocking_locks.pid AS blocking_pid,
    blocked_activity.query AS blocked_query
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_locks blocking_locks
    ON blocking_locks.locktype = blocked_locks.locktype
    AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database
    AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
    AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
    AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
    AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
    AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
    AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
    AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
    AND blocking_locks.pid != blocked_locks.pid
JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
WHERE NOT blocked_locks.granted;

-- Primary에서 확인
SELECT
    client_addr,
    state,
    sent_lsn,
    write_lsn,
    flush_lsn,
    replay_lsn,
    pg_wal_lsn_diff(sent_lsn, replay_lsn) as replication_lag_bytes
FROM pg_stat_replication;

# PromQL - WAL 생성률 (bytes/sec)
rate(pg_stat_archiver_archived_count[5m]) * avg(pg_stat_archiver_last_archived_wal)

-- 읽기 부하 시 확인할 지표
SELECT
    relname,
    seq_scan,           -- Sequential Scan 횟수 (인덱스 미사용)
    seq_tup_read,       -- Seq Scan으로 읽은 튜플 수
    idx_scan,           -- Index Scan 횟수
    idx_tup_fetch       -- Index Scan으로 가져온 튜플 수
FROM pg_stat_user_tables
ORDER BY seq_tup_read DESC
LIMIT 10;

-- 쓰기 부하 시 확인할 지표
SELECT
    relname,
    n_tup_ins,
    n_tup_upd,
    n_tup_del,
    n_tup_hot_upd,      -- HOT Update (인덱스 업데이트 없이 처리)
    n_dead_tup
FROM pg_stat_user_tables
ORDER BY n_tup_ins + n_tup_upd + n_tup_del DESC;

-- 락 경합 확인
SELECT
    relation::regclass,
    mode,
    count(*)
FROM pg_locks
WHERE granted = true
GROUP BY relation, mode
ORDER BY count DESC;

-- 현재 실행 중인 쿼리와 대기 상태
SELECT
    pid,
    now() - query_start as duration,
    wait_event_type,
    wait_event,
    state,
    left(query, 100) as query
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY duration DESC;

-- 기본값 없는 컬럼 추가: 즉시 완료 (메타데이터만 변경)
ALTER TABLE large_table ADD COLUMN new_col VARCHAR(100);

-- 기본값이 있는 컬럼 추가 (PostgreSQL 11+): 즉시 완료
ALTER TABLE large_table ADD COLUMN new_col VARCHAR(100) DEFAULT 'value';

-- PostgreSQL 10 이하: 전체 테이블 재작성 발생!

-- DDL 진행 상황 확인
SELECT
    pid,
    now() - xact_start as duration,
    wait_event_type,
    wait_event,
    query
FROM pg_stat_activity
WHERE query LIKE 'ALTER TABLE%';

-- 락 대기 확인
SELECT
    blocked.pid as blocked_pid,
    blocked.query as blocked_query,
    blocking.pid as blocking_pid,
    blocking.query as blocking_query
FROM pg_stat_activity blocked
JOIN pg_locks blocked_locks ON blocked.pid = blocked_locks.pid
JOIN pg_locks blocking_locks ON blocked_locks.relation = blocking_locks.relation
    AND blocked_locks.pid != blocking_locks.pid
JOIN pg_stat_activity blocking ON blocking_locks.pid = blocking.pid
WHERE NOT blocked_locks.granted;

-- 일반 인덱스 생성: AccessExclusiveLock (쓰기 차단)
CREATE INDEX idx_col ON large_table(column);

-- CONCURRENTLY 옵션: ShareUpdateExclusiveLock (쓰기 허용)
CREATE INDEX CONCURRENTLY idx_col ON large_table(column);

-- 인덱스 생성 진행률 (PostgreSQL 12+)
SELECT
    a.pid,
    p.phase,
    p.blocks_total,
    p.blocks_done,
    round(100.0 * p.blocks_done / nullif(p.blocks_total, 0), 2) as progress_pct,
    p.tuples_total,
    p.tuples_done
FROM pg_stat_progress_create_index p
JOIN pg_stat_activity a ON p.pid = a.pid;

# prometheus-rules.yml 추가
groups:
  - name: postgresql-ddl
    rules:
      - alert: PostgreSQLLongRunningDDL
        expr: |
          (time() - pg_stat_activity_query_start_time{query=~"ALTER TABLE.*|CREATE INDEX.*"}) > 300
        for: 1m
        labels:
          severity: warning
        annotations:
          summary: "DDL 작업 5분 이상 진행 중"

      - alert: PostgreSQLDDLBlockingQueries
        expr: pg_locks_waiting > 10
        for: 2m
        labels:
          severity: critical
        annotations:
          summary: "DDL로 인한 락 대기 쿼리 10개 초과"

-- 1. 락 타임아웃 설정 (너무 오래 대기하지 않도록)
SET lock_timeout = '5s';

-- 2. 짧은 트랜잭션에서 실행
BEGIN;
SET LOCAL lock_timeout = '5s';
ALTER TABLE large_table ADD COLUMN new_col VARCHAR(100);
COMMIT;

-- 3. 현재 활성 트랜잭션 확인 후 실행
SELECT count(*) FROM pg_stat_activity
WHERE state = 'active' AND query != '<IDLE>';

┌─────────────────────────────────────────────────────────┐
│  Overview: TPS, 커넥션 사용률, 캐시 히트율               │
├─────────────────────────────────────────────────────────┤
│  Connections          │  Transaction Rate              │
├───────────────────────┼────────────────────────────────┤
│  Cache Hit Ratio      │  Tuple Activity                │
├───────────────────────┼────────────────────────────────┤
│  Dead Tuples          │  Replication Lag               │
├─────────────────────────────────────────────────────────┤
│  Slow Queries (Top 10)                                  │
└─────────────────────────────────────────────────────────┘

# docker-compose.yml
services:
  postgres_exporter:
    image: prometheuscommunity/postgres-exporter
    environment:
      DATA_SOURCE_NAME: "postgresql://user:pass@postgres:5432/mydb?sslmode=disable"
    ports:
      - "9187:9187"

pg_slow_queries:
  query: |
    SELECT count(*) as count
    FROM pg_stat_activity
    WHERE state = 'active'
    AND now() - query_start > interval '5 seconds'
  metrics:
    - count:
        usage: "GAUGE"
        description: "Number of slow queries (>5s)"

# prometheus-rules.yml
groups:
  - name: postgresql
    rules:
      - alert: PostgreSQLHighConnections
        expr: sum(pg_stat_activity_count) / pg_settings_max_connections > 0.8
        for: 5m
        labels:
          severity: warning
        annotations:
          summary: "PostgreSQL 커넥션 사용률 80% 초과"

      - alert: PostgreSQLLowCacheHitRatio
        expr: pg_stat_database_blks_hit / (pg_stat_database_blks_hit + pg_stat_database_blks_read) < 0.95
        for: 10m
        labels:
          severity: warning
        annotations:
          summary: "PostgreSQL 캐시 히트율 95% 미만"

      - alert: PostgreSQLReplicationLag
        expr: pg_replication_lag_seconds > 30
        for: 1m
        labels:
          severity: critical
        annotations:
          summary: "PostgreSQL 복제 지연 30초 초과"