PostgreSQL MVCC와 VACUUM: Index Bloat

PostgreSQL을 운영하다 보면 데이터 건수는 그대로인데 디스크 사용량만 기하급수적으로 늘어나는 현상을 마주하게 됩니다. 이는 PostgreSQL의 동시성 제어 아키텍처인 MVCC(Multi-Version Concurrency Control)의 특징 때문이라는 글을 읽었습니다.
그런데 문득 기존 데이터를 제자리에 덮어쓰지 않고 '죽은 튜플(Dead Tuple)'로 남겨두는 방식이 실제로 디스크 용량을 얼마나 부풀게(Bloat) 하는지, 그리고 이를 제어하는 VACUUM이 내부적으로 어떻게 동작하는지 궁금해졌습니다. 더 깊이 이해하고 싶어서 직접 10만 건의 데이터를 넣고 테스트를 진행하며 관련 내용을 찾아보았습니다.

인덱스 부풀기(Index Bloat)란?

• PostgreSQL에서 UPDATE가 발생하면 기존 행을 죽은 튜플로 남기고 새로운 행을 물리적인 디스크 공간에 새로 삽입합니다.
• 이때 테이블 크기만 커지는 것이 아니라, 해당 테이블에 걸려있는 인덱스는 B-Tree의 정렬 구조를 유지해야 합니다. 업데이트 시 기존 인덱스 엔트리는 Dead로 마킹되지만, 그 공간은 정렬 순서가 정확히 일치하는 데이터가 들어오기 전까지 재사용되지 못하고 자리를 차지합니다. 이것이 테이블보다 인덱스가 훨씬 가파르게 부풀어 오르는 원인입니다

1. 실험 환경 준비

• 정확한 관찰을 위해 10만 건의 데이터를 삽입하고, Autovacuum을 잠시 끕니다.

-- 1. 기존 테이블 삭제 및 재생성  
DROP TABLE IF EXISTS vacuum_demo;  

CREATE TABLE vacuum_demo (  
    id INT,  
    indexed_col TEXT,     -- 인덱스를 걸 컬럼  
    non_indexed_col TEXT  -- 인덱스가 없는 컬럼  
);  

-- 2. Autovacuum 비활성화 (실험 목적)  
ALTER TABLE vacuum_demo SET (autovacuum_enabled = false);  

-- 3. 데이터 10만 건 삽입  
INSERT INTO vacuum_demo (id, indexed_col, non_indexed_col)  
SELECT i, 'idx_data_' || i, 'non_idx_data_' || i  
FROM generate_series(1, 100000) s(i);  

-- 4. 인덱스 생성  
CREATE INDEX idx_vacuum_demo_col ON vacuum_demo(indexed_col);  

-- 5. 초기 상태(Baseline) 확인  
SELECT   
    pg_size_pretty(pg_table_size('vacuum_demo')) AS table_size,  
    pg_size_pretty(pg_indexes_size('vacuum_demo')) AS index_size;  

-- [실행 결과]  
-- table_size: 6704 kB   
-- index_size: 3104 kB  

 

2. Index Bloat (인덱스 부풀기) 유도

인덱스가 걸려 있는 컬럼을 업데이트해 봅니다.

-- 6. 인덱스가 걸린 컬럼을 3번 연속 업데이트 (총 30만 번의 UPDATE 발생)  
UPDATE vacuum_demo SET indexed_col = 'updated_1_' || id;  
UPDATE vacuum_demo SET indexed_col = 'updated_2_' || id;  
UPDATE vacuum_demo SET indexed_col = 'updated_3_' || id;  

-- 7. 부풀기(Bloat) 결과 확인  
SELECT   
    pg_size_pretty(pg_table_size('vacuum_demo')) AS bloated_table,  
    pg_size_pretty(pg_indexes_size('vacuum_demo')) AS bloated_index,  
    (SELECT n_dead_tup FROM pg_stat_user_tables WHERE relname = 'vacuum_demo') AS dead_tuples;  

-- [실행 결과]  
-- bloated_table: 26 MB (약 4배 증가)  
-- bloated_index: 18 MB (약 6배 증가)  
-- dead_tuples: 300,000  

• 테이블 크기뿐만 아니라 인덱스 크기도 크게 증가(Index Bloat)했습니다.
• 인덱스 역시 이전 버전의 튜플을 가리키는 물리적 포인터를 유지해야 하므로, 테이블 데이터가 갱신될 때마다 인덱스 파일에도 새로운 엔트리가 추가되어 쓰기 I/O가 대량으로 발생합니다.

3. PostgreSQL 성능 최적화의 핵심: HOT 업데이트

• 이번에는 인덱스가 없는 컬럼을 업데이트해 보겠습니다.

