PostgreSQL autovacuum와 bloat: 큰 churn 테이블에서 dead tuple이 쌓이는 이유와 대응

PostgreSQL autovacuum와 bloat

상위: Database MOC

개요

PostgreSQL은 MVCC(다중 버전 동시성 제어)를 쓰기 때문에 UPDATE와 DELETE가 물리적으로 행을 지우지 않는다. 대신 기존 버전을 “죽은 튜플(dead tuple)“로 표시하고 새 버전을 추가한다. 이 죽은 튜플을 회수하는 것이 VACUUM이고, 이를 자동으로 돌리는 것이 autovacuum이다. 문제는 기본 임계치가 큰 테이블·잦은 UPDATE(high churn)에 맞지 않는다는 점이다. 기본값 그대로 두면 수백만 행 테이블에서 죽은 튜플이 수십만 개씩 쌓여 테이블과 인덱스가 붓고(bloat), 조회가 느려지며, 통계가 낡아 플래너가 나쁜 실행계획을 고른다. 이 문서는 그 메커니즘과, 테이블별 튜닝·진단 쿼리를 정리한다.

원리

autovacuum이 언제 도는가

autovacuum이 특정 테이블을 청소할지는 다음 임계식으로 결정된다.

vacuum 임계치 = autovacuum_vacuum_threshold
              + autovacuum_vacuum_scale_factor × 테이블 행수

기본값은 autovacuum_vacuum_threshold = 50, autovacuum_vacuum_scale_factor = 0.2다. 즉 **“전체 행의 20%가 죽어야 청소를 시작”**한다는 뜻이다. 작은 테이블에는 합리적이지만 큰 테이블에서는 이 20%가 지나치게 커진다.

테이블 행수	scale_factor 0.2	청소 시작에 필요한 dead tuple
1만	0.2	약 2천
220만	0.2	약 44만
220만	0.02 (튜닝 후)	약 4.4만

붕괴 시나리오

220만 행 테이블을 배치 작업이 두 번 전량 UPDATE했다고 하자. UPDATE는 각 행마다 새 버전을 만들고 기존 버전을 dead로 남긴다. 한 번의 전량 UPDATE만으로도 220만 dead가 생길 수 있는데, autovacuum은 44만을 넘길 때까지 기다린다. 실무에서는 백그라운드 vacuum이 따라잡지 못해 어느 시점에 ~44만 개의 dead tuple이 상주하는 상태로 관측되곤 한다. 이 죽은 튜플은:

• 힙(heap)을 부풀려 같은 데이터를 읽는 데 더 많은 페이지를 읽게 하고,
• 인덱스에도 죽은 포인터를 남겨 인덱스 스캔을 무겁게 하며,
• ANALYZE가 밀리면 통계가 낡아 플래너가 seq scan/nested loop을 잘못 고르게 한다.

실전

테이블별 스케일 팩터 낮추기

전역 설정을 건드리면 모든 테이블의 vacuum 빈도가 바뀌므로, churn이 큰 테이블만 개별 튜닝하는 것이 안전하다.

ALTER TABLE document_chunks SET (
  autovacuum_vacuum_scale_factor  = 0.02,   -- 2%만 죽어도 vacuum
  autovacuum_analyze_scale_factor = 0.02,   -- 통계도 더 자주 갱신
  autovacuum_vacuum_threshold     = 1000
);

대량 변경 직후 수동 ANALYZE

autovacuum의 analyze를 기다리지 말고, 배치 작업 끝에 직접 통계를 갱신한다. 통계가 최신이어야 플래너가 옳은 계획을 세운다.

-- 대량 UPDATE/적재 직후
ANALYZE document_chunks;
 
-- bloat가 이미 심하고 다운타임 창이 있으면 물리적 회수
VACUUM (ANALYZE) document_chunks;      -- 온라인, 공간을 OS에 반납하진 않음
-- VACUUM FULL document_chunks;        -- 배타 락 + 테이블 재작성, 운영 중 금지

VACUUM은 죽은 튜플 공간을 테이블 내부에서 재사용 가능하게 만들 뿐 OS로 반납하지는 않는다. 파일 크기까지 줄이려면 VACUUM FULL이 필요하지만 이는 ACCESS EXCLUSIVE 락을 걸어 테이블을 완전히 멈추므로 운영 중에는 피하고, 온라인 재구성이 필요하면 pg_repack을 쓴다.

HOT update와 fillfactor로 bloat 예방

