본문으로 건너뛰기
Docs
Part XIII. 성능과 튜닝
13.2 인덱스 전략

13.2 인덱스 전략

Part VI에서 인덱스의 타입을 봤다면, 어떤 인덱스를 언제 만들고 언제 안 만들지의 운영 의사결정 가이드. 잘못된 인덱스는 만들지 않은 것보다 나쁜 결과를 낸다 — INSERT 비용·BLOAT·플래너 부담입니다.

인덱스 추가의 비용

인덱스 N개를 추가하면:

비용영향
디스크N배 row 데이터
INSERT/UPDATEN개 인덱스 갱신
HOT update 가능성변경 컬럼에 인덱스 있으면 HOT 안 됨
autovacuumN개 인덱스 정리
플래너후보 plan 폭증
BLOAT별도 관리

이래서 필요한 만큼만, 좁게 가 황금률.

인덱스 추가 의사결정 

    flowchart TD
  Q["WHERE에 자주 등장하는 컬럼?"]
  Q --> S{selectivity 좋은가?}
  S -- "yes (적은 row 매칭)" --> ADD["인덱스 후보"]
  S -- "no (대부분 row 매칭)" --> NO["인덱스 효과 적음 → seq scan이 더 빠름"]
  ADD --> COMBO{여러 컬럼 결합?}
  COMBO -- "yes" --> COMP["composite 인덱스"]
  COMBO -- "no" --> SINGLE["단일 컬럼"]
  COMP --> PART{일부 row만?}
  SINGLE --> PART
  PART -- "yes" --> PARTIAL["partial index 검토"]
  PART -- "no" --> FULL["full index"]

  classDef ok fill:#d1fae5,stroke:#047857,color:#064e3b
  classDef no fill:#fee2e2,stroke:#b91c1c,color:#7f1d1d
  classDef q fill:#dbeafe,stroke:#1d4ed8,color:#1e3a8a
  class ADD,COMP,SINGLE,PARTIAL,FULL ok
  class NO no
  class Q,S,COMBO,PART q

selectivity 기준

selectivity인덱스 효과
< 1% (적은 row)매우 좋음
1~10%좋음
10~30%보통 (Bitmap scan이 도와줌)
> 30%seq scan이 더 빠를 가능성

status 컬럼처럼 대부분 row가 같은 값은 인덱스 효과 적습니다. partial index가 답입니다.

-- 효과 적음
CREATE INDEX ON orders(status);   -- 90%가 'paid'

-- 효과 좋음
CREATE INDEX ON orders(user_id) WHERE status = 'pending';

composite index — 컬럼 순서 

CREATE INDEX ON orders(user_id, status, created_at);

가장 좌측 prefix 기준:

WHERE활용
user_id = 42가능
user_id = 42 AND status = 'paid'가능
user_id = 42 AND status = 'paid' AND created_at > ...가능
status = 'paid' 만부분 활용 또는 skip scan(PG 18+ 일부)
created_at > ... 만활용 안 됨

선행 컬럼 선택 원칙:

  1. WHERE에 항상 등장 (등호 위주)
  2. selectivity 높음
  3. 마지막 컬럼은 정렬·범위 조건용

INCLUDE — covering index 

CREATE INDEX ON orders(user_id, status)
  INCLUDE (created_at, amount);

INCLUDE 컬럼은 정렬·검색에 안 쓰이지만 index-only scan(4.3)에 답을 제공합니다.

사용메모
자주 SELECT하는 컬럼 묶음크지 않으면 INCLUDE 권장
큰 텍스트·JSONB인덱스 비대 — 안 좋음
정렬에 쓰일 컬럼INCLUDE 대신 키 컬럼으로

자주 보는 안티패턴 인덱스

1. 중복 인덱스 

CREATE INDEX ON orders(user_id);
CREATE INDEX ON orders(user_id, status);
-- 첫 번째는 두 번째에 포함됨 — 중복

후자가 있으면 전자는 대부분 불필요. 6.7의 중복 점검 SQL로 정리합니다.

2. 변경 자주 되는 컬럼에 인덱스 

CREATE INDEX ON orders(updated_at);
-- updated_at은 매 UPDATE마다 갱신 — 인덱스도 갱신

HOT update 차단 효과 + UPDATE 부하 ↑. 정말 필요한가 검토합니다.

3. NULL이 많은 컬럼에 full 인덱스 

