PostgreSQL: Autovacuum 최적화

※ PostgreSQL: autovacuum optimization.

 

안녕하세요. 듀스트림입니다.

 

PostgreSQL을 사용하시는 분들은 VACUUM 때문에 조금씩은 머리가 아프실 거에요.

이번 포스팅은 유지관리에서 가장 신경써야할 것 중 하나인 autovacuum에 관한 내용입니다.

---

19.10. Automatic Vacuuming

 

오토배큠에 대한 PostgreSQL 공식 문서입니다.

 

네.. 저도 알아요. 여러분이 이런 걸 바라는 게 아니라는걸...

그럼 실제 운영환경에서 튜닝하는 주요 파라미터와 방법만 뽑아서 알아보겠습니다.

 

파라미터에 대한 자세한 설명은 공식 문서 참고 부탁드립니다.

---

1. 환경 및 워크로드 분석

• 데이터 변경 패턴 파악
→ 각 테이블의 INSERT, UPDATE, DELETE 빈도 및 변경량 분석.
→ 테이블별 변경률을 기반으로 autovacuum 트리거 기준(vacuum_scale_factor, vacuum_threshold)을 설정.

 

• Hot-standby를 사용하는 HA 환경 고려
→ standby 서버와의 충돌 최소화를 위해 primary의 VACUUM 부하 및 지연 시간 조절.
→ hot_standby_feedback과 연계하여 trade-off 분석.

---

2. 주요 autovacuum 파라미터

2.1. 기본 파라미터

• autovacuum_naptime
  • 목적: autovacuum 프로세스 간의 간격 조정.
  • 예시:

# postgresql.conf
autovacuum_naptime = 200s

 

• autovacuum_vacuum_scale_factor, autovacuum_vacuum_threshold
  • 목적: dead tuple의 비율이 기준에 도달할 때만 VACUUM 실행.
  • 예시:

# postgresql.conf
autovacuum_vacuum_threshold = 100000       # 기본값 50
autovacuum_vacuum_scale_factor = 0.3       # 기본값 0.2

-- 테이블 별 설정
ALTER TABLE table_name
  SET (autovacuum_vacuum_scale_factor = 0.2, autovacuum_vacuum_threshold = 100);

---

2.2. VACUUM 작업 부하 제어 파라미터

• autovacuum_vacuum_cost_delay
  • 목적: VACUUM 중 I/O 부하 감소.
    → VACUUM 작업이 일정 비용(autovacuum_vacuum_cost_limit에 도달) 후 설정 시간만큼 작업을 멈춤.
    → 이 휴식 시간 동안 다른 프로세스들이 I/O 자원을 사용할 수 있게 되어, 전체 시스템의 I/O 부하가 분산됨.
  • 예시:

# postgresql.conf에서 설정
autovacuum_vacuum_cost_delay = 50ms

 

• autovacuum_vacuum_cost_limit
  • 목적: 한 사이클에 수행할 비용 상한 설정.
    → 기본값인 -1로 설정하면  vacuum_cost_limit 파라미터 값이 사용됨.
  • 각 작업에 대한 기본 비용:
    → vacuum_cost_page_hit (기본값: 1): Shared buffer에서 페이지를 읽을 때의 비용 (메모리 I/O)
    → vacuum_cost_page_miss (기본값: 2): 디스크에서 페이지를 읽을 때의 비용 (디스크 I/O)
    → vacuum_cost_page_dirty (기본값: 20): 변경된 페이지(dirty block)를 디스크에 기록(flush)할 때의 비용
  • 예시:

# postgresql.conf
autovacuum_vacuum_cost_limit = 2000    # 기본값 -1(vacuum_cost_limit 기본값 200)

---

2.3. 트랜잭션 ID 및 인덱스 관리

• autovacuum_freeze_max_age & vacuum_freeze_min_age
  • 목적: 트랜잭션 ID wraparound 예방 및 인덱스 bloat 관리.
    → 업데이트가 잦은 환경에서는 freezing 작업을 빠르게 불러 wraparound를 방지하기 위해 기본값보다 수치를 낮추기도 함.
    → 너무 작은 값으로 설정하면 freezing 자주 발생, xmin이 FrozenXID로 변경되면서 테이블 및 인덱스가 자주 업데이트됨. (freezing 작업은 update와 같은 형태로 동작)
    → freezing이 일어나면 인덱스 엔트리도 해당 레코드의 새로운 위치에 맞게 수정되지만, 기존 인덱스 항목은 제거되지 않기 때문에 시간이 지나면서 불필요한 인덱스 엔트리(bloat)가 계속 증가.
  • 예시:

# postgresql.conf
autovacuum_freeze_max_age = 150000000     # 기본값 200000000
vacuum_freeze_min_age = 3000000           # 기본값 5000000

 

+ 자세한 파라미터들은 아래 포스팅 참고 부탁드립니다.

PostgreSQL: Autovacuum 파라미터

---

3. 테이블별 최적화 전략

3.1. 업데이트가 잦은 테이블

• 낮은 vacuum_scale_factor 및 vacuum_threshold 적용.

ALTER TABLE table_name
  SET (autovacuum_vacuum_scale_factor = 0, autovacuum_vacuum_threshold = 1000000);

---

3.2. 업데이트가 거의 없는 테이블

• 튜닝 불필요.

---

3.3. 적재된 데이터가 얼마 없는 신생 테이블

• autovacuum_vacuum_threshold 값을 높게 설정하여 불필요한 autovacuum 방지.

ALTER TABLE table_name
  SET (autovacuum_vacuum_threshold = 5000000);

---

3.4. 파티셔닝된 테이블

