올바른 인덱스 선택: PostgreSQL 인덱스 유형 가이드

잘못된 PostgreSQL 인덱스는 디스크를 낭비하고 쓰기 속도를 저하시키며, 여전히 쿼리가 수백만 행을 스캔하게 만들 수 있습니다. 올바른 인덱스는 WHERE 절의 연산자, 컬럼 유형, 데이터 형태에 따라 달라집니다.

일반적인 동등 조건 및 범위 조회에는 B-트리로 시작하세요. 쿼리 패턴에 특정 연산자 지원이 필요한 경우 GIN, GiST, BRIN 또는 SP-GiST를 사용하세요.

PostgreSQL에서 인덱싱의 중요성

PostgreSQL에서 인덱싱의 핵심은 쿼리를 만족시키기 위해 검사해야 하는 데이터 양을 줄이는 것입니다. 인덱스가 없으면 PostgreSQL은 많은 쿼리에 대해 전체 테이블 스캔을 수행해야 하며, 특히 대규모 테이블의 경우 매우 느릴 수 있습니다. 인덱스는 데이터베이스가 관련 행을 빠르게 찾을 수 있도록 하는 데이터 구조를 생성합니다. 인덱스의 효과는 다음에 크게 좌우됩니다:

• 사용된 인덱스 유형: 다양한 인덱스 유형은 서로 다른 데이터 구조와 쿼리 연산에 적합합니다.
• 데이터 분포: 치우친 데이터는 인덱스 성능에 영향을 줄 수 있습니다.
• 쿼리 패턴: 데이터를 쿼리하는 방식이 중요한 요소입니다.

다음은 가장 자주 선택하게 될 인덱스 유형입니다.

PostgreSQL 인덱스 유형 설명

PostgreSQL은 여러 인덱스 유형을 제공합니다. 일상적인 성능 작업에 가장 유용한 것은 B-트리, GIN, GiST, BRIN 및 SP-GiST입니다.

1. B-트리 인덱스

B-트리는 PostgreSQL의 기본이자 가장 다재다능한 인덱스 유형입니다. =, <, >, <=, >=를 포함한 일반적인 비교 연산자에 적합합니다. B-트리 인덱스는 동등 조건 검사, 범위 스캔, 정렬, 고유 제약 조건 및 기본 키에 탁월합니다.

작동 방식: B-트리 인덱스는 정렬된 트리 구조에 데이터를 저장합니다. 트리의 각 노드에는 키와 자식 노드에 대한 포인터가 포함됩니다. 이 구조는 검색, 삽입 및 삭제가 일반적으로 로그 시간 복잡도로 효율적임을 보장합니다.

사용 사례:

• 동등 검색 (WHERE column = value)
• 범위 쿼리 (WHERE column BETWEEN value1 AND value2 또는 WHERE column > value)
• 정렬 (ORDER BY column)
• 최소값 또는 최대값 찾기 (ORDER BY column LIMIT 1)
• 고유 제약 조건 및 기본 키 (암시적으로 B-트리 사용)

예시:

수백만 개의 레코드가 있는 users 테이블을 고려하세요. B-트리를 사용하여 email 컬럼을 인덱싱하면 이메일 주소로 특정 사용자를 찾는 조회 속도가 크게 향상됩니다.

CREATE INDEX idx_users_email ON users (email);
-- 이제 다음과 같은 쿼리가 훨씬 빨라집니다:
SELECT * FROM users WHERE email = '[email protected]';

팁: B-트리 인덱스는 일반적으로 좋은 시작점이며 많은 일반적인 데이터베이스 작업에 충분합니다. 그러나 전문 검색이나 지리 공간 데이터와 같은 특정 사용 사례의 경우 다른 인덱스 유형이 더 성능이 좋을 수 있습니다.

2. GIN (일반화된 역 인덱스) 인덱스

GIN 인덱스는 배열, JSON 문서 또는 전문 검색 문서(tsvector)와 같이 여러 항목을 포함하는 복합 값 또는 값을 인덱싱하도록 설계되었습니다. 이러한 복합 값 내에서 특정 요소의 존재 여부를 검색하는 쿼리에 특히 효과적입니다.

