PostgreSQL VACUUM 안 끝남? bloat·락 7단계 진단

운영 중인 PostgreSQL에서 VACUUM 이 끝나지 않거나, autovacuum 이 계속 도는 것처럼 보이는데 디스크도 줄지 않고 성능만 떨어지는 상황을 자주 만납니다. 대부분은 “VACUUM이 느린 것”이 아니라, 진행을 막는 락/세션, dead tuple 폭증(bloat), freeze 지연(wraparound 압박), I/O 병목이 겹친 결과입니다.

이 글은 “지금 이 테이블의 VACUUM이 왜 안 끝나는가”를 빠르게 좁혀가기 위한 7단계 진단 체크리스트입니다. 각 단계는 바로 실행 가능한 SQL과 함께, 다음 액션까지 연결되도록 구성했습니다.

관련해서 운영 트러블슈팅을 체계적으로 쪼개는 방식은 다른 글에서도 유효합니다. 예를 들어 리눅스 디스크 100%인데 삭제해도 용량이 안 늘 때처럼 “겉으로 보이는 증상”과 “실제 원인”이 다른 케이스가 많습니다.

0단계: 지금 돌고 있는 VACUUM이 뭔지부터 확인

먼저 수동 VACUUM 인지, autovacuum 인지, VACUUM FULL 인지에 따라 락/동작이 완전히 달라집니다.

• VACUUM / VACUUM (ANALYZE)
  • 일반적으로 테이블에 강한 락을 오래 잡지 않습니다(동시 DML 가능)
  • dead tuple을 “바로 삭제”하는 게 아니라 재사용 가능 표시를 하고, 통계 갱신/인덱스 정리 등을 수행
• VACUUM FULL
  • 테이블을 재작성(rewrite)하며 ACCESS EXCLUSIVE 락이 필요
  • 트래픽 있는 테이블에서 사실상 “다운타임 작업”에 가깝습니다

현재 어떤 vacuum이 실행 중인지부터 확인합니다.

SELECT
  pid,
  usename,
  application_name,
  backend_type,
  state,
  wait_event_type,
  wait_event,
  now() - xact_start AS xact_age,
  now() - query_start AS query_age,
  query
FROM pg_stat_activity
WHERE query ILIKE '%vacuum%'
ORDER BY query_start;

여기서 query 에 VACUUM FULL 이 보이면, “안 끝남”의 원인이 락 대기일 가능성이 급상승합니다.

1단계: VACUUM 진행률을 pg_stat_progress_vacuum 으로 확인

PostgreSQL은 vacuum 진행 상황을 뷰로 제공합니다. “진짜로 멈춘 것”인지 “매우 느리지만 진행 중”인지 구분하는 게 1순위입니다.

SELECT
  p.pid,
  c.relname,
  p.phase,
  p.heap_blks_total,
  p.heap_blks_scanned,
  p.heap_blks_vacuumed,
  p.index_vacuum_count,
  p.max_dead_tuples,
  p.num_dead_tuples
FROM pg_stat_progress_vacuum p
JOIN pg_class c ON c.oid = p.relid
ORDER BY p.pid;

해석 포인트:

• phase 가 scanning heap 에서 오래 머무르면: 테이블이 크거나 I/O 병목, 또는 cost delay 영향
• heap_blks_scanned 가 증가하지 않으면: 진짜로 멈췄거나, 강한 대기(wait) 상태
• num_dead_tuples 가 계속 큰데 줄지 않으면: 오래 열린 트랜잭션 때문에 cleanup이 제한될 수 있음

진행률 뷰가 비어 있으면:

• vacuum이 이미 끝났거나
• autovacuum worker가 다른 테이블을 처리 중이거나
• 버전에 따라 노출 조건이 다르거나
• 실행이 vacuum이 아니라 analyze만 하는 케이스도 있습니다

2단계: “VACUUM을 막는 락”부터 제거

일반 VACUUM 은 대개 동시 실행이 가능하지만, 다음 상황에서 락 대기가 길어질 수 있습니다.

• VACUUM FULL / CLUSTER / 일부 ALTER TABLE
• 오래 걸리는 DDL과 충돌
• 특정 단계에서 필요한 잠금이 획득되지 못하는 경우

