▲

PostgreSQL 인덱스 소개

 (dlt.github.io)
2P by GN⁺ 18시간전 | ★ favorite | 댓글 1개
  • PostgreSQL 인덱스는 데이터 접근 속도를 높이기 위한 핵심 구조로, 디스크에서 읽어야 하는 데이터 양을 줄여 쿼리 성능을 향상시킴
  • 인덱스는 Btree, Hash, BRIN, GIN, GiST, SP-GiST 등 다양한 형태로 제공되며, 각기 다른 데이터 특성과 쿼리 패턴에 최적화됨
  • 인덱스는 디스크 공간, 쓰기 성능, 쿼리 플래너 복잡도, 메모리 사용량 등 여러 비용을 수반함
  • 부분 인덱스, 다중 컬럼 인덱스, 커버링 인덱스, 표현식 인덱스 등 고급 기능을 통해 특정 상황에서 효율성을 극대화할 수 있음
  • 적절한 인덱스 선택과 관리가 PostgreSQL 성능 최적화의 핵심 요소로 강조됨

인덱스의 기본 개념

  • 인덱스는 데이터베이스가 디스크에서 읽는 데이터 양을 줄여 쿼리 속도를 높이는 구조
    • 기본 키, 유니크 키, 배타 제약 조건 등도 인덱스를 통해 구현
    • 쿼리 결과가 전체 테이블의 15~20% 미만일 때 인덱스가 효과적이며, 그 이상이면 순차 스캔이 더 효율적일 수 있음
  • PostgreSQL은 기본적으로 6가지 인덱스 유형을 제공하며, 확장을 통해 더 많은 유형을 사용할 수 있음
    • 각 인덱스는 키 값과 해당 데이터 위치(TID)를 연결

디스크에 저장되는 데이터 구조

  • PostgreSQL의 테이블은 힙(heap) 파일로 저장되며, 8KB 페이지 단위로 구성
  • 각 행(tuple)은 특정 순서 없이 저장되고, 내부 주소는 ctid (current tuple id) 로 식별됨
    • 예: (0,1)은 페이지 0의 첫 번째 튜플을 의미
  • 인덱스는 이러한 힙의 위치(ctid)를 트리 구조로 연결하여 빠른 검색을 지원

인덱스가 데이터 접근을 가속화하는 방식

  • 인덱스가 없는 경우, PostgreSQL은 모든 페이지를 읽는 순차 스캔을 수행
    • 예시 쿼리에서 name='Ronaldo'를 찾을 때 6272개의 페이지를 읽고 265ms 소요
  • 인덱스를 추가하면 Index Scan으로 전환되어 4개의 페이지만 읽고 0.077ms에 완료
    • 인덱스는 값과 ctid를 매핑하여 필요한 행만 빠르게 찾음
  • 인덱스 파일 크기는 테이블 크기와 유사할 수 있음 (예: 30MB 테이블 → 30MB 인덱스)

인덱스의 비용 요소

  • 인덱스는 성능 향상 외에도 여러 부담 요소를 동반

디스크 공간

  • 인덱스는 별도의 저장 공간을 차지하며, 테이블보다 더 클 수 있음
    • 백업, 복제, 장애 복구 시 추가 비용 발생
    • 부분 인덱스, 다중 컬럼 인덱스, BRIN 등을 통해 공간 효율 개선 가능

쓰기 작업

  • UPDATE, INSERT, DELETE 시 인덱스가 포함된 컬럼이 변경되면 인덱스 갱신 오버헤드 발생

쿼리 플래너

  • 인덱스가 많을수록 플래너가 고려해야 할 옵션이 증가하여 쿼리 계획 수립 시간이 늘어남

메모리 사용량

  • 인덱스 페이지는 shared buffer에 적재되어 캐시되며, 인덱스가 많을수록 메모리 부담 증가
  • btree 노드 크기 제한으로 인해 컬럼이 클수록 트리 깊이가 증가
  • 정렬, 다중 컬럼 스캔, vacuum, reindex 등에서도 work memory가 추가로 사용됨

