Lv.3 중급PostgreSQL

2026.05.1828분 읽기Lv.3 중급

시리즈PostgreSQL Vacuum 완전 정복 · 파트 3시리즈 허브 보기

PostgreSQL Vacuum Part 3 — 실전 모니터링, Bloat 제거, 운영 전략

Vacuum을 이해하는 것과 실제로 다루는 것은 다르다. pgstattuple·pgstatindex로 Bloat를 숫자로 진단하고, 실전 모니터링 쿼리로 dead tuple·XID age·장기 트랜잭션·replication slot 지연을 한 번에 잡는다. VACUUM FULL·pg_repack·pg_squeeze·REINDEX CONCURRENTLY의 잠금 수준과 디스크 요구사항을 비교해 운영 상황에 맞는 도구를 선택하고, fillfactor와 HOT Update로 Bloat 자체를 예방하는 방법까지 — PostgreSQL Vacuum 시리즈의 완결편.

시리즈 구성

• Part 1 — VACUUM의 존재 이유: MVCC와 Dead Tuple
• Part 2 — Autovacuum 튜닝과 XID Wraparound 재난 방지
• Part 3 — 실전 모니터링, Bloat 제거, 운영 전략 (현재 편 · 완결)

목차

1. Bloat 정밀 진단: pgstattuple 활용법
2. 실전 모니터링 쿼리 모음
3. pg_stat_progress_vacuum — Vacuum 실시간 추적
4. Bloat 제거 도구 비교: VACUUM FULL vs pg_repack vs pg_squeeze
5. OLTP / OLAP 혼합 환경 전략
6. fillfactor로 Bloat 예방하기
7. 자주 묻는 질문 (FAQ)
8. 운영 체크리스트 — 일간 / 주간 / 월간
9. 시리즈 최종 정리

1. Bloat 정밀 진단: pgstattuple 활용법

pg_stat_user_tables의 n_dead_tup은 빠른 확인용이다. 정확한 Bloat 측정은 pgstattuple 확장이 필요하다.

-- 확장 설치 (슈퍼유저 필요)
CREATE EXTENSION IF NOT EXISTS pgstattuple;

테이블 Bloat 측정

-- 기본 pgstattuple 분석
SELECT * FROM pgstattuple('public.orders');

결과 컬럼 해석:

-- 전체 테이블 Bloat 요약 (대형 DB에서는 읽기 전용 복제본에서 실행 권장)
SELECT *
FROM (
    SELECT
        schemaname,
        tablename,
        (pgstattuple(schemaname || '.' || tablename)).*
    FROM pg_tables
    WHERE schemaname = 'public'
) t
ORDER BY dead_tuple_percent DESC
LIMIT 20;

주의: pgstattuple은 전체 테이블을 스캔하므로 대형 테이블에서 I/O 부하가 크다. 프로덕션에서는 특정 테이블만 선택적으로 실행하거나, 읽기 전용 복제본에서 돌리는 것을 권장한다.

인덱스 Bloat 측정

-- 인덱스 Bloat 분석
SELECT * FROM pgstatindex('public.orders_pkey');

-- 전체 인덱스 Bloat 요약
SELECT
    c.relname                                        AS index_name,
    i.indrelid::regclass                             AS table_name,
    pg_size_pretty(pg_relation_size(c.oid))          AS index_size,
    (pgstatindex(c.oid)).avg_leaf_density,
    (pgstatindex(c.oid)).leaf_fragmentation
FROM pg_index i
JOIN pg_class c ON c.oid = i.indexrelid
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname = 'public'
  AND c.relkind = 'i'
ORDER BY pg_relation_size(c.oid) DESC
LIMIT 20;

2. 실전 모니터링 쿼리 모음

대시보드나 cron 기반 알람 스크립트에 바로 붙여 쓸 수 있는 쿼리 모음이다.

종합 Vacuum 상태 점검 (대시보드 메인 쿼리)

