PostgreSQL VACUUM 안 도는 이유 7가지와 해법

-- 1) 테이블별 vacuum/autovacuum 실행 이력
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;

-- 2) 현재 진행 중인 vacuum 확인(autovacuum 포함)
SELECT
  pid,
  usename,
  state,
  wait_event_type,
  wait_event,
  query_start,
  now() - query_start AS running_for,
  query
FROM pg_stat_activity
WHERE query ILIKE '%vacuum%'
ORDER BY query_start;

# postgresql.conf
log_autovacuum_min_duration = 0          # 모든 autovacuum 로깅(부담되면 1s~5s로)
log_min_duration_statement = 1000        # 느린 쿼리 추적(선택)

SHOW autovacuum;

-- 테이블 옵션으로 autovacuum이 꺼진 경우
SELECT
  n.nspname AS schema,
  c.relname AS table,
  c.reloptions
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = 'r'
  AND c.reloptions IS NOT NULL
  AND array_to_string(c.reloptions, ',') ILIKE '%autovacuum%'
ORDER BY 1,2;

autovacuum = on

ALTER TABLE your_table SET (autovacuum_enabled = true);

-- autovacuum 트리거 임계치 추정
-- threshold ≈ autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor * reltuples
SELECT
  n.nspname AS schema,
  c.relname AS table,
  st.n_dead_tup,
  c.reltuples::bigint AS reltuples_est,
  current_setting('autovacuum_vacuum_threshold')::int AS base_threshold,
  current_setting('autovacuum_vacuum_scale_factor')::float AS scale_factor,
  (current_setting('autovacuum_vacuum_threshold')::int
   + current_setting('autovacuum_vacuum_scale_factor')::float * c.reltuples)::bigint AS vacuum_trigger_est
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
JOIN pg_stat_user_tables st ON st.relid = c.oid
WHERE c.relkind='r'
ORDER BY (st.n_dead_tup - (current_setting('autovacuum_vacuum_threshold')::int
   + current_setting('autovacuum_vacuum_scale_factor')::float * c.reltuples)) DESC
LIMIT 30;

-- 예: 대형/고변경 테이블은 더 자주 vacuum
ALTER TABLE orders SET (
  autovacuum_vacuum_scale_factor = 0.01,
  autovacuum_vacuum_threshold = 5000
);

-- 분석(ANALYZE)도 같이 자주
ALTER TABLE orders SET (
  autovacuum_analyze_scale_factor = 0.02,
  autovacuum_analyze_threshold = 3000
);

SHOW autovacuum_max_workers;
SHOW autovacuum_naptime;

-- 데이터베이스별 autovacuum 작업자/런처 상태
SELECT
  backend_type,
  count(*)
FROM pg_stat_activity
WHERE backend_type IN ('autovacuum launcher', 'autovacuum worker')
GROUP BY 1;

autovacuum_max_workers = 6      # CPU/IO 여유에 따라 3~10+ 조정
autovacuum_naptime = 10s        # 기본 1min은 느릴 수 있음

-- 오래 열린 트랜잭션 찾기
SELECT
  pid,
  usename,
  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 20;

-- DB별 freeze 진행 상태(랩어라운드 위험 관측)
SELECT
  datname,
  age(datfrozenxid) AS xid_age
FROM pg_database
ORDER BY xid_age DESC;

idle_in_transaction_session_timeout = 60s
statement_timeout = 30s              # 워크로드에 맞게

SELECT pg_terminate_backend(<pid>);

SHOW autovacuum_vacuum_cost_limit;
SHOW autovacuum_vacuum_cost_delay;
SHOW vacuum_cost_limit;
SHOW vacuum_cost_delay;

# 예시(서버 여유에 맞게)
autovacuum_vacuum_cost_limit = 4000
autovacuum_vacuum_cost_delay = 2ms

ALTER TABLE events SET (
  autovacuum_vacuum_cost_limit = 8000,
  autovacuum_vacuum_cost_delay = 0
);

-- vacuum이 무엇을 기다리는지
SELECT
  pid,
  wait_event_type,
  wait_event,
  now() - query_start AS running_for,
  query
FROM pg_stat_activity
WHERE query ILIKE '%vacuum%';

-- 락 홀더/대기자 확인
SELECT
  a.pid,
  a.usename,
  a.state,
  now() - a.query_start AS age,
  l.locktype,
  l.mode,
  l.granted,
  left(a.query, 120) AS query
FROM pg_locks l
JOIN pg_stat_activity a ON a.pid = l.pid
WHERE l.relation IS NOT NULL
ORDER BY l.granted, age DESC;

VACUUM (VERBOSE, ANALYZE) your_table;

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_indexes_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;

VACUUM (FULL, ANALYZE) your_table;

-- A) dead tuple 상위 테이블
SELECT schemaname, relname, n_live_tup, n_dead_tup, last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;

-- B) 오래 열린 트랜잭션
SELECT pid, usename, state, now() - xact_start AS xact_age, left(query,120)
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY xact_age DESC
LIMIT 20;

-- C) vacuum 관련 세션의 대기 원인
SELECT pid, wait_event_type, wait_event, now() - query_start AS running_for, left(query,120)
FROM pg_stat_activity
WHERE query ILIKE '%vacuum%'
ORDER BY query_start;