beautifulsoups.dev
Published on

PostgreSQL autovacuum 지연으로 팽창·슬로우쿼리 잡기

Authors

서버가 갑자기 느려졌는데 CPU는 애매하게 낮고, 디스크 I/O만 치솟고, 쿼리 플랜은 인덱스를 타지 않거나 불필요한 Seq Scan을 반복한다면 autovacuum 지연으로 인한 bloat(팽창) 를 의심할 타이밍입니다. PostgreSQL은 MVCC 특성상 UPDATE/DELETE가 즉시 공간을 회수하지 못하고, 죽은 튜플(dead tuple) 을 VACUUM이 정리해야만 테이블/인덱스가 건강해집니다.

이 글은 “왜 autovacuum이 밀리는지”, “어떤 지표로 확정하는지”, “테이블 팽창과 슬로우쿼리를 어떻게 되돌리는지”를 운영자 관점에서 단계적으로 다룹니다.

> 인프라에서 병목을 진단하는 접근은 다른 영역에서도 유사합니다. 예를 들어 커널 레벨에서 메모리 압박으로 장애가 나는 경우는 리눅스 OOM Killer로 프로세스 죽을 때 진단법처럼 증상→지표→원인 분해 흐름이 중요합니다.

autovacuum 지연이 만드는 3가지 문제

1) 테이블/인덱스 팽창(bloat)

  • UPDATE는 내부적으로 새 버전의 row를 INSERT 하고, 기존 버전은 dead tuple로 남습니다.
  • DELETE도 dead tuple을 남깁니다.
  • autovacuum이 제때 돌지 않으면 dead tuple이 쌓여 테이블 파일이 커지고, 인덱스도 불필요한 엔트리가 늘어 랜덤 I/O 를 유발합니다.

2) 통계(ANALYZE) 부정확 → 잘못된 실행 계획

autovacuum은 VACUUM뿐 아니라 autoanalyze도 수행합니다. 지연되면 통계가 낡아져서:

  • 선택도 추정이 틀어지고
  • Nested Loop 폭발 / Hash Join 메모리 부족 / Seq Scan 남발 같은 슬로우쿼리 패턴이 생깁니다.

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

vacuum이 오래 밀리면 datfrozenxid가 뒤처져 wraparound 방지용 강제 vacuum이 발생할 수 있고, 이 경우 운영에 큰 영향을 줍니다(최악은 쓰기 중단).

“정말 autovacuum 문제인가?” 빠른 확인 체크리스트

1) dead tuple과 vacuum/analyze 상태 확인

아래 쿼리는 테이블별로 dead tuple이 많은 순서와 마지막 vacuum/analyze 시점을 봅니다.

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이 오래 전이면 유력합니다.
  • dead_pct가 높으면(예: 20~30% 이상) 실제 쿼리 성능에 체감이 오기 시작합니다.

2) autovacuum이 “돌고는 있는데” 따라가지 못하는지 확인

현재 실행 중인 vacuum을 봅니다.

SELECT
  pid,
  datname,
  relid::regclass AS table_name,
  phase,
  heap_blks_scanned,
  heap_blks_total,
  index_vacuum_count,
  max_dead_tuples,
  num_dead_tuples
FROM pg_stat_progress_vacuum
ORDER BY heap_blks_scanned DESC;
  • vacuum이 특정 대형 테이블에서 오래 머물거나,
  • num_dead_tuples가 계속 높게 유지되면 쓰기량이 vacuum 처리량을 초과하고 있을 가능성이 큽니다.

3) 슬로우쿼리가 “I/O bound”인지 확인

  • bloat는 보통 버퍼 히트율 하락 + 디스크 읽기 증가로 나타납니다.
  • pg_stat_statements가 있다면 상위 쿼리의 shared_blks_read가 증가했는지 확인하세요.

autovacuum이 지연되는 대표 원인 7가지

1) autovacuum 워커 수 부족

기본값 autovacuum_max_workers가 작으면(예: 3) 여러 테이블에서 동시에 청소가 필요할 때 병목이 납니다.

2) 비용 제한(cost-based throttling)으로 너무 느리게 동작

autovacuum_vacuum_cost_limit, autovacuum_vacuum_cost_delay는 autovacuum의 디스크 사용을 제한합니다. 값이 보수적이면 “돌긴 도는데” 너무 느립니다.

