인덱스 — B-Tree·GIN·BRIN

인덱스는 "책 뒤의 색인" — 없으면 처음부터 끝까지 다 읽어야 하지만, 있으면 단숨에 페이지를 펼칩니다. 다만 종류와 사용 패턴이 맞아야 효력. 16편은 PG 의 5가지 주요 인덱스, 컬럼 순서, partial/covering 인덱스, 그리고 "어디에 인덱스를 둘까" 의 가이드.

5가지 인덱스 종류

B-Tree — 기본형

-- 기본
CREATE INDEX ix_users_email ON users (email);

-- UNIQUE (제약 + 인덱스)
CREATE UNIQUE INDEX uq_users_email ON users (email);

-- 정렬 방향
CREATE INDEX ix_posts_created ON posts (created_at DESC);
-- ORDER BY created_at DESC 인 쿼리에서 더 효율

-- NULLS 순서
CREATE INDEX ix_users_phone ON users (phone NULLS LAST);

multi-column 인덱스 — 컬럼 순서가 핵심

-- 자주 같이 쓰는 컬럼
CREATE INDEX ix_orders_user_date ON orders (user_id, created_at);

-- 어떤 쿼리에 듣나?
SELECT * FROM orders WHERE user_id = 5;                              -- ✅ 듣음
SELECT * FROM orders WHERE user_id = 5 AND created_at > '2026-01-01'; -- ✅ 최적
SELECT * FROM orders WHERE created_at > '2026-01-01';                -- ❌ 안 듣음
-- 첫 컬럼이 WHERE 에 없으면 인덱스 못 씀 (B-Tree 의 기본 동작)

partial index — 일부만

-- "활성 사용자" 만 인덱스 → 더 작고 빠름
CREATE INDEX ix_users_active_email ON users (email)
WHERE active = true;

-- 쿼리도 같은 조건이어야 사용됨
SELECT * FROM users WHERE email = '[email protected]' AND active = true;   -- ✅

-- 작업 큐 패턴 (15편 SKIP LOCKED 와 함께)
CREATE INDEX ix_jobs_pending ON jobs (created_at)
WHERE status = 'pending';
-- 'done' 행은 인덱스에 없음 → 인덱스 크기 안정적

covering index (INCLUDE) — 인덱스만으로 응답

-- 자주 select 하는 컬럼을 INCLUDE 로
CREATE INDEX ix_users_email_inc ON users (email) INCLUDE (name, created_at);

-- 인덱스 안에 name·created_at 가 함께 들어있으니
-- 테이블 본문 안 가도 응답 가능 → "Index Only Scan"
SELECT name, created_at FROM users WHERE email = '[email protected]';

표현식 인덱스 — 계산된 값에

-- lower(email) 로 자주 비교한다면
CREATE INDEX ix_users_email_lower ON users ((lower(email)));

-- 쿼리에 같은 표현식 필요
SELECT * FROM users WHERE lower(email) = lower($1);   -- ✅

-- json 키
CREATE INDEX ix_products_color ON products ((meta->>'color'));

GIN — jsonb·배열·전문 검색

-- jsonb
CREATE INDEX ix_products_meta ON products USING GIN (meta);
SELECT * FROM products WHERE meta @> '{"color":"red"}';   -- 빠름

-- 배열
CREATE INDEX ix_posts_tags ON posts USING GIN (tags);
SELECT * FROM posts WHERE 'sale' = ANY(tags);

-- 전문 검색 (tsvector)
CREATE INDEX ix_articles_search ON articles USING GIN (to_tsvector('simple', title));
SELECT * FROM articles WHERE to_tsvector('simple', title) @@ to_tsquery('PostgreSQL');

-- pg_trgm — LIKE '%xxx%' 가속 (확장 필요)
CREATE EXTENSION pg_trgm;
CREATE INDEX ix_users_name_trgm ON users USING GIN (name gin_trgm_ops);
SELECT * FROM users WHERE name ILIKE '%준성%';   -- 인덱스 사용!

BRIN — 거대한 시계열의 가벼운 인덱스

-- 로그 테이블 (수억 행, created_at 순으로 누적)
CREATE INDEX ix_logs_created_brin ON logs USING BRIN (created_at);

-- BRIN 은 B-Tree 대비
-- · 인덱스 크기 1/1000 (예: 1GB → 1MB)
-- · 조회는 약간 느림
-- · 쓰기 부하 없음
SELECT * FROM logs WHERE created_at > CURRENT_DATE - INTERVAL '1 day';

인덱스 만들기·삭제 — 운영 환경 주의

-- 일반 CREATE INDEX — 테이블에 SHARE 락 (INSERT/UPDATE 동안 차단)
CREATE INDEX ix_x ON big_table (col);

-- 안전 — 락 안 잡음 (느리지만 운영 OK)
CREATE INDEX CONCURRENTLY ix_x ON big_table (col);

-- 삭제도
DROP INDEX CONCURRENTLY ix_x;

-- 인덱스 다시 만들기 (bloat 정리)
REINDEX INDEX CONCURRENTLY ix_x;

인덱스 사용 확인 — EXPLAIN

EXPLAIN SELECT * FROM users WHERE email = '[email protected]';

-- "Index Scan using ix_users_email" → 인덱스 사용됨
-- "Seq Scan on users" → 인덱스 무시됨 (테이블 풀스캔)

-- 실제 시간까지
EXPLAIN ANALYZE SELECT * FROM users WHERE email = '[email protected]';
-- "actual time=0.012..0.014" 같은 실제 측정

인덱스 통계 — 안 쓰는 인덱스 찾기

-- 각 인덱스가 얼마나 자주 쓰였나
SELECT schemaname, relname, indexrelname,
       idx_scan, idx_tup_read, idx_tup_fetch
FROM   pg_stat_user_indexes
ORDER BY idx_scan;
-- idx_scan = 0 인 인덱스는 한 번도 안 쓰임 — 제거 후보

-- 인덱스 크기
SELECT relname, pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM   pg_stat_user_indexes
ORDER BY pg_relation_size(indexrelid) DESC;

인덱스의 비용

한 줄 가이드

• FK·자주 WHERE 에 나오는 컬럼 → B-Tree
• UNIQUE 제약은 자동으로 인덱스 (UNIQUE 도)
• (a, b) 인덱스는 a 단독 쿼리에도 작동, b 단독은 아님
• jsonb / 배열 / 전문 검색 → GIN
• 로그·시계열 (수억 행) → BRIN
• "활성·미발송" 같은 좁은 부분 → partial
• SELECT 컬럼이 적고 자주 → INCLUDE 로 covering
• 운영 DB: 항상 CONCURRENTLY