PostgreSQL VACUUM 안 끝날 때 원인과 해결법

서버에서 VACUUM을 걸어놨는데 몇 시간째 끝나지 않으면, 대부분은 “VACUUM이 느린 것”이 아니라 무언가를 기다리거나(락/스냅샷), 시스템 자원(I/O/CPU)을 못 쓰거나, 해야 할 일이 과도하게 많거나(테이블/인덱스 비대화) 중 하나입니다. 이 글은 운영 환경에서 원인 분류 → 증거 수집 → 안전한 조치 순으로 정리한 트러블슈팅 가이드입니다.

> 참고: autovacuum 자체가 멈추거나 테이블이 폭증하는 케이스는 별도로 정리해둔 글이 있습니다. 상황이 비슷하다면 함께 보세요: PostgreSQL autovacuum 멈춤으로 테이블 폭증 해결

1) 먼저 확인: 어떤 VACUUM인가?

VACUUM은 한 종류가 아닙니다. “안 끝난다”의 의미도 달라집니다.

• VACUUM(일반): dead tuple을 재사용 가능하게 표시(가시성 지도/프리즈 일부 포함). 테이블에 강한 락을 오래 잡지 않음(대부분 ShareUpdateExclusive).
• VACUUM (ANALYZE): 통계 갱신까지 포함.
• VACUUM FULL: 테이블을 새로 써서 압축(rewrite). AccessExclusiveLock 필요 → 다른 쿼리/트랜잭션과 충돌하면 대기하거나 블로킹.
• VACUUM FREEZE: 오래된 XID를 동결. 오래된 트랜잭션이 있으면 진행이 제한될 수 있음.

현재 수행 중인 VACUUM을 먼저 식별합니다.

-- 실행 중인 vacuum 관련 쿼리 확인
SELECT pid, usename, application_name, 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;

여기서 wait_event_type / wait_event가 핵심입니다.

• Lock이면 락 대기
• IO/DataFileRead/DataFileWrite 계열이면 I/O 병목
• Client면 클라이언트가 응답을 안 읽는 등 다른 이슈

2) 가장 흔한 원인 1: 락 대기(특히 VACUUM FULL)

2-1. VACUUM은 어떤 락을 기다릴까?

• 일반 VACUUM도 특정 상황에서 다른 락과 충돌하여 대기할 수 있습니다.
• VACUUM FULL은 사실상 “테이블 재작성”이라 독점 락이 필요합니다. 운영 중에 걸면 거의 항상 문제가 됩니다.

락 대기 여부는 아래로 확인합니다.

-- 누가 누구를 막고 있는지(대기/블로킹 관계)
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.xact_start AS blocking_xact_age
FROM pg_stat_activity a
JOIN pg_stat_activity b
  ON b.pid = ANY (pg_blocking_pids(a.pid))
WHERE a.query ILIKE '%vacuum%';

해결 체크리스트

1. 블로킹 트랜잭션을 종료할 수 있는지 판단

-- 블로킹 PID 종료(주의: 롤백 발생)
SELECT pg_terminate_backend(<blocking_pid>);

2. VACUUM을 다시 걸어야 한다면, 운영 시간대에는 다음을 고려

• VACUUM(일반) + 튜닝(autovacuum/비용/병렬)로 해결
• VACUUM FULL은 점검창에만 수행
• pg_repack 같은 온라인 재작성 도구 검토(환경/권한/확장 설치 필요)

3. 애초에 lock_timeout을 걸어 “무한 대기”를 방지

-- 세션 단위로 락 대기 상한 설정
SET lock_timeout = '5s';
VACUUM (VERBOSE, ANALYZE) your_table;

3) 가장 흔한 원인 2: 오래된 트랜잭션(스냅샷)이 dead tuple 제거를 막음

VACUUM이 “돌고는 있는데” 테이블이 안 줄고, pg_stat_all_tables.n_dead_tup이 계속 높다면 오래 열린 트랜잭션이 원인일 가능성이 큽니다.

3-1. 왜 오래된 트랜잭션이 VACUUM을 막나?

PostgreSQL은 MVCC라서, 어떤 트랜잭션이 오래된 스냅샷을 잡고 있으면 VACUUM이 그 시점에서 “아직 보일 수 있는 튜플”을 완전히 제거하지 못합니다. 결과:

• dead tuple이 쌓여서 I/O 증가
• 인덱스/테이블 비대화(bloat)
• VACUUM이 끝나도 효과가 제한적

오래된 트랜잭션을 찾습니다.

-- 오래 열린 트랜잭션/idle in transaction 탐지
SELECT pid, usename, application_name, client_addr,
       state,
       now() - xact_start AS xact_age,
       now() - state_change AS state_age,
       query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY xact_start ASC
LIMIT 30;

특히 state = 'idle in transaction'은 위험 신호입니다.

해결 체크리스트

