데이터베이스 쿼리 속도를 높이는 인덱스 활용법

본문은 요즘IT와 번역가 David 가 함께 마네슈와르 홀라( Maneshwar C Holla )의 글 Speed Up DB Queries Like a Pro >을 번역한 글입니다. 필자는 소프트웨어 엔지니어이자 기술 작가로, DEV Community와 Forem에서 활발히 활동하고 있습니다. 현재 그는 Antino에서 근무하며, 이전에는 Hexmos에서 경험을 쌓았습니다. 또한 i3 윈도우 매니저와 Linux Mint를 애용하며, 자신의 블로그를 통해 개발 및 기술에 대한 통찰력 있는 글들을 공유하고 있습니다.

이번 글에서는 데이터베이스 쿼리의 성능을 향상시키기 위한 인덱스 사용법과 그 중요성에 대해 다룹니다. 그는 PostgreSQL의 B-트리 인덱스 를 예로 들어, 인덱스의 작동 방식과 적절한 사용 시점을 강조하며, EXPLAIN ANALYZE 명령어를 활용한 성능 측정 기법도 소개합니다.

필자에게 허락을 받고 번역했으며, 글에 포함된 각주(*표시)는 ‘번역자주’입니다. 글에 포함된 링크는 원문에 따라 표시했습니다.

데이터베이스 쿼리가 끝나기를 한참 기다려본 경험이 있다면, 그 기다림이 얼마나 고통스러운지 아실 겁니다. 다행히도 인덱스가 이 문제를 해결해 줄 수 있습니다. PostgreSQL은 강력하지만, 적절한 인덱싱 없이는 데이터베이스가 마치 구형 컴퓨터에서 돌아가는 것처럼 느릴 수 있습니다.

인덱스는 쿼리 속도를 획기적으로 향상시키는 비결이지만, 동시에 트레이드오프도 존재합니다.

인덱스란 무엇인가? PostgreSQL의 인덱스 는 책의 색인과 같은 원리로 작동합니다. 모든 페이지를 일일이 살펴보는 대신, 필요한 부분으로 바로 이동할 수 있죠. 인덱스가 없다면 PostgreSQL은 순차 검색 을 수행하는데, 이는 일치하는 항목을 찾기 위해 모든 행 을 확인해야 한다는 의미입니다. 성능 면에서 좋지 않죠.

인덱스는 특히 다음과 같은 경우에 유용합니다.

‘WHERE’ 절이 있는 쿼리 속도 개선 JOIN 성능 개선

인덱스를 사용하면 쿼리 실행 시간이 선형적이 아닌 로그 시간으로 줄어듭니다. O(n)에서 O(log n) 복잡도로 개선된다고 생각하면 됩니다. 하지만 인덱스가 마법은 아닙니다. 트레이드오프가 있죠. 저장 공간을 차지하고 쓰기 작업을 느리게 만들 수 있습니다.

따라서 언제, 어디에 사용할지 현명하게 판단해야 합니다.

인덱스의 작동 원리: B-트리의 마법 PostgreSQL의 기본 인덱스 유형은 B-트리(균형 트리)입니다. 빠른 검색을 위해 데이터를 정렬된 상태로 유지하는 트리 구조라고 생각하시면 됩니다. 작동 방식은 다음과 같습니다.

루트 노드: 모든 검색의 시작점 분기 노드: 검색을 알맞은 리프 노드로 안내 리프 노드: 실제 데이터 포인터를 저장

예를 들어, 테이블에서 “김철수”라는 이름을 검색한다면, B-트리는 다음과 같이 동작합니다.

루트에서 시작 현재 노드와 “김철수” 비교 비교 결과에 따라 왼쪽 또는 오른쪽으로 이동 정확히 일치하는 항목을 찾을 때까지 반복

이 과정을 통해 필요한 비교 횟수가 줄어들어 검색 속도가 로그 시간 으로 빨라집니다.

