PostgreSQL VACUUM 안 먹힐 때 - bloat·autovacuum 튜닝

서버가 느려져서 VACUUM을 돌렸는데도 테이블이 여전히 무겁고, 디스크 사용량도 줄지 않고, 쿼리 플랜도 개선되지 않는 경우가 있습니다. 이때 많은 팀이 VACUUM FULL을 바로 시도하거나, 무작정 autovacuum 파라미터를 올리다가 락과 I/O 스파이크로 더 큰 장애를 만들곤 합니다.

핵심은 “VACUUM이 무엇을 해결할 수 있고, 무엇은 해결하지 못하는지”를 분리해 보는 것입니다. 일반 VACUUM은 죽은 튜플을 재사용 가능 상태로 표시하고 통계를 업데이트하지만, 파일 크기를 줄이거나 OS에 공간을 반환하지는 않는 경우가 대부분입니다. 또한 long-running transaction, replication slot, 너무 낮은 autovacuum 작업량, 인덱스 bloat 같은 요인이 있으면 VACUUM이 돌아도 체감이 없습니다.

이 글에서는 다음을 목표로 합니다.

• VACUUM이 “안 먹히는” 전형적인 원인 5가지를 빠르게 진단
• bloat를 수치로 확인하고, 테이블/인덱스별 우선순위 결정
• autovacuum이 실제로 일을 하도록 튜닝하는 방법
• 운영 중단을 최소화하는 재정리 전략(REINDEX CONCURRENTLY, pg_repack 등)

관련해서 bloat 진단을 더 깊게 다룬 글은 PostgreSQL VACUUM 안 돌 때 bloat 진단·해결도 함께 참고하면 좋습니다.

1) 먼저 오해부터: VACUUM이 줄여주는 것과 못 줄이는 것

VACUUM이 해주는 일

• dead tuple을 “재사용 가능”으로 표시
• visibility map 갱신(특히 index-only scan에 영향)
• 통계 갱신(ANALYZE 포함 시)

VACUUM이 보통 못 해주는 일

• 테이블 파일 크기 축소 및 OS로 공간 반환
• 이미 부풀어진 인덱스 파일 크기 축소

즉, VACUUM 후에도 pg_relation_size가 거의 그대로인 것은 정상일 수 있습니다. 중요한 것은 “새 쓰기 작업이 그 공간을 재사용해서 성능이 좋아지는지”입니다.

2) VACUUM이 체감이 없는 5가지 대표 원인

원인 A: long-running transaction이 xmin을 붙잡고 있음

VACUUM은 “모든 활성 트랜잭션보다 오래된 dead tuple”만 제거(정확히는 제거 가능 표시)할 수 있습니다. 오래 열린 트랜잭션이 있으면 dead tuple이 계속 남아 bloat가 누적됩니다.

아래 쿼리로 오래 열린 세션을 확인합니다.

SELECT
  pid,
  usename,
  state,
  now() - xact_start AS xact_age,
  now() - query_start AS query_age,
  wait_event_type,
  wait_event,
  left(query, 200) AS query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY xact_age DESC
LIMIT 20;

• xact_age가 몇 시간 이상이면 위험 신호
• idle in transaction 상태는 특히 치명적

필요하다면 애플리케이션 커넥션 풀 설정(트랜잭션 누수)부터 잡아야 합니다. VACUUM 튜닝만으로는 해결되지 않습니다.

원인 B: replication slot이 xmin 또는 catalog_xmin을 유지

논리 복제나 CDC 도구가 slot을 만들고 소비를 못 하면 WAL뿐 아니라 vacuum 진행에도 간접적으로 영향을 줄 수 있습니다(특히 카탈로그 쪽).

SELECT
  slot_name,
  plugin,
  slot_type,
  active,
  xmin,
  catalog_xmin,
  pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained_wal
FROM pg_replication_slots
ORDER BY retained_wal DESC;

slot이 비활성인데 retained_wal이 계속 커지면 소비자 장애를 의심하고, 필요 시 slot 정리도 검토해야 합니다.

원인 C: autovacuum이 “돌긴 도는데” 너무 약하게 돌고 있음

기본 설정은 많은 워크로드에서 보수적입니다.

• 테이블이 커질수록 autovacuum_vacuum_scale_factor 때문에 임계치가 너무 커짐
• autovacuum_vacuum_cost_limit과 autovacuum_vacuum_cost_delay로 인해 I/O를 지나치게 아낌
• 작업자 수(autovacuum_max_workers)가 부족해 backlog가 쌓임

현재 autovacuum 활동을 확인합니다.

SELECT
  pid,
  relid::regclass AS relation,
  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_total DESC;

원인 D: 인덱스 bloat가 병목인데 테이블만 vacuum함

테이블이 재사용 가능해져도, 인덱스가 부풀면 랜덤 I/O가 늘고 캐시 효율이 떨어져 체감 성능이 회복되지 않습니다. 일반 VACUUM은 인덱스의 “죽은 엔트리 정리”에는 도움되지만, 인덱스 파일 크기 자체를 줄이지는 못합니다.