3) 특정 테이블의 업데이트/삭제가 폭주

예: 이벤트 로그 테이블에 상태 업데이트가 반복되거나, 큐 테이블에서 UPDATE가 잦은 경우.

4) 장기 트랜잭션(오래 열린 tx)이 vacuum을 막음

PostgreSQL은 아직 “볼 수 있는” 오래된 스냅샷이 있으면 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;
  • xact_age가 비정상적으로 큰 세션이 있으면 먼저 해결해야 합니다.
  • 커넥션 풀/배치 잡/리포팅 쿼리가 원인인 경우가 흔합니다.

5) vacuum freeze가 뒤처짐(특히 대형 테이블)

freeze는 더 비싸고 오래 걸릴 수 있어, 정기적인 유지보수 창이 필요합니다.

6) 인덱스가 너무 많거나, 불필요한 인덱스로 vacuum 부담 증가

인덱스가 많으면 vacuum이 처리해야 할 작업이 증가합니다(특히 인덱스 vacuum).

7) 디스크/스토리지 성능 한계

bloat가 커질수록 vacuum 자체도 더 많은 I/O를 요구 → 악순환.

해결 전략: “지금 불 끄기”와 “재발 방지”를 분리

1단계: 즉시 복구(응급 처치)

A) 문제 테이블을 선별해 수동 VACUUM(ANALYZE)

가장 dead tuple이 큰 테이블부터 수동으로 처리합니다.

VACUUM (ANALYZE, VERBOSE) public.big_table;
  • 일반 VACUUM은 공간을 OS에 반환하지는 않지만, dead tuple을 정리해 쿼리 성능을 회복시키는 데 즉효가 있습니다.
  • ANALYZE로 통계를 갱신해 플랜을 정상화합니다.

B) 장기 트랜잭션 제거(가장 중요한 선행조건)

장기 트랜잭션이 있으면 vacuum이 “청소할 수 있는 범위”가 제한되어 효과가 떨어집니다.

  • 애플리케이션의 idle in transaction을 끊거나
  • 운영 정책으로 타임아웃을 강제합니다.
-- 예: 5분 넘게 열린 트랜잭션을 강제 종료(운영 정책에 맞게 조정)
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
  AND now() - xact_start > interval '5 minutes';

C) 팽창이 심각하면 VACUUM FULL / CLUSTER / pg_repack 고려

  • VACUUM FULL: 테이블을 재작성해 디스크 공간을 회수하지만, 강한 락(AccessExclusive) 이 필요합니다.
  • CLUSTER: 인덱스 순서로 테이블 재정렬(락 필요) + 성능 개선 가능.
  • pg_repack: 온라인에 가깝게 재작성(추가 확장/권한/운영 복잡도 필요).

운영 중단이 가능한 유지보수 창이 없다면 pg_repack이 현실적인 선택이 됩니다.

2단계: autovacuum 튜닝(재발 방지 핵심)

A) 전역 파라미터(서버 공통) 조정

아래는 “기본값이 보수적이라 따라가지 못하는” 환경에서 자주 조정하는 항목입니다. 값은 워크로드/스토리지/CPU에 따라 달라서, 작게 시작해 관측하며 올리는 방식이 안전합니다.

# postgresql.conf 예시(개념용)
autovacuum = on
autovacuum_max_workers = 6

autovacuum_vacuum_cost_limit = 4000
# 0이면 delay 없이 달림(주의). 보통 2~10ms 사이에서 조정
autovacuum_vacuum_cost_delay = 2ms

# vacuum이 자주 돌도록(테이블별 override 권장)
autovacuum_vacuum_scale_factor = 0.05
# analyze도 자주 갱신
autovacuum_analyze_scale_factor = 0.02
  • autovacuum_max_workers: 동시에 처리할 테이블 수를 늘려 적체를 줄입니다.
  • cost_limit/delay: vacuum 처리량(=I/O 사용량)을 늘립니다.
  • scale_factor: 테이블이 커질수록 기준치가 커지는 문제를 완화합니다.

B) “문제 테이블만” 더 공격적으로(테이블별 스토리지 파라미터)

