PostgreSQL 인덱스 완전정복 — B-tree·GIN·GiST·BRIN 언제 어떻게 쓰나

인덱스 타입 한눈에 — 어떤 상황에 무엇을 쓰나

결론부터. 일반적인 등호·부등호·정렬·범위 조회는 B-tree(기본값)면 99% 해결된다. 나머지는 데이터 모양이 특수할 때만 쓴다. JSONB나 배열, 전문검색처럼 "한 컬럼 안에 여러 값"이 들어 있으면 GIN, 범위형·기하·근접 검색이면 GiST, 값이 물리적 저장 순서와 상관관계가 강한 초대형 테이블이면 BRIN이다.

튜닝에 앞서 알아두면 좋은 핵심 기본값과 수치. 대부분 이 값들을 그대로 두다가 발목 잡힌다.

B-tree — 90%는 이걸로 끝난다

B-tree는 정렬된 트리 구조라 등호(=), 범위(< > BETWEEN), IN, 그리고 ORDER BY까지 한 인덱스로 처리한다. CREATE INDEX에 타입을 안 적으면 자동으로 B-tree다. 정렬돼 있으니 ORDER BY created_at DESC LIMIT 20 같은 상위 N개 조회에서 정렬 자체를 건너뛴다 — 이게 B-tree의 숨은 강점이다.

-- 기본 B-tree
CREATE INDEX idx_users_email ON users (email);

-- 정렬 방향까지 맞추면 ORDER BY에서 정렬 단계 생략
CREATE INDEX idx_posts_created ON posts (created_at DESC);

-- NULL 위치도 ORDER BY ... NULLS LAST와 맞추기
CREATE INDEX idx_posts_score ON posts (score DESC NULLS LAST);

자주 놓치는 것. B-tree는 LIKE 'abc%'처럼 앞이 고정된 패턴은 탈 수 있지만 LIKE '%abc'나 ILIKE '%abc%'는 못 탄다. 후자는 pg_trgm + GIN이 필요하다. 그리고 C 로케일이 아닌 DB에서 LIKE 인덱스를 쓰려면 text_pattern_ops 오퍼레이터 클래스를 붙여야 한다 — 이거 몰라서 "인덱스 걸었는데 왜 안 타냐"로 반나절 날린 적 있다.

-- 로케일이 C가 아닌 DB에서 LIKE 'abc%'를 인덱스로 태우려면
CREATE INDEX idx_users_name_pat ON users (name text_pattern_ops);
SELECT * FROM users WHERE name LIKE 'kim%';  -- 이제 인덱스 탐

복합 인덱스 — 컬럼 순서가 성능을 가른다

복합 인덱스는 여러 컬럼을 하나로 묶는다. 핵심 규칙은 딱 하나: 등호(=) 컬럼을 앞에, 범위(< > BETWEEN) 컬럼을 뒤에 둔다. B-tree가 왼쪽 컬럼부터 순서대로 좁혀 들어가기 때문이다. 앞 컬럼이 범위면 그 뒤 컬럼은 정렬 이점을 못 살린다.

예를 들어 WHERE tenant_id = 42 AND created_at >= '2026-01-01' ORDER BY created_at라면 (tenant_id, created_at)가 맞다. 반대로 (created_at, tenant_id)로 걸면 tenant_id 등호가 스캔 범위를 좁히는 데 제대로 못 쓰인다.

복합 인덱스는 "왼쪽 접두(leftmost prefix)"만 단독으로 쓸 수 있다. (a, b)는 WHERE a=? 단독엔 쓰지만 WHERE b=? 단독은 잘 못 쓴다. 그래서 "가장 자주, 등호로 걸리는 컬럼"을 맨 앞에 놓는 게 원칙이다. 카디널리티가 높은 컬럼을 앞에 두라는 조언도 있지만, 나는 그보다 쿼리 패턴을 먼저 본다. 안 걸리는 컬럼의 카디널리티는 의미가 없으니까.

-- 멀티테넌트 목록: tenant 등호, 시간 범위+정렬
CREATE INDEX idx_posts_tenant_time
  ON posts (tenant_id, created_at DESC);

-- 이 쿼리가 위 인덱스를 최적으로 탄다
SELECT id, title FROM posts
WHERE tenant_id = 42
  AND created_at >= now() - interval '7 days'
