beautifulsoups.dev
Published on

Postgres VACUUM이 안 도는 이유 - wraparound·락

Authors

운영 중인 PostgreSQL에서 autovacuum 로그가 뜸해지고 테이블/인덱스가 계속 불어나면 보통 결론은 하나입니다. VACUUM이 "안 돈다". 하지만 실제로는 돌려고 했는데 못 도는 상태가 대부분입니다. 특히 아래 두 축에서 문제가 터집니다.

  • wraparound(트랜잭션 ID 순환) 위험 때문에 강제로 vacuum이 필요하지만, 오래 열린 트랜잭션이 이를 막음
  • 락(잠금) 충돌 때문에 vacuum이 필요한 테이블에 접근을 못함

이 글에서는 VACUUM이 안 도는 것처럼 보일 때 무엇을 먼저 확인해야 하는지, 어떤 SQL로 원인을 좁히고, 어떤 순서로 조치해야 하는지까지 실무 관점으로 정리합니다.

참고로 장애 대응은 결국 "원인 파악을 빠르게" 하는 게임입니다. 쿠버네티스에서 Running인데도 503이 나는 상황처럼, 겉으로 보이는 상태와 실제 병목이 다를 때가 많습니다. 비슷한 접근법은 EKS에서 Pod는 Running인데 503가 뜰 때 점검에서도 다룹니다.

VACUUM이 실제로 하는 일(짧게)

PostgreSQL의 MVCC 특성상 UPDATE/DELETE는 기존 행을 즉시 지우지 않고 dead tuple을 남깁니다. VACUUM은 크게 두 가지를 합니다.

  1. dead tuple을 정리하고, 재사용 가능한 공간으로 표시
  2. 통계 갱신(ANALYZE 포함 시)과 가시성 맵 업데이트

여기서 중요한 점은, 일반 VACUUM은 테이블을 "전부 잠가서" 못 쓰게 만들지 않습니다. 다만 특정 락이 필요하고, 다른 세션이 특정 락을 오래 잡으면 vacuum이 대기하거나 포기합니다.

증상 1: wraparound 경고가 뜨는데 VACUUM이 안 끝난다

wraparound가 왜 위험한가

PostgreSQL은 각 트랜잭션에 XID(트랜잭션 ID)를 부여합니다. XID는 32비트이며, 계속 증가하다가 언젠가 순환합니다. 이때 오래된 행의 가시성 판단이 깨질 수 있어 데이터 무결성 문제가 됩니다.

그래서 PostgreSQL은 wraparound를 막기 위해 강제 vacuum을 수행합니다. 핵심은 FREEZE입니다.

  • VACUUM (FREEZE)는 오래된 튜플의 XID를 "영구히 과거"로 고정해 wraparound 위험을 제거
  • autovacuum도 임계치에 도달하면 aggressive vacuum을 수행

하지만 문제는, 오래 열린 트랜잭션이 있으면 freeze가 진행되지 못하고 relfrozenxid가 앞으로 당겨지지 않습니다.

지금 wraparound가 얼마나 임박했는지 확인

아래 SQL로 데이터베이스별로 "얼마나 XID를 소모했는지"를 봅니다.

SELECT
  datname,
  age(datfrozenxid) AS xid_age,
  current_setting('autovacuum_freeze_max_age') AS freeze_max_age
FROM pg_database
ORDER BY xid_age DESC;

xid_age가 autovacuum_freeze_max_age에 가까우면, autovacuum이 공격적으로 돌거나 경고 로그가 나옵니다.

테이블 단위로도 확인합니다.

SELECT
  n.nspname AS schema,
  c.relname AS table,
  age(c.relfrozenxid) AS table_xid_age,
  c.reloptions
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = 'r'
ORDER BY table_xid_age DESC
LIMIT 30;

여기서 상위에 뜨는 테이블이 wraparound 관점에서 가장 위험합니다.

wraparound를 막는 "범인" 찾기: 오래 열린 트랜잭션

VACUUM이 freeze를 못 하는 가장 흔한 이유는 "트랜잭션이 너무 오래 열려 있음"입니다. 아래로 상위 장기 트랜잭션을 찾습니다.

SELECT
  pid,
  usename,
  application_name,
  client_addr,
  state,
  xact_start,
  now() - xact_start AS xact_age,
  query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY xact_age DESC
LIMIT 30;