WITH vacuum_stats AS (
    SELECT
        s.schemaname || '.' || s.relname                        AS table_name,
        s.n_live_tup                                            AS live_rows,
        s.n_dead_tup                                            AS dead_rows,
        ROUND(100.0 * s.n_dead_tup
            / NULLIF(s.n_live_tup + s.n_dead_tup, 0), 1)       AS dead_pct,
        age(c.relfrozenxid)                                     AS xid_age,
        pg_size_pretty(pg_total_relation_size(c.oid))           AS total_size,
        s.last_vacuum,
        s.last_autovacuum,
        s.vacuum_count,
        s.autovacuum_count
    FROM pg_stat_user_tables s
    JOIN pg_class c ON c.relname = s.relname
        AND c.relnamespace = (
            SELECT oid FROM pg_namespace WHERE nspname = s.schemaname
        )
)
SELECT
    table_name,
    live_rows,
    dead_rows,
    dead_pct,
    xid_age,
    total_size,
    COALESCE(last_autovacuum::text, last_vacuum::text, 'NEVER') AS last_vacuumed,
    CASE
        WHEN xid_age  > 1500000000 THEN 'XID CRITICAL'
        WHEN dead_pct > 30         THEN 'BLOAT CRITICAL'
        WHEN xid_age  > 1000000000 THEN 'XID WARNING'
        WHEN dead_pct > 15         THEN 'BLOAT WARNING'
        WHEN last_autovacuum IS NULL
         AND last_vacuum IS NULL   THEN 'NEVER VACUUMED'
        ELSE 'OK'
    END AS health
FROM vacuum_stats
WHERE dead_rows > 100 OR xid_age > 100000000
ORDER BY
    CASE WHEN xid_age > 1500000000 OR dead_pct > 30 THEN 0 ELSE 1 END,
    dead_rows DESC;

Vacuum을 막는 주범 찾기

-- ① 장기 실행 트랜잭션 (Vacuum의 최대 적)
SELECT
    pid,
    usename,
    application_name,
    state,
    ROUND(EXTRACT(EPOCH FROM (now() - xact_start)) / 60, 1) AS tx_min,
    LEFT(query, 100) AS query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
  AND state NOT IN ('idle')
ORDER BY xact_start
LIMIT 10;

-- ② 비활성 Replication Slot (Vacuum 지연 유발)
SELECT
    slot_name,
    active,
    catalog_xmin,
    age(catalog_xmin)  AS catalog_age,
    xmin,
    age(xmin)          AS xmin_age,
    pg_size_pretty(
        pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)
    )                  AS wal_retained
FROM pg_replication_slots
ORDER BY age(xmin) DESC NULLS LAST;

-- ③ Vacuum Horizon을 잡고 있는 소스별 확인
SELECT
    'long_transaction'  AS source,
    pid::text           AS identifier,
    age(backend_xmin)   AS horizon_age
FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL
  AND state != 'idle'
UNION ALL
SELECT
    'replication_slot'  AS source,
    slot_name           AS identifier,
    age(xmin)           AS horizon_age
FROM pg_replication_slots
WHERE xmin IS NOT NULL
ORDER BY horizon_age DESC
LIMIT 10;

Autovacuum 활동 로그 활성화

-- Autovacuum 실행 시간이 1초 이상이면 로그 기록
ALTER SYSTEM SET log_autovacuum_min_duration = '1s';
SELECT pg_reload_conf();

-- 이후 pg_log에서 확인:
-- automatic vacuum of table "mydb.public.orders": ...
-- automatic analyze of table "mydb.public.orders": ...

3. pg_stat_progress_vacuum — Vacuum 실시간 추적

PostgreSQL 9.6+에서 도입된 pg_stat_progress_vacuum 뷰는 현재 진행 중인 Vacuum의 상세 정보를 실시간으로 제공한다.

-- 실행 중인 Vacuum 진행 현황 (PG 17 기준)
SELECT
    p.pid,
    p.datname,
    p.relid::regclass                                             AS table_name,
    p.phase,
    p.heap_blks_total,
    p.heap_blks_scanned,
    ROUND(100.0 * p.heap_blks_scanned
        / NULLIF(p.heap_blks_total, 0), 1)                       AS heap_scan_pct,
    p.heap_blks_vacuumed,
    p.index_vacuum_count,
    p.num_dead_item_ids,
    p.indexes_total,
    p.indexes_processed,
    ROUND(EXTRACT(EPOCH FROM now() - a.query_start) / 60, 1)     AS running_min
FROM pg_stat_progress_vacuum p
JOIN pg_stat_activity a USING (pid);

Vacuum 단계(Phase) 해석

팁: scanning heap 단계에서 heap_blks_scanned가 오랫동안 정체되면 Lock 경합이나 I/O 병목을 의심한다.

4. Bloat 제거 도구 비교: VACUUM FULL vs pg_repack vs pg_squeeze

Dead Tuple 제거를 넘어 물리적 공간 반환이 필요할 때 선택할 수 있는 세 가지 도구를 비교한다.

