VACUUM 안 해서 폭증한 PostgreSQL 테이블 복구

서버는 멀쩡한데 디스크만 계속 줄고, 쿼리는 점점 느려지고, pg_stat_user_tables 를 보면 삭제/업데이트가 많은 테이블의 크기가 비정상적으로 커져 있다면 거의 항상 VACUUM(autovacuum 포함) 미동작 또는 미튜닝으로 인한 블로트(bloat) 상황입니다. 이 글은 “왜 커졌는지”를 짧게 정리하고, 운영 환경에서 안전하게 복구하는 절차(진단 -> 우선 조치 -> 재구성 -> 재발 방지)를 코드와 함께 제공합니다.

1) PostgreSQL에서 테이블이 폭증하는 이유

PostgreSQL은 MVCC 기반이라 UPDATE 나 DELETE 가 실행되면 기존 행을 즉시 덮어쓰거나 제거하지 않습니다.

• UPDATE: 새 버전의 행이 추가되고, 기존 행은 “죽은 튜플(dead tuple)”이 됨
• DELETE: 행은 즉시 사라지지 않고 “죽은 튜플”로 남음

이 죽은 튜플은 VACUUM 이 회수(reclaim)해서 재사용 가능 상태로 만들어야 합니다. VACUUM이 제때 돌지 않으면:

• 테이블 파일이 계속 커지고
• 인덱스도 함께 비대해지고
• 캐시 효율이 떨어져 I/O가 증가하고
• 결국 쿼리 지연, 체크포인트/백업 시간 증가, 디스크 고갈로 장애로 이어집니다.

추가로 다음 상황이면 블로트가 더 빨리 악화됩니다.

• 대량 UPDATE/DELETE 작업을 자주 수행
• 파티셔닝 없이 “핫 테이블”에 쓰기 집중
• autovacuum 설정이 기본값 그대로(보수적)인데 테이블이 매우 큼
• autovacuum 워커가 부족하거나, I/O 제한 때문에 따라가지 못함

2) 먼저 해야 할 것: 디스크 고갈 방지와 위험 신호 확인

블로트가 심하면 “복구 작업(재구성)” 자체가 추가 디스크를 요구할 수 있습니다. 따라서 디스크 여유 확보가 1순위입니다.

2.1 디스크/테이블 상위 랭킹 확인

-- 테이블 크기 상위 20개
SELECT
  n.nspname AS schema,
  c.relname AS table,
  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_total_relation_size(c.oid) - pg_relation_size(c.oid)) AS index_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 20;

2.2 dead tuple(죽은 튜플) 규모 확인

-- dead tuple이 많은 테이블
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 이 오래 비어 있거나 과거에 멈춰 있다면, autovacuum이 사실상 “못 따라가는” 상태일 가능성이 큽니다.

2.3 트랜잭션 ID(Freeze) 위험도 확인

VACUUM을 안 하면 성능 문제뿐 아니라 wraparound 위험이 커집니다.

SELECT
  datname,
  age(datfrozenxid) AS xid_age,
  mxid_age(datminmxid) AS mxid_age
FROM pg_database
ORDER BY xid_age DESC;

xid_age 가 임계치에 가까우면 “느려도 무조건 vacuum”이 우선입니다.

3) 복구 전략 선택: VACUUM만으로는 “줄지” 않는다

여기서 많은 분들이 오해합니다.

• VACUUM 은 죽은 튜플을 회수해서 재사용 가능하게 만들지만
• 파일 크기를 OS에 반환하지는 않습니다(대부분의 경우)

즉, 디스크가 이미 폭증한 상태에서 “크기를 줄이는” 목적이라면 보통 다음 중 하나가 필요합니다.

1. VACUUM (FULL)
2. CLUSTER
3. pg_repack (권장, 운영 친화적)
4. 테이블 재작성(새 테이블로 INSERT SELECT 후 스왑)

각각 장단점이 명확합니다.

• VACUUM (FULL): 테이블을 새로 써서 줄여주지만 긴 시간의 강한 잠금이 걸림
• CLUSTER: 정렬 기준으로 재작성, 역시 강한 잠금
• pg_repack: 온라인에 가깝게 재구성(추가 오브젝트/디스크 필요), 운영에서 많이 씀

4) 1차 응급 처치: autovacuum이 “돌 수 있게” 만들기

테이블이 폭증한 상황에서는 autovacuum이 느리거나 멈춘 것처럼 보일 수 있습니다. 먼저 “돌아가게” 만들어야 추가 악화를 막습니다.

