PostgreSQL 16~18 autovacuum이 밀릴 때: freeze age와 장기 트랜잭션 진단

PostgreSQL 16~18에서 autovacuum이 밀린다고 느껴질 때는 n_dead_tup만 보지 말고, 데이터베이스와 테이블의 frozen XID 나이, 오래 열린 트랜잭션, 현재 VACUUM 진행 상태를 함께 확인해야 해요. dead tuple이 많으면 공간 재사용과 성능 문제일 수 있지만, wraparound 위험은 age(datfrozenxid)와 age(relfrozenxid)가 더 직접적인 신호입니다.

오래 열린 트랜잭션·prepared transaction·replication slot이 오래된 xmin을 붙잡고 있으면 VACUUM이 실행돼도 지울 수 있는 행 버전이 제한될 수 있어요. 반대로 autovacuum 프로세스가 보이지 않는다는 사실만으로 실패라고 단정하면 안 됩니다. 트리거 기준, 작업 간격, 진행 중인 worker, 최근 실행 시각을 순서대로 대조해야 합니다.

적용 조건과 영향 범위

무엇을 같은 화면에서 봐야 하나

dead tuple과 freeze age는 서로 다른 질문이다

VACUUM은 UPDATE·DELETE 뒤 남은 오래된 행 버전을 재사용할 수 있게 하고, 실행계획 통계와 visibility map을 유지하며, 오래된 transaction ID를 freeze하는 역할을 합니다. 한 작업 안에 여러 목적이 섞여 있지만 진단 지표는 같지 않아요.

pg_stat_all_tables.n_dead_tup은 dead tuple의 추정치예요. 값이 커지면 테이블과 인덱스 비대화, 추가 I/O를 의심할 근거가 되지만 이 값 하나로 wraparound까지 남은 거리를 알 수는 없습니다. wraparound 쪽은 pg_database.datfrozenxid와 pg_class.relfrozenxid의 나이를 봐야 합니다. PostgreSQL 공식 문서는 age()가 현재 XID와 기준 XID 사이에 지난 트랜잭션 수를 나타낸다고 설명합니다.

따라서 “autovacuum이 밀린다”는 말은 최소 두 갈래로 나눠야 해요. 변경량을 따라가지 못해 dead tuple이 쌓이는 문제인지, 오래된 XID를 freeze하지 못해 안전 여유가 줄어드는 문제인지 먼저 구분합니다. 둘이 동시에 나타날 수도 있지만 대응 순서와 긴급도는 다릅니다.

첫 점검: 데이터베이스와 테이블의 XID 나이

클러스터 전체의 위험을 보려면 접속 가능한 데이터베이스마다 datfrozenxid 나이를 확인합니다. 이 값은 해당 데이터베이스 안 테이블들의 relfrozenxid 중 가장 오래된 값에 근거한 하한선이에요.

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

위험한 데이터베이스에 접속한 뒤에는 일반 테이블과 materialized view, 연결된 TOAST 테이블 가운데 나이가 큰 대상을 찾습니다. 아래 쿼리는 PostgreSQL 공식 문서의 진단 예제를 운영자가 읽기 쉽도록 스키마와 관계 이름을 분리한 형태예요.

SELECT n.nspname AS schema_name,
       c.relname AS relation_name,
       c.relkind,
       GREATEST(
         age(c.relfrozenxid),
         age(COALESCE(t.relfrozenxid, c.relfrozenxid))
       ) AS xid_age
FROM pg_class AS c
JOIN pg_namespace AS n
  ON n.oid = c.relnamespace
LEFT JOIN pg_class AS t
  ON t.oid = c.reltoastrelid
WHERE c.relkind IN ('r', 'm')
ORDER BY xid_age DESC
LIMIT 30;

SELECT name,
       setting,
       unit,
       source
FROM pg_settings
WHERE name IN (
  'autovacuum',
  'track_counts',
  'autovacuum_max_workers',
  'autovacuum_naptime',
  'autovacuum_freeze_max_age',
  'vacuum_freeze_table_age'
)
ORDER BY name;

두 번째 점검: autovacuum이 정말 일을 못 하고 있나

테이블 통계는 최근 실행 시각과 dead tuple 추정치를 함께 봅니다. 통계 수집은 비동기이고 값이 추정치라서, 한 번의 스냅샷으로 결론을 내리기보다 일정 간격으로 증가 방향을 비교하는 편이 안전해요.

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

현재 일반 VACUUM 또는 autovacuum worker가 실행 중이면 pg_stat_progress_vacuum에 행이 나타납니다. VACUUM FULL은 이 뷰가 아니라 pg_stat_progress_cluster에서 확인해야 해요. 대기 이벤트가 lock으로 보이면 pg_locks와 pg_stat_activity로 차단 세션을 찾는 절차를 이어서 확인할 수 있습니다.

SELECT p.pid,
       p.datname,
       p.relid::regclass AS relation_name,
       p.phase,
       p.heap_blks_scanned,
       p.heap_blks_total,
       a.query_start,
       a.wait_event_type,
       a.wait_event
FROM pg_stat_progress_vacuum AS p
JOIN pg_stat_activity AS a
  ON a.pid = p.pid
ORDER BY a.query_start;

판단 순서

세 번째 점검: 오래 열린 트랜잭션과 xmin 보유자

오래 열린 트랜잭션은 과거 스냅샷을 계속 필요로 할 수 있어요. 이 경우 VACUUM이 이미 실행됐더라도 해당 스냅샷에서 보일 수 있는 행 버전을 제거하지 못합니다. idle in transaction 세션도 트랜잭션을 끝내지 않았다면 후보가 됩니다. 같은 장기 트랜잭션은 동시 인덱스 작업도 지연시킬 수 있으므로, 인덱스 잔재가 함께 보인다면 CREATE INDEX CONCURRENTLY 실패 뒤 invalid 인덱스 확인 순서도 분리해서 점검하세요.