한눈에 비교

pg_repack 사용법

# 설치
apt install postgresql-17-repack
# 또는
CREATE EXTENSION pg_repack;

# 특정 테이블 리팩
pg_repack -h localhost -U postgres -d mydb -t public.orders

# 인덱스만 리팩 (테이블 데이터는 정상, 인덱스만 Bloat)
pg_repack -h localhost -U postgres -d mydb --only-indexes -t public.orders

# 전체 DB 리팩 (대규모 유지보수 시)
pg_repack -h localhost -U postgres -d mydb --no-superuser-check

주의: pg_repack은 작업 중 테이블 크기의 약 2배 디스크 공간이 필요하다. 1TB 테이블 리팩 시 최대 2TB 추가 공간이 필요하므로 사전에 여유 공간을 반드시 확인한다.

pg_squeeze 사용법

-- 설치
CREATE EXTENSION pg_squeeze;

-- 특정 테이블을 Bloat 모니터링 대상으로 등록 (자동 처리)
SELECT squeeze.add_squeeze_job(
    tabschema  := 'public',
    tabname    := 'orders',
    threshold  := 0.3,       -- Dead Tuple 30% 이상 시 자동 Squeeze
    max_retry  := 3
);

-- 즉시 수동 Squeeze
SELECT squeeze.squeeze_table('public', 'orders', NULL, NULL, NULL);

-- 등록된 작업 확인
SELECT * FROM squeeze.tables;

pg_squeeze는 서버사이드 Background Worker로 동작해 별도 CLI 없이도 운영된다. 단, 테이블에 PRIMARY KEY 또는 UNIQUE 제약이 반드시 있어야 한다.

인덱스 Bloat 제거: REINDEX CONCURRENTLY

-- 논블로킹 인덱스 재생성 (PG 12+)
REINDEX INDEX CONCURRENTLY public.orders_pkey;

-- 테이블의 모든 인덱스 재생성
REINDEX TABLE CONCURRENTLY public.orders;

주의: REINDEX CONCURRENTLY는 실행 중 xmin horizon을 잡아두므로 장시간 실행 시 다른 테이블의 Autovacuum에 간접 영향을 줄 수 있다. 피크 타임 외 시간대에 실행하는 것을 권장한다.

5. OLTP / OLAP 혼합 환경 전략

같은 PostgreSQL 인스턴스에서 OLTP(짧고 빈번한 트랜잭션)와 OLAP(오래 걸리는 분석 쿼리)를 함께 실행하면 Vacuum에 심각한 문제가 생긴다.

핵심 충돌 메커니즘

[OLAP 장기 쿼리가 Vacuum에 미치는 영향]

OLAP 분석 쿼리 시작 -----> 2시간 소요 -----> 쿼리 종료
         |
         v  트랜잭션 스냅샷 생성 (Horizon 고정)

스냅샷 생성 시점의 XID = "트랜잭션 Horizon"

- Vacuum이 정리 가능: Horizon 이전의 Dead Tuple만
- Vacuum이 못 하는 것: Horizon 이후 새로 생긴 Dead Tuple

결과: OLTP 테이블이 2시간치 Dead Tuple을 그대로 보유 -> Bloat

해결 전략 3가지

전략 1: 읽기 전용 복제본으로 OLAP 분리 (권장)

Primary DB에서는 OLAP 쿼리가 사라지므로, Autovacuum이 방해받지 않고 Dead Tuple을 즉각 정리한다.

전략 2: idle_in_transaction_session_timeout 설정

-- 분석 쿼리용 별도 Role에 타임아웃 적용
ALTER ROLE analyst SET statement_timeout = '30min';
ALTER ROLE analyst SET idle_in_transaction_session_timeout = '10min';

-- 전체 DB에 적용
ALTER DATABASE mydb SET idle_in_transaction_session_timeout = '30min';

전략 3: 파티셔닝으로 VACUUM 범위 축소

대형 히스토리 테이블은 날짜 기반 파티셔닝 후 오래된 파티션을 TRUNCATE 또는 DROP으로 관리한다. Dead Tuple이 발생하지 않으므로 VACUUM 자체가 불필요해진다.

-- 파티션 테이블 생성 예시 (월별)
CREATE TABLE events (
    id         BIGSERIAL,
    event_time TIMESTAMPTZ NOT NULL,
    payload    JSONB
) PARTITION BY RANGE (event_time);

CREATE TABLE events_2026_01 PARTITION OF events
    FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');

