PostgreSQL 인덱스 소개

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