←

Index types beyond B-tree: BRIN, GIN, partial, expression indexes

DB perf

Understand it step by step (한국어로 이해 → 영어로 말하기)

B-tree는 기본값일 뿐, 만능이 아니다. 시계열처럼 자연 정렬된 거대 테이블엔 BRIN이 B-tree보다 1000배 작고, JSONB·배열·전문검색엔 GIN, '활성 행만'이나 '소문자 이메일'처럼 조건·표현식 위에 거는 쿼리엔 partial/expression 인덱스가 맞는 도구다. 핵심은 '쿼리가 무엇을 묻는가'에 인덱스 종류를 맞추는 것 — 증상 → 왜 B-tree로 부족한가 → EXPLAIN으로 확인 → 맞는 인덱스 처방 → 스케일 처방 → 그 대가 순서로 올라간다.
  1. 1

    증상: events 테이블이 5억 행, created_at으로 자연스레 쌓인다(append-only). 최근 하루치 범위 조회는 빠른데, 이 테이블에 건 created_at B-tree 인덱스 하나가 디스크 11GB를 먹는다. 인덱스가 RAM에 안 들어가서 캐시 미스가 잦고, INSERT마다 트리 유지 비용도 붙는다. 쿼리는 멀쩡한데 '인덱스 자체가 너무 크다'가 증상이다.

    🔧 도구:\di+ (인덱스 크기 확인)pg_relation_size('idx_name')

    -- 500M rows, inserted in created_at order (time-series)
SELECT count(*) FROM events
WHERE created_at >= now() - interval '1 day';

\di+ idx_events_created
--  Index              | Type  | Size
--  idx_events_created | btree | 11 GB   <- bigger than fits in cache
    🗣 영어로 말해

    “On a five-hundred-million-row append-only table, the B-tree on created_at alone is eleven gigabytes.”

    checking microphone…

  2. 2

    왜 B-tree로 부족한가: B-tree는 '모든 값의 위치'를 행 단위로 다 저장한다 — 5억 행이면 5억 개의 포인터다. 그런데 이 데이터는 이미 디스크에 created_at 순서로 깔려 있다(상관관계 ≈ 1). 이럴 땐 '블록 범위'만 알면 된다: '블록 0~127은 6월 1일치, 128~255는 6월 2일치'. BRIN(Block Range INdex)은 블록 범위마다 min/max만 저장하므로, 같은 컬럼이 11GB → 약 3MB가 된다. 단, 데이터가 정렬돼 있을 때만 — 무작위로 섞여 있으면 BRIN은 무용지물이다.

    ⚖️ Trade-off: BRIN의 전제는 '물리적 정렬(상관관계가 1에 가까움)'이다. UPDATE가 잦아 행이 여기저기 끼면 블록 범위의 min/max가 넓어져 프루닝이 안 먹는다.

    🔧 도구:BRIN (Block Range Index)pg_stats.correlation (정렬 정도 확인)

    🗣 영어로 말해

    “B-tree stores a pointer per row; BRIN stores just min and max per block range, which only works when the data is physically ordered.”

    checking microphone…

  3. 3

    눈으로 확인: pg_stats에서 correlation을 본다 — created_at은 0.99(거의 완벽 정렬), 무작위 UUID 컬럼은 0.01이다. 이 숫자가 BRIN이 먹힐지 말지를 가른다. BRIN을 만든 뒤 EXPLAIN을 보면 'Bitmap Heap Scan'에 'Rows Removed by Index Recheck'가 보인다 — BRIN은 블록 범위를 골라줄 뿐 그 안에서 실제 행은 다시 거른다(lossy). 추측 말고 correlation과 플랜을 같이 본다.

    🔧 도구:pg_stats.correlationEXPLAIN (ANALYZE) — Recheck/Removed 확인

    SELECT attname, correlation FROM pg_stats
