Vacuum과 유지보수

학습 목표

dead tuple·bloat가 MVCC UPDATE/DELETE 후 성능에 미치는 이유를 설명할 수 있다.

autovacuum 트리거·모니터링·장시간 TX 차단을 설명할 수 있다.

VACUUM vs VACUUM FULL·ANALYZE 역할과 트레이드오프를 설명할 수 있다.

transaction id freeze·wraparound 위험을 설명할 수 있다.

InnoDB purge가 undo 정리에 대응함을 개요 수준에서 설명할 수 있다.

문제 상황

• orders UPDATE 대량 후 테이블 크기 동일 · Seq Scan 2배 느림
• n_dead_tup 40% — autovacuum 안 돔
• 장시간 idle in transaction 하나 — 전 테이블 vacuum 정지
• VACUUM FULL 피크 실행 — 30분 exclusive lock · 서비스 중단
• EXPLAIN rows 추정 10배 오차 — ANALYZE 6개월 전
• MySQL undo tablespace 90% — purge lag · long read TX

앞서 MVCC — dead tuple 개념; 슬로우 쿼리 — bloat 의심. 이번 편은 운영 유지보수 절차다.

1. 왜 vacuum이 필요한가

MVCC — UPDATE/DELETE는 in-place 삭제 아님 — old 버전 dead tuple 남음.

	live tuple	dead tuple
가시성	active 스냅샷 가능	어느 TX도 안 봄
공간	데이터	재사용 대기
인덱스	참조	여전히 참조 — bloat

UPDATE row  →  old version dead  +  new version live
DELETE row  →  tombstone dead
VACUUM      →  dead space reusable · prune index pointers

미 vacuum
테이블·인덱스 팽창	더 많은 I/O
Seq Scan 비용 ↑	슬로우 쿼리 (21편)
Index Only Scan 실패	visibility map stale
XID wraparound	freeze 필요

• 앞서 디스크·페이지 — 힙 슬롯 재사용
• InnoDB — heap in-place + undo — purge 대응

2. VACUUM과 autovacuum

VACUUM — dead tuple 공간 회수 · freeze · 통계 힌트.

종류
autovacuum	백그라운드 worker — 설정 임계
VACUUM	수동·cron — lock 가벼움
VACUUM ANALYZE	vacuum + 통계 갱신
VACUUM FULL	재작성 — compact — exclusive lock

-- code/vacuum-commands.sql
VACUUM (VERBOSE, ANALYZE) orders;

VACUUM (PARALLEL 4) orders_2025_01;  -- PG 13+

-- last resort — downtime window
VACUUM FULL orders;

autovacuum 트리거 (개념)
autovacuum_vacuum_threshold + scale_factor × reltuples	dead 비율
autovacuum_vacuum_insert_threshold	insert-only bloat
anti-wraparound vacuum	age (relfrozenxid) 임계

# code/autovacuum-config.example
autovacuum = on
autovacuum_vacuum_scale_factor = 0.1
autovacuum_analyze_scale_factor = 0.05
autovacuum_max_workers = 4
log_autovacuum_min_duration = 1s

차단 요인
Long transaction	oldest xmin — dead 정리 불가
Replication slot	lag — WAL·vacuum 압력
Disabled autovacuum	per-table autovacuum_enabled = false

3. 모니터링

-- code/autovacuum-monitor.sql
SELECT relname,
       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,
       last_autoanalyze,
       age(relfrozenxid) AS xid_age
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;

-- transactions blocking vacuum
SELECT pid, xact_start, state, backend_xmin,
       left(query, 80) AS query
FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL
ORDER BY xact_start;

지표	주의
dead_pct > 20%	vacuum 지연 의심
last_autovacuum NULL·오래됨	임계·blocker
xid_age 높음	freeze vacuum
pg_stat_progress_vacuum	진행 중 작업

• Observability — n_dead_tup·autovacuum duration 메트릭
• Hot 테이블 — 낮은 scale_factor override

4. VACUUM vs VACUUM FULL

	VACUUM	VACUUM FULL
