Vacuum과 bloat: autovacuum 튜닝, freeze, xid wraparound 방지

Vacuum을 단순 청소 작업으로 보면 안 되는 이유

PostgreSQL 운영에서 VACUUM은 "디스크를 비우는 명령" 정도로 설명되곤 한다. 하지만 실제 운영자의 관점에서는 훨씬 중요하다. VACUUM은 MVCC가 남긴 오래된 row version을 재사용 가능하게 만들고, 인덱스와 visibility map을 정리하며, transaction ID wraparound를 막는 안전장치다.

PostgreSQL은 UPDATE나 DELETE가 발생해도 기존 row를 즉시 물리적으로 지우지 않는다. 다른 트랜잭션이 아직 그 버전을 볼 수 있기 때문이다. 대신 새 row version을 만들거나 기존 row version을 더 이상 최신이 아닌 상태로 표시한다. 이렇게 남은 row version이 충분히 오래되어 어떤 트랜잭션도 볼 필요가 없게 되면 VACUUM이 이를 정리한다.

문제는 workload가 커질수록 이 정리 작업이 자연스럽게 "언젠가 되겠지" 수준으로 끝나지 않는다는 점이다. 업데이트가 많은 테이블, 긴 트랜잭션, replication slot, 느린 replica, 너무 높은 autovacuum threshold가 겹치면 dead tuple이 계속 쌓인다. 그러면 테이블과 인덱스가 커지고, 캐시 효율이 떨어지고, 쿼리 latency가 길어지고, 최악의 경우 XID wraparound 보호 때문에 쓰기 작업이 멈출 수 있다.

Vacuum이 해결하는 네 가지 문제

PostgreSQL 공식 문서는 routine vacuuming의 목적을 크게 네 가지로 설명한다.

1. 업데이트·삭제된 row version의 공간 재사용
2. planner statistics 갱신
3. visibility map 갱신을 통한 index-only scan 지원
4. transaction ID / multixact ID wraparound 방지

운영에서 자주 놓치는 부분은 1번과 4번의 차이다. dead tuple이 많아서 테이블이 커지는 문제와, 오래된 tuple의 XID가 freeze되지 않아 wraparound 위험이 커지는 문제는 서로 관련은 있지만 같은 문제는 아니다. 어떤 테이블은 dead tuple은 많지 않아도 오랫동안 vacuum되지 않아 relfrozenxid가 오래될 수 있다. 반대로 dead tuple이 많아도 XID wraparound까지는 아직 여유가 있을 수 있다.

따라서 PostgreSQL vacuum 운영은 두 개의 질문으로 나누어 봐야 한다.

• 공간과 성능 관점: dead tuple과 free space가 workload 대비 과도하게 쌓이고 있는가?
• 안전성 관점: database/table의 XID age가 wraparound 보호 한계에 가까워지고 있는가?

이 둘을 분리해서 봐야 불필요한 VACUUM FULL이나 과도한 autovacuum 튜닝을 피할 수 있다.

전체 흐름: UPDATE 이후 autovacuum까지

이 그림에서 핵심은 VACUUM이 단순히 마지막 단계가 아니라는 점이다. 긴 트랜잭션이 있으면 vacuum이 제거할 수 있는 범위가 줄어든다. autovacuum threshold가 너무 높으면 작업 시작 자체가 늦어진다. freeze가 제때 되지 않으면 dead tuple이 많지 않아도 wraparound 위험이 커진다. 그래서 vacuum 장애를 볼 때는 항상 workload, snapshot, threshold, freeze age를 같이 봐야 한다.

Dead tuple, free space, bloat의 차이

운영 대화에서 dead tuple, free space, bloat가 섞여 쓰이지만, 실제 의미는 다르다.

VACUUM은 보통 공간을 운영체제에 반환하지 않고 같은 테이블 안에서 재사용 가능하게 만든다. 그래서 vacuum을 했는데도 pg_relation_size()가 줄지 않는 것은 정상이다. 실제 파일 크기를 줄이려면 VACUUM FULL, CLUSTER, pg_repack, REINDEX 같은 재작성 또는 재구성 작업이 필요하다. 다만 이런 작업은 lock, 추가 디스크, WAL 증가, replica lag 같은 비용이 크다.