대형/고변경 테이블은 전역 기본값으로는 부족합니다.

ALTER TABLE public.big_table SET (
  autovacuum_vacuum_scale_factor = 0.02,
  autovacuum_vacuum_threshold = 5000,
  autovacuum_analyze_scale_factor = 0.01,
  autovacuum_analyze_threshold = 3000
);
  • threshold + scale_factor 조합으로 “작은 테이블은 너무 자주”, “큰 테이블은 너무 늦게”라는 극단을 줄입니다.
  • 특히 수천만~수억 row 테이블은 scale_factor를 낮추지 않으면 vacuum 트리거가 지나치게 늦습니다.

C) autovacuum 로그로 실제 동작을 관측

vacuum이 언제, 얼마나 오래, 얼마나 청소했는지 로그를 남기면 튜닝이 쉬워집니다.

log_autovacuum_min_duration = 5s
  • 5초 이상 걸린 autovacuum만 로그에 남겨 노이즈를 줄입니다.

3단계: 슬로우쿼리 관점에서의 후속 조치

A) 통계 갱신 후에도 느리면 인덱스/쿼리 자체 점검

bloat를 줄였는데도 느리다면:

  • 인덱스 누락
  • 조건절의 함수 적용으로 인덱스 미사용
  • 조인 순서/카디널리티 문제 를 의심해야 합니다.

B) HOT update를 유도해 인덱스 bloat 자체를 줄이기

가능하다면 UPDATE가 인덱스를 건드리지 않도록 설계하면(인덱스 컬럼 업데이트 최소화) HOT update가 늘어 vacuum 부담이 줄 수 있습니다.

C) 파티셔닝으로 vacuum 대상 크기를 줄이기

시간 기반 데이터(로그/이벤트)는 월/일 단위 파티션으로 나누면:

  • vacuum이 작은 단위로 수행
  • 오래된 파티션은 read-only로 전환
  • 드롭으로 즉시 공간 회수 가 가능해집니다.

운영에서 자주 놓치는 함정 5가지

  1. idle in transaction 방치: vacuum이 아무리 돌아도 죽은 튜플을 못 치웁니다.
  2. 전역 scale_factor만 낮추기: 작은 테이블에 autovacuum이 과도하게 돌며 오히려 잡음을 만듭니다. 큰 테이블은 테이블별 override가 정답인 경우가 많습니다.
  3. VACUUM FULL을 무계획으로 실행: 락으로 서비스가 멈춥니다.
  4. 인덱스 과다: vacuum/쓰기 비용 증가 + 플래너 혼란.
  5. 스토리지 한계: bloat→I/O 증가→vacuum 지연→bloat 악화의 루프를 끊으려면 스토리지 성능/IOPS도 함께 봐야 합니다.

실전 점검/복구 플레이북(요약)

1) 팽창/지연 확정

  • pg_stat_user_tables에서 n_dead_tup, last_autovacuum 확인
  • pg_stat_progress_vacuum으로 vacuum 적체 확인

2) 방해 요인 제거

  • 장기 트랜잭션/idle in transaction 정리

3) 성능 즉시 회복

  • 상위 문제 테이블부터 VACUUM (ANALYZE)
  • 필요 시 유지보수 창에 VACUUM FULL 또는 pg_repack

4) 재발 방지

  • autovacuum_max_workers, cost 파라미터 조정
  • 대형/고변경 테이블에 테이블별 autovacuum 설정
  • log_autovacuum_min_duration로 관측

마무리

PostgreSQL에서 autovacuum은 “있으면 좋은 기능”이 아니라 성능과 안정성을 유지하는 필수 백그라운드 작업입니다. autovacuum 지연이 시작되면 bloat와 통계 부정확이 함께 누적되어, 어느 순간부터는 쿼리 튜닝만으로는 회복이 안 되는 구간에 진입합니다.

따라서 핵심은 (1) dead tuple/진행 상황을 지표로 확인하고, (2) 장기 트랜잭션 같은 방해 요인을 제거한 뒤, (3) 테이블별로 autovacuum을 “워크로드에 맞게” 조정하는 것입니다. 이 3가지만 체계화해도 테이블 팽창과 슬로우쿼리의 상당수를 예방할 수 있습니다.

Discuss on Twitter • View on GitHub