PostgreSQL VACUUM 안됨? dead tuple·autovacuum 튜닝

서버가 느려지고 디스크가 계속 차는데 VACUUM은 이미 돌고 있다면, 대부분의 문제는 dead tuple 자체보다 “왜 회수가 안 되는가”에 있습니다. PostgreSQL은 MVCC 구조라서 UPDATE와 DELETE가 곧바로 공간을 되돌려주지 않습니다. 대신 기존 행은 dead tuple로 남고, VACUUM이 이를 정리해 재사용 가능한 공간으로 표시합니다.

그런데 운영에서 흔히 겪는 현상은 다음과 같습니다.

• VACUUM을 수동으로 돌려도 n_dead_tup가 크게 줄지 않는다
• autovacuum 로그는 보이는데 테이블이 계속 비대해진다
• 특정 테이블만 꾸준히 bloat가 쌓인다
• pg_stat_activity에 오래 열린 트랜잭션이 있고, vacuum이 멈춘 듯 보인다

이 글은 “VACUUM 안됨”으로 보이는 상황을 재현 가능한 진단 순서로 쪼개고, autovacuum을 테이블 특성에 맞게 조정하는 방법을 정리합니다.

문제가 더 광범위하게 느껴진다면, 원인별 체크리스트를 확장한 글인 PostgreSQL VACUUM 안 도는 이유와 튜닝 체크리스트도 함께 참고하세요.

dead tuple이 생기는 원리와 “안 줄어드는” 이유

PostgreSQL의 UPDATE는 “제자리 수정”이 아니라 새 버전을 쓰고 이전 버전을 남깁니다. 이전 버전은 더 이상 어떤 트랜잭션에서도 보이지 않을 때 dead tuple이 됩니다.

여기서 핵심은 “어떤 트랜잭션에서도 보이지 않을 때”입니다. 즉, 다음이 있으면 vacuum이 지우고 싶어도 못 지웁니다.

• 오래 열린 트랜잭션(특히 idle in transaction)
• 오래된 스냅샷을 잡고 있는 세션(리포트 쿼리, ETL, 커넥션 풀의 잘못된 사용)
• 논리 복제 슬롯, hot_standby_feedback로 인해 필요한 튜플이 보존되는 상황

이때 vacuum은 dead tuple을 “회수”할 수 없고, 그 결과 n_dead_tup가 줄지 않거나 bloat가 계속 쌓입니다.

1단계: 지금 vacuum이 실제로 돌고 있는지 확인

먼저 “안 돈다”와 “도는데 효과가 없다”를 분리해야 합니다.

현재 실행 중인 vacuum 확인

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;

• 결과가 비어 있으면 현재 vacuum 작업이 실행 중이 아닐 수 있습니다
• phase가 scanning heap에서 오래 멈춰 있으면 I/O 병목 또는 동시성 이슈를 의심합니다

autovacuum이 최근에 언제 돌았는지 확인

SELECT
  relname,
  n_live_tup,
  n_dead_tup,
  last_vacuum,
  last_autovacuum,
  last_analyze,
  last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;

여기서 last_autovacuum이 계속 갱신되는데도 n_dead_tup가 잘 안 줄면, 다음 단계(트랜잭션/스냅샷/설정)를 봐야 합니다.

2단계: vacuum이 회수하지 못하게 막는 “오래 열린 트랜잭션” 찾기

가장 흔한 1순위 원인입니다. vacuum은 “아직 누군가 볼 수 있는” 튜플을 지울 수 없습니다.

오래 열린 트랜잭션과 idle in transaction 찾기

SELECT
  pid,
  usename,
  application_name,
  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 30;

• state가 idle in transaction이고 xact_age가 길면 거의 확정적으로 문제를 만듭니다
• 커넥션 풀에서 트랜잭션을 열고 커밋을 안 하는 코드 패턴이 있는지 점검하세요

어떤 테이블의 vacuum이 막히는지 간접 추정

vacuum이 지우지 못하는 튜플이 많으면 pg_stat_user_tables.n_dead_tup가 누적됩니다. 또한 “가장 오래된 xmin”이 뒤로 밀리지 않으면 freeze 관련 경고도 나타납니다.

가능하면 DB 레벨에서 다음도 확인합니다.

SELECT
  datname,
  age(datfrozenxid) AS xid_age
FROM pg_database
ORDER BY xid_age DESC;

xid_age가 비정상적으로 크면, 오래 열린 트랜잭션이 freeze 진행을 막고 있을 가능성이 큽니다.