CREATE INDEX ON users(deleted_at);
-- 99%가 NULL이면 partial이 답
CREATE INDEX ON users(deleted_at) WHERE deleted_at IS NOT NULL;

PostgreSQL은 NULL도 인덱싱하지만 대부분 NULL이면 partial이 99% 작습니다.

4. 미사용 인덱스 

SELECT relname, indexrelname,
       pg_size_pretty(pg_relation_size(indexrelid)) AS size,
       idx_scan
  FROM pg_stat_user_indexes
 WHERE idx_scan = 0 ORDER BY pg_relation_size(indexrelid) DESC;

idx_scan = 0이고 최근 만든 게 아닌 인덱스 = drop 후보입니다.

5. 함수 호출 WHERE인데 일반 인덱스 

CREATE INDEX ON users(email);
SELECT * FROM users WHERE LOWER(email) = 'a@b.com';   -- 인덱스 못 씀

-- 답
CREATE INDEX ON users(LOWER(email));   -- expression index (6.6)

JSONB 인덱스 전략 

-- 전체 트리에 GIN — containment 다양한 쿼리
CREATE INDEX ON events USING gin (payload jsonb_path_ops);

-- 특정 경로에 B-tree — 정확 등호·범위
CREATE INDEX ON events ((payload->>'user_id'));
CREATE INDEX ON events (((payload->'meta'->>'priority')::int));

운영 표준 = GIN + 자주 쓰는 경로의 B-tree 조합. JSONB 컬럼 전체에 GIN 하나만 두는 패턴은 쿼리 패턴이 다양할 때.

부하 분리 — read replica용 인덱스

OLTP에는 가벼운 인덱스, 분석 standby에 별도 무거운 인덱스 두는 패턴입니다.

-- 분석 standby에서만
CREATE INDEX CONCURRENTLY ON orders (created_at) WHERE status = 'paid';

standby는 promote 시 옛 primary의 모든 인덱스도 함께 가지므로 기본은 같음. 다른 패턴은 logical replication이나 ETL 분리가 답입니다.

정기 점검 SQL 

-- 사용량 낮은 인덱스
SELECT s.relname, s.indexrelname, s.idx_scan,
       pg_size_pretty(pg_relation_size(s.indexrelid)) AS size
  FROM pg_stat_user_indexes s
  JOIN pg_index i ON s.indexrelid = i.indexrelid
 WHERE s.idx_scan < 100  -- 운영 시작 후 100회 미만
   AND NOT i.indisunique
   AND pg_relation_size(s.indexrelid) > 10*1024*1024
 ORDER BY s.idx_scan, pg_relation_size(s.indexrelid) DESC;

-- 중복 후보
SELECT indrelid::regclass, array_agg(indexrelid::regclass)
  FROM pg_index
 GROUP BY indrelid, indkey
HAVING count(*) > 1;

월 점검 항목.

인덱스 만들 때 — CONCURRENTLY

운영 중에는 항상 CONCURRENTLY (6.1).

CREATE INDEX CONCURRENTLY ON orders (user_id, status);

진행률:

SELECT * FROM pg_stat_progress_create_index;

안티패턴 요약

패턴문제답
모든 컬럼에 인덱스INSERT·BLOAT 폭증WHERE 패턴 분석
함수 호출 WHERE인데 일반 인덱스활용 안 됨expression index
status 같은 low-selectivity에 full효과 적음partial
INCLUDE에 큰 텍스트인덱스 비대좁은 컬럼만
중복 인덱스비용 두 배정기 점검
idx_scan = 0이지만 drop 안 함의미 없는 비용정리
좋은 인덱스 = 작고 많이 쓰는 것. 적은 컬럼·partial·자주 쓰이는 — 이 세 조건을 만족하는 인덱스가 가장 효율적입니다.

정리

  • 인덱스 추가는 필요한 만큼만, 좁게
  • selectivity 1~10% 컬럼이 인덱스 효과 가장 큼
  • composite는 가장 왼쪽부터 활용합니다. 순서가 핵심
  • partial·expression·INCLUDE로 정밀하게
  • 미사용·중복 인덱스 정기 정리
  • JSONB는 GIN + 자주 쓰는 경로 B-tree 조합
  • 운영 중 인덱스는 항상 CONCURRENTLY

다음 절(13.3)에서는 큰 테이블의 성능을 결정하는 파티셔닝 튜닝을 봅니다.

13.1 튜닝 워크플로우13.3 파티셔닝을 활용한 튜닝