PostgreSQL VACUUM 튜닝: 자동 VACUUM 설정 심층 분석

발행: 2주 전 (2026년 5월 24일 PM 06:37 GMT+9)

10 분 소요

Source: Dev.to

작성자 주: 이 글은 실제 운영 중 발생한 사고 조사와 기본 문서를 다시 살펴보면서 얻은 기술적 인사이트를 기록한 것입니다. 수정 작업은 동료가 수행했으며, 이 글은 올바른 문서 검토 과정을 통해 얻은 학습 과정을 담고 있습니다.

발생 상황

Aurora PostgreSQL 프로덕션 클러스터에서 3~~4시간 동안 CPU 사용률이 85~~90% 수준으로 지속되었습니다. CloudWatch Performance Insights는 주요 대기 이벤트가 CPU임을 (I/O나 락 경쟁이 아님) 확인했으며, 가장 많은 CPU를 소모한 작업은 두 개의 대형 테이블에 대해 실행되는 autovacuum VACUUM 프로세스였습니다.

관찰된 상태

Table A (593 GB, 623M 행): 1억 2400만개의 dead tuple (dead 비율 16.6%)
Table B (465M 행): 7400만개의 dead tuple (dead 비율 13.7%)
Autovacuum 워커: 동시에 2개 실행
CPU 사용률: 85–90%
Autovacuum 빈도: 4–6시간마다 한 번씩 대규모 vacuum 작업 수행
테이블 상태: 인스턴스 생성 이후 수동 vacuum 수행 이력 없음

근본 원인
Autovacuum 임계값이 시스템 기본값으로 설정돼 있었으며, 이는 2~3시간마다 대량 업데이트가 발생하는 고 churn 테이블에 적합하지 않았습니다.

PostgreSQL과 Oracle의 동시 접근 방식 차이 이해하기

Oracle: Undo 테이블스페이스 기반 MVCC

업데이트 동작: 행이 업데이트될 때, 기존 버전은 테이블이 아닌 undo 테이블스페이스에 기록됩니다.
Undo 보존: Oracle의 Automatic Undo Retention Management (AUM)는 UNDO_RETENTION 파라미터와 사용 가능한 테이블스페이스 용량을 기준으로 undo 영역을 원형 버퍼처럼 자동 재활용합니다.
공간 회수: (a) 보존 기간이 만료되거나 (b) 테이블스페이스 압박이 발생해 오래된 undo 데이터를 롤백해야 할 때 자동으로 회수됩니다. DBA는 초기 undo 테이블스페이스 용량만 충분히 할당하면 됩니다.
핵심 특징: DBA는 한 번만 보존 기간과 테이블스페이스 크기를 설정하고, 이후 Oracle 백그라운드 프로세스가 undo 수명 주기를 자동으로 관리합니다.

[IMAGE 2: Oracle vs PostgreSQL MVCC Architecture Diagram]

PostgreSQL: 힙 기반 MVCC와 명시적 VACUUM

업데이트 동작: 행이 업데이트될 때 새로운 버전이 동일 테이블에 삽입되고, 기존 버전은 “dead” 상태로 표시되지만 물리적으로는 그대로 남아 있습니다.
공간 회수: VACUUM이 테이블을 스캔해 dead tuple을 찾아 그 공간을 재사용 가능하게 표시해야 합니다. dead tuple은 자동으로 제거되지 않습니다.
Autovacuum 트리거: Autovacuum은 튜닝 가능한 임계값을 기준으로 언제 vacuum을 실행할지 결정하는 백그라운드 프로세스입니다. Oracle의 자동 undo 재활용과 달리, PostgreSQL에서는 vacuum이 언제 트리거될지 명시적으로 설정해야 합니다.
핵심 특징: DBA는 테이블의 churn 패턴에 맞춰 VACUUM 파라미터를 적극적으로 튜닝해야 하며, “설정하고 잊어버리는” 메커니즘은 없습니다.
시사점: Oracle에서는 업데이트가 많을수록 undo가 늘어나고 AUM이 이를 처리합니다. PostgreSQL에서는 같은 업데이트 양이 dead tuple을 늘리며, autovacuum 임계값이 보수적이면 dead tuple이 누적돼 CPU 스파이크를 일으키는 시점에야 vacuum이 실행됩니다.

Autovacuum 트리거 계산식

Autovacuum이 실행될지 판단할 때 각 테이블에 대해 다음 공식을 사용합니다.

VACUUM_TRIGGER_THRESHOLD = autovacuum_vacuum_threshold +
                           (autovacuum_vacuum_scale_factor × n_live_tup)

autovacuum_vacuum_threshold : 최소 dead tuple 수 (기본값 50)
autovacuum_vacuum_scale_factor : 테이블 크기의 비율 (기본값 0.1 = 10%)
n_live_tup : 현재 살아있는 튜플 수

클러스터의 시스템 기본값

