[Aurora Postgresql] VACUUM 정리

1. VACUUM의 필요성

PostgreSQL에서 VACUUM이 필요한 이유는 크게 두 가지입니다:

필요성	설명	중요도
MVCC 구조로 인한 Dead Tuple 제거	UPDATE/DELETE 시 이전 버전이 즉시 삭제되지 않고 dead tuple로 남아있어 공간을 차지하므로 이를 정리	높음
Transaction ID 관리	PostgreSQL의 txid는 32비트로 제한되어 있어 wraparound 방지를 위해 오래된 txid를 freeze 처리	매우 높음

 

MVCC와 Dead Tuple

• PostgreSQL은 Multiversion Concurrency Control (MVCC) 구조 사용
• UPDATE/DELETE 시 기존 행을 즉시 삭제하지 않고 새로운 버전 생성
• 기존 버전은 다른 트랜잭션이 참조할 수 있도록 dead tuple로 보관
• 모든 트랜잭션이 완료되면 VACUUM으로 dead tuple 제거 가능

Transaction ID Wraparound

• PostgreSQL의 txid(트랜잭션 ID)는 32비트 (약 21억개)
• VACUUM이 오래된 txid를 freeze 처리하여 wraparound 방지
• freeze 처리란 txid를 frozen XID(XID=2) 라는 값으로 변경해주어 트랜잭션 겹침 현상을 막아주는 처리
• vacuum_freeze_min_age 설정보다 큰 age의 tuple에 대하여 처리됩니다.

2. Auto VACUUM 트리거 조건

Auto VACUUM은 다음 세 가지 조건 중 하나가 만족되면 실행됩니다:

 

2.1 입력 수에 따른 VACUUM

입력 튜플 수 > autovacuum_vacuum_insert_threshold + (autovacuum_vacuum_insert_scale_factor × 테이블의 총 튜플 수)

매개변수	기본값	설명
autovacuum_vacuum_insert_threshold	1000	삽입 기반 vacuum의 기본 임계값
autovacuum_vacuum_insert_scale_factor	0.2	테이블 크기에 따른 삽입 비율 임계값

 

2.2 Dead Tuple 수에 따른 VACUUM

Dead tuple 수 > autovacuum_vacuum_threshold + (autovacuum_vacuum_scale_factor × 테이블의 총 튜플 수)

매개변수	기본값	설명
autovacuum_vacuum_threshold	50	Dead tuple 기반 vacuum의 기본 임계값
autovacuum_vacuum_scale_factor	0.1	테이블 크기에 따른 dead tuple 비율 임계값

 

2.3 autovacuum_freeze_max_age로 인한 VACUUM

테이블 나이 age(relfrozenxid) ≥ min(vacuum_freeze_table_age, autovacuum_freeze_max_age * 0.95)

※ age 함수는 현재 txid 와 변수간의 차이를 의미 합니다. age(relfrozenxid) = txid_current(가장 최신의 txid) - relfrozenxid (테이블의 가장 오래된 txid)

매개변수	기본값	설명
autovacuum_freeze_max_age	200,000,000	Freeze vacuum 트리거 임계값

 

트리거 조건 예시 

테이블 크기	Dead Tuple 임계값	삽입 임계값	Freeze 임계값
1,000행	50 + (0.1 × 1,000) = 150개	1,000 + (0.2 × 1,000) = 1,200개	200M txid
1,000,000행	50 + (0.1 × 1M) = 100,050개	1,000 + (0.2 × 1M) = 200,001개	200M txid

 

3. VACUUM의 상황에 따른 구분

3.1 수행자에 따른 구분

 

Auto VACUUM vs VACUUM

구분	Auto VACUUM	VACUUM
실행 주체	PostgreSQL 내장 daemon	DBA 또는 스케줄러
실행 조건	설정된 임계값 도달 시 자동 실행	수동 명령어 실행
장점	자동화, 실시간 대응	세밀한 제어 가능
단점	설정 미스로 인한 문제 가능	모니터링 필요

 

3.2 VACUUM 대상에 따른 구분

Plain(Lazy) VACUUM vs Aggressive(Eager) VACUUM

구분	Lazy Mode VACUUM	Aggressive VACUUM
실행 조건	Dead tuple 임계값 도달 시	relfrozenxid > vacuum_freeze_table_age 일 때
스캔 범위	VM의 0번 비트 (ALL_VISIBLE) 이 0인 페이지	VM의 1번 (ALL Frozen)이 0인 페이지
성능 영향	상대적으로 적음	더 큰 성능 영향

 

