beautifulsoups.dev
Published on

PostgreSQL VACUUM 안 먹힐 때 bloat 10분 진단·해결

Authors

서버에서 VACUUM을 돌렸는데도 디스크 사용량이 그대로이거나, 쿼리 성능이 회복되지 않거나, 오히려 더 느려지는 상황이 종종 있습니다. 이런 경우는 대부분 “VACUUM이 안 먹힌다”기보다 VACUUM이 할 수 있는 일과 할 수 없는 일이 섞여 있거나, **bloat(테이블/인덱스 팽창)**가 이미 커져서 “청소”만으로는 되돌릴 수 없는 상태인 경우가 많습니다.

이 글은 운영 중인 PostgreSQL에서 10분 안에 bloat 원인을 분류하고, 가장 비용 대비 효과가 큰 해결책(VACUUM / VACUUM FULL / REINDEX / pg_repack / 파티셔닝/설정 조정)을 선택할 수 있게 구성했습니다.

> 다른 장애도 “10분 진단” 프레임으로 접근하면 빨라집니다. 예: EKS에서 Karpenter 노드가 안 늘 때 10분 진단

0) 먼저 알아야 할 핵심: VACUUM이 디스크를 줄이지 않는 이유

PostgreSQL의 일반 VACUUM은 죽은 튜플(dead tuple)을 “재사용 가능” 상태로 표시하고, 가시성 맵/통계 등을 정리하지만, 보통 파일 자체를 OS에 반환하지 않습니다(일부 페이지 끝부분이 비면 truncate로 약간 줄 수는 있음).

따라서 “디스크를 줄이고 싶다”면 보통 아래 중 하나가 필요합니다.

  • 테이블 파일을 재작성: VACUUM FULL (강력한 락, 느림)
  • 인덱스만 팽창: REINDEX 또는 REINDEX CONCURRENTLY
  • 운영 중 온라인 재작성: pg_repack(확장) 또는 논리적 재작성(CTAS/스왑)

반대로, “디스크는 그대로인데 성능이 좋아져야 하는” 상황이라면 VACUUM과 ANALYZE가 의미가 있고, autovacuum 튜닝/쿼리 플랜 안정화가 핵심일 수 있습니다.

1) 10분 진단 체크리스트(바로 실행)

아래 SQL은 권한이 있는 계정으로 psql에서 실행한다고 가정합니다.

1-1. 지금 VACUUM이 막혀 있나? (락/장기 트랜잭션)

VACUUM이 “진행은 하는데 효과가 없다”의 흔한 원인은 장기 트랜잭션이 old row version을 붙잡고 있어서 dead tuple을 정리/재사용하기 어려운 경우입니다.

-- 1) 장기 실행/idle in transaction 확인
SELECT pid, usename, state, now() - xact_start AS xact_age,
       now() - query_start AS query_age,
       left(query, 120) AS query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY xact_age DESC
LIMIT 20;

-- 2) VACUUM 작업 자체가 실행 중인지
SELECT pid, relid::regclass AS table_name, phase,
       heap_blks_scanned, heap_blks_vacuumed,
       index_vacuum_count, max_dead_tuples
FROM pg_stat_progress_vacuum;
  • idle in transaction이 수분~수시간 지속되면, VACUUM이 치울 수 있는 범위가 급격히 줄어듭니다.
  • pg_stat_progress_vacuum.phase가 오래 멈춰 있으면 락/IO 병목/리소스 제한을 의심합니다.

1-2. 테이블 bloat 징후: dead tuple이 쌓였나?

SELECT 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 20;
  • dead_pct가 높고 last_autovacuum이 오래 전이면 autovacuum이 못 따라가고 있을 확률이 큽니다.
  • 단, n_dead_tup은 통계 기반이라 100% 정확하지 않습니다. 그래도 “우선순위 정하기”에는 충분합니다.

1-3. “테이블이 아니라 인덱스가 부풀었다”를 빠르게 확인

인덱스 bloat는 일반 VACUUM으로 잘 줄지 않습니다. 아래로 “인덱스가 테이블보다 과도하게 큰지”를 1차로 봅니다.

SELECT
  t.relname AS table_name,
  pg_size_pretty(pg_total_relation_size(t.relid)) AS total_size,
  pg_size_pretty(pg_relation_size(t.relid)) AS heap_size,
  pg_size_pretty(pg_total_relation_size(t.relid) - pg_relation_size(t.relid)) AS index_size
FROM pg_catalog.pg_statio_user_tables t
ORDER BY pg_total_relation_size(t.relid) DESC
LIMIT 20;
  • index_size가 비정상적으로 크면 REINDEX/pg_repack이 더 효과적입니다.