이 경우 REINDEX 또는 REINDEX CONCURRENTLY가 필요합니다.

원인 E: HOT update가 깨져서 dead tuple이 과도하게 생김

업데이트가 HOT(Heap-Only Tuple)로 처리되면 인덱스 업데이트가 줄어 bloat가 완화됩니다. 하지만 아래 요인으로 HOT가 깨지면 bloat가 빨리 쌓입니다.

• 자주 업데이트되는 컬럼이 인덱스에 포함됨
• fillfactor가 너무 높아 페이지 내 여유 공간이 없음

테이블별 업데이트 패턴이 심한 곳은 fillfactor 조정이 효과적입니다.

3) bloat와 vacuum 상태를 “수치로” 확인하는 최소 진단 세트

3-1) dead tuple이 많은 테이블 찾기

SELECT
  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_vacuum,
  last_autovacuum,
  last_analyze,
  last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 30;

• dead_pct가 높고 last_autovacuum이 오래됐으면 autovacuum backlog 가능성
• last_autovacuum이 최근인데도 n_dead_tup가 계속 높으면 long transaction이나 scale factor 문제를 의심

3-2) 테이블/인덱스 크기와 비중 확인

SELECT
  relname,
  pg_size_pretty(pg_table_size(relid)) AS table_size,
  pg_size_pretty(pg_indexes_size(relid)) AS index_size,
  pg_size_pretty(pg_total_relation_size(relid)) AS total_size
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC
LIMIT 20;

인덱스가 테이블보다 과도하게 큰 상위 테이블부터 재정리 후보로 잡습니다.

3-3) autovacuum 임계치가 너무 큰 테이블 찾기

테이블이 큰데 scale factor가 기본값이면 vacuum이 너무 늦게 시작됩니다. 아래는 테이블별 vacuum 트리거 임계치를 계산하는 예시입니다.

SELECT
  c.relname,
  s.n_live_tup,
  s.n_dead_tup,
  current_setting('autovacuum_vacuum_threshold')::int AS base_threshold,
  current_setting('autovacuum_vacuum_scale_factor')::numeric AS scale_factor,
  (current_setting('autovacuum_vacuum_threshold')::int
   + current_setting('autovacuum_vacuum_scale_factor')::numeric * s.n_live_tup
  )::bigint AS vacuum_trigger_est
FROM pg_class c
JOIN pg_stat_user_tables s ON s.relid = c.oid
WHERE c.relkind = 'r'
ORDER BY vacuum_trigger_est DESC
LIMIT 30;

vacuum_trigger_est가 수백만 단위로 커져 있으면 “너무 늦게 청소하는 구조”일 가능성이 큽니다.

4) autovacuum 튜닝: 전역 설정 vs 테이블별 설정

운영에서 안전한 접근은 보통 다음 순서입니다.

1. 전역 설정을 과격하게 바꾸기 전에, 문제 테이블에만 storage parameter로 적용
2. backlog가 줄어들면 전역 설정을 점진적으로 개선

4-1) 테이블별 autovacuum 파라미터(권장 출발점)

대형 테이블은 scale factor를 낮추고 threshold를 적절히 둬서 더 자주 vacuum이 돌게 만듭니다.

ALTER TABLE public.events
SET (
  autovacuum_vacuum_scale_factor = 0.02,
  autovacuum_vacuum_threshold = 5000,
  autovacuum_analyze_scale_factor = 0.01,
  autovacuum_analyze_threshold = 5000
);

• 업데이트/삭제가 잦은 테이블일수록 scale factor를 낮게
• 분석 통계가 중요한 OLTP는 analyze도 같이 촘촘히

4-2) 비용 기반 딜레이(cost-based vacuum) 조정

vacuum이 너무 “예의 바르게” 돌면 bloat가 이깁니다. 반대로 너무 공격적이면 OLTP 지연이 튈 수 있습니다. 다음은 흔한 타협점입니다.

• autovacuum_vacuum_cost_limit 상향
• autovacuum_vacuum_cost_delay 하향

예시(환경에 맞게 조정 필요):

ALTER SYSTEM SET autovacuum_vacuum_cost_limit = 4000;
ALTER SYSTEM SET autovacuum_vacuum_cost_delay = '5ms';
SELECT pg_reload_conf();

디스크가 느리거나 shared storage라면 비용 제한을 너무 풀지 말고, 대신 worker 수와 테이블별 임계치 조정부터 하는 편이 안전합니다.

4-3) worker 수와 동시성

backlog가 쌓이면 “언젠가 autovacuum이 하겠지”가 아니라 “영원히 못 따라간다”가 됩니다.

ALTER SYSTEM SET autovacuum_max_workers = 6;
ALTER SYSTEM SET autovacuum_naptime = '10s';
SELECT pg_reload_conf();