Lazy Mode VM의 1번 비트가 0인 블록에 대하여 (OldestXmin = 50,002,500 freezeLimitx_txid = 2500) 2500보다 작은 t_xmin의 경우 frozen xid가 됨

Eager Mode VM의 2번 비트가 0인 블록에 대하여 (OldestXmin = 150,002,000 freezeLimitx_txid = 100,002,000) 100,002,000 보다 작은 t_xmin에 대하여 frozen xid가 됨

 

※vacuum_freeze_min_age 의 기본값은 50,000,000 으로 Aggressive Vacuum 이후 일반적으로 테이블의 AGE는 50,000,001 로 변경 됨

※Manual Vacuum 중 FREEZE 옵션을 추가할 경우 vacuum_freeze_table_age 을 0으로 변경하여 VACUUM이 발생 → 테이블 AGE가 1로 변경 됨

※Aggresive Vaccum 으로 인한 영향도가 높은 사례

• 단일 테이블에 입력만 다수 발생하는 경우 → Vaccum의 경우 트리거를 조절하여 영향도 낮게 처리가 가능하지만 이럴 경우 VM의 0번 비트만 변경되고 1번 비트는 전부 0으로 남게 됩니다. 즉 VM의 0번 비트가 계속 증가하게 되며 Aggressive Vaccum이 될 때 해당 페이지가 모두 대상이 되기 때문에 긴 시간 많은 페이지를 대상으로 VACUUM이 동작하게 됩니다. 이를 개선하기 위해선 1) 주기적으로 Vacuum Freeze 명령어를 통해 수동 처리 또는 2)파티셔닝 테이블 같은 테이블 분할 기술을 통하여 대상을 조절할 수 있습니다

 

--Auto Aggressive Vacuum Log
--1번 로그
automatic aggressive vacuum to prevent wraparound of table "TABLEB": index scans: 0
--2번 로그
automatic aggressive vacuum of table "public.TABLEA": index scans: 1

--aggressive 조건
Min(vacuum_freeze_table_age, autovacuum_freeze_max_age(클러스터 파라미터) * 0.95) < age(relfrozenxid)

