PostgreSQL VACUUM과 MVCC 내부 구조 완벽 가이드: 테이블 Bloat 방지·Autovacuum 튜닝·XID Wraparound 대응

-- 튜플의 숨겨진 시스템 컬럼 조회
SELECT ctid, xmin, xmax, id, name
FROM users
WHERE id = 1;

-- 결과 예시:
--  ctid  | xmin  | xmax | id |  name
-- -------+-------+------+----+--------
--  (0,1) | 12345 |    0 |  1 | Alice

-- UPDATE 전후 비교
BEGIN;
UPDATE users SET name = 'Bob' WHERE id = 1;

-- 같은 트랜잭션에서 확인 (pageinspect 확장 필요)
SELECT t_xmin, t_xmax, t_ctid, t_data
FROM heap_page_items(get_raw_page('users', 0))
WHERE t_xmin IS NOT NULL;

-- 결과: 원본 튜플의 xmax가 현재 트랜잭션 ID로 설정됨
-- 새로운 튜플이 별도 위치에 생성됨
COMMIT;

-- Dead Tuple 현황 및 HOT 업데이트 비율 조회
SELECT
    schemaname,
    relname AS table_name,
    n_live_tup,
    n_dead_tup,
    ROUND(n_dead_tup::numeric / NULLIF(n_live_tup + n_dead_tup, 0) * 100, 2) AS dead_ratio_pct,
    n_tup_hot_upd,
    n_tup_upd,
    ROUND(n_tup_hot_upd::numeric / NULLIF(n_tup_upd, 0) * 100, 2) AS hot_update_ratio_pct,
    last_vacuum,
    last_autovacuum
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC;

-- fillfactor를 80%로 설정하여 HOT 업데이트 공간 확보
ALTER TABLE orders SET (fillfactor = 80);
-- 기존 데이터에 적용하려면 VACUUM FULL 또는 pg_repack 필요

-- VACUUM VERBOSE로 상세 동작 확인
VACUUM (VERBOSE, ANALYZE) orders;

-- 출력 예시:
-- INFO:  vacuuming "public.orders"
-- INFO:  scanned index "orders_pkey" to remove 15234 row versions
-- INFO:  scanned index "idx_orders_user_id" to remove 15234 row versions
-- INFO:  table "orders": removed 15234 dead item identifiers in 892 pages
-- INFO:  table "orders": found 15234 removable, 2847561 nonremovable row versions
--        in 45213 out of 62874 pages
-- INFO:  table "orders": truncated 62874 to 51230 pages
-- DETAIL:  CPU: user: 2.15 s, system: 0.89 s, elapsed: 5.43 s

-- pgstattuple로 정밀 bloat 측정
CREATE EXTENSION IF NOT EXISTS pgstattuple;

SELECT
    table_len,
    tuple_count,
    tuple_len,
    tuple_percent,
    dead_tuple_count,
    dead_tuple_len,
    dead_tuple_percent,
    free_space,
    free_percent
FROM pgstattuple('orders');

-- 결과 예시:
--  table_len   | 514850816
--  tuple_count | 2847561
--  tuple_len   | 398258540
--  tuple_percent | 77.35
--  dead_tuple_count | 0        -- VACUUM 직후라면 0
--  dead_tuple_len   | 0
--  dead_tuple_percent | 0
--  free_space   | 89456280
--  free_percent | 17.37       -- 이 비율이 높으면 bloat 상태

-- postgresql.conf 권장 설정 (전역)
-- autovacuum_max_workers = 5             -- 기본 3에서 증가
-- autovacuum_vacuum_cost_delay = 2ms     -- PostgreSQL 17 기본값
-- autovacuum_vacuum_cost_limit = 400     -- 기본 200에서 증가
-- maintenance_work_mem = 1GB             -- VACUUM용 메모리 충분히 할당
-- autovacuum_naptime = 30s               -- 기본 1분에서 단축