• worker 수를 늘리면 I/O 경쟁도 늘어납니다. 모니터링과 함께 점진적으로.

4-4) vacuum freeze 관련 파라미터

freeze가 밀리면 결국 anti-wraparound vacuum이 강제로 들어오고, 그때는 더 공격적으로 자원을 씁니다. 평소에 분산시키는 편이 낫습니다.

• autovacuum_freeze_max_age
• vacuum_freeze_min_age
• vacuum_freeze_table_age

여기는 장애 예방 관점에서 중요하지만, 값 조정은 버전/워크로드에 따라 부작용이 커서 변경 전 테스트가 권장됩니다.

5) “디스크가 안 줄어드는” 상황에서의 선택지

5-1) 정말 파일 크기를 줄여야 한다면: VACUUM FULL

VACUUM FULL은 테이블을 새로 써서 압축하므로 파일 크기를 줄일 수 있습니다. 대신 강한 락이 걸려 운영 중에는 위험합니다.

VACUUM (FULL, VERBOSE, ANALYZE) public.events;

• 가능하면 점검 창에 수행
• 대안으로 pg_repack을 고려(운영 중 락을 줄이는 방식)

5-2) 인덱스 bloat는 REINDEX CONCURRENTLY

운영 중단을 최소화하려면 concurrent 옵션을 우선 검토합니다.

REINDEX INDEX CONCURRENTLY public.events_created_at_idx;

테이블 전체라면:

REINDEX TABLE CONCURRENTLY public.events;

• concurrent는 시간이 더 걸리지만 락 영향이 훨씬 적습니다.
• 디스크에 임시로 여유 공간이 더 필요할 수 있습니다.

5-3) 테이블 설계 측면: 파티셔닝과 수명주기

로그/이벤트처럼 append가 많고 삭제가 주기적인 테이블은 “삭제 후 vacuum”보다 “파티션 drop”이 훨씬 효율적입니다.

• 월 단위 파티셔닝 후 오래된 파티션 DROP TABLE
• 인덱스/테이블 bloat 자체를 구조적으로 줄임

6) 운영에서 자주 쓰는 실전 플레이북

단계 1: 지금 vacuum이 막혔는지, 그냥 느린지 구분

• pg_stat_progress_vacuum에 진행이 보이면 느린 것
• 진행이 없고 dead tuple이 증가하면 long transaction, slot, cost 제한을 의심

단계 2: “죽은 튜플 상위 10개 테이블”을 먼저 잡기

• 테이블별 autovacuum scale factor 낮추기
• 필요 시 수동 VACUUM (ANALYZE)를 오프피크에 수행

VACUUM (VERBOSE, ANALYZE) public.events;

단계 3: 인덱스 비중이 큰 테이블은 인덱스 재정리 계획 수립

• REINDEX CONCURRENTLY 우선
• 인덱스가 너무 많다면 “정말 필요한 인덱스인가”부터 재검토

단계 4: 재발 방지

• idle in transaction 방지(애플리케이션 레벨 타임아웃)
• 대형 테이블의 테이블별 autovacuum 파라미터 고정
• 업데이트가 잦은 테이블은 fillfactor 조정 검토

예시:

ALTER TABLE public.events SET (fillfactor = 80);
VACUUM (ANALYZE) public.events;

fillfactor 변경은 기존 페이지를 재작성하지 않으므로, 효과를 보려면 이후의 업데이트 패턴에서 누적되거나 재정리 작업과 함께 적용하는 것이 일반적입니다.

7) 체크리스트: “VACUUM 안 먹힘” 빠른 결론 내리기

• 오래 열린 트랜잭션이 있는가 (pg_stat_activity)
• replication slot이 소비되지 않고 쌓이는가 (pg_replication_slots)
• autovacuum이 backlog 상태인가 (pg_stat_user_tables의 last_autovacuum와 n_dead_tup)
• 인덱스 비중이 과도한가 (pg_indexes_size)
• 정말 필요한 것은 파일 축소인가, 성능 회복인가

bloat 진단과 실제 조치(특히 stuck 상황) 사례 중심으로 더 보고 싶다면 PostgreSQL VACUUM 안 돌 때 bloat 진단·해결도 함께 읽어보면 문제를 더 빨리 좁힐 수 있습니다.

8) 마무리

VACUUM이 “안 먹히는” 것처럼 보일 때는 대개 다음 중 하나입니다.

• 제거할 수 없는 dead tuple이 쌓이는 구조(긴 트랜잭션, slot)
• autovacuum이 너무 늦게, 너무 약하게 도는 설정
• 인덱스/테이블 bloat의 성격에 맞지 않는 처방

먼저 진단 쿼리로 원인을 수치화하고, 테이블별로 autovacuum 임계치를 낮춰 backlog를 줄인 뒤, 필요한 곳에만 REINDEX CONCURRENTLY나 pg_repack 같은 재정리 도구를 쓰는 것이 운영에서 가장 안전하고 효과적인 접근입니다.