인덱스의 주요 유형

Btree

  • PostgreSQL의 기본 인덱스 구조로, 대부분의 DBMS에서 사용되는 범용 인덱스
    • O(log n) 시간 복잡도로 빠른 검색 지원
    • 모든 리프 노드가 동일한 깊이를 가지는 균형 트리 구조
    • ORDER BY, JOIN 연산에 유리하며, 기본키·유니크키 제약에 사용
  • 내부 노드는 하위 노드 포인터를, 리프 노드는 키와 힙 포인터를 저장
  • 왼쪽·오른쪽 노드 포인터를 통해 양방향 탐색 가능
다중 인덱스 사용
  • PostgreSQL은 여러 인덱스를 비트맵 AND/OR 연산으로 결합해 복합 조건을 처리
    • 예: age=30 AND login_count=100 조건 시 두 인덱스의 비트맵을 결합
다중 컬럼 인덱스
  • 여러 컬럼을 하나의 인덱스로 묶어 공간 절약과 속도 향상 가능
    • 단, 컬럼 순서가 중요하며 왼쪽부터 일치하는 조건만 인덱스 사용 가능
부분 인덱스
  • 조건식을 사용해 특정 행만 인덱싱
    • 인덱스 크기 축소, RAM 적합성 향상, 조회 속도 개선
    • 예: create index on rules(status) where status='enabled';
    • 값 분포가 불균형할 때 유용 (status <> 'TODO' 등)
커버링 인덱스
  • 쿼리에서 필요한 모든 컬럼이 인덱스에 포함되면 heap 접근 없이 결과 반환(index-only scan) 가능
    • create index abc_cov_idx on bar(a, b) including c;
    • 다중 컬럼 인덱스보다 공간 효율적
표현식 인덱스
  • 컬럼 값이 아닌 함수나 표현식 결과를 인덱싱
    • 예: CREATE INDEX idx_lower_name ON customers (lower(name));
    • LOWER(name) 같은 변환된 값으로 검색 시 유용
    • 불변(immutable) 함수만 사용 가능

Hash

  • 해시맵 구조 기반 인덱스로, 긴 문자열이나 UUID 등에서 공간 효율적
    • 32비트 해시 코드를 저장하여 크기 절감
    • 동등 비교(=) 연산만 지원하며, 정렬이나 다중 컬럼 인덱스는 불가
    • 균등한 해시 분포일 때 Btree보다 빠른 읽기 성능 가능
  • 공식 문서에 따르면, 해시 인덱스는 버킷 페이지 직접 접근으로 대규모 테이블에서 I/O 감소

BRIN (Block Range Index)

  • 각 블록 범위의 최소·최대값만 저장하는 인덱스
    • 매우 압축적이고 캐시 친화적
    • 대규모, append-only, 시계열 데이터에 적합
  • 행이 자주 갱신되면 MVCC로 인한 중복 저장으로 효율 저하
  • pages_per_range 설정으로 정확도와 크기 간 트레이드오프 조정 가능

GIN (Generalized Inverted Index)

  • 복합 데이터 검색에 적합한 인덱스
    • 텍스트, 배열, JSONB 등에서 특정 요소 검색 지원
    • 데이터 타입별 전용 전략(opclass) 사용
    • JSON은 JSONB 컬럼, 텍스트는 tsvector 또는 pg_trgm 확장과 함께 사용 권장

GiST & SP-GiST

  • 일반화 검색 트리(GiST) 와 공간 분할 트리(SP-GiST) 는 특정 데이터 타입용 인덱스 구현 프레임워크
    • GiST는 균형 트리, SP-GiST는 비균형 구조 지원
    • 지리정보, inet, 범위, 텍스트 벡터 등에 활용
    • GIN은 빠른 조회, GiST는 구축·유지 비용이 낮음
    • 전체 텍스트 검색 시 두 방식 중 요구사항에 맞게 선택

