PostgreSQL 실전 강좌 #4 인덱스 전략 심화: 부분·복합·커버링, 그리고 GIN·BRIN

인프라 PostgreSQL 데이터베이스
4 분 소요

기초 4편에서 B-tree의 기본 규칙(선행 컬럼, 컬럼 가공 금지, 선택도)을 세웠고, 지난 편에서 느린 쿼리를 찾는 루틴을 만들었습니다. 이번 편은 그 루틴이 물어온 쿼리에 “기본 인덱스로는 부족할 때” 꺼내는 전략들입니다.

부분 인덱스: 조건까지 인덱스에 #

인덱스 정의에 WHERE를 넣으면 그 조건에 맞는 행만 색인됩니다.

부분 인덱스 생성 
-- 미처리 주문만 색인: 전체의 1%라면 인덱스도 1% 크기
CREATE INDEX idx_orders_pending ON orders (created_at)
WHERE status = 'pending';

전형적인 자리는 “전체는 크지만 쿼리는 항상 일부만 보는” 테이블입니다. 주문 테이블은 수억 행이어도 애플리케이션이 반복해서 보는 것은 미처리 건뿐입니다. 부분 인덱스는 작아서 빠르고, 색인 밖의 행(완료된 주문)이 갱신될 때 이 인덱스는 건드릴 필요가 없으므로 쓰기 세금도 줄어듭니다. 조건이 WHERE status = 'pending'인 쿼리만 이 인덱스를 탈 수 있다는 것, 즉 쿼리의 조건이 인덱스의 조건을 포함해야 한다는 것만 기억하면 됩니다. 같은 구조로 WHERE deleted_at IS NULL(소프트 삭제 테이블의 살아 있는 행만), UNIQUE 부분 인덱스(활성 행에만 유일성)도 실무 단골입니다.

커버링 인덱스: 테이블에 안 가기 #

기초 5편의 표에서 가장 빠른 스캔은 Index Only Scan, 즉 테이블 접근 없이 인덱스만으로 응답하는 형태였습니다. 조건은 쿼리가 요구하는 모든 컬럼이 인덱스 안에 있을 것입니다. 정렬 키로는 필요 없지만 응답에는 필요한 컬럼을 INCLUDE로 실어 두는 것이 커버링 인덱스입니다.

커버링 인덱스 생성 
-- SELECT email, name FROM users WHERE email = ? 를 인덱스만으로
CREATE INDEX idx_users_email_covering ON users (email) INCLUDE (name);

주의할 점이 둘 있습니다. 첫째, INCLUDE 컬럼만큼 인덱스가 커지고 그 컬럼의 갱신마다 인덱스도 갱신되므로, 실측된 핫 쿼리에만 씁니다. 둘째, Index Only Scan이 실제로 성사되는지는 EXPLAIN으로 확인해야 합니다. MVCC 구조상 “이 행이 모든 트랜잭션에 보이는가"를 담은 가시성 지도(visibility map)가 낡아 있으면 결국 테이블을 확인하러 가는데, 이 지도를 갱신하는 것이 다음 편의 VACUUM입니다. 인덱스 전략과 VACUUM이 여기서 연결됩니다.

B-tree 밖: GIN, GiST, BRIN #

타입자리예
GIN하나의 값 안에 요소가 여럿(문서·배열)JSONB, 배열, 전문 검색
GiST“겹침·근접” 질의지리 데이터(PostGIS), 범위 타입, 최근접 탐색
BRIN물리 순서와 값 순서가 비례하는 거대 테이블시계열의 created_at

GIN은 기초 7편에서 JSONB와 함께 이미 만났습니다. 실전에서 추가로 알아 둘 것은 BRIN입니다. 블록 구간별로 최솟값·최댓값만 저장하는 극단적으로 작은 인덱스로, 시간순으로만 쌓이는 로그·이벤트 테이블의 시각 컬럼처럼 “저장 순서와 값 순서가 거의 일치"할 때 B-tree의 수백분의 일 크기로 범위 검색을 받쳐 줍니다. 수억 행 시계열에 B-tree가 부담스러울 때 기억해 낼 이름입니다.

정리: 안 쓰는 인덱스 찾기 #

기초 4편의 결론이 “인덱스는 쓰기 세금"이었으니, 전략에는 정리도 포함됩니다. 판단 재료는 통계 뷰입니다.

안 쓰는 인덱스 찾기 
SELECT indexrelname, idx_scan,
       pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
JOIN pg_index USING (indexrelid)
WHERE idx_scan = 0 AND NOT indisunique
ORDER BY pg_relation_size(indexrelid) DESC;

idx_scan = 0, 즉 통계 수집 이후 한 번도 안 읽힌 인덱스가 후보입니다. 두 가지만 조심합니다. UNIQUE 인덱스는 조회가 아니라 제약을 위해 존재하므로 제외하고(위 쿼리의 NOT indisunique), 월말 배치처럼 드물게 쓰는 쿼리용일 수 있으니 충분히 긴 기간의 통계로 판단합니다. 복제 구성이라면 읽기 부하가 스탠바이에 있을 수 있다는 점도 염두에 둡니다(실전 8편).

마지막으로 블로트입니다. 갱신이 잦은 테이블의 인덱스는 MVCC의 dead tuple 흔적으로 점점 부풀어, 같은 일에 더 많은 읽기가 들게 됩니다. 재구축은 서비스를 세우지 않는 REINDEX INDEX CONCURRENTLY idx_name;으로 합니다(실전 1편의 CREATE INDEX CONCURRENTLY와 같은 계열입니다). 블로트가 왜 생기고 평소에 어떻게 억제하는지, 그 본체가 다음 편 VACUUM입니다.

정리 #

  • 부분 인덱스는 “항상 일부만 보는” 큰 테이블의 정답입니다. 작고, 빠르고, 쓰기 세금까지 줄입니다.
  • 커버링 인덱스(INCLUDE)는 Index Only Scan을 노립니다. 성사 여부는 EXPLAIN과 가시성 지도(VACUUM)에 달려 있습니다.
  • B-tree 밖의 선택지는 자리로 기억합니다. 문서·배열은 GIN, 겹침·근접은 GiST, 거대 시계열은 BRIN입니다.
  • 안 쓰는 인덱스는 pg_stat_user_indexes의 idx_scan으로 찾아 정리합니다. UNIQUE와 드문 배치용만 조심합니다.
  • 부푼 인덱스는 REINDEX CONCURRENTLY로 재구축합니다. 부풀음의 원리와 예방이 다음 편의 주제입니다.
X