본문으로 건너뛰기
Docs
Part VI. 인덱스
6.7 인덱스 BLOAT와 REINDEX CONCURRENTLY

6.7 인덱스 BLOAT와 REINDEX CONCURRENTLY

PostgreSQL의 인덱스는 시간이 지나면 BLOAT(필요 이상으로 큰 상태)로 자랍니다. UPDATE·DELETE가 dead entry를 남기고, 페이지 분할이 빈 공간을 남기기 때문. BLOAT은 인덱스 크기 증가뿐 아니라 캐시 효율 저하, 더 느린 스캔으로 이어집니다. BLOAT의 원인·측정·정리 절차를 정리합니다.

BLOAT의 원인

원인설명
UPDATEnon-HOT update면 새 row가 추가되고 인덱스 entry도 새로 들어감. 이전 entry는 vacuum 시 정리되지만 페이지 자리는 그대로
DELETEdead tuple은 vacuum이 표시·재사용. 인덱스에서 사라지는 시점은 다름
페이지 분할새 row가 들어갈 자리 없으면 B-tree 페이지가 둘로 쪼개짐 — 두 페이지가 모두 약 50%만 채워진 상태로 시작
autovacuum 부족일이 못 따라오면 dead entry가 누적
긴 트랜잭션xmin horizon이 잡혀 vacuum이 dead tuple을 못 정리 (3.2 참고)

측정 — pgstattuple

가장 정확한 BLOAT 측정합니다.

CREATE EXTENSION pgstattuple;

SELECT * FROM pgstattuple_approx('idx_orders_user_id');
-- approx_free_percent | 약 12.5  -- 인덱스에서 빈 공간 비율

전체 인덱스 한 번에 보기:

SELECT i.relname AS index,
       pg_size_pretty(pg_relation_size(i.oid)) AS size,
       round(s.avg_leaf_density, 1) AS density,
       round(100 - s.avg_leaf_density, 1) AS bloat_pct
  FROM pg_index x
  JOIN pg_class i ON x.indexrelid = i.oid
  JOIN pgstatindex(i.oid) s ON true
 WHERE i.relkind = 'i'
 ORDER BY pg_relation_size(i.oid) DESC
 LIMIT 10;
지표좋은 값
avg_leaf_density80% 이상
leaf_fragmentation30% 미만

90% 이상이 잘 운영된 B-tree. 50% 가까이 떨어지면 REINDEX 검토합니다.

대안 (확장 없이): 공식 wiki의 bloat 측정 SQL. 정확도는 약하지만 빠릅니다.

REINDEX CONCURRENTLY

PostgreSQL 12+에서 도입. 운영 중에도 테이블 락 없이 인덱스를 새로 만들고 교체.

REINDEX INDEX CONCURRENTLY idx_orders_user_id;

-- 한 테이블의 모든 인덱스
REINDEX TABLE CONCURRENTLY orders;

-- 데이터베이스 전체 (위험·오래 걸림)
REINDEX DATABASE CONCURRENTLY app_main;

내부 동작:

  1. 새 인덱스를 _ccnew 접미사로 같이 생성 (모든 row 인덱싱)
  2. 새 인덱스를 valid 상태로 활성
  3. 옛 인덱스를 drop

이 과정 동안 SHARE UPDATE EXCLUSIVE 락만 잡아 SELECT/INSERT/UPDATE 가능합니다.

실패 처리

CONCURRENTLY 빌드가 실패(예: deadlock, 충돌)하면 INVALID 상태로 남습니다.

SELECT indexrelid::regclass, indisvalid
  FROM pg_index
 WHERE NOT indisvalid;

INVALID 인덱스는:

  • 옵티마이저가 사용 안 함
  • 자동 정리 안 됨

대응:

-- 정리하고 다시 시도
DROP INDEX CONCURRENTLY idx_orders_user_id_ccnew;
REINDEX INDEX CONCURRENTLY idx_orders_user_id;

CONCURRENTLY의 제약

제약메모
트랜잭션 안에서 못 실행단독 명령으로만
임시 디스크 공간이 필요옛+새 인덱스 동시 존재 → 인덱스 크기의 2배+α
빌드 중 dead tuple 정리 지연빌드 중에는 xmin horizon이 일시적으로 잡힘
SI 락이 막힐 수 있음DDL과 동시에 실행 시 wait

pg_repack — 더 강력한 대안

PostgreSQL에 기본 포함되지는 않지만 운영에서 자주 쓰이는 외부 도구입니다. 테이블+인덱스 모두 BLOAT 제거, VACUUM FULL을 대신해 운영 중 무중단으로 재구성합니다.

pg_repack -d app_main -t orders
pg_repack -d app_main -t orders --only-indexes

장점: 매우 정밀, 트리거 기반의 동기화로 락 영향 최소화 단점: 외부 도구, 운영 자동화에 통합 필요, 일시 디스크 2배 필요

pg_repack은 별도 챕터로 다룰 만한 깊이가 있다 — 운영자가 반드시 익혀 둘 도구입니다.

운영 표준

작업주기
pg_stat_user_indexes로 미사용 인덱스 점검월
pgstattuple로 BLOAT 측정월 (저녁/주말)
큰 인덱스 REINDEX CONCURRENTLY분기 또는 BLOAT > 30%
pg_repack 풀 정비반기 또는 BLOAT > 50%

자동화 패턴:

# 주말 새벽 — BLOAT 25% 이상인 인덱스만 REINDEX
psql -c "
WITH bloated AS (
  SELECT i.indexrelid::regclass AS idx
    FROM pg_index x
    JOIN pg_class i ON x.indexrelid = i.oid
    JOIN pgstatindex(i.oid) s ON true
   WHERE i.relkind = 'i' AND s.avg_leaf_density < 75
     AND pg_relation_size(i.oid) > 100*1024*1024
)
SELECT format('REINDEX INDEX CONCURRENTLY %s;', idx) FROM bloated;
" | psql

BLOAT 예방

  1. autovacuum이 잘 따라오게 — autovacuum_vacuum_scale_factor를 큰 테이블에서 작게 (0.05~0.02)
  2. 긴 트랜잭션·잊혀진 prepared 추적 — 3.2 참고
  3. HOT update가 가능하도록 인덱스 최소화 — 4.3
  4. fillfactor 적정 (70~85 for update-heavy)
  5. GIN은 fastupdate + 정기 REINDEX — 6.4

정리

  • 인덱스 BLOAT은 UPDATE/DELETE/페이지 분할로 자연 발생
  • 측정: pgstattuple의 avg_leaf_density — 80% 이상이 정상
  • 정리: REINDEX CONCURRENTLY (PG 12+) — 운영 중 안전
  • 실패 시 INVALID 인덱스 남음 — 수동 정리 필요
  • pg_repack은 외부 도구지만 더 강력 — 운영 표준 추천
  • 예방은 autovacuum 튜닝 + HOT 친화적 인덱스 설계 + 짧은 트랜잭션

Part VI 인덱스가 끝났습니다. 다음 Part VII에서는 대용량 테이블을 다루는 파티셔닝을 봅니다.

6.6 부분/표현식 인덱스