beautifulsoups.dev
Published on

PostgreSQL VACUUM 안됨? autovacuum 튜닝 실전

Authors

서버 운영하다 보면 VACUUM을 돌렸는데도 디스크가 줄지 않거나, 특정 테이블만 계속 비대해져서 “VACUUM 안됨”처럼 느껴지는 순간이 옵니다. PostgreSQL의 VACUUM은 공간을 즉시 OS에 반환하는 작업이 아니라, 기본적으로는 Dead tuple을 정리해 재사용 가능하게 만들고(가시성 맵/프리징 포함), 통계를 갱신하는 작업입니다. 그래서 “안 됨”의 상당수는 실제로는 기대한 효과가 달랐거나, autovacuum이 임계치에 못 미쳐 트리거되지 않거나, 락/IO/동시성 제약으로 진행이 느리거나, 특정 테이블 설정이 잘못되어 아예 스킵되는 케이스입니다.

아래는 현장에서 자주 겪는 패턴을 기준으로, 어떤 지표를 보고 어떤 설정을 어떻게 바꾸면 되는지 정리한 실전 가이드입니다.

> 인덱스/테이블 bloat 진단과 REINDEX까지 포함한 더 넓은 관점은 PostgreSQL 인덱스가 느릴 때 - Bloat·VACUUM·REINDEX도 함께 참고하면 좋습니다.

1) “VACUUM 안됨” 착시를 만드는 대표 원인

1.1 디스크가 안 줄어드는 건 정상일 수 있다

  • VACUUM(일반)은 페이지 내 빈 공간을 재사용 가능하게 만들지만, 파일 크기를 줄이진 않습니다.
  • 파일 크기를 줄여 OS에 반환하려면 보통 VACUUM FULL 또는 CLUSTER가 필요합니다(둘 다 테이블 재작성).
  • 다만 VACUUM FULL은 긴 배타적 락이 걸려 운영 환경에선 위험합니다.

기대효과를 분리해서 생각하세요.

  • 쿼리 성능/인덱스 효율 저하 → bloat 진단 후 REINDEX(CONCURRENTLY) 또는 재작성 전략 검토
  • 트랜잭션 ID wraparound 위험 → freeze 관련 설정/모니터링
  • 디스크 반환 → VACUUM FULL(다운타임) 또는 파티셔닝/아카이빙/재작성 설계

1.2 autovacuum이 “안 도는 것처럼” 보이는 이유

  • 임계치(threshold)가 너무 높아 트리거가 잘 안 됨
  • autovacuum worker 수가 부족하거나, 한 worker가 큰 테이블에 묶여 다른 테이블이 밀림
  • autovacuum_vacuum_cost_limit/cost_delay가 보수적이라 너무 느리게 진행
  • autovacuum이 해당 테이블에서 비활성화되어 있음
  • 장기 트랜잭션/복제 슬롯 등으로 인해 dead tuple이 정리 불가(vacuum은 돌지만 효과가 제한)

2) 지금 상태를 “숫자”로 확인하는 진단 쿼리

2.1 테이블별 Dead tuple/수정량/마지막 vacuum 시간

SELECT
  schemaname,
  relname,
  n_live_tup,
  n_dead_tup,
  n_mod_since_analyze,
  last_vacuum,
  last_autovacuum,
  last_analyze,
  last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 30;
  • n_dead_tup가 큰데 last_autovacuum이 오래됐다 → autovacuum 트리거/동시성/자원 문제 가능
  • n_mod_since_analyze가 큰데 analyze가 안 돈다 → 플래너 통계가 낡아 성능 저하로 이어짐

2.2 autovacuum이 실제로 돌고 있는지(진행 상황)

SELECT
  a.pid,
  a.datname,
  a.usename,
  a.state,
  a.query,
  now() - a.query_start AS runtime,
  v.relid::regclass AS relation,
  v.phase,
  v.heap_blks_scanned,
  v.heap_blks_total,
  v.index_vacuum_count,
  v.max_dead_tuples
FROM pg_stat_activity a
LEFT JOIN pg_stat_progress_vacuum v
  ON a.pid = v.pid
WHERE a.query ILIKE 'autovacuum:%'
ORDER BY runtime DESC;
  • phase가 오래 멈춰 있거나 heap_blks_scanned가 거의 안 늘면 IO 병목/비용 제한/경합을 의심

2.3 “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, 120) AS query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY xact_age DESC
LIMIT 20;
  • 장기 트랜잭션이 있으면 vacuum이 dead tuple을 “정리”할 수 없고, 결과적으로 bloat가 쌓입니다.
  • 애플리케이션에서 idle in transaction이 많은지도 함께 보세요.