락 대기 관계를 확인합니다.

SELECT
  a.pid AS waiting_pid,
  a.wait_event_type,
  a.wait_event,
  a.query AS waiting_query,
  b.pid AS blocking_pid,
  b.query AS blocking_query,
  now() - b.query_start AS blocking_for
FROM pg_stat_activity a
JOIN pg_locks l1 ON l1.pid = a.pid AND NOT l1.granted
JOIN pg_locks l2 ON l2.locktype = l1.locktype
  AND l2.database IS NOT DISTINCT FROM l1.database
  AND l2.relation IS NOT DISTINCT FROM l1.relation
  AND l2.page IS NOT DISTINCT FROM l1.page
  AND l2.tuple IS NOT DISTINCT FROM l1.tuple
  AND l2.virtualxid IS NOT DISTINCT FROM l1.virtualxid
  AND l2.transactionid IS NOT DISTINCT FROM l1.transactionid
  AND l2.classid IS NOT DISTINCT FROM l1.classid
  AND l2.objid IS NOT DISTINCT FROM l1.objid
  AND l2.objsubid IS NOT DISTINCT FROM l1.objsubid
  AND l2.pid != l1.pid
JOIN pg_stat_activity b ON b.pid = l2.pid
WHERE a.query ILIKE '%vacuum%'
ORDER BY blocking_for DESC;

액션 가이드:

• blocking 쿼리가 배치/리포트성 장기 쿼리면: 우선 취소(pg_cancel_backend) 고려
• 트랜잭션이 꼬여 있고 즉시 해소가 필요하면: 최후 수단으로 종료(pg_terminate_backend)

SELECT pg_cancel_backend(`12345`);
-- 또는
SELECT pg_terminate_backend(`12345`);

주의: 종료는 애플리케이션 에러/재시도 폭증을 유발할 수 있으니, 우선 cancel부터 시도하고 영향 범위를 확인하세요.

3단계: “오래 열린 트랜잭션”이 dead tuple 청소를 막는지 확인

VACUUM이 dead tuple을 정리하려면, 해당 row 버전이 어떤 트랜잭션에게도 보일 가능성이 없어야 합니다. 그런데 장기 트랜잭션이 있으면 xmin 이 오래 유지되어, 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,
  query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY xact_start;

특히 state 가 idle in transaction 인 세션이 오래 지속되면:

• vacuum이 회수할 수 있는 dead tuple이 제한
• autovacuum 이 freeze를 못 해서 wraparound 위험 증가
• 전체 성능 저하(인덱스/테이블 bloat 악화)

액션:

• 애플리케이션에서 트랜잭션을 짧게 유지하도록 수정
• 커넥션 풀에서 idle_in_transaction_session_timeout 설정 고려

ALTER SYSTEM SET idle_in_transaction_session_timeout = '60s';
SELECT pg_reload_conf();

4단계: bloat(부풀음)인지, 단순히 “디스크가 안 줄어드는 정상 동작”인지 구분

많이 오해하는 지점이 있습니다.

• 일반 VACUUM 은 파일 크기를 즉시 줄이지 않습니다
• 대신 내부 free space map에 “재사용 가능 공간”으로 남습니다
• OS 레벨에서 디스크가 줄어드는 것은 보통 VACUUM FULL 또는 테이블 재작성 계열 작업에서만 기대할 수 있습니다

그래도 bloat 여부는 수치로 확인해야 합니다.

SELECT
  relname,
  n_live_tup,
  n_dead_tup,
  round(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 2) AS dead_ratio_percent,
  last_vacuum,
  last_autovacuum,
  last_analyze,
  last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;

추가로 테이블 크기/인덱스 크기를 봅니다.

SELECT
  relname,
  pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
  pg_size_pretty(pg_relation_size(relid)) AS table_size,
  pg_size_pretty(pg_total_relation_size(relid) - pg_relation_size(relid)) AS index_size
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC
LIMIT 20;

판단:

• n_dead_tup 이 크고 last_autovacuum 이 최근인데도 계속 크면
  • 장기 트랜잭션(3단계)
  • autovacuum 파라미터 부족(6단계)
  • 업데이트 폭주로 vacuum이 따라가지 못함
