PostgreSQL VACUUM 안 먹을 때 - autovacuum 튜닝

서버가 점점 느려지고 디스크는 불어나는데, VACUUM을 수동으로 돌려도 체감이 없을 때가 있습니다. 이때 많은 경우 문제는 “VACUUM이 안 돈다”가 아니라 autovacuum이 기대한 시점/강도로 작동하지 않거나, VACUUM만으로는 회수되지 않는 공간(특히 인덱스 bloat) 이 쌓여 있는 상황입니다. 이 글에서는 “VACUUM 안 먹는 것처럼 보이는” 전형적인 증상을 기준으로, 원인 진단 → autovacuum 튜닝 → 테이블별 처방 → 운영 체크리스트 순으로 정리합니다.

운영 장애를 10분 내에 좁혀가는 방식은 네트워크 이슈 진단 글과도 결이 비슷합니다. 빠른 원인 분해가 필요하다면 EKS Pod는 뜨는데 트래픽 0 - NetPol·SG·CNI 10분 진단처럼 관측 지표→가설→확인 쿼리 흐름을 그대로 적용하면 좋습니다.

1) VACUUM이 “안 먹는 것처럼 보이는” 대표 증상

1-1. 테이블/인덱스 bloat가 계속 증가

• pg_total_relation_size()가 계속 증가
• n_dead_tup(dead tuple)가 줄지 않음
• 인덱스 스캔이 느려지고 캐시 미스가 증가

1-2. autovacuum worker가 거의 안 보이거나, 항상 한두 개만 뜸

• pg_stat_activity에서 autovacuum: VACUUM ...가 드물게 보임
• 또는 항상 같은 테이블만 붙잡고 오래 돎

1-3. “VACUUM 했는데 용량이 안 줄어요”

PostgreSQL의 일반 VACUUM은 테이블 파일을 OS에 반환하지 않습니다.

• dead tuple을 “재사용 가능” 상태로 만들고
• visibility map 등을 갱신해 성능을 회복 하지만 파일 크기 자체를 줄이려면 보통 VACUUM FULL(테이블 rewrite) 또는 pg_repack 같은 접근이 필요합니다. 따라서 “용량이 안 줄었다”는 이유만으로 VACUUM 실패로 판단하면 오진이 잦습니다.

2) 먼저 해야 할 진단: 지금 autovacuum이 왜 못 도는가

2-1. autovacuum이 켜져 있는지, 워커가 부족한지

SHOW autovacuum;
SHOW autovacuum_max_workers;
SHOW autovacuum_naptime;
SHOW maintenance_work_mem;
SHOW autovacuum_work_mem;

• autovacuum = on인지 확인
• autovacuum_max_workers가 너무 작으면(예: 3) 큰 테이블이 몇 개만 있어도 다른 테이블이 굶습니다.

2-2. 어떤 테이블이 죽은 튜플을 쌓고 있는지

SELECT
  schemaname,
  relname,
  n_live_tup,
  n_dead_tup,
  last_autovacuum,
  last_vacuum,
  autovacuum_count,
  vacuum_count
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 30;

여기서 핵심은:

• n_dead_tup 상위 테이블이 last_autovacuum이 오래 전이면 트리거/스케줄 문제가 크고
• last_autovacuum은 최근인데도 n_dead_tup이 계속 높으면 진공이 중간에 멈추거나(락/비용 제한), dead tuple 생성 속도가 더 빠른 상황일 수 있습니다.

2-3. vacuum이 막히는 가장 흔한 원인: 오래 열린 트랜잭션

오래 열린 트랜잭션이 있으면 vacuum이 dead tuple을 치워도 freeze/cleanup이 제한되고, 결국 bloat가 누적됩니다.

SELECT
  pid,
  usename,
  application_name,
  client_addr,
  state,
  xact_start,
  now() - xact_start AS xact_age,
  query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY xact_start ASC
LIMIT 20;

• xact_age가 수십 분~수시간인 세션이 상시 존재하면 위험 신호
• 특히 “idle in transaction”은 autovacuum의 천적입니다.

추가로, replication slot이나 long-running logical decoding도 xmin을 잡아 vacuum을 방해할 수 있습니다:

SELECT
  slot_name,
  plugin,
  slot_type,
  active,
  xmin,
  restart_lsn
FROM pg_replication_slots;

2-4. autovacuum이 비용 제한(cost limit) 때문에 너무 느린지

autovacuum은 기본적으로 시스템 부하를 줄이기 위해 “조심스럽게” 동작합니다. 그 결과 대형 테이블에서는 끝까지 못 따라가는 일이 흔합니다.

