beautifulsoups.dev
Published on

PostgreSQL autovacuum 멈춤으로 테이블 폭증 해결

Authors

서버 디스크가 갑자기 꽉 차고, 쿼리는 점점 느려지며, pg_stat_user_tables를 보면 업데이트/삭제가 많은 테이블의 크기만 기하급수적으로 커지는 경우가 있습니다. 대부분은 autovacuum이 제대로 동작하지 않아 dead tuple이 회수되지 못하고 bloat(팽창) 이 발생한 케이스입니다.

이 글은 “왜 autovacuum이 안 도는지”를 감으로 때려 맞추는 대신, 관측 → 원인 분류 → 즉시 완화 → 근본 해결(튜닝/운영 습관) 순서로 정리합니다.

> 운영 환경이 Kubernetes/EKS 위라면, DB 폭증이 결국 Pod OOM 또는 노드 디스크 압박으로 이어져 연쇄 장애가 나기 쉽습니다. 관련해서는 EKS Pod OOMKilled 반복 원인과 메모리·GC·Limit 튜닝도 함께 참고하면 좋습니다.

1) autovacuum이 “안 돈다”의 정확한 의미

먼저 용어를 분해해야 합니다.

  • autovacuum launcher: 워커를 기동하고 작업을 스케줄링
  • autovacuum worker: 실제 VACUUM/ANALYZE 수행
  • VACUUM: dead tuple을 재사용 가능하게 표시, visibility map 갱신
  • VACUUM FULL: 테이블을 재작성(락 큼), 즉시 물리 공간 반환
  • ANALYZE: 통계 갱신(플래너 품질)

“autovacuum이 안 돈다”는 말은 보통 아래 중 하나입니다.

  1. 워커가 아예 안 뜬다(설정/권한/프로세스 문제)
  2. 워커는 뜨는데 특정 테이블에서 계속 막혀 있다(락/장기 트랜잭션)
  3. 워커는 돌지만 임계치가 너무 커서 실행 타이밍이 늦다(설정 미스)
  4. 워커가 돌다가 비용 제한(cost limit) 때문에 너무 느리다(I/O 여유 대비 보수적)
  5. vacuum이 돌아도 인덱스/테이블 bloat가 이미 커져 효과가 미미하다(재작성 필요)

2) 10분 진단 체크리스트(필수 쿼리)

아래 쿼리만으로도 “지금 autovacuum이 왜 효과가 없는지”를 상당히 좁힐 수 있습니다.

2.1 autovacuum 설정 확인

SHOW autovacuum;
SHOW autovacuum_max_workers;
SHOW autovacuum_naptime;
SHOW autovacuum_vacuum_threshold;
SHOW autovacuum_vacuum_scale_factor;
SHOW autovacuum_analyze_threshold;
SHOW autovacuum_analyze_scale_factor;
SHOW autovacuum_vacuum_cost_limit;
SHOW autovacuum_vacuum_cost_delay;
  • autovacuum = on이 아니면 게임 끝입니다.
  • autovacuum_max_workers가 너무 작으면(예: 1~2) 큰 테이블이 여러 개일 때 backlog가 쌓입니다.

2.2 현재 vacuum이 돌고 있는지(또는 막혔는지)

SELECT
  pid,
  now() - xact_start AS xact_age,
  now() - query_start AS query_age,
  wait_event_type,
  wait_event,
  state,
  query
FROM pg_stat_activity
WHERE query ILIKE '%vacuum%'
   OR backend_type ILIKE '%autovacuum%'
ORDER BY query_age DESC;
  • wait_event_type = Lock 이면 락 경합으로 vacuum이 대기 중일 수 있습니다.
  • xact_age가 비정상적으로 긴 세션이 있으면(수시간~수일) vacuum이 “완료는 했는데 공간을 못 줄이거나” freeze 관련 작업이 밀립니다.

2.3 “장기 트랜잭션/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_age DESC
LIMIT 30;
  • 특히 state = 'idle in transaction'은 vacuum의 천적입니다.
  • 애플리케이션 커넥션 풀에서 트랜잭션을 열어둔 채로 반환하는 버그가 흔한 원인입니다.

2.4 어떤 테이블이 bloat/미청소 상태인지

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_autovacuum,
  last_vacuum,
  last_autoanalyze,
  vacuum_count,
  autovacuum_count
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;
  • n_dead_tup 상위 테이블이 “폭증의 진원지”일 확률이 높습니다.
  • last_autovacuum이 오래 비어 있거나 과거 시점이면 스케줄/임계치 문제일 수 있습니다.