ORDER BY created_at DESC
LIMIT 20;

커버링 인덱스 — 테이블을 아예 안 읽게

커버링 인덱스는 쿼리가 필요로 하는 컬럼을 인덱스 안에 다 담아 테이블(heap) 접근 없이 인덱스만 읽고 끝낸다. EXPLAIN에서 Index Only Scan으로 나오면 성공. PG 11부터 INCLUDE 절로 "검색엔 안 쓰지만 반환할" 컬럼을 리프에 얹을 수 있다.

-- email로 검색하고 id, name만 반환하는 쿼리를 커버
CREATE INDEX idx_users_email_cover
  ON users (email) INCLUDE (id, name);

-- heap을 안 건드리고 Index Only Scan
SELECT id, name FROM users WHERE email = 'a@b.com';

부분 인덱스 — 필요한 행만 색인해서 크기를 줄인다

부분(partial) 인덱스는 WHERE 조건을 만족하는 행만 색인한다. 조회 조건이 거의 항상 고정돼 있을 때 인덱스 크기를 극적으로 줄이고 쓰기 부담도 던다. 대표 사례가 소프트 딜리트 — 죽은 행까지 색인할 이유가 없다.

-- 활성 행만 색인
CREATE INDEX idx_orders_active
  ON orders (user_id) WHERE status = 'active';

-- 쿼리에도 같은 조건이 있어야 인덱스를 탄다
SELECT * FROM orders WHERE user_id = 7 AND status = 'active';

-- NULL 아닌 값만 색인해 유니크 제약을 부분 적용
CREATE UNIQUE INDEX uq_users_ssn
  ON users (ssn) WHERE ssn IS NOT NULL;

함정 하나. 부분 인덱스가 쓰이려면 쿼리의 WHERE가 인덱스 조건을 포함(imply)해야 한다. 조건을 상수로 박아야 하고, 파라미터 바인딩(status = $1)으로 넘기면 플래너가 일치를 증명 못 해 건너뛰기도 한다. 문서에 잘 안 나오는데, 부분 인덱스가 안 탈 때 열에 아홉은 이 문제다.

표현식 인덱스 — 함수 결과에 색인을

컬럼에 함수를 씌워 조회하면(WHERE lower(email) = ...) 일반 인덱스는 안 탄다. 함수 결과에 색인하는 게 표현식(expression) 인덱스다. 대소문자 무시 검색, JSON 필드 추출 등에 쓴다.

-- 대소문자 무시 이메일 조회
CREATE INDEX idx_users_lower_email ON users (lower(email));
SELECT * FROM users WHERE lower(email) = 'a@b.com';  -- 인덱스 탐

-- JSONB의 특정 키를 자주 조회하면 그 표현식에 색인
CREATE INDEX idx_events_type ON events ((payload->>'type'));
SELECT * FROM events WHERE payload->>'type' = 'signup';

주의할 점은 쿼리의 표현식이 인덱스 정의와 글자 그대로 일치해야 한다는 것. lower(email)로 만들었으면 쿼리도 lower(email)이어야 하고, email ILIKE ... 같은 다른 표현으로는 못 탄다.

GIN·GiST·BRIN — 특수 데이터를 위한 인덱스

한 컬럼 안에 여러 값이 들어 있으면(JSONB, 배열, 텍스트 토큰) GIN이다. 각 요소를 역색인처럼 매핑해 @>(포함), ?(키 존재), &&(교집합), 전문검색 @@를 빠르게 처리한다. 대신 인덱스가 크고 쓰기가 느리다.

-- JSONB 포함 조회 / 키 존재 (@> 위주면 jsonb_path_ops가 더 작음)
CREATE INDEX idx_docs_data ON docs USING gin (data);
SELECT * FROM docs WHERE data @> '{"status":"open"}';

-- 부분일치(LIKE '%foo%')를 인덱스로: pg_trgm + GIN
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX idx_users_name_trgm ON users USING gin (name gin_trgm_ops);
SELECT * FROM users WHERE name ILIKE '%kim%';  -- 이제 인덱스 탐

-- 전문검색
CREATE INDEX idx_articles_fts
  ON articles USING gin (to_tsvector('simple', body));