작동 방식: GIN 인덱스는 복합 값 내의 각 요소를 해당 요소를 포함하는 행 목록에 매핑합니다. 역 인덱스이므로 행을 직접 인덱싱하는 대신 값 자체를 인덱싱합니다. 이는 더 큰 구조 내에 특정 항목이 존재하는지 확인하는 데 효율적입니다.

사용 사례:

• 전문 검색 (tsvector 대 tsquery)
• 배열 인덱싱 (ANY, @> 연산자)
• JSONB 데이터 인덱싱 (?, ?|, ?&, @>, <@ 연산자)

예시:

문자열 ARRAY 유형의 tags 컬럼이 있는 documents 테이블이 있다고 가정하세요. 'database' 태그가 있는 모든 문서를 찾으려고 합니다.

CREATE INDEX idx_documents_tags ON documents USING GIN (tags);
-- 'database' 태그가 있는 문서를 찾는 쿼리:
SELECT * FROM documents WHERE tags @> ARRAY['database'];
-- 또는 JSONB의 경우:
CREATE TABLE products (id SERIAL PRIMARY KEY, details JSONB);
CREATE INDEX idx_products_details ON products USING GIN (details);
SELECT * FROM products WHERE details ? 'manufacturer';

참고: GIN 인덱스는 각 요소를 다시 인덱싱해야 하기 때문에 B-트리 인덱스보다 업데이트 속도가 느릴 수 있습니다. 그러나 복합 유형 내의 요소를 검색하는 검색에서는 뛰어난 쿼리 성능을 제공합니다.

3. GiST (일반화된 검색 트리) 인덱스

GiST 인덱스는 사용자 정의 인덱스 유형 생성을 허용하는 프레임워크입니다. 일반적으로 기하학적 데이터 유형 인덱싱 및 전문 검색에 사용됩니다. GiST 인덱스는 데이터가 복잡하고 B-트리 구조에 깔끔하게 맞지 않을 때 특히 유용합니다.

작동 방식: GiST는 매우 유연한 인덱싱 방법입니다. 데이터 공간을 재귀적으로 분할하여 작동합니다. 내부 구조는 사용된 특정 연산자 클래스에 따라 다를 수 있지만 일반적으로 트리와 유사한 구조로 데이터를 구성합니다.

사용 사례:

• 공간 쿼리를 위한 기하학적 데이터 유형 (점, 선, 다각형) (&&, @>).
• 범위 인덱싱.
• GiST 연산자 클래스를 사용한 전문 검색.

예시:

공간 인덱싱의 경우, 관심 지점(POI) 테이블이 있고 특정 지리적 영역 내의 모든 POI를 찾으려고 한다고 가정하세요.

CREATE TABLE pois (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    location GEOMETRY(Point, 4326) -- PostGIS 확장 사용
);

-- location 컬럼에 GiST 인덱스 생성
CREATE INDEX idx_pois_location ON pois USING GIST (location);

-- 경계 상자 내의 POI 찾기 (PostGIS 함수 사용 예시)
SELECT * FROM pois WHERE ST_Intersects(location, ST_MakeEnvelope(lon1, lat1, lon2, lat2, 4326));

팁: GiST 인덱스는 복잡한 데이터 유형 및 공간 쿼리에 강력합니다. 또한 조건에 따라 행의 하위 집합만 인덱싱하는 부분 인덱스에도 사용할 수 있어 성능을 더욱 최적화할 수 있습니다.

4. BRIN (블록 범위 인덱스) 인덱스

BRIN 인덱스는 데이터가 디스크의 물리적 저장 위치와 자연스러운 상관 관계가 있는 매우 큰 테이블을 위해 설계되었습니다. 개별 행 값 대신 물리적 블록 주소 범위를 인덱싱하여 작동합니다. 이로 인해 매우 작고 빠르게 생성할 수 있지만 인덱싱된 컬럼의 값이 물리적 순서와 상관 관계가 있는 경우에만 효과적입니다.