죽은 튜플을 사후에 청소하는 것보다, 애초에 덜 만드는 것이 낫다. UPDATE가 인덱스에 포함되지 않은 컬럼만 바꾸고 같은 페이지에 새 버전을 넣을 공간이 있으면, PostgreSQL은 HOT(Heap-Only Tuple) update를 수행해 인덱스를 건드리지 않고 죽은 버전도 빠르게 회수한다. churn이 큰 테이블은 fillfactor를 낮춰 페이지에 여유 공간을 남기면 HOT update 비율이 올라간다.

-- 페이지의 10%를 UPDATE 여유 공간으로 남긴다 (기본 100)
ALTER TABLE document_chunks SET (fillfactor = 90);
-- 이후 재작성되어야 적용됨 (VACUUM FULL / pg_repack / 신규 삽입분)

pg_stat_user_tables의 n_tup_hot_upd / n_tup_upd 비율로 HOT update가 잘 일어나는지 확인한다. 비율이 낮다면 자주 바뀌는 컬럼에 인덱스가 걸려 있거나 페이지 여유가 없는 것이다.

인덱스 bloat와 재빌드

테이블뿐 아니라 인덱스도 붓는다. 죽은 튜플이 많던 테이블의 인덱스는 페이지가 성겨져 스캔이 무거워진다. 운영 중이라면 락 없이 재빌드한다.

-- 온라인 재빌드 (테이블 쓰기를 막지 않음)
REINDEX INDEX CONCURRENTLY idx_chunks_doc;

REINDEX INDEX CONCURRENTLY는 안전한 스키마 마이그레이션의 CONCURRENTLY 인덱스와 같은 원리로, 트랜잭션 블록 밖에서 실행해야 하며 실패 시 무효 인덱스를 남길 수 있다.

함정·트러블슈팅

진단 쿼리

-- dead tuple이 많은 테이블 상위 10개
SELECT relname,
       n_live_tup,
       n_dead_tup,
       round(n_dead_tup::numeric / NULLIF(n_live_tup, 0), 3) AS dead_ratio,
       last_autovacuum,
       last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;

dead_ratio가 지속적으로 높은데 last_autovacuum이 오래됐다면 스케일 팩터가 너무 높거나 autovacuum이 다른 테이블에 밀려 못 오는 것이다.

흔한 함정

증상	원인	대응
dead_ratio 높음 + last_autovacuum 오래됨	scale_factor 기본(0.2)이 큰 테이블에 과함	테이블별 scale_factor 하향
vacuum이 도는데도 안 줄어듦	오래된 트랜잭션/replication slot이 xmin을 붙잡음	pg_stat_activity, pg_replication_slots의 오래된 xmin 확인
계획이 갑자기 나빠짐	대량 변경 후 통계 낡음	ANALYZE 즉시 실행
autovacuum이 부하를 못 따라감	워커 부족/비용 제한	autovacuum_max_workers, autovacuum_vacuum_cost_limit 상향

• 오래된 트랜잭션이 vacuum을 막는다 — VACUUM은 아직 살아있는 트랜잭션이 볼 수 있는 튜플을 회수하지 못한다. idle in transaction으로 오래 열려 있는 세션이나, 소비되지 않는 replication slot이 있으면 dead가 계속 쌓인다. pg_stat_activity에서 오래된 xact_start를 찾아 정리한다.
• wraparound 방지 vacuum — 트랜잭션 ID 소진을 막는 anti-wraparound autovacuum은 취소해도 계속 돌아온다. 이걸 억지로 막지 말고, 원인이 되는 대량 churn 자체를 줄이거나 vacuum 처리량을 높인다.

정리

• 기본 autovacuum_vacuum_scale_factor = 0.2는 큰 테이블에서 “20%가 죽어야 청소”라는 뜻이라, 220만 행 테이블을 2회 전량 UPDATE하면 ~44만 dead tuple이 상주할 수 있다.
• 해법은 테이블별 scale_factor 하향(예: 0.02)과 대량 변경 직후 ANALYZE다. 전역 변경은 신중히.
• pg_stat_user_tables의 n_dead_tup과 last_autovacuum으로 상시 진단한다.
• vacuum이 도는데도 안 줄면 오래된 트랜잭션/replication slot이 xmin을 붙잡고 있는지 본다.

관련: pgvector - 대규모 임베딩 적재 튜닝 · 커넥션 관리와 asyncpg 함정 · 안전한 스키마 마이그레이션