SHOW autovacuum_vacuum_cost_limit;
SHOW autovacuum_vacuum_cost_delay;
SHOW vacuum_cost_limit;
SHOW vacuum_cost_delay;

• autovacuum_vacuum_cost_delay가 크면(예: 20ms) vacuum이 지나치게 양보
• autovacuum_vacuum_cost_limit가 작으면(예: 200) 처리량이 부족

2-5. 로그로 “진짜로” 확인하기

운영에서 가장 강력한 건 로그입니다.

SHOW log_autovacuum_min_duration;

권장:

• 문제 분석 기간에는 log_autovacuum_min_duration = 0(모든 autovacuum 로깅)
• 평시에는 1s~10s 등으로 조정

로그에는 다음이 찍힙니다:

• 처리한 dead tuple 수
• 소요 시간
• I/O, buffer hit/miss
• index cleanup 여부

3) autovacuum 트리거 튜닝: “언제” 도는가를 바꿔라

autovacuum은 기본적으로 아래 조건을 만족하면 vacuum을 트리거합니다.

• dead_tuples > autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor * reltuples

대형 테이블에서 scale_factor가 기본(0.2)이면, 예를 들어 1억 row 테이블은 2천만 dead tuple이 쌓여야 vacuum이 도는 셈이라 너무 늦습니다.

3-1. 전역 기본값 예시(출발점)

아래는 “쓰기 많은 OLTP”에서 흔히 쓰는 출발점입니다. 정답은 아니고, 반드시 워크로드에 맞춰 조정해야 합니다.

# postgresql.conf 예시
autovacuum = on

# 더 자주 깨우기
autovacuum_naptime = '10s'

# 동시성 확대(디스크/CPU 여유가 있을 때)
autovacuum_max_workers = 6

# 트리거를 앞당기기(대형 테이블에서 특히 중요)
autovacuum_vacuum_scale_factor = 0.05
autovacuum_vacuum_threshold = 1000

autovacuum_analyze_scale_factor = 0.02
autovacuum_analyze_threshold = 1000

• naptime은 “체크 주기”라서 너무 길면 굶는 테이블이 생깁니다.
• max_workers는 늘리면 좋은데, 디스크가 버티지 못하면 오히려 전체 latency가 튑니다.

3-2. 테이블별(또는 파티션별)로 더 공격적으로

전역 설정만으로는 한계가 있습니다. 업데이트/삭제가 많은 “핫 테이블”은 테이블 옵션으로 따로 조정하세요.

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

• 대형 테이블일수록 scale_factor를 낮추는 게 핵심
• 파티션 테이블이라면 파티션별로 설정하는 편이 효율적입니다(활성 파티션만 강하게)

4) 처리량 튜닝: “얼마나 세게” 도는가를 바꿔라

트리거를 앞당겨도, vacuum 자체가 느리면 결국 밀립니다. 이때는 “비용 제한”과 “메모리”를 조정합니다.

4-1. 비용 제한 완화

# autovacuum이 너무 양보해서 못 따라갈 때
autovacuum_vacuum_cost_delay = '2ms'
autovacuum_vacuum_cost_limit = 2000

• delay를 줄이고(limit을 올리면) vacuum이 더 적극적으로 I/O를 사용합니다.
• SSD 환경에서는 특히 delay를 과하게 둘 이유가 줄어듭니다.

4-2. autovacuum_work_mem / maintenance_work_mem

인덱스 정리와 vacuum 효율에 영향이 있습니다.

maintenance_work_mem = '1GB'
autovacuum_work_mem = '256MB'

• autovacuum_work_mem을 너무 작게 두면 인덱스 cleanup이 비효율적일 수 있습니다.
• 다만 워커 수가 많으면 총 메모리 사용량이 커집니다(워커 * work_mem).

4-3. parallel vacuum(가능한 버전/상황에서)

PostgreSQL은 버전에 따라 vacuum의 병렬성이 제한적이지만, 인덱스 vacuum 등 일부 경로에서 이점이 있습니다. 다만 autovacuum이 자동으로 최적화하길 기대하기보다는, 핫 테이블은 maintenance window에 수동 vacuum 전략을 병행하는 것이 현실적입니다.

5) “VACUUM 했는데도 느림”의 진짜 범인: 인덱스 bloat

일반 vacuum은 인덱스 공간을 OS에 반환하지 않습니다. 업데이트가 많은 테이블에서 인덱스가 비대해지면, 테이블은 괜찮아도 인덱스 스캔이 느려지고 캐시 효율이 떨어집니다.