WHERE tablename = 'events' AND attname IN ('created_at','id');
--  attname    | correlation
--  created_at |  0.99   <- BRIN-friendly
--  id (uuid)  |  0.01   <- BRIN useless, recheck removes everything
    🗣 영어로 말해

    “I check pg_stats.correlation first — near one means BRIN will work, near zero means it won't.”

    checking microphone…

  4. 4

    처방 ①(BRIN): created_at에 BRIN을 만든다. 11GB → 약 3MB. 인덱스가 통째로 RAM에 들어가니 범위 조회가 빠르고, INSERT 유지 비용도 거의 0이다. pages_per_range로 블록 묶음 크기를 조절한다(기본값 128, 작게 하면 더 정밀하지만 인덱스가 커진다). 핵심: BRIN은 '범위 조회 + 정렬된 거대 테이블'에 특화된 도구지, =로 한 행 찾기엔 B-tree가 낫다.

    ✅ Fix: For range scans on a physically ordered time-series table, BRIN is the answer; for single-row equality or unordered columns, go back to B-tree. 정렬된 시계열·로그 테이블의 범위 조회면 BRIN이 정답이다. 단일 행 동등 조회(=)나 정렬 안 된 컬럼이면 B-tree로 돌아간다.

    🔧 도구:CREATE INDEX USING brinWITH (pages_per_range = N)

    CREATE INDEX idx_events_created_brin
  ON events USING brin (created_at)
  WITH (pages_per_range = 64);   -- default is 128

EXPLAIN (ANALYZE)
SELECT count(*) FROM events
WHERE created_at >= now() - interval '1 day';
-- Bitmap Heap Scan on events
--   Recheck Cond: (created_at >= ...)
--   -> Bitmap Index Scan on idx_events_created_brin  (3 MB, in cache)
    🗣 영어로 말해

    “A BRIN index drops eleven gigabytes to about three megabytes and stays in memory.”

    checking microphone…

  5. 5

    처방 ②(GIN — 다른 종류의 쿼리): 이제 offers.tags가 text[]이고 'VIP 태그가 붙은 오퍼'를 찾는다(tags @> '{vip}'). B-tree는 '컬럼 값 = X'만 다루지 배열 안의 원소나 JSONB 키는 못 본다 → 풀스캔. GIN(Generalized Inverted Index)은 역색인이라 '값 → 그 값을 가진 행 목록'을 저장한다. 'vip가 들어있는 행이 어디?'에 바로 답한다. JSONB 컬럼(metadata @> '{...}'), 배열 포함(@>), 그리고 pg_trgm으로 LIKE '%부분일치%'(접두·접미 와일드카드까지)와 typo 허용 유사도 검색(%)까지 GIN이 받는다.

    ✅ Fix: If the predicate is containment or membership — @>, ?, LIKE '%...%' — use GIN; for equality, range, or sort, use B-tree. The query's operator picks the index type. @>, ?, LIKE '%...%' 같은 '포함/원소' 조건이면 GIN. 동등·범위·정렬이면 B-tree. 쿼리의 연산자가 인덱스 종류를 결정한다.

    🔧 도구:GIN (Generalized Inverted Index)jsonb_path_ops (containment-only @>/@?/@@, 더 작음)pg_trgm (gin_trgm_ops — LIKE/ILIKE/~ + % 유사도)

    -- array / JSONB containment + substring & fuzzy text
CREATE INDEX idx_offers_tags_gin   ON offers USING gin (tags);
CREATE INDEX idx_offers_meta_gin   ON offers USING gin (metadata jsonb_path_ops);
CREATE INDEX idx_homes_addr_trgm   ON homes  USING gin (address gin_trgm_ops);

