Quick Comparison

CREATE INDEX idx_users_email ON users USING btree (email);
CREATE INDEX idx_docs_tags ON docs USING gin (tags);
CREATE INDEX idx_logs_created_at_brin ON logs USING brin (created_at);

일반 equality·range·정렬은 B-tree에서 시작합니다. 한 행의 여러 요소를 찾으면 GIN, range·공간·거리 연산이면 해당 operator class가 있는 GiST, 값과 물리 저장 순서가 강하게 상관된 대형 append 테이블이면 BRIN을 검토합니다. 인덱스 이름만으로 선택하지 말고 사용하려는 연산자와 EXPLAIN (ANALYZE, BUFFERS) 결과를 함께 확인합니다.

갈리는 기준

B-tree는 기본값이자 가장 넓은 선택지다

PostgreSQL에서 USING을 생략하고 CREATE INDEX를 만들면 보통 B-tree가 쓰입니다. 동등 비교, 범위 조건, 정렬, unique 제약에 폭넓게 맞습니다.

CREATE INDEX idx_orders_created_at
ON orders (created_at DESC);

SELECT *
FROM orders
WHERE created_at >= NOW() - INTERVAL '7 days'
ORDER BY created_at DESC;

일반적인 ID, email, timestamp, foreign key 조회는 B-tree를 먼저 검토합니다. 다른 인덱스 방식은 데이터 구조나 연산자가 B-tree로 해결되지 않을 때 선택합니다.

GIN은 한 행 안에 여러 검색 항목이 있을 때 강하다

GIN은 하나의 행이 여러 key를 품는 데이터에 적합합니다. 배열 원소, JSONB key/value, tsvector 토큰처럼 내부 요소를 역색인하는 방식입니다.

CREATE INDEX idx_articles_search
ON articles USING gin (search_vector);

CREATE INDEX idx_products_attrs
ON products USING gin (attributes jsonb_path_ops);

GIN은 읽기 검색에는 강하지만 쓰기 비용과 인덱스 크기가 커질 수 있습니다. 자주 갱신되는 JSONB나 배열 컬럼에 무조건 GIN을 붙이면 쓰기 부하가 커질 수 있습니다.

특수 인덱스

GiST는 범위, 공간, nearest-neighbor 성격의 검색에 쓰입니다. built-in range type이나 PostGIS 같은 extension의 operator class처럼 “겹치는가”, “가까운가”를 묻는 문제에 등장합니다. GiST 자체가 모든 타입·연산을 지원하는 것은 아니므로, 대상 타입의 operator class와 지원 연산자를 함께 확인해야 합니다.

CREATE INDEX idx_bookings_period
ON bookings USING gist (during);

BRIN은 매우 큰 테이블에서 값이 물리 저장 순서와 어느 정도 같이 움직일 때 효과적입니다. 예를 들어 append-only 로그의 created_at처럼 뒤에 쌓일수록 시간이 커지는 컬럼은 작은 BRIN 인덱스로 많은 block을 제외할 수 있습니다. 값이 무작위로 흩어졌다면 후보 block이 늘어 효과가 작으므로 상관관계를 관측해야 합니다.

CREATE INDEX idx_logs_created_at_brin
ON logs USING brin (created_at);

선택 기준

주의할 점