PostgreSQL VACUUM 안끝남 - bloat·wraparound 해결

서버가 느려지고 디스크가 계속 차는데 VACUUM은 “돌고 있는 것 같지만” 끝나지 않는 상황이 종종 발생합니다. 이때 원인을 크게 둘로 나누면 진단이 빨라집니다.

• bloat(팽창): 삭제/업데이트로 생긴 dead tuple이 회수되지 못해 테이블/인덱스가 비대해진 상태
• wraparound(트랜잭션 ID 회전): 오래된 XID가 재사용되기 전에 반드시 청소해야 하는 안전장치(anti-wraparound vacuum)가 몰려 발생

이 글은 “왜 안 끝나는지”를 SQL로 확인하고, **진짜로 끝내는 방법(락/장기 트랜잭션/인덱스/IO 병목)**을 단계별로 정리합니다. (운영 환경 기준)

> 참고: 장애 원인 분석을 체계화하는 방식은 인프라 트러블슈팅에서도 동일합니다. 네트워크/리소스 병목을 추적하는 관점은 EKS CrashLoopBackOff - OOMKilled·Exit 137 원인과 해결 같은 글의 접근과도 유사합니다.

1) VACUUM이 “안 끝나는 것처럼 보이는” 대표 패턴

1-1. VACUUM 자체가 멈춘 게 아니라 너무 느린 경우

• 테이블이 매우 크거나(수백 GB~TB)
• 인덱스가 많거나
• IO가 포화(특히 shared storage, 느린 EBS, 스토리지 크레딧 고갈)
• autovacuum이 너무 보수적으로 설정됨

이 경우는 진행률을 보고 판단해야 합니다.

1-2. VACUUM이 dead tuple을 못 지우는 경우(장기 트랜잭션)

VACUUM은 “삭제된 행”을 당장 물리적으로 없애는 게 아니라, 어떤 트랜잭션도 그 행을 더 이상 볼 수 없을 때 회수합니다. 즉, 오래 열린 트랜잭션/세션이 있으면 dead tuple이 계속 남습니다.

1-3. wraparound 방지 VACUUM이 몰려서 끝이 안 나는 경우

age(datfrozenxid)가 임계치에 가까워지면 PostgreSQL은 강제로 anti-wraparound vacuum을 수행합니다. 이건 “성능 최적화”가 아니라 데이터 손상 방지이므로, 느려도 멈추기 어렵고 우선순위가 높습니다.

2) 지금 VACUUM이 뭘 하고 있는지: 필수 진단 SQL

2-1. 실행 중인 VACUUM/Autovacuum 확인

SELECT
  pid,
  datname,
  usename,
  state,
  wait_event_type,
  wait_event,
  now() - xact_start AS xact_age,
  now() - query_start AS query_age,
  query
FROM pg_stat_activity
WHERE query ILIKE '%vacuum%'
ORDER BY query_start;

• wait_event_type이 IO/Lock/Client인지에 따라 방향이 갈립니다.
• xact_age가 비정상적으로 길면(예: 수시간~수일) “장기 트랜잭션이 VACUUM을 막는” 상황일 수 있습니다.

2-2. VACUUM 진행률 보기(버전별)

• VACUUM(일반): pg_stat_progress_vacuum
• VACUUM FULL: pg_stat_progress_cluster

SELECT
  pid,
  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
ORDER BY heap_blks_scanned DESC;

phase가 오래 고정되어 있으면(예: vacuuming indexes에서 수시간) 인덱스/IO/락을 의심합니다.

2-3. 누가 VACUUM을 막는지(락/장기 트랜잭션)

락 대기가 있는지:

SELECT
  a.pid AS waiting_pid,
  a.query AS waiting_query,
  a.wait_event_type,
  a.wait_event,
  b.pid AS blocking_pid,
  b.query AS blocking_query,
  now() - b.query_start AS blocking_for
FROM pg_stat_activity a
JOIN pg_stat_activity b
  ON b.pid = ANY(pg_blocking_pids(a.pid))
WHERE a.wait_event_type = 'Lock';

VACUUM이 지우지 못하게 만드는 오래된 스냅샷/트랜잭션:

SELECT
  pid,
  usename,
  datname,
  state,
  now() - xact_start AS xact_age,
  now() - query_start AS query_age,
  query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY xact_start;

특히 다음이 흔한 원인입니다.

• 커넥션 풀에서 BEGIN만 해놓고 놀고 있는 세션
• 배치 작업이 REPEATABLE READ/SERIALIZABLE로 오래 읽는 쿼리
• 논리 복제/리플리케이션 슬롯으로 인해 제거가 지연되는 케이스(환경에 따라)

3) bloat 진단: “VACUUM이 끝나도 공간이 안 줄어드는” 이유

핵심: 일반 VACUUM은 파일 크기를 줄이지 않습니다.