4.1 autovacuum 동작 여부 확인

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

4.2 특정 테이블에 강제 VACUUM/ANALYZE

VACUUM (ANALYZE, VERBOSE) public.big_table;

4.3 테이블 단위 autovacuum 튜닝(핫 테이블만)

전역 파라미터를 건드리기 어렵다면, 문제 테이블에만 적용하는 방식이 안전합니다.

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 를 낮추면 더 자주 vacuum/analyze가 트리거됩니다.
• 너무 공격적으로 설정하면 I/O를 잡아먹을 수 있으니, “상위 몇 개 핫 테이블”에만 적용하는 것을 권합니다.

4.4 vacuum 비용 제한 완화(가능한 경우)

운영에서 I/O 여유가 있다면 다음 설정을 완화해 vacuum 속도를 올릴 수 있습니다.

• autovacuum_vacuum_cost_limit
• autovacuum_vacuum_cost_delay

단, 이는 시스템 전체에 영향을 줄 수 있으니 변경 전후로 I/O와 지연을 관찰하세요.

5) 본 복구: “커진 크기”를 실제로 줄이는 방법

이제부터가 진짜 복구입니다. 목표는 테이블/인덱스 파일을 재작성해서 물리 크기를 줄이는 것입니다.

5.1 가장 단순하지만 위험한 방법: VACUUM FULL

-- 강한 잠금이 걸리므로 점검창(maintenance window)에서만 권장
VACUUM (FULL, ANALYZE, VERBOSE) public.big_table;

주의점:

• 실행 동안 테이블에 대한 접근이 크게 제한됩니다(사실상 다운타임).
• 작업 중 추가 디스크가 필요할 수 있습니다.

5.2 운영 친화적 권장: pg_repack

pg_repack 은 테이블을 재구성하면서도 비교적 온라인에 가깝게 진행할 수 있어 운영에서 자주 사용합니다.

설치(예: Debian/Ubuntu 계열, 버전은 환경에 맞게):

sudo apt-get update
sudo apt-get install -y postgresql-16-repack

실행 예시:

# 특정 테이블 재구성
pg_repack -h db.host -p 5432 -U app_user -d app_db -t public.big_table

# 특정 스키마 전체
pg_repack -h db.host -p 5432 -U app_user -d app_db -s public

운영 팁:

• 테이블이 매우 크면 작업 시간 동안 WAL 증가가 커질 수 있습니다.
• 복제 환경(Streaming Replication)이라면 리플리카 지연도 함께 모니터링하세요.
• pg_repack 도 내부적으로 잠금을 완전히 피하진 못하므로, 트래픽이 낮은 시간대가 안전합니다.

5.3 인덱스만 비대한 경우: REINDEX (CONCURRENTLY)

테이블은 괜찮은데 인덱스만 커졌다면 인덱스 재구성이 더 효율적입니다.

-- 운영에서 가능한 옵션(단, 제약 조건 존재)
REINDEX INDEX CONCURRENTLY public.big_table_some_idx;

-- 테이블의 모든 인덱스(상황에 따라 오래 걸림)
REINDEX TABLE CONCURRENTLY public.big_table;

5.4 테이블 재작성 스왑(최후의 수단)

스키마 변경/정리도 겸하고 싶거나 도구 사용이 어렵다면 새 테이블로 복사 후 스왑합니다.

BEGIN;

-- 1) 새 테이블 생성(구조 복제)
CREATE TABLE public.big_table_new (LIKE public.big_table INCLUDING ALL);

-- 2) 데이터 이관
INSERT INTO public.big_table_new
SELECT * FROM public.big_table;

-- 3) 스왑(짧은 잠금 구간을 목표)
ALTER TABLE public.big_table RENAME TO big_table_old;
ALTER TABLE public.big_table_new RENAME TO big_table;

COMMIT;

주의:

• 트리거/시퀀스/권한/외래키/의존 오브젝트를 빠짐없이 점검해야 합니다.
• 대용량 테이블에서는 이관 시간이 길고, 이관 중 변경분 동기화 전략이 필요합니다.

6) 복구 후 검증 체크리스트

복구는 “작업이 끝났다”가 아니라 “효과가 확인됐다”까지가 한 세트입니다.

6.1 크기 감소 확인

SELECT
  pg_size_pretty(pg_total_relation_size('public.big_table')) AS total,
  pg_size_pretty(pg_relation_size('public.big_table')) AS heap,
  pg_size_pretty(pg_total_relation_size('public.big_table') - pg_relation_size('public.big_table')) AS indexes;

