PostgreSQL VACUUM이 안 도는 이유와 Bloat 튜닝

서버가 느려지고 디스크가 계속 늘어나는데 VACUUM 로그는 조용한 경우가 있습니다. 이때 많은 팀이 "VACUUM이 안 돈다"고 느끼지만, 실제로는 autovacuum이 돌긴 도는데 효과가 없거나, 락/트랜잭션/설정 문제로 제때 못 돌거나, 혹은 bloat가 이미 커져서 일반 VACUUM만으로는 줄지 않는 상황인 경우가 많습니다.

이 글은 다음을 목표로 합니다.

• VACUUM 이 "안 도는" 것처럼 보이는 대표 원인 정리
• 지금 무슨 일이 벌어지는지 확인하는 진단 SQL
• bloat(테이블/인덱스 팽창) 줄이기 위한 튜닝과 운영 전략

운영 장애 디버깅 관점에서는 "원인 후보를 체크리스트로 빠르게 좁히는" 방식이 가장 효율적입니다. 비슷한 접근은 systemd 서비스가 계속 재시작될 때 원인 9가지 같은 글에서도 통합니다.

VACUUM 기본 동작을 짧게 정리

PostgreSQL은 MVCC 구조라서 DELETE 나 UPDATE 는 기존 튜플을 즉시 제거하지 않고 dead tuple 을 남깁니다.

• VACUUM 은 dead tuple을 "재사용 가능" 상태로 만들고, visibility map 등을 갱신합니다.
• 하지만 일반 VACUUM 은 파일 크기를 OS에 반환하지 않습니다. 즉, bloat가 커진 뒤에는 vacuum이 돌아도 디스크 사용량이 줄지 않을 수 있습니다.
• 파일 크기를 실제로 줄이려면 보통 VACUUM FULL(테이블 재작성), CLUSTER, pg_repack 같은 방식이 필요합니다.

따라서 "VACUUM이 안 돈다"는 말은 아래 3가지로 분해해서 봐야 합니다.

1. autovacuum 워커가 아예 실행되지 않는다
2. 실행되지만 진도가 안 나간다(락, I/O, throttling)
3. 실행돼도 bloat가 줄어들지 않는다(일반 vacuum의 한계)

1) autovacuum이 아예 안 도는 이유

1-1. autovacuum 자체가 꺼져 있음

가장 먼저 확인할 것.

SHOW autovacuum;
SHOW track_counts;

• autovacuum = off 면 당연히 안 돕니다.
• track_counts = off 면 통계가 갱신되지 않아 autovacuum 트리거가 비정상적으로 동작할 수 있습니다.

1-2. 워커/런처 자원이 부족함

autovacuum은 런처와 워커 프로세스로 동작합니다. 워커 수가 부족하면 큐가 밀립니다.

SHOW autovacuum_max_workers;
SHOW autovacuum_naptime;
SHOW max_connections;

진단 포인트

• autovacuum_max_workers 가 너무 작으면 큰 테이블이 많은 DB에서 항상 밀립니다.
• autovacuum_naptime 이 너무 크면 반응이 느립니다.
• 커넥션이 꽉 차서 워커가 접속을 못 하는 경우도 있습니다. 이때는 max_connections 를 무작정 늘리기보다 풀러(PgBouncer)나 앱 커넥션 관리가 우선입니다.

1-3. 특정 테이블에서 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, ',') LIKE '%autovacuum%';

autovacuum_enabled=false 같은 옵션이 보이면 원인 후보입니다.

2) VACUUM이 실행되지만 진도가 안 나가는 이유

2-1. 오래 열린 트랜잭션이 vacuum을 막음

가장 치명적이고 흔한 원인입니다. 오래 열린 트랜잭션이 있으면, vacuum은 해당 트랜잭션이 보는 스냅샷 때문에 dead tuple을 제거(재사용 처리)하지 못합니다.

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

특히 다음 패턴이 위험합니다.

• 배치/리포팅 쿼리가 BEGIN 후 오래 실행
• 커넥션 풀에서 트랜잭션이 열린 채로 반환
• idle in transaction 상태로 방치

즉시 대응(운영 정책)

• 앱 레벨에서 트랜잭션 범위를 최소화
• idle_in_transaction_session_timeout 설정
• 정말 필요하면 해당 세션 종료(pg_terminate_backend) 고려

