PostgreSQL - 인덱스: 초보자 가이드

안녕하세요, 미래의 데이터베이스 마법사 여러분! 오늘 우리는 PostgreSQL 인덱스의 세계로 흥미로운 여정을 떠납니다. 코드를 한 줄도 작성해본 적이 없더라도 걱정 마세요 - 저는 여러분의 친절한 안내자가 되겠습니다. 우리는 이 주제를 단계별로 함께 탐구해보겠습니다. 그럼 마음에 드는 음료를 한 잔 마시면서 시작해보겠습니다!

인덱스는 무엇인가요?

본격적인 내용에 들어가기 전에 간단한 비유로 시작해보겠습니다. 도서관에서 특정 책을 찾고 있는 상상해보세요. 어떤 조직 시스템이 없다면 모든 책을 하나씩 뒤지며 원하는 책을 찾아야 합니다. 그것은 영원히 끝나지 않을 것입니다! 하지만 다행히 도서관은 인덱스 - 카드 목록이나 컴퓨터 시스템과 같은 것 - 를 가지고 있어 빠르게 원하는 책을 찾을 수 있도록 도와줍니다.

데이터베이스의 세계에서도 인덱스는 비슷한 목적을 가집니다. 인덱스는 데이터베이스 검색 엔진이 데이터 검색 속도를 빠르게 하기 위해 사용할 수 있는 특별한 조회 테이블입니다. 본질적으로 인덱스는 테이블에서 연산 속도를 향상시키는 데이터 구조입니다.

이 튜토리얼을 통해 사용할 간단한 테이블을 생성해보겠습니다:

CREATE TABLE students (
id SERIAL PRIMARY KEY,
name VARCHAR(100),
age INT,
grade CHAR(1)
);

INSERT INTO students (name, age, grade) VALUES
('Alice', 18, 'A'),
('Bob', 19, 'B'),
('Charlie', 20, 'A'),
('David', 18, 'C'),
('Eve', 19, 'B');

이렇게 하면 students라는 테이블을 생성하고, id, name, age, grade라는 네 개의 열을 추가하며, 몇 가지 샘플 데이터를 입력합니다.

인덱스 유형

이제 테이블이 준비되었으므로, PostgreSQL이 제공하는 다양한 유형의 인덱스를 탐구해보겠습니다. 각 유형은 자신만의 장점을 가지고 있으며, 다른 상황에 맞게 사용됩니다.

1. B-tree 인덱스

B-tree (Balanced tree)는 PostgreSQL의 기본 인덱스 유형입니다. 다양한 상황에 유용한 스위스 아미니 knife와 같은 인덱스입니다.

name 열에 B-tree 인덱스를 생성해보겠습니다:

CREATE INDEX idx_student_name ON students USING BTREE (name);

이 인덱스는 name 열을 검색하거나 정렬하는 질의에 특히 유용할 것입니다.

2. 해시 인덱스

해시 인덱스는 동등 비교에 최적화되었습니다. 사전처럼 빠르게 단어를 찾을 수 있는 것입니다.

age 열에 해시 인덱스를 생성해보겠습니다:

CREATE INDEX idx_student_age ON students USING HASH (age);

이 인덱스는 WHERE age = 18와 같은 정확한 연령 매칭 질의의 속도를 빠르게 합니다.

3. GiST 인덱스

GiST (Generalized Search Tree) 인덱스는 전문 텍스트 검색과 도형 데이터나 사용자 정의 데이터 유형을 인덱싱하는 데 유용합니다.

전문 텍스트 검색을 위한 GiST 인덱스 생성 예제:

CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX idx_student_name_gist ON students USING GIST (name gist_trgm_ops);

이 인덱스는 name 열의 부분 매칭이나 유사성 검색에 유용합니다.

4. GIN 인덱스

GIN (Generalized Inverted Index) 인덱스는 단일 열에 여러 값을 가지는 열, 예를 들어 배열이나 JSON 데이터에 가장 적합합니다.