1-4. “VACUUM이 안 먹힌다”의 또 다른 원인: 통계(ANALYZE) 부재

VACUUM은 공간/가시성 문제를, ANALYZE는 플래너 통계를 다룹니다. 성능 이슈라면 통계가 더 중요할 수 있습니다.

-- 테이블별 마지막 analyze 시점
SELECT relname, last_analyze, last_autoanalyze
FROM pg_stat_user_tables
ORDER BY coalesce(last_autoanalyze, last_analyze) NULLS FIRST
LIMIT 30;

last_autoanalyze가 오래됐으면 VACUUM (ANALYZE) 혹은 ANALYZE를 우선 고려합니다.

2) 상황별 처방: 무엇을 해야 “먹히는지”

2-1. 장기 트랜잭션/락이 원인일 때

증상

  • pg_stat_activity에서 xact_age가 비정상적으로 긴 세션 존재
  • dead tuple이 줄지 않고 누적
  • autovacuum이 계속 돌거나, freeze가 지연

해결

  1. 애플리케이션/배치에서 트랜잭션 범위를 줄이기
  2. 정말 필요하면 해당 세션을 종료(신중)
-- 위험: 해당 세션 트랜잭션을 강제로 종료
SELECT pg_terminate_backend(<pid>);
  1. 운영 정책: idle_in_transaction_session_timeout 적용(가능하면)
-- 예: 5분 이상 idle in transaction이면 강제 종료
ALTER SYSTEM SET idle_in_transaction_session_timeout = '5min';
SELECT pg_reload_conf();

2-2. 테이블 bloat가 크고 “디스크를 줄여야” 할 때

선택지 A: VACUUM FULL (가장 단순, 가장 위험)

  • 장점: 내장 기능, 확실히 파일 재작성
  • 단점: 테이블에 강한 락(ACCESS EXCLUSIVE), 대용량이면 장시간 블로킹
VACUUM (FULL, VERBOSE, ANALYZE) public.big_table;

운영 중에는 보통 추천하지 않습니다(가능하면 점검창/유지보수 시간에).

선택지 B: pg_repack (온라인에 가까운 재작성)

  • 장점: 읽기/쓰기 영향이 상대적으로 적고, 디스크 반환 효과 큼
  • 단점: 확장 설치 필요, 추가 디스크(재작성 공간) 필요
# 예시: 테이블 재작성
pg_repack --dbname=mydb --table=public.big_table

# 예시: DB 전체(시간/공간 주의)
pg_repack --dbname=mydb

> pg_repack은 내부적으로 새 테이블/인덱스를 만들고 스왑하는 방식이라, 대용량에서는 임시로 1.2~2배 공간이 필요할 수 있습니다.

선택지 C: CTAS + 스왑(확장 없이도 가능)

특정 테이블에 대해 “정말 급하게” 재작성해야 하고 확장 설치가 어렵다면, 아래 패턴을 고려합니다(외래키/권한/트리거/시퀀스 주의).

BEGIN;

-- 1) 새 테이블로 재작성 (필요 컬럼만)
CREATE TABLE public.big_table_new AS
SELECT * FROM public.big_table;

-- 2) 인덱스 재생성(필수)
CREATE INDEX ON public.big_table_new (id);

-- 3) 짧은 락 구간에 스왑
ALTER TABLE public.big_table RENAME TO big_table_old;
ALTER TABLE public.big_table_new RENAME TO big_table;

COMMIT;

-- 검증 후 드랍
DROP TABLE public.big_table_old;

이 방식은 “작동은 하지만 운영 리스크가 큰” 편이라, 가능하면 pg_repack을 권합니다.

2-3. 인덱스 bloat가 주범일 때: REINDEX

인덱스가 비대해지면 캐시 효율이 떨어지고, 랜덤 IO가 늘고, 쓰기 비용도 증가합니다.

가장 안전한 편: REINDEX CONCURRENTLY

  • 장점: 락 영향 최소화(완전 무중단은 아니지만 훨씬 낫습니다)
  • 단점: 시간이 더 걸리고 추가 공간 필요
-- 특정 인덱스
REINDEX INDEX CONCURRENTLY public.big_table_idx;

-- 특정 테이블의 모든 인덱스
REINDEX TABLE CONCURRENTLY public.big_table;

즉시 효과가 필요하지만 락 허용 시

REINDEX TABLE public.big_table;

2-4. autovacuum이 못 따라가는 구조일 때(근본 해결)

“한 번 VACUUM으로 해결”이 아니라, 계속 재발한다면 autovacuum이 워크로드를 못 따라가는 겁니다.

빠른 점검: 테이블별 autovacuum 설정이 너무 느슨한가?

