PostgreSQL VACUUM
MVCC가 만든 옛 버전을 치워주는 VACUUM.
왜 VACUUM이 필요한가
PostgreSQL은 데이터를 8KB 페이지 단위로 디스크에서 읽고 쓴다. 한 행만 따로 가져오는 게 아니라 한 페이지를 통째로 메모리에 올린 뒤 그 안의 행을 들여다본다.
PostgreSQL은 UPDATE가 일어나면 기존 행을 덮어쓰지 않고 새 튜플을 추가한다. 옛 튜플은 xmax가 찍힌 채 같은 테이블에 그대로 남는다.
이게 MVCC가 락 없이 동시성을 만드는 비결이지만, 청소를 안 하면 부작용이 누적된다.
이 옛 튜플을 dead tuple, 그리고 dead tuple이 누적돼 테이블이 실제 살아있는 행 수보다 훨씬 큰 공간을 차지하는 현상을 bloat (팽창)라고 부른다.
bloat가 쌓이면 두 가지 비용이 발생한다.
- 디스크 공간 낭비: 살아있는 행 1만 건짜리 테이블이 dead tuple까지 합쳐 10만 건 분량의 페이지를 차지할 수 있다
- 조회 성능 저하: 인덱스 탐색 후 페이지를 읽었는데 그 안의 대부분이 dead tuple이면, 살아있는 행을 찾기까지 더 많은 페이지를 읽어야 한다
여기서 “페이지를 읽는다”는 한 번의 디스크 I/O를 뜻한다. 8KB 페이지 안에 살아있는 행이 1개뿐이고 dead가 99개라면, 그 1개를 얻기 위해 다른 페이지를 또 가져와야 하니 I/O 횟수가 늘어난다.
UPDATE를 반복하면 살아있는 행 수는 그대로지만 dead가 누적돼 페이지 수가 늘어난다 (bloat). VACUUM이 돌면 그 자리를 재사용 가능으로 표시하고, 이후 INSERT/UPDATE가 들어와 그 자리를 채우면서 비로소 디스크 점유가 회수된다.
VACUUM은 이 dead tuple을 정리하는 작업이다. 평소엔 백그라운드 데몬(autovacuum)이 자동으로 돌리지만 수동으로 실행할 수도 있다.
-- 단일 테이블
VACUUM orders;
-- DB 전체
VACUUM;VACUUM이 하는 일
VACUUM은 단순히 청소만 하지 않는다. 같은 작업 중에 네 가지를 한꺼번에 처리한다.
dead tuple 회수
페이지를 읽어 dead tuple이 차지하던 공간을 “재사용 가능”으로 표시한다.
“표시”란 페이지 안의 line pointer 상태를
LP_DEAD로 바꿔두는 작업이다. 데이터를 지우거나 페이지를 압축하지는 않고, 다음 INSERT/UPDATE가 그 자리를 덮어 써도 된다고 알려놓을 뿐이다.
일반 VACUUM은 페이지 안에서 행을 옮기거나 페이지를 합치지 않는다.
그래서 디스크 사용량은 즉시 줄지 않고, 새 INSERT나 UPDATE가 그 빈 자리를 재사용해야 비로소 회수 효과가 생긴다.
Visibility Map 갱신
Visibility Map(VM)은 각 페이지가 “모든 트랜잭션에 보이는 상태”인지 1비트로 기록하는 보조 자료구조다. VM이 켜진 페이지는 가시성 검사를 생략할 수 있어 Index-Only Scan이 가능해진다.
VACUUM이 페이지를 청소하면서 VM을 갱신한다. VACUUM이 자주 돌수록 Index-Only Scan이 잘 동작한다.
Free Space Map 갱신
Free Space Map(FSM)은 각 페이지에 얼마나 빈 공간이 있는지 기록한다. 새 INSERT가 들어올 때 어느 페이지에 넣을지 결정하는 정보다.
Transaction ID Freeze
뒤에서 다룰 wraparound 방지 작업이다. 오래된 튜플의 xmin을 “영원한 과거”로 표시한다.
VACUUM, VACUUM FULL, ANALYZE
세 명령은 자주 헷갈린다. 일하는 방식과 락이 완전히 다르다.
| 명령 | 락 | 효과 | When-to-use |
|---|---|---|---|
| VACUUM | SHARE UPDATE EXCLUSIVE | dead tuple 표시, FSM/VM 갱신, freeze | 일반적인 상황(autovacuum) |
| VACUUM FULL | ACCESS EXCLUSIVE | 테이블 새로 쓰기, 디스크 공간 실제 회수 | 점검 시간 |
| ANALYZE | SHARE UPDATE EXCLUSIVE | 통계만 갱신 (옵티마이저용) | 대량 변경 직후 |
VACUUM은 읽기, 쓰기를 막지 않는다. 같은 테이블의 일반 쿼리와 동시에 돈다.
VACUUM FULL은 다르다.
테이블 전체를 새 파일로 다시 쓰면서 ACCESS EXCLUSIVE 락을 잡는다. 그동안 그 테이블은 SELECT조차 못 한다.
운영 중에는 절대 일상적으로 쓰면 안 된다.
ANALYZE는 행을 건드리지 않는다. 통계만 다시 계산해 옵티마이저가 더 나은 실행 계획을 세우게 돕는다. 보통 autovacuum이 함께 처리한다.
autovacuum
직접 VACUUM 명령을 칠 일은 거의 없다. PostgreSQL은 autovacuum 이라는 백그라운드 데몬을 내장한다.
autovacuum은 주기적으로 깨어나 각 테이블의 dead tuple 수를 점검하고, 임계치를 넘으면 VACUUM(또는 ANALYZE)을 자동 실행한다.
임계치는 두 파라미터로 결정된다.
autovacuum_vacuum_threshold(기본 50): 최소 dead tuple 수. 작은 테이블이 너무 자주 돌지 않게 잡는 하한autovacuum_vacuum_scale_factor(기본 0.2): 전체 행 수에 대한 비율
dead tuple 수가 threshold + scale_factor × 전체 행 수를 넘으면 VACUUM이 시작된다.
기본값(
scale_factor = 0.2)은 전체 행의 20%가 dead가 되어야 트리거된다는 뜻이다. 1억 행짜리 테이블이라면 dead tuple 2천만 개가 쌓일 때까지 기다려야 하니 그 사이 bloat가 폭증한다.
큰 테이블은 scale_factor를 낮춰주는 것이 정석이다.
ALTER TABLE orders SET (autovacuum_vacuum_scale_factor = 0.05);쓰기가 많은 테이블에 0.05~0.1이 자주 쓰이는 값이다.
다른 핵심 튜닝 포인트.
autovacuum_naptime(기본 1분): 데몬이 다음 점검까지 자는 시간. 짧을수록 반응 빠름vacuum_cost_delay(기본 2ms): VACUUM 자체의 부하를 늦추는 sleep. 너무 늘리면 VACUUM이 따라가지 못함
Transaction ID Wraparound
PostgreSQL의 트랜잭션 ID(XID)는 32-bit다. 약 21억 트랜잭션이 지나면 한 바퀴 돈다. wrap된 XID는 옛 트랜잭션의 XID와 숫자 비교가 무너지면서 가시성 판단이 깨지고 데이터가 보이지 않게 된다.
PostgreSQL은 이를 막기 위해 freeze라는 메커니즘을 쓴다. 충분히 오래된 튜플의 xmin을 특별 값으로 바꿔 “비교 대상에서 영원히 제외”한다. freeze된 튜플은 wraparound와 무관하다.
freeze는 평소 autovacuum이 일반 VACUUM과 함께 처리하므로, 기본 설정에 정상 운영이라면 마주칠 일이 거의 없는 메커니즘이다.
다만 며칠~몇 주씩 살아있는 idle 트랜잭션이나 비활성 replication slot 같은 사고가 누적되면 freeze가 밀리기 시작한다. 대규모 운영 환경에서는 다음 쿼리로 wraparound age를 모니터링 항목에 포함해두는 것이 안전하다.
-- 데이터베이스별 남은 XID 여유 확인
SELECT datname, age(datfrozenxid)
FROM pg_database
ORDER BY age(datfrozenxid) DESC;age가 autovacuum_freeze_max_age(기본 2억) 근처면 freeze가 밀리고 있다는 신호다.
FILLFACTOR와 HOT update
VACUUM 부하를 사전에 줄이는 기법으로 HOT(Heap-Only Tuple) update 가 있다. 갱신되는 컬럼이 인덱스에 없고 새 튜플이 같은 페이지에 들어갈 자리가 있으면, PostgreSQL은 인덱스를 건드리지 않고 같은 페이지 안에서 갱신을 끝낸다. dead tuple 회수와 인덱스 정리 비용이 동시에 줄어든다.
같은 페이지에 자리를 두려면 페이지를 꽉 채우지 않고 여유를 둬야 한다. fillfactor 옵션이 그 비율을 정한다. 기본값 100은 읽기 위주 테이블에 적합하고, 갱신이 잦은 테이블은 80~90이 자주 쓰인다.
ALTER TABLE accounts SET (fillfactor = 80);
페이지에 여유 공간이 있으면 같은 페이지 안에서 갱신이 끝나 인덱스 정리와 dead tuple 회수 부담이 모두 줄어든다.
MySQL InnoDB의 Purge와 비교
같은 MVCC 계열이지만 옛 버전을 어디에 두느냐가 다르고, 그래서 청소 모델도 갈라진다.
PostgreSQL은 옛 튜플을 본 테이블에 그대로 둔 채 autovacuum이 주기적으로 정리한다. 본 테이블에 dead tuple이 누적되니 SELECT 성능에까지 영향이 가고, 운영자가 scale_factor·freeze·cost 같은 파라미터를 직접 튜닝해야 한다. XID가 32-bit라 wraparound 위험도 있다.
MySQL InnoDB는 옛 버전을 본 테이블이 아니라 별도의 Undo Log에 보관하고, 백그라운드 Purge Thread가 자동으로 정리한다. 본 테이블이 부풀지 않는 대신 Undo가 부풀어, 긴 트랜잭션이 옛 버전 추적 비용과 디스크 점유를 동시에 끌어올린다. 사용자가 만질 만한 옵션은 innodb_purge_threads 정도라 거의 보이지 않게 자동 처리된다.
운영 입장에서 PostgreSQL은 “VACUUM과 친해져야 하는 DB”이고, MySQL은 “긴 트랜잭션이 Undo를 폭증시키는 사고를 조심해야 하는 DB”다. 양쪽 모두 옛 버전 청소 비용은 존재하지만 어디서 그 비용이 드러나느냐가 다르다.
Things to note
Long-Running Transaction
PostgreSQL은 어떤 트랜잭션이 살아 있는 한, 그 트랜잭션의 스냅샷에서 보일 수 있는 옛 튜플을 VACUUM이 지우지 못한다.
SELECT pid, age(backend_xid), age(backend_xmin), state, query
FROM pg_stat_activity
WHERE backend_xid IS NOT NULL OR backend_xmin IS NOT NULL
ORDER BY age(backend_xmin) DESC NULLS LAST;backend_xmin이 큰 세션이 있으면 그 세션이 끝날 때까지 dead tuple이 누적된다. 며칠씩 살아있는 idle 트랜잭션 하나가 전체 테이블의 bloat를 만드는 사고가 흔하다.
autovacuum
운영 중에는 다음 두 컬럼을 항상 본다.
SELECT relname, n_live_tup, n_dead_tup,
n_dead_tup::float / NULLIF(n_live_tup, 0) AS dead_ratio,
last_autovacuum, last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;dead_ratio가 0.2 넘는 테이블이 자주 보이면 autovacuum이 못 따라가고 있다는 뜻이다. scale_factor를 낮추거나, cost_delay를 줄이거나, 워커 수를 늘려야 한다.
VACUUM FULL
bloat가 심각해도 VACUUM FULL은 마지막 수단이다. ACCESS EXCLUSIVE 락이라 운영 중에는 그 테이블이 통째로 막힌다.
대안으로 자주 쓰이는 게 pg_repack 익스텐션이다. 새 테이블에 데이터를 옮긴 뒤 swap 하는 방식이라, 짧은 락만 잡고 끝난다.