B-tree만 알던 내가 GIN 인덱스 보고 눈 돌아간 썰 (PostgreSQL 인덱스 완벽 정리)

B-tree만 알던 내가 GIN 인덱스 보고 눈 돌아간 썰 (PostgreSQL 인덱스 완벽 정리)

테스트 환경: PostgreSQL 17, Rocky Linux 9 / Oracle 19c 비교 기준

Oracle DBA에게 인덱스는 곧 B-tree입니다. 비트맵 인덱스도 있지만 OLTP에선 잘 안 쓰죠. 그래서 저도 "인덱스 = B-tree" 공식으로 15년을 살았습니다.

그런데 PostgreSQL을 배우다가 이런 걸 봤습니다.

-- JSONB 컬럼을 통째로 인덱싱?!
CREATE INDEX idx_data ON events USING GIN (data);

-- 10억 건 테이블 인덱스가 B-tree의 1/8500 크기?!
CREATE INDEX idx_time ON logs USING BRIN (created_at);

"이게 무슨 마법이지?" 눈이 돌아갔습니다.

PostgreSQL은 인덱스 종류가 7가지나 됩니다. B-tree는 그중 기본값일 뿐이고, 나머지가 진짜 무기입니다. JSONB, 배열, 전문검색, 대용량 시계열... Oracle에서 골치 아프던 것들을 인덱스 하나로 해결합니다.

이번 편은 실무 시리즈의 두 번째 글입니다.

• PostgreSQL 인덱스 7종 지도
• ★ GIN - JSONB·배열의 게임 체인저
• ★ BRIN - 대용량 테이블의 마법 (B-tree의 1/8500!)
• GiST - 공간·범위 데이터
• 부분 인덱스·표현식 인덱스 (Oracle에도 있지만 강력)
• 언제 뭘 써야 하나 (실전 가이드)

바쁘신 분은 인덱스 선택표부터 보세요.

 

PostgreSQL 인덱스 7종 지도

일단 전체 그림입니다. Oracle과 비교하면 이렇습니다.

Oracle 주요 인덱스:
- B-tree (대부분)
- Bitmap (DW용)
- Function-based
- Reverse key 등

PostgreSQL 인덱스 (7종):
- B-tree  ← 기본 (Oracle과 동일 용도)
- Hash    ← 등호(=) 전용
- GIN     ← JSONB, 배열, 전문검색 (★)
- GiST    ← 공간, 범위, 유사도 (★)
- SP-GiST ← 공간 분할
- BRIN    ← 대용량 정렬 데이터 (★)
- Bloom   ← 다중 컬럼 필터

핵심: Oracle DBA가 새로 배워야 할 건 GIN, BRIN, GiST 세 가지입니다. 나머지는 특수 목적이라 필요할 때 찾아보면 됩니다.

 

기본: B-tree (이건 Oracle과 똑같다)

먼저 익숙한 것부터. B-tree는 Oracle과 사실상 동일합니다.

-- 기본 인덱스 (USING 안 쓰면 B-tree)
CREATE INDEX idx_emp_name ON employees (emp_name);

-- 복합 인덱스
CREATE INDEX idx_emp_dept_name ON employees (dept_id, emp_name);

-- 유니크 인덱스
CREATE UNIQUE INDEX idx_emp_email ON employees (email);

언제 쓰나: =, <, >, BETWEEN, ORDER BY, LIKE 'prefix%'. 즉 대부분의 일반 쿼리. Oracle과 똑같이 생각하면 됩니다.

여기까진 편합니다. 문제는(아니 재미는) 지금부터입니다.

 

★ GIN - JSONB와 배열의 게임 체인저

GIN(Generalized Inverted iNdex) 은 "한 컬럼에 여러 값이 들어있을 때" 쓰는 인덱스입니다. Oracle엔 마땅한 대응이 없습니다.

문제 상황: JSONB 검색이 느리다

2편에서 본 JSONB를 기억하시나요? 이렇게 검색하면:

CREATE TABLE events (id SERIAL, data JSONB);

-- JSONB 안의 값으로 검색 (인덱스 없으면 풀스캔!)
SELECT * FROM events WHERE data @> '{"action": "login"}';

데이터가 많으면 이 쿼리는 테이블 전체를 스캔합니다. 느립니다.

해결: GIN 인덱스

-- JSONB에 GIN 인덱스!
CREATE INDEX idx_events_data ON events USING GIN (data);

-- 이제 @> 검색이 인덱스를 탄다
SELECT * FROM events WHERE data @> '{"action": "login"}';

@>(포함) 연산자로 JSONB 내부를 검색하는데 인덱스가 걸립니다. Oracle에서 JSON 검색하려면 별도 함수 인덱스나 복잡한 설정이 필요한데, PostgreSQL은 GIN 한 방입니다.

배열 검색에도

-- 배열 컬럼 (2편 참고)
CREATE TABLE products (id SERIAL, tags TEXT[]);
CREATE INDEX idx_products_tags ON products USING GIN (tags);