SELECT relname, reloptions
FROM pg_class
WHERE relname IN (
  SELECT relname FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 20
);

실전 튜닝(테이블 단위가 효과적)

대형/고변경 테이블에만 강하게 거는 게 보통 안전합니다.

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    = 5000
);
  • scale_factor는 “테이블 크기에 비례해 발동”하므로, 대형 테이블은 기본값(0.2)이 너무 늦게 청소를 시작합니다.
  • 테이블마다 업데이트/삭제 비율이 다르므로, 상위 5~10개 핫 테이블만 조정해도 효과가 큽니다.

autovacuum worker/비용 파라미터(전역)

운영 환경에 따라 다르지만, “청소가 항상 밀린다”면 아래를 점검합니다.

  • autovacuum_max_workers
  • autovacuum_naptime
  • autovacuum_vacuum_cost_limit, autovacuum_vacuum_cost_delay

적용 예시:

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

> 비용 파라미터는 디스크/IOPS 상황에 따라 역효과가 날 수 있어, 변경 전후로 pg_stat_bgwriter, iostat, 쿼리 지연을 함께 보세요.

2-5. Freeze(트랜잭션 ID wraparound) 압박이 있을 때

VACUUM이 “안 먹히는” 느낌과 별개로, wraparound 방지 때문에 VACUUM이 공격적으로 돌면서 운영 성능에 영향을 주는 경우가 있습니다.

-- DB별 age 확인 (값이 클수록 위험)
SELECT datname, age(datfrozenxid) AS xid_age
FROM pg_database
ORDER BY xid_age DESC;

xid_age가 임계치에 가까우면(환경/버전에 따라 다름) 단순 bloat 해결이 아니라 freeze 우선순위로 접근해야 합니다. 이때는 장기 트랜잭션 제거, autovacuum 정상화가 최우선입니다.

3) “VACUUM 했는데도 느리다”를 끝내는 확인 루틴

3-1. VACUUM + ANALYZE를 명시적으로 수행

VACUUM (VERBOSE, ANALYZE) public.big_table;
  • VACUUM만 하고 ANALYZE를 안 하면 플래너가 여전히 잘못된 카디널리티로 플랜을 고집할 수 있습니다.

3-2. 인덱스 사용/스캔 방식이 바뀌었는지 확인

EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM public.big_table
WHERE user_id = 123
ORDER BY created_at DESC
LIMIT 50;
  • BUFFERS에서 shared hit/read 비율이 나빠졌다면 캐시/인덱스 팽창 가능성이 큽니다.
  • 예상 row와 실제 row 차이가 크면 통계/상관관계 문제를 의심합니다.

4) 운영에서 자주 하는 실수 5가지

  1. 디스크를 줄이려고 VACUUM만 반복
    • 일반 VACUUM은 “공간 재사용”이지 “공간 반환”이 아닙니다.
  2. 장기 트랜잭션을 방치
    • VACUUM/Freeze/인덱스 정리 모두의 적입니다.
  3. 인덱스 bloat를 테이블 bloat로 착각
    • REINDEX가 정답인 케이스가 많습니다.
  4. autovacuum을 전역으로만 튜닝
    • 핫 테이블에 테이블 단위 옵션을 주는 게 더 안전하고 효과적입니다.
  5. VACUUM FULL을 무심코 운영시간에 실행
    • 락으로 장애를 만들 수 있습니다.

5) 10분 결론: 이렇게 의사결정하면 실패 확률이 낮다

  • 디스크를 반드시 줄여야 한다 → pg_repack(가능하면) → 안 되면 유지보수 창에 VACUUM FULL
  • 인덱스가 과도하게 크다 → REINDEX CONCURRENTLY 우선
  • dead tuple이 계속 쌓인다 → 장기 트랜잭션 제거 + 핫 테이블 autovacuum 옵션 강화
  • VACUUM 후에도 쿼리가 느리다 → ANALYZE/통계 갱신 + EXPLAIN (ANALYZE, BUFFERS)로 플랜 검증

운영에서 중요한 건 “한 번의 VACUUM 명령”이 아니라, 왜 bloat가 생겼는지(워크로드/트랜잭션/설정)와 어떤 레벨(테이블 vs 인덱스)에서 팽창했는지를 빠르게 가르는 것입니다. 위 쿼리들로 상위 1~3개 문제 테이블만 정확히 잡아도, 대부분은 10분 안에 방향이 정해집니다.

> 장애 대응 관점에서 원인-가설-검증 루틴이 익숙해지면 다른 인프라 이슈도 빨라집니다. 예: Kubernetes CrashLoopBackOff 원인별 로그·Probe·리소스 디버깅

Discuss on Twitter • View on GitHub