• 일반 VACUUM: dead tuple을 재사용 가능 상태로 표시(내부 free space 증가)
• 디스크 반환: VACUUM FULL 또는 CLUSTER 또는 재작성(rewrite) 계열 작업

3-1. 테이블/인덱스 크기와 튜플 상태 보기

SELECT
  relname AS table_name,
  pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
  pg_size_pretty(pg_relation_size(relid)) AS heap_size,
  pg_size_pretty(pg_total_relation_size(relid) - pg_relation_size(relid)) AS index_toast_size,
  n_live_tup,
  n_dead_tup,
  last_vacuum,
  last_autovacuum,
  vacuum_count,
  autovacuum_count
FROM pg_stat_user_tables
ORDER BY pg_total_relation_size(relid) DESC
LIMIT 20;

• n_dead_tup가 크고 last_autovacuum이 오래됐으면 autovacuum이 따라가지 못하는 중입니다.
• 인덱스 bloat는 이 쿼리만으로는 한계가 있어, 확장/샘플링 기반으로 추정하거나(예: pgstattuple), 운영 정책으로 재작성하는 접근을 씁니다.

3-2. bloat가 커지는 구조적 원인

• UPDATE가 많은 테이블(특히 HOT update가 깨지는 스키마)
• 인덱스가 과도하게 많거나, 자주 업데이트되는 컬럼에 인덱스가 있음
• fillfactor 기본값(100)로 페이지가 꽉 차서 업데이트 시 페이지 분할 증가

4) wraparound 진단: “지금 당장 위험한지” 확인

4-1. 데이터베이스별 age 확인

SELECT
  datname,
  age(datfrozenxid) AS xid_age,
  current_setting('autovacuum_freeze_max_age') AS freeze_max_age
FROM pg_database
ORDER BY age(datfrozenxid) DESC;

xid_age가 autovacuum_freeze_max_age에 가까우면 anti-wraparound vacuum이 강하게 돌 수 있습니다.

4-2. 테이블별 freeze 지연 확인

SELECT
  c.oid::regclass AS table_name,
  age(c.relfrozenxid) AS table_xid_age,
  pg_size_pretty(pg_total_relation_size(c.oid)) AS total_size
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = 'r'
  AND n.nspname NOT IN ('pg_catalog', 'information_schema')
ORDER BY age(c.relfrozenxid) DESC
LIMIT 30;

여기서 상위에 뜨는 테이블이 wraparound 압박의 주범인 경우가 많습니다.

5) “VACUUM 안끝남”을 실제로 끝내는 해결 전략

5-1. 1순위: 장기 트랜잭션/세션부터 끊기

장기 트랜잭션이 있으면 VACUUM은 끝나더라도 효과가 제한되거나, 아예 회수가 지연됩니다.

• 애플리케이션에서 idle in transaction을 만들지 않도록 수정
• 풀 설정에서 statement/transaction timeout 적용
• 운영 중에는 문제 세션을 종료(주의: 롤백 발생)

-- idle in transaction 세션 찾기
SELECT pid, usename, now() - xact_start AS xact_age, state, query
FROM pg_stat_activity
WHERE state = 'idle in transaction'
ORDER BY xact_start;

-- 정말 필요할 때만 종료
SELECT pg_terminate_backend(<pid>);

5-2. 2순위: autovacuum 튜닝(테이블 단위가 효과적)

전체 파라미터를 바꾸기보다, 문제 테이블에만 강하게 거는 게 안전합니다.

예시(대량 UPDATE/DELETE 테이블):

ALTER TABLE public.big_table SET (
  autovacuum_vacuum_scale_factor = 0.02,
  autovacuum_vacuum_threshold = 10000,
  autovacuum_analyze_scale_factor = 0.01,
  autovacuum_analyze_threshold = 5000,
  autovacuum_vacuum_cost_limit = 2000,
  autovacuum_vacuum_cost_delay = 5
);

• scale_factor를 낮추면 더 자주 청소합니다.
• cost_limit/delay는 IO와의 트레이드오프입니다. “안 끝남”이면 대개 너무 보수적이라 올리는 쪽이 필요합니다.

5-3. 3순위: 수동 VACUUM(ANALYZE) + 타겟 테이블 분할 실행

대형 DB에서 VACUUM (ANALYZE)를 한 번에 돌리기보다, 문제 테이블부터 순서대로 처리합니다.

VACUUM (ANALYZE, VERBOSE) public.big_table;

진행률은 pg_stat_progress_vacuum으로 확인합니다.

5-4. bloat가 심각하면: VACUUM FULL/CLUSTER/재작성(다운타임 고려)

디스크를 줄여야 한다면 일반 VACUUM이 아니라 테이블 재작성이 필요합니다.

선택지 비교

• VACUUM FULL: 테이블을 새로 써서 공간 반환, 강한 락(거의 독점)
• CLUSTER: 인덱스 순서로 재정렬 + 재작성, 역시 강한 락
• 온라인 재작성 도구(예: pg_repack): 락을 최소화하지만 운영 복잡도 증가