-- 배열 안에 특정 값이 있는 행 검색 (인덱스 탐)
SELECT * FROM products WHERE tags @> ARRAY['전자'];

전문검색(Full-text search)에도

-- 텍스트 전문검색용 GIN
CREATE INDEX idx_doc_search ON documents 
USING GIN (to_tsvector('english', content));

Oracle의 Oracle Text에 해당하는 기능을 GIN으로 구현합니다.

⚠️ GIN의 대가

공짜는 없습니다.

GIN 장점: JSONB/배열/전문검색 초고속
GIN 단점: 쓰기(INSERT/UPDATE)가 느림
         → 행 하나 넣을 때 여러 인덱스 엔트리 생성

→ 읽기 많고 쓰기 적은 테이블에 최적
→ 대량 INSERT 시엔 인덱스를 나중에 만드는 게 나음

 

★ BRIN - 대용량 테이블의 마법

이게 제 눈이 제일 돌아간 부분입니다. BRIN(Block Range INdex) 은 대용량 테이블에서 B-tree의 수천분의 1 크기로 같은 일을 합니다.

마법의 원리

B-tree: 모든 행의 위치를 저장 (정확하지만 큼)
BRIN:   블록 범위별 요약(min/max)만 저장 (작지만 조건부)

예: 로그 테이블 (created_at 순서로 쌓임)
→ "이 블록 범위는 1월 1일~1월 2일 데이터"
→ 이 요약만 저장
→ 범위 검색 시 관련 없는 블록은 통째로 건너뜀

실측 데이터에 따르면 BRIN이 B-tree보다 약 8,500배 작습니다. 10억 건 테이블에서 이 차이는 어마어마합니다.

실전 예시

-- 로그 테이블 (시간순으로 쌓이는 대용량)
CREATE TABLE logs (
    id BIGSERIAL,
    created_at TIMESTAMP,
    message TEXT
);

-- BRIN 인덱스 (created_at은 물리적으로 정렬됨)
CREATE INDEX idx_logs_time ON logs USING BRIN (created_at);

-- 특정 날짜 범위 검색 (BRIN이 효율적)
SELECT count(*) FROM logs 
WHERE created_at BETWEEN '2026-01-10' AND '2026-01-11';

★ BRIN의 결정적 조건

BRIN이 효과적: 데이터가 물리적으로 정렬되어 있을 때
   예: created_at, id처럼 순차적으로 쌓이는 컬럼
   (로그, 이벤트, 시계열 데이터)

BRIN이 무용지물: 데이터가 랜덤하게 흩어져 있을 때
   예: 이메일, 이름처럼 순서 없는 컬럼

핵심: 로그·이벤트·시계열처럼 계속 쌓이기만 하는(append-only) 대용량 테이블의 시간 컬럼에 BRIN을 쓰면, 디스크는 거의 안 쓰면서 범위 검색을 빠르게 합니다. Oracle에는 없는 개념이라 특히 유용합니다.

PostgreSQL 17 개선: 병렬 BRIN 인덱스 빌드가 추가되어 대용량 테이블에서 인덱스 생성 시간이 크게 줄었습니다.

 

GiST - 공간·범위 데이터

GiST(Generalized Search Tree) 는 "등호가 주가 아닌" 데이터에 씁니다. 대표적으로 공간 데이터와 범위 타입입니다.

-- 범위 타입 겹침 검색 (예: 예약 시스템)
CREATE TABLE reservations (
    room_id INT,
    during TSRANGE   -- 시간 범위 타입
);
CREATE INDEX idx_res_during ON reservations USING GIST (during);

-- 시간대가 겹치는 예약 찾기 (&& = 겹침 연산자)
SELECT * FROM reservations 
WHERE during && '[2026-07-01 14:00, 2026-07-01 16:00)'::tsrange;

대표 용도:

• 지리 정보(PostGIS): "반경 5km 내 매장"
• 범위 타입: "겹치는 예약 찾기"
• 유사도 검색

Oracle Spatial에 해당하는 기능을 GiST + PostGIS로 구현합니다. 지도 서비스라면 필수입니다.

 

인덱스 수정자 - 부분·표현식 인덱스

인덱스 타입은 아니지만, 어떤 인덱스에도 붙일 수 있는 강력한 옵션입니다. Oracle에도 비슷한 게 있지만 PostgreSQL이 더 유연합니다.

부분 인덱스 (Partial Index)

조건에 맞는 행만 인덱싱해서 크기를 줄입니다.

-- 실패한 주문만 인덱싱 (전체의 0.5%)
CREATE INDEX idx_failed_orders ON orders (order_id) 
WHERE status = 'failed';

-- 이 쿼리가 작은 인덱스를 탐
SELECT * FROM orders WHERE status = 'failed';