3단계: autovacuum이 “너무 늦게” 돌도록 설정돼 있지 않은지 확인

autovacuum은 기본값이 무난하지만, 쓰기량이 많은 테이블에는 부족한 경우가 많습니다. 트리거 조건은 크게 두 가지입니다.

• 임계치: autovacuum_vacuum_threshold
• 비율: autovacuum_vacuum_scale_factor

테이블이 커질수록 “비율” 조건 때문에 vacuum이 지나치게 늦게 시작될 수 있습니다.

특정 테이블에만 autovacuum을 더 공격적으로 걸기

예를 들어 대형 이벤트 테이블에서 업데이트/삭제가 빈번하면 scale factor를 낮추는 편이 효과적입니다.

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

• scale factor를 낮추면 더 자주 vacuum이 실행돼 bloat 누적을 줄입니다
• 단, vacuum 빈도가 늘면 I/O와 CPU 사용량이 증가하므로 운영 시간대와 리소스 여유를 고려해야 합니다

autovacuum worker 수와 비용 파라미터 점검

autovacuum이 “해야 할 일이 많은데” 처리량이 부족하면 backlog가 쌓입니다. 대표적으로 다음을 봅니다.

• autovacuum_max_workers
• autovacuum_naptime
• autovacuum_vacuum_cost_limit
• autovacuum_vacuum_cost_delay

예시(서버 사양에 따라 다르게 접근해야 함):

-- 현재 설정 확인
SHOW autovacuum_max_workers;
SHOW autovacuum_naptime;
SHOW autovacuum_vacuum_cost_limit;
SHOW autovacuum_vacuum_cost_delay;

튜닝 방향은 보통 이렇습니다.

• backlog가 쌓이면 autovacuum_max_workers를 늘려 병렬 처리량을 확보
• vacuum이 너무 “착하게” 쉬고 있으면 cost limit을 늘리거나 delay를 줄여 처리량을 높임

다만 cost 파라미터를 과하게 올리면 사용자 쿼리 I/O를 압박할 수 있으니, 피크 시간대를 피해 단계적으로 조정하고 모니터링해야 합니다.

4단계: VACUUM과 bloat의 관계를 오해하고 있지 않은지 확인

중요한 포인트 하나는, VACUUM은 “디스크 파일 크기를 줄이는 작업”이 아니라는 점입니다.

• VACUUM은 dead tuple을 “재사용 가능”으로 표시합니다
• 테이블 파일 자체 크기를 OS에 반환하려면 VACUUM FULL 또는 CLUSTER 같은 재작성 작업이 필요합니다

즉, VACUUM을 했는데 디스크 사용량이 그대로라고 해서 실패한 게 아닐 수 있습니다. 대신 다음을 확인하세요.

• 쿼리 성능이 회복됐는지(특히 인덱스 스캔과 힙 접근 패턴)
• n_dead_tup가 줄거나, 최소한 증가 속도가 완화됐는지

bloat가 심하면 VACUUM FULL을 고려하되, 락을 이해하기

VACUUM FULL은 테이블을 재작성하고 보통 강한 락이 필요합니다. 운영 중 바로 적용하면 장애로 이어질 수 있습니다.

-- 주의: 운영 트래픽에 영향이 큼
VACUUM (FULL, ANALYZE) public.events;

대안으로는

• 파티셔닝으로 “문제 구간만” 재작성 가능하게 만들기
• 오프피크에 pg_repack 같은 도구를 검토하기

같은 접근이 있습니다.

5단계: 인덱스 bloat와 vacuum의 한계

테이블의 dead tuple이 줄어도 인덱스는 별개로 비대해질 수 있습니다. 특히 업데이트가 많은 테이블은 인덱스 페이지가 지저분해지기 쉽습니다.

인덱스 스캔 패턴과 함께 확인

SELECT
  schemaname,
  relname,
  idx_scan,
  idx_tup_read,
  idx_tup_fetch
FROM pg_stat_user_tables
ORDER BY idx_scan DESC
LIMIT 20;

인덱스 bloat를 정리하려면 REINDEX가 필요할 수 있습니다.

-- 특정 인덱스만
REINDEX INDEX CONCURRENTLY public.events_created_at_idx;

-- 테이블의 모든 인덱스
REINDEX TABLE CONCURRENTLY public.events;

CONCURRENTLY는 시간이 더 걸리지만 락 영향이 줄어 운영에서 현실적인 선택지입니다.

6단계: freeze와 wraparound 리스크까지 같이 보기