특히 다음 패턴이 위험합니다.

  • 배치가 BEGIN 후 대량 읽기/쓰기하고 커밋을 늦게 함
  • 커넥션 풀/앱 버그로 idle in transaction 상태가 장시간 유지
  • 논리 복제 슬롯/CDC가 오래된 xmin을 붙잡는 구조

idle in transaction은 거의 항상 "즉시 고쳐야 하는" 애플리케이션 문제입니다.

SELECT
  pid,
  usename,
  application_name,
  state,
  now() - xact_start AS xact_age,
  query
FROM pg_stat_activity
WHERE state = 'idle in transaction'
ORDER BY xact_age DESC;

조치 순서(실무)

  1. 장기 트랜잭션을 종료하거나 애플리케이션에서 트랜잭션 범위를 줄입니다.
    • 운영에서 가장 빠른 응급조치는 pg_terminate_backend(pid)지만, 영향도(롤백 비용)를 고려해야 합니다.
  2. 위험 테이블에 수동 vacuum freeze를 걸어 wraparound를 뒤로 미룹니다.
VACUUM (FREEZE, VERBOSE) public.big_table;
  1. 이후 autovacuum이 정상적으로 따라오도록 파라미터와 테이블별 설정을 조정합니다.

증상 2: VACUUM이 대기 상태로 멈춘다(락 충돌)

VACUUM이 필요한 락과 충돌하는 락

일반 VACUUM은 보통 ShareUpdateExclusiveLock 수준을 사용하며, 대부분의 SELECT/INSERT와는 공존합니다. 하지만 다음 상황에서 대기가 길어질 수 있습니다.

  • DDL(ALTER TABLE, CREATE INDEX, DROP, TRUNCATE)이 더 강한 락을 잡음
  • VACUUM (FULL)은 AccessExclusiveLock이 필요해서 사실상 테이블을 독점
  • vacuum이 인덱스 청소 단계에서 특정 작업과 경합

즉, "vacuum이 안 돈다"는 말은 종종 "누군가가 테이블 락을 오래 잡고 있다"는 뜻입니다.

vacuum이 지금 무엇을 기다리는지 확인

pg_stat_activity에서 wait 이벤트를 봅니다.

SELECT
  pid,
  usename,
  application_name,
  state,
  wait_event_type,
  wait_event,
  query
FROM pg_stat_activity
WHERE query ILIKE '%vacuum%'
ORDER BY pid;

락 대기라면 pg_locks와 조합해 블로커를 찾습니다.

WITH blocked AS (
  SELECT
    a.pid AS blocked_pid,
    a.query AS blocked_query,
    l.locktype,
    l.relation
  FROM pg_locks l
  JOIN pg_stat_activity a ON a.pid = l.pid
  WHERE NOT l.granted
), blockers AS (
  SELECT
    bl.blocked_pid,
    a.pid AS blocker_pid,
    a.usename AS blocker_user,
    a.application_name AS blocker_app,
    a.query AS blocker_query
  FROM blocked bl
  JOIN pg_locks l2
    ON l2.locktype = bl.locktype
   AND l2.relation = bl.relation
   AND l2.granted
  JOIN pg_stat_activity a ON a.pid = l2.pid
)
SELECT *
FROM blockers
ORDER BY blocked_pid, blocker_pid;

이 결과에서 blocker_query가 DDL이거나 장기 트랜잭션이면, 그 세션이 vacuum을 막고 있는 겁니다.

조치 포인트

  • DDL을 운영 피크에 수행하지 말고, 락 타임아웃을 명시합니다.
SET lock_timeout = '3s';
ALTER TABLE public.big_table ADD COLUMN new_col text;
  • vacuum 자체도 무한 대기하지 않게 타임아웃을 둘 수 있습니다.
SET statement_timeout = '30min';
VACUUM (VERBOSE, ANALYZE) public.big_table;
  • VACUUM (FULL)은 마지막 수단으로 두고, 가능하면 pg_repack 같은 온라인 재작성 도구를 검토합니다.

autovacuum이 "안 도는" 것처럼 보이는 또 다른 흔한 이유

wraparound와 락이 가장 치명적이지만, 아래도 현장에서 자주 겹칩니다.

1) autovacuum이 너무 약하다(스케일 대비)

대형 테이블/고 TPS 환경에서 기본값은 보통 부족합니다.

  • autovacuum_max_workers가 너무 작아 큐가 밀림
  • autovacuum_vacuum_cost_limit/autovacuum_vacuum_cost_delay가 보수적이라 처리량이 낮음
  • 테이블별로 dead tuple이 쌓이는데 threshold가 너무 큼

