PostgreSQL Autovacuum 튜닝: 느려진 쿼리와 Bloat를 동시에 잡는 법

UPDATE orders SET status = 'shipped' WHERE id = 1;

내부 동작:
┌─────────────────┐     ┌─────────────────┐
│  Tuple v1       │     │  Tuple v2       │
│  status='new'   │     │  status='shipped'│
│  xmin=100       │     │  xmin=200       │
│  xmax=200  ◄────┼─────┤  xmax=0         │
│  (DEAD)         │     │  (LIVE)         │
└─────────────────┘     └─────────────────┘

-- HOT 발생 조건: 인덱스 컬럼(id) 미변경 + 같은 페이지 내 공간 있음
UPDATE orders SET status = 'delivered' WHERE id = 1;

Visibility Map (VM):
Page 0: ALL_VISIBLE ✓  → VACUUM 스킵, Index-Only Scan 가능
Page 1: NOT VISIBLE ✗  → VACUUM 대상
Page 2: ALL_VISIBLE ✓  → VACUUM 스킵
Page 3: ALL_FROZEN  ❄  → Freeze 완료, Wraparound 안전

Free Space Map (FSM):
Page 0: 200 bytes free
Page 1: 4000 bytes free  → INSERT 시 이 페이지 우선
Page 2: 100 bytes free

VACUUM 발동 조건:
  dead_tuples >= autovacuum_vacuum_threshold
                 + autovacuum_vacuum_scale_factor × n_live_tup

ANALYZE 발동 조건:
  changed_tuples >= autovacuum_analyze_threshold
                    + autovacuum_analyze_scale_factor × n_live_tup

autovacuum_vacuum_cost_delay = 2ms     -- 기본값
autovacuum_vacuum_cost_limit = 200     -- 기본값 (-1이면 vacuum_cost_limit 사용)

비용 계산:
- 버퍼 캐시에서 읽기: vacuum_cost_page_hit = 1
- 디스크에서 읽기:     vacuum_cost_page_miss = 2  (기본값)
- dirty 페이지:       vacuum_cost_page_dirty = 20

cost_limit 도달 → cost_delay만큼 sleep → 반복

autovacuum_max_workers = 3             -- 기본값
autovacuum_naptime = 60s               -- 체크 주기

-- 3개 worker가 cost_limit을 나눠 씀:
-- worker당 실효 limit = 200 / 3 ≈ 66

-- 핫 테이블: 주문 (하루 수백만 UPDATE)
ALTER TABLE orders SET (
    autovacuum_vacuum_scale_factor = 0.01,
    autovacuum_analyze_scale_factor = 0.005,
    autovacuum_vacuum_threshold = 5000,
    autovacuum_analyze_threshold = 2500,
    -- Worker throttling 완화
    autovacuum_vacuum_cost_delay = 0,        -- sleep 없음 (공격적)
    autovacuum_vacuum_cost_limit = 2000,     -- 기본값의 10배
    -- HOT update 유도
    fillfactor = 80
);

-- 로그/이력 테이블: INSERT만, UPDATE 거의 없음
ALTER TABLE audit_logs SET (
    autovacuum_vacuum_scale_factor = 0.1,    -- 느슨하게
    autovacuum_enabled = true                -- 끄지 마세요
);

-- 파티션 테이블: 개별 파티션에 설정
ALTER TABLE orders_2026_q1 SET (
    autovacuum_vacuum_scale_factor = 0.02,
    autovacuum_vacuum_threshold = 1000
);

-- postgresql.conf (서버 레벨)
autovacuum_max_workers = 5               -- 기본 3 → 5 (코어 수 고려)
autovacuum_vacuum_cost_delay = 0         -- 최신 PG에서는 0 권장
autovacuum_vacuum_cost_limit = 800       -- 기본 200 → 800
autovacuum_naptime = 30                  -- 기본 60s → 30s (더 자주 체크)
log_autovacuum_min_duration = 1000       -- 1초 이상 걸린 VACUUM 로깅

트랜잭션 ID 공간 (원형):
         현재 XID = 2,100,000,000
              │
    ┌─────────┼─────────┐
    │  "과거"  │  "미래"  │
    │(보이는)  │(안 보이는)│
    └─────────┴─────────┘