동작	dead 슬롯 재사용 표시	새 파일 복사 — compact
크기	OS 파일 크기 유지 가능	파일 축소
Lock	ShareUpdateExclusive — DML 대부분 가능	ACCESS EXCLUSIVE — 차단
빈도	상시·autovacuum	드묾·점검 창
대안	routine ops	pg_repack·partition DROP

Bloat 80% but VACUUM ran  →  space reusable inside file, not returned to OS
Need disk back            →  VACUUM FULL or pg_repack or detach/drop partition

선택
일상	autovacuum + ANALYZE
dead 높음	원인 (long TX) 먼저
디스크 급함	VACUUM FULL·repack·아카이브 partition DROP

5. ANALYZE

ANALYZE — 통계 갱신 — 옵티마이저 rows·ndistinct (실행 계획 8편).

	VACUUM	ANALYZE
목적	dead 공간·freeze	쿼리 플랜
autovacuum	vacuum worker	analyze 단계
수동	VACUUM	ANALYZE / VACUUM ANALYZE

ANALYZE orders;
ANALYZE orders (user_id, created_at);  -- column stats (PG 11+)

SELECT relname, last_analyze, last_autoanalyze, n_mod_since_analyze
FROM pg_stat_user_tables
WHERE relname = 'orders';

시점
대량 LOAD 후	COPY 끝 — ANALYZE
스키마 변경	인덱스 추가 후
플랜 급변	rows 추정 오차 — 21편

• ANALYZE ≠ VACUUM — 둘 다 필요
• default_statistics_target — 정밀 vs 속도

6. Freeze와 wraparound

Transaction ID (XID) — 32-bit 순환 — 오래된 tuple freeze.

age(relfrozenxid) grows  →  anti-wraparound autovacuum
xid age > 2^31 - safety  →  database shutdown risk (forced vacuum)

freeze	tuple xmin → FrozenXid — 모든 TX 가시
visibility map	all-frozen page — index-only skip heap
모니터	pg_database.datfrozenxid · per-table relfrozenxid

• 장기 미 vacuum — 클러스터 전체 읽기 전용 강제 위험
• 파티션 — 오래된 partition DROP = freeze 부담 감소

7. InnoDB purge 개요

	PostgreSQL VACUUM	InnoDB purge
대상	heap dead tuple	undo log 정리
실행	autovacuum worker	purge thread (background)
차단	long TX xmin	long read view (MVCC)
compact	VACUUM FULL	OPTIMIZE TABLE (lock)

-- code/innodb-purge-notes.sql (MySQL)
SHOW VARIABLES LIKE 'innodb_undo_log_truncate';
SHOW VARIABLES LIKE 'innodb_max_undo_log_size';

SELECT * FROM information_schema.innodb_metrics
WHERE name LIKE '%purge%';

증상
undo tablespace full	purge lag
history list length 높음	long running query

• 앞서 MVCC — 양쪽 다중 버전 · GC 메커니즘 다름
• 공통 — long transaction 적 enemy

8. 운영 체크리스트

항목	확인
autovacuum	on · workers 충분?
dead_pct	hot table TOP?
Blocker	idle in transaction·slot?
Analyze	last_autoanalyze·bulk load 후?
xid_age	wraparound 경고?
FULL	정말 필요·창 있음?
InnoDB	undo·purge lag?

9. 정리

• dead tuple — MVCC 부산물 — VACUUM 없으면 bloat
• autovacuum — 일상 방어 — long TX 차단 주의
• VACUUM — 재사용 표시 · VACUUM FULL — compact·락
• ANALYZE — 플랜 품질 — vacuum 과 별개
• freeze — XID wraparound 방지
• InnoDB — purge — undo 정리 대응

다음에 다룰 것

• 파티션 운영
• detach·attach·drop, 핫 파티션

---

해당 내용은 Database System Concepts, 7/E (Avraham Silberschatz, Henry F. Korth, S. Sudarshan), PostgreSQL · MySQL InnoDB 공식 문서 의 내용을 기반으로 합니다.