실전 효과: 상태 컬럼에서 특정 값이 극소수인데 그것만 자주 조회한다면(예: 실패 로그), 부분 인덱스로 인덱스 크기를 수십분의 1로 줄일 수 있습니다.

표현식 인덱스 (Expression Index)

함수 결과에 인덱스를 겁니다. Oracle의 함수 기반 인덱스와 동일한 개념입니다.

-- 대소문자 무시 검색을 위한 인덱스
CREATE INDEX idx_lower_email ON users (LOWER(email));

-- 이 쿼리가 인덱스를 탐
SELECT * FROM users WHERE LOWER(email) = 'test@test.com';

커버링 인덱스 (INCLUDE)

인덱스에 추가 컬럼을 포함시켜 테이블 접근 없이 조회합니다.

-- emp_id로 검색하면서 emp_name도 인덱스에서 바로 가져옴
CREATE INDEX idx_emp_cover ON employees (emp_id) INCLUDE (emp_name);

Oracle 12c의 인덱스와 유사하며, index-only scan을 유도해 성능을 높입니다.

 

★ 인덱스 선택 치트시트

결국 "언제 뭘 쓰냐"가 핵심입니다. 이 표만 기억하세요.

상황 인덱스 예시

일반 검색 (=, 범위, 정렬)	B-tree	WHERE age > 30
JSONB 내부 검색	GIN	data @> '{...}'
배열 검색	GIN	tags @> ARRAY[...]
전문검색	GIN	to_tsvector
대용량 시계열 (정렬된)	BRIN	로그의 created_at
공간 데이터	GiST	PostGIS
범위 겹침	GiST	예약 시간
특정 값만 자주 조회	부분 인덱스	WHERE status='failed'
함수 결과 검색	표현식 인덱스	LOWER(email)

의사결정 순서:

1. 일반적인 검색? → B-tree (고민 끝)
2. JSONB/배열/전문검색? → GIN
3. 대용량 append-only 시계열? → BRIN
4. 공간/범위? → GiST
5. 나머지는 필요할 때 찾아보기

 

★ AI로 인덱스 전략 짜기

어떤 인덱스를 써야 할지 헷갈릴 때 Claude에게 물어보세요.

프롬프트:
"PostgreSQL 17에서 이 테이블과 쿼리 패턴에 
최적의 인덱스를 추천해줘. Oracle DBA가 
이해하기 쉽게 이유도 설명해줘.

테이블: [CREATE TABLE 문]
자주 쓰는 쿼리: [쿼리들]
데이터 규모: [행 수, 증가 패턴]"

특히 GIN/BRIN 선택은 데이터 특성에 크게 좌우되므로, 실제 테이블 정보를 주고 물어보면 정확한 답을 얻습니다.

⚠️ 검증 필수: 추천받은 인덱스는 반드시 EXPLAIN ANALYZE로 실제 효과를 확인하세요. 인덱스가 있어도 옵티마이저가 안 쓸 수 있습니다. (EXPLAIN은 10편에서 자세히!)

 

Oracle DBA가 인덱스에서 자주 하는 실수

실수 1: 다 B-tree로만

JSONB 검색인데 B-tree 걸고 "왜 느리지?" 
→ GIN을 써야 함

실수 2: BRIN을 랜덤 데이터에

이메일 컬럼에 BRIN 걸고 효과 없다고 실망
→ BRIN은 정렬된 데이터 전용

실수 3: 인덱스 너무 많이

Oracle과 동일: 인덱스가 많으면 쓰기가 느려짐
→ 특히 GIN은 쓰기 부하가 크니 신중히

실수 4: 외래키에 인덱스 안 검

[2편에서도 언급] PostgreSQL도 FK에 인덱스 자동 생성 안 함
→ 수동으로 만들어야 조인/락 성능 확보

 

마무리

PostgreSQL 인덱스의 핵심입니다.

1. B-tree는 기본 — Oracle과 동일, 대부분 이걸로
2. GIN — JSONB·배열·전문검색의 게임 체인저
3. BRIN — 대용량 시계열의 마법 (정렬된 데이터 전용)
4. GiST — 공간·범위 데이터
5. 부분·표현식 인덱스 — 크기 줄이고 성능 높이기

Oracle에서 "인덱스 = B-tree"였다면, PostgreSQL에선 데이터 특성에 맞는 인덱스를 고르는 재미가 있습니다. 특히 JSONB에 GIN, 로그 테이블에 BRIN을 걸어보면 그 위력에 놀라실 겁니다. 저처럼 눈이 돌아갈 수도 있어요.

다음 편에서는 PL/pgSQL을 다룹니다. Oracle DBA가 애착 갖는 PL/SQL이 PostgreSQL에선 어떻게 다른지, 그리고 "패키지가 없다"는 충격적 사실과 그 대안을 이야기합니다.

여러분이 재미 본 인덱스 전략이나, GIN/BRIN으로 성능을 극적으로 개선한 경험이 있다면 댓글로 공유해주세요.