현재 autovacuum 설정을 확인합니다.

SHOW autovacuum;
SHOW autovacuum_max_workers;
SHOW autovacuum_vacuum_cost_limit;
SHOW autovacuum_vacuum_cost_delay;
SHOW autovacuum_naptime;

테이블별로 튜닝이 필요하면 ALTER TABLE ... SET으로 조정합니다.

ALTER TABLE public.big_table SET (
  autovacuum_vacuum_scale_factor = 0.02,
  autovacuum_analyze_scale_factor = 0.01,
  autovacuum_vacuum_threshold = 5000,
  autovacuum_analyze_threshold = 5000
);

2) autovacuum이 돌긴 도는데 "효과"가 없다

다음 케이스는 vacuum이 실행되어도 dead tuple이 충분히 줄지 않습니다.

  • 업데이트가 매우 빈번해서 dead tuple 생성 속도가 청소 속도를 앞지름
  • 인덱스 bloat가 심한데 일반 vacuum으로는 인덱스 크기가 줄지 않음

진단은 pg_stat_user_tables를 먼저 봅니다.

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

last_autovacuum은 최근인데 n_dead_tup가 계속 크면, 처리량/설정/워크로드 구조를 같이 봐야 합니다.

3) 복제 슬롯이 xmin을 잡아 vacuum을 방해

논리 복제(또는 일부 CDC 툴)는 replication slot이 오래된 xmin을 유지해 vacuum이 튜플 제거를 못 하게 만들 수 있습니다.

SELECT
  slot_name,
  plugin,
  slot_type,
  active,
  xmin,
  catalog_xmin,
  restart_lsn
FROM pg_replication_slots
ORDER BY active DESC, slot_name;

active = false인데도 슬롯이 남아 있고 xmin이 오래되면 정리 대상일 수 있습니다(삭제는 영향이 크므로 도구/파이프라인 확인 후 진행).

운영 대응 체크리스트(우선순위)

  1. age(datfrozenxid)가 임계치에 가까운가(=wraparound 위험)?
  2. 장기 트랜잭션, 특히 idle in transaction이 있는가?
  3. vacuum이 락을 기다리는가? 블로커 쿼리는 무엇인가?
  4. autovacuum 워커 수/처리량이 스케일에 맞는가?
  5. replication slot이 vacuum을 막고 있지 않은가?

이 순서가 중요한 이유는, wraparound는 "성능" 문제가 아니라 "데이터 무결성"과 직결된 강제 장애로 이어질 수 있기 때문입니다.

예시: wraparound 경고 발생 시 즉시 실행할 커맨드 묶음

아래는 장애 대응 중 터미널에서 빠르게 실행할 만한 SQL 묶음입니다.

-- 1) DB별 xid age
SELECT datname, age(datfrozenxid) AS xid_age
FROM pg_database
ORDER BY xid_age DESC;

-- 2) 테이블별 xid age 상위
SELECT n.nspname, c.relname, age(c.relfrozenxid) AS table_xid_age
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = 'r'
ORDER BY table_xid_age DESC
LIMIT 20;

-- 3) 장기 트랜잭션
SELECT pid, usename, application_name, state,
       xact_start, now() - xact_start AS xact_age,
       query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY xact_age DESC
LIMIT 20;

원인이 장기 트랜잭션으로 좁혀지면, 애플리케이션 레벨에서 트랜잭션 범위를 줄이는 것이 근본 해결입니다. 이는 분산 환경에서 "보상 트랜잭션"으로 수습하는 접근과도 닮아 있습니다. 트랜잭션을 길게 잡는 설계가 어떤 운영 비용을 만드는지는 MSA Saga 보상 트랜잭션 설계와 디버깅 실전도 함께 참고할 만합니다.

마무리

PostgreSQL에서 VACUUM이 안 도는 것처럼 보일 때, 대부분은 vacuum 자체가 문제가 아니라 vacuum이 전진할 수 없는 조건이 문제입니다.

  • wraparound: age(datfrozenxid)와 장기 트랜잭션을 최우선으로 확인
  • 락: vacuum 대기 여부와 블로킹 세션을 찾아 DDL/장기 락을 제거

정리하면, "vacuum을 더 돌려라"가 아니라 "vacuum이 돌 수 있게 만들어라"가 운영에서의 정답입니다.

Discuss on Twitter • View on GitHub