목차
- autovacuum이 뭐고 왜 점점 느려졌나
- 한 달 동안 인덱스만 들여다본 시간
- autovacuum 동작 원리 — 늦게 알게 된 것들
- 파라미터를 어떻게 잡았나 — 시간순
- 같이 봤어야 했는데 늦게 본 것들
- 언제 튜닝하고 언제 두는가 — 판단 기준
PostgreSQL autovacuum 튜닝은 dead tuple이 쌓여 쿼리가 점점 느려질 때 가장 먼저 손대야 하는 영역이다. 5천만 행짜리 주문 테이블의 SELECT가 평소 50ms 수준에서 슬금슬금 2초 가까이로 늘어나는 걸 3개월 동안 추적했고, 결국 인덱스가 아니라 autovacuum 파라미터가 범인이었다.
이 글은 그 3개월을 시간순으로 풀어본다. 처음에 잘못 짚었던 가설, 중간에 IO를 폭주시킨 설정, 마지막에 안정된 값까지. PostgreSQL 14 기준이다.
autovacuum이 뭐고 왜 점점 느려졌나
즉, PostgreSQL은 UPDATE나 DELETE를 해도 기존 row를 그 자리에서 지우지 않는다. MVCC 구조 때문에 옛 버전(dead tuple)을 그대로 두고 새 버전을 옆에 적는다. 이 dead tuple을 나중에 회수하는 게 VACUUM이고, 그걸 자동으로 돌려주는 백그라운드 프로세스가 autovacuum이다.
문제는 dead tuple이 회수되지 않으면 같은 쿼리를 위해 더 많은 페이지를 읽어야 한다는 점이다. 인덱스 스캔도 마찬가지로 더 많은 힙 페이지를 들춰봐야 한다. 결과적으로 데이터가 늘지 않아도 쿼리 시간이 늘어난다.
특히, 운영 중이던 서비스의 orders 테이블은 약 5천만 행 수준이었다. 하루에 UPDATE가 200만 건쯤 발생하는 구조였고, 어느 시점부터 P95 쿼리 지연이 슬금슬금 올라왔다.
-- 의심이 든 시점에 가장 먼저 본 쿼리
SELECT relname, n_live_tup, n_dead_tup,
last_vacuum, last_autovacuum
FROM pg_stat_user_tables
WHERE relname = 'orders';
n_dead_tup이 약 1,500만이었다. 전체 row의 30%가 dead 상태였다는 뜻이다. 그제서야 autovacuum이 의심 후보에 들어왔다.
한 달 동안 인덱스만 들여다본 시간
부끄러운 얘기지만, 첫 한 달은 인덱스만 들여다봤다. EXPLAIN ANALYZE를 돌려보면 Seq Scan이 가끔 잡혔고, 인덱스를 하나 더 추가하면 잠시 빨라졌다가 며칠 후 다시 느려졌다. 같은 패턴이 두 번 반복되고 나서야 인덱스가 본질이 아닌 것 같다는 의심이 들었다.
EXPLAIN ANALYZE만 보면 안 보이는 것
EXPLAIN ANALYZE 출력에는 Buffers: shared hit=... read=...만 보인다. 이 페이지가 살아있는 row를 담고 있는지 dead tuple만 가득한지는 직접 알려주지 않는다. 같은 쿼리의 Buffers 수치가 시간이 지나며 점점 늘어났는데, 그 이유를 한참 뒤에야 이해했다.
dead tuple과 쿼리 시간의 관계
테스트 환경에서 일부러 dead tuple을 쌓아두고 같은 쿼리를 돌려봤다. 같은 인덱스, 같은 조건, 같은 행 수를 반환하는데 응답 시간이 분명히 달랐다. 페이지에 살아있는 row 비율이 떨어지면 같은 결과를 위해 더 많은 블록을 읽어야 한다는 걸 그제서야 체감했다.
autovacuum 동작 원리 — 늦게 알게 된 것들
파라미터를 만지기 전에 동작 모델부터 정리해야 했다. 공식 문서 Routine Vacuuming을 읽고 또 읽었다.
trigger 조건: scale_factor와 threshold
autovacuum이 한 테이블에 손을 대는 조건은 단순하다. dead tuple 수가 아래 값을 넘으면 trigger된다.
threshold + scale_factor × n_live_tup
기본값은 autovacuum_vacuum_threshold = 50, autovacuum_vacuum_scale_factor = 0.2다. 즉 5천만 행 테이블에서는 50 + 0.2 × 50,000,000 = 약 1천만. dead tuple이 1천만 넘게 쌓이고 나서야 autovacuum이 들어온다는 뜻이다.
또한, 작은 테이블에는 합리적이지만 대형 테이블에는 너무 늦다. 그동안 쿼리는 점점 느려진다.
cost-based delay: nap time과 cost_limit
한편, trigger되어 들어와도 autovacuum은 한 번에 다 끝내지 않는다. IO 부담을 피하려고 cost-based delay 방식으로 일정량 처리한 뒤 잠시 쉰다.
autovacuum_vacuum_cost_limit: 한 사이클에 누적 가능한 비용 한계 (기본 -1, 즉vacuum_cost_limit값 200을 따라감)autovacuum_vacuum_cost_delay: 한계에 도달하면 쉬는 시간 (PostgreSQL 12부터 기본 2ms)autovacuum_naptime: 다음 데이터베이스를 검사하기까지 쉬는 시간 (기본 1분)
즉, 기본값으로 두면 큰 테이블에서는 vacuum 한 번이 몇 시간씩 걸린다. 그 사이에도 UPDATE는 계속 들어오므로 dead tuple 증가 속도가 회수 속도를 따라잡지 못하는 상황이 생긴다.
파라미터를 어떻게 잡았나 — 시간순
1차 시도: scale_factor만 낮춤
또한, 가장 안전해 보이는 시도부터 했다. trigger를 더 자주 걸도록 orders 테이블에만 다음을 적용했다.
ALTER TABLE orders SET (
autovacuum_vacuum_scale_factor = 0.05,
autovacuum_vacuum_threshold = 10000
);
물론, 이제 dead tuple이 약 260만(50,000,000 × 0.05 + 10,000)을 넘으면 trigger된다. 며칠 지켜보니 trigger는 더 자주 됐는데 한 번 시작한 vacuum이 끝나기 전에 다음 trigger 조건이 또 충족되는 상황이 벌어졌다. 한 사이클 안에 처리 가능한 양 자체가 작았기 때문이다.
2차 시도: cost_limit를 한 번에 올림
이때 무리수를 뒀다. autovacuum_vacuum_cost_limit을 200에서 5000으로 한 번에 올렸다. 결과는 좋지 않았다. 새벽도 아닌 시간대에 디스크 IO 사용량이 평소의 4배까지 치솟았고, 모니터링 알람이 울렸다. autovacuum은 더 빨리 끝났지만 서비스 응답 시간 자체가 출렁였다.
이처럼, 이 구간에서 배운 게 컸다. cost_limit은 자원을 강제로 더 쓰겠다는 선언이라 디스크 IO 여유가 있는지를 먼저 확인하지 않으면 위험하다.
3차 시도: 점진적 조정 + worker 수 분리
결국 아래 조합으로 안정됐다.
| 파라미터 | 기본값 | 최종값 | 의도 |
|---|---|---|---|
| autovacuum_vacuum_scale_factor (orders) | 0.2 | 0.05 | 큰 테이블에 trigger 빨리 걸기 |
| autovacuum_vacuum_threshold (orders) | 50 | 10000 | 너무 잦은 trigger 방지 |
| autovacuum_vacuum_cost_limit | 200 | 2000 | 처리량 늘리되 IO 여유 안에서 |
| autovacuum_vacuum_cost_delay | 2ms | 2ms | 변경 없음 |
| autovacuum_max_workers | 3 | 5 | 다른 테이블이 영향 안 받게 |
| autovacuum_naptime | 60s | 30s | 빠르게 다음 사이클 진입 |
cost_limit을 5000이 아니라 2000으로 절반 이하로 낮춘 게 핵심이었다. 처리량은 분명히 늘었지만 IO는 평소의 1.5배 수준에서 멈췄다.
-- 최종 적용 후 확인
SELECT relname, n_dead_tup, n_live_tup,
round(n_dead_tup::numeric / NULLIF(n_live_tup,0) * 100, 2) AS dead_pct
FROM pg_stat_user_tables
WHERE relname = 'orders';
dead tuple 비율이 30%대에서 4%대로 떨어졌다. P95 쿼리 지연은 원래 수준으로 돌아왔다.
같이 봤어야 했는데 늦게 본 것들
세 달이 끝나갈 무렵 알게 된 것 두 가지가 있다.
첫째, pg_stat_progress_vacuum이라는 뷰가 있다. autovacuum이 지금 어디까지 진행했는지, 어느 단계(heap scan, vacuuming indexes, vacuuming heap, cleaning up)에서 멈춰있는지를 실시간으로 볼 수 있다. 이걸 일찍 봤으면 1차 시도에서 "왜 trigger는 되는데 안 끝나지" 같은 의문을 빨리 풀었을 것이다.
한편, 둘째, HOT(Heap-Only Tuple) update가 가능한 컬럼은 인덱싱하지 않는 쪽이 dead tuple 누적 속도를 크게 낮춘다. 자주 갱신되는 컬럼에 굳이 인덱스를 걸어둔 게 있었는데, 이걸 제거한 게 파라미터 튜닝 못지않게 효과가 있었다. (개인적으로 이 부분이 가장 의외였다.)
언제 튜닝하고 언제 두는가 — 판단 기준
이번 회고에서 끌어낸 실용 기준이다.
- 테이블 행 수가 천만 미만이라면 기본값으로 두는 게 맞다. trigger가 늦지 않고 cost_limit도 충분하다.
- 행 수가 천만을 넘고 UPDATE/DELETE 비율이 일 평균 5% 이상이라면
autovacuum_vacuum_scale_factor를 테이블 단위로 0.05~0.1로 낮춰라. 전역 설정 말고ALTER TABLE ... SET (...)로 부분 적용하는 쪽이 안전하다. autovacuum_vacuum_cost_limit은 한 번에 두 배 이상 올리지 마라. 200 → 500 → 1000 → 2000 식으로 단계적으로 올리며 IO 모니터링을 함께 봐라.- dead tuple 비율이 늘 10%를 넘어 있다면 파라미터보다 먼저 HOT update가 가능한지 점검하라. 자주 갱신되는 컬럼의 인덱스가 진짜로 필요한지 다시 본다.
- 대형 테이블은
pg_stat_progress_vacuum을 모니터링 대시보드에 올려둬라. 사후 분석이 사전 감지보다 항상 비싸다.
지금 운영 중인 클러스터는 위 설정으로 한 달 넘게 안정 상태다.
관련 글
- PostgreSQL 커넥션 풀 설정 회고: PgBouncer 3개월 운영 기록 – PostgreSQL 커넥션 풀 설정으로 PgBouncer를 도입했다가 Transaction 모드에서 prepared statement 충돌…
- Redis 캐싱 전략 실무 — Cache-Aside, Write-Through, TTL 설계까지 – Redis를 캐시로 도입할 때 흔히 저지르는 실수와 Cache-Aside, Write-Through, TTL 설계 기준을 실무 관점에서 정…
- PostgreSQL 인덱스 최적화 실무 — 인덱스 걸었는데 왜 느린지 모르겠다면 – 인덱스 걸면 빨라진다고들 하는데, 실제로는 그렇지 않은 경우가 많다. EXPLAIN ANALYZE 해석부터 복합 인덱스 컬럼 순서, par…