PostgreSQL VACUUM 안 도는 이유와 튜닝 체크리스트

서버가 느려지고 테이블이 비대해졌는데 VACUUM이 전혀 동작하지 않는 것처럼 보일 때가 있습니다. 실제로는 (1) autovacuum이 조건을 만족하지 못해 시작조차 안 했거나, (2) 시작했지만 락/리소스/설정 때문에 진행이 지연되거나, (3) “VACUUM이 해결해 줄 문제”가 아닌데 VACUUM만 바라보고 있는 경우가 많습니다.

이 글은 “왜 VACUUM이 안 도는가”를 관측 가능한 지표로 분해하고, 바로 적용할 수 있는 튜닝 체크리스트를 제공합니다. (원인별 상세 해법이 더 필요하면 PostgreSQL VACUUM 안 도는 이유 7가지와 해법도 함께 참고하세요.)

먼저 정리: VACUUM이 ‘안 돈다’는 말의 3가지 의미

1) autovacuum이 아예 트리거되지 않음

• dead tuple이 쌓여도 임계치(threshold) 를 넘지 않아 시작이 안 될 수 있습니다.
• autovacuum 자체가 꺼져 있거나, 테이블 단위로 비활성화 되어 있을 수 있습니다.

2) autovacuum이 돌긴 도는데 너무 느림/자주 중단됨

• I/O 제한, cost delay, worker 부족
• 긴 트랜잭션 때문에 dead tuple이 “제거 불가” 상태로 남음
• VACUUM (FULL)을 기대했는데 일반 VACUUM만 보고 “왜 공간이 안 줄지?” 오해

3) VACUUM으로 해결되지 않는 문제를 VACUUM으로 풀려 함

• 인덱스 bloat가 심하면 VACUUM만으로는 디스크가 줄지 않습니다.
• 통계가 오래되어 플래너가 잘못된 계획을 선택하면 ANALYZE가 더 중요할 수 있습니다.

진단 0단계: 지금 autovacuum이 살아있는지 확인

서버 설정 확인

SHOW autovacuum;
SHOW autovacuum_max_workers;
SHOW autovacuum_naptime;
SHOW log_autovacuum_min_duration;

• autovacuum=on이 아니면 게임 끝입니다.
• log_autovacuum_min_duration을 예: 1s 또는 0으로 잠시 낮추면 “돌고 있는지”가 로그로 보입니다(운영에서는 로그량 주의).

지금 돌고 있는 VACUUM 작업 확인

SELECT
  pid,
  datname,
  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 pid;

• 결과가 비어있다고 해서 “VACUUM이 안 돈다”는 뜻은 아닙니다. 시작 조건 미충족일 수 있습니다.

진단 1단계: 테이블별로 ‘왜 트리거가 안 걸리는지’ 보기

autovacuum은 기본적으로 다음 조건을 만족할 때 실행됩니다.

• dead_tuples > autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor * reltuples

즉, 테이블이 커질수록 scale factor 때문에 “상당히 많이” 죽어야 시작합니다.

테이블별 vacuum/analyze 상태 요약

SELECT
  n.nspname AS schema,
  c.relname AS table,
  s.n_live_tup,
  s.n_dead_tup,
  s.last_autovacuum,
  s.last_vacuum,
  s.last_autoanalyze,
  s.last_analyze
FROM pg_stat_user_tables s
JOIN pg_class c ON c.oid = s.relid
JOIN pg_namespace n ON n.oid = c.relnamespace
ORDER BY s.n_dead_tup DESC
LIMIT 50;

여기서 확인할 것:

• n_dead_tup이 많은데도 last_autovacuum이 오래되었으면 “트리거/리소스/락” 중 하나입니다.
• last_autoanalyze가 오래되면 계획이 틀어져 성능이 악화될 수 있습니다.

autovacuum이 테이블에서 꺼져 있는지

SELECT
  n.nspname AS schema,
  c.relname AS table,
  c.reloptions
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = 'r'
  AND c.reloptions IS NOT NULL
  AND array_to_string(c.reloptions, ',') ILIKE '%autovacuum%'
ORDER BY 1,2;

• autovacuum_enabled=false 같은 옵션이 박혀 있으면 해당 테이블은 autovacuum이 안 돕니다.

진단 2단계: “긴 트랜잭션”이 VACUUM을 막는지 확인

VACUUM은 dead tuple을 치우려면 그 dead tuple을 볼 수 있는 가장 오래된 스냅샷이 지나가야 합니다. 즉, 오래 열린 트랜잭션/세션이 있으면 VACUUM이 작업을 해도 “지울 수 없는” 상태가 됩니다.

