---
title: "PostgreSQL 인덱스 설계 총정리 — 부분·복합·커버링·표현식부터 GIN/BRIN까지"
description: "PostgreSQL의 성능 최적화를 위해 부분·복합·커버링·표현식 인덱스 등 다양한 설계 기법을 정리하고, 데이터 특성에 맞는 GIN·BRIN 활용법과 운영 중 안전한 인덱스 관리 및 검증 전략을 상세히 설명한다"
date: 2026-06-11
updated: 2026-06-11T06:05:23.024Z
tags: [postgresql, database, index, sql, 성능]
canonical: https://blog.wooncloud.com/posts/postgresql-index-design
---

![넓게 펼쳐진 추상적 '데이터 풍경' 배너. 정렬된 열에 데이터베이스 블록(페이지)들이 줄지](/images/posts/postgresql-index-design/thumb-86848e1b-cf1b-45c5-8658-ebba4e1e1ae8.jpg)

업무 코드에서 이런 인덱스를 처음 만났다고 하자.

```sql
CREATE INDEX idx_fd_prj_folders_proj
  ON flow_drive_project_folders (use_intt_id, colabo_srno)
  WHERE (trashed_at IS NULL);
```

`(use_intt_id, colabo_srno)` 두 컬럼을 묶은 **복합 인덱스**인데, 끝에 `WHERE trashed_at IS NULL`이 붙어 있다. 이게 **부분 인덱스(partial index)**다. "삭제되지 않은(=활성) 폴더"만 인덱싱하겠다는 뜻이다. PostgreSQL은 이렇게 인덱스를 *필요한 행·필요한 형태로만* 정밀하게 깎는 도구가 유난히 많다. 이 글은 그 기교들을 한 번에 정리한다.

## 인덱스는 공짜가 아니다

먼저 깔고 가자. 인덱스는 읽기를 빠르게 하는 대신,

- **쓰기를 느리게** 한다. INSERT/UPDATE/DELETE마다 인덱스도 갱신된다.
- **디스크를 먹는다.** 큰 테이블에선 인덱스가 테이블만큼 커지기도 한다.
- 플래너에 약간의 **선택 비용**을 더한다.

그래서 인덱스 설계의 본질은 "최소한의 인덱스로 최대한의 쿼리를 커버"하는 것이다. 아래 기법들은 대부분 *더 작고 정확한* 인덱스를 만드는 방법이다.

## 1. 부분 인덱스 (Partial Index)

조건을 만족하는 행만 인덱싱한다.

```sql
CREATE INDEX idx_folders_active
  ON folders (use_intt_id, colabo_srno)
  WHERE trashed_at IS NULL;
```

soft delete 테이블이 대표 사례다. 보통 쿼리는 "살아있는 행"만 본다(`WHERE trashed_at IS NULL`). 그런데 시간이 지나면 삭제된 행이 테이블의 절반 이상을 차지하기도 한다. 부분 인덱스는 **살아있는 행만** 담으므로:

- 인덱스가 작아져 캐시 효율·스캔 속도가 좋아진다.
- 삭제된 행을 건드려도 이 인덱스는 갱신하지 않는다(쓰기 비용 절감).

**주의:** 플래너가 이 인덱스를 쓰려면 쿼리의 WHERE가 인덱스 술어를 **함의(imply)**해야 한다.

```sql
-- 인덱스 사용됨
SELECT * FROM folders
WHERE use_intt_id = 7 AND colabo_srno = 100 AND trashed_at IS NULL;

-- 인덱스 사용 안 됨 (trashed_at 조건이 빠짐)
SELECT * FROM folders WHERE use_intt_id = 7 AND colabo_srno = 100;
```

또 하나 강력한 용도는 **부분 유니크 인덱스**다. "활성 행 중에서만 유일"을 강제할 수 있다.

```sql
-- 한 사용자당 '활성' 폴더 이름은 유일하되, 삭제된 동명 폴더는 허용
CREATE UNIQUE INDEX uq_folder_name_active
  ON folders (use_intt_id, name)
  WHERE trashed_at IS NULL;
```

## 2. 복합 인덱스와 컬럼 순서 (Multicolumn)

`(use_intt_id, colabo_srno)`처럼 여러 컬럼을 묶는다. 핵심은 **컬럼 순서**다. B-tree 복합 인덱스는 **왼쪽 접두사(leftmost prefix)**만 효율적으로 쓸 수 있다.

```sql
CREATE INDEX ON t (a, b, c);
-- 잘 쓰임:  WHERE a = ?
--          WHERE a = ? AND b = ?
--          WHERE a = ? AND b = ? AND c = ?
-- 비효율:  WHERE b = ?            (선두 a 가 없음)
--          WHERE c = ?
```

설계 원칙:

- **등치(=) 컬럼을 앞에, 범위(`<`, `>`, `BETWEEN`)·정렬 컬럼을 뒤에.** 범위 컬럼 뒤의 컬럼은 인덱스로 더 좁히지 못한다.
- 자주 단독으로 쓰는 컬럼을 선두에.
- 선택도가 높은(값이 다양한) 컬럼을 앞에 두는 게 보통 유리하지만, 결국 **쿼리 패턴이 가장 중요**하다.