• 애플리케이션 커넥션 풀에서 트랜잭션을 열어둔 채로 대기하지 않게 수정
• 운영 즉시 대응이 필요하면 해당 세션 종료

SELECT pg_terminate_backend(<pid>);

• 예방: idle_in_transaction_session_timeout 설정

-- 예: 60초 이상 idle in transaction이면 서버가 종료
ALTER SYSTEM SET idle_in_transaction_session_timeout = '60s';
SELECT pg_reload_conf();

4) 가장 흔한 원인 3: I/O 병목(디스크가 못 따라옴)

VACUUM은 대량의 페이지를 스캔하고(읽기), visibility map 업데이트/힌트 비트/프리즈 등으로 쓰기도 합니다. 디스크가 느리거나 다른 워크로드가 I/O를 잡아먹으면 “끝나지 않는 것처럼” 보입니다.

4-1. 증거 수집: 진행률과 I/O 관찰

PostgreSQL 9.6+에서는 진행률 뷰가 있습니다.

-- VACUUM 진행률(테이블 단위)
SELECT pid, relid::regclass AS table_name,
       phase,
       heap_blks_total, heap_blks_scanned, heap_blks_vacuumed,
       index_vacuum_count,
       num_dead_tuples,
       now() - query_start AS elapsed
FROM pg_stat_progress_vacuum
ORDER BY query_start;

• heap_blks_scanned가 거의 안 늘면 I/O 또는 락/대기 가능성
• phase가 vacuuming indexes에서 오래 멈추면 인덱스가 크거나 I/O가 부족

OS 레벨에서는 iostat -x, vmstat, 클라우드라면 EBS/Burst 크레딧 등을 같이 봅니다.

해결 체크리스트

• 즉시 완화(운영 영향 줄이기)
  • vacuum cost를 낮춰 다른 쿼리에 양보하거나(반대로 maintenance window에는 높여서 빨리 끝내기)

-- 지금 세션에서 VACUUM 속도/부하 조절
SET vacuum_cost_limit = 2000;
SET vacuum_cost_delay = '5ms';
VACUUM (VERBOSE) your_table;

• 장기 해결
  • 스토리지 성능 상향(특히 랜덤 I/O)
  • bloat 원인 제거(대량 UPDATE/DELETE 패턴 개선, 파티셔닝)
  • autovacuum이 제때 돌도록 튜닝(다음 섹션)

5) 가장 흔한 원인 4: autovacuum/테이블 설정이 워크로드와 불일치

VACUUM이 “안 끝나는” 근본 원인이 평소에 vacuum이 너무 늦게/약하게 돌아서 할 일이 누적된 경우가 많습니다. 즉, 수동 VACUUM은 증상 완화일 뿐이고, 재발합니다.

5-1. 테이블별 dead tuple/마지막 vacuum 확인

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

n_dead_tup이 크고 last_autovacuum이 오래 전이면 autovacuum이 못 따라가고 있는 상태입니다.

5-2. 테이블별 autovacuum 임계치 조정(실전)

기본 설정은 대형 테이블에 불리할 수 있습니다(스케일 팩터가 커서 vacuum 트리거가 늦음). 특정 테이블만 임계치를 낮춰 자주 돌게 합니다.

-- 예: 대형 테이블은 더 자주 vacuum/analyze 하도록
ALTER TABLE your_table SET (
  autovacuum_vacuum_scale_factor = 0.02,
  autovacuum_vacuum_threshold    = 5000,
  autovacuum_analyze_scale_factor = 0.01,
  autovacuum_analyze_threshold    = 3000
);

추가로 autovacuum worker 수/비용 한도도 점검합니다.

SHOW autovacuum_max_workers;
SHOW autovacuum_vacuum_cost_limit;
SHOW autovacuum_vacuum_cost_delay;
SHOW maintenance_work_mem;

• autovacuum_max_workers가 너무 낮으면 여러 큰 테이블이 동시에 밀릴 때 병목
• maintenance_work_mem이 너무 작으면 인덱스 vacuum 단계가 비효율적일 수 있음

6) “진짜로 멈춘 것처럼” 보이는 케이스들

6-1. 인덱스 vacuum 단계가 과도하게 오래 걸림

진행률 뷰에서 phase = 'vacuuming indexes'가 길면:

• 인덱스가 너무 많거나
• 인덱스 bloat가 심하거나
• random I/O가 느리거나
• maintenance_work_mem이 작아서 정리가 비효율

즉각 조치로는 maintenance window에 maintenance_work_mem을 올려 VACUUM을 재시도(세션/시스템 단위)할 수 있습니다.

-- 세션 단위(해당 세션에서만)
SET maintenance_work_mem = '2GB';
VACUUM (VERBOSE, ANALYZE) your_table;

6-2. wraparound(트랜잭션 ID) 위험으로 vacuum이 “필수 작업”이 됨