-- 8. 인덱스가 '없는' 컬럼을 3번 연속 업데이트  
UPDATE vacuum_demo SET non_indexed_col = 'hot_1_' || id;  
UPDATE vacuum_demo SET non_indexed_col = 'hot_2_' || id;  
UPDATE vacuum_demo SET non_indexed_col = 'hot_3_' || id;  

-- 9. HOT 업데이트 결과 확인  
SELECT   
    pg_size_pretty(pg_table_size('vacuum_demo')) AS table_after_hot,  
    pg_size_pretty(pg_indexes_size('vacuum_demo')) AS index_after_hot;  

-- [실행 결과]  
-- table_after_hot: 43 MB (계속 증가함)  
-- index_after_hot: 19 MB (거의 증가하지 않음!) 

• 테이블 크기는 이전과 마찬가지로 늘어났지만, 인덱스 크기는 거의 변동이 없습니다. 이것은 PostgreSQL의 HOT 업데이트때문입니다.
• 일반적인 UPDATE는 새로운 튜플이 생성되어 물리적 위치가 변경되므로, 해당 테이블에 연결된 모든 인덱스 파일을 수정하는 쓰기 I/O가 발생합니다. 
• 하지만 HOT 업데이트는 수정하려는 컬럼에 인덱스가 없고, 데이터가 저장된 동일한 물리적 페이지(Block) 내에 여유 공간(fillfactor)이 있을 때 작동합니다. 이 경우 인덱스 파일은 전혀 수정하지 않고, 데이터 페이지 내부에서만 기존의 오래된 튜플이 새 튜플의 물리적 위치를 가리키도록 체인 형태로 포인터를 연결합니다.
• 결과적으로 데이터 파일 I/O만 발생할 뿐 인덱스 파일 수정 I/O는 생략되므로 성능이 향상됩니다. DB 설계 시 "자주 업데이트되는 컬럼에는 가급적 인덱스를 걸지 말라"라는 규칙이 바로 이 원리 때문입니다
• fillfactor
  • 낮추면 ⬇️:
    • 장점) hot 업데이트 성공률이 높아짐
    • 단점) 저장공간낭비
  • 높이면 ⬆️
    
    • 장점)데이터를꾺꾺눌러담앗으니까 한번의 io로 더많은 데이터를 읽을수잇음
    • 장점)저장 공간 절약
    • 단점) 데이터 수정되는순간 들어갈자리가 없어서 다른 페이지로 이사가야함. not-hot업데이트발생

4. VACUUM과 물리적 이동

VACUUM

-- 10. 일반 VACUUM 실행  
VACUUM vacuum_demo;  

-- 11. 상태 확인 (크기와 물리적 파일 이름)  
SELECT   
    pg_size_pretty(pg_table_size('vacuum_demo')) AS size_after_vacuum,  
    relfilenode   
FROM pg_class WHERE relname = 'vacuum_demo';  

-- [실행 결과]  
-- size_after_vacuum: 43 MB (용량 감소 없음)  
-- relfilenode: 5253114  

• 물리적인 파일 크기와 파일 이름(relfilenode)이 그대로입니다. 
  
  • VACUUM이 파일 크기를 줄이는 것이 아니라 내부의 빈 공간을 찾아 '재사용 가능' 상태로만 마킹하기 때문입니다.
  • (relfilenode는 해당 테이블이 디스크 상에 저장될 때 사용하는 실제 파일의 이름(번호)입니다)
• 추가로, 앞서 60만 번의 UPDATE를 수행하는 동안, 내부적으로 60만 개의 트랜잭션 ID(XID)가 소모되었습니다.
• 이 XID는 약 42억 개를 한도로 순환하는데, 한계를 넘으면 과거데이터와 미래 데이터를 구분하지 못해 DB가 멈추는 Transaction ID Wraparound 현상이 발생합니다.
• 일반 VACUUM은 아주 오래된 데이터들을 찾아 더 이상 XID 비교 대상이 되지 않도록 냉동 처리하여, DB의 셧다운 타이머를 초기화하는 생명 연장의 역할을 합니다.
  • 냉동처리: 이 데이터는 아주 옛날데이터니, 앞으로는 어떤 트랜잭션 번호와 비교해도 항상 과거의 데이터로 간주하도록 표식해놓는것
• 일반 VACUUM이 인덱스의 물리적 크기를 줄이지 못하는 이유:
  • '정렬 제약' 때문입니다.
  • 인덱스 페이지 전체가 비지 않는 한 OS에 공간을 반환할 수 없으며, 중간중간 뚫린 구멍은 순서가 맞는 데이터만 채울 수 있어 재사용 효율이 매우 떨어집니다.

