PostgreSQL autovacuum 지연으로 bloat 폭증 해결

서버가 멀쩡한데도 PostgreSQL 쿼리가 갑자기 느려지고 디스크가 빠르게 차오르는 경우, 원인은 종종 autovacuum 지연으로 인한 bloat 폭증입니다. 특히 업데이트/삭제가 많은 테이블(세션, 이벤트, 큐, 주문 상태 테이블 등)에서 dead tuple이 쌓이면, 테이블과 인덱스가 비대해져 캐시 효율이 떨어지고 I/O가 증가하며, 결국 전체 성능이 무너집니다.

이 글에서는 autovacuum이 왜 밀리는지, 현재 bloat/지연을 어떻게 측정하는지, 그리고 **장애 대응(응급 처치) + 재발 방지(튜닝)**를 한 번에 정리합니다.

운영 환경에서 DB 문제는 애플리케이션 장애로 전이되기 쉽습니다. 예를 들어 DB가 느려져 타임아웃이 늘면 L7에서 504가 간헐적으로 튀기도 합니다. 인프라에서만 원인을 찾다가 시간을 쓰는 경우가 많으니, 증상 연쇄를 함께 보는 습관이 중요합니다. 참고: EKS ALB Ingress 504(5xx) 간헐 발생 원인·해결

bloat가 생기는 메커니즘(핵심만)

PostgreSQL은 MVCC 구조라서 UPDATE는 사실상 DELETE + INSERT처럼 동작합니다. 기존 행은 즉시 물리적으로 지워지지 않고, 더 이상 보이지 않는 버전(= dead tuple)로 남습니다.

• VACUUM은 dead tuple이 차지한 공간을 재사용 가능 상태로 표시합니다.
• VACUUM FULL은 테이블을 재작성해서 물리적으로 공간을 줄입니다(락이 큼).
• 인덱스는 dead tuple이 늘수록 함께 비대해지고, 스캔 비용이 증가합니다.

autovacuum은 이 과정을 자동으로 돌려주는데, 다음 중 하나라도 걸리면 쉽게 밀립니다.

• 테이블이 너무 많고 변경량이 많아 워커가 부족
• autovacuum_vacuum_cost_limit이 너무 낮아 작업이 지나치게 느림
• autovacuum_max_workers가 작음
• 긴 트랜잭션이 오래 살아 있어 vacuum이 dead tuple을 치우지 못함
• autovacuum가 I/O 경쟁에서 계속 밀림(스토리지/체크포인트/대형 쿼리)

1단계: autovacuum 지연과 bloat 징후 빠르게 확인

1) 테이블 단위 vacuum 상태 확인

아래 쿼리는 테이블별로 dead tuple 추정치와 마지막 vacuum 시점을 봅니다.

SELECT
  schemaname,
  relname,
  n_live_tup,
  n_dead_tup,
  last_vacuum,
  last_autovacuum,
  last_analyze,
  last_autoanalyze,
  vacuum_count,
  autovacuum_count
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 30;

• n_dead_tup가 큰데 last_autovacuum가 오래되었으면 위험 신호입니다.
• n_dead_tup가 n_live_tup 대비 과도하게 크면(예: 수십 퍼센트 이상) 이미 성능 영향이 있을 가능성이 큽니다.

2) autovacuum이 실제로 돌고 있는지, 어디서 막혔는지

진행 중인 vacuum을 확인합니다.

SELECT
  pid,
  datname,
  relid::regclass AS relation,
  phase,
  heap_blks_total,
  heap_blks_scanned,
  heap_blks_vacuumed,
  index_vacuum_count,
  max_dead_tuples,
  num_dead_tuples
FROM pg_stat_progress_vacuum;

phase가 오래 멈춰 있거나, 특정 테이블에서 계속 반복되면 해당 테이블이 병목일 수 있습니다.

3) 가장 흔한 원인: 긴 트랜잭션(Oldest xmin) 확인

vacuum은 모든 세션에서 더 이상 참조하지 않는 튜플만 치울 수 있습니다. 긴 트랜잭션이 있으면 xmin이 오래 잡혀서 vacuum이 사실상 무력화됩니다.

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가 비정상적으로 큰 세션이 있으면 우선적으로 확인해야 합니다.
• 배치가 트랜잭션을 열어둔 채 오래 작업하거나, 커넥션 풀/ORM이 트랜잭션을 제대로 닫지 않는 경우가 많습니다.

2단계: 응급 처치(서비스 영향 최소화)

상황이 이미 나빠졌다면, 튜닝 이전에 지금 쌓인 bloat를 줄여야 합니다.

1) 우선순위 테이블만 수동 VACUUM(ANALYZE)