예: `WHERE use_intt_id = ? AND created_at > ?` 라면 `(use_intt_id, created_at)` — 등치 먼저, 범위 나중.

## 3. 커버링 인덱스와 INCLUDE (Index-Only Scan)

인덱스에 쿼리가 필요한 컬럼이 다 들어 있으면, PostgreSQL은 테이블(heap)을 안 보고 인덱스만으로 결과를 만든다 — **index-only scan**.

```sql
-- created_at 으로 찾고 title 만 반환하는 쿼리를 커버
CREATE INDEX ON posts (created_at) INCLUDE (title);

SELECT title FROM posts WHERE created_at > now() - interval '7 days';
```

`INCLUDE`(PG 11+) 컬럼은 인덱스 **리프에 페이로드로만** 저장된다. 검색·정렬엔 못 쓰지만 heap 접근을 없애준다. 키로 쓰지 않을 컬럼은 키에 넣지 말고 INCLUDE로 분리하라.

**단서:** index-only scan은 해당 행이 **visibility map**상 "모든 트랜잭션에 보임"으로 표시돼야 동작한다. 즉 `VACUUM`이 잘 돌아야 효과가 난다. `EXPLAIN`에 `Index Only Scan` + `Heap Fetches: 0`이면 성공.

## 4. 표현식 인덱스 (Expression Index)

컬럼이 아니라 **식의 결과**를 인덱싱한다.

```sql
-- 대소문자 무시 검색
CREATE INDEX ON users (lower(email));
SELECT * FROM users WHERE lower(email) = 'a@b.com';   -- 사용됨

-- jsonb 필드
CREATE INDEX ON events ((data->>'user_id'));
SELECT * FROM events WHERE data->>'user_id' = '42';

-- 날짜 버킷
CREATE INDEX ON logs (date_trunc('day', created_at));
```

**규칙:** 쿼리의 식과 인덱스의 식이 **정확히 일치**해야 한다. `lower(email)` 인덱스는 `email = ...`엔 쓰이지 않는다.

## 5. 인덱스 종류 고르기

PostgreSQL의 기본은 B-tree지만, 데이터 모양에 따라 다른 타입이 압도적으로 낫다.

| 타입 | 언제 |
| --- | --- |
| **B-tree** | 기본. 등치·범위·정렬. 대부분의 컬럼. |
| **Hash** | 등치(`=`)만. PG 10부터 crash-safe. 그래도 B-tree로 충분해 거의 안 씀. |
| **GIN** | 한 행에 값이 여러 개: 배열, `jsonb`, 전문검색(`tsvector`), 트라이그램. |
| **GiST** | 기하/범위 타입, 최근접 이웃(KNN), 배제 제약. |
| **SP-GiST** | 공간 분할 기반 비균형 트리. 특수 용도. |
| **BRIN** | 거대한 자연정렬 테이블(시계열·append-only). 인덱스가 아주 작다. |

### GIN — 배열·jsonb·전문검색

```sql
CREATE INDEX ON posts USING gin (tags);            -- text[] 배열
SELECT * FROM posts WHERE tags @> ARRAY['postgres'];

CREATE INDEX ON events USING gin (data);            -- jsonb 통째로
SELECT * FROM events WHERE data @> '{"type":"click"}';
```

GIN은 조회는 빠르지만 갱신이 무겁다. `fastupdate` 옵션이나 "데이터 적재 후 인덱스 생성"으로 완화한다.

### BRIN — 시계열 대용량

```sql
-- created_at 이 대체로 증가하는 거대 로그 테이블
CREATE INDEX ON logs USING brin (created_at);
```

BRIN은 블록 범위별 min/max만 저장해 **인덱스 크기가 KB 단위**다. 단, 컬럼이 물리적으로 정렬돼 있어야(상관관계가 높아야) 효과가 난다.

## 6. 연산자 클래스 (Operator Class)

같은 타입이라도 어떤 연산을 빠르게 할지 **연산자 클래스**로 바꾼다.

### LIKE 'prefix%' — `text_pattern_ops`

C 로케일이 아닌 DB에서 `LIKE 'abc%'`를 인덱스로 타려면 패턴 연산자 클래스가 필요하다.

```sql
CREATE INDEX ON users (email text_pattern_ops);
SELECT * FROM users WHERE email LIKE 'kim%';   -- 이제 인덱스 사용
```

(등치 `=`에는 필요 없다. 접두 LIKE에만.)

### 부분 일치·유사도 — pg_trgm

`LIKE '%x%'`(선두 와일드카드)나 오타 허용 검색은 트라이그램으로.

```sql
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX ON products USING gin (name gin_trgm_ops);

SELECT * FROM products WHERE name ILIKE '%note%';
SELECT * FROM products WHERE name % '노트북';     -- 유사도 검색
```

### jsonb — `jsonb_path_ops`

포함(`@>`) 위주면 더 작고 빠른 연산자 클래스가 있다.