5-1. 인덱스 크기 확인

SELECT
  schemaname,
  relname AS table_name,
  indexrelname AS index_name,
  pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
ORDER BY pg_relation_size(indexrelid) DESC
LIMIT 20;

5-2. 해결 옵션

• REINDEX (CONCURRENTLY) : 인덱스를 재작성해 bloat 제거(운영 친화적)
• pg_repack : 테이블/인덱스를 온라인에 가깝게 재구성
• 최후의 수단 VACUUM FULL : 테이블 락이 크고 위험

예: 큰 인덱스 하나만 재작성

REINDEX INDEX CONCURRENTLY public.orders_created_at_idx;

6) autovacuum을 “막는” 운영 습관 교정

6-1. idle in transaction 금지

• 커넥션 풀/ORM에서 트랜잭션 범위가 불필요하게 길어지지 않게
• API에서 BEGIN 후 외부 호출(HTTP 등) 끼워 넣지 않기

6-2. 배치 작업은 작은 트랜잭션으로 쪼개기

대량 업데이트/삭제를 한 방에 하면 dead tuple이 폭발하고 vacuum이 따라가지 못합니다.

예: 배치 삭제를 청크로

WITH to_delete AS (
  SELECT id
  FROM events
  WHERE created_at < now() - interval '90 days'
  ORDER BY id
  LIMIT 5000
)
DELETE FROM events e
USING to_delete d
WHERE e.id = d.id;

6-3. 파티셔닝 + DROP 전략

시계열/로그성 데이터는 “DELETE로 청소”보다 “파티션 DROP”이 vacuum 부담을 근본적으로 줄입니다.

7) 실전 체크리스트: 한 번에 정리

7-1. 지금 당장 확인할 쿼리 5개

-- 1) dead tuple 상위 테이블
SELECT schemaname, relname, n_live_tup, n_dead_tup, last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;

-- 2) 오래 열린 트랜잭션
SELECT pid, now()-xact_start AS xact_age, state, query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY xact_start;

-- 3) autovacuum 설정
SHOW autovacuum;
SHOW autovacuum_max_workers;
SHOW autovacuum_naptime;

-- 4) cost 관련
SHOW autovacuum_vacuum_cost_delay;
SHOW autovacuum_vacuum_cost_limit;

-- 5) replication slot이 xmin 잡는지
SELECT slot_name, active, xmin, restart_lsn
FROM pg_replication_slots;

7-2. 조치 우선순위

1. 오래 열린 트랜잭션 제거/원인 수정(가장 효과 큼)
2. 핫 테이블에 테이블별 scale_factor 낮추기
3. autovacuum_max_workers, cost limit/delay로 처리량 확보
4. 인덱스 bloat가 크면 REINDEX CONCURRENTLY / pg_repack
5. 구조적으로는 파티션 + DROP로 vacuum 의존도 낮추기

8) 운영 팁: 변경은 “관측 가능”하게

autovacuum 튜닝은 캐시/권한 이슈처럼 “설정 바꾸면 끝”이 아니라, 반드시 지표로 검증해야 합니다. 로그/메트릭을 켜고, 변경 전후를 비교하세요.

• log_autovacuum_min_duration로 autovacuum 로그 확보
• pg_stat_user_tables의 n_dead_tup, last_autovacuum 추이
• 디스크 I/O, latency(P95/P99), buffer cache hit ratio

장애가 재발할 때를 대비해, 원인-대응을 문서화해두면 다음엔 훨씬 빨리 끝납니다. 이런 “재현→근본 해결”의 기록 방식은 aiohttp ClientSession is closed 재현과 근본 해결 같은 글의 접근과도 통합니다.

마무리

“VACUUM이 안 먹는다”는 말은 보통 세 가지로 쪼개집니다.

1. autovacuum이 트리거가 늦다(scale_factor가 큼)
2. autovacuum이 너무 약하다(worker 부족, cost 제한, work_mem 부족)
3. vacuum으로는 해결이 어려운 인덱스/테이블 bloat가 누적됐다(REINDEX/pg_repack 필요)

가장 먼저 오래 열린 트랜잭션과 replication slot을 의심하고, 그 다음에 테이블별 autovacuum 설정을 공격적으로 조정하세요. 마지막으로 “용량이 안 줄어드는 문제”는 VACUUM의 역할을 정확히 이해하고, 필요한 경우 재작성 전략(REINDEX/pg_repack/파티션 DROP)으로 넘어가면 됩니다.