테이블에 JSON 열을 추가하고 GIN 인덱스를 생성해보겠습니다:

ALTER TABLE students ADD COLUMN hobbies JSONB;
CREATE INDEX idx_student_hobbies ON students USING GIN (hobbies);

이 인덱스는 JSON 데이터 내 특정 취미를 검색하는 데 유용합니다.

다음은 이러한 인덱스 유형을 요약한 표입니다:

인덱스 유형	가장 적합한 경우	예제 사용 사례
B-tree	일반 목적, 정렬	이름 검색 또는 정렬
해시	동등 비교	정확한 연령 매칭
GiST	전문 텍스트 검색, 도형 데이터	부분 이름 매칭
GIN	배열 또는 JSON 데이터	JSON 필드 내 검색

부분 인덱스

때로는 데이터의 하위 집합만 인덱스를 생성하고 싶을 때가 있습니다. 이때 부분 인덱스가 유용합니다. 부분 인덱스는 책의 특정 섹션에 대한 인덱스를 만드는 것과 같은 것입니다.

grade가 'A'인 학생들에 대한 부분 인덱스를 생성해보겠습니다:

CREATE INDEX idx_student_grade_a ON students (name) WHERE grade = 'A';

이 인덱스는 특정 'A' 등급 학생을 검색하는 질의의 속도를 빠르게 합니다.

묵시적 인덱스

PostgreSQL은 특정 상황에서 자동으로 인덱스를 생성합니다. 가장 흔한 경우는 PRIMARY KEY나 UNIQUE 제약 조건을 정의할 때입니다.

우리의 students 테이블에서 PostgreSQL은 id 열에 PRIMARY KEY로 정의되었기 때문에 자동으로 인덱스를 생성합니다.

인덱스 제거 명령어

인덱스를 생성할 수 있는 것처럼, 필요하지 않을 때 인덱스를 제거할 수도 있습니다. 인덱스를 제거하는 방법은 다음과 같습니다:

DROP INDEX idx_student_name;

이 명령어를 사용할 때는 주의해야 합니다. 인덱스를 제거하면 질의 성능에 큰 영향을 미칠 수 있습니다.

인덱스를 피해야 할 때

인덱스는 질의 성능을 크게 향상시킬 수 있지만, 항상 최고의 솔루션이 아닙니다. 다음은 인덱스를 생성하기 전에 고려해야 할 몇 가지 상황입니다:

1. 작은 테이블: 테이블에 행이 매우 적다면 전체 테이블 스캔이 인덱스 사용보다 빠를 수 있습니다.
2. 자주 업데이트되는 테이블: 인덱스는 테이블 데이터가 변경될 때 갱신되므로, 쓰기 연산이 자주 발생하는 테이블에서 인덱스는 속도를 늦출 수 있습니다.
3. 낮은 선택성을 가진 열: 열이 고유한 값을 거의 가지지 않는 경우, 인덱스가 큰 도움이 되지 않을 수 있습니다.
4. 거의 질의되지 않는 테이블: 테이블이 주로 데이터를 쓰는 데 사용되고 질의가 거의 발생하지 않는 경우, 인덱스 유지 비용이 혜택을 상회할 수 있습니다.

인덱스는 예술이자 과학입니다. 특정 사용 사례에 맞는 균형을 찾기 위해서는 실험과 성능 테스트가 필요합니다.

그렇게 해서 여러분! 우리는 PostgreSQL 인덱스의 세계를 여행하며 기본 개념에서 더 고급 개념까지 다루었습니다. 이 가이드가 인덱스에 대한 당신의 오해를 풀어주길 바랍니다. 실습이 완벽함을 이루는 길입니다. 따라서 자신의 데이터베이스 프로젝트에서 이 개념을 실험해보지 마세요.

행복한 인덱싱을 기원하며, 질의가 항상 빠르기를 바랍니다!

Credits: Image by storyset