결론

  • 인덱스는 PostgreSQL 성능 최적화의 핵심이며, 읽기 속도 향상과 쓰기·저장 비용 간 균형이 중요
  • 데이터 특성과 쿼리 패턴에 맞는 인덱스 유형을 선택하면 빠르고 효율적인 데이터베이스 운영 가능
  • 적절한 인덱스 설계는 대규모 시스템의 확장성과 안정성 확보에 필수 요소임
▲
GN⁺ 18시간전  [-]
Hacker News 의견들

  • PostgreSQL 공식 문서가 정말 잘 쓰여 있고 읽는 재미도 있어서 공유함
    PostgreSQL Indexes 소개 문서

  • 다중 컬럼 인덱스 부분이 내가 배웠던 방식과 거의 같음
    하지만 최신 PostgreSQL 버전에서도 여전히 그런지 궁금했음
    예전에 세 번째 예시와 비슷한 쿼리에서 bitmap index scan이 사용되는 걸 봤는데, 그때부터 기존의 ‘정설’을 다시 생각하게 되었음
    참고로 인덱스 관련해서는 Use The Index, Luke 사이트와 책이 팀 전체가 읽을 만한 고전 자료라고 생각함

    • PostgreSQL 18에서는 index skip scan이 추가되어 이제는 다중 컬럼 인덱스의 하위 컬럼만으로도 효율적인 검색이 가능해졌음
      이전 버전에서도 가능은 했지만, 그땐 전체 인덱스 스캔이 필요해서 비효율적이었음
      관련 영상: YouTube 링크
    • bitmap index scan은 데이터가 있을 가능성이 있는 페이지를 좁혀주지만, 실제 조건 검증은 다시 해야 해서 일반 인덱스 스캔보다 성능이 떨어짐

  • PostgreSQL에서 incremental view maintenance를 기본 지원하면 좋겠다고 생각함
    이는 인덱스처럼 기본 데이터가 바뀔 때 자동으로 갱신되지만, 특정 뷰에 한정되지 않고 임의의 뷰에도 적용되는 개념임

    • 이건 트랜잭션 처리가 얽혀 있어서 꽤 어려운 문제임
      Noria, Materialize, Apache Flink, GCP Continuous Queries, Spark Streaming Tables, Delta Tables, ClickHouse streaming tables, TimescaleDB, ksqlDB, StreamSQL 등 관련 프로젝트가 많음
      PostgreSQL에서는 최근에 pg_ivm이라는 확장이 이 문제를 다루기 시작했음
    • 시계열 데이터라면 TimescaleDB의 continuous aggregates 기능이 이미 이런 역할을 함

  • B-tree vs Hash 인덱스 논의가 흥미로움
    많은 사람들이 ID 컬럼은 hash가 낫다고 생각하지만, 실제로는 기본 B-tree가 더 효율적임
    특히 거의 순차적인 값 삽입에서는 트리 구조가 더 유리함
    다만 이번에 언급된 블로그 글에서는 반대로 hash가 벤치마크에서 이겼다고 함

  • 이번 글의 타이밍이 좋았음
    다중 컬럼 인덱스의 leading column 규칙은 항상 헷갈렸는데, bitmap index scan 덕분에 예전만큼 치명적이지 않게 되었음
    PostgreSQL 18의 skip scan 기능은 기존의 상식을 크게 바꾸므로, 예전 버전 기준으로 배운 사람이라면 정신 모델 업데이트가 필요함

  • PostgreSQL용 자료로 정말 멋진 글이라고 생각함
    B-tree 인덱스 관련해서는 예전부터 Use The Index, Luke를 자주 참고해왔음

  • 필독서라고 생각함
    단순한 입문서 수준을 넘어서 깊이 있으면서도 내부 구조를 다루지 않는 한 충분히 읽기 쉬움

  • 이런 단순하고 겸손한 글쓰기 스타일이 마음에 듦
    지식을 직접적으로 전달하는 방식이 좋음

답변달기