2.5 wraparound 위험(autovacuum이 freeze 못하고 밀릴 때)

SELECT
  datname,
  age(datfrozenxid) AS xid_age,
  current_setting('autovacuum_freeze_max_age') AS freeze_max_age
FROM pg_database
ORDER BY xid_age DESC;
  • xid_age가 autovacuum_freeze_max_age에 가까워지면 운영상 매우 위험합니다(쓰기 중단까지 갈 수 있음).

3) 대표 원인 6가지와 해결 방향

3.1 장기 트랜잭션 때문에 vacuum이 “청소를 못 함”

증상

  • pg_stat_activity에 오래된 트랜잭션
  • dead tuple이 계속 늘고 vacuum이 돌아도 줄지 않음

해결

  1. 원인 세션을 종료(운영 영향 판단 후)
-- 신중히 사용
SELECT pg_terminate_backend(<pid>);
  1. 애플리케이션에서
  • 트랜잭션 범위를 최소화
  • idle_in_transaction_session_timeout 설정
ALTER SYSTEM SET idle_in_transaction_session_timeout = '60s';
SELECT pg_reload_conf();

3.2 vacuum이 락에 막힘(DDL/긴 쿼리/배치)

증상

  • autovacuum worker가 wait_event_type=Lock
  • 특정 테이블에서 오래 대기

해결

  • 배치/DDL 시간대를 vacuum과 분리
  • lock_timeout / statement_timeout을 업무 특성에 맞게 설정
  • 대규모 업데이트는 가능하면 작게 쪼개서 커밋

3.3 임계치(scale factor) 때문에 “너무 늦게” 도는 문제

PostgreSQL의 autovacuum 트리거는 대략:

  • vacuum 트리거 튜플 수 ≈ autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor * reltuples

즉, 테이블이 클수록 scale factor가 크면 vacuum이 너무 늦게 시작합니다.

해결(테이블 단위 튜닝 권장)

ALTER TABLE public.big_table SET (
  autovacuum_vacuum_scale_factor = 0.02,
  autovacuum_vacuum_threshold = 10000,
  autovacuum_analyze_scale_factor = 0.01,
  autovacuum_analyze_threshold = 5000
);
  • 대형/핫 테이블은 scale_factor를 0.2 같은 기본값에 두면 bloat가 커지기 쉽습니다.

3.4 autovacuum worker 수가 부족해서 backlog가 쌓임

증상

  • vacuum 대상 테이블이 많은데 실제 worker는 소수
  • last_autovacuum이 전반적으로 오래됨

해결

ALTER SYSTEM SET autovacuum_max_workers = 6;
ALTER SYSTEM SET autovacuum_naptime = '10s';
SELECT pg_reload_conf();
  • 워커를 늘리면 I/O가 늘 수 있으니, 스토리지 성능과 함께 조정합니다.

3.5 cost limit이 너무 보수적이라 vacuum이 “너무 느림”

증상

  • autovacuum은 돌지만 dead tuple이 줄어드는 속도가 업무 쓰기량을 못 따라감

해결

  • 시스템 여유가 있다면 cost 제한을 완화
ALTER SYSTEM SET autovacuum_vacuum_cost_limit = 4000;
ALTER SYSTEM SET autovacuum_vacuum_cost_delay = '2ms';
SELECT pg_reload_conf();
  • 또는 특정 테이블만 더 공격적으로:
ALTER TABLE public.big_table SET (
  autovacuum_vacuum_cost_limit = 8000,
  autovacuum_vacuum_cost_delay = 0
);

3.6 이미 bloat가 커져서 일반 VACUUM으로는 디스크가 안 줄어듦

중요 포인트:

  • 일반 VACUUM은 파일 크기를 즉시 줄이지 않습니다(재사용 가능 상태만 표시)
  • 디스크를 당장 확보해야 하면 VACUUM FULL 또는 pg_repack류가 필요합니다.

선택지

(A) VACUUM (ANALYZE)로 성능 회복 우선

VACUUM (ANALYZE, VERBOSE) public.big_table;
  • 디스크 반환은 제한적이지만, 인덱스/플래너 통계 측면에서 즉효가 있습니다.

(B) VACUUM FULL (락 큼, 윈도우 필요)

VACUUM (FULL, ANALYZE, VERBOSE) public.big_table;
  • 테이블 재작성 + 큰 락(사실상 운영 중단급) → 점검 창에서 수행.

(C) 재작성 방식(예: 파티셔닝/교체)

  • 로그성/이력성 테이블이면 파티셔닝 후 오래된 파티션 DROP이 가장 확실한 “디스크 즉시 회수” 전략입니다.