인덱스를 사용해야 하는 경우 읽기 성능을 크게 향상시킬 수 있을 때 인덱스를 사용하세요. 일반적인 시나리오는 다음과 같습니다.

인덱스를 사용하지 말아야 하는 경우 인덱스는 공짜가 아닙니다. INSERT, UPDATE, DELETE 작업을 할 때마다 인덱스도 함께 업데이트해야 합니다. 이는 쓰기 위주의 작업에서 성능 저하를 초래할 수 있습니다. 다음과 같은 경우에는 인덱스 사용을 피하세요.

테이블이 작은 경우(PostgreSQL이 어차피 빠르게 스캔 가능) 인덱스된 열로 거의 필터링하지 않는 경우 쓰기가 빈번하고 읽기 속도가 중요하지 않은 경우 데이터베이스가 트랜잭션 위주이며 빠른 삽입/업데이트가 필요한 경우

PostgreSQL의 MVCC* 메커니즘은 “힙 전용 튜플”(HOT) 업데이트로 이어질 수 있어 죽은 행이 생기고 I/O가 증가할 수 있습니다.

*MVCC(다중 버전 동시성 제어): 데이터베이스에서 여러 사용자가 동시에 데이터를 수정할 때 충돌 없이 안전하게 작업할 수 있도록 관리하는 기술

인덱스 성능 측정 무작정 인덱스를 추가하기 전에, 실제로 도움이 되는지 아래와 같이 테스트해 볼 수 있습니다. PostgreSQL은 쿼리 실행 시간을 분석할 수 있는 EXPLAIN ANALYZE 를 제공합니다. 다음과 같이 사용해 보세요.

sql

EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'test@example.com';

Seq Scan과 Index Scan을 확인하세요. 인덱스를 추가해도 쿼리 시간이 줄지 않는다면, 그 인덱스는 불필요한 것입니다.

쓸모없는 인덱스를 제거하고 싶다면 다음 명령어를 사용하세요.

DROP INDEX index_name;

PostgreSQL의 인덱스 유형들 모든 인덱스가 동일하게 만들어진 것은 아닙니다. PostgreSQL은 여러 유형의 인덱스를 제공합니다.

B-트리 인덱스(기본값) 실질적으로 모든 데이터베이스는 일부 B-트리 인덱스를 가지고 있습니다. B-트리는, 트리의 각 분기에 있는 데이터의 양이 대략적으로 동일하도록 균형을 유지하려고 합니다. 따라서 행을 찾기 위해 순회해야 하는 레벨의 수는 항상 비슷한 수준입니다.

B-트리 인덱스는 동등 비교와 범위 쿼리에 효율적으로 사용될 수 있습니다. 모든 데이터 타입에 대해 작동할 수 있으며, NULL 값을 검색하는 데에도 사용될 수 있습니다. B-트리는 부분적으로만 캐시되어 있더라도 캐싱과 매우 잘 작동하도록 설계되었습니다.

강점 : 동등 및 범위 쿼리 (=, , >=)

예시)

CREATE INDEX idx_users_email ON users(email); 해시 인덱스 (PostgreSQL 10+ 버전에서 개선됨) PostgreSQL 10 이전 버전에서는 동등 비교에만 유용하였으나, 트랜잭션에 안전하지 않고 충돌 후 수동으로 재구축해야 하며 팔로워에 복제되지 않기 때문에 사용하지 않는 것이 좋습니다. 따라서 B-Tree에 비해 장점이 매우 적습니다.

PostgreSQL 10 이상의 버전에서는 해시 인덱스가 이제 선행 기록되고 팔로워에게 복제됩니다.

동등 비교 (=)에 최적화되어 있습니다. 범위 쿼리(>, 적합하지 않습니다 .

예시)