-- 3개월 이상 된 파티션 제거 (VACUUM 불필요)
DROP TABLE events_2025_12;

6. fillfactor로 Bloat 예방하기

Bloat가 생긴 후 치료하는 것보다 예방이 훨씬 낫다. fillfactor는 PostgreSQL이 데이터 페이지에 미리 여백을 남겨 두게 하는 설정이다.

HOT(Heap-Only Tuple) Update 원리

UPDATE 시 같은 페이지 내에 새 버전을 쓸 수 있으면, 인덱스를 건드리지 않고 페이지 내부만 갱신하는 HOT Update가 발생한다. 이는 인덱스 Bloat를 막고 Vacuum 부하도 줄인다.

[fillfactor=90 설정 시 페이지 구조]

+-----------------------------------------------------+
| [행1][행2][행3]...[행N]              [10% 여백]       |
+-----------------------------------------------------+
                                           ^
                                     UPDATE 시 이 여백에
                                     새 버전 저장 가능
                                     -> HOT Update 발생
                                     -> 인덱스 변경 없음

-- UPDATE가 빈번한 테이블에 fillfactor 적용
CREATE TABLE sessions (
    session_id UUID PRIMARY KEY,
    user_id    INT NOT NULL,
    last_seen  TIMESTAMPTZ,
    data       JSONB
) WITH (fillfactor = 80);  -- 80% 채움, 20% 여백 확보

-- 기존 테이블에 적용 (효과는 VACUUM FULL / pg_repack 이후 반영됨)
ALTER TABLE sessions SET (fillfactor = 80);
VACUUM FULL sessions;  -- 또는 pg_repack으로 무중단 적용

fillfactor 권장 값

7. 자주 묻는 질문 (FAQ)

Q1. "VACUUM FULL을 쓰면 안 되나요?"

A: 절대 안 되는 건 아니다. 다만 VACUUM FULL은 AccessExclusive Lock으로 테이블 전체를 잠그기 때문에 서비스 중에는 사용해선 안 된다. 유지보수 창이 있고, 테이블이 소형(수십 GB 이하)이며, 빠르게 공간을 회수해야 한다면 사용 가능하다. 그 외 대부분의 경우 pg_repack이나 pg_squeeze가 올바른 선택이다.

Q2. "autovacuum을 꺼버리면 어떻게 되나요?"

A: 절대 끄면 안 된다. autovacuum을 끄면 Dead Tuple이 무한 누적되고, XID Wraparound 방어 기능도 작동하지 않아 결국 데이터베이스가 쓰기를 거부하는 상황에 이른다. autovacuum이 문제처럼 느껴진다면, 끄는 것이 아니라 튜닝이 정답이다.

Q3. "VACUUM 후에도 테이블 크기가 줄지 않아요"

A: 표준 VACUUM은 Dead Tuple을 제거하되 디스크 공간을 OS에 돌려주지 않는다. 해당 공간은 "재사용 가능"으로 표시되어 새 데이터가 들어오면 먼저 채운다. 실제 파일 크기를 줄이려면 VACUUM FULL 또는 pg_repack이 필요하다. 단, 테이블 크기 자체가 줄지 않아도 Bloat가 없는 상태라면 성능 문제는 없다.

Q4. "HOT Update가 일어나고 있는지 어떻게 확인하나요?"

-- HOT Update 비율 확인
SELECT
    relname,
    n_tup_upd                                                    AS total_updates,
    n_tup_hot_upd                                                AS hot_updates,
    ROUND(100.0 * n_tup_hot_upd / NULLIF(n_tup_upd, 0), 1)     AS hot_update_pct
FROM pg_stat_user_tables
WHERE n_tup_upd > 0
ORDER BY n_tup_upd DESC
LIMIT 20;
-- hot_update_pct가 높을수록 좋다 (인덱스 Bloat 최소화)

Q5. "VACUUM이 끝났는데 왜 Dead Tuple이 여전히 있나요?"

A: 활성 트랜잭션의 스냅샷보다 새로운 Dead Tuple은 Vacuum이 정리할 수 없다. VACUUM 실행 시점에 열려 있던 오래된 트랜잭션이 있었다면, 그 트랜잭션이 열린 이후 생긴 Dead Tuple은 Vacuum이 건드리지 못한다. pg_stat_activity에서 오래된 트랜잭션을 확인하고 정리하는 것이 선행 조건이다.

Q6. "Bloat가 성능에 얼마나 영향을 주나요?"