VACUUM FULL

-- 12. VACUUM FULL 실행 (강력한 Lock 발생, 운영 중 주의!)  
VACUUM FULL vacuum_demo;  

-- 13. 최종 결과 및 '이사'의 증거 확인  
SELECT   
    pg_size_pretty(pg_table_size('vacuum_demo')) AS final_table_size,  
    pg_size_pretty(pg_indexes_size('vacuum_demo')) AS final_index_size,  
    relfilenode   
FROM pg_class WHERE relname = 'vacuum_demo';  

-- [실행 결과]  
-- final_table_size: 5896 kB (초기 상태로 복구)  
-- final_index_size: 3104 kB (초기 상태로 복구)  
-- relfilenode: 5253121 (번호가 변경됨!!)  

• 용량이 초기 상태로 줄어들었으며, relfilenode값이 변경되었습니다. 
• 즉, VACUUM FULL은 활성 상태인 데이터만을 추출해 디스크상에 완전히 새로운 파일을 생성하는 방식입니다.
• 이사를 가는 과정이기 때문에 작업 중 해당 테이블에 대한 모든 접근이 차단(Access Exclusive Lock)되며, 원본 테이블 크기만큼의 추가적인 디스크 여유 공간이 반드시 필요합니다.

실무대안

운영 중에 VACUUM FULL의 Lock을 감당할 수 없다면 다음 대안을 고려할 수 있습니다.

1. REINDEX CONCURRENTLY: 서비스 중단 없이 낡은 인덱스 새 인덱스로 교체 (PG 12+).
   • 단점) 새로운 인덱스를 만드는 동안 기존 인덱스도 유지해야해서 I/O 및 CPU 부하 2배,
   • 실패 시 'Invalid' 인덱스 남음
     • 1. 테이블을 한번훑고 그사이에 들어온 변화를 훑으며 계속확인하면서 인덱스를 만듦
     • 2. 이중간에 네트워크 장애 및 유니크제약위배 및 쿼리취소하는 등의 사고가 발생하면 인덱스 만들기는 실패함.
     • 3. PostgreSQL은 이 실패한 인덱스를 자동으로 지우지 않고 INVALID라는 낙인만 찍은 채 그대로 둠 (용량차지, 사용도 안되는 인덱스가 됨. 시스템 카탈로그를 조회해봐야 존재를 알게됨)
2. pg_repack : 서비스 영향 없이 테이블/인덱스 Bloat을 물리적으로 제거하는 툴.
   • 단점) 테이블을 통째로 새로 복사하는 방식이라 추가 디스크 공간 필요 (테이블 크기만큼), 외부 툴 의존
3. Autovacuum 튜닝: autovacuum_vacuum_scale_factor를 기본값(0.2)보다 낮게(0.01~0.05) 설정하여 Bloat이 쌓이기 전에 청소하게 유도.
   • 단점) (다른 중요한 비즈니스 쿼리가써야할 자원을 Autovacuum이 사용할수도?) 상시적인 I/O 부하, 이미 커진 파일은 줄이지 못함

결론

• 첫째, 인덱스 부풀기(Bloat)의 실체
  • MVCC 특성상 UPDATE가 발생하면 테이블뿐만 아니라 인덱스까지 기하급수적으로 비대해지며, 30만 개의 죽은 튜플이 생성될 때 인덱스 파일에도 새로운 엔트리가 계속 추가되어 막대한 쓰기 I/O를 초래한다는 것을 알 수 있습니다.
• 둘째, HOT 업데이트를 활용한 최적화 전략
  • 수정하려는 컬럼에 인덱스가 없다면, 인덱스 파일은 전혀 건드리지 않고 데이터 페이지 내부에서만 체인 형태로 포인터를 연결하는 HOT(Heap Only Tuple) 업데이트가 작동합니다. 값이 자주 변경되는 컬럼(상태값, 조회수 등)에는 가급적 인덱스를 걸지 않아야 DB 성능을 올릴수있습니다.
• 셋째, VACUUM의 진짜 목적과 한계
  • 일반 VACUUM 명령어를 친다고 해서 디스크 용량이 반환되지 않습니다.
  • 일반 VACUUM은 빈 공간을 재사용 가능 상태로 마킹하고 트랜잭션 ID 고갈로 인한 DB 셧다운을 방지합니다.
  • 물리적인 용량 확보를 위해서는 VACUUM FULL이 필요하며, 이는 내부적으로 아예 새로운 파일을 생성해 유효한 데이터만 이사시키는 무거운 작업이므로 디스크 여유 공간 확보와 Lock에 대한 주의가 필요합니다.

---

참고
물리크기측정함수
relfilenode