8.3 REINDEX와 인덱스 BLOAT

인덱스 BLOAT의 원인·측정·CONCURRENTLY 옵션은 6.7에서 봤습니다. 여기서는 운영 관점에서 REINDEX와 pg_repack을 언제, 어떤 단위로 돌릴지의 절차에 초점을 둡니다.

BLOAT 정비 도구 선택

REINDEX 단위

REINDEX INDEX idx_orders_user_id;            -- 단일 인덱스
REINDEX TABLE orders;                         -- 한 테이블의 모든 인덱스
REINDEX SCHEMA public;                        -- 스키마 전체
REINDEX DATABASE app_main;                    -- DB 전체 (위험)
REINDEX SYSTEM postgres;                      -- 시스템 카탈로그

CONCURRENTLY는 INDEX/TABLE에 가능 (PG 12+). DATABASE·SCHEMA에도 CONCURRENTLY 가능하지만 매우 오래 걸립니다.

운영 중 REINDEX의 안전한 형태

-- 단일 인덱스 (가장 권장)
REINDEX INDEX CONCURRENTLY idx_orders_user_id;

-- 한 테이블의 모든 인덱스 (PostgreSQL 12+)
REINDEX TABLE CONCURRENTLY orders;

	CONCURRENTLY	락
`REINDEX INDEX`	PG 12+	SHARE UPDATE EXCLUSIVE
`REINDEX TABLE`	PostgreSQL 12+	같음
`REINDEX DATABASE/SCHEMA`	PostgreSQL 12+	같음 (하지만 오래)

CONCURRENTLY 실패 후 정리

CONCURRENTLY 빌드가 deadlock·취소·crash로 실패하면 임시 인덱스가 INVALID 상태로 남습니다.

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

정리:

DROP INDEX CONCURRENTLY idx_orders_user_id_ccnew;   -- _ccnew 접미사
-- 또는
REINDEX INDEX CONCURRENTLY idx_orders_user_id;  -- 다시 시도

디스크 여유 점검

CONCURRENTLY는 옛 인덱스와 새 인덱스가 동시에 존재하므로 인덱스 크기의 2배 + α 만큼 여유가 필요합니다. 큰 인덱스 REINDEX 전 디스크 잔여 확인합니다.

SELECT pg_size_pretty(pg_relation_size('idx_orders_user_id')) AS index_size;

pg_repack — 인덱스 + 테이블 둘 다

REINDEX는 인덱스만 정리합니다. 테이블 자체의 BLOAT(특히 dead tuple의 물리 공간)는 손대지 않습니다. VACUUM FULL이 답이지만 ACCESS EXCLUSIVE 락 — 운영 중 금지합니다.

pg_repack은 운영 중 무중단으로 테이블+인덱스 모두 재구성합니다.

# 한 테이블
pg_repack -d app_main -t orders

# 인덱스만
pg_repack -d app_main -t orders --only-indexes

# 스키마 전체
pg_repack -d app_main -s public

# dry-run (실제 실행 안 함)
pg_repack -d app_main -t orders --dry-run

장점	단점
운영 중 무중단	외부 도구, 별도 설치 필요
테이블 + 인덱스 모두	임시 디스크 2배 필요
`VACUUM FULL`의 효과	짧은 ACCESS EXCLUSIVE 락 한 번 (rename 시점)

설치:

# RHEL/Rocky
sudo dnf install -y pg_repack_17

# Debian/Ubuntu
sudo apt-get install -y postgresql-17-repack

확장 활성:

CREATE EXTENSION pg_repack;

정기 운영 절차

월간 점검

-- BLOAT 큰 인덱스
WITH b AS MATERIALIZED (
  SELECT i.oid, i.relname
    FROM pg_class i
    JOIN pg_am am ON i.relam = am.oid
   WHERE i.relkind = 'i' AND am.amname = 'btree' AND pg_relation_size(i.oid) > 100*1024*1024
)
SELECT b.relname AS index,
       pg_size_pretty(pg_relation_size(b.oid)) AS size,
       round(s.avg_leaf_density::numeric, 1) AS density
  FROM b
  CROSS JOIN LATERAL pgstatindex(b.oid) s
 ORDER BY s.avg_leaf_density;

