백엔드 데이터 인프라 34편 — 인덱스 소개와 큰 그림

이 글은 백엔드 데이터 인프라 시리즈 70편 중 34편이에요. 인덱스는 PG 성능의 가장 큰 결정 요소. 이번 34편은 그 큰 그림 — "왜 빠른가, 언제 만드나".

인덱스가 없으면 — 풀스캔

SELECT * FROM users WHERE email = 'alice@example.com';

인덱스 없으면 users 테이블의 모든 행을 차례로 검사해요. 1000행이면 OK, 1000만 행이면 5초+. "풀스캔 = 운영의 적".

EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'alice@example.com';
-- Seq Scan on users (cost=0.00..18334.00 rows=1)
--   Filter: (email = 'alice@example.com')
-- Planning Time: 0.1ms
-- Execution Time: 4823ms      ← 5초!

해결은 인덱스 박기예요.

CREATE INDEX idx_users_email ON users(email);

EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'alice@example.com';
-- Index Scan using idx_users_email (cost=0.42..8.44 rows=1)
-- Execution Time: 0.5ms       ← 10000배 빨라짐

인덱스가 왜 빠른가 — B-Tree 직관

PG 기본 인덱스는 B-Tree (Balanced Tree, 균형 잡힌 트리). 책의 "색인" 과 같은 구조예요.

            [50]
           /    \
       [25]      [75]
      /    \    /    \
   [10] [40] [60] [90]

1000만 행에서 50 찾는 데 25번 비교면 끝나요 (log₂ 10000000 ≈ 23). 풀스캔의 1000만 회 vs B-Tree 의 25회.

PG B-Tree는 각 노드에 여러 키를 박은 변형(M-way tree, 한 노드가 여러 자식을 갖는 다진 트리)이에요. 디스크 페이지 단위로 효율적입니다.

인덱스의 트레이드오프

인덱스가 "무조건 좋은" 게 아님:

룰은 단순해요 — "필요한 인덱스만". 모든 컬럼에 박지 말 것.

인덱스 만드는 기본

CREATE INDEX idx_users_email ON users(email);

명명은 idx_<테이블>_<컬럼> 표준을 쓰고, 단순 1컬럼 인덱스, 기본은 B-Tree 입니다.

UNIQUE 인덱스

CREATE UNIQUE INDEX idx_users_email_unique ON users(email);

조회 + 중복 방지 + UNIQUE 제약 효과.

복합 인덱스

CREATE INDEX idx_orders_user_status ON orders(user_id, status);

여러 컬럼 조합. "순서가 중요" — 35편에서 깊이.

부분 인덱스

CREATE INDEX idx_users_email_active
    ON users(email) WHERE deleted_at IS NULL;

특정 조건의 행만 인덱스. 크기 작고 빠름. Soft Delete(삭제 플래그만 세우고 실제 행은 남기는 방식)의 핵심이에요.

표현식 인덱스

CREATE INDEX idx_users_email_lower ON users (LOWER(email));

SELECT * FROM users WHERE LOWER(email) = 'alice@example.com';
-- 함수 결과를 인덱스에서 직접

함수·연산식 결과를 인덱스로 박는 방식.

CONCURRENTLY — 락 없이

CREATE INDEX CONCURRENTLY idx_users_email ON users(email);

기본 CREATE INDEX는 테이블 쓰기 락을 잡아서 운영 중엔 위험해요. CONCURRENTLY를 붙이면 락이 거의 없어요(인덱스를 두 번 빌드해서 시간은 더 걸리지만 안전).

운영 인덱스 추가는 CONCURRENTLY 무조건입니다.

자동 생성되는 인덱스

-- PRIMARY KEY → 자동 unique 인덱스
CREATE TABLE users (id BIGSERIAL PRIMARY KEY, ...);
-- users_pkey UNIQUE INDEX 자동

-- UNIQUE 제약 → 자동 unique 인덱스
CREATE TABLE users (email TEXT UNIQUE);
-- users_email_key UNIQUE INDEX 자동

이건 별도로 박지 않아도 OK.

인덱스 안 박는 컬럼

여기서 카디널리티(컬럼 값의 고유성, 종류 수)가 핵심 판단 기준이에요.

인덱스 통계 확인

-- 인덱스 사용 통계
SELECT
    schemaname, tablename, indexname,
    idx_scan AS scans,
    idx_tup_read AS rows_read,
    idx_tup_fetch AS rows_fetched,
    pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE schemaname = 'public'
ORDER BY pg_relation_size(indexrelid) DESC;

idx_scan = 0 이면 안 쓰이는 인덱스라 제거 검토 대상, size 가 큰데 잘 안 쓰이면 이것도 검토 대상이에요.

인덱스 제거

DROP INDEX idx_users_email;
DROP INDEX CONCURRENTLY idx_users_email;   -- 락 없이
DROP INDEX IF EXISTS idx_users_email;

비싼 인덱스 — 큰 테이블

수억 행 테이블에 인덱스를 추가하면 수십 분에서 수 시간까지 걸립니다. 대응은 두 가지예요.

-- 백그라운드 빌드
CREATE INDEX CONCURRENTLY ON huge_table(col);

-- 또는 파티션별 분리
-- 새 파티션부터 인덱스 만들고 옛 파티션은 점진 적용

인덱스의 부정확한 직관 — 함정