따라서 bloat 대응의 순서는 보통 다음과 같다.

1. dead tuple 증가가 일시적인지, 지속적인지 확인한다.
2. autovacuum이 실행되고 있는지, 실행되지만 따라가지 못하는지 구분한다.
3. 같은 테이블에서 공간이 재사용되고 있는지 본다.
4. 실제 성능 문제나 디스크 압박이 있을 때만 재작성 작업을 검토한다.

bloat percentage만 보고 바로 VACUUM FULL을 실행하는 것은 위험하다. 작은 테이블의 50% bloat는 몇 MB에 불과할 수 있고, hot table의 일부 free space는 이후 쓰기 workload에서 재사용되어 오히려 유리할 수 있다.

Autovacuum이 실행되는 조건

Autovacuum은 모든 테이블을 같은 주기로 무조건 vacuum하지 않는다. 통계 정보를 보고 테이블별로 필요성을 판단한다. 일반적인 UPDATE/DELETE 기반 vacuum trigger는 다음 형태다.

autovacuum trigger = autovacuum_vacuum_threshold
                   + autovacuum_vacuum_scale_factor * table_tuple_count

PostgreSQL 18 기준 기본값은 대략 다음과 같다.

예를 들어 10억 row 테이블에서 scale factor 0.2를 그대로 두면, 이론적으로 2억 개 수준의 update/delete가 누적되어야 vacuum 대상이 된다. PostgreSQL 18에는 autovacuum_vacuum_max_threshold 기본값이 있어 상한이 걸리지만, 그래도 대형 테이블에서는 기본값이 너무 느슨할 수 있다.

대형 테이블 운영에서는 global 설정을 무작정 낮추기보다, 테이블별 storage parameter로 조정하는 편이 안전하다.

-- 대형 주문 테이블은 20%를 기다리지 않고 고정 dead tuple 수 기준으로 vacuum
ALTER TABLE public.orders SET (
  autovacuum_vacuum_scale_factor = 0.01,
  autovacuum_vacuum_threshold = 50000,
  autovacuum_analyze_scale_factor = 0.02,
  autovacuum_analyze_threshold = 50000
);

이 설정은 예시일 뿐이다. 실제 값은 테이블 크기, update/delete rate, vacuum duration, I/O 여유, replica lag 허용치를 보고 정해야 한다.

Autovacuum이 느린 것과 막힌 것은 다르다

Autovacuum 이슈를 볼 때 가장 먼저 구분할 것은 세 가지다.

1. 아예 실행되지 않는다: autovacuum 또는 track_counts 문제, 통계 미갱신, table-level disable 가능성
2. 실행되지만 너무 늦게 시작한다: threshold와 scale factor가 workload에 맞지 않음
3. 실행되지만 끝나지 못한다: I/O throttle, worker 부족, lock conflict, 긴 트랜잭션, 계속 증가하는 dead tuple

상태 확인은 다음 쿼리에서 시작할 수 있다.

