- Published on
PostgreSQL VACUUM 안먹힘? bloat 진단·해결
- Authors
- Name
- 스타차일드
- https://x.com/ETFBITX
서버에서 VACUUM을 돌렸는데도 디스크 사용량이 안 줄고, 쿼리는 계속 느리며, pg_stat_user_tables만 보면 “vacuum은 돌긴 도는 것 같은데 효과가 없다”는 상황이 자주 나옵니다. 이때 핵심은 VACUUM이 하는 일과 하지 못하는 일을 정확히 구분하고, “bloat가 어디에 얼마나 생겼는지”를 수치로 잡아낸 다음, **원인(특히 오래 열린 트랜잭션/replication slot/부적절한 autovacuum 임계치)**을 제거하는 것입니다.
이 글은 다음 순서로 진행합니다.
VACUUM이 디스크를 줄이지 않는 이유- bloat(테이블/인덱스) 진단 쿼리
- autovacuum이 안 먹는 대표 원인
- 해결:
VACUUM (FULL),REINDEX,pg_repack, 파티셔닝/아카이빙 - 운영 중 안전하게 적용하는 체크리스트
참고로 락/경합 이슈가 함께 보이면 PostgreSQL 데드락(40P01) 원인·해결 9단계도 같이 보는 것을 권합니다. bloat 정리 작업은 락을 유발하거나 락 대기 시간을 늘릴 수 있기 때문입니다.
1) VACUUM이 “안 먹히는” 것처럼 보이는 이유
1-1. 일반 VACUUM은 파일 크기를 줄이지 않는다
PostgreSQL의 일반 VACUUM은 죽은 튜플(dead tuple)을 “재사용 가능” 상태로 표시하고, 가시성 맵/통계 갱신을 돕습니다. 하지만 이미 할당된 데이터 파일 페이지는 OS에 반환하지 않습니다. 그래서 du나 스토리지 대시보드에서 디스크가 그대로인 경우가 정상입니다.
- 디스크를 실제로 줄이려면 보통
VACUUM (FULL)또는 테이블 재작성(예:pg_repack)이 필요합니다. - 다만 일반
VACUUM만으로도 성능이 개선될 수는 있습니다(인덱스/테이블 스캔 효율, HOT 업데이트 활용 등).
1-2. “정리할 수 없는” dead tuple이 쌓인다: 오래 열린 트랜잭션
가장 흔한 원인입니다. 어떤 세션이 오래 열린 트랜잭션을 유지하면, 그 시점 이후에 삭제/업데이트된 튜플이 아직 다른 트랜잭션에서 볼 수 있을지도 모른다고 판단되어 vacuum이 제거(정리)하지 못합니다.
즉, vacuum은 돌지만 실제로 공간 회수가 제한되고, bloat는 계속 커집니다.
1-3. 인덱스 bloat는 테이블 vacuum만으로 해결되지 않는다
테이블에서 dead tuple을 정리해도, 인덱스 페이지는 구조상 “빈 공간이 생겼다고 즉시 줄어들지” 않습니다. 그래서 체감 성능(특히 랜덤 I/O)이 여전히 나쁠 수 있습니다.
- 인덱스 bloat는
REINDEX또는pg_repack(인덱스 포함 재작성)을 고려해야 합니다.
1-4. autovacuum이 임계치에 못 미쳐서 “너무 늦게” 돈다
기본 autovacuum 임계치는 대형 테이블에서 비효율적일 수 있습니다.
autovacuum_vacuum_threshold+autovacuum_vacuum_scale_factor * reltuples- 테이블이 크면 클수록 scale factor 때문에 vacuum 시작이 너무 늦어져 dead tuple이 크게 쌓일 수 있습니다.
2) bloat 진단: 어디가 얼마나 부었는지 계측하기
운영에서 가장 먼저 할 일은 “감”이 아니라 수치로 상위 문제 테이블/인덱스를 특정하는 것입니다.
2-1. 테이블별 dead tuple/last vacuum 확인
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,
last_analyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 30;
n_dead_tup가 큰데last_autovacuum이 오래됐으면 autovacuum이 못 돌고 있거나, 너무 늦게 돌고 있거나, 락/자원 부족으로 실패했을 가능성이 있습니다.
2-2. 테이블 크기/토스트 포함 크기 확인
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 heap_size,
pg_size_pretty(pg_total_relation_size(c.oid) - pg_relation_size(c.oid)) AS other_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;
other_size에는 인덱스/TOAST 등이 포함됩니다. 테이블은 작아 보이는데other_size가 큰 경우 인덱스/TOAST bloat를 의심합니다.
2-3. 인덱스별 크기 상위 확인
SELECT
schemaname,
tablename,
indexname,
pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
idx_scan
FROM pg_stat_user_indexes
ORDER BY pg_relation_size(indexrelid) DESC
LIMIT 30;
idx_scan이 거의 0인데 큰 인덱스는 “안 쓰는데 큰” 인덱스일 수 있습니다. 제거 후보(검증 필수)입니다.
2-4. 오래 열린 트랜잭션/idle in transaction 잡기
SELECT
pid,
usename,
application_name,
client_addr,
state,
now() - xact_start AS xact_age,
now() - query_start AS query_age,
wait_event_type,
wait_event,
left(query, 120) AS query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY xact_age DESC
LIMIT 30;
state가idle in transaction이고xact_age가 길면 vacuum을 막는 주범일 확률이 큽니다.
2-5. replication slot이 xmin을 붙잡고 있는지 확인
논리 복제/CDC에서 slot 소비가 지연되면 제거 가능한 튜플이 제거되지 못해 bloat가 커질 수 있습니다.
SELECT
slot_name,
plugin,
slot_type,
active,
pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained_wal,
xmin
FROM pg_replication_slots
ORDER BY retained_wal DESC;
active = false인데 slot이 남아 있고retained_wal이 크면 위험 신호입니다(디스크/WAL 압박 + vacuum 방해).
3) autovacuum이 “안 먹는” 대표 원인 체크리스트
3-1. 오래 열린 트랜잭션이 vacuum을 막는다
- 배치/관리자 세션이 트랜잭션을 열어둔 채 대기
- 애플리케이션 커넥션 풀에서 트랜잭션 종료 누락
REPEATABLE READ/SERIALIZABLE장기 트랜잭션
해결은 단순하지만 조직적으로 어렵습니다.
- 애플리케이션에서 트랜잭션 범위를 최소화
- 타임아웃 설정:
idle_in_transaction_session_timeout
ALTER SYSTEM SET idle_in_transaction_session_timeout = '60s';
SELECT pg_reload_conf();
3-2. autovacuum이 자원 제한에 걸려 “너무 느리게” 돈다
autovacuum_vacuum_cost_limit,autovacuum_vacuum_cost_delay가 보수적이면 대형 테이블에서 따라잡지 못합니다.- I/O가 빡빡한 환경에서는 vacuum이 계속 밀립니다.
확인은 pg_stat_progress_vacuum으로 합니다.
SELECT
pid,
relid::regclass AS table,
phase,
heap_blks_total,
heap_blks_scanned,
heap_blks_vacuumed,
index_vacuum_count,
max_dead_tuples
FROM pg_stat_progress_vacuum;
3-3. 테이블별 autovacuum 임계치가 부적절하다
대형 테이블은 scale factor를 낮추고, threshold를 조정해 “자주 조금씩” 치우는 방식이 유리한 경우가 많습니다.
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
);
- 값은 워크로드에 따라 다릅니다. 핵심은 “dead tuple이 폭증하기 전에” vacuum이 시작되게 만드는 것입니다.
3-4. vacuum freeze 관련 경고가 보인다
autovacuum은 bloat뿐 아니라 트랜잭션 ID wraparound를 막기 위해서도 필수입니다. datfrozenxid가 위험 수위면 “성능”이 아니라 “장애”로 이어질 수 있습니다.
SELECT
datname,
age(datfrozenxid) AS xid_age
FROM pg_database
ORDER BY xid_age DESC;
4) 해결 전략: 상황별로 무엇을 선택할까
여기서부터는 “디스크를 줄이고 싶다”와 “성능을 되돌리고 싶다”를 분리해서 접근합니다.
4-1. 성능만 회복하면 된다: VACUUM (ANALYZE) 우선
통계가 낡아 플래너가 잘못된 계획을 고르는 경우가 많습니다.
VACUUM (ANALYZE) public.big_table;
- 즉시 효과가 나면 디스크 회수는 나중 문제로 미룰 수 있습니다.
4-2. 테이블 파일 크기를 줄여야 한다: VACUUM (FULL)의 함정
VACUUM (FULL, ANALYZE) public.big_table;
- 장점: 테이블을 재작성하여 실제 디스크를 줄일 수 있음
- 단점: 긴 시간의 강한 락(사실상 접근 차단)을 잡을 수 있음
운영 환경에서 무작정 실행하면 장애로 이어질 수 있으니, 아래 대안을 먼저 검토합니다.
4-3. 운영 중 디스크 회수: pg_repack(권장 케이스 많음)
pg_repack은 테이블/인덱스를 온라인에 가깝게 재구성해 bloat를 줄입니다(내부적으로 트리거/임시 테이블을 사용). 완전 무중단은 아니고 마지막 스왑 구간에 락이 필요하지만, VACUUM (FULL) 대비 훨씬 현실적입니다.
설치가 필요하므로 DB 권한/운영 정책을 확인하세요.
- 특정 테이블 재구성(예시)
pg_repack --dbname=mydb --table=public.big_table --no-superuser-check
- 인덱스만 재구성(예시)
pg_repack --dbname=mydb --index=public.big_table_some_idx --no-superuser-check
주의: 대용량 테이블은 추가 디스크(임시 공간)가 필요합니다. “줄이려다 더 꽉 차서 장애”가 날 수 있으니 여유 공간을 먼저 계산하세요.
4-4. 인덱스 bloat가 핵심이면: REINDEX 또는 REINDEX CONCURRENTLY
REINDEX INDEX CONCURRENTLY public.big_table_some_idx;
CONCURRENTLY는 시간이 더 걸리지만 락 영향이 줄어듭니다.- 단, 버전/상황에 따라 제약이 있으니 변경 윈도우에서 충분히 테스트하세요.
4-5. 근본 처방: 파티셔닝/아카이빙/삭제 대신 만료 전략
대량 DELETE는 bloat를 만들기 쉽습니다. 아래가 더 안정적입니다.
- 시간 기반 파티셔닝 후 오래된 파티션
DROP - soft delete를 쓰더라도 주기적으로 아카이브 테이블로 이동 후 원본에서 제거
- TTL성 데이터는 별도 저장소/테이블로 분리
파티션 DROP은 메타데이터 작업에 가깝기 때문에 대량 DELETE보다 훨씬 저렴하게 공간을 회수할 수 있습니다.
5) “VACUUM을 돌렸는데도 bloat가 계속 커진다” 실전 디버깅 플로우
아래 순서대로 보면 대부분 원인이 좁혀집니다.
5-1. 먼저 막는 요인 제거: 오래 열린 트랜잭션 종료
pg_stat_activity에서xact_age상위 확인- 애플리케이션/배치 담당자 확인
- 필요 시 세션 종료
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE pid = 12345;
- 종료 전 해당 세션이 중요한 작업인지 반드시 확인하세요.
5-2. autovacuum이 실제로 돌고 있는지, 진행이 멈췄는지 확인
pg_stat_progress_vacuum확인- 로그에서 autovacuum 관련 메시지 확인(설정에 따라 출력)
운영에서 원인 파악을 위해 autovacuum 로그를 제한적으로 켜는 것도 방법입니다.
ALTER SYSTEM SET log_autovacuum_min_duration = '5s';
SELECT pg_reload_conf();
5-3. 테이블별 autovacuum 임계치/비용 파라미터 조정
- 큰 테이블에만
ALTER TABLE ... SET (autovacuum_...)로 튜닝 - 전역 파라미터는 신중히(다른 테이블에 부작용 가능)
5-4. bloat가 이미 심각하면 “재작성” 계열로 마무리
- 다운타임 가능:
VACUUM (FULL) - 다운타임 최소화:
pg_repack - 인덱스만 문제:
REINDEX CONCURRENTLY
6) 운영 적용 체크리스트(장애 예방)
- 여유 디스크: 재작성 작업은 일시적으로 공간을 더 먹습니다.
- 락 영향도:
VACUUM (FULL)은 특히 위험. 가능하면 대안 우선. - 피크 타임 회피: I/O와 체크포인트 압박을 고려.
- 슬로우 쿼리/락 모니터링: 작업 중 대기 증가 여부 확인.
- 애플리케이션 트랜잭션 위생:
idle in transaction방지 설정.
DB 성능 문제는 애플리케이션 커넥션/트랜잭션 패턴과도 강하게 결합됩니다. 커넥션 풀 이슈가 의심되면 Spring Boot HikariCP 커넥션 고갈 원인 8가지도 함께 점검하면 “vacuum 방해 세션”을 줄이는 데 도움이 됩니다.
7) 빠른 처방 요약
- 디스크가 안 줄어드는 건 일반
VACUUM에선 정상일 수 있음 - 진짜 문제는 대개 다음 중 하나
- 오래 열린 트랜잭션이 dead tuple 정리를 막음
- autovacuum 임계치/자원 설정이 워크로드에 안 맞음
- 인덱스 bloat가 핵심인데 테이블만 vacuum함
- 해결은 단계적으로
VACUUM (ANALYZE)로 통계/성능부터 회복- 원인(트랜잭션/slot) 제거
- 필요 시
REINDEX CONCURRENTLY또는pg_repack - 최후에
VACUUM (FULL)
bloat는 한 번 크게 터지면 “정리 작업” 자체가 운영 리스크가 됩니다. 따라서 가장 좋은 전략은 autovacuum이 제때 돌 수 있도록 트랜잭션을 짧게 유지하고, 대형 테이블은 테이블별 autovacuum 설정을 조정해 bloat가 커지기 전에 관리하는 것입니다.