GiST는 범위형·기하 데이터의 겹침/근접 조회에 쓴다. 예약 시간대 겹침(&&), 좌표 최근접(<->) 정렬, PostGIS 공간 검색이 전형적이다. exclusion 제약과 함께 쓰면 "같은 방에 시간 겹치는 예약 금지"를 DB 레벨에서 강제한다.

-- 시간대 겹침 검색 + 겹침 금지 제약
CREATE EXTENSION IF NOT EXISTS btree_gist;
CREATE INDEX idx_bookings_during ON bookings USING gist (during);
ALTER TABLE bookings ADD CONSTRAINT no_overlap
  EXCLUDE USING gist (room_id WITH =, during WITH &&);

BRIN은 수억 행 규모에서 빛난다. 블록 범위(기본 128페이지)마다 최소/최대만 요약해 인덱스가 B-tree의 수백~수천분의 1 크기다. 단 전제가 있다 — 색인 컬럼 값이 물리적 저장 순서와 상관관계가 높아야 한다. 시간순으로 append되는 로그 created_at이 딱 맞고, 무작위로 흩어져 있으면 거의 쓸모없다.

-- 시계열 로그: created_at이 append 순서와 일치할 때 최고
CREATE INDEX idx_logs_created_brin ON logs USING brin (created_at);
-- 범위 조회에서 대부분의 블록을 건너뛴다
SELECT count(*) FROM logs
WHERE created_at >= '2026-09-01' AND created_at < '2026-09-02';

EXPLAIN으로 "진짜로 타는지" 확인하기

인덱스를 걸었으면 반드시 EXPLAIN (ANALYZE, BUFFERS)로 확인한다. 걸었다고 다 타는 게 아니다. 플래너가 "전체 훑는 게 싸다"고 판단하면 무시한다. 계획 노드를 읽는 법.

• Seq Scan — 테이블 전체 읽음. 소량이면 정상, 대량인데 이거면 의심.
• Index Scan — 인덱스로 위치 찾고 heap 읽음. 보통 좋음.
• Index Only Scan — 인덱스만으로 결과 완성. 최고(단 Heap Fetches 값 확인).
• Bitmap Heap Scan — 여러 인덱스 결과를 비트맵으로 모아 heap 읽음. 중간 선택도에서 흔하고 정상.
• rows= 추정 vs 실제(ANALYZE에서 actual rows) — 크게 어긋나면 통계 문제니 ANALYZE.

-- 실제 실행하며 시간·버퍼까지 (운영에선 트랜잭션으로 감싸 롤백)
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, title FROM posts
WHERE tenant_id = 42 AND created_at >= now() - interval '7 days'
ORDER BY created_at DESC LIMIT 20;

-- 통계가 오래됐다 싶으면
ANALYZE posts;

인덱스가 안 타는 흔한 이유

"인덱스 걸었는데 Seq Scan이에요"는 튜닝 질문의 절반이다. 원인은 대개 정해져 있고, 아래 순서로 점검하면 잡힌다.

• 컬럼에 함수를 씌웠다. WHERE date(created_at) = ..., WHERE lower(email) = ...는 일반 인덱스 못 탐 → 범위 조건으로 바꾸거나 표현식 인덱스 사용.
• 타입 불일치. varchar 컬럼을 WHERE col = 123(정수)로 비교하면 암묵 캐스팅으로 인덱스를 놓친다.
• 앞 와일드카드. LIKE '%foo%'는 B-tree 불가 → pg_trgm GIN 필요.
• 선택도가 낮다. 조건에 맞는 행이 테이블의 큰 비중(대략 5~10% 이상)이면 플래너가 Seq Scan을 고른다 — 대개 옳은 선택이다.
• 통계가 낡았다. 대량 적재 후 ANALYZE를 안 돌리면 행수를 오판한다.
• 복합 인덱스 접두 미준수. (a, b)인데 WHERE b=?만 걸었다.
• OR로 묶었다. a=? OR b=?는 두 컬럼에 각각 인덱스가 있어야 Bitmap OR로 합쳐진다. 한쪽만 있으면 Seq Scan.
• 부분 인덱스 조건 불일치. 쿼리 WHERE가 인덱스의 WHERE를 포함하지 못함.
• random_page_cost가 높다. SSD인데 기본값(4.0) 그대로면 인덱스 비용이 과대평가된다. 1.1 전후로 낮춰보라.

-- SSD 환경이면 인덱스 스캔 비용 추정을 현실화
ALTER SYSTEM SET random_page_cost = 1.1;
SELECT pg_reload_conf();