SHOW idle_in_transaction_session_timeout;

2-2. vacuum이 락에서 대기 중

일반 VACUUM 은 보통 강한 락을 오래 잡지 않지만, 특정 상황에서는 락 대기 때문에 진도가 안 나가는 것처럼 보일 수 있습니다.

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

대기 이벤트가 Lock 이고 granted=false 가 보이면, 누가 막는지 역추적해야 합니다.

2-3. autovacuum throttling 때문에 너무 느림

autovacuum은 기본적으로 I/O를 과도하게 쓰지 않도록 "천천히" 동작합니다. 트래픽이 많은 서비스에서는 이게 오히려 bloat를 키웁니다.

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

• cost_delay 가 크고 cost_limit 이 낮으면 vacuum이 매우 느립니다.
• 스토리지 성능이 충분하고 bloat가 심각하다면, 일시적으로 cost를 완화해 따라잡게 만들 수 있습니다.

3) VACUUM이 돌아도 bloat가 줄지 않는 이유

3-1. 일반 VACUUM은 디스크를 줄이지 않는다

다시 강조하지만, VACUUM 은 내부 free space를 재사용 가능하게 만들 뿐 OS에 파일을 반환하지 않습니다.

• "디스크가 줄어야 vacuum이 돈 것"이라는 기대는 틀릴 수 있습니다.
• 대신 아래 지표를 봐야 합니다.

3-2. dead tuple이 계속 생산되는 워크로드

대량 업데이트(특히 HOT update가 깨지는 패턴), soft delete, 빈번한 upsert는 bloat를 빠르게 키웁니다.

• 업데이트 컬럼이 인덱스에 포함되어 있으면 HOT update가 어려워져 bloat가 더 커집니다.
• FILLFACTOR 를 낮춰 페이지 내 여유 공간을 만들면 page split을 줄일 수 있습니다.

bloat 진단: 지금 어디가 얼마나 부었나

PostgreSQL은 "정확한" bloat 크기를 기본 뷰만으로 계산하기 어렵지만, 운영에서 빠르게 판단하기 위한 실용적인 쿼리는 있습니다.

1) 테이블/인덱스 크기 상위

SELECT
  n.nspname AS schema,
  c.relname AS table,
  pg_size_pretty(pg_total_relation_size(c.oid)) AS total_size,
  pg_size_pretty(pg_relation_size(c.oid)) AS table_size,
  pg_size_pretty(pg_total_relation_size(c.oid) - pg_relation_size(c.oid)) AS index_size
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = 'r'
  AND n.nspname NOT IN ('pg_catalog', 'information_schema')
ORDER BY pg_total_relation_size(c.oid) DESC
LIMIT 30;

2) dead tuple이 많은 테이블

SELECT
  schemaname,
  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;

• n_dead_tup 가 큰데 last_autovacuum 이 오래되었으면 autovacuum이 못 따라가는 상태일 가능성이 큽니다.
• 반대로 last_autovacuum 이 최근인데도 dead tuple이 계속 많으면, 워크로드가 vacuum 속도보다 더 빠르게 dead tuple을 만들거나, 오래 열린 트랜잭션이 방해하고 있을 수 있습니다.

3) freeze 위험(트랜잭션 ID wraparound)

vacuum이 제대로 안 돌면 bloat뿐 아니라 wraparound 위험도 커집니다.

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

xid_age 가 과도하게 커지면 강제 vacuum이 발생하거나 장애로 이어질 수 있어 우선순위가 매우 높습니다.

autovacuum 튜닝: "전체"가 아니라 "문제 테이블"부터

전역 설정을 크게 바꾸기 전에, bloat가 심한 상위 몇 개 테이블에만 table-level로 autovacuum 파라미터를 주는 것이 안전합니다.

1) 트리거 파라미터 이해

autovacuum은 대략 아래 조건으로 vacuum을 시작합니다.

• autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor * reltuples

큰 테이블일수록 scale factor 때문에 트리거가 너무 늦어질 수 있습니다.

2) 테이블 단위 설정 예시

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

• 대형 테이블은 scale_factor 를 낮춰 더 자주 vacuum/analyze가 돌게 합니다.
• analyze가 늦으면 플래너가 잘못된 계획을 선택해 쿼리 지연이 커질 수 있습니다.

3) vacuum 속도 올리기(신중히)

I/O 여유가 있다면 cost를 완화합니다.

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