-- 대용량 테이블에 대한 개별 autovacuum 설정
ALTER TABLE orders SET (
    autovacuum_vacuum_scale_factor = 0.01,     -- 1% (기본 20% 대비 공격적)
    autovacuum_vacuum_threshold = 1000,         -- 최소 1000개
    autovacuum_analyze_scale_factor = 0.005,    -- 분석도 더 자주
    autovacuum_vacuum_cost_delay = 1,           -- 더 빠르게 처리 (ms)
    autovacuum_vacuum_cost_limit = 600          -- 더 많은 작업 허용
);

-- 로그성 테이블 (INSERT만 발생, UPDATE/DELETE 없음)
ALTER TABLE audit_logs SET (
    autovacuum_vacuum_scale_factor = 0.05,
    autovacuum_freeze_max_age = 500000000,     -- freeze만 관리
    autovacuum_enabled = true
);

-- 현재 실행 중인 autovacuum 프로세스 확인
SELECT
    pid,
    datname,
    relid::regclass AS table_name,
    phase,
    heap_blks_total,
    heap_blks_scanned,
    heap_blks_vacuumed,
    ROUND(100.0 * heap_blks_vacuumed / NULLIF(heap_blks_total, 0), 1) AS progress_pct,
    index_vacuum_count,
    num_dead_tuples
FROM pg_stat_progress_vacuum;

-- Autovacuum이 필요하지만 아직 실행되지 않은 테이블 목록
SELECT
    schemaname,
    relname,
    n_live_tup,
    n_dead_tup,
    ROUND(n_dead_tup::numeric / NULLIF(n_live_tup, 0) * 100, 2) AS dead_pct,
    last_autovacuum,
    last_autoanalyze,
    CURRENT_TIMESTAMP - COALESCE(last_autovacuum, '2000-01-01'::timestamp) AS since_last_vacuum
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY n_dead_tup DESC
LIMIT 20;

-- 테이블 Bloat 추정 쿼리 (통계 기반, 무잠금)
WITH constants AS (
    SELECT
        current_setting('block_size')::numeric AS bs,
        23 AS hdr,   -- 힙 튜플 헤더 크기
        8 AS ma      -- MAXALIGN
),
bloat_info AS (
    SELECT
        schemaname,
        tablename,
        cc.reltuples::bigint AS est_rows,
        cc.relpages::bigint AS real_pages,
        bs,
        CEIL((cc.reltuples * (datahdr + nullhdr + 4 + ma -
            CASE WHEN datahdr % ma = 0 THEN ma ELSE datahdr % ma END
        )) / (bs - 20)) AS est_pages
    FROM (
        SELECT
            schemaname,
            tablename,
            hdr + COALESCE(SUM(
                CASE WHEN staattnum IS NOT NULL
                    THEN (1 + stawidth) ELSE 0 END
            ), 0) AS datahdr,
            COALESCE(SUM(
                CASE WHEN staattnum IS NOT NULL AND stanullfrac > 0
                    THEN 1 ELSE 0 END
            ) / 8, 0) AS nullhdr,
            ma, bs, hdr
        FROM pg_stats
        CROSS JOIN constants
        LEFT JOIN pg_statistic ON schemaname = schemaname AND tablename = tablename
        GROUP BY schemaname, tablename, hdr, ma, bs
    ) AS sub
    JOIN pg_class cc ON cc.relname = sub.tablename
    JOIN pg_namespace nn ON cc.relnamespace = nn.oid AND nn.nspname = sub.schemaname
)
SELECT
    schemaname,
    tablename,
    real_pages,
    est_pages,
    CASE WHEN real_pages > 0
        THEN ROUND(100.0 * (real_pages - est_pages) / real_pages, 1)
        ELSE 0
    END AS bloat_pct,
    pg_size_pretty((real_pages - est_pages)::bigint * bs::bigint) AS wasted_size
FROM bloat_info
WHERE real_pages > est_pages + 10
ORDER BY (real_pages - est_pages) * bs DESC
LIMIT 20;

