Introduction to PostgreSQL Indexes
Author: Dalto Curvelano | Source: DLT Blog | Published: 2024-09-11
한 줄 요약
PostgreSQL의 6가지 인덱스 유형(B-Tree, Hash, BRIN, GIN, GiST, SP-GiST)별 내부 동작 원리와 트레이드오프, 그리고 Partial/Covering/Expression 인덱스 등 최적화 기법을 종합적으로 정리한다.
핵심 주장/내용
- B-Tree는 O(log n) 검색으로 100만 건에 20회 비교만 필요하며, PostgreSQL의 기본이자 가장 범용적인 인덱스 타입이다
- PostgreSQL 18의 Skip Scan 기능으로 복합 인덱스의 선행 컬럼을 건너뛰는 쿼리도 최적화가 가능해졌다
- Partial Index로 인덱싱 대상을 조건부로 제한하여 크기, 유지보수 비용, 조회 성능을 동시에 개선할 수 있다
- BRIN은 시계열 등 append-only 테이블에서 극도로 컴팩트한 인덱스를 제공하지만, 빈번한 업데이트 테이블에는 부적합하다
- 인덱스는 디스크 공간, 쓰기 성능, 쿼리 플래너 복잡성, 메모리 사용 면에서 비용이 발생하며, 테이블의 15-20% 이상을 반환하는 쿼리에는 도움이 되지 않는다
주요 수치 / 사실
- 인덱스 없이 100만 행 쿼리: 265ms, 6,369 페이지 읽기 → 인덱스 적용 후 0.077ms, 4 페이지
- B-Tree: 100만 항목에 20회 비교 (O(log n))
- 인덱스 효용 기준: 테이블의 15-20% 미만 반환 시에만 유효
- PostgreSQL 기본 페이지 크기: 8KB
관련 위키
Source: 원문 보기