6.1 B-tree 인덱스

B-tree는 PostgreSQL의 기본 인덱스 타입입니다. 등호·범위·정렬·LIKE 'prefix%'·IS NULL·ORDER BY·B-tree 기반 unique constraint 등 가장 흔한 쿼리 패턴 대부분을 처리합니다. CREATE INDEX에 타입을 안 지정하면 B-tree.

구조

    flowchart TD
  R["Root page<br/>(internal)"]
  M1["Internal page"]
  M2["Internal page"]
  L1["Leaf<br/>1~30, ctid"]
  L2["Leaf<br/>31~80, ctid"]
  L3["Leaf<br/>81~120, ctid"]
  L4["Leaf<br/>121~..., ctid"]
  R --> M1
  R --> M2
  M1 --> L1
  M1 --> L2
  M2 --> L3
  M2 --> L4
  L1 -.linked.- L2 -.linked.- L3 -.linked.- L4

  classDef root fill:#ede9fe,stroke:#6d28d9,color:#3b0764,stroke-width:2px
  classDef internal fill:#dbeafe,stroke:#1d4ed8,color:#1e3a8a
  classDef leaf fill:#d1fae5,stroke:#047857,color:#064e3b
  class R root
  class M1,M2 internal
  class L1,L2,L3,L4 leaf

Leaf 페이지가 실제 인덱스 entry — (key, ctid) 쌍을 정렬해 보관
같은 키 값이 여러 row를 가리키면 posting list(PG 13+)로 묶어 공간 절약
Leaf끼리 양방향 linked list로 연결 — 범위 스캔이 효율적
Internal page는 자식 페이지의 경계 키

잘 처리하는 쿼리

쿼리	인덱스 활용
`WHERE col = ?`	등호
`WHERE col > ?`, `<`, `BETWEEN`, `>=`	범위
`WHERE col IS NULL`, `IS NOT NULL`	NULL 처리 (PG 11+)
`WHERE col LIKE 'foo%'`	prefix match (default opclass 한정)
`ORDER BY col`	정렬 결과를 직접 반환
`(a, b, c) > (?, ?, ?)`	다중 컬럼 비교

처리 못 하는 쿼리:

쿼리	이유
`WHERE col LIKE '%bar%'`	suffix·중간 매치는 인덱스 불가 (GIN trgm 필요)
`WHERE LOWER(col) = ?`	함수 호출 결과는 모름 → expression index 필요
`WHERE col[1] = ?`	array 부분 — GIN 필요
`WHERE jsonb_col @> ?`	JSONB containment — GIN 필요

자주 쓰는 옵션

unique

CREATE UNIQUE INDEX idx_email ON users(email);

PK·UNIQUE constraint도 내부적으로 unique B-tree 인덱스를 생성합니다.

다중 컬럼 (composite)

CREATE INDEX idx_orders_user_status ON orders(user_id, status, created_at);

가장 왼쪽 컬럼부터 차례로 사용합니다. WHERE user_id = 42 AND status = 'paid'는 OK, WHERE status = 'paid'만은 인덱스 부분 활용 또는 skip scan(미지원).

선행 컬럼 선택 원칙:

selectivity가 높은 컬럼 먼저 (필터 효과 큰 것)
등호 조건 컬럼 먼저, 범위는 뒤
ORDER BY에 자주 쓰는 컬럼이 마지막에 있으면 정렬도 인덱스로 처리

Partial index

CREATE INDEX idx_active_orders ON orders(user_id)
  WHERE status = 'pending';

조건이 자주 등장하면 인덱스가 작아져 빠릅니다. 매우 큰 테이블의 hot 부분만 인덱싱하는 데 강력.

Expression index

CREATE INDEX idx_users_lower_email ON users(LOWER(email));
-- 매칭 쿼리도 같은 식
SELECT * FROM users WHERE LOWER(email) = 'a@b.com';

INCLUDE (covering, PG 11+)

CREATE INDEX idx_orders_user_status_inc
  ON orders(user_id, status)
  INCLUDE (created_at, amount);

INCLUDE 컬럼은 정렬 키는 아니지만 인덱스에 같이 저장합니다. index-only scan(4.3) 가능성을 높여 줍니다.

opclass

기본 B-tree opclass는 텍스트 비교에 로케일을 따릅니다. LIKE 'prefix%'로 인덱스를 쓰려면 C 로케일이 아닌 클러스터에서는 text_pattern_ops가 필요:

CREATE INDEX idx_users_name_prefix ON users(name text_pattern_ops);

opclass	용도
`text_ops` (기본)	일반 비교
`text_pattern_ops`	`LIKE 'foo%'` 인덱스 활용
`varchar_pattern_ops`, `bpchar_pattern_ops`	varchar·char용

fillfactor

B-tree의 기본 fillfactor는 90 (페이지의 90%만 채움). hot update와의 상호작용은 4.3 참고합니다. unique 인덱스는 분할이 잦으면 fillfactor를 70~80으로 낮춰 빈 공간을 확보하는 패턴이 있습니다.

인덱스 생성 옵션

옵션	의미
`CONCURRENTLY`	`SHARE UPDATE EXCLUSIVE` 락만 사용 — 운영 중 안전
`IF NOT EXISTS`	이미 있으면 skip
`WITH (fillfactor = 80)`	fillfactor 지정
`TABLESPACE name`	다른 tablespace에 (4.5)

운영 중 인덱스 생성은 항상 CONCURRENTLY:

CREATE INDEX CONCURRENTLY idx_new ON orders(...);

CONCURRENTLY는 한 트랜잭션 안에서는 못 쓰며, 실패 시 INVALID 상태로 남습니다. 정리:

SELECT indexrelid::regclass FROM pg_index WHERE NOT indisvalid;
DROP INDEX CONCURRENTLY idx_new;   -- 또는 REINDEX CONCURRENTLY

인덱스 점검 SQL

-- 미사용 인덱스 (idx_scan = 0)
SELECT relname AS table, indexrelname AS index,
       pg_size_pretty(pg_relation_size(indexrelid)) AS size,
       idx_scan
  FROM pg_stat_user_indexes
 WHERE idx_scan = 0
 ORDER BY pg_relation_size(indexrelid) DESC;

-- 중복 인덱스 후보
SELECT indrelid::regclass, array_agg(indexrelid::regclass) AS dup
  FROM pg_index
 GROUP BY indrelid, indkey
HAVING count(*) > 1;

BLOAT

UPDATE가 인덱스 컬럼을 자주 건드리거나 분할이 잦으면 인덱스 페이지에 dead entry가 쌓입니다. PG 13+의 B-tree 중복 제거가 어느 정도 완화하지만, REINDEX CONCURRENTLY가 정기 관리 도구입니다.

REINDEX INDEX CONCURRENTLY idx_orders_user_status;

CONCURRENTLY는 PG 12+. 그 이전은 pg_repack 같은 도구를 썼습니다.

정리

B-tree는 기본 인덱스 — 등호·범위·정렬·LIKE 'prefix%' 처리
다중 컬럼은 선행 컬럼 등호 → 범위 → 정렬 순서가 보통 정답
Partial / Expression / INCLUDE 옵션으로 큰 효과
LIKE 'foo%' 인덱스 활용은 text_pattern_ops가 필요할 수 있음
운영 중 인덱스 생성은 항상 CONCURRENTLY
미사용 인덱스 점검과 BLOAT 관리는 6.7에서

다음 절(6.2)에서는 거의 안 쓰지만 알아 둘 가치는 있는 Hash 인덱스를 봅니다.

6.2 Hash 인덱스