4) 즉시 완화(runbook): 디스크 폭증 상황에서의 순서

4.1 원인 세션부터 제거(장기 트랜잭션)

  • pg_stat_activity로 최장 트랜잭션 확인 → 업무 영향 검토 → 종료

4.2 상위 bloat 테이블부터 수동 vacuum

-- 우선순위: dead tuple 최상위 테이블
VACUUM (ANALYZE) public.big_table;
  • 동시에 여러 테이블을 무작정 vacuum 돌리면 I/O가 터질 수 있으니, 핫 테이블 1~2개씩 진행합니다.

4.3 autovacuum 튜닝은 “테이블 단위”로 먼저

  • 전역 설정을 급격히 바꾸면 전체 부하가 튈 수 있습니다.
ALTER TABLE public.big_table SET (
  autovacuum_vacuum_scale_factor = 0.02,
  autovacuum_analyze_scale_factor = 0.01
);

4.4 디스크가 이미 임계면: VACUUM FULL/재작성/파티션 DROP 검토

  • 운영 중단이 가능한지 먼저 판단
  • 불가능하면 임시로 스토리지 증설 후(클라우드 볼륨 확장) 점검 창에 구조적 조치

5) 재발 방지: 관측/알림/운영 습관

5.1 bloat 조기 경보 지표

  • 테이블별 n_dead_tup, dead_pct
  • last_autovacuum가 일정 시간 이상 갱신되지 않는 테이블 수
  • DB별 age(datfrozenxid)

예: dead tuple 상위 테이블을 주기적으로 뽑아 슬랙/알림으로 보내는 쿼리

SELECT
  schemaname,
  relname,
  n_dead_tup,
  n_live_tup,
  round(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 2) AS dead_pct,
  last_autovacuum
FROM pg_stat_user_tables
WHERE n_dead_tup > 100000
ORDER BY dead_pct DESC
LIMIT 20;

5.2 애플리케이션 측 습관

  • “트랜잭션을 열고 오래 잡는” 패턴 제거
  • 배치 작업은 대량 UPDATE/DELETE를 청크 단위로 커밋
  • 커넥션 풀에서 autoCommit=false + 예외 시 롤백 누락 같은 실수를 방지

5.3 (EKS/컨테이너 환경) 리소스 연쇄 장애 방지

DB가 폭증하면 스토리지뿐 아니라 메모리/CPU 압박이 커지고, 결국 애플리케이션 Pod까지 OOM/타임아웃이 전파됩니다. 장애가 “DB 한 곳”에서 끝나지 않는 이유입니다.

6) 실전 예시: “핫 테이블 1개만” 먼저 살리는 튜닝 세트

가장 자주 쓰는 안전한 접근은 이렇습니다.

  1. 장기 트랜잭션 제거
  2. 해당 테이블에만 autovacuum 민감도 상향
  3. 수동 vacuum + analyze
-- 1) (필요 시) 장기 트랜잭션 종료
-- SELECT pg_terminate_backend(pid) ...

-- 2) 테이블 단위 autovacuum 강화
ALTER TABLE public.events SET (
  autovacuum_vacuum_scale_factor = 0.01,
  autovacuum_vacuum_threshold = 20000,
  autovacuum_analyze_scale_factor = 0.005,
  autovacuum_analyze_threshold = 10000,
  autovacuum_vacuum_cost_limit = 8000,
  autovacuum_vacuum_cost_delay = 0
);

-- 3) 즉시 통계/청소
VACUUM (ANALYZE, VERBOSE) public.events;

이후 며칠간 n_dead_tup, last_autovacuum 추이를 보면서 전역 설정을 조정할지 판단합니다.

7) 마무리: “autovacuum이 안 돈다”를 끝내는 핵심 3줄

  • 장기 트랜잭션/락이 있으면 autovacuum은 “돌아도 효과가 없다”. 먼저 이것부터 제거한다.
  • 대형 테이블은 기본 scale_factor로는 늦다. 테이블 단위로 민감도(0.01~0.05) 조정이 실전에서 가장 안전하다.
  • 디스크를 당장 줄여야 한다면 일반 VACUUM으로는 부족하다. VACUUM FULL/재작성/파티션 전략을 점검 창에 계획한다.

운영 중인 PostgreSQL 버전, 문제 테이블의 쓰기 패턴(UPDATE/DELETE 비율), 스토리지 타입(EBS gp3/io2 등)을 알려주면 위 튜닝 값을 더 보수적/공격적으로 구체화해서 제안할 수 있습니다.

Discuss on Twitter • View on GitHub