주의

• 스토리지/CPU 여유가 없는데 cost를 풀면, 서비스 쿼리가 더 느려질 수 있습니다.
• 변경 전후로 iostat, pg_stat_statements, P95 latency를 같이 보세요.

bloat를 "줄이는" 작업: 언제 무엇을 쓸까

1) VACUUM (ANALYZE)

• 목적: dead tuple 정리, 통계 갱신
• 장점: 온라인에 가깝고 안전
• 한계: 디스크 반환 불가

VACUUM (ANALYZE, VERBOSE) public.orders;

2) VACUUM FULL

• 목적: 테이블 재작성으로 디스크 반환
• 단점: 강한 락이 필요(운영 영향 큼)

VACUUM FULL public.orders;

대형 테이블에 즉흥적으로 수행하면 장애가 나기 쉽습니다. 점검 창이 필수입니다.

3) REINDEX / REINDEX CONCURRENTLY

인덱스 bloat가 큰 경우 테이블보다 인덱스 재구성이 체감 효과가 더 큰 경우가 많습니다.

REINDEX INDEX CONCURRENTLY public.orders_created_at_idx;

• CONCURRENTLY 는 시간이 더 걸릴 수 있지만 락 영향이 훨씬 적습니다.

4) pg_repack(권장되는 경우가 많음)

• 목적: 온라인에 가깝게 테이블/인덱스 재작성하여 bloat 제거
• 단점: 확장 설치 필요, 추가 디스크/부하

운영 환경에서 "디스크를 줄여야 하는데 VACUUM FULL 락은 못 견딘다"면 현실적인 선택지입니다.

워크로드 기반 설계 튜닝(재발 방지)

1) Soft delete 남발 줄이기

deleted_at 로만 지우고 실제 삭제를 안 하면 dead tuple이 계속 남습니다.

• 파티셔닝 후 오래된 파티션 DROP 으로 제거
• 주기적 하드 delete 배치 + vacuum 설계

2) 업데이트 패턴 점검

• 자주 바뀌는 컬럼이 여러 인덱스에 들어가 있으면 bloat가 커집니다.
• 정말 필요한 인덱스만 남기고, 커버링 인덱스 남용을 줄입니다.

3) FILLFACTOR 적용

업데이트가 잦은 테이블에 페이지 여유를 주면 page split과 bloat를 줄이는 데 도움이 됩니다.

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

이미 커진 bloat를 즉시 줄이진 않지만, 재작성 이후 유지에 효과가 있습니다.

운영 체크리스트: "VACUUM이 안 돈다"고 느낄 때 10분 진단

1. SHOW autovacuum; 와 SHOW track_counts; 확인
2. pg_stat_activity 에서 오래 열린 트랜잭션(xact_age) 탐지
3. vacuum 관련 세션이 락 대기인지(wait_event_type = Lock) 확인
4. pg_stat_user_tables 에서 n_dead_tup, last_autovacuum 상위 테이블 확인
5. 인덱스가 비정상적으로 큰지(테이블 대비 인덱스 비중) 확인 후 REINDEX CONCURRENTLY 검토
6. 디스크 반환이 목표라면 VACUUM FULL 대신 pg_repack/점검창 재작성 계획 수립

장애 상황에서 "원인 후보를 빠르게 제거"하는 방식은 인프라 문제를 다룰 때도 유사합니다. 예를 들어 Azure VM 부팅 실패, Boot Diagnostics로 10분 진단처럼, 먼저 관측 가능한 신호를 모으고 가설을 줄이는 게 핵심입니다.

마무리

PostgreSQL에서 vacuum 문제는 단순히 "vacuum을 한 번 돌리면 해결"되는 성격이 아닙니다.

• 안 도는 것처럼 보이는 원인은 대개 오래 열린 트랜잭션, 워커 부족, throttling, 테이블별 설정 누락에서 시작합니다.
• bloat를 실제로 줄이는 작업은 VACUUM 만으로는 한계가 있어, 인덱스 재구성(REINDEX CONCURRENTLY)이나 테이블 재작성(VACUUM FULL, pg_repack) 전략이 필요합니다.

다음 액션으로는, 상위 5개 테이블을 골라 n_dead_tup 과 last_autovacuum 을 확인하고, 해당 테이블에만 scale factor를 낮추는 것부터 시작하는 것을 권합니다.