• 인덱스가 과도하게 큰 경우
  • 인덱스 bloat 가능
  • REINDEX CONCURRENTLY 같은 접근을 검토

5단계: wraparound(트랜잭션 ID) 압박으로 “강제 vacuum” 상태인지 확인

VACUUM이 “안 끝나는 것처럼 보이는” 또 다른 원인은, 시스템이 wraparound를 피하기 위해 freeze vacuum을 강하게 요구하는 상황입니다. 이 경우 autovacuum이 공격적으로 돌고, I/O를 많이 쓰며, 다른 작업을 압박할 수 있습니다.

데이터베이스별 age를 확인합니다.

SELECT
  datname,
  age(datfrozenxid) AS frozenxid_age,
  age(datminmxid) AS minmxid_age
FROM pg_database
ORDER BY frozenxid_age DESC;

테이블별로 freeze가 밀린 대상을 찾습니다.

SELECT
  c.relname,
  age(c.relfrozenxid) AS relfrozenxid_age,
  s.n_dead_tup,
  s.last_autovacuum
FROM pg_class c
JOIN pg_stat_user_tables s ON s.relid = c.oid
WHERE c.relkind = 'r'
ORDER BY relfrozenxid_age DESC
LIMIT 50;

액션:

• freeze가 밀린 테이블을 우선순위로 vacuum
• 장기 트랜잭션 제거(3단계)
• autovacuum freeze 관련 설정 점검(6단계)

6단계: autovacuum이 “너무 약해서” 못 따라가는지 튜닝

VACUUM이 느린 이유가 단순히 리소스를 너무 아껴서일 때가 많습니다. 기본값은 안전하지만, 쓰기 많은 서비스에서는 부족합니다.

핵심 파라미터:

• autovacuum_max_workers: 동시에 돌 수 있는 worker 수
• autovacuum_vacuum_cost_limit, autovacuum_vacuum_cost_delay: vacuum이 스스로 쉬는 정도
• autovacuum_naptime: 주기
• 테이블별 autovacuum_vacuum_scale_factor, autovacuum_vacuum_threshold

현재 설정 확인:

SHOW autovacuum;
SHOW autovacuum_max_workers;
SHOW autovacuum_vacuum_cost_limit;
SHOW autovacuum_vacuum_cost_delay;
SHOW autovacuum_naptime;

테이블 단위로 “이 테이블은 더 자주 vacuum” 하도록 바꾸는 것이 부작용이 적습니다.

ALTER TABLE your_table
SET (
  autovacuum_vacuum_scale_factor = 0.02,
  autovacuum_vacuum_threshold = 1000,
  autovacuum_analyze_scale_factor = 0.05,
  autovacuum_analyze_threshold = 1000
);

운영 팁:

• scale factor는 테이블이 클수록 vacuum 트리거가 늦어지는 구조라, 대형 테이블은 낮춰야 합니다.
• cost limit을 올리면 vacuum이 더 공격적으로 I/O를 사용합니다. 스토리지 여유가 없다면 오히려 지연이 커질 수 있어, 7단계(I/O)와 같이 봐야 합니다.

7단계: I/O 병목, 체크포인트, 인덱스 정리 단계에서 막히는지 확인

VACUUM이 진행률은 보이는데 너무 느리면 결국 I/O 문제일 확률이 큽니다.

DB 내부에서 힌트를 얻는 방법:

• 세션 wait 이벤트 확인

SELECT
  pid,
  wait_event_type,
  wait_event,
  state,
  query
FROM pg_stat_activity
WHERE query ILIKE '%vacuum%'
ORDER BY pid;

wait_event_type 가 IO 쪽이면 스토리지 지연을 의심합니다.

• 체크포인트/버퍼 관련 통계(대략적인 압력 확인)

SELECT
  checkpoints_timed,
  checkpoints_req,
  checkpoint_write_time,
  checkpoint_sync_time,
  buffers_checkpoint,
  buffers_clean,
  maxwritten_clean
FROM pg_stat_bgwriter;

운영 액션 후보:

• 스토리지 성능(EBS gp2/gp3, io2 등)과 IOPS, throughput 점검
• vacuum 시점에 대형 배치/인덱스 생성/대량 업데이트가 겹치지 않게 스케줄 조정
• 인덱스 bloat가 심하면 REINDEX CONCURRENTLY 검토

EKS 같은 환경에서 스토리지/마운트 이슈가 성능 문제로 번지는 경우도 많습니다. 인프라 레벨 점검이 필요하면 EKS PVC Bound인데 Mount 실패 - EBS CSI 권한·AZ·fsType도 함께 참고할 만합니다.

실전 처방전: 상황별로 무엇을 해야 하나

케이스 A: VACUUM FULL 이 락 대기로 멈춘 것 같다

1. 2단계 쿼리로 blocking 세션 확인
2. 업무 영향이 적은 방향으로 cancel 또는 terminate
3. 가능하면 VACUUM FULL 대신
   • bloat가 목적이면 pg_repack 같은 온라인 재작성 도구 검토
   • 인덱스가 문제면 REINDEX CONCURRENTLY

케이스 B: 일반 vacuum인데도 dead tuple이 안 줄고 계속 쌓인다

1. 3단계로 장기 트랜잭션 제거
2. 6단계로 테이블별 autovacuum 트리거를 앞당김
3. 업데이트 패턴을 점검(핫 업데이트, 불필요한 UPDATE, 대량 UPSERT)

케이스 C: 디스크가 안 줄어든다

• 일반 vacuum은 디스크를 줄이지 않는 것이 정상
• “디스크를 즉시 줄이기”가 목표면 VACUUM FULL 또는 테이블 재작성(다운타임/락 비용)과 트레이드오프
• OS 레벨에서 공간이 안 돌아오는 문제라면, DB 밖의 파일 삭제/마운트/오픈 파일 이슈도 함께 의심해야 합니다. 이 관점은 리눅스 디스크 100%인데 삭제해도 용량이 안 늘 때와 동일한 구조입니다.

점검용 원샷 쿼리 모음

운영 중 빠르게 복사해서 쓰는 용도로, 핵심만 묶어두겠습니다.

-- 1) vacuum 실행/대기 확인
SELECT pid, state, wait_event_type, wait_event, now() - query_start AS age, query
FROM pg_stat_activity
WHERE query ILIKE '%vacuum%'
ORDER BY query_start;

-- 2) vacuum 진행률
SELECT p.pid, c.relname, p.phase, p.heap_blks_total, p.heap_blks_scanned, p.heap_blks_vacuumed,
       p.index_vacuum_count, p.max_dead_tuples, p.num_dead_tuples
FROM pg_stat_progress_vacuum p
JOIN pg_class c ON c.oid = p.relid
ORDER BY p.pid;

-- 3) 장기 트랜잭션
SELECT pid, usename, state, now() - xact_start AS xact_age, query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY xact_start;

-- 4) dead tuple 상위
SELECT relname, n_live_tup, n_dead_tup,
       round(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 2) AS dead_ratio_percent,
       last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;

-- 5) wraparound 위험(데이터베이스)
SELECT datname, age(datfrozenxid) AS frozenxid_age
FROM pg_database
ORDER BY frozenxid_age DESC;

마무리: “VACUUM이 안 끝난다”는 증상은 대개 합성 문제

정리하면, VACUUM이 끝나지 않는 문제는 보통 다음 중 하나로 귀결됩니다.

1. VACUUM FULL 또는 DDL로 인한 락 대기
2. 장기 트랜잭션이 xmin 을 붙잡아 cleanup을 막음
3. dead tuple이 생성되는 속도를 vacuum이 못 따라감(autovacuum 설정/테이블별 scale factor 문제)
4. wraparound 압박으로 freeze vacuum이 과도하게 발생
5. 스토리지 I/O 병목

7단계를 순서대로 밟으면 “원인 후보”가 아니라 증거 기반으로 범위를 빠르게 좁힐 수 있습니다. 다음에 원하시면, 실제 pg_stat_activity 와 pg_stat_progress_vacuum 출력 샘플을 기준으로 어떤 값을 보면 되는지(phase별로 정상/비정상 패턴)도 케이스 스터디 형태로 이어서 정리해드릴게요.