SELECT
  schemaname,
  relname,
  n_live_tup,
  n_dead_tup,
  ROUND(n_dead_tup::numeric / NULLIF(n_live_tup + n_dead_tup, 0) * 100, 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;

현재 실행 중인 vacuum은 pg_stat_progress_vacuum으로 본다.

SELECT
  p.pid,
  p.datname,
  p.relid::regclass AS table_name,
  p.phase,
  p.heap_blks_total,
  p.heap_blks_scanned,
  p.heap_blks_vacuumed,
  ROUND(100.0 * p.heap_blks_scanned / NULLIF(p.heap_blks_total, 0), 1) AS scan_pct,
  p.index_vacuum_count,
  now() - a.xact_start AS elapsed
FROM pg_stat_progress_vacuum p
LEFT JOIN pg_stat_activity a ON a.pid = p.pid
ORDER BY elapsed DESC NULLS LAST;

pg_stat_progress_vacuum은 일반 VACUUM과 autovacuum worker를 보여준다. VACUUM FULL은 table rewrite 작업이므로 pg_stat_progress_cluster 쪽에서 관측된다.

긴 트랜잭션이 vacuum을 방해하는 방식

Vacuum이 dead tuple을 제거하려면, 그 row version을 볼 수 있는 오래된 snapshot이 더 이상 없어야 한다. 문제는 application transaction이 오래 열려 있거나, idle in transaction 세션이 방치되거나, logical decoding slot이 오래된 변경을 붙잡고 있으면 vacuum이 안전하게 제거할 수 있는 범위가 줄어든다는 점이다.

다음 쿼리는 오래된 transaction과 xmin을 확인하는 출발점이다.

SELECT
  pid,
  usename,
  application_name,
  state,
  xact_start,
  now() - xact_start AS xact_age,
  backend_xmin,
  wait_event_type,
  wait_event,
  left(query, 120) AS query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY xact_start;

운영 판단은 단순히 "오래됐으니 kill"이 아니다. 백업, logical replication, batch job, migration이 의도적으로 오래 실행 중일 수 있다. 하지만 idle in transaction 상태로 오래된 세션은 vacuum뿐 아니라 lock, xmin horizon, connection pool 고갈까지 일으킬 수 있으므로 강한 경고 대상이다.

운영에서는 다음 정책을 두는 편이 좋다.

• application pool에 idle_in_transaction_session_timeout을 설정한다.
• 긴 batch는 작은 transaction 단위로 쪼갠다.
• online migration은 lock timeout과 statement timeout을 둔다.
• replication slot lag를 별도로 감시한다.

Freeze와 XID wraparound

PostgreSQL은 transaction ID(XID)를 사용해 row version의 가시성을 판단한다. XID 공간은 유한하며 순환한다. 아주 오래된 row가 계속 일반 XID를 들고 있으면, 시간이 지나 새 XID와 오래된 XID를 구분하는 안전성이 깨질 수 있다. 이를 막기 위해 PostgreSQL은 충분히 오래되어 모든 현재·미래 트랜잭션에 보이는 row를 frozen 상태로 표시한다.

핵심 지표는 database의 datfrozenxid와 table의 relfrozenxid age다.

-- 데이터베이스별 XID age
SELECT
  datname,
  age(datfrozenxid) AS xid_age
FROM pg_database
ORDER BY xid_age DESC;

-- 테이블별 XID age: TOAST 테이블까지 함께 고려
SELECT
  c.oid::regclass AS table_name,
  greatest(age(c.relfrozenxid), age(t.relfrozenxid)) AS xid_age,
  pg_size_pretty(pg_total_relation_size(c.oid)) AS total_size
FROM pg_class c
LEFT JOIN pg_class t ON c.reltoastrelid = t.oid
WHERE c.relkind IN ('r', 'm')
ORDER BY xid_age DESC
LIMIT 30;

autovacuum_freeze_max_age 기본값은 2억 transaction이다. 이 값에 가까워지면 PostgreSQL은 wraparound 방지 vacuum을 강제로 실행한다. 중요한 점은 autovacuum을 꺼도 wraparound 방지 autovacuum은 필요하면 실행될 수 있다는 것이다. 그만큼 위험도가 높다는 뜻이다.

운영자는 다음 상태를 특히 경계해야 한다.

• 로그에 must be vacuumed within ... transactions 경고가 보인다.
• pg_stat_activity에 autovacuum: VACUUM ... to prevent wraparound가 반복된다.
• 특정 대형 테이블의 xid_age가 계속 상승하고 vacuum 완료 이력이 없다.
• 긴 트랜잭션이나 replication slot 때문에 xmin horizon이 오래 유지된다.

Wraparound 위험이 임박하면 일반 성능 튜닝보다 데이터 안전이 우선이다. 이때는 index cleanup을 줄여서라도 heap freeze를 먼저 진행해야 할 수 있다. 다만 이것은 비상 대응이며, 평소에는 autovacuum이 여유 있게 freeze를 진행하도록 만들어야 한다.

Bloat 측정: 추정과 직접 측정의 차이

pg_stat_user_tables.n_dead_tup은 유용하지만 추정 통계다. 통계 갱신 타이밍, workload, analyze 상태에 따라 실제 물리 상태와 차이가 날 수 있다. 더 정확한 bloat 측정이 필요하면 pgstattuple 확장을 사용할 수 있다.

CREATE EXTENSION IF NOT EXISTS pgstattuple;

SELECT *
FROM pgstattuple('public.orders'::regclass);

pgstattuple은 live tuple, dead tuple, free space를 relation 단위로 보여준다. B-tree index는 pgstatindex()로 leaf density와 fragmentation을 볼 수 있다.

SELECT
  version,
  tree_level,
  pg_size_pretty(index_size) AS index_size,
  avg_leaf_density,
  leaf_fragmentation
FROM pgstatindex('public.orders_pkey'::regclass);

주의할 점은 pgstattuple이 정확한 대신 object를 실제로 스캔한다는 것이다. 대형 테이블에서 무심코 실행하면 I/O와 cache에 영향을 줄 수 있다. 운영 DB에서는 다음 원칙을 지킨다.

• 먼저 catalog/statistics 기반으로 후보를 좁힌다.
• 큰 테이블은 replica나 낮은 트래픽 시간에 측정한다.
• percentage만 보지 말고 실제 wasted bytes와 성능 영향을 같이 본다.
• index bloat는 REINDEX CONCURRENTLY 또는 새 index 생성·교체 전략까지 포함해 계획한다.

Autovacuum 튜닝의 순서

Autovacuum 튜닝은 파라미터를 낮추는 일이 아니라, "정리 작업이 workload보다 조금 앞서가게 만드는 일"이다. 권장 순서는 다음과 같다.

1. workload별 테이블 분류

모든 테이블을 같은 기준으로 보면 실패한다.

2. Global worker와 비용 제한 확인

Autovacuum worker가 부족하면 여러 테이블이 대기한다. 반대로 worker만 늘리고 I/O 제한을 풀면 업무 쿼리에 영향을 준다.

주요 파라미터는 다음과 같다.

고성능 스토리지에서 기본 cost limit/delay가 너무 보수적이면 vacuum이 뒤처질 수 있다. 반대로 shared storage나 replica lag가 민감한 환경에서는 너무 공격적인 vacuum이 I/O와 WAL 압박을 만들 수 있다.

3. Per-table storage parameter로 조정

대부분의 운영 튜닝은 per-table 설정이 더 안전하다.

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

조정 후에는 최소 한두 번의 peak cycle을 관찰해야 한다. dead tuple이 줄었는지뿐 아니라 vacuum duration, I/O wait, WAL generation, replica lag, query latency도 함께 봐야 한다.

VACUUM FULL은 언제 쓰는가

VACUUM FULL은 일반 vacuum과 다르다. 테이블을 새 파일로 다시 쓰고, 사용하지 않는 공간을 OS에 반환한다. 대신 ACCESS EXCLUSIVE lock을 잡고, 추가 디스크 공간이 필요하며, WAL과 replication lag를 크게 만들 수 있다. 그래서 routine maintenance로 쓰면 안 된다.

사용을 검토할 수 있는 경우는 제한적이다.

• 대량 삭제 이후 디스크를 반드시 OS에 반환해야 한다.
• 해당 테이블을 장시간 lock해도 되는 maintenance window가 있다.
• replica lag와 backup window 영향을 감당할 수 있다.
• 더 안전한 대안인 pg_repack, partition detach/drop, REINDEX CONCURRENTLY 등을 검토했다.

대량 데이터 삭제가 반복되는 테이블이라면 VACUUM FULL보다 설계를 바꾸는 것이 낫다. 예를 들어 기간 기준 partition을 쓰고 오래된 partition을 DROP하거나 DETACH하면 row 단위 삭제보다 훨씬 예측 가능하다.

운영 런북: vacuum 문제가 의심될 때

실제 장애 대응에서는 다음 순서로 좁혀간다.

1. 현상 확인

SELECT
  schemaname,
  relname,
  n_live_tup,
  n_dead_tup,
  last_autovacuum,
  last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;

확인할 질문:

• 특정 테이블만 문제인가, 전체 DB가 문제인가?
• dead tuple이 계속 증가 중인가, vacuum 이후 안정화되는가?
• last_autovacuum이 오래되었는가?

2. 현재 vacuum 진행 확인

SELECT
  relid::regclass AS table_name,
  phase,
  heap_blks_total,
  heap_blks_scanned,
  heap_blks_vacuumed,
  index_vacuum_count
FROM pg_stat_progress_vacuum;

확인할 질문:

• vacuum이 실제로 실행 중인가?
• 특정 phase에서 오래 머무는가?
• index vacuum이 반복적으로 오래 걸리는가?

3. 오래된 transaction 확인

SELECT
  pid,
  state,
  now() - xact_start AS xact_age,
  backend_xmin,
  wait_event_type,
  wait_event,
  left(query, 120) AS query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY xact_start;

확인할 질문:

• idle in transaction이 있는가?
• logical dump, batch, migration이 vacuum을 막고 있는가?
• application timeout 정책이 없는가?

4. Wraparound 위험 확인

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

SELECT
  c.oid::regclass AS table_name,
  age(c.relfrozenxid) AS xid_age,
  pg_size_pretty(pg_total_relation_size(c.oid)) AS size
FROM pg_class c
WHERE c.relkind IN ('r', 'm')
ORDER BY age(c.relfrozenxid) DESC
LIMIT 20;

확인할 질문:

• database/table age가 autovacuum_freeze_max_age에 가까운가?
• wraparound 방지 autovacuum이 반복되는가?
• 비상 manual vacuum이 필요한가?

5. 조치 선택

실무 기준선

PostgreSQL vacuum 운영은 "기본값이 나쁘다"에서 시작하면 안 된다. 기본 autovacuum은 대부분의 작은 테이블과 보통 workload에는 충분하다. 문제가 되는 것은 보통 큰 테이블, 높은 update/delete rate, 긴 트랜잭션, 느슨한 per-table 기준, 관측 부재가 겹친 경우다.

좋은 운영 기준선은 다음과 같다.

• autovacuum은 끄지 않는다.
• 대형·고변경 테이블은 per-table autovacuum 설정을 둔다.
• n_dead_tup, last_autovacuum, pg_stat_progress_vacuum, age(datfrozenxid)를 대시보드에 올린다.
• idle in transaction과 오래된 replication slot을 알림 대상으로 둔다.
• VACUUM FULL은 routine이 아니라 예외적 재작성 작업으로 취급한다.
• bloat 제거보다 먼저 bloat가 계속 생기는 원인을 줄인다.

PostgreSQL에서 vacuum은 배경 청소부가 아니라 MVCC 시스템의 생명 유지 장치다. 운영자는 vacuum을 "가끔 수동으로 돌리는 명령"이 아니라, workload와 함께 설계해야 하는 지속적인 maintenance pipeline으로 봐야 한다.

References

• PostgreSQL Documentation: Routine Vacuuming — https://www.postgresql.org/docs/current/routine-vacuuming.html
• PostgreSQL Documentation: Vacuuming configuration — https://www.postgresql.org/docs/current/runtime-config-vacuum.html
• PostgreSQL Documentation: VACUUM command — https://www.postgresql.org/docs/current/sql-vacuum.html
• PostgreSQL Documentation: Cumulative Statistics System — https://www.postgresql.org/docs/current/monitoring-stats.html
• PostgreSQL Documentation: Progress Reporting — https://www.postgresql.org/docs/current/progress-reporting.html
• PostgreSQL Documentation: pgstattuple — https://www.postgresql.org/docs/current/pgstattuple.html
• Crunchy Data Blog: Checking for PostgreSQL Bloat — https://www.crunchydata.com/blog/checking-for-postgresql-bloat
• Percona Blog: Overcoming VACUUM WRAPAROUND — https://www.percona.com/blog/overcoming-vacuum-wraparound