```sql
CREATE INDEX ON events USING gin (data jsonb_path_ops);  -- @> 전용, 더 컴팩트
```

## 7. 정렬을 인덱스로 (ORDER BY)

인덱스는 정렬된 구조라 `ORDER BY`를 공짜로 해결할 수 있다. 정렬 방향·NULL 위치까지 인덱스에 박을 수 있다.

```sql
-- 최신순 페이지네이션용 인덱스
CREATE INDEX ON posts (created_at DESC, id DESC);
SELECT * FROM posts ORDER BY created_at DESC, id DESC LIMIT 20;  -- 별도 sort 없음

-- NULL 위치 맞추기
CREATE INDEX ON t (score DESC NULLS LAST);
SELECT * FROM t ORDER BY score DESC NULLS LAST;
```

복합 인덱스의 컬럼 순서·방향이 `ORDER BY`와 맞아야 정렬 단계가 사라진다.

## 8. 유니크·배제 제약

`PRIMARY KEY`·`UNIQUE`는 내부적으로 유니크 인덱스를 만든다. 더 일반적인 "겹치면 안 됨"은 **배제 제약(exclusion constraint, GiST)**으로 표현한다.

```sql
-- 같은 방의 예약 시간대가 겹치면 거부
CREATE EXTENSION IF NOT EXISTS btree_gist;
ALTER TABLE reservations ADD CONSTRAINT no_overlap
  EXCLUDE USING gist (room_id WITH =, during WITH &&);
```

(앞서 본 **부분 유니크 인덱스**도 여기 묶이는 강력한 기교다.)

## 9. 운영 기교

### 잠금 없이 만들기 — CONCURRENTLY

`CREATE INDEX`는 기본적으로 테이블에 쓰기 잠금을 건다. 운영 중엔 위험하다.

```sql
CREATE INDEX CONCURRENTLY idx_x ON t (col);
```

`CONCURRENTLY`는 쓰기를 막지 않고 만든다(대신 느리고, 트랜잭션 블록 안에선 못 쓴다). 도중 실패하면 `INVALID` 인덱스가 남으니 `DROP INDEX` 후 재시도. 재구성도 `REINDEX INDEX CONCURRENTLY`.

### 안 쓰는 인덱스 찾아 지우기

```sql
SELECT indexrelid::regclass AS index, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;
```

`idx_scan = 0`이 오래 유지된 인덱스는 쓰기 비용만 내는 죽은 무게다(PK·유니크 제약용은 제외).

### 중복·잉여 인덱스

`(a, b)` 인덱스가 있으면 `(a)` 인덱스는 보통 잉여다(왼쪽 접두사로 커버됨). 같은 컬럼 집합의 인덱스가 둘 있지 않은지 점검하라.

### fillfactor

UPDATE가 잦은 인덱스는 페이지에 여유를 둬 페이지 분할과 블로트를 줄인다.

```sql
CREATE INDEX ON t (col) WITH (fillfactor = 80);
```

### 검증은 EXPLAIN으로

```sql
EXPLAIN (ANALYZE, BUFFERS)
SELECT ...;
```

인덱스를 만들었으면 반드시 `EXPLAIN`으로 `Index Scan`/`Index Only Scan`이 뜨는지, 의도대로 타는지 확인한다. 안 타면 통계부터 의심(`ANALYZE`).

## 10. 인덱스가 안 쓰이는 흔한 이유

만들었는데 seq scan이 뜬다면 대개 이 중 하나다.

- 컬럼에 **함수/연산**을 씌웠는데 표현식 인덱스가 없음 (`WHERE lower(x) = ...` ← `lower(x)` 인덱스 필요).
- **타입 불일치**(예: `bigint` 컬럼을 문자열과 비교) → 암묵 캐스팅이 인덱스를 무력화.
- **선택도가 낮음** — 전체의 상당 비율을 가져오면 seq scan이 더 싸서 플래너가 일부러 안 쓴다(정상).
- `LIKE '%x%'`처럼 **선두 와일드카드**(→ 트라이그램 필요).
- 통계가 낡음 → `ANALYZE`.
- 테이블이 작음 → 그냥 seq scan이 빠름(정상).

## 정리

- 인덱스는 *작고 정확*할수록 좋다. PostgreSQL은 그걸 위한 도구가 풍부하다.
- **부분 인덱스**로 필요한 행만(soft delete의 활성 행), **INCLUDE**로 heap 접근 제거, **표현식 인덱스**로 식을 인덱싱, **복합 인덱스의 컬럼 순서**로 여러 쿼리를 한 인덱스로 커버.
- 데이터 모양이 특수하면 **GIN(jsonb·배열·검색)·BRIN(시계열)·GiST(범위·배제)**로 갈아탄다.
- 운영에선 **CONCURRENTLY**로 안전하게 만들고, **pg_stat_user_indexes**로 죽은 인덱스를 솎고, 항상 **EXPLAIN**으로 검증한다.

처음 본 그 `WHERE trashed_at IS NULL` 한 줄이, 사실은 이 모든 설계 사고로 들어가는 입구였던 셈이다.