백엔드 데이터 인프라 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) — 부분 인덱스로 가거나 아예 빼기
• 자주 UPDATE — HOT(Heap-Only Tuple, 인덱스 갱신 회피) 활용 위해 피하기
• 거의 조회 안 함

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이면 안 쓰여서 제거 후보, 매우 높으면 핵심 인덱스라 절대 제거하면 안 됩니다. 큰 크기에 낮은 사용이 겹치면 비용 대비 효율이 떨어지는 거예요.

안 쓰는 인덱스 찾기

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가지 쿼리 패턴이 모두 빨라집니다. BRIN(Block Range Index, 블록 범위 요약 인덱스)은 시계열처럼 자연 정렬된 대용량 컬럼에 어울리는 가벼운 인덱스예요.

인덱스 + 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% 라면 그 90% 만 인덱싱해서 크기를 1/10 로 줄일 수 있어요. 인덱스 갱신도 블로트도 모두 적게 듭니다.

인덱스 + JPA

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

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

JPA(Java Persistence API, 자바 ORM 표준)가 자동 생성하는 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주 모니터 후 제거
• 안 쓰는 인덱스 = 디스크·검색 비용
• 한국 백엔드 운영 = 분기별 인덱스 점검 표준

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

이전 글:

• 31편 — 쿼리 종합 WHERE GROUP BY HAVING ORDER BY
• 32편 — 데이터 타입 개요
• 33편 — JSON과 JSONB 깊이
• 34편 — 인덱스 소개와 큰 그림
• 35편 — 인덱스 종류 6가지 B-Tree·Hash·GIN·GiST·BRIN·SP-GiST

다음 글:

• 37편 — MVCC 소개
• 38편 — MVCC 격리 수준과 동시성 함정
• 39편 — 전문 검색 to_tsvector to_tsquery
• 40편 — EXPLAIN 쿼리 계획 읽기
• 41편 — 성능 팁 종합