SELECT * FROM offers WHERE tags @> '{vip}';            -- array contains
SELECT * FROM offers WHERE metadata @> '{"source":"zillow"}';
SELECT * FROM homes  WHERE address ILIKE '%montgomery%'; -- substring (gin_trgm_ops)
SELECT * FROM homes  WHERE address % 'mongomery';        -- typo-tolerant: % similarity op
    🗣 영어로 말해

    “For array containment or JSONB or trigram search, B-tree can't help — GIN is an inverted index from value to row list.”

    checking microphone…

  6. 6

    처방 ③(partial + expression — 인덱스를 작게·정확하게): 두 가지 더. (1) partial index — 'PENDING 오퍼'만 자주 조회하는데 전체의 2%뿐이면, WHERE status='PENDING' 조건을 붙여 그 2%만 인덱싱한다. 인덱스가 작아지고(빠르고 RAM 절약), 나머지 98%의 INSERT/UPDATE는 인덱스를 안 건드린다. (2) expression index — 로그인은 lower(email)='x'로 조회하는데 일반 인덱스는 원본 email에 걸려 안 먹는다. 표현식 자체에 인덱스를 건다. 둘 다 '쿼리가 실제로 쓰는 모양'에 인덱스를 맞추는 것.

    ✅ Fix: If queries are always confined to one condition (active, unfinished, etc.), use a partial index for that subset; if they always wrap a column in a function (lower/date_trunc), put an expression index on that function's result. The planner uses it only when the WHERE clause or expression matches the index definition verbatim. 조회가 항상 특정 조건(활성·미완료 등)에 갇혀 있으면 partial로 그 부분집합만, 항상 함수(lower/date_trunc 등)로 감싸 조회하면 expression으로 그 함수 결과에 인덱스를 건다. WHERE/표현식이 인덱스 정의와 글자 그대로 맞아야 플래너가 쓴다.

    🔧 도구:partial index (CREATE INDEX ... WHERE)expression index (lower(col), date_trunc(...))UNIQUE 표현식 인덱스 (대소문자 무시 유일성)

    -- partial: index only the 2% of rows actually queried
CREATE INDEX idx_offers_pending
  ON offers (created_at DESC)
  WHERE status = 'PENDING';      -- plain B-tree on a slow query stays slow

-- expression: match the function used in the WHERE clause
CREATE UNIQUE INDEX idx_users_email_lower
  ON users (lower(email));        -- query: WHERE lower(email) = lower($1)
-- planner uses it ONLY if the query's expression matches exactly
    🗣 영어로 말해

    “A partial index covers only the rows you query, and an expression index matches lower of email when that's what the WHERE clause uses.”

    checking microphone…

  7. 7

    대가 정리: 인덱스 종류 선택은 공짜가 아니다. ① BRIN: 작고 싸지만 lossy라 정밀하지 않고, 데이터가 정렬을 잃으면(빈번한 UPDATE/삭제+재삽입) 쓸모가 사라진다 — =로 한 행 찾기엔 B-tree가 압도적. ② GIN: 읽기는 강력하지만 빌드·INSERT 유지 비용이 크다(역색인 갱신). 쓰기 폭주 테이블이면 fastupdate와 배치 갱신을 고려. ③ partial: 그 조건의 쿼리만 가속하고, 조건 밖 쿼리는 못 쓴다 — 플래너가 partial을 쓰려면 WHERE가 인덱스 술어를 포함해야 한다. ④ expression: 쿼리가 똑같은 표현식을 써야만 먹는다(한 글자만 달라도 무시). 규칙은 하나 — 인덱스 종류는 '데이터 모양 + 쿼리 연산자'에서 도출하고, 실제로 안 쓰는 인덱스는 pg_stat_user_indexes(idx_scan=0)로 찾아 지운다.

    ⚖️ Trade-off: BRIN is tiny but lossy and dies if the data loses physical order; GIN is powerful for reads but expensive to maintain on writes; partial and expression indexes only fire when the query matches their predicate or expression exactly. BRIN은 작은 대신 정밀도와 '정렬 유지' 전제를 산다. GIN은 읽기 성능을 쓰기 유지비로 산다. partial/expression은 인덱스 크기를 절약하는 대신, 쿼리가 술어·표현식과 정확히 일치할 때만 작동한다는 제약을 받아들인다.

    🔧 도구:pg_stat_user_indexes (idx_scan=0 → 미사용 인덱스)GIN fastupdate (쓰기 부하 완화)ANALYZE (correlation·통계 갱신)

    🗣 영어로 말해

    “Each index type buys a specific query shape at a cost, so I derive the type from the data and the operator, then drop whatever idx_scan shows unused.”

    checking microphone…

7단계 영어를 다 말하면 → 이 메커니즘 전체를 영어로 설명할 수 있게 된다.