density < 70이면 REINDEX 후보입니다.

분기 정비

#!/bin/bash
# 큰 인덱스 자동 REINDEX
psql -d app_main -t -c "
WITH b AS MATERIALIZED (
  SELECT i.oid, n.nspname AS schemaname, i.relname AS indexname
    FROM pg_class i
    JOIN pg_namespace n ON i.relnamespace = n.oid
    JOIN pg_am am ON i.relam = am.oid
   WHERE i.relkind = 'i' AND am.amname = 'btree' AND pg_relation_size(i.oid) > 100*1024*1024
)
SELECT format('REINDEX INDEX CONCURRENTLY %I.%I;',
       b.schemaname, b.indexname)
  FROM b
  CROSS JOIN LATERAL pgstatindex(b.oid) s
 WHERE s.avg_leaf_density < 70;
" | psql -d app_main

야간 시간대 cron으로 실행합니다.

반기·연간

대규모 BLOAT나 테이블 자체의 dead 공간이 큰 경우 pg_repack:

pg_repack -d app_main -t orders --no-superuser-check

VACUUM FULL을 안 쓰는 이유

VACUUM FULL은 읽기·쓰기·DDL 모두 차단하는 ACCESS EXCLUSIVE 락을 잡고 테이블을 완전히 재구성합니다.

시나리오	영향
100GB 테이블	수 시간 이상 트래픽 정지
운영 DB	절대 금지
단순 검증 환경	OK
점검 시간 명확	가능, 하지만 pg_repack이 더 안전

운영에서는 pg_repack 또는 분기 REINDEX CONCURRENTLY가 답입니다.

외래 키와 REINDEX

REINDEX는 인덱스만 재구성합니다. 부모-자식 관계, FK constraint는 그대로. unique constraint를 drop and recreate가 필요하면 CREATE INDEX CONCURRENTLY + ADD CONSTRAINT USING INDEX 같은 패턴입니다.

-- 새 인덱스 빌드
CREATE UNIQUE INDEX CONCURRENTLY users_email_new ON users(email);

-- 기존 제약 교체 (DROP CONSTRAINT가 기존 인덱스도 함께 삭제)
BEGIN;
ALTER TABLE users DROP CONSTRAINT users_email_key;
ALTER TABLE users ADD CONSTRAINT users_email_key UNIQUE USING INDEX users_email_new;
COMMIT;

이 제약을 FK가 참조 중이면 DROP CONSTRAINT가 실패하므로 FK 재생성이 필요합니다.

운영 안티패턴

안티패턴	문제
`REINDEX DATABASE` 운영 중 실행	모든 인덱스 ACCESS EXCLUSIVE — 클러스터 정지
`VACUUM FULL`을 정기 작업으로	운영 시간 정지, pg_repack 권장
CONCURRENTLY 빌드 중 deadlock 무시	INVALID 인덱스 누적
디스크 여유 안 보고 REINDEX	진행 중 디스크 풀 → 클러스터 정지

경고

큰 인덱스 REINDEX CONCURRENTLY 전 반드시 디스크 여유 확인. 100GB 인덱스를 재구성하면 일시적으로 200GB+가 점유됩니다. 디스크 90%+ 사용 중인 클러스터에서 시작하면 사고입니다.

정리

REINDEX는 인덱스만 재구성하고 테이블 자체의 BLOAT는 손대지 않습니다. 운영 중에는 항상 CONCURRENTLY를 붙여 REINDEX INDEX 또는 REINDEX TABLE로 돌리고, 실패하면 남은 INVALID 인덱스를 정리합니다.

pg_repack: 테이블 + 인덱스 모두 무중단 재구성, 운영 표준
VACUUM FULL은 운영 중 금지
분기·반기 정비 자동화가 BLOAT 누적을 막음

REINDEX 단위​

운영 중 REINDEX의 안전한 형태​

CONCURRENTLY 실패 후 정리​

디스크 여유 점검​

pg_repack — 인덱스 + 테이블 둘 다​

정기 운영 절차​

월간 점검​

분기 정비​

반기·연간​

VACUUM FULL을 안 쓰는 이유​

외래 키와 REINDEX​

운영 안티패턴​

정리​