PostgreSQL 인덱스 설계 가이드: B-Tree

PostgreSQL 인덱스가 중요한 이유

인덱스는 데이터베이스 성능의 핵심입니다. 적절한 인덱스 하나가 쿼리 응답 시간을 수 초에서 수 밀리초로 줄이고, 잘못된 인덱스 하나가 쓰기 성능을 절반으로 떨어뜨립니다. PostgreSQL은 B-Tree, Hash, GIN, GiST, BRIN 등 다양한 인덱스 타입을 지원하며, 각 타입은 특정 쿼리 패턴에 최적화되어 있습니다.

이 글에서는 인덱스 타입별 동작 원리, 복합 인덱스 컬럼 순서 전략, Partial/Covering 인덱스, EXPLAIN ANALYZE 읽는 법, 그리고 실무에서 자주 발생하는 인덱스 설계 실수까지 다룹니다.

인덱스 타입별 비교

타입	적합한 쿼리	지원 연산자	사용 사례
B-Tree (기본)	=, <, >, BETWEEN, ORDER BY	비교 연산자 전체	대부분의 쿼리
Hash	= (동등 비교만)	=	정확한 값 조회
GIN	배열 포함, 전문 검색	@>, &&, @@	JSONB, tsvector, 배열
GiST	범위, 근접, 포함	&&, @>, <->	지리 데이터, 범위 타입
BRIN	물리적으로 정렬된 데이터	<, >, =	시계열, 로그 테이블

B-Tree 인덱스: 기본이자 핵심

PostgreSQL에서 CREATE INDEX를 실행하면 기본적으로 B-Tree 인덱스가 생성됩니다. 정렬, 범위 검색, 동등 비교 모두 지원하며, 대부분의 OLTP 쿼리에 적합합니다.

-- 단일 컬럼 인덱스
CREATE INDEX idx_users_email ON users (email);

-- 유니크 인덱스
CREATE UNIQUE INDEX idx_users_email_unique ON users (email);

-- 내림차순 인덱스 (ORDER BY DESC 최적화)
CREATE INDEX idx_orders_created_desc ON orders (created_at DESC);

복합 인덱스: 컬럼 순서가 성능을 결정한다

복합 인덱스에서 컬럼 순서는 성능에 직접적인 영향을 미칩니다. PostgreSQL은 인덱스의 왼쪽(선행) 컬럼부터 사용합니다.

-- 올바른 순서: 동등 조건 → 범위 조건 → 정렬
CREATE INDEX idx_orders_status_created
    ON orders (status, created_at DESC);

-- 이 인덱스가 효과적인 쿼리
SELECT * FROM orders
WHERE status = 'pending'
ORDER BY created_at DESC
LIMIT 20;

-- 이 인덱스가 비효과적인 쿼리 (선행 컬럼 status 조건 없음)
SELECT * FROM orders
WHERE created_at > '2026-01-01';

컬럼 순서 결정 원칙

순서	기준	이유
1순위	동등 조건 (=) 컬럼	인덱스 범위를 가장 많이 좁힘
2순위	선택도가 높은 컬럼	고유 값이 많을수록 필터링 효과 큼
3순위	범위 조건 컬럼	범위 스캔 시작점 결정
4순위	ORDER BY 컬럼	정렬 연산 제거 (Index Scan)

Partial 인덱스: 조건부 인덱스로 크기 줄이기

Partial 인덱스는 WHERE 절을 추가하여 특정 조건의 행만 인덱싱합니다. 인덱스 크기를 줄이고 쓰기 성능을 보존합니다.

-- 활성 사용자만 인덱싱 (전체의 10%라면 인덱스 크기 90% 감소)
CREATE INDEX idx_users_active_email
    ON users (email)
    WHERE is_active = true;

-- 미처리 주문만 인덱싱
CREATE INDEX idx_orders_pending
    ON orders (created_at)
    WHERE status = 'pending';

-- 소프트 삭제된 행 제외
CREATE INDEX idx_posts_published
    ON posts (published_at DESC)
    WHERE deleted_at IS NULL;

Partial 인덱스가 사용되려면 쿼리의 WHERE 절이 인덱스의 조건을 포함해야 합니다. WHERE is_active = true AND email = 'test@example.com'처럼 조건이 일치해야 플래너가 인덱스를 선택합니다.

Covering 인덱스 (INCLUDE): Index-Only Scan 달성

PostgreSQL 11부터 INCLUDE 절로 Covering 인덱스를 생성할 수 있습니다. 쿼리에 필요한 모든 컬럼이 인덱스에 포함되면 테이블 접근 없이 인덱스만으로 결과를 반환합니다.

-- 주문 목록 조회: status로 필터, created_at 정렬, total_amount 표시
CREATE INDEX idx_orders_covering
    ON orders (status, created_at DESC)
    INCLUDE (total_amount, customer_id);

-- Index-Only Scan 발생
SELECT status, created_at, total_amount, customer_id
FROM orders
WHERE status = 'completed'
ORDER BY created_at DESC
LIMIT 50;

INCLUDE 컬럼은 인덱스 검색에는 사용되지 않고, 결과 반환에만 사용됩니다. B-Tree 정렬 구조에 포함되지 않으므로 인덱스 크기 증가가 적습니다.

GIN 인덱스: JSONB와 전문 검색

-- JSONB 필드 인덱싱
CREATE INDEX idx_products_attrs ON products USING GIN (attributes);

-- 쿼리: JSONB 키-값 포함 검색
SELECT * FROM products
WHERE attributes @> '{"color": "red", "size": "L"}';

-- 전문 검색 인덱싱
CREATE INDEX idx_articles_search
    ON articles USING GIN (to_tsvector('english', title || ' ' || body));

