백엔드 데이터 인프라 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). 디스크 페이지 단위로 효율적.

인덱스의 트레이드오프

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

영향	좋음	나쁨
SELECT 속도	수십~수만 배 빠름
INSERT 속도		인덱스 N개면 N번 갱신
UPDATE 속도		변경 컬럼 인덱스 갱신 (HOT 회피)
DELETE 속도		인덱스 항목 삭제
디스크 공간		인덱스마다 별도 공간
메모리		자주 쓰는 인덱스는 캐시

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

인덱스 만드는 기본

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.

인덱스 안 박는 컬럼

컬럼 종류	인덱스?
WHERE 자주 사용	박기
ORDER BY 자주 사용	박기
JOIN 키	박기 (외래 키 자식)
카디널리티 낮음 (TRUE/FALSE)	신중
자주 UPDATE	신중 (HOT 회피)
한 번 INSERT 후 안 변경	OK
TEXT 큰 컬럼 (게시글 본문 등)	풀스캔으로 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

자주 UPDATE 되는 컬럼에 인덱스 = HOT 불가. 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 인덱스
• 한국 회사 표준 = 외래 키 + 자주 검색 + 부분 인덱스

시리즈 다른 편

• Part 2 SQL Language 깊이: 32편 데이터 타입 개요 · 33편 JSONB 깊이 · 34편 (현재 글)

시리즈 다음 글

다음 글(35편)에서는 인덱스 종류 6가지 — B-Tree·Hash·GIN·GiST·BRIN·SP-GiST.

공식 문서: PostgreSQL 18 — Indexes Introduction에서 더 자세한 사양을 확인할 수 있어요.