백엔드 데이터 인프라 36편 — 인덱스 종합 운영 전략과 튜닝

이 글은 백엔드 데이터 인프라 시리즈 70편 중 36편이에요. 34편 인덱스 소개 ·35편 인덱스 종류 까지 인덱스의 "기본·종류" 를 다뤘으니, 이번 36편은 운영 전략 종합.

운영 인덱스 라이프사이클

1. 설계 (스키마 + 자주 쓰이는 쿼리)
2. 생성 (CONCURRENTLY)
3. 검증 (EXPLAIN ANALYZE)
4. 모니터링 (pg_stat_user_indexes)
5. 튜닝 (재설계 또는 REINDEX)
6. 제거 (안 쓰는 인덱스)

이 6단계가 운영 표준 흐름.

1단계 — 설계

질문: 어떤 쿼리가 자주 나오나?

운영 시작 전 — "어떤 SELECT·WHERE·JOIN 이 가장 자주 나올지" 예측. 90% 의 쿼리를 커버하는 인덱스 셋 설계.

인덱스 박을 곳

• 모든 PRIMARY KEY (자동)
• 모든 UNIQUE 제약 (자동)
• 모든 외래 키 (수동)
• 자주 WHERE 조건 컬럼
• 자주 ORDER BY 컬럼 (방향 일치)
• 자주 JOIN 키

인덱스 안 박을 곳

• 카디널리티 매우 낮음 (BOOLEAN) — 부분 인덱스 또는 X
• 자주 UPDATE — HOT 회피
• 거의 조회 안 함

2단계 — 생성

-- 운영 환경 = CONCURRENTLY 무조건
CREATE INDEX CONCURRENTLY idx_orders_user_status
    ON orders(user_id, status);

CONCURRENTLY 만들 때 함정: - 실패하면 — INVALID 인덱스가 남음 (사용 안 됨, 디스크만 차지)

-- INVALID 인덱스 찾기
SELECT * FROM pg_indexes
WHERE indexname IN (
    SELECT indexrelid::regclass::text FROM pg_index WHERE NOT indisvalid
);

-- DROP 후 재생성
DROP INDEX CONCURRENTLY idx_invalid;
CREATE INDEX CONCURRENTLY idx_orders_user_status ON orders(user_id, status);

3단계 — 검증

EXPLAIN ANALYZE
SELECT * FROM orders WHERE user_id = 1 AND status = 'PAID'
ORDER BY created_at DESC LIMIT 10;

좋은 출력:

Limit (cost=0.42..8.44 rows=10)
  ->  Index Scan using idx_orders_user_status on orders
        Index Cond: (user_id = 1 AND status = 'PAID')
        Filter: (created_at IS NOT NULL)
Execution Time: 2ms

나쁜 출력:

Limit (cost=12345.00..12345.10 rows=10)
  ->  Seq Scan on orders
        Filter: (...)
Execution Time: 5000ms

Seq Scan 보이면 — 인덱스 안 쓰임. 통계 갱신 또는 인덱스 재검토.

4단계 — 모니터링

-- 자주 쓰이는 인덱스 TOP
SELECT
    schemaname || '.' || tablename AS table,
    indexname,
    idx_scan AS scans,
    pg_size_pretty(pg_relation_size(indexrelid)) AS size,
    idx_tup_read AS rows_read,
    ROUND(idx_tup_fetch::numeric / NULLIF(idx_tup_read, 0) * 100, 2) AS hit_rate
FROM pg_stat_user_indexes
JOIN pg_index USING (indexrelid)
WHERE NOT indisunique
ORDER BY idx_scan DESC;

지표: - idx_scan 0 = 안 쓰임 (제거 후보) - idx_scan 매우 높음 = 핵심 인덱스 (절대 제거 X) - 큰 크기 + 낮은 사용 = 비용 대비 효율 낮음

안 쓰는 인덱스 찾기

SELECT
    schemaname || '.' || tablename AS table,
    indexname,
    pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
JOIN pg_index USING (indexrelid)
WHERE idx_scan = 0
AND NOT indisunique
AND NOT indisprimary
ORDER BY pg_relation_size(indexrelid) DESC;

UNIQUE·PRIMARY 는 제외 (제약 보장).

5단계 — 튜닝

인덱스 블로트

-- 인덱스 크기 vs 데이터 크기
SELECT
    indexname,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
    pg_size_pretty(pg_relation_size(indrelid)) AS table_size,
    ROUND(pg_relation_size(indexrelid)::numeric / NULLIF(pg_relation_size(indrelid), 0), 2) AS ratio
FROM pg_stat_user_indexes
JOIN pg_index USING (indexrelid);

인덱스가 데이터의 2배+ = 블로트 의심. REINDEX.

REINDEX CONCURRENTLY

REINDEX INDEX CONCURRENTLY idx_orders_user_status;

-- 또는 테이블 전체
REINDEX TABLE CONCURRENTLY orders;

PG 12+. 락 없이 인덱스 재구성. 옛 인덱스 → 새 인덱스 → 옛 DROP 흐름.

통계 갱신 — ANALYZE

ANALYZE orders;

PG 계획자 = 통계 기반 결정. 통계 부정확하면 — 잘못된 인덱스 선택. autovacuum 이 자동 갱신, 대량 DML 후엔 수동.

6단계 — 제거

-- 안 쓰는 인덱스 (idx_scan = 0, 충분한 운영 기간)
DROP INDEX CONCURRENTLY idx_old;

운영에서 — 최소 1주 ~ 1달 모니터 후 제거. 한 번 지운 인덱스 = 재생성 비용 큼.

실전 — 복합 인덱스 설계 예