오래 열린 트랜잭션 찾기

SELECT
  pid,
  usename,
  datname,
  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_start ASC
LIMIT 30;

운영에서 자주 보는 패턴:

• 애플리케이션이 트랜잭션을 열고 커밋을 늦게 함(커넥션 풀/ORM)
• 배치가 REPEATABLE READ로 오래 실행
• idle in transaction 세션이 방치됨

이 경우 VACUUM이 “도는 것처럼 보이는데 bloat가 줄지 않는” 상황이 발생합니다.

진단 3단계: 락/경합으로 VACUUM이 대기 중인지 확인

일반 VACUUM은 강한 락을 오래 잡지 않지만, 특정 DDL과 충돌하거나, VACUUM FULL/REINDEX는 락 이슈가 큽니다.

락 대기 확인

SELECT
  a.pid,
  a.state,
  a.wait_event_type,
  a.wait_event,
  l.locktype,
  l.mode,
  l.granted,
  a.query
FROM pg_stat_activity a
JOIN pg_locks l ON l.pid = a.pid
WHERE a.query ILIKE '%vacuum%'
ORDER BY a.pid;

• granted=false가 보이면 대기 중입니다.
• 대기 원인을 찾으려면 blocking PID를 추적해야 합니다.

진단 4단계: 리소스/설정 때문에 “너무 느린” 케이스

(1) autovacuum worker 수가 부족

• 큰 DB에서 autovacuum_max_workers=3 기본값은 흔히 부족합니다.
• 테이블이 많고 write가 많은 시스템에서는 vacuum backlog가 생깁니다.

(2) cost 기반 지연이 과도

• autovacuum_vacuum_cost_limit, autovacuum_vacuum_cost_delay가 보수적으로 설정되어 있으면 I/O를 아껴서 느려집니다.

(3) I/O 병목

• 스토리지가 느리거나, 체크포인트/백업/배치와 경합하면 vacuum이 밀립니다.

vacuum 관련 로그를 켜서 “실행은 되는지/얼마나 걸리는지” 관측

-- 세션 단위로 테스트할 때
SET log_autovacuum_min_duration = '0';

-- 또는 postgresql.conf에서 전역 설정 후 reload
-- log_autovacuum_min_duration = 0

로그에서 확인 포인트:

• 어떤 테이블이 얼마나 자주 vacuum 되는지
• vacuum이 항상 특정 시간대에만 몰리는지(경합)

핵심 오해: “VACUUM 했는데 디스크가 왜 안 줄어?”

일반 VACUUM은 dead tuple을 재사용 가능 상태로 표시할 뿐, OS에 파일을 돌려주지 않습니다.

• 파일 크기를 줄이려면 VACUUM FULL(강한 락/테이블 rewrite) 또는 pg_repack 같은 접근이 필요합니다.
• 인덱스 bloat는 REINDEX가 필요할 수 있습니다.

bloat/튜닝 관점은 PostgreSQL VACUUM·AUTOVACUUM 튜닝 - bloat로 느려질 때에서 더 깊게 다룹니다.

튜닝 체크리스트 (운영 적용 순서 중심)

1) “관측” 먼저: 최소한의 메트릭/뷰를 대시보드에 올리기

다음은 거의 필수입니다.

• pg_stat_user_tables.n_dead_tup, last_autovacuum, last_autoanalyze
• pg_stat_progress_vacuum (진행률)
• pg_stat_activity에서 오래 열린 트랜잭션 수
• DB 로그의 autovacuum duration

간단한 “상위 dead tuple 테이블” 뷰를 만들어도 효과가 큽니다.

CREATE OR REPLACE VIEW ops_top_dead_tuples AS
SELECT
  now() AS observed_at,
  n.nspname AS schema,
  c.relname AS table,
  s.n_live_tup,
  s.n_dead_tup,
  s.last_autovacuum,
  s.last_autoanalyze
FROM pg_stat_user_tables s
JOIN pg_class c ON c.oid = s.relid
JOIN pg_namespace n ON n.oid = c.relnamespace
ORDER BY s.n_dead_tup DESC;

2) autovacuum이 “시작”할 수 있게 임계치 재조정

대형 테이블에서 기본 autovacuum_vacuum_scale_factor=0.2는 너무 큽니다.

• write-heavy 테이블은 테이블 단위로 낮추는 것이 일반적입니다.