-- 다운타임/락 감수 가능할 때
VACUUM (FULL, VERBOSE, ANALYZE) public.big_table;

-- 특정 인덱스 기준으로 물리 정렬까지 하고 싶을 때
CLUSTER VERBOSE public.big_table USING big_table_some_idx;
ANALYZE public.big_table;

운영 팁:

• VACUUM FULL은 “끝나면 좋아지지만, 끝날 때까지 서비스가 멈출 수 있는” 작업입니다. 윈도우를 확보하거나 온라인 도구를 검토하세요.

5-5. wraparound 압박이면: FREEZE를 포함해 우선 처리

wraparound는 성능 문제가 아니라 안전 문제입니다. 대상 테이블을 우선적으로 얼려(freeze) XID age를 낮추는 게 핵심입니다.

-- 해당 테이블을 강하게 동결(대형 테이블은 오래 걸릴 수 있음)
VACUUM (FREEZE, VERBOSE) public.hot_table;

-- DB 전체 동결(운영 영향 매우 큼, 신중)
VACUUM (FREEZE, VERBOSE);

그리고 autovacuum freeze 관련 설정도 점검합니다.

• autovacuum_freeze_max_age
• vacuum_freeze_min_age
• vacuum_freeze_table_age

값을 무작정 키우면 “지금은 조용하지만 나중에 폭발”할 수 있어, 보통은 테이블별로 자주 vacuum이 돌도록 만드는 방향이 안전합니다.

6) 자주 놓치는 병목: 인덱스/IO/체크포인트

6-1. 인덱스가 많으면 vacuuming indexes 단계가 길어진다

• 불필요한 인덱스를 줄이거나
• 업데이트가 잦은 컬럼의 인덱스를 재검토
• 대량 삭제 후에는 인덱스 재작성(REINDEX)도 고려

-- 특정 테이블의 인덱스 목록/크기
SELECT
  i.indexrelid::regclass AS index_name,
  pg_size_pretty(pg_relation_size(i.indexrelid)) AS index_size
FROM pg_index i
WHERE i.indrelid = 'public.big_table'::regclass
ORDER BY pg_relation_size(i.indexrelid) DESC;

-- 필요 시(락/부하 주의)
REINDEX (VERBOSE) TABLE public.big_table;

6-2. IO 포화면 vacuum은 “정상인데” 끝이 안 난다

• 스토리지 지표(IOPS/throughput/latency) 확인
• autovacuum_vacuum_cost_* 완화
• vacuum 작업을 분산(동시성/스케줄)

Kubernetes/EKS 환경이라면 노드/스토리지/네트워크 병목이 겹쳐 체감이 더 심해질 수 있습니다. 네트워크 단의 간헐적 병목을 추적하는 방식은 EKS Pod egress 간헐 끊김 - SNAT·NAT GW 추적법처럼 “관측 포인트를 늘려서 원인을 좁히는” 접근이 유효합니다.

7) 운영에서 재발 방지 체크리스트

7-1. 애플리케이션/쿼리 레벨

• idle in transaction 금지(풀/프레임워크 설정)
• 배치/리포트 쿼리에 timeout 적용
• 대량 UPDATE/DELETE는 배치 크기 제한(예: 5k~50k 단위)

7-2. DB 레벨

• 문제 테이블에 autovacuum aggressive 설정
• 정기적으로 pg_stat_user_tables에서 n_dead_tup 상위 감시
• wraparound 모니터링: age(datfrozenxid) 알람

간단한 모니터링 쿼리 예:

-- dead tuple 상위 테이블
SELECT relid::regclass AS table_name, n_dead_tup, n_live_tup,
       round(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 2) AS dead_pct
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;

-- wraparound 위험도 상위 DB
SELECT datname, age(datfrozenxid) AS xid_age
FROM pg_database
ORDER BY age(datfrozenxid) DESC;

8) 결론: “VACUUM이 안 끝난다”는 증상은 3가지만 보면 풀린다

1. 진행률/대기 이벤트로 진짜 멈춤인지, 느림인지 구분하고
2. 장기 트랜잭션/락을 먼저 제거한 뒤
3. bloat면 재작성(VACUUM FULL/CLUSTER/pg_repack), wraparound면 FREEZE 우선으로 접근하면 됩니다.

VACUUM은 단순한 정리 작업이 아니라, MVCC 기반 PostgreSQL의 “시간(트랜잭션)과 공간(스토리지)을 관리하는 핵심 메커니즘”입니다. 한 번 안 끝나기 시작하면 원인은 대부분 반복되므로, 위 진단 SQL과 테이블 단위 autovacuum 정책을 템플릿으로 만들어두면 다음 장애를 훨씬 빨리 끝낼 수 있습니다.