-- 데이터베이스별 XID 사용량 모니터링
SELECT
    datname,
    age(datfrozenxid) AS xid_age,
    ROUND(100.0 * age(datfrozenxid) / 2147483647, 2) AS pct_towards_wraparound,
    current_setting('autovacuum_freeze_max_age')::bigint AS freeze_max_age,
    CASE
        WHEN age(datfrozenxid) > 1500000000 THEN 'CRITICAL'
        WHEN age(datfrozenxid) > 1000000000 THEN 'WARNING'
        WHEN age(datfrozenxid) > 500000000  THEN 'CAUTION'
        ELSE 'OK'
    END AS status
FROM pg_database
WHERE datallowconn
ORDER BY age(datfrozenxid) DESC;

-- 테이블별 XID age 확인 (가장 오래된 것부터)
SELECT
    c.oid::regclass AS table_name,
    age(c.relfrozenxid) AS xid_age,
    pg_size_pretty(pg_table_size(c.oid)) AS table_size,
    ROUND(100.0 * age(c.relfrozenxid) /
        current_setting('autovacuum_freeze_max_age')::bigint, 1) AS pct_of_freeze_max
FROM pg_class c
JOIN pg_namespace n ON c.relnamespace = n.oid
WHERE c.relkind = 'r'
    AND n.nspname NOT IN ('pg_catalog', 'information_schema')
ORDER BY age(c.relfrozenxid) DESC
LIMIT 20;

# 1. pg_repack 설치 (서버에서)
# Debian/Ubuntu
sudo apt-get install postgresql-17-repack

# RHEL/CentOS
sudo yum install pg_repack_17

# 2. 확장 설치 (데이터베이스에서)
psql -d mydb -c "CREATE EXTENSION IF NOT EXISTS pg_repack;"

# 3. 디스크 공간 확인 (대상 테이블 크기의 2배 이상 필요)
psql -d mydb -c "SELECT pg_size_pretty(pg_total_relation_size('orders'));"

# 4. 단일 테이블 repack (인덱스 포함)
pg_repack -d mydb -t orders --no-superuser-check --wait-timeout=60

# 5. 특정 인덱스만 repack
pg_repack -d mydb -i idx_orders_created_at --no-superuser-check

# 6. 스키마 전체 repack (주의: 시간 오래 걸림)
pg_repack -d mydb -s public --no-superuser-check --wait-timeout=120

# 7. 실행 후 확인
psql -d mydb -c "
SELECT
    relname,
    pg_size_pretty(pg_table_size(oid)) AS table_size,
    pg_size_pretty(pg_indexes_size(oid)) AS indexes_size,
    pg_size_pretty(pg_total_relation_size(oid)) AS total_size
FROM pg_class
WHERE relname = 'orders';
"

-- 비활성 Replication Slot 확인
SELECT
    slot_name,
    slot_type,
    active,
    age(xmin) AS slot_xid_age,
    age(catalog_xmin) AS catalog_xid_age,
    pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS wal_lag
FROM pg_replication_slots
WHERE NOT active;

-- 불필요한 Slot 제거 (확인 후 실행)
SELECT pg_drop_replication_slot('inactive_slot_name');

-- 5분 이상 실행 중인 트랜잭션 확인
SELECT
    pid,
    usename,
    state,
    age(backend_xid) AS xid_age,
    now() - xact_start AS xact_duration,
    now() - query_start AS query_duration,
    LEFT(query, 100) AS query_preview
FROM pg_stat_activity
WHERE state != 'idle'
    AND xact_start IS NOT NULL
    AND now() - xact_start > interval '5 minutes'
ORDER BY xact_start;

-- idle in transaction 상태 확인 (가장 위험)
SELECT
    pid,
    usename,
    state,
    now() - state_change AS idle_duration,
    LEFT(query, 100) AS last_query
FROM pg_stat_activity
WHERE state = 'idle in transaction'
    AND now() - state_change > interval '10 minutes';

-- 오래된 Prepared Transaction 확인
SELECT
    gid,
    prepared,
    owner,
    database,
    now() - prepared AS age
FROM pg_prepared_xacts
ORDER BY prepared;