SELECT pid,
       usename,
       application_name,
       client_addr,
       state,
       xact_start,
       backend_xid,
       backend_xmin,
       wait_event_type,
       wait_event,
       LEFT(query, 120) AS query_sample
FROM pg_stat_activity
WHERE pid <> pg_backend_pid()
  AND (xact_start IS NOT NULL OR backend_xmin IS NOT NULL)
ORDER BY xact_start NULLS LAST;

세션만 보면 준비된 트랜잭션과 replication slot을 놓칠 수 있습니다. 공식 문서가 wraparound 복구 절차에서 이 둘을 별도로 확인하라고 안내하는 이유예요.

SELECT gid,
       owner,
       database,
       prepared,
       age(transaction) AS xid_age
FROM pg_prepared_xacts
ORDER BY xid_age DESC;

SELECT slot_name,
       slot_type,
       active,
       database,
       age(xmin) AS xmin_age,
       age(catalog_xmin) AS catalog_xmin_age
FROM pg_replication_slots
WHERE xmin IS NOT NULL
   OR catalog_xmin IS NOT NULL
ORDER BY GREATEST(
  COALESCE(age(xmin), 0),
  COALESCE(age(catalog_xmin), 0)
) DESC;

조치: 원인을 제거한 뒤 일반 VACUUM으로 확인한다

xmin을 붙잡던 원인이 정상적으로 끝났다면 위험 테이블부터 일반 VACUUM을 실행해 relfrozenxid가 전진하는지 확인합니다. 운영에서 처음부터 데이터베이스 전체에 VACUUM (FREEZE)를 넓게 걸면 불필요한 전체 페이지 작업과 I/O를 만들 수 있어요. 먼저 작은 범위와 유지보수 시간대에서 예상 시간을 측정하세요.

VACUUM (VERBOSE, ANALYZE) app.orders;

SELECT age(c.relfrozenxid) AS xid_age_after,
       s.last_vacuum,
       s.last_autovacuum,
       s.n_dead_tup
FROM pg_class AS c
JOIN pg_stat_user_tables AS s
  ON s.relid = c.oid
WHERE c.oid = 'app.orders'::regclass;

테이블별 autovacuum 조정은 마지막 단계다

변경이 집중되는 큰 테이블은 기본 scale factor만으로 trigger가 너무 늦을 수 있습니다. 그렇다고 전역 값을 먼저 낮추면 모든 테이블의 VACUUM 빈도와 I/O가 함께 늘어요. 테이블 크기, 분당 변경량, 실제 실행 시간, worker 포화를 측정한 뒤 해당 테이블에 한정한 storage parameter를 검토합니다.

SELECT c.oid::regclass AS relation_name,
       c.reltuples,
       c.reloptions
FROM pg_class AS c
WHERE c.oid = 'app.orders'::regclass;

-- 예시일 뿐입니다. 측정값 없이 운영에서 바로 적용하지 마세요.
ALTER TABLE app.orders SET (
  autovacuum_vacuum_scale_factor = 0.02,
  autovacuum_vacuum_threshold = 5000
);

하지 말아야 할 빠른 처방

• VACUUM FULL부터 실행하기: 테이블을 다시 쓰고 ACCESS EXCLUSIVE 잠금을 요구합니다. 일반적인 autovacuum 지연이나 임박한 wraparound의 첫 대응이 아니에요.
• freeze max age만 올리기: 경고 시점을 늦출 뿐, 장기 트랜잭션이나 worker 부족을 해결하지 않습니다.
• anti-wraparound autovacuum을 습관적으로 종료하기: 쿼리명이 (to prevent wraparound)로 끝나는 worker는 안전 장치입니다. DDL과 충돌한다는 이유만으로 반복 종료하면 여유가 더 줄어들 수 있어요.
• dead tuple 숫자 하나로 정상·장애 판단하기: 추정 통계, frozen XID 나이, 진행 단계와 오래된 xmin을 함께 보세요.
• 세션을 PID만 보고 종료하기: 애플리케이션 이름, 사용자, 트랜잭션 시작 시각, 업무 소유자와 재시도 동작을 먼저 확인합니다.

마지막 확인

조치 뒤에는 같은 쿼리를 다시 실행해 XID 나이가 감소하거나 증가 속도가 안정됐는지, 오래된 xmin 보유자가 사라졌는지, 최근 autovacuum 시각이 갱신되는지를 확인해요. 한 번의 성공보다 시간대별 추세가 더 중요합니다. worker가 늘어났는데 I/O 지연만 커졌다면 threshold와 cost 설정을 다시 조정해야 합니다.

운영 판단은 “autovacuum이 보이느냐”가 아니라 안전 여유가 유지되고 변경량을 따라잡는지에 달려 있습니다. frozen XID 나이로 긴급도를 정하고, 장기 트랜잭션·prepared transaction·replication slot을 분리한 뒤, 일반 VACUUM과 테이블별 조정을 가장 작은 범위에서 검증하세요.

공식 출처

• PostgreSQL 18: Routine Vacuuming
• PostgreSQL 18: The Cumulative Statistics System
• PostgreSQL 18: Progress Reporting
• PostgreSQL 18: pg_class
• PostgreSQL 18: pg_database
• PostgreSQL 18: Vacuuming Configuration
• PostgreSQL 18: VACUUM
• PostgreSQL 16: Routine Vacuuming