테이블 단위 권장 예시

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

주의:

• scale factor를 낮추면 vacuum 빈도가 올라가 I/O가 증가할 수 있습니다. 아래 3)~4)와 같이 조합해서 봐야 합니다.

3) backlog가 있다면 worker/스케줄링부터

• autovacuum_max_workers를 늘려 병렬성을 확보합니다.
• autovacuum_naptime을 줄이면 더 자주 체크합니다(너무 줄이면 오버헤드).

예:

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

4) “너무 느리다”면 cost 파라미터 조정

I/O 여유가 있는 환경이라면 autovacuum이 더 공격적으로 돌도록 조정할 수 있습니다.

ALTER SYSTEM SET autovacuum_vacuum_cost_limit = '2000';
ALTER SYSTEM SET autovacuum_vacuum_cost_delay = '2ms';
SELECT pg_reload_conf();

• 정답은 없습니다. 스토리지 성능, 동시 쿼리, 피크 타임에 따라 다릅니다.

5) 긴 트랜잭션을 “제거”하는 운영 규칙 만들기

기술 튜닝보다 효과가 큰 경우가 많습니다.

• 애플리케이션에서 트랜잭션 범위를 최소화
• 커넥션 풀에서 idle in transaction 감지/킬
• 배치 작업은 커밋을 자주 하거나, 읽기 일관성이 필요 없다면 격리수준 재검토

DB 레벨에서 감시 쿼리를 주기 실행해도 좋습니다.

SELECT pid, usename, now() - xact_start AS xact_age, state, left(query, 120)
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
  AND now() - xact_start > interval '10 minutes'
ORDER BY xact_start;

6) analyze가 밀리면 vacuum만 해서는 체감 개선이 없다

• VACUUM (ANALYZE) 또는 autovacuum analyze 튜닝이 필요합니다.
• 특히 대량 업데이트/삭제 이후 플래너 통계가 틀어지면 쿼리 성능이 급락합니다.

수동으로 빠르게 회복:

VACUUM (ANALYZE, VERBOSE) public.events;

7) bloat가 심하면 “재작성(rewrite)” 전략을 분리

• 일반 vacuum은 공간 반환이 목적이 아니라 “재사용 가능”이 목적입니다.
• 인덱스/테이블 bloat가 이미 심각하면 다음을 검토합니다.
  • REINDEX CONCURRENTLY
  • pg_repack (운영 영향 최소화)
  • (최후) VACUUM FULL (강한 락, 긴 다운타임 가능)

실전 점검 시나리오: 15분 안에 원인 좁히기

1) 지금 진행 중인 vacuum이 있는가?

• pg_stat_progress_vacuum 확인

2) 상위 dead tuple 테이블은 무엇인가?

• pg_stat_user_tables에서 n_dead_tup 상위 확인

3) 해당 테이블이 autovacuum 대상인가?

• reloptions에서 autovacuum_enabled=false 여부 확인

4) 오래 열린 트랜잭션이 있는가?

• pg_stat_activity에서 xact_age 긴 세션 확인

5) worker/비용 파라미터로 인해 backlog가 생겼는가?

• vacuum 로그/실행 빈도, autovacuum_max_workers 점검

이 흐름대로 보면 “안 돈다”가 설정 문제인지, 조건 문제인지, 경합 문제인지 빠르게 갈라집니다.

결론: VACUUM 문제는 ‘설정’보다 ‘관측+워크로드’가 먼저다

VACUUM이 안 도는 것처럼 보일 때, 가장 흔한 원인은 다음 두 가지입니다.

1. 트리거 임계치가 워크로드에 비해 너무 커서 시작 자체가 늦다
2. 긴 트랜잭션/idle in transaction이 dead tuple 정리를 막는다

그 다음이 worker/cost/I/O 튜닝입니다. 먼저 pg_stat_user_tables, pg_stat_progress_vacuum, pg_stat_activity를 기준으로 “지금 무슨 일이 벌어지는지”를 수치로 확인한 뒤, 테이블 단위 autovacuum 설정과 운영 규칙(긴 트랜잭션 제거)을 결합하면 대부분의 ‘VACUUM이 안 돈다’ 이슈는 안정적으로 해결됩니다.

추가로 원인별 처방을 더 촘촘히 보고 싶다면 PostgreSQL VACUUM 안 도는 이유 7가지와 해법, bloat 중심 튜닝은 PostgreSQL VACUUM·AUTOVACUUM 튜닝 - bloat로 느려질 때를 권합니다.