시나리오: 전자상거래 주문 시스템

쿼리 패턴:

-- 사용자의 최근 주문
SELECT * FROM orders WHERE user_id = ? ORDER BY created_at DESC LIMIT 20;

-- 상태별 주문 조회
SELECT * FROM orders WHERE status = ? AND created_at > ? ORDER BY created_at DESC;

-- 통계
SELECT user_id, COUNT(*) FROM orders WHERE created_at > ? GROUP BY user_id;

설계:

-- 1. 사용자별 최근 주문 (user_id 가 가장 선별적)
CREATE INDEX CONCURRENTLY idx_orders_user_created
    ON orders(user_id, created_at DESC);

-- 2. 상태 + 시간 (상태가 적고 시간 범위)
CREATE INDEX CONCURRENTLY idx_orders_status_created
    ON orders(status, created_at DESC);

-- 3. 시계열 통계 (대용량 — BRIN)
CREATE INDEX CONCURRENTLY idx_orders_created_brin
    ON orders USING BRIN (created_at);

3개 인덱스 = 위 3가지 쿼리 패턴 모두 빠름.

인덱스 + JOIN

SELECT u.name, o.amount
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.deleted_at IS NULL
ORDER BY o.created_at DESC LIMIT 100;

필요 인덱스: - users(id) — PRIMARY KEY 자동 - users(deleted_at) 또는 부분 인덱스 - orders(user_id, created_at DESC) — JOIN + ORDER BY

이 3개로 — JOIN + 필터 + 정렬 모두 인덱스 활용.

부분 인덱스 운영 예

-- Soft Delete + 활성 사용자
CREATE INDEX idx_users_email_active
    ON users(email) WHERE deleted_at IS NULL;

-- 활성 상태별
CREATE INDEX idx_orders_pending
    ON orders(user_id, created_at)
    WHERE status = 'PENDING';

90% 활성 사용자만 인덱스 = 크기 1/10. 인덱스 갱신·블로트 모두 적음.

인덱스 + JPA

자바 백엔드 입문 50편 QueryDSL 의 쿼리들이 — 어떤 인덱스가 박혀야 빠른가 알아야 운영 단계 가능.

List<Order> findByUserIdAndStatusOrderByCreatedAtDesc(Long userId, String status);
// → CREATE INDEX ON orders(user_id, status, created_at DESC);

JPA 가 자동 생성하는 SQL 의 WHERE·ORDER BY 분석해 인덱스 설계.

함정 7가지

(1) CONCURRENTLY 안 박음

운영 락 → 다른 트랜잭션 대기. 무조건 CONCURRENTLY.

(2) INVALID 인덱스 방치

CONCURRENTLY 실패 = INVALID 인덱스. 디스크·검색 비용. 검출·정리.

(3) 안 쓰는 인덱스 누적

idx_scan = 0 인 인덱스가 1년+ 박혀 있음. 주기적 정리.

(4) 통계 갱신 안 함

대량 DML 후 ANALYZE 안 하면 계획자 잘못 판단.

(5) 복합 인덱스 순서 잘못

선별성 낮은 컬럼 앞 = 효율 폭락. 35편 참고.

(6) Index Only Scan 안 활용

INCLUDE 컬럼으로 — 테이블 안 봐도 답 가능. 인덱스 약간 크지만 쿼리 매우 빠름.

(7) 인덱스 너무 많음

쓰기 성능 폭락. 분기별 정리.

한 줄 정리 — 인덱스 운영 6단계. CONCURRENTLY 무조건. INVALID·블로트·안 쓰는 인덱스 검출. REINDEX CONCURRENTLY 로 무중단 재구성. 부분 인덱스·INCLUDE 활용. 복합 인덱스 순서 = 선별성 + 정확 비교 + 범위. ANALYZE 후 EXPLAIN 으로 검증.

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

• 운영 6단계 = 설계·생성·검증·모니터링·튜닝·제거
• 박을 곳 = PK·UNIQUE·FK·자주 WHERE·ORDER BY·JOIN
• 안 박을 곳 = 카디널리티 낮음·자주 UPDATE
• CONCURRENTLY = 락 없이 (운영)
• INVALID 인덱스 = CONCURRENTLY 실패 잔존
• pg_index.indisvalid = false 검출
• DROP + 재생성
• EXPLAIN ANALYZE = 사용 확인
• Seq Scan vs Index Scan
• Index Only Scan + Heap Fetches: 0
• pg_stat_user_indexes.idx_scan = 사용 통계
• idx_scan = 0 → 제거 후보
• 인덱스 크기 vs 데이터 = 블로트
• REINDEX CONCURRENTLY = 무중단 재구성 (PG 12+)
• ANALYZE = 통계 갱신
• 대량 DML 후 = ANALYZE
• 복합 인덱스 = 선별성·정확 비교·범위 순서
• 부분 인덱스 = WHERE 조건 (Soft Delete)
• INCLUDE = 커버링 인덱스
• 인덱스 너무 많음 = 쓰기 폭락 + 분기별 정리
• JPA SQL 분석 후 인덱스 설계
• @ManyToOne·@JoinColumn = FK 인덱스 필요
• 운영 표준 = 1주 모니터 후 제거
• 안 쓰는 인덱스 = 디스크·검색 비용
• 한국 백엔드 운영 = 분기별 인덱스 점검 표준

시리즈 다른 편

• Part 2 SQL Language 깊이: 33편 JSONB · 34편 인덱스 소개 · 35편 인덱스 종류 · 36편 (현재 글)

시리즈 다음 글

다음 글(37편)에서는 MVCC 소개 — Multi-Version Concurrency Control 의 큰 그림.

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