• 파티션 테이블 수가 많을 경우 autovacuum_max_workers 수를 높여 VACUUM 작업 병목 발생 방지.

# postgresql.conf
autovacuum_max_workers = 10    # 기본값 3

-- 서버 사양 및 PostgreSQL의 총 워커 프로세스 수를 고려하여 조정 필요.

 

• 각 파티션마다 독립적인 autovacuum 설정 적용.
• 필요에 따라 파티션 단위로 autovacuum_enabled를 조정하거나, 수동 VACUUM 계획 수립.

ALTER TABLE table_2025_01
  SET (autovacuum_vacuum_scale_factor = 0.3, autovacuum_vacuum_threshold = 50000);

ALTER TABLE table_2025_02
  SET (autovacuum_vacuum_scale_factor = 0.2, autovacuum_vacuum_threshold = 100000);

---

3.5. 특정 테이블 autovacuum 비활성화

• 장시간 실행되는 쿼리 등으로 인해 충돌 위험이 높은 경우 적용.

• 반드시 수동 VACUM 계획을 수립해야 함. (쉘을 활용한 VACUUM 배치 cron 등록 등)

ALTER TABLE table_name SET (autovacuum_enabled = false);

---

3.6. autovacuum에 의한 ANALYZE 비활성화 (우회적 방식)

• PostgreSQL은 autovacuum_analyze_enabled 옵션을 제공하지 않으므로, ANALYZE만 직접적으로 끄는 설정은 불가능.

• autovacuum_analyze_threshold와 autovacuum_analyze_scale_factor 값을 비정상적으로 높게 설정하여 ANALYZE가 사실상 트리거되지 않도록 구성 가능.

• 이 방식은 실행 계획 품질 저하의 위험이 있으므로 정기적인 수동 ANALYZE 수행이 필요.

ALTER TABLE table_name SET (autovacuum_analyze_threshold = 5000000000, autovacuum_analyze_scale_factor = 50.0);

---

3.7. 매일 정량적 INSERT가 일어나는 테이블

• 매일 일정량의 데이터가 INSERT되는 테이블은 변경 비율이 아닌 정량 기준으로 VACUUM 트리거를 설정하는 것이 유리함.
• autovacuum_vacuum_insert_scale_factor = 0으로 설정하고, 튜플 수 기준으로만 작동하도록 조정.
• INSERT량이 명확하다면 적절한 autovacuum_vacuum_insert_threshold 값으로 컨트롤 가능.

ALTER TABLE table_name SET (autovacuum_vacuum_insert_scale_factor = 0, autovacuum_vacuum_insert_threshold = 2000000);

---

3.8. 오래된 트랜잭션으로 인한 autovacuum 실패 방지

• autovacuum 시 idle in transaction이 해당 테이블을 점유하고 있으면 autovacuum이 정상적으로 수행되지 않음.

• idle_in_transaction_session_timeout을 AP 수준에 맞게 조정. (이 설정은 트랜잭션 누수 방지와 락 점유 방지를 위해서라도 대부분의 경우 설정 권장)

# postgresql.conf
idle_in_transaction_session_timeout = 15min    # 기본값 -1(무제한)

ALTER SYSTEM SET idle_in_transaction_session_timeout = '15min';

-- 데이터베이스 단위
ALTER DATABASE db_name SET idle_in_transaction_session_timeout = '15min';

-- 사용자 단위
ALTER ROLE user_name SET idle_in_transaction_session_timeout = '15min';

-- 설정 후 pg_ctl reload/SELECT pg_reload_conf(); 필요

---

4. Hot-standby를 사용하는 HA 환경에서의 고려사항

• hot_standby_feedback 활성화

→ standby에서 수행하는 장시간 쿼리 보호를 위해 사용할 수 있지만, primary의 VACUUM 지연에 따른 bloat, 복제 지연 등 모니터링 필수.

 

• VACUUM 부하 조절

→ primary에서의 autovacuum 부하로 인한 standby 충돌 최소화를 위해 cost delay/limit 값 조정.

→ standby 쿼리 모니터링과 replication lag 지표(pg_stat_replication, pg_stat_database_conflicts) 모니터링 필수.

---

5. 모니터링 전략

5.1. 주기적인 테이블 bloat 상태, autovacuum 동작 유무 모니터링

SELECT
    relname AS table_name,
    pg_total_relation_size(relid) / (1024*1024) ||' MB' AS table_size,
    n_live_tup AS live_tuples,
    n_dead_tup AS dead_tuples,
    CASE
        WHEN n_live_tup = 0 THEN 0.00
        ELSE round(n_dead_tup::numeric / n_live_tup::numeric * 100.0, 2)
    END AS dead_tuple_ratio,
    last_autovacuum AS last_autovacuum_time
FROM pg_stat_user_tables
ORDER BY dead_tuple_ratio, table_size DESC;

---

5.2. PostgreSQL 로그 파라미터 설정

• autovacuum 작업을 로그에 기록하여 정상 수행, 충돌 여부 등 이상 징후를 파악.

• 값을 0으로 하면 모든 autovacuum 작업을 로그에 기록.

# postgresql.conf
log_autovacuum_min_duration = 0  # 기본값 10min

---

주기적인 모니터링과 로그 분석을 통해, 전역 설정과 테이블별 설정을 조합하여 VACUUM 부하와 테이블 bloat, 트랜잭션 ID wraparound 문제를 균형 있게 관리하는 게 중요합니다.

 

Hot-standby를 사용하는 HA 환경에서는 standby와의 충돌을 최소화하기 위해 추가 설정(hot_standby_feedback, cost_delay 등 + max_standby_streaming_delay)과 모니터링이 필수입니다.

 

오늘은 여기까지~