A: Bloat 자체가 나쁜 것은 아니다. 오히려 적정 Bloat는 HOT Update를 위한 여백으로 기능한다. 문제는 과도한 Bloat다. dead_pct가 30-50%를 넘어서면 Seq Scan 비용이 선형으로 늘어나고, 인덱스 스캔 후 힙 조회 비용도 증가한다. 실무적으로는 dead_pct 20% 이상을 경고 기준으로 삼는 것이 일반적이다.

8. 운영 체크리스트 — 일간 / 주간 / 월간

일간 자동 모니터링 (Cron/Alert 설정 권장)

-- 알람 조건 1: XID Age 10억 초과
SELECT datname, age(datfrozenxid) AS xid_age
FROM pg_database
WHERE age(datfrozenxid) > 1000000000;

-- 알람 조건 2: Dead Tuple 비율 30% 초과
SELECT relname, n_dead_tup, n_live_tup,
       ROUND(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 1) AS dead_pct
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
  AND (100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0)) > 30;

-- 알람 조건 3: 30분 이상 열린 트랜잭션
SELECT pid, usename, state,
       ROUND(EXTRACT(EPOCH FROM (now() - xact_start)) / 60, 1) AS tx_min
FROM pg_stat_activity
WHERE xact_start < now() - interval '30 minutes'
  AND state NOT IN ('idle');

-- 알람 조건 4: 비활성 Replication Slot (WAL 축적)
SELECT slot_name, age(xmin) AS xmin_age,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS wal_lag
FROM pg_replication_slots
WHERE NOT active AND xmin IS NOT NULL;

주간 점검 항목

[ ] pg_stat_user_tables에서 last_autovacuum이 일주일 넘은 테이블 확인
[ ] 각 테이블 dead_pct 트렌드 확인 (증가 추세면 autovacuum 튜닝)
[ ] log_autovacuum_min_duration 로그에서 장시간 Autovacuum 확인
[ ] pg_stat_progress_vacuum으로 현재 실행 중인 Vacuum 점검
[ ] Replication Slot 상태 및 WAL 축적량 점검

월간 점검 항목

[ ] pgstattuple로 상위 10개 대형 테이블 Bloat 정밀 측정
[ ] pgstatindex로 인덱스 avg_leaf_density 점검 (20% 미만 -> REINDEX 검토)
[ ] XID Age 데이터베이스별 추이 그래프 확인
[ ] pg_repack 또는 pg_squeeze로 과도한 Bloat 테이블 정리
[ ] autovacuum 파라미터 리뷰: 추가된 대형 테이블이 있으면 테이블별 오버라이드 추가
[ ] PostgreSQL 마이너 버전 업데이트 확인 및 적용 계획 수립

9. 시리즈 최종 정리

3편에 걸쳐 PostgreSQL VACUUM의 모든 것을 살펴봤다. 핵심 개념을 한 장으로 정리하면 다음과 같다.

시리즈 전체 핵심 원칙 10가지

1. autovacuum은 절대 끄지 않는다 — 끄는 순간 Wraparound 재난으로 가는 길이다
2. 대형 테이블은 반드시 테이블별 파라미터를 오버라이드 — 전역 기본값은 소형 테이블 기준이다
3. NVMe 환경에서는 cost_delay=0 — 기본 제한은 HDD 시대의 유물이다
4. XID Age는 10억(1B) 초과 시 즉각 알람 — 방치하면 DB가 멈춘다
5. 장기 트랜잭션은 Vacuum의 적 — idle_in_transaction_session_timeout 설정 필수
6. VACUUM FULL은 유지보수 창 전용 — 프로덕션에서는 pg_repack/pg_squeeze
7. Bloat의 적정 수준은 허용 — dead_pct 20% 이하는 정상 범위
8. OLAP 쿼리는 읽기 복제본에서 — Primary에서의 장기 쿼리는 Vacuum을 막는다
9. fillfactor로 HOT Update를 유도 — 예방이 치료보다 훨씬 저렴하다
10. pgstattuple로 정기적으로 측정 — 느낌이 아니라 데이터로 의사결정한다

참고 자료

• PostgreSQL 공식 문서 — Routine Vacuuming
• Microsoft Tech Blog — Managing bloat with pgstattuple
• Crunchy Data — Checking for PostgreSQL Bloat
• CYBERTEC — pg_squeeze
• AWS RDS — pg_repack 가이드
• postgres.ai — How to deal with bloat