vacuum 이슈는 성능 문제로 시작하지만, 방치하면 트랜잭션 ID wraparound로 “쓰기 불가” 상황까지 갈 수 있습니다. 다음 신호를 정기적으로 확인하세요.

SELECT
  datname,
  age(datfrozenxid) AS xid_age,
  age(datminmxid) AS mxid_age
FROM pg_database
ORDER BY xid_age DESC;

또한 테이블 단에서도 확인할 수 있습니다.

SELECT
  relname,
  age(relfrozenxid) AS xid_age
FROM pg_class
WHERE relkind = 'r'
ORDER BY xid_age DESC
LIMIT 30;

xid age가 커지는 속도가 빠르면, autovacuum이 “청소”뿐 아니라 “freeze”를 제때 못 하고 있을 가능성이 있습니다.

7단계: 운영에서 자주 놓치는 실전 팁

autovacuum 로그를 켜서 “왜 늦는지” 근거 확보

설정 예시는 다음과 같습니다.

• log_autovacuum_min_duration을 낮춰 로그를 남긴다

SHOW log_autovacuum_min_duration;

값을 예를 들어 1000(ms) 정도로 두면 1초 이상 걸린 autovacuum이 기록되어, 어떤 테이블이 병목인지 추적이 쉬워집니다.

vacuum과 analyze를 같이 보지 않으면 플랜이 망가진다

dead tuple 정리도 중요하지만, 통계가 낡으면 쿼리 플래너가 잘못된 선택을 합니다. 업데이트/삭제가 많은 테이블은 autovacuum_analyze_scale_factor도 함께 낮추는 편이 좋습니다.

애플리케이션 장애처럼 보일 때는 “원인 추적 루틴”을 재사용

vacuum 지연이 앱 지연으로 번지면, DB만 보다가 끝나는 경우가 많습니다. 시스템 전반의 병목을 체계적으로 좁히는 방식은 다른 운영 이슈에도 동일하게 적용됩니다. 예를 들어 서비스가 불안정하게 재시작되는 상황에서 원인을 체크리스트로 좁히는 방법은 systemd 서비스가 자꾸 재시작될 때 진단 방법 글의 접근과 유사합니다.

테이블 유형별 autovacuum 튜닝 가이드

1) 대형 테이블 + 잦은 UPDATE/DELETE

• 목표: bloat 누적 방지, vacuum을 더 자주
• 전략: scale factor를 낮추고, 필요 시 테이블별 cost limit 상향

ALTER TABLE public.orders
SET (
  autovacuum_vacuum_scale_factor = 0.01,
  autovacuum_vacuum_threshold = 10000,
  autovacuum_vacuum_cost_limit = 4000
);

2) INSERT 위주 + 거의 삭제 없음

• 목표: analyze를 자주, vacuum은 상대적으로 덜 중요

ALTER TABLE public.pageviews
SET (
  autovacuum_analyze_scale_factor = 0.02,
  autovacuum_analyze_threshold = 5000
);

3) 단기 데이터 테이블(롤링 삭제, TTL)

• 목표: 파티셔닝이 가장 큰 효과
• 전략: 오래된 파티션 DROP이 최고의 vacuum

예를 들어 일 단위 파티션이면, 만료 데이터는 DELETE 대신 파티션 drop으로 제거해 dead tuple 자체를 줄입니다.

최소 진단 체크리스트(요약)

• pg_stat_progress_vacuum으로 실제 vacuum 진행 여부 확인
• pg_stat_activity에서 오래 열린 트랜잭션, 특히 idle in transaction 제거
• pg_stat_user_tables에서 dead tuple 상위 테이블 식별
• 대형 테이블은 테이블별 autovacuum_vacuum_scale_factor를 낮춰 더 자주 vacuum
• vacuum이 디스크를 줄이지 않는다는 점을 전제로 bloat는 VACUUM FULL 또는 REINDEX를 별도로 검토
• age(datfrozenxid)로 wraparound 리스크 모니터링

마무리

VACUUM이 안 된다는 말은 실제로는 세 가지 중 하나인 경우가 많습니다.

1. vacuum이 실행 자체를 못 한다(워커 부족, 설정, 락)
2. 실행은 하는데 지울 수가 없다(오래 열린 트랜잭션, 스냅샷)
3. 지웠는데 기대한 결과가 다르다(디스크 반환은 별개, 인덱스 bloat)

위 순서대로 확인하면 “감”이 아니라 근거 기반으로 원인을 좁힐 수 있고, 테이블별 autovacuum 튜닝으로 재발도 줄일 수 있습니다.