PostgreSQL 인덱스는 왜 이렇게 많은가

PostgreSQL에는 B-Tree, Hash, GiST, GIN, BRIN, Bloom — 6가지 인덱스 타입이 있다. MySQL에는 사실상 B-Tree 하나다. 왜 PostgreSQL은 이렇게 많은 인덱스 타입을 만들었을까? 그리고 이 선택들 사이에서 어떤 원칙이 반복되는가?

하나의 인덱스가 모든 것을 커버할 수 없는 이유

B-Tree는 1차원 순서가 있는 데이터에 최적화되어 있다. 정수, 날짜, 문자열처럼 “크다/작다”를 정의할 수 있는 값들 사이에서는 탁월하다. 하지만 JSONB의 {"status": "active"}가 문서에 포함되는지, 두 시간 구간이 겹치는지, 어떤 좌표가 반경 1km 안에 있는지를 B-Tree로 효율적으로 인덱싱할 방법은 없다.

PostgreSQL의 인덱스 다양성은 “범용성의 포기”에서 시작한다. 각 인덱스 타입은 특정 질문에만 답하는 대신, 그 질문에 대해서는 최적에 가까운 성능을 낸다.

데이터 형태별 인덱스 선택

각 타입의 설계 동기를 보면 패턴이 보인다.

B-Tree는 범용 기본값이다. 등가, 범위, 정렬, LIKE 'prefix%'를 모두 처리한다. 리프 노드끼리 연결되어 있어 범위 스캔이 효율적이고, 복합 인덱스에서 선두 컬럼 기반 부분 활용도 가능하다. 단, 리프 노드에는 키와 TID만 있고 실제 행 데이터는 Heap에 따로 있다. PK 조회조차 Index Scan + Heap Fetch 두 단계를 거치는 이유다.

GIN은 역색인이다. JSONB, 배열, tsvector처럼 하나의 값이 여러 원소를 포함할 때 사용한다. 원소 키마다 그것을 포함하는 Heap TID 목록을 저장한다. WHERE data @> '{"type": "click"}' 쿼리는 "type"="click" 키의 포스팅 리스트를 곧바로 조회한다. 대신 INSERT 하나가 JSONB의 키 수만큼 인덱스 업데이트를 발생시키므로 쓰기 비용이 크다. fastupdate 옵션은 이 비용을 Pending List로 미루는 방식으로 완화하지만, 병합 시점에 지연이 발생할 수 있다는 트레이드오프가 있다.

GiST는 확장 가능한 트리 프레임워크다. 7개의 함수(consistent, union, penalty, picksplit 등)를 구현하면 어떤 데이터 타입도 GiST 인덱스로 만들 수 있다. 공간 데이터는 Bounding Box로, 범위 타입은 &&(겹침) 연산으로 처리된다. 예약 시스템의 중복 방지를 EXCLUDE USING GIST (room_id WITH =, time_range WITH &&) 한 줄로 표현할 수 있는 것도 GiST 덕분이다.

BRIN은 가장 단순하다. 블록 범위마다 최솟값과 최댓값만 저장한다. 100GB 테이블의 BRIN 인덱스가 100MB 미만인 이유다. 단, 물리적 저장 순서와 컬럼 값의 순서가 일치할 때만 효과적이다. 타임스탬프 순으로 삽입되는 로그 테이블이 이상적인 사용 사례다. 랜덤 삽입이나 UPDATE가 빈번한 테이블에서는 모든 블록 범위가 동일한 min/max를 갖게 되어 필터링 효과가 사라진다.

Index-Only Scan — 설계의 핵심 보상

각 인덱스 타입을 이해하면 Index-Only Scan이 왜 특별한지 보인다. PostgreSQL B-Tree의 리프 노드는 키와 TID만 저장한다. “이 튜플이 내 스냅샷에서 보이는가”를 확인하려면 Heap을 읽어야 한다. MVCC 가시성 정보가 인덱스에 없기 때문이다.

Index-Only Scan이 가능한 조건은 두 가지다. 쿼리에 필요한 모든 컬럼이 인덱스에 포함되어야 하고(Covering Index), Heap 페이지가 “All-Visible” 상태여야 한다. All-Visible 비트는 VACUUM이 설정한다. 즉, VACUUM을 주기적으로 실행하는 것이 Index-Only Scan의 실질적인 전제 조건이다.

-- VACUUM 전: Heap Fetches > 0
EXPLAIN (ANALYZE) SELECT user_id, amount FROM orders WHERE user_id = 42;

-- VACUUM 후: Heap Fetches = 0
VACUUM orders;
EXPLAIN (ANALYZE) SELECT user_id, amount FROM orders WHERE user_id = 42;

Partial Index와 Expression Index — 인덱스 크기를 줄이는 두 가지 방법

인덱스 타입 선택 못지않게 중요한 것이 인덱스 범위다.

Partial Index는 WHERE 절로 인덱싱 대상을 제한한다. 99%가 'DONE' 상태인 orders 테이블에서 'PENDING'인 주문만 조회한다면:

CREATE INDEX ON orders(created_at) WHERE status = 'PENDING';

전체 인덱스 대비 1%의 크기로 동일한 성능을 낸다. Soft Delete 패턴(WHERE deleted_at IS NULL), 조건부 유니크 제약 같은 경우에도 자연스럽게 적용된다.

Expression Index는 컬럼 값 대신 함수 결과를 인덱싱한다. WHERE lower(email) = ? 쿼리에 일반 email 인덱스는 쓸모가 없다. lower() 변환 후 값을 비교하기 때문이다.

CREATE INDEX ON users(lower(email));
-- 이제 WHERE lower(email) = 'alice@example.com' 에서 인덱스 사용 가능

함수가 IMMUTABLE이어야 한다는 조건이 있다. NOW()나 random() 같은 VOLATILE 함수는 사용할 수 없다.

EXPLAIN ANALYZE로 설계를 검증한다

인덱스를 만들었다고 끝이 아니다. 플래너가 그 인덱스를 사용하는지 확인해야 한다.

EXPLAIN (ANALYZE, BUFFERS)
SELECT user_id, amount FROM orders WHERE user_id = 42;

rows=10 actual rows=50000처럼 추정치와 실제값이 크게 다르면 통계가 낡았다는 신호다. ANALYZE orders로 통계를 갱신하고, 특정 컬럼의 분포가 복잡하다면 statistics_target을 높인다.

Buffers: shared hit=N과 shared read=M의 비율도 중요하다. shared read가 많다면 캐시 미스가 잦다는 뜻이다. Index-Only Scan에서 Heap Fetches: 0을 확인하면 VACUUM이 제대로 동작했다는 증거가 된다.

정리

• B-Tree는 기본값이다. 등가, 범위, 정렬을 처리하지만 JSONB·공간·시계열에는 맞지 않는다.
• GIN은 역색인이다. JSONB @>, 배열 &&, 전문 검색 @@에 최적이지만 쓰기 비용이 크다.
• GiST는 프레임워크다. 공간 데이터와 범위 타입, EXCLUDE 제약의 기반이다.
• BRIN은 초경량이다. 물리 순서가 보장된 시계열 데이터에서만 효과적이다.
• Index-Only Scan은 VACUUM이 All-Visible 비트를 설정했을 때만 가능하다.
• Partial Index와 Expression Index로 인덱스 수와 크기를 줄일 수 있다.

다음 글에서는 MVCC와 VACUUM이 어떻게 동작하는지, 그리고 autovacuum이 All-Visible 비트 설정 외에 어떤 일을 하는지 추적한다.