본문으로 건너뛰기
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.1 튜닝 워크플로우13.3 파티셔닝을 활용한 튜닝