autovacuum_vacuum_threshold = 50
autovacuum_vacuum_scale_factor = 0.1

Table A (725M 행) 계산 예시

THRESHOLD = 50 + (0.1 × 725,000,000)
          = 50 + 72,500,000
          = 7,250만 dead tuple

해석: Table A에서는 7,250만 개의 dead tuple이 쌓여야 autovacuum이 트리거됩니다. 이것이 바로 잘못된 설정이었습니다. 실제로 Table A는 vacuum이 완료되기 전까지 1억 2400만 개의 dead tuple을 축적했으며, 이는 임계값을 훨씬 초과한 상태였습니다. 즉, autovacuum이 이미 라이프사이클 초기에 트리거됐지만, 높은 임계값 때문에 지속적인 작업 부하와 함께 계속 실행되었습니다.

데이터 로더 패턴과 dead tuple 급증

데이터 로더는 2~3시간마다 4개의 병렬 워커를 사용해 다음과 같은 COALESCE 기반 MERGE 쿼리를 실행했습니다.

UPDATE table_a t SET 
  column_1 = COALESCE(s.column_1, t.column_1),
  column_2 = COALESCE(s.column_2, t.column_2),
  column_3 = COALESCE(s.column_3, t.column_3)
FROM staging_table s
WHERE t.id = s.id;

이 패턴은 실제 값이 변하든 안 변하든 접근된 모든 행마다 dead tuple을 하나씩 생성합니다. 수백만 행을 2~3시간 동안 처리하면서 dead tuple 생성 속도가 높은 임계값 때문에 autovacuum이 따라잡지 못했습니다.

충돌 요인

대량 UPDATE에 의한 높은 dead tuple 생성률
기본값에 맞춰 조정된 autovacuum 임계값 (중간 규모 테이블에 적합)
이 워크로드에 맞는 테이블 수준 오버라이드 부재

결과: dead tuple이 테이블 크기의 16.6%까지 누적된 뒤에야 안정화되었습니다.

진단을 위한 3가지 쿼리

1️⃣ 현재 dead tuple 현황

SELECT 
  relname, 
  n_live_tup,
  n_dead_tup,
  ROUND(100.0 * n_dead_tup / (n_live_tup + n_dead_tup), 2) AS dead_ratio_pct,
  last_autovacuum
FROM pg_stat_user_tables
WHERE relname IN ('table_a', 'table_b')
ORDER BY n_dead_tup DESC;

결과

Table A: 623M live, 124M dead (16.6%)
Table B: 465M live, 74M dead (13.7%)

2️⃣ 현재 실행 중인 autovacuum 프로세스

SELECT 
  pid, 
  query, 
  query_start,
  EXTRACT(EPOCH FROM (NOW() - query_start))::INT AS runtime_seconds
FROM pg_stat_activity
WHERE query LIKE '%VACUUM%' 
  AND query NOT LIKE '%pg_stat%';

결과: 두 개의 autovacuum 워커가 동시에 실행 중이며, 각각 55분 이상, 3분 이상 실행되고 있었습니다.

3️⃣ 누적 churn 분석

SELECT 
  relname,
  n_live_tup,
  n_dead_tup,
  n_tup_upd + n_tup_del AS total_modifications,
  ROUND(100.0 * (n_tup_upd + n_tup_del) / n_live_tup, 1) AS churn_ratio_pct
FROM pg_stat_user_tables
WHERE relname IN ('table_a', 'table_b');

결과

Table A: 160억 건의 총 수정 (725M 행 기준 2203% 누적 churn)
Table B: 119억 건의 총 수정 (465M 행 기준 2566% 누적 churn)

해석: 인스턴스 생성 이후 누적된 통계이며, 2200% 이상의 비율은 평균적으로 각 행이 약 22번씩 업데이트되었음을 의미합니다.

공식 기반 임계값 재설정

임시 방편이 아닌, 공식에 기반한 접근법으로 적절한 임계값을 산출했습니다. 동료는 유지보수 메모리 할당 및 임계값 계산에 대한 교과서 공식을 참고해 시스템 기본값이 이와 같은 고 churn 테이블에 부적절함을 확인했습니다.

적용된 테이블 수준 변경

ALTER TABLE table_a SET (
  autovacuum_vacuum_scale_factor = 0.01,
  autovacuum_vacuum_threshold = 10000,
  autovacuum_analyze_scale_factor = 0.005,
  autovacuum_analyze_threshold = 5000,
  autovacuum_vacuum_cost_delay = 2,
  autovacuum_vacuum_cost_limit = 5000
);

ALTER TABLE table_b SET (
  autovacuum_vacuum_scale_factor = 0.01,
  autovacuum_vacuum_threshold = 10000,
  autovacuum_analyze_scale_factor = 0.005,
  autovacuum_analyze_threshold = 5000,
  autovacuum_vacuum_cost_delay