PostgreSQL 인덱스 설계 — B-tree 밖을 선택해야 할 때

대부분의 PostgreSQL 쿼리는 B-tree 인덱스로 잘 돌아간다. `WHERE user_id = $1`이나 `ORDER BY created_at DESC` 같은 쿼리에 B-tree는 충분하다. 하지만 배열 포함 여부를 조회하거나, 전문 검색을 하거나, 좌표 기반 거리 쿼리를 날리기 시작하면 B-tree가 아무것도 못 하거나 오히려 방해가 된다.

이걸 처음 마주하면 당황스럽다. "인덱스 걸었는데 Seq Scan이 나오네" — 이 상황이 생기는 이유는 쿼리 연산자가 인덱스 접근 방식과 맞지 않기 때문이다.

## B-tree가 못 하는 것들

B-tree는 비교 연산(`=`, `<`, `>`, `BETWEEN`)에 특화되어 있다. 다음 케이스에서는 작동하지 않는다.

**배열/JSON에 포함 여부 확인** (`@>`, `&&`, `?`):

```sql
-- tags 배열에 'postgres'가 있는 글 조회
SELECT * FROM posts WHERE 'postgres' = ANY(tags);
```

이 쿼리에 B-tree 인덱스를 걸어봤자 Seq Scan이 나온다. `=`이나 `<`가 아니라 포함 연산자이기 때문이다.

**전문 검색** (`@@`, `to_tsvector`):

```sql
SELECT * FROM docs WHERE to_tsvector('english', body) @@ to_tsquery('docker & kubernetes');
```

B-tree는 ts_vector 타입을 정렬할 수 없다. 이 연산자를 위한 인덱스 타입이 따로 있다.

**지리 좌표 근접 쿼리** (PostGIS `<->`):

```sql
SELECT *, point(lng, lat) <-> point(37.5, 127.0) AS dist
FROM locations ORDER BY dist LIMIT 10;
```

B-tree는 2차원 거리를 이해하지 못한다.

## GIN: 역인덱스

GIN(Generalized Inverted Index)은 하나의 행이 여러 값을 포함할 때 사용한다. 각 값에서 그 값을 포함하는 행 목록으로의 매핑을 저장한다.

```sql
-- 배열 필드에 GIN 인덱스
CREATE INDEX idx_posts_tags ON posts USING GIN(tags);

-- 이제 이 쿼리가 인덱스를 탄다
SELECT * FROM posts WHERE tags @> ARRAY['postgresql'];
SELECT * FROM posts WHERE tags && ARRAY['postgresql', 'redis'];
```

JSONB 컬럼 전체를 대상으로도 쓸 수 있다:

```sql
CREATE INDEX idx_data_gin ON items USING GIN(data);

-- 특정 키 존재 여부
SELECT * FROM items WHERE data ? 'price';

-- 키-값 포함 여부
SELECT * FROM items WHERE data @> '{"status": "active"}';
```

전문 검색에도 GIN을 쓴다:

```sql
CREATE INDEX idx_docs_fts ON docs USING GIN(to_tsvector('english', body));

SELECT * FROM docs
WHERE to_tsvector('english', body) @@ to_tsquery('english', 'replica & failover');
```

**GIN의 트레이드오프**: 업데이트 비용이 높다. 한 행에 새 태그를 추가하면 인덱스에서 이전 태그 목록을 삭제하고 새 목록을 삽입해야 한다. 쓰기가 많은 테이블에서는 `fastupdate` 옵션을 이해하고 써야 한다.

## GiST: 검색 트리

GiST(Generalized Search Tree)는 인덱스 구조 자체를 커스터마이즈할 수 있는 프레임워크다. PostGIS의 공간 인덱스, 범위 타입 인덱스, tsvector 인덱스 등이 GiST를 쓴다.

```sql
-- PostGIS와 함께
CREATE INDEX idx_locations_geo ON locations USING GiST(geom);

-- 근접 쿼리 (KNN)
SELECT * FROM locations
ORDER BY geom <-> ST_MakePoint(127.0, 37.5)
LIMIT 10;
```

범위 타입에도 유용하다:

```sql
CREATE INDEX idx_events_period ON events USING GiST(period);

-- 겹치는 기간 조회
SELECT * FROM events WHERE period && '[2026-06-01, 2026-06-30)';
```

**GiST vs GIN**: 전문 검색에는 GIN이 더 빠른 경우가 많다. GiST는 KNN 쿼리를 지원한다는 점에서 공간 데이터에 유리하다.

## BRIN: 블록 범위 인덱스

BRIN(Block Range INdex)은 물리적으로 연속된 블록 내의 최솟값/최댓값만 기록한다. 인덱스 크기가 매우 작다.

```sql
CREATE INDEX idx_logs_created ON logs USING BRIN(created_at);
```

**언제 쓰나**: 테이블이 삽입 순서와 쿼리 패턴이 일치할 때. 시계열 로그 테이블에서 시간 범위로 조회하는 경우, `created_at`이 물리적으로 순서대로 삽입되므로 BRIN이 잘 작동한다. B-tree 인덱스 대비 수백 배 작은 크기로 비슷한 효과를 낸다.

**언제 안 쓰나**: 데이터가 무작위로 삽입되거나, 업데이트가 빈번하거나, 범위 검색이 아닌 등가 검색이 주를 이루면 BRIN은 효과가 없다.

## 부분 인덱스

인덱스 타입은 아니지만 언급할 가치가 있다. 조건절이 있는 인덱스를 만들면 인덱스 크기가 줄고 업데이트 비용이 낮아진다.

```sql
-- 처리 중인 작업만 인덱스
CREATE INDEX idx_jobs_pending ON jobs(created_at)
WHERE status = 'pending';

-- 삭제되지 않은 행만 인덱스 (소프트 딜리트 패턴)
CREATE INDEX idx_users_active ON users(email)
WHERE deleted_at IS NULL;
```

`WHERE status = 'pending'`으로 자주 조회하는데 `pending` 상태가 전체 행의 1%밖에 안 된다면, 전체 테이블에 인덱스를 거는 것보다 부분 인덱스가 훨씬 작고 빠르다.

## 어떤 인덱스를 선택할지

| 쿼리 패턴 | 인덱스 타입 |
|-----------|------------|
| `=`, `<`, `>`, `BETWEEN`, `LIKE 'prefix%'` | B-tree (기본) |
| 배열 포함 `@>`, `&&`, `?` | GIN |
| JSONB 키 존재/포함 | GIN |
| 전문 검색 `@@` | GIN (또는 GiST) |
| 지리 좌표, KNN 거리 순 | GiST + PostGIS |
| 범위 타입 겹침 `&&` | GiST |
| 대용량 시계열, 삽입 순 범위 쿼리 | BRIN |

결정적인 확인 방법은 `EXPLAIN (ANALYZE, BUFFERS)`. 어떤 인덱스 타입을 쓰든, 실제로 인덱스를 타는지 플랜을 보고 확인하는 습관이 중요하다. 옵티마이저가 틀리는 경우도 있고, 통계가 오래됐을 때 예상과 다른 플랜이 나오기도 한다.

PostgreSQL 인덱스 설계 — B-tree 밖을 선택해야 할 때

// COMMENTS

ON THIS PAGE