CREATE INDEX idx_users_hash_email ON users USING hash(email); GIN (일반화된 역 인덱스) GIN은 하나의 행에 여러 값을 매핑해야 할 때 유용합니다. 반면 B-Tree 인덱스는 행에 단일 키값이 있을 때 최적화되어 있습니다.

GIN은 배열 값의 인덱싱과 전체 텍스트 검색을 구현하는 데 좋습니다.

전문 검색 과 JSONB 필드 에 사용됩니다.

예시)

CREATE INDEX idx_users_bio ON users USING gin(to_tsvector('korean', bio)); GiST (일반화된 검색 트리) GiST 인덱스를 사용하면 일반적인 균형 트리 구조를 구축할 수 있으며, 동등 비교와 범위 비교를 넘어선 작업에도 사용할 수 있습니다.

기하학적 데이터 타입뿐만 아니라 전체 텍스트 검색에도 사용됩니다.

기하 및 범위 쿼리 에 최적화되어 있습니다. PostGIS (공간 데이터 인덱싱)에서 사용됩니다.

예시)

CREATE INDEX idx_locations ON places USING gist(location); BRIN (블록 범위 인덱스) 대용량의 순차적으로 저장된 데이터(시계열 데이터와 같은)에 효율적입니다.

B-트리보다 저장 공간을 적게 사용합니다.

예시)

CREATE INDEX idx_logs_timestamp ON logs USING brin(timestamp);

고급 인덱싱 전략 복합 인덱스 자주 함께 조회되는 여러 열에 대해 인덱스를 생성합니다.

CREATE INDEX idx_orders_user_date ON orders(user_id, order_date);

B-트리, GiST, GIN, BRIN 인덱스 유형만이 다중 키 열 인덱스를 지원합니다. 다중 키 열의 사용 가능 여부는 INCLUDE 열을 인덱스에 추가할 수 있는지와는 별개입니다. 인덱스는 INCLUDE 열을 포함하여 최대 32개의 열을 가질 수 있습니다.

부분 인덱스 공간을 절약하기 위해 데이터의 일부분만 인덱싱합니다.

CREATE INDEX idx_active_users ON users(email) WHERE is_active = true;

부분 인덱스는 테이블의 일부분에 대해 구축된 인덱스입니다. 이 부분 집합은 조건식에 의해 정의됩니다. 해당 인덱스는 조건을 만족하는 테이블 행에 대한 항목만 포함합니다.

커버링 인덱스 메인 테이블에 접근하지 않도록 추가 열을 저장합니다.

CREATE INDEX idx_orders_covering ON orders(user_id, order_date) INCLUDE (total_price);

PostgreSQL의 모든 인덱스는 보조 인덱스입니다. 이는 각 인덱스가 테이블의 주요 데이터 영역과 별도로 저장된다는 것을 의미합니다.

고유 인덱스 열의 고유성을 효율적으로 보장합니다.

CREATE UNIQUE INDEX idx_unique_email ON users(email);

인덱스를 사용하여 하나의 열 또는 여러 열의 조합된 값의 고유성을 강제할 수 있습니다. B-tree 인덱스만이 고유 인덱스로 선언될 수 있습니다.

트레이드오프: 읽기 vs 쓰기 성능

읽기 작업이 많은 애플리케이션이라면, 인덱스 사용은 당연한 선택입니다. 반면, 쓰기 작업이 많은 경우에는 신중하게 선택해야 합니다.

정리하며 인덱스는 PostgreSQL에서 성능을 획기적으로 개선할 수 있는 핵심 요소 중 하나입니다. 다음 사항을 고려하여 전략적으로 활용하시기 바랍니다.

필터링, 정렬, 조인에 인덱스를 사용하세요. 자주 업데이트되는 테이블에는 인덱스 사용을 피하세요. 인덱스를 추가하기 전에 EXPLAIN ANALYZE로 테스트하세요. 쿼리 패턴에 맞는 적절한 인덱스 유형을 선택하세요.

Speed Up DB Queries Like a Pro