-- 안 쓰이는 인덱스 찾아 정리 (idx_scan=0이 후보)
SELECT relname, indexrelname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY relname;

운영 팁 — 만들 때·지울 때

운영 중인 테이블에 그냥 CREATE INDEX하면 쓰기 잠금이 걸려 서비스가 멈춘다. 반드시 CONCURRENTLY를 쓴다. 대신 트랜잭션 안에서는 못 쓰고, 실패 시 INVALID 인덱스가 남으니 확인·재생성이 필요하다.

-- 잠금 없이 온라인으로 생성 (트랜잭션 밖에서)
CREATE INDEX CONCURRENTLY idx_posts_tenant_time
  ON posts (tenant_id, created_at DESC);

-- 실패로 남은 무효 인덱스 확인
SELECT indexrelid::regclass FROM pg_index WHERE NOT indisvalid;

-- 팽창한 인덱스를 잠금 최소화로 재구축 (PG 12+)
REINDEX INDEX CONCURRENTLY idx_posts_tenant_time;

-- 삭제도 온라인으로
DROP INDEX CONCURRENTLY idx_unused;

마지막으로 개인적인 원칙 하나. 인덱스는 쿼리를 보고 만든다. "많이 조회할 것 같으니 미리"는 대개 안 쓰이는 인덱스로 끝난다. 느린 쿼리를 pg_stat_statements로 잡고, EXPLAIN으로 병목을 확인하고, 딱 맞는 인덱스를 붙이고, 다시 EXPLAIN으로 검증한다. 이 순환이 전부다.

자주 묻는 질문

인덱스는 많이 걸수록 좋은가요?

아니요. 조회는 빨라지지만 INSERT/UPDATE/DELETE마다 모든 관련 인덱스를 갱신해야 해서 쓰기가 느려지고 디스크와 WAL도 늘어납니다. 실제로 쓰이는(EXPLAIN에서 타는) 인덱스만 남기고, pg_stat_user_indexes에서 idx_scan = 0인 것은 정리하세요.

복합 인덱스 컬럼 순서는 어떻게 정하나요?

등호(=)로 거는 컬럼을 앞에, 범위(< > BETWEEN)나 정렬용 컬럼을 뒤에 둡니다. 그리고 인덱스는 왼쪽 접두만 단독으로 쓸 수 있으니, 가장 자주 등호로 걸리는 컬럼을 맨 앞에 배치하세요. 카디널리티보다 실제 쿼리 패턴이 우선입니다.

JSONB 컬럼엔 어떤 인덱스를 써야 하나요?

@> 포함이나 키 존재(?) 조회가 많으면 GIN입니다. @> 위주라면 더 작고 빠른 jsonb_path_ops 오퍼레이터 클래스를 쓰세요. 특정 키 하나만 자주 조회한다면 GIN 대신 그 키에 대한 표현식 B-tree 인덱스(((data->>'type')))가 더 작고 빠를 때가 많습니다.

LIKE '%검색어%' 부분일치를 인덱스로 처리할 수 있나요?

네. pg_trgm 확장을 켜고 gin_trgm_ops로 GIN 인덱스를 만들면 LIKE '%foo%'와 ILIKE도 인덱스를 탑니다. 일반 B-tree는 앞이 고정된 'foo%'만 처리할 수 있고 앞 와일드카드는 불가능합니다.

인덱스를 걸었는데도 Seq Scan이 나오는 이유는?

조건에 맞는 행이 테이블의 큰 비중이면 플래너가 전체 스캔을 더 싸다고 판단한 것으로, 대개 옳은 선택입니다. 그 외엔 컬럼에 함수 적용, 타입 불일치, 통계 낡음(ANALYZE 필요), 복합 인덱스 접두 미준수, SSD인데 random_page_cost가 기본값(4.0)인 경우를 점검하세요.

운영 중인 큰 테이블에 인덱스를 안전하게 추가하려면?

CREATE INDEX CONCURRENTLY를 쓰면 쓰기 잠금 없이 온라인으로 만듭니다. 단 트랜잭션 블록 안에서는 실행할 수 없고, 중간에 실패하면 INVALID 인덱스가 남으니 pg_index에서 indisvalid = false를 확인해 지우고 다시 만드세요.