-- 쿼리: 전문 검색
SELECT * FROM articles
WHERE to_tsvector('english', title || ' ' || body) @@ to_tsquery('kubernetes & deployment');

GIN 인덱스는 읽기 성능이 뛰어나지만 쓰기 시 업데이트 비용이 높습니다. 쓰기가 많은 테이블에서는 fastupdate = off 옵션을 검토하세요.

BRIN 인덱스: 대용량 시계열 테이블의 선택

-- 로그 테이블: created_at이 물리적으로 정렬되어 있을 때
CREATE INDEX idx_logs_created_brin
    ON logs USING BRIN (created_at)
    WITH (pages_per_range = 32);

-- B-Tree 대비 인덱스 크기
-- B-Tree: 수백 MB → BRIN: 수 KB

BRIN은 데이터의 물리적 순서와 논리적 순서가 일치할 때만 효과적입니다. INSERT만 하는 append-only 테이블(로그, 이벤트)에 적합하고, UPDATE/DELETE가 빈번한 테이블에는 부적합합니다.

EXPLAIN ANALYZE 읽는 법

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM orders WHERE status = 'pending' ORDER BY created_at DESC LIMIT 20;

-- 결과 해석
-- Limit  (cost=0.42..15.30 rows=20 width=120) (actual time=0.035..0.089 rows=20 loops=1)
--   ->  Index Scan using idx_orders_status_created on orders
--         (cost=0.42..4521.10 rows=6078 width=120) (actual time=0.033..0.082 rows=20 loops=1)
--         Index Cond: (status = 'pending')
--         Buffers: shared hit=4
-- Planning Time: 0.150 ms
-- Execution Time: 0.115 ms

핵심 지표	확인 포인트
Scan 타입	Seq Scan → 인덱스 필요, Index Scan → 정상, Index Only Scan → 최적
actual rows vs rows	추정치와 실제 차이가 크면 ANALYZE 실행 필요
Buffers shared hit	캐시 히트, shared read가 많으면 메모리 부족
Sort Method	external merge → work_mem 부족, quicksort → 정상

인덱스 모니터링: 사용되지 않는 인덱스 찾기

-- 사용되지 않는 인덱스 조회
SELECT
    schemaname, tablename, indexname,
    idx_scan AS times_used,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
    AND indexrelid NOT IN (
        SELECT conindid FROM pg_constraint
        WHERE contype IN ('p', 'u')
    )
ORDER BY pg_relation_size(indexrelid) DESC;

-- 인덱스 대비 테이블 크기 비율
SELECT
    relname AS table_name,
    pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
    pg_size_pretty(pg_relation_size(relid)) AS table_size,
    pg_size_pretty(pg_total_relation_size(relid) - pg_relation_size(relid)) AS index_size
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC;

흔한 실수와 해결법

실수 1: 모든 컬럼에 인덱스 생성

인덱스는 INSERT, UPDATE, DELETE 성능을 저하시킵니다. 실제 쿼리 패턴을 분석한 뒤 필요한 인덱스만 생성하세요. pg_stat_user_indexes로 사용되지 않는 인덱스를 주기적으로 정리하세요.

실수 2: 낮은 선택도 컬럼에 인덱스 생성

boolean 컬럼(true/false)이나 status 컬럼(3~4가지 값)에 단독 인덱스를 만들면 플래너가 Seq Scan을 선택합니다. 이런 컬럼은 Partial 인덱스로 특정 값만 인덱싱하세요.

실수 3: LIKE ‘%keyword%’ 패턴에 B-Tree 기대

B-Tree 인덱스는 접두사 매칭(LIKE 'keyword%')만 지원합니다. 중간 매칭이 필요하면 GIN + pg_trgm 확장을 사용하세요.

CREATE EXTENSION pg_trgm;
CREATE INDEX idx_products_name_trgm ON products USING GIN (name gin_trgm_ops);

실수 4: 운영 중 인덱스 생성 시 테이블 잠금

기본 CREATE INDEX는 테이블에 쓰기 잠금을 겁니다. 운영 환경에서는 반드시 CONCURRENTLY를 사용하세요.

CREATE INDEX CONCURRENTLY idx_users_name ON users (name);

운영 체크리스트

주 1회 pg_stat_user_indexes에서 미사용 인덱스 점검 및 제거
새 쿼리 추가 시 EXPLAIN ANALYZE로 실행 계획 확인
복합 인덱스 컬럼 순서: 동등 → 선택도 → 범위 → 정렬
운영 환경 인덱스 생성은 CONCURRENTLY 필수
주기적으로 REINDEX CONCURRENTLY로 인덱스 bloat 해소
autovacuum 설정 점검 (인덱스 효율에 직접 영향)
인덱스 총 크기가 테이블 크기를 넘지 않는지 확인

마무리

PostgreSQL 인덱스 설계는 쿼리 패턴 분석에서 시작합니다. B-Tree로 대부분의 쿼리를 커버하고, Partial 인덱스로 크기를 줄이고, Covering 인덱스로 Index-Only Scan을 달성하고, GIN/BRIN으로 특수 데이터를 처리하세요. 인덱스는 만드는 것보다 관리하는 것이 더 중요합니다.

데이터베이스 커넥션 풀 최적화가 필요하다면 Spring Boot HikariCP 커넥션 풀 심화를 참고하세요. 캐시 계층 설계는 Nginx Reverse Proxy 캐싱 설정에서 다루고 있습니다.

운영 DB에서 pg_stat_user_indexes를 조회해 미사용 인덱스를 찾아보세요. 제거 전후 쓰기 성능 변화를 댓글로 공유해 주시면, 인덱스 리팩토링 전략을 추가로 안내드리겠습니다.