PostgreSQL 인덱스 완전 가이드: 내부 동작 원리부터 실전 최적화까지
관련글
Introduction to PostgreSQL Indexes
이 문서의 대상 독자
이 문서는 데이터베이스 인덱스가 무엇인지 직관적으로는 이해하고 있지만, 내부적으로 어떻게 동작하는지, 어떤 트레이드오프가 있는지, PostgreSQL이 제공하는 다양한 인덱스 유형이 무엇인지, 그리고 특정 사용 사례에 맞게 인덱스를 최적화하는 고급 옵션을 어떻게 활용할 수 있는지에 대해 깊이 이해하고자 하는 개발자를 위해 작성되었다. 데이터베이스 성능 최적화에 관심이 있고, 단순히 인덱스를 추가하는 것을 넘어서 왜 그것이 작동하는지, 언제 작동하지 않는지를 이해하고 싶은 이들에게 유용할 것이다.
인덱스의 기본 개념
데이터베이스 인덱스는 데이터 접근 속도를 높이기 위해 특별히 설계된 데이터베이스 객체다. 인덱스의 핵심 목적은 데이터베이스가 디스크에서 읽어야 하는 데이터의 양을 줄이는 것이다. 디스크 I/O는 데이터베이스 성능에서 가장 큰 병목 중 하나이기 때문에, 필요한 데이터만 선택적으로 읽을 수 있다면 쿼리 성능이 극적으로 향상될 수 있다. 인덱스는 데이터 접근 속도 향상이라는 주요 목적 외에도 기본 키, 고유 키, 배타 제약조건과 같은 제약조건을 강제하는 데에도 사용된다.
하지만 인덱스가 모든 쿼리를 가속화하는 것은 아니다. 인덱스는 쿼리가 인덱스에 정의된 컬럼과 데이터 타입과 일치할 때만 도움이 된다. 또한 매우 대략적인 경험 규칙으로, 인덱스는 쿼리가 테이블의 약 15-20% 미만을 반환할 때만 도움이 된다. 그 이상을 반환하는 경우, 쿼리 플래너라고 불리는 PostgreSQL의 구성 요소가 순차 스캔을 선호할 수 있다. 쿼리 플래너는 쿼리가 어떻게 실행될지 결정하는 역할을 담당하며, 통계 정보와 각 스캔 유형에 미리 정의된 비용을 사용하여 최적의 실행 계획을 수립한다. 물론 실제로는 이 경험 규칙보다 훨씬 복잡한 상황이 많지만, 이 문서에서는 쿼리 플래너의 동작을 부분적으로만 다룬다.
따라서 쿼리가 테이블의 많은 부분을 반환한다면, 인덱스를 무작정 추가하기 전에 쿼리를 리팩토링하거나, 요약 테이블을 사용하거나, 다른 기술을 고려해야 한다. 이러한 기본 개념을 염두에 두고, PostgreSQL이 디스크에 데이터를 어떻게 저장하는지, 그리고 인덱스가 이 데이터에 대한 쿼리를 어떻게 가속화하는지 자세히 살펴보자.
PostgreSQL의 기본 설치에는 6가지 유형의 인덱스가 포함되어 있으며, 확장을 통해 더 많은 유형을 사용할 수 있다. 일반적으로 인덱스는 키 값을 해당 키를 포함하는 테이블의 하나 이상의 행의 데이터 위치와 연결하는 방식으로 작동한다. 각 행은 TID(Tuple ID)로 식별된다.
PostgreSQL의 디스크 데이터 저장 구조
인덱스를 이해하기 위해서는 먼저 PostgreSQL이 테이블 데이터를 디스크에 어떻게 저장하는지 이해하는 것이 중요하다. PostgreSQL의 모든 테이블은 크기에 따라 디스크에 하나 이상의 해당 파일을 가지고 있다. 이 파일 집합을 힙(heap)이라고 부르며, 8KB 크기의 페이지로 나뉜다. 테이블의 모든 행, 내부적으로 “튜플(tuple)”이라고 불리는 것들은 이 파일들에 저장되며 특정한 순서를 가지지 않는다. 인덱스는 인덱스 컬럼을 힙의 행 위치, 즉 ctid(current tuple id)에 연결하는 트리 구조다. 인덱스의 내부 구조에 대해서는 나중에 더 자세히 살펴볼 것이다.
힙 파일을 확인하기 위해 몇 가지 PostgreSQL 내부 테이블을 사용할 수 있다. 먼저 psql에 접속하여 show data_directory; 명령으로 PostgreSQL이 데이터베이스의 물리적 파일을 저장하는 디렉토리를 확인할 수 있다. 예를 들어 이 명령은 /opt/homebrew/var/postgresql@16과 같은 경로를 반환할 수 있다.
이제 내부 테이블인 pg_class를 사용하여 힙 테이블이 저장된 파일을 찾을 수 있다. 먼저 간단한 테이블을 생성해보자. create table foo (id int, name text); 명령으로 두 개의 컬럼을 가진 테이블을 생성한다. 그런 다음 pg_database 테이블에서 현재 데이터베이스의 OID를 찾는다. 예를 들어 select oid, datname from pg_database where datname = 'my_database'; 쿼리는 데이터베이스의 OID를 반환한다. 이어서 select relfilenode from pg_class where relname = 'foo'; 명령으로 테이블의 파일 노드 번호를 얻을 수 있다.
이 정보를 바탕으로 셸에서 실제 파일을 확인할 수 있다. ls -lrt $PGDATA/base/<database_oid>/<table_oid> 형식의 명령을 사용하면 된다. 예를 들어 ls -lrt /opt/homebrew/var/postgresql@16/base/71122/71123과 같은 명령이 될 것이다. 처음에는 아직 INSERT 작업을 수행하지 않았기 때문에 파일 크기가 0으로 표시된다.
이제 테이블에 몇 개의 행을 추가해보자. insert into foo (id, name) values (1, 'Ronaldo');와 insert into foo (id, name) values (2, 'Romario'); 명령으로 두 개의 행을 삽입한다. 쿼리에 ctid 필드를 추가하면 각 행의 ctid를 조회할 수 있다. ctid는 힙에서 행의 주소를 가지는 내부 필드로, 행의 위치를 가리키는 포인터라고 생각할 수 있다. ctid는 (m, n) 형식의 튜플로 구성되며, 여기서 m은 블록 ID이고 n은 튜플 오프셋이다. select ctid, * from foo; 쿼리를 실행하면 첫 번째 행이 페이지 0의 오프셋 1에 저장되어 있음을 확인할 수 있다. 예를 들어 ctid가 (0,1)이면 페이지 0의 첫 번째 위치를, (0,2)이면 페이지 0의 두 번째 위치를 나타낸다.
인덱스가 데이터 접근을 가속화하는 방법
이제 테이블에 더 많은 선수를 추가하여 총 행 수가 백만 개가 되도록 해보자. insert into foo (id, name) select generate_series(3, 1000000), 'Player ' || generate_series(3, 1000000); 명령으로 많은 데이터를 생성할 수 있다. 이렇게 하면 테이블의 해당 파일 크기가 약 30MB가 되며, 내부적으로는 8KB 페이지로 나뉜다. ls -lrtah 명령으로 파일을 확인하면 실제로 30MB 정도의 크기를 볼 수 있다.
인덱스 없이 테이블을 쿼리하면 PostgreSQL은 모든 페이지의 모든 튜플을 읽고 필터를 적용한다. 예를 들어 name 컬럼 값이 “Ronaldo”인 행을 검색하는 명령을 분석해보자. explain (analyze, buffers) select * from foo where name = 'Ronaldo'; 명령을 사용하면 데이터베이스가 이 검색을 어떻게 수행했는지 확인할 수 있다. explain 명령의 analyze 옵션은 비용 추정치만 사용하는 것이 아니라 실제로 쿼리를 실행하고, buffers 옵션은 수행된 I/O 작업의 양을 보여준다.
실행 계획을 보면 “Parallel Seq Scan on foo”라는 줄이 있는데, 이는 데이터베이스가 순차 스캔을 수행하여 테이블의 모든 행을 읽었음을 나타낸다. 이 쿼리의 실행 시간은 약 265밀리초였다. 또한 “Buffers: shared hit=97 read=6272”라는 줄도 볼 수 있는데, 이는 메모리에서 97개의 페이지를 읽었고 디스크에서 6272개의 페이지를 읽었음을 의미한다.
이제 name 컬럼에 인덱스를 추가하고 동일한 쿼리의 성능을 확인해보자. 쓰기 작업을 차단하고 싶지 않기 때문에 create index concurrently on foo(name); 명령을 사용한다. 인덱스 생성 후 동일한 쿼리를 다시 실행하면 실행 계획이 크게 달라진다. 이제 “Index Scan using foo_name_idx on foo”라는 줄이 보이며, 인덱스가 사용되었고 이 경우 실행 시간이 265밀리초에서 0.077밀리초로 줄어들었다. 데이터베이스는 이제 4개의 페이지만 읽으면 된다.
실행 시간이 감소한 이유는 이제 테이블의 모든 행을 읽는 대신 인덱스를 사용하기 때문이다. 인덱스는 “Ronaldo”라는 값을 name 컬럼에 이 값을 가진 행의 ctid에 매핑하는 트리 구조다. 이 예제에서는 그러한 행이 하나뿐이다. ctid는 힙에서 이러한 행을 빠르게 찾는 데 사용된다.
\di+ 명령을 사용하여 데이터베이스의 인덱스를 표시하면 방금 생성한 인덱스가 30MB를 차지하고 있음을 알 수 있다. 이는 foo 테이블의 크기와 거의 같다. 이는 인덱스의 중요한 특성 중 하나로, 인덱스가 상당한 디스크 공간을 차지할 수 있다는 것을 보여준다.
인덱스와 관련된 비용
인덱스가 가져오는 추가 속도는 여러 비용과 연관되어 있으며, 인덱스를 어디에 어떻게 적용할지 결정할 때 이러한 비용을 고려해야 한다. 이러한 비용을 이해하면 인덱스를 더 현명하게 사용할 수 있고, 불필요한 인덱스로 인한 오버헤드를 피할 수 있다.
디스크 공간
인덱스는 힙과 별도의 영역에 저장되며 추가 디스크 공간을 차지한다. 테이블에 인덱스가 많을수록 이를 저장하는 데 필요한 디스크 공간이 더 많아진다. 이는 데이터베이스와 백업에 대한 추가 스토리지 비용을 발생시키고, 복제 트래픽을 증가시키며, 백업 및 장애 조치 복구 시간을 늘린다. btree 인덱스가 테이블 자체보다 큰 것이 드문 일이 아니라는 점을 명심해야 한다. 부분 인덱스, 다중 컬럼 인덱스, BRIN과 같은 공간 효율적인 인덱스 유형에 대해 배우는 것이 도움이 될 수 있다.
쓰기 작업
UPDATE, INSERT, DELETE와 같은 쓰기 작업에는 유지보수 비용이 있다. 인덱스의 일부인 필드가 수정되면 해당 인덱스를 업데이트해야 하며, 이는 쓰기 프로세스에 상당한 오버헤드를 추가할 수 있다. 따라서 쓰기가 빈번한 테이블에 인덱스를 추가할 때는 이러한 유지보수 비용을 신중하게 고려해야 한다.
쿼리 플래너
쿼리 플래너는 쿼리에 대한 최상의 실행 전략을 결정하는 구성 요소다. 사용 가능한 인덱스가 많을수록 쿼리 플래너가 고려해야 할 옵션이 많아지며, 이는 쿼리 계획에 필요한 시간을 증가시킬 수 있다. 특히 복잡한 쿼리가 많거나 사용 가능한 인덱스가 많은 시스템에서 이러한 영향이 두드러진다.
메모리 사용
PostgreSQL은 자주 액세스되는 데이터 및 인덱스 페이지의 일부를 공유 버퍼라는 메모리에 유지한다. 인덱스가 사용되면 관련 인덱스 페이지가 공유 버퍼에 로드되어 액세스 속도를 높인다. 인덱스가 많고 자주 사용될수록 더 많은 공유 버퍼 메모리가 필요하다. 공유 버퍼는 제한되어 있고 데이터 페이지 캐싱에도 사용되므로, 공유 버퍼를 인덱스로 채우면 테이블 데이터의 캐싱 효율성이 떨어질 수 있다.
또한 노드 크기 용량에는 제한이 있기 때문에, 인덱싱된 컬럼의 전체 값이 btree의 모든 노드에 복사된다는 점을 염두에 두어야 한다. 인덱싱된 컬럼이 클수록 트리가 더 깊어진다. 메모리 사용의 또 다른 측면은 PostgreSQL이 정렬이나 복잡한 인덱스 스캔(다중 컬럼 또는 커버링 인덱스 포함)을 포함하는 쿼리를 실행할 때 작업 메모리를 사용한다는 것이다. 더 큰 인덱스는 이러한 작업에 더 많은 메모리를 필요로 한다. 또한 인덱스는 시스템 카탈로그 캐시에 구조, 컬럼 이름 및 통계에 대한 일부 메타데이터를 저장하는 데 메모리가 필요하다. 마지막으로 인덱스는 vacuum 및 reindex 작업과 같은 유지보수 작업에 메모리가 필요하다.
인덱스의 유형
PostgreSQL은 다양한 사용 사례에 최적화된 여러 유형의 인덱스를 제공한다. 각 인덱스 유형은 특정 데이터 특성과 쿼리 패턴에 맞게 설계되었으며, 올바른 인덱스 유형을 선택하는 것이 성능 최적화의 핵심이다.
Btree 인덱스
B-Tree는 PostgreSQL뿐만 아니라 거의 모든 데이터베이스 관리 시스템에 존재하는 매우 강력한 데이터 구조다. 이는 매우 우수한 범용 인덱스이기 때문이다. B-Tree는 보잉에서 근무하던 Rudolf Bayer와 Edward M.McCreight가 발명했다. B-tree의 “B”가 Bayer, Boeing, balanced, better 중 무엇을 나타내는지 아무도 정확히 알지 못하며, 사실 그것은 중요하지 않다. 정말 중요한 것은 B-Tree가 트리에서 요소를 O(log n) 시간에 검색할 수 있게 해준다는 것이다. 빅오 표기법에 익숙하지 않더라도, 이것이 정말 빠르다는 것만 알면 된다. 백만 개의 항목이 있는 집합에서 요소를 찾기 위해 20번의 비교만 하면 된다.
더욱이 B-Tree는 컴퓨터에서 사용 가능한 RAM보다 큰 데이터 세트에 대해서도 O(log n) 시간 복잡도를 유지할 수 있다. 즉, B-Tree가 원하는 데이터를 찾기 위한 디스크 페이지 액세스를 효율적으로 방지하기 때문에 디스크를 사용하여 RAM을 확장할 수 있다는 의미다. PostgreSQL에서 btree는 가장 일반적인 인덱스 유형이며 기본값이다. 시스템 및 TOAST 인덱스를 지원하는 데에도 사용된다. 빈 데이터베이스조차도 수백 개의 btree 인덱스를 가지고 있다. 이는 기본 키 및 고유 키 제약조건에 사용할 수 있는 유일한 인덱스 유형이기도 하다.
이진 트리와 달리 B-Tree는 균형 트리이며 모든 리프 노드가 루트로부터 같은 거리에 있다. 루트 노드와 내부 노드는 하위 레벨에 대한 포인터를 가지고 있으며, 리프 노드는 키와 힙에 대한 포인터를 가지고 있다. PostgreSQL의 btree는 또한 더 쉬운 순방향 및 역방향 스캔을 위해 왼쪽 및 오른쪽 노드에 대한 포인터를 가지고 있다. 노드는 여러 키를 가질 수 있으며 이러한 키는 정렬되어 있어 순서대로 탐색하고 ORDER BY 및 JOIN 작업을 수행하기 쉽다.
값은 리프 노드에만 저장되며, 이는 트리를 더 컴팩트하게 만들고 모든 리프 노드를 선형으로 통과하여 트리의 객체를 완전히 순회하는 것을 용이하게 한다. 이것은 PostgreSQL Btree 인덱스의 단순화된 설명일 뿐이다. 낮은 수준의 세부 사항을 알고 싶다면 PostgreSQL 소스 코드의 README와 이를 영감을 준 논문을 읽는 것이 좋다.
여러 인덱스 사용
PostgreSQL은 비트맵의 지원으로 여러 인덱스 스캔에서 AND 및 OR 조건을 형성하여 단일 인덱스 스캔으로 처리할 수 없는 경우를 처리하기 위해 여러 인덱스를 사용할 수 있다. 비트맵은 쿼리에 필요한 대로 AND 또는 OR로 결합되고, 마지막으로 테이블 행이 방문되어 반환된다. 예를 들어 다음과 같은 쿼리가 있다고 가정해보자. select * from users where age = 30 and login_count = 100;
age와 login_count 컬럼이 인덱싱되어 있으면, PostgreSQL은 age 인덱스를 스캔하여 age가 30인 모든 페이지를 찾고, age가 30인 행을 포함할 수 있는 페이지가 true인 비트맵을 만든다. 비슷한 방식으로 login_count 인덱스를 사용하여 비트맵을 만든다. 그런 다음 두 비트맵을 AND 연산하여 세 번째 비트맵을 형성하고, 후보 값을 포함할 수 있는 페이지만 읽는 테이블 스캔을 수행하며, age가 30이고 login_count가 100인 행만 결과 세트에 추가한다.
다중 컬럼 인덱스
다중 컬럼 인덱스는 여러 인덱스를 사용하는 대안이다. 일반적으로 여러 인덱스를 사용하는 것보다 작고 빠르지만 유연성은 떨어진다. 이는 컬럼의 순서가 중요하기 때문이다. 데이터베이스는 인덱싱된 컬럼의 하위 집합을 검색할 수 있지만, 가장 왼쪽 컬럼이어야 한다. 예를 들어 컬럼 a에 인덱스가 있고 컬럼 b에 다른 인덱스가 있는 경우, 이러한 인덱스는 다음 쿼리 모두에 도움이 된다.
1
2
3
select * from my_table where a = 42 and b = 420;
select * from my_table where a = 43;
select * from my_table where b = 99;
반면에 create index on my_table(a, b);와 같은 명령으로 (a, b)에 다중 컬럼 인덱스를 생성한 경우 처음 두 쿼리만 인덱스를 사용한다. 따라서 다중 컬럼 인덱스를 만들 때는 인덱스가 가능한 한 많은 쿼리에 사용될 수 있도록 컬럼 순서를 잘 선택해야 한다.
부분 인덱스
부분 인덱스를 사용하면 조건식을 사용하여 인덱싱될 행의 하위 집합을 제어할 수 있으며, 이는 많은 이점을 가져올 수 있다. 인덱스가 더 작아져 RAM에 들어갈 가능성이 높아지고, 인덱스가 더 얕아져 조회가 더 빠르며, INSERT, UPDATE, DELETE에 대한 오버헤드가 적다. 다만 인덱스에서 행을 필터링하는 데 사용하는 컬럼이 매우 자주 업데이트되어 지속적인 인덱스 유지보수를 트리거하는 경우 오버헤드가 더 커질 수도 있다.
부분 인덱스는 일부 행에 관심이 없거나 한 값의 비율이 다른 값보다 훨씬 큰 컬럼을 인덱싱하는 상황에서 주로 유용하다. 두 가지 예를 들어보겠다.
첫 번째 경우는 일부 행에 관심이 없을 때다. 행을 enabled 또는 disabled로 표시할 수 있는 rules 테이블이 있다고 가정해보자. 대부분의 행이 disabled 상태이고 쿼리에서는 enabled 행에만 관심이 있다면, 다음과 같이 disabled 행을 필터링하는 부분 인덱스를 가질 수 있다. create index on rules(status) where status = 'enabled';
두 번째 경우는 값의 분포가 치우쳐 있을 때다. todo 애플리케이션을 구축하고 있고 status 컬럼 값이 TODO, DOING, DONE 중 하나일 수 있다고 가정해보자. 백만 개의 행이 있고 현재 각 상태의 행 분포가 TODO 90%, DOING 5%, DONE 5%라고 하자. PostgreSQL은 테이블 컬럼의 값 분포에 대한 통계를 유지하고 대부분의 행이 TODO 상태에 있다는 것을 알기 때문에, 쿼리의 WHERE 절에 status=’TODO’가 있을 때는 status에 인덱스가 있더라도 tasks 테이블에 대한 순차 스캔을 선택하여 인덱스의 대부분이 사용되지 않고 공간을 낭비하게 된다. 이 경우 다음과 같은 부분 스캔이 권장된다. create index on tasks(status) where status <> 'TODO';
커버링 인덱스
인덱스에 있는 컬럼만 선택하는 쿼리가 있는 경우, PostgreSQL은 쿼리에 필요한 모든 정보를 인덱스에 가지고 있으며 결과를 반환하기 위해 힙에서 페이지를 가져올 필요가 없다. 이러한 최적화를 인덱스 전용 스캔(index-only scan)이라고 한다. 작동 방식을 이해하기 위해 다음 시나리오를 고려해보자.
1
2
3
4
5
6
7
8
create table bar (a int, b int, c int);
create index abc_idx on bar(a, b);
/* 쿼리 1 */
select a, b from bar;
/* 쿼리 2 */
select a, b, c from bar;
첫 번째 쿼리에서 PostgreSQL은 a와 b 값이 인덱스에 있기 때문에 인덱스 전용 스캔을 수행하고 힙에서 데이터를 가져오는 것을 피할 수 있다. 두 번째 쿼리에서는 c가 인덱스에 없기 때문에 PostgreSQL이 힙에 대한 참조를 따라 그 값을 가져와야 한다. 첫 번째 쿼리에서는 다중 컬럼 인덱스의 도움으로 PostgreSQL이 인덱스 전용 스캔을 수행할 수 있게 했지만, 커버링 인덱스를 사용하여 동일한 결과를 얻을 수도 있다. 커버링 인덱스를 만드는 구문은 다음과 같다. create index abc_cov_idx on bar(a, b) including c;
이것은 c가 btree의 리프 노드에만 삽입되기 때문에 (a, b, c)에 다중 컬럼 인덱스를 만드는 것보다 공간 효율적이다. 또한 고유 인덱스를 원하는데 c가 인덱스의 고유성을 “깨뜨리는” 경우 커버링 인덱스를 사용할 수 있다.
표현식 인덱스
표현식 인덱스는 원시 컬럼 값이 아니라 표현식이나 함수의 결과를 인덱싱한다. 이는 변환된 버전의 데이터를 기반으로 자주 쿼리할 때 매우 유용할 수 있다. WHERE 절의 일부로 함수를 사용하는 경우 필수적이다. 다음 예를 살펴보자.
1
2
3
4
5
6
7
CREATE TABLE customers (
id SERIAL PRIMARY KEY,
name TEXT
);
CREATE INDEX idx_name ON customers(name);
SELECT * FROM customers WHERE LOWER(name) = 'john doe';
이 예제에서 PostgreSQL은 인덱스가 name 컬럼에 대해 만들어졌기 때문에 인덱스를 사용하지 않는다. 작동하려면 WHERE 절에서 사용되는 것처럼 인덱스 키가 lower 함수를 호출해야 한다. 이를 수정하려면 다음과 같이 한다. CREATE INDEX idx_lower_name ON customers (lower(name));
| 이제 이와 같은 쿼리를 실행하면 PostgreSQL이 표현식 인덱스를 사용하여 일치하는 행을 효율적으로 찾을 수 있다. 표현식 인덱스는 다양한 유형의 표현식을 사용하여 만들 수 있다. lower(), upper() 등과 같은 내장 함수, 불변인 한 사용자 정의 함수, first_name | ’ ‘ | last_name과 같은 문자열 연결 등을 사용할 수 있다. |
Hash 인덱스
해시 인덱스는 구조상 B-Tree와 다르며, 대부분의 프로그래밍 언어에 있는 해시맵 데이터 구조와 훨씬 유사하다. 예를 들어 Python의 dict, PHP의 array, Java의 HashMap 등이 있다. 전체 컬럼 값을 인덱스에 추가하는 대신 32비트 해시 코드를 파생시켜 해시에 추가한다. 이로 인해 UUID, URL 등과 같은 긴 데이터를 인덱싱할 때 해시 인덱스가 btree보다 훨씬 작아진다. PostgreSQL의 해싱 함수의 도움으로 모든 데이터 타입을 인덱싱할 수 있다. psql에서 \df hash*를 입력하고 TAB을 누르면 50개 이상의 해시 관련 함수가 있음을 알 수 있다.
해시 충돌을 우아하게 처리하지만, 해시 값의 균등한 분포를 위해 더 잘 작동하며 고유하거나 대부분 고유한 데이터에 가장 적합하다. 올바른 조건에서는 btree 인덱스보다 작을 뿐만 아니라 btree에 비해 읽기 속도도 빠르다. 공식 문서에서는 이렇게 말한다.
“B-tree 인덱스에서 검색은 리프 페이지를 찾을 때까지 트리를 내려가야 한다. 수백만 행이 있는 테이블에서 이러한 하강은 데이터 액세스 시간을 증가시킬 수 있다. 해시 인덱스에서 리프 페이지에 해당하는 것을 버킷 페이지라고 한다. 대조적으로 해시 인덱스는 버킷 페이지에 직접 액세스할 수 있어 더 큰 테이블에서 인덱스 액세스 시간을 잠재적으로 줄일 수 있다. 이러한 ‘논리적 I/O’ 감소는 shared_buffers/RAM보다 큰 인덱스/데이터에서 더욱 두드러진다.”
제한 사항으로는 동등 연산만 지원하며 인덱싱된 필드로 정렬하는 데 도움이 되지 않는다. 또한 다중 컬럼 인덱스와 고유성 확인을 지원하지 않는다. 해시 인덱스가 btree와 비교하여 어떻게 수행되는지에 대한 심층 분석을 원한다면 Evgeniy Demin의 블로그 게시물을 확인하는 것이 좋다.
BRIN 인덱스
BRIN은 Block Range Index의 약자이며 이름에서 구현 방식에 대해 많은 것을 알 수 있다. BRIN 인덱스의 노드는 인덱스가 참조하는 페이지에 있는 값 범위의 최소값과 최대값을 저장한다. 이로 인해 인덱스가 더 컴팩트하고 캐시 친화적이지만 사용 사례가 제한된다. 쓰기가 많고 삭제 및 업데이트가 적은 워크로드에서 매우 큰 데이터가 있는 경우 BRIN이 적합하다. BRIN 인덱스는 매우 큰 데이터베이스에서 대량의 데이터에 대한 순차 스캔의 최적화 도구로 생각할 수 있으며, 테이블을 파티셔닝하기 전에 시도해볼 만한 좋은 최적화다.
BRIN 인덱스가 잘 작동하려면 인덱스 키가 힙에서 행의 위치와 강하게 상관관계가 있는 컬럼이어야 한다. BRIN에 대한 좋은 사용 사례는 append-only 테이블과 시계열 데이터를 저장하는 테이블이다. BRIN은 행이 지속적으로 업데이트되는 테이블에서는 잘 작동하지 않는다. 이는 행을 복제하고 힙의 다른 부분에 저장하는 MVCC의 특성 때문이다. 이러한 튜플 복제 및 이동은 상관관계에 부정적인 영향을 미치고 인덱스의 효율성을 감소시킨다.
pg_repack 또는 pg_squeeze와 같은 확장을 사용하는 것은 BRIN 인덱스를 사용하는 테이블에 권장되지 않는다. 이러한 도구는 테이블의 내부 데이터 레이아웃을 변경하고 상관관계를 엉망으로 만들기 때문이다. 또한 이 인덱스는 리프 노드가 특정 범위 내의 값을 포함할 수 있는 페이지를 가리킨다는 점에서 손실이 있다(lossy). 이러한 이유로 BRIN은 대량의 데이터 하위 집합을 반환해야 하는 경우에 더 유용하며, 하나 또는 몇 개의 행만 반환하는 쿼리에 대해서는 btree가 읽기 성능이 더 좋다. pages_per_range 설정을 조정하여 인덱스를 더 많거나 적게 손실되도록 만들 수 있으며, 그 트레이드오프는 인덱스 크기다.
GIN 인덱스
Generalized Inverted Index는 텍스트 블롭에서 단어 찾기, 배열에서 항목 찾기, JSON에서 객체 찾기와 같이 복합 데이터에서 항목을 검색하려는 경우에 적합하다. GIN은 일부 항목에 대한 검색을 가속화하는 방법을 알 필요가 없다는 점에서 일반화되어 있다. 대신 각 데이터 타입에 특정한 사용자 지정 전략 세트가 있다. JSON 값을 인덱싱하려면 JSONB 컬럼에 저장되어야 한다는 점을 유의해야 한다. 마찬가지로 텍스트를 인덱싱하는 경우 tsvector로 저장하거나 변환하거나 pg_trgm 확장을 사용하는 것이 좋다.
GiST 및 SP-GiST 인덱스
Generalized Search Tree와 Space-Partitioned Generalized Search Tree는 특정 데이터 타입에 대한 인덱스를 구현하기 위한 기본 템플릿으로 사용할 수 있는 트리 구조다. 인덱스를 구축하기 위한 프레임워크로 생각할 수 있다. GiST는 균형 트리이고 SP-GiST는 불균형 데이터 구조의 개발을 허용한다. 포인트와 기하학적 유형, inet, 범위 및 텍스트 벡터를 인덱싱하는 데 유용하다. PostgreSQL과 함께 제공되는 내장 전략의 광범위한 목록은 공식 문서에서 찾을 수 있다.
애플리케이션에서 전체 텍스트 검색을 활성화하기 위해 인덱스가 필요한 경우 GIN과 GiST 중에서 선택해야 한다. 대략적으로 말하면 GIN은 조회가 더 빠르지만 크기가 더 크고 구축 및 유지보수 비용이 더 높다. 따라서 적합한 인덱스 유형은 애플리케이션 요구사항에 따라 달라진다.
결론
인덱스를 이해하고 효과적으로 사용하는 것은 PostgreSQL에서 데이터베이스 성능을 최적화하는 데 매우 중요하다. 인덱스는 쿼리 실행 속도를 크게 높이고 전반적인 효율성을 향상시킬 수 있지만, 쓰기 작업과 스토리지에 미치는 영향을 염두에 두는 것이 중요하다. 특정 사용 사례에 따라 적절한 유형의 인덱스를 신중하게 선택하면 PostgreSQL 데이터베이스가 빠르고 효율적으로 유지되도록 할 수 있다.
이 글이 PostgreSQL 인덱스에 대해 몰랐던 한두 가지를 가르쳐주었기를 바라며, 이제부터 데이터베이스와 관련된 다양한 시나리오를 더 잘 처리할 수 있는 능력을 갖추게 되기를 희망한다. 인덱스는 데이터베이스 성능 최적화의 핵심 도구이지만, 맹목적으로 적용하기보다는 각 인덱스의 특성과 비용을 이해하고 데이터의 특성과 쿼리 패턴에 맞게 전략적으로 사용하는 것이 중요하다.
작성일자: 2026-01-28