XID가 wrap하면:
- 과거로 분류되던 튜플이 갑자기 "미래"로 전환
- SELECT 결과에서 데이터가 사라짐
- PostgreSQL은 이를 방지하기 위해 강제 VACUUM 모드 진입

-- 현재 Freeze 상태 확인
SELECT
    c.relname,
    c.relfrozenxid,
    age(c.relfrozenxid) AS xid_age,
    pg_size_pretty(pg_total_relation_size(c.oid)) AS size
FROM pg_class c
JOIN pg_namespace n ON c.relnamespace = n.oid
WHERE c.relkind = 'r' AND n.nspname = 'public'
ORDER BY age(c.relfrozenxid) DESC
LIMIT 10;

-- age가 1.5억을 넘는 테이블 모니터링
SELECT relname, age(relfrozenxid)
FROM pg_class
WHERE relkind = 'r' AND age(relfrozenxid) > 150000000
ORDER BY age(relfrozenxid) DESC;

B-Tree 인덱스:
┌────────┐
│ Root   │
├────────┤
│ Branch │ → dead pointer가 정리되지 않으면
├────────┤    인덱스 크기만 커지고 검색 경로 증가
│ Leaf   │ → [live][dead][dead][live][dead]...
└────────┘

-- pgstattuple 확장 사용
CREATE EXTENSION IF NOT EXISTS pgstattuple;

-- 인덱스 bloat 비율 확인
SELECT
    indexrelname,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
    idx_scan,
    round(100.0 * avg_leaf_density, 2) AS leaf_density_pct
FROM pg_stat_user_indexes
JOIN pgstatindex(indexrelname) ON true
WHERE schemaname = 'public'
ORDER BY pg_relation_size(indexrelid) DESC
LIMIT 10;

-- 간이 bloat 추정 (pgstattuple 없이)
SELECT
    schemaname, tablename, indexname,
    pg_size_pretty(pg_relation_size(indexname::regclass)) AS index_size,
    pg_size_pretty(pg_relation_size(tablename::regclass)) AS table_size,
    round(
        100.0 * pg_relation_size(indexname::regclass) /
        NULLIF(pg_relation_size(tablename::regclass), 0), 1
    ) AS index_table_ratio
FROM pg_indexes
WHERE schemaname = 'public'
ORDER BY pg_relation_size(indexname::regclass) DESC
LIMIT 10;

-- 운영 중 안전한 인덱스 재생성
REINDEX INDEX CONCURRENTLY idx_orders_status;

-- 또는 새 인덱스 생성 후 교체
CREATE INDEX CONCURRENTLY idx_orders_status_new ON orders(status);
-- 검증 후
DROP INDEX idx_orders_status;
ALTER INDEX idx_orders_status_new RENAME TO idx_orders_status;

-- 방법 1: cron + ALTER SYSTEM으로 시간대별 전환
-- 업무시간 (09:00~18:00): 보수적
ALTER SYSTEM SET autovacuum_vacuum_cost_delay = '2ms';
ALTER SYSTEM SET autovacuum_vacuum_cost_limit = 200;
SELECT pg_reload_conf();

-- 야간 (18:00~09:00): 공격적
ALTER SYSTEM SET autovacuum_vacuum_cost_delay = '0';
ALTER SYSTEM SET autovacuum_vacuum_cost_limit = 2000;
SELECT pg_reload_conf();

SELECT
    schemaname, relname,
    n_live_tup, n_dead_tup,
    round(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 2) AS dead_pct,
    last_autovacuum,
    last_autoanalyze,
    autovacuum_count,
    pg_size_pretty(pg_total_relation_size(relid)) AS total_size
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC
LIMIT 20;

SELECT
    pid, datname, relid::regclass AS table_name,
    phase, heap_blks_total, heap_blks_scanned, heap_blks_vacuumed,
    index_vacuum_count, max_dead_tuples, num_dead_tuples
FROM pg_stat_progress_vacuum;

-- 오래된 트랜잭션은 dead tuple 정리를 막음
SELECT
    pid, usename, state,
    age(backend_xid) AS xid_age,
    age(backend_xmin) AS xmin_age,
    now() - xact_start AS duration,
    query
FROM pg_stat_activity
WHERE state != 'idle'
  AND backend_xmin IS NOT NULL
ORDER BY age(backend_xmin) DESC
LIMIT 5;