5장. 인덱스 완전 정복

PostgreSQL엔 인덱스가 6종류나 있습니다. B-tree가 왜 기본이고, 언제 GIN·BRIN을 꺼내야 하며, 인덱스가 공짜가 아닌 이유까지 — 선택의 기준을 세웁니다.

5.1 인덱스는 "찾아가는 지도"다

PostgreSQL의 테이블(heap)은 정렬되지 않은 튜플 무더기입니다(4장). 조건에 맞는 행을 찾으려면 원칙적으로 모든 페이지를 읽어야 하죠(Seq Scan). 인덱스는 키 → TID((블록, 오프셋)) 매핑을 정렬·구조화해 유지함으로써, heap의 필요한 위치만 찍어 읽게 해줍니다.

공짜는 아닙니다. INSERT/UPDATE/DELETE마다 모든 관련 인덱스도 갱신해야 하고(write amplification), MVCC 특성상 인덱스에도 bloat가 쌓입니다. 그리고 많이 읽어야 하는 조건(관행적으로 5~10% 이상)은 Seq Scan이 오히려 빠릅니다 — 플래너가 이 판단을 대신해 줍니다(6장).

5.2 B-tree — 기본값이자 만능에 가까운 선택

USING 절 없이 CREATE INDEX를 하면 B-tree입니다. =·범위(<, >, BETWEEN)·정렬·전방 일치 LIKE 'abc%'를 모두 처리합니다. 수백만 행에서 원하는 키를 찾는 데 페이지 몇 번이면 충분한 이유를 눈으로 봅시다.

-- 범위 + 정렬 모두 인덱스 하나로
CREATE INDEX idx_orders_created ON orders(created_at);
SELECT * FROM orders ORDER BY created_at DESC LIMIT 100;  -- Sort 노드 불필요

-- INCLUDE(11+): 키가 아닌 컬럼을 leaf에 실어 Index-Only Scan 확장
CREATE INDEX idx_orders_user_inc
  ON orders(user_id) INCLUDE (amount, status);

-- LIKE 'abc%' 전방 일치를 안정적으로: pattern opclass
CREATE INDEX idx_users_email ON users(email text_pattern_ops);

5.3 Seq Scan vs Index Scan — 언제나 인덱스가 이기는 건 아니다

"인덱스를 만들었는데 왜 안 써요?"의 답은 대부분 선택도(selectivity)에 있습니다. 같은 테이블, 두 가지 쿼리를 나란히 봅시다.

5.4 특수 인덱스 — Hash · GIN · GiST · SP-GiST

GIN은 "하나의 값이 여러 요소로 쪼개지는" 컬럼의 해결사입니다. 각 요소(단어, 배열 원소, JSON 키)마다 "어느 행에 있는지" 목록(posting list)을 유지합니다.

-- jsonb 포함 검색
CREATE INDEX idx_events_payload ON events USING gin(payload);
SELECT * FROM events WHERE payload @> '{"level":"error"}';

-- 부분 문자열 매칭: B-tree로는 불가능한 %kim%
CREATE EXTENSION pg_trgm;
CREATE INDEX idx_users_name_trgm ON users USING gin(name gin_trgm_ops);
SELECT * FROM users WHERE name ILIKE '%kim%';

-- GiST 배타 제약: 회의실 이중 예약 원천 차단
CREATE TABLE bookings (
    room_id int,
    period tstzrange,
    EXCLUDE USING gist (room_id WITH =, period WITH &&)
);

5.5 BRIN — 초소형 요약 인덱스

BRIN(Block Range Index)은 개별 행이 아니라 페이지 묶음(기본 128페이지=1MB)의 min/max 요약만 저장합니다. 시계열 로그처럼 물리 순서와 값 순서가 일치하는 데이터에서, GB급 B-tree 대신 몇 MB로 비슷한 효과를 냅니다.

CREATE INDEX idx_logs_ts_brin ON logs USING brin(ts);

-- 정밀도 조절: 범위를 좁히면 스킵 정밀도↑, 인덱스 크기↑
CREATE INDEX idx_logs_ts_brin2
  ON logs USING brin(ts) WITH (pages_per_range = 32);

5.6 Partial · Expression · Unique — 인덱스를 더 얇고 똑똑하게

-- Partial: 전체의 3%인 활성 행만 인덱싱 → 크기·쓰기 비용 급감
CREATE INDEX idx_users_active_email
  ON users(email) WHERE is_active = true;

-- Expression: 함수 결과를 인덱싱 (쿼리 표현식과 문법적으로 일치해야 사용됨)
CREATE INDEX idx_users_email_lower ON users(lower(email));
SELECT * FROM users WHERE lower(email) = 'kim@example.com';

-- Partial Unique: 소프트 삭제 환경에서 "살아있는 행 중에만" 중복 방지
CREATE UNIQUE INDEX idx_users_email_alive
  ON users(email) WHERE deleted_at IS NULL;

5.7 유지 비용 — 인덱스는 만들 때보다 기를 때가 어렵다

인덱스가 N개면 쓰기 I/O도 대략 N배(write amplification). MVCC UPDATE는 인덱스에도 새 엔트리를 만들고, 페이지 분할(split)은 원상 복구되지 않아 index bloat가 됩니다. 관리 루틴:

-- 사용되지 않는 인덱스 찾기 (idx_scan=0이 오래 지속되면 삭제 후보)
SELECT s.relname, s.indexrelname, s.idx_scan,
       pg_size_pretty(pg_relation_size(s.indexrelid)) AS size
FROM pg_stat_user_indexes s
JOIN pg_index i ON i.indexrelid = s.indexrelid
WHERE NOT i.indisunique AND NOT i.indisprimary AND s.idx_scan < 50
ORDER BY pg_relation_size(s.indexrelid) DESC;

-- 12+: 쿼리를 막지 않는 재구축
REINDEX INDEX CONCURRENTLY idx_orders_created;
-- 실패 시 INVALID 고아 인덱스가 남을 수 있음: 확인 필수
SELECT * FROM pg_index WHERE NOT indisvalid;

✍️ 이해도 체크