로그에 must be vacuumed within ... transactions 같은 메시지가 보이면, 일반 성능 문제가 아니라 데이터베이스 생존 이슈입니다. 이때는 오래된 트랜잭션 제거가 최우선이고, 필요하면 긴급 점검창을 잡아야 합니다.

-- 데이터베이스별 age 확인
SELECT datname,
       age(datfrozenxid) AS xid_age,
       mxid_age(datminmxid) AS mxid_age
FROM pg_database
ORDER BY xid_age DESC;

6-3. VACUUM을 중단해도 되나?

• 일반 VACUUM은 중단해도 테이블이 논리적으로 깨지지 않습니다. 다만 “진행하던 정리”가 중간에 멈출 뿐입니다.
• VACUUM FULL은 락/재작성 특성상 운영 영향이 크니, 대기/블로킹이 확인되면 과감히 중단하고 계획을 다시 잡는 편이 안전합니다.

중단은 보통 클라이언트에서 CANCEL(Ctrl+C) → 안 되면 pg_cancel_backend → 최후에 pg_terminate_backend 순으로 갑니다.

-- 우선 cancel(쿼리만 중단)
SELECT pg_cancel_backend(<vacuum_pid>);

-- 안 되면 terminate(세션 종료)
SELECT pg_terminate_backend(<vacuum_pid>);

7) 운영에서 바로 써먹는 “10분 진단” 플로우

7-1. 1분: VACUUM이 무엇을 기다리는지

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

• Lock → 2분: 블로킹 PID 찾기
• IO 또는 진행률이 느림 → 3분: 진행률 확인 + 디스크 지표
• 대기는 없는데 효과가 없음 → 4분: 오래된 트랜잭션 확인

7-2. 2분: 블로킹 관계 확인

SELECT a.pid AS waiting_pid,
       b.pid AS blocking_pid,
       b.state,
       now() - b.xact_start AS blocking_xact_age,
       b.query AS blocking_query
FROM pg_stat_activity a
JOIN pg_stat_activity b
  ON b.pid = ANY (pg_blocking_pids(a.pid))
WHERE a.query ILIKE '%vacuum%';

7-3. 3분: 진행률 확인

SELECT pid, relid::regclass, phase,
       heap_blks_total, heap_blks_scanned, heap_blks_vacuumed,
       num_dead_tuples
FROM pg_stat_progress_vacuum;

7-4. 4분: 오래된 트랜잭션 확인

SELECT pid, state, now() - xact_start AS xact_age, query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY xact_start;

7-5. 5~10분: 조치 결정

• 블로킹 트랜잭션 종료 가능? (업무 영향/롤백 비용)
• VACUUM을 지금 해야 하나? (wraparound 위험 여부)
• 점검창으로 미루고 autovacuum 튜닝으로 재발 방지할 건가?

8) 재발 방지: “VACUUM이 안 끝나는 구조”를 없애기

• 긴 트랜잭션 금지: 배치/리포팅 쿼리는 커밋을 자주 하거나 읽기 전용 복제본으로 분리
• autovacuum 테이블별 튜닝: 대형 테이블은 scale_factor를 낮춰 조기 청소
• 파티셔닝: 대량 UPDATE/DELETE가 특정 기간 데이터에 집중되면 파티션 드롭/교체가 vacuum보다 훨씬 싸게 끝남
• bloat 모니터링: dead tuple, 테이블/인덱스 크기 증가율을 지표화

추가로, 운영 장애가 “원인은 다른데 증상이 오래 걸림/타임아웃”으로 나타나는 경우가 많습니다. 쿠버네티스/네트워크 계층에서 타임아웃이 겹치면 DB 작업이 더 길어 보일 수 있으니, 인프라 단의 타임아웃 진단 글도 참고할 만합니다: Gunicorn Uvicorn Worker timeout 재현과 해결

마무리

VACUUM이 안 끝나는 문제는 감으로 해결하기 어렵고, (1) 락 대기인지 (2) 오래된 트랜잭션인지 (3) I/O 병목인지 (4) 누적된 청소량인지를 빠르게 분류하는 것이 핵심입니다. pg_stat_activity, pg_stat_progress_vacuum, pg_blocking_pids()만 제대로 써도 원인의 80%는 10분 안에 좁힐 수 있습니다.

운영에서 가장 안전한 접근은 다음 순서입니다.

1. 진행률/대기 이벤트로 “정말 멈춘 건지, 기다리는 건지” 확인
2. 블로킹 트랜잭션과 오래된 트랜잭션을 제거(가능한 범위에서)
3. VACUUM FULL은 점검창으로 이관하고, 평소엔 autovacuum이 제때 돌도록 테이블별 설정을 튜닝

이렇게 하면 “VACUUM이 끝날 때까지 기도”하는 상황을 대부분 없앨 수 있습니다.