락 부담이 상대적으로 적고, 즉시 통계 갱신까지 할 수 있습니다.

VACUUM (ANALYZE, VERBOSE) public.big_hot_table;

• 공간이 즉시 줄어들지는 않을 수 있습니다(재사용 가능 상태로만 전환).
• 하지만 인덱스/테이블 스캔 비용이 줄고 플래너가 통계를 갱신해 체감 성능이 좋아지는 경우가 많습니다.

2) 인덱스 bloat가 심하면 REINDEX 고려

특정 인덱스가 비대해져서 랜덤 I/O가 폭증하는 케이스가 흔합니다.

REINDEX INDEX CONCURRENTLY public.idx_big_hot_table_created_at;

• CONCURRENTLY는 운영 중 적용에 유리하지만 더 오래 걸리고 리소스를 씁니다.
• 버전에 따라 제약이 있으니 사전 확인이 필요합니다.

3) 테이블 자체가 과도하게 비대하면 pg_repack 또는 VACUUM FULL

• VACUUM FULL은 테이블을 재작성하며 강한 락이 걸립니다.
• 가능하면 pg_repack(확장/툴)을 검토하는 편이 운영 친화적입니다.

개념적으로는 아래와 같은 흐름입니다.

# 예시(환경에 맞게 조정): pg_repack으로 특정 테이블 재구성
pg_repack --dbname=mydb --table=public.big_hot_table --no-superuser-check

운영에서는 다음 기준으로 선택합니다.

• 당장 디스크가 부족하거나 테이블이 너무 커져서 I/O가 폭발: pg_repack 또는 점검창에 VACUUM FULL
• 인덱스만 비대: REINDEX CONCURRENTLY
• 단순히 dead tuple이 많아 플래너/캐시 효율이 나쁨: VACUUM (ANALYZE)

3단계: autovacuum이 밀린 “진짜 이유”를 찾아 재발 방지

응급 처치만 하면 다시 터집니다. autovacuum 지연은 보통 구조적인 원인이 있습니다.

원인 A: autovacuum 워커 수가 부족

기본값은 보수적이라, 테이블이 많고 쓰기가 활발하면 쉽게 backlog가 생깁니다.

자주 조정하는 파라미터:

• autovacuum_max_workers
• autovacuum_naptime
• maintenance_work_mem

예시(상황에 맞게 점진적으로):

ALTER SYSTEM SET autovacuum_max_workers = '6';
ALTER SYSTEM SET autovacuum_naptime = '10s';
ALTER SYSTEM SET maintenance_work_mem = '1GB';
SELECT pg_reload_conf();

주의:

• 워커를 늘리면 vacuum이 더 자주/동시에 돌아 I/O를 더 씁니다. 스토리지 여유가 없으면 오히려 지연이 늘 수 있어 모니터링이 필요합니다.

원인 B: cost 기반 throttling 때문에 vacuum이 너무 느림

vacuum은 기본적으로 다른 쿼리와의 공존을 위해 “천천히” 돌도록 설계되어 있습니다. 하지만 변경량이 폭증하면 이 속도가 부족해 backlog가 누적됩니다.

주요 파라미터:

• autovacuum_vacuum_cost_limit
• autovacuum_vacuum_cost_delay

예시:

ALTER SYSTEM SET autovacuum_vacuum_cost_limit = '4000';
ALTER SYSTEM SET autovacuum_vacuum_cost_delay = '2ms';
SELECT pg_reload_conf();

• 너무 공격적으로 올리면 사용자 쿼리 latency가 튈 수 있습니다.
• 관측 기반으로 조금씩 올리는 방식이 안전합니다.

원인 C: 특정 테이블만 변경량이 압도적으로 큼(테이블별 튜닝 필요)

전체 설정을 올리면 다른 테이블까지 과하게 vacuum이 돌아 비용이 커질 수 있습니다. 핫 테이블은 테이블 단위 storage parameter로 autovacuum을 별도 튜닝하는 게 정석입니다.

예시: 업데이트/삭제가 많은 테이블은 vacuum 트리거 임계치를 낮춥니다.

ALTER TABLE public.big_hot_table SET (
  autovacuum_vacuum_scale_factor = 0.02,
  autovacuum_vacuum_threshold = 2000,
  autovacuum_analyze_scale_factor = 0.05,
  autovacuum_analyze_threshold = 2000
);

• scale_factor는 테이블 크기에 비례하는 트리거 비율입니다.
• 큰 테이블은 기본값(예: 0.2)이 너무 커서 vacuum이 “너무 늦게” 시작합니다.