2.4 테이블별 autovacuum 설정(테이블 단위 오버라이드 확인)

SELECT
  n.nspname AS schema,
  c.relname,
  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
ORDER BY 1, 2;
  • autovacuum_enabled=false가 박혀 있는 테이블이 의외로 흔합니다(과거 장애 대응으로 꺼놓고 잊음).

3) autovacuum 트리거(임계치) 이해: 왜 큰 테이블이 특히 문제인가

autovacuum은 아래 조건을 만족하면 vacuum/analyze를 트리거합니다(단순화):

  • Vacuum: dead_tuples > autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor * reltuples
  • Analyze: mod_tuples > autovacuum_analyze_threshold + autovacuum_analyze_scale_factor * reltuples

즉 테이블이 클수록(scale_factor) 필요한 변경량이 기하급수적으로 커집니다.

  • 예: 1억 행 테이블, scale_factor=0.2라면 2천만 행이 죽어야 vacuum 트리거… 사실상 너무 늦습니다.

3.1 큰 테이블은 “테이블 단위”로 scale_factor를 낮추는 게 정석

전역 설정을 공격적으로 바꾸면 작은 테이블까지 과도하게 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
);
  • 변경량이 많은 대형 테이블(로그/이벤트/큐 성격)은 특히 효과가 큽니다.
  • threshold는 너무 낮추면 자주 돌고, 너무 높으면 늦게 돕니다. “변경량/시간당 TPS” 기준으로 맞추는 게 좋습니다.

4) autovacuum이 느리게 도는 경우: cost 기반 제한 풀기

PostgreSQL은 autovacuum이 시스템을 과도하게 때리지 않도록 cost 기반 throttling을 겁니다. IO가 빡빡한 환경(EBS gp 계열, 공유 스토리지)에서는 이 제한 때문에 vacuum이 “거의 진행되지 않는 것”처럼 보일 수 있습니다.

4.1 전역 튜닝 예시(보수적으로 시작)

# postgresql.conf
autovacuum_max_workers = 6
autovacuum_naptime = '10s'

# vacuum이 너무 느릴 때 완화
autovacuum_vacuum_cost_limit = 2000
autovacuum_vacuum_cost_delay = '2ms'

# analyze도 함께 적절히
autovacuum_analyze_scale_factor = 0.05
autovacuum_vacuum_scale_factor = 0.05
  • autovacuum_max_workers: 큰 테이블이 여러 개면 worker가 부족해 backlog가 생깁니다.
  • naptime: 트리거 체크 주기. 너무 길면 “왜 안 돌지?”가 됩니다.
  • cost 파라미터는 워크로드/스토리지에 따라 달라서, 변경 후에는 IO/latency를 꼭 같이 모니터링해야 합니다.

4.2 특정 테이블만 더 빠르게(테이블 단위 cost_limit)

ALTER TABLE public.events SET (
  autovacuum_vacuum_cost_limit = 5000
);
  • 대형 테이블 하나가 병목이라면 전역이 아니라 해당 테이블만 가속하는 편이 안전합니다.

5) VACUUM이 “돌다가 멈추는” 것처럼 보일 때: 락/경합 체크

일반 VACUUM은 강한 락을 오래 잡지 않지만, 다음 상황에선 진행이 지연될 수 있습니다.

  • 테이블 확장/페이지 접근이 IO 병목
  • 인덱스 vacuum 단계에서 경합
  • autovacuum이 다른 작업(체크포인트, 대량 쓰기)과 경쟁

5.1 락 대기 확인

SELECT
  a.pid,
  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;
  • VACUUM FULL은 별개로, 운영 중에는 웬만하면 피하고 윈도우를 잡아 실행하세요.

6) vacuum은 했는데 bloat가 계속 쌓이는 구조적 원인들

6.1 UPDATE가 많은 테이블(특히 HOT update가 깨지는 스키마)

  • PostgreSQL의 MVCC 특성상 UPDATE는 사실상 DELETE+INSERT에 가깝습니다.
  • 인덱스가 많거나, 자주 갱신되는 컬럼이 인덱스에 포함되면 HOT update가 깨져 bloat가 빨리 쌓입니다.

대응:

  • 인덱스 재검토(정말 필요한 것만)
  • 자주 변하는 컬럼을 인덱스에서 분리
  • 파티셔닝/아카이빙(시간 기반)