(1) "WHERE 컬럼에 무조건 인덱스"

WHERE is_deleted = FALSE   -- 99% 의 행이 매칭 → 인덱스 쓸 이유 X

카디널리티가 낮으면 풀스캔이 더 빠를 수도 있어요. 이럴 땐 부분 인덱스가 정답.

(2) "인덱스 = 항상 빠름"

WHERE col > 100   -- 90% 행 매칭 → 풀스캔이 더 빠름

PG 계획자가 "인덱스 vs 풀스캔" 을 자동 선택해요. EXPLAIN 으로 확인.

(3) "복합 인덱스 = 컬럼 순서 무관"

CREATE INDEX idx ON orders(user_id, status);

WHERE user_id = 1 AND status = 'PAID'   -- 인덱스 사용
WHERE user_id = 1                        -- 인덱스 사용 (prefix)
WHERE status = 'PAID'                    -- 인덱스 사용 X

복합 인덱스는 "왼쪽부터 prefix 매칭" 이에요. 35편에서 깊이.

EXPLAIN — 인덱스 사용 확인

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

출력에서 Seq Scan 은 풀스캔(인덱스 미사용), Index Scan 은 인덱스 사용, Index Only Scan 은 인덱스만으로 끝낸 것(테이블 안 봄), Bitmap Index Scan 은 여러 인덱스를 결합한 경우입니다. 40편에서 깊이.

함정 5가지

(1) 모든 컬럼에 인덱스

쓰기 성능 폭락. 필요한 컬럼만.

(2) CONCURRENTLY 안 박고 운영 인덱스

테이블 락이 걸리면 다른 트랜잭션이 줄줄이 대기해요. CONCURRENTLY 무조건.

(3) 인덱스 사용 안 확인

pg_stat_user_indexes.idx_scan = 0 은 쓸모없는 인덱스. 주기적으로 정리.

(4) HOT UPDATE 인식 X

HOT(Heap-Only Tuple, 인덱스 갱신을 건너뛰는 UPDATE 최적화)은 자주 UPDATE 되는 컬럼에 인덱스가 박혀 있으면 동작하지 않아요. 26편 UPDATE 깊이 참고.

(5) JOIN 키에 인덱스 없음

FROM orders JOIN users ON orders.user_id = users.id
-- users.id 는 PRIMARY KEY 자동 인덱스
-- orders.user_id 는? 외래 키여도 자동 인덱스 X

FK 컬럼엔 무조건 인덱스를 박아 둡니다. 15편 참고.

한 줄 정리 — 인덱스 = B-Tree 기반 색인. 풀스캔의 수십~수만 배 빠름. 단 INSERT·UPDATE·DELETE 비용. 운영 추가 = CONCURRENTLY. 외래 키·자주 WHERE 컬럼 박고, 안 쓰는 인덱스는 제거. EXPLAIN 으로 검증.

시험 직전 한 번 더 — 인덱스 소개 입문자가 매번 헷갈리는 것

• 인덱스 = 책 색인 (B-Tree)
• 1000만 행에서 log₂ ≈ 25번 비교
• 풀스캔 vs 인덱스 = 수십~수만 배 차이
• 트레이드오프 = SELECT 빠름, INSERT·UPDATE·DELETE 느림
• CREATE INDEX name ON table(col)
• UNIQUE INDEX = 조회 + 중복 방지
• 복합 인덱스 = (col1, col2) (순서 중요)
• 부분 인덱스 = WHERE 조건
• 표현식 인덱스 = (LOWER(col))
• CONCURRENTLY = 락 없이 (운영 무조건)
• PRIMARY KEY = 자동 unique 인덱스
• UNIQUE 제약 = 자동 unique 인덱스
• 외래 키 자식 = 자동 인덱스 X — 박기
• 카디널리티 낮은 컬럼 = 부분 인덱스
• 자주 UPDATE 컬럼 = 신중 (HOT)
• pg_stat_user_indexes = 사용 통계
• idx_scan = 0 = 쓸모없는 인덱스 (제거)
• DROP INDEX [CONCURRENTLY] [IF EXISTS]
• EXPLAIN 으로 사용 확인
• Seq Scan = 풀스캔, Index Scan = 인덱스
• Index Only Scan = 인덱스만으로 (테이블 X)
• Bitmap Index Scan = 여러 인덱스 결합
• 모든 컬럼 인덱스 X
• 인덱스 추가 후 = ANALYZE
• 운영 표준 = WHERE·ORDER BY·JOIN 인덱스
• 한국 회사 표준 = 외래 키 + 자주 검색 + 부분 인덱스

시리즈 다른 편 (앞뒤 글 모음)

이전 글:

• 29편 — SELECT 절과 표현식의 깊이
• 30편 — 테이블 표현식 FROM 절의 깊이
• 31편 — 쿼리 종합 WHERE GROUP BY HAVING ORDER BY
• 32편 — 데이터 타입 개요
• 33편 — JSON과 JSONB 깊이

다음 글:

• 35편 — 인덱스 종류 6가지 B-Tree·Hash·GIN·GiST·BRIN·SP-GiST
• 36편 — 인덱스 종합 운영 전략과 튜닝
• 37편 — MVCC 소개
• 38편 — MVCC 격리 수준과 동시성 함정
• 39편 — 전문 검색 to_tsvector to_tsquery