원인 D: 긴 트랜잭션/idle in transaction이 vacuum을 막음

위에서 확인한 긴 트랜잭션이 반복적으로 발생하면, autovacuum을 아무리 튜닝해도 해결이 어렵습니다.

실전 대응:

• 애플리케이션에서 트랜잭션 범위를 줄이기(특히 배치)
• 커넥션 풀에서 누수/미반납 점검
• DB에서 방어적으로 타임아웃 설정

-- 트랜잭션을 오래 잡고 있는 세션을 줄이기 위한 방어 설정 예시
ALTER SYSTEM SET idle_in_transaction_session_timeout = '60s';
ALTER SYSTEM SET statement_timeout = '30s';
SELECT pg_reload_conf();

주의:

• statement_timeout은 정상 쿼리도 끊을 수 있으니 서비스 특성에 맞게 적용해야 합니다.

4단계: bloat를 수치로 보고(대략) 추적하는 방법

PostgreSQL은 “정확한 bloat”를 기본 뷰로 바로 주지 않습니다. 하지만 운영에서는 다음 조합으로 충분히 추적 가능합니다.

1) 테이블/인덱스 크기 상위 확인

SELECT
  n.nspname AS schema,
  c.relname AS name,
  pg_size_pretty(pg_total_relation_size(c.oid)) AS total_size,
  pg_size_pretty(pg_relation_size(c.oid)) AS table_size,
  pg_size_pretty(pg_indexes_size(c.oid)) AS indexes_size
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = 'r'
ORDER BY pg_total_relation_size(c.oid) DESC
LIMIT 30;

크기 상위 테이블이 “업무상 그 정도로 커야 하는지”부터 검증합니다. 업무 데이터량 대비 과도하면 bloat 가능성이 큽니다.

2) 확장 도구로 bloat 추정(가능하면)

운영에서 많이 쓰는 선택지:

• pgstattuple 확장으로 튜플/프리 스페이스 추정

-- 1) 확장 설치(권한 필요)
CREATE EXTENSION IF NOT EXISTS pgstattuple;

-- 2) 특정 테이블의 상태 확인
SELECT * FROM pgstattuple('public.big_hot_table');

이 결과의 free_space, dead_tuple_len 등이 크면 vacuum이 따라가지 못했거나 테이블이 비대해졌을 가능성이 큽니다.

5단계: 운영에서 자주 하는 “안전한” 개선 시나리오

시나리오 1) 핫 테이블만 먼저 잡는다

1. pg_stat_user_tables에서 n_dead_tup 상위 테이블 식별
2. 해당 테이블에 VACUUM (ANALYZE) 수동 수행
3. 테이블별 autovacuum 임계치 낮추기
4. 인덱스 bloat 의심 시 REINDEX INDEX CONCURRENTLY

시나리오 2) autovacuum backlog가 상시 발생한다

1. autovacuum_max_workers 증가
2. maintenance_work_mem 확대
3. cost limit 상향(점진적으로)
4. 스토리지 IOPS/Throughput 병목 여부 확인

이때 DB 지연이 곧바로 API 지연으로 전파되면, 인프라 레벨에서는 504나 5xx로만 보이는 경우가 많습니다. 애플리케이션과 인프라를 함께 보는 관점이 도움이 됩니다. 참고: EKS ALB Ingress 504(5xx) 간헐 발생 원인·해결

체크리스트(재발 방지)

• n_dead_tup 상위 테이블을 주기적으로 리포팅한다
• 핫 테이블은 테이블별 autovacuum 설정을 별도로 둔다
• 긴 트랜잭션을 만드는 배치/잡을 제거하거나 쪼갠다
• idle_in_transaction_session_timeout 같은 방어 설정을 둔다
• 인덱스 bloat가 반복되면 주기적 REINDEX CONCURRENTLY 또는 pg_repack 운영 절차를 만든다
• 대형 테이블은 파티셔닝(업무 특성에 따라)도 검토한다

마무리

autovacuum 지연으로 인한 bloat 폭증은 “어느 날 갑자기 DB가 느려진” 것처럼 보이지만, 실제로는 vacuum이 처리해야 할 변경량이 누적된 결과인 경우가 많습니다.

핵심은 두 가지입니다.

1. 지금 쌓인 bloat를 줄이는 응급 처치(VACUUM (ANALYZE), REINDEX CONCURRENTLY, 필요 시 pg_repack)
2. autovacuum이 밀린 구조적 원인을 제거(워커/비용/테이블별 임계치/긴 트랜잭션)

이 두 축을 같이 잡으면, 디스크 증가와 쿼리 지연을 안정적으로 되돌리고 재발도 크게 줄일 수 있습니다.