6.2 장기 트랜잭션/idle in transaction

  • vacuum이 청소할 수 있는 dead tuple의 “최소 XID”가 막히면, vacuum은 돌아도 효과가 제한됩니다.
  • 애플리케이션 커넥션 풀에서 트랜잭션을 열어둔 채로 오래 대기하는 패턴이 흔합니다.

대응:

  • 애플리케이션에서 트랜잭션 범위 축소
  • idle_in_transaction_session_timeout 도입 검토
idle_in_transaction_session_timeout = '60s'

6.3 복제 슬롯/논리 복제 사용 시

  • 논리 복제 슬롯이 오래 소비되지 않으면 카탈로그/튜플 정리가 지연될 수 있습니다.
  • pg_replication_slots와 pg_stat_replication을 함께 점검하세요.
SELECT slot_name, slot_type, active, restart_lsn, confirmed_flush_lsn
FROM pg_replication_slots
ORDER BY active DESC, slot_name;

7) 운영에서 자주 쓰는 “안전한” 수동 VACUUM/ANALYZE 패턴

7.1 특정 테이블만 우선 처리

VACUUM (VERBOSE, ANALYZE) public.events;
  • VERBOSE로 진행 로그를 남기면 “정말로 뭐가 얼마나 정리됐는지”가 보입니다.

7.2 전체 DB를 무작정 VACUUM FULL 하지 말 것

  • VACUUM FULL은 테이블 재작성 + 인덱스 재작성 + 강한 락
  • 장애 대응으로 급히 실행했다가 애플리케이션이 멈추는 경우가 많습니다.

대안:

  • bloat가 심한 테이블만 선별
  • 가능하면 REINDEX (CONCURRENTLY) / 파티션 교체 / 재적재 전략

8) 튜닝 체크리스트(우선순위 순)

  1. 장기 트랜잭션 존재 여부: dead tuple이 줄지 않는 1순위 원인
  2. 큰 테이블의 scale_factor 낮추기: 전역보다 테이블 단위 권장
  3. autovacuum worker 수/nap time 조정: backlog 제거
  4. cost 제한 완화: 너무 느리게 돌면 결국 못 따라감
  5. 테이블별 autovacuum 비활성화 여부 확인: reloptions 점검
  6. 인덱스 과다/UPDATE 패턴 개선: 구조적으로 bloat를 덜 만들기

9) 예시: “events 테이블만 계속 커져요” 케이스 처방전

  • 증상: events는 초당 insert/update가 많고, 디스크가 계속 증가. autovacuum 로그는 가끔 보이지만 효과 미미.
  • 진단:
    • pg_stat_user_tables.n_dead_tup 상위
    • last_autovacuum는 뜨지만 dead tuple 유지
    • pg_stat_activity에 장기 트랜잭션이 가끔 존재

처방:

  1. 장기 트랜잭션 제거(애플리케이션/배치 점검)
  2. events에만 scale_factor/threshold 낮추기
  3. 해당 테이블 autovacuum cost_limit 상향
  4. 인덱스 재검토(특히 자주 갱신되는 컬럼이 인덱스에 있으면 정리)
-- 1) 테이블 단위 autovacuum 민감도 조정
ALTER TABLE public.events SET (
  autovacuum_vacuum_scale_factor = 0.02,
  autovacuum_vacuum_threshold = 10000,
  autovacuum_analyze_scale_factor = 0.01,
  autovacuum_analyze_threshold = 10000,
  autovacuum_vacuum_cost_limit = 5000
);

-- 2) 즉시 통계/청소(운영 부하 고려)
VACUUM (ANALYZE) public.events;

10) 마무리: “VACUUM이 안 된다”가 아니라 “정책이 안 맞는다”

PostgreSQL에서 vacuum 문제는 대부분 기능 고장이 아니라 워크로드 대비 정책(임계치/자원/동시성)이 맞지 않는 상태입니다. 특히 대형 테이블은 기본 scale_factor로는 너무 늦게 트리거되기 때문에, 테이블 단위로 민감도를 낮추고(cost 제한 포함) 장기 트랜잭션을 제거하는 것만으로도 체감 성능과 디스크 증가 속도가 크게 달라집니다.

추가로 bloat가 이미 심해진 상태라면 vacuum만으로는 회복이 어렵고, 재작성/인덱스 재구성이 필요할 수 있습니다. 이 경우에는 원인 진단부터 정리한 PostgreSQL 인덱스가 느릴 때 - Bloat·VACUUM·REINDEX를 함께 보면서 “왜 bloat가 생겼는지”까지 같이 해결하는 편이 재발을 막는 최단 경로입니다.

Discuss on Twitter • View on GitHub