작동 방식: BRIN 인덱스는 테이블 블록 범위에 대한 최소값과 최대값을 저장합니다. 쿼리할 때 PostgreSQL은 블록 범위에 대한 최소/최대값을 확인합니다. 쿼리 조건이 이 범위를 벗어나면 전체 블록 범위를 건너뛰어 전체 테이블 스캔을 피합니다. 이는 타임스탬프나 시퀀스 ID와 같이 자연스럽게 정렬된 데이터에 가장 효과적입니다.

사용 사례:

• 매우 큰 테이블.
• 물리적 저장 순서와 강한 자연 상관 관계가 있는 컬럼 (예: created_at 타임스탬프, 자동 증가 ID).
• 블록의 값 범위가 해당 블록의 행 수보다 훨씬 작은 경우.

예시:

timestamp로 정렬된 수십억 개의 항목이 있는 로그 테이블을 고려하세요.

CREATE TABLE logs (
    id BIGSERIAL PRIMARY KEY,
    message TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- created_at에 BRIN 인덱스 생성
CREATE INDEX idx_logs_created_at ON logs USING BRIN (created_at);

-- 특정 날짜의 로그 쿼리:
SELECT * FROM logs WHERE created_at >= '2023-10-26 00:00:00' AND created_at < '2023-10-27 00:00:00';

경고: BRIN 인덱스는 데이터가 물리적으로 정렬된 경우에만 효과적입니다. 데이터가 무작위 순서로 삽입되거나 컬럼 값이 물리적 위치와 상관 관계가 없는 경우 BRIN 인덱스는 상당한 성능 이점을 제공하지 않으며 성능을 저하시킬 수도 있습니다. pages_per_range 매개변수를 조정하여 BRIN 인덱스 효율성을 최적화할 수 있습니다.

5. SP-GiST (공간 분할 일반화된 검색 트리) 인덱스

SP-GiST는 GiST와 유사한 또 다른 유형의 일반화된 검색 트리이지만, 공간을 불균형하게 분할하는 알고리즘에 최적화되어 있습니다. 비균일 데이터 분포와 쿼드트리 또는 k-d 트리와 같은 복잡한 공간 데이터 구조를 인덱싱하는 데 특히 유용합니다.

작동 방식: SP-GiST는 다양한 분할 전략을 사용하여 다양한 데이터 유형과 쿼리 패턴에 적응할 수 있습니다. 특정 유형의 데이터, 특히 고도로 클러스터링되거나 희소한 분포를 가진 데이터 세트를 다룰 때 GiST보다 더 효율적일 수 있습니다.

사용 사례:

• k-d 트리 또는 쿼드트리를 사용한 점 데이터.
• 네트워크 데이터.
• 지리 공간 데이터.
• 텍스트 검색.

예시:

복잡한 기하학적 구조에 자주 사용되지만, 일반적인 사용 사례는 대규모 점 집합을 인덱싱하는 것입니다.

-- 점 좌표가 있는 테이블 가정
CREATE TABLE points (id SERIAL PRIMARY KEY, coord POINT);

-- SP-GiST 인덱스 생성
CREATE INDEX idx_points_coord ON points USING SPGIST (coord);

-- 특정 영역 내의 점 쿼리
SELECT * FROM points WHERE coord <@ box '((x1,y1),(x2,y2))';

고려 사항: SP-GiST 인덱스는 기존 B-트리나 GiST가 어려움을 겪을 수 있는 특정 데이터 구조 및 쿼리 패턴에 대해 성능 이점을 제공할 수 있습니다. 그러나 복잡성으로 인해 특정 벤치마크가 이점을 나타내지 않는 한 항상 첫 번째 선택은 아닙니다.

기타 인덱스 유형 (간략히)

• 해시 인덱스: 동등 비교(=)만 지원합니다. 최신 PostgreSQL 릴리스에서는 WAL 로깅되지만, B-트리 인덱스가 더 많은 연산자와 정렬을 지원하므로 일반적으로 첫 번째 선택입니다.
• 부분 인덱스: 이러한 인덱스는 WHERE 절을 만족하는 테이블 행의 하위 집합만 인덱싱합니다. 쿼리가 특정 데이터 하위 집합을 자주 대상으로 하는 경우 공간을 절약하고 성능을 향상시킬 수 있습니다.
• 표현식 인덱스: 하나 이상의 컬럼에 대한 표현식이나 함수에 인덱스를 생성할 수 있습니다. 이는 lower(email)과 같은 표현식을 WHERE 절에서 자주 사용하는 쿼리에 유용합니다.

어떤 인덱스 유형을 언제 사용해야 할까요?

올바른 인덱스를 선택하는 것은 PostgreSQL 성능 튜닝의 중요한 부분입니다. 다음은 결정을 돕기 위한 빠른 가이드입니다:

인덱스 유형	가장 적합한 경우	지원되는 연산자	고려 사항
B-트리	일반 목적, 동등 조건, 범위, 정렬	=, <, >, <=, >=	기본, 다재다능, 좋은 만능.
GIN	전문 검색, 배열, JSONB, 복합 유형	@@, @>, <@, ?, `?	, ?&`
GiST	공간 데이터, 기하학적 유형, 전문 검색	&&, @>, <@, @@ (및 연산자 클래스를 통한 기타)	유연함, 복잡한 데이터 구조에 좋음, B-트리보다 느릴 수 있음.
BRIN	물리적으로 상관된 데이터가 있는 매우 큰 테이블	<, >, <=, >=, =	작은 크기, 빠른 생성, 정렬된 데이터 상관 관계에서만 효과적.
SP-GiST	비균일 데이터, 복잡한 공간 구조	연산자 클래스에 따라 다름 (예: 공간, 네트워크)	특정 분할 전략에 효율적, 튜닝이 더 복잡할 수 있음.

고려해야 할 요소:

1. 쿼리 패턴: 가장 자주 실행하는 쿼리 유형은 무엇인가요? 동등 조건 검사, 범위 스캔, 전문 검색 또는 공간 쿼리인가요?
2. 데이터 유형: 인덱싱되는 데이터 유형(예: 문자열, 숫자, 배열, JSON, 기하학적 점)은 최상의 인덱스 선택에 큰 영향을 미칩니다.
3. 데이터 분포: 데이터가 자연스럽게 정렬되어 있습니까(타임스탬프처럼) 아니면 무작위로 분포되어 있습니까?
4. 업데이트 빈도: 인덱싱된 컬럼의 데이터가 얼마나 자주 업데이트됩니까? GIN 및 GiST 인덱스는 B-트리보다 업데이트 속도가 느릴 수 있습니다.
5. 테이블 크기: 매우 큰 테이블의 경우 데이터 상관 관계가 존재하면 BRIN 인덱스가 유리할 수 있습니다.
6. 인덱스 크기 및 유지 관리: 인덱스에 필요한 디스크 공간과 유지 관리 오버헤드를 고려하세요.

인덱스 생성 및 관리

PostgreSQL은 인덱스 관리를 위한 간단한 SQL 명령을 제공합니다:

• 인덱스 생성:

  CREATE INDEX index_name ON table_name USING index_type (column_name [ASC|DESC] [NULLS FIRST|LAST], ...);
  

• 인덱스 삭제:

  DROP INDEX index_name;
  

• 기존 인덱스 보기:

  \d+ table_name;
  

모범 사례: 프로덕션에 변경 사항을 적용하기 전에 항상 스테이징 환경에서 인덱스 생성 또는 변경의 성능 영향을 테스트하세요. EXPLAIN ANALYZE를 사용하여 쿼리가 인덱스를 어떻게 사용하는지 이해하세요.

결론

연산자와 데이터 형태에 맞는 인덱스를 선택한 다음 EXPLAIN ANALYZE로 증명하세요. 인덱스는 쓰기 경로의 일부이기도 하므로 실제 쿼리에 도움이 되는 인덱스는 유지하고 유지 관리 비용만 추가하는 인덱스는 제거하세요.