VACUUM, ANALYZE, autovacuum

Quick Reference

sql

VACUUM ANALYZE orders;

죽은 tuple을 재사용 가능하게 만드는 작업은 VACUUM, planner가 행 수와 분포를 추정하도록 하는 작업은 ANALYZE입니다. 대량 적재 뒤 plan이 문제면 먼저 ANALYZE, update·delete가 많은 테이블의 정리 지연은 autovacuum과 장기 transaction을 함께 봅니다. 일반 VACUUM도 lock을 잡지만 일반 SELECT·DML은 보통 막지 않으며, VACUUM FULL만 서비스 중단 수준의 ACCESS EXCLUSIVE lock을 요구합니다.

문법

MVCC 모델은 UPDATE / DELETE 마다 죽은 튜플을 남겨 테이블을 부풀린다

PostgreSQL 은 행을 덮어쓰지 않습니다. UPDATE 는 기존 행을 "삭제된 것으로 표시"하고 새 버전을 추가하며, DELETE 도 행을 실제로 지우지 않고 만료 표시만 합니다. 오래된 스냅샷을 참조하는 트랜잭션이 없을 때 비로소 이 죽은 튜플(dead tuple)을 정리할 수 있습니다. 정리되지 않으면 테이블이 계속 커져 Sequential Scan 비용이 늘고, 인덱스 크기도 함께 부풀어 오릅니다.

sql

-- 테이블별 죽은 튜플 수 확인
SELECT
  relname,
  n_live_tup,
  n_dead_tup, -- 통계 기반 추정치
  round(n_dead_tup::numeric / NULLIF(n_live_tup + n_dead_tup, 0) * 100, 1) AS dead_ratio_pct,
  last_vacuum,
  last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;

VACUUM과 ANALYZE는 해결하는 문제가 다르다

VACUUM은 죽은 튜플을 정리해 공간을 재사용 가능하게 만들고, ANALYZE는 통계를 갱신해 옵티마이저가 더 나은 계획을 고르게 합니다. 느린 쿼리를 볼 때 둘을 같은 작업으로 보면 원인을 헷갈리기 쉽습니다.

sql

VACUUM orders;   -- 공간 재사용
ANALYZE orders;  -- 통계 갱신
VACUUM ANALYZE orders; -- 둘 다 같이

VACUUM 은 공간을 재사용 가능하게 만들고 VACUUM FULL 만 실제 공간을 반환한다

일반 VACUUM은 죽은 tuple을 정리해 그 공간을 같은 테이블의 새 행이 재사용할 수 있도록 표시합니다. 그러나 운영체제에 반환하지는 않으므로 파일 크기 자체는 줄어들지 않습니다. 일반 VACUUM은 SHARE UPDATE EXCLUSIVE lock을 잡아 다른 vacuum·일부 DDL과 충돌하지만, 일반 SELECT·INSERT·UPDATE·DELETE를 막지는 않습니다. VACUUM FULL은 테이블을 새로 작성해 실제 디스크 공간을 반환하지만, ACCESS EXCLUSIVE lock으로 다른 query를 차단합니다. 온라인 재작성 도구는 별도 운영 절차·extension 설치·추가 디스크 공간을 검토한 뒤 선택합니다.

sql

-- 일반 VACUUM: 공간 재사용, 일반 SELECT/DML은 계속 가능
VACUUM orders;

-- VACUUM ANALYZE: 정리와 통계 갱신 동시 수행
VACUUM ANALYZE orders;

-- VACUUM FULL: 디스크 반환, 테이블 잠금 주의
-- VACUUM FULL orders;  -- 운영 중 사용 자제

ANALYZE 가 없으면 옵티마이저가 잘못된 실행 계획을 선택한다

ANALYZE 는 컬럼별 데이터 분포(히스토그램, 가장 많은 값, NULL 비율 등)를 pg_statistic 에 저장합니다. 옵티마이저는 이 통계를 바탕으로 Sequential Scan 과 Index Scan 중 어느 쪽이 유리한지, 조인 방식(Nested Loop / Hash Join / Merge Join)은 무엇이 적합한지 판단합니다. 대규모 INSERT 나 배치 처리 후 통계가 현실을 반영하지 못하면 옵티마이저가 잘못된 계획을 고르고, 실제 수행 시간이 예상보다 훨씬 길어집니다.

sql

-- 통계 갱신
ANALYZE orders;

-- 특정 컬럼만 통계 갱신
ANALYZE orders (status, created_at);

-- 통계 확인
SELECT attname, n_distinct, correlation
FROM pg_stats
WHERE tablename = 'orders';

autovacuum 은 사실상 필수 안전장치이지만 대량 쓰기 직후에는 수동 보완이 필요하다

autovacuum 데몬은 테이블별 죽은 튜플 비율과 변경 행 수가 임계값을 넘으면 자동으로 VACUUM 과 ANALYZE 를 실행합니다. 기본 임계값(autovacuum_vacuum_threshold = 50, autovacuum_vacuum_scale_factor = 0.2)은 대용량 테이블에서는 너무 늦게 반응할 수 있습니다. 수백만 행 테이블에서는 scale_factor 를 낮춰 더 자주 실행되도록 조정하거나, 대량 배치 처리 직후에는 수동으로 VACUUM ANALYZE 를 실행하는 것이 좋습니다.

sql

-- 특정 테이블에 대해 autovacuum 파라미터 조정
ALTER TABLE orders SET (
  autovacuum_vacuum_scale_factor = 0.01,
  autovacuum_analyze_scale_factor = 0.005
);

-- autovacuum 상태 모니터링
SELECT pid, datname, relid::regclass, phase, heap_blks_scanned, heap_blks_vacuumed
FROM pg_stat_progress_vacuum;