--to prevent wraparound 로깅 조건`
is_wraparound = true -> autovacuum_freeze_max_age(테이블 또는 클러스터 파라미터) < age(relfrozenxid)

- 1번 로그 DB 설정
autovacuum_freeze_max_age (db config 설정) = 100,000,000
vacuum_freeze_table_age = 150,000,000

-- 2번 로그 DB 설정
autovacuum_freeze_max_age (클러스터 파라미터 설정) = 200,000,000
vacuum_freeze_table_age = 150,000,000


--VACUUM 트리거 확인 쿼리 및 결과
--튜플 정보 조회
SELECT
    n.nspname AS schema_name,  --스키마명
    c.relname AS table_name,   -- 테이블명
    c.relpages  as table_pages,  --페이지 수
seq_scan, --seq scan 발생 수
idx_scan, -- index scan 발생 수
      pg_stat_get_live_tuples(c.oid) + pg_stat_get_dead_tuples(c.oid) as total_tuple, -- 전체 튜플 수
    pg_stat_get_live_tuples(c.oid) AS live_tuple, -- live 튜플 수
    pg_stat_get_dead_tuples(c.oid) AS dead_tupple, -- dead 튜플 수
    s.n_mod_since_analyze, --마지막 통계 수집 이후 변경된 데이터 양
    s.n_ins_since_vacuum , --마지막 auto vauum 이후 입력된 데이터 양
    s.last_autovacuum AS Autovacuum, --마지막 auto vacuum 시점
    s.last_autoanalyze AS Autoanalyze, --마지막 통계 수집 시점
    pg_size_pretty(pg_total_relation_size(c.oid)) as total_relation_size, --인덱스+데이터 사이즈
    pg_size_pretty(pg_relation_size(c.oid)) as relation_size --데이터 사이즈
FROM pg_class AS c
JOIN pg_catalog.pg_namespace AS n ON n.oid = c.relnamespace
join pg_stat_user_tables as S on C.relname = S.relname
WHERE  c.relname NOT LIKE 'pg_%';

 schema_name |                       table_name                        | table_pages | seq_scan | idx_scan  | total_tuple | live_tuple | dead_tupple | n_mod_since_analyze | n_ins_since_vacuum |          autovacuum           |          autoanalyze          | total_relation_size | relation_size 
-------------+---------------------------------------------------------+-------------+----------+-----------+-------------+------------+-------------+---------------------+--------------------+-------------------------------+-------------------------------+---------------------+------------
 public      | TABLETABLETABLEAA                                       |      412744 |   127334 |   9763542 |     9038943 |    8548674 |      490269 |              158500 |             474633 | 2025-07-29 15:57:06.556846+00 | 2025-08-04 11:29:21.537066+00 | 6277 MB             | 3261 MB
 

--테이블 나이 조회
SELECT n.nspname as "Schema", c.relname as "Name"
, c.relfrozenxid --테이블에서 가장 오래된 txid
, age(c.relfrozenxid) --테이블의 이
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','')
AND n.nspname = 'public' AND n.nspname !~ '^pg_toast'
AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY c.relfrozenxid::text::bigint DESC;

 Schema |                          Name                           | relfrozenxid |   age    
--------+---------------------------------------------------------+--------------+----------
 public | TABLETABLETABL                                          |    201730148 | 50000043
 
--Aggressive Vacuum 모니터링을 위한 쿼리
--DB 별 수집
select round(age(datfrozenxid)::decimal / current_setting('vacuum_freeze_table_age')::decimal,2) as aggressive_vacuum_percent, datname 
FROM pg_database where datname not in ('rdsadmin', 'template0','template1', 'postgres') ;
 
aggressive_vacuum_percent |        datname         
---------------------------+------------------------
                      0.54 | DB1
                      0.06 | DB2
                      0.54 | DB3
                      0.06 | DB4

 

 

4. VACUUM 관련 파라미터

4-1. 병렬 처리 관련 파라미터

a.. 병렬 워커 수 제어

파라미터	기본값	설명	영향 범위
max_parallel_maintenance_workers	2	하나의 병렬 처리에 사용 가능한 프로세스 수	수동 VACUUM, CREATE INDEX 등
max_parallel_workers	GREATEST($DBInstanceVCPU/2,8)	전체 병렬 처리가 가능한 프로세스의 수	모든 병렬 작업
max_parallel_workers_per_gather	2	단일 쿼리에서 사용할 수 있는 최대 워커 수	DML 쿼리

 

4-2. Autovacuum 워커 관련 파라미터

a. 워커 수 및 실행 주기

파라미터	기본값	설명	권장 설정
autovacuum_max_workers	GREATEST({DBInstanceClassMemory/64371566592},3)	PostgreSQL 서버가 한번에 최대 돌 수 있는 AUTO VACUUM 수	CPU 코어 수에 따라 조정
autovacuum_naptime	5s	autovacuum 실행 사이의 최소 지연 시간	부하에 따라 조정

 

b. 워커 동작 방식

특징	설명
싱글 프로세스	각 autovacuum은 하나의 테이블만 처리
동시 실행	최대 autovacuum_max_workers개 테이블을 동시에 vacuum 처리

 

4-3. Vacuum Delay Cost 파라미터

a. Autovacuum Cost 제어

파라미터	기본값	설명
autovacuum_vacuum_cost_limit	GREATEST({log(DBInstanceClassMemory/21474836480)*600},200) {600}	AutoVacuum이 한 번 수행될 때마다 사용할 수 있는 코스트 수
autovacuum_vacuum_cost_delay	5ms	AutoVacuum이 cost_limit 을 다 사용하면 해당 시간동안 SLEEP

 

b. 수동 VACUUM Cost 제어

파라미터	기본값	설명
vacuum_cost_limit	200	수동 VACUUM이 한 번 수행될 때마다 사용할 수 있는 코스트 수
vacuum_cost_delay	0ms	수동 VACUUM이 cost_limit 을 다 사용하면 해당 시간동안 sleep

 

4-4. Vacuum 트리거 관련 파라미터

파라미터	기본값	설명
autovacuum_freeze_max_age	200000000	AUTO VACUUM이 동작하는 트리거
vacuum_freeze_table_age	150000000	Aggressive VACUUM이 동작하는 트리거
vacuum_freeze_min_age	50000000	VACUUM이 동작할 때 Freeze XID 로 전환하는 임게치
autovacuum_vacuum_threshold	50	AUTO VACUUM의 대상이 되는 최소 DEAD 튜플 수
autovacuum_vacuum_scale_factor	0.1	AUTO VACUUM의 대상이 되는 최소 DEAD 튜플 비율
autovacuum_vacuum_insert_threshold	1000	AUTO VACUUM의 대상이 되는 최소 튜플 입력 수
autovacuum_vacuum_insert_scale_factor	0.2	AUTO VACUUM의 대상이 되는 최소 튜플 입력 비율