6.2 통계 갱신(플래너 안정화)

재작성 후에는 통계가 흔들릴 수 있어 ANALYZE 를 명시적으로 수행하는 편이 안전합니다.

ANALYZE VERBOSE public.big_table;

6.3 쿼리 성능 및 I/O 확인

• 상위 N개 느린 쿼리의 평균/95퍼센타일이 내려갔는지
• 버퍼 히트율, 디스크 read가 줄었는지
• 체크포인트 간격/시간이 안정화됐는지

관측 스택이 쿠버네티스 위에 있다면, DB 부하로 애플리케이션 파드가 OOM/CrashLoopBackOff로 번지는 경우도 흔합니다. 증상이 앱 장애로 보일 때는 인프라 진단 글도 함께 참고하세요: K8s CrashLoopBackOff - OOMKilled·Probe 5분 진단

7) 재발 방지: “autovacuum이 따라가게” 만드는 운영 패턴

블로트 복구는 한 번으로 끝나지 않습니다. 다음을 같이 설계해야 재발이 줄어듭니다.

7.1 핫 테이블은 테이블 단위로 autovacuum 파라미터를 관리

핫 테이블은 대개 소수입니다. 전역 파라미터를 공격적으로 바꾸기보다, 상위 몇 개 테이블에만 ALTER TABLE ... SET (autovacuum_...) 를 적용하세요.

7.2 대량 작업 후에는 명시적 VACUUM/ANALYZE를 루틴화

배치가 큰 DELETE 를 실행했다면 끝나고 다음을 습관화합니다.

VACUUM (ANALYZE) public.big_table;

7.3 파티셔닝 또는 보관 정책으로 “죽은 튜플” 자체를 줄이기

시간 기반 데이터라면 파티셔닝 후 오래된 파티션을 DROP 하는 방식이 vacuum 비용을 크게 낮춥니다.

• DELETE WHERE created_at ... 대신
• 오래된 파티션 DROP TABLE 로 제거

7.4 모니터링 지표를 고정해 두기

최소한 아래는 대시보드/알람으로 상시 감시하는 것을 권합니다.

• n_dead_tup 상위 테이블
• last_autovacuum 지연
• DB 디스크 사용량 추세
• age(datfrozenxid)
• 복제 지연(있다면)

디스크 고갈은 OS 레벨 장애로 이어져 DB 뿐 아니라 노드 전체가 불안정해질 수 있습니다. 파일 디스크립터 고갈 같은 2차 장애도 함께 발생할 수 있으니, 시스템 리소스 관점의 점검도 도움이 됩니다: 리눅스 Too many open files 해결 - ulimit·systemd·Nginx

8) 실전 시나리오: “폭증 테이블” 복구 추천 순서

운영에서 가장 사고가 적었던 순서를 정리하면 다음과 같습니다.

1. 상위 테이블/인덱스 크기와 n_dead_tup 로 타겟 선정
2. 디스크 여유 확보(스냅샷/백업/로그 보관 정책 점검)
3. 타겟 테이블에 VACUUM (ANALYZE) 로 추가 악화 중지
4. 인덱스만 문제면 REINDEX ... CONCURRENTLY
5. 테이블 자체가 비대하면 pg_repack 으로 재구성
6. 통계 ANALYZE 및 성능/지표 검증
7. 핫 테이블 autovacuum 파라미터 튜닝 및 모니터링 알람 추가

9) 자주 하는 실수

• VACUUM 을 돌렸는데 크기가 안 줄어 “효과 없다”고 판단
  • 정상입니다. 크기 반환이 목적이면 재구성이 필요합니다.
• VACUUM FULL 을 피크 타임에 실행
  • 강한 잠금으로 장애를 유발합니다.
• autovacuum를 꺼두고 “나중에 수동으로” 하겠다고 미룸
  • 결국 wraparound 위험까지 커집니다.
• 블로트 복구만 하고, 재발 방지(튜닝/파티셔닝/정책)를 안 함
  • 몇 주 내 재발하는 경우가 많습니다.

테이블 폭증은 대부분 “한 번의 큰 실수”가 아니라 “작은 방치가 누적된 결과”입니다. 핵심은 VACUUM 을 정상 궤도에 올려 추가 악화를 멈추고, 필요한 경우 pg_repack/REINDEX CONCURRENTLY 같은 방식으로 물리 크기를 되돌린 뒤, 핫 테이블 중심으로 autovacuum를 따라갈 수 있게 튜닝하는 것입니다.