JSONB 인덱스 전략 실패와 복구: 쿼리 패턴 모르고 GIN 깔았던 2개월

Q: 언제 JSONB에 Expression B-tree를 선택해야 하는가?

**특정 키만 일관되게 조회·수정한다면 GIN이 아니라 Expression B-tree 인덱스를 먼저 검토해야 합니다.** 우리는 사용자 프로필의 `status`, `tier`, `updated_at` 세 필드를 WHERE 절과 ORDER BY에서 반복적으로 참조했는데, 모든 필드에 대해 GIN 인덱스(`jsonb_ops`)를 생성했습니다.

Q: GIN 인덱스 유지비용이 쓰기 성능을 왜 죽일까?

**JSONB의 모든 업데이트는 행 전체에 대한 락(row-level lock)을 발생시킵니다.** 우리 테이블에는 GIN 인덱스 3개가 있었는데, 각 INSERT/UPDATE마다 3개 인덱스 엔트리를 모두 갱신해야 했습니다.

Q: 정규화 판단: 언제 JSONB를 버려야 하나?

**3주차 분석에서 "hot fields"를 식별했습니다.** `status`, `tier`, `updated_at`, `last_login`이 전체 쿼리의 87%를 차지했습니다.

장애 요약

2025년 상반기, 사용자 프로필 메타데이터를 JSONB로 저장하는 테이블에서 데이터 쓰기 지연이 초단위에서 수초로 악화되었습니다. 초당 처리량(throughput)이 8,000 QPS에서 2,000 QPS로 떨어졌고, 데이터베이스 CPU는 70% 이상 유지되었습니다. 원인은 인덱스 설계의 근거 없는 선택이었습니다.

언제 JSONB에 Expression B-tree를 선택해야 하는가?

특정 키만 일관되게 조회·수정한다면 GIN이 아니라 Expression B-tree 인덱스를 먼저 검토해야 합니다. 우리는 사용자 프로필의 status, tier, updated_at 세 필드를 WHERE 절과 ORDER BY에서 반복적으로 참조했는데, 모든 필드에 대해 GIN 인덱스(jsonb_ops)를 생성했습니다.

핵심 수치:

GIN 인덱스 크기: 테이블 3.2GB 대비 약 2.8GB (인덱스가 테이블 크기에 근접)
Expression B-tree 인덱스(data->'status' 등)로 전환 후: 각 인덱스 약 80~120MB (GIN 대비 약 20분의 1)
INSERT 지연: GIN 환경에서 평균 8.5ms → B-tree 전환 후 1.2ms

이 비율은 PostgreSQL 공식 문서의 JSONB 인덱싱 가이드와 실제 프로덕션 측정값이 일치했습니다. 특정 키의 고정적 검색에서는 B-tree가 GIN보다 dramatically faster인 것을 직접 겪었습니다.

인덱스 선택 기준:

Expression B-tree: 고정된 키 (data->'status', data->>'tier')를 WHERE/ORDER BY에서 반복 사용
GIN (jsonb_path_ops): 임의의 키를 포함(@>) 검색하거나 스키마가 완전히 동적일 때

우리는 처음부터 쿼리 분석을 건너뛰고 "JSONB면 GIN이지"라는 관성으로 인덱스를 설계했습니다.

GIN 인덱스 유지비용이 쓰기 성능을 왜 죽일까?

JSONB의 모든 업데이트는 행 전체에 대한 락(row-level lock)을 발생시킵니다. 우리 테이블에는 GIN 인덱스 3개가 있었는데, 각 INSERT/UPDATE마다 3개 인덱스 엔트리를 모두 갱신해야 했습니다.

pg_stat_user_indexes를 조회한 결과:

                idx_name                | idx_scan | idx_tup_read | idx_tup_fetch
────────────────────────────────────────┼──────────┼──────────────┼───────────────
 idx_profile_data_gin                   |    12450 |     2847361  |     892103
 idx_profile_data_gin_status            |      680 |      18450   |      1823
 idx_profile_data_gin_tier              |      620 |      19203   |      892

GIN 인덱스 메인(idx_profile_data_gin)의 스캔 횟수가 다른 두 인덱스보다 18배 많았고, 대부분은 쓰기 후 유지비용(maintenance)이었습니다. 읽기는 상대적으로 적었는데, GIN의 높은 갱신 비용만 남겨진 상태였습니다.

JSONB 파싱 오버헤드 자체는 JSON(텍스트)보다 약간 느리다고 알려져 있지만, 실제 병목은 인덱스 갱신이었습니다. 3개의 GIN 인덱스가 각각 대규모 B+ 트리를 유지하느라 I/O 경합이 심했습니다.

쿼리 로그와 실제 패턴 재현

원인 확정을 위해 2주간의 슬로우 쿼리 로그(slow query log)와 EXPLAIN ANALYZE를 분석했습니다.

-- 실제 자주 나오는 쿼리 (전체의 약 68%)
SELECT id, email, data FROM profiles 
WHERE (data->>'status')::text = 'active' 
  AND (data->>'tier')::text IN ('premium', 'enterprise')
ORDER BY (data->>'updated_at')::timestamp DESC
LIMIT 100;

GIN 환경에서의 EXPLAIN 결과(간략):

Seq Scan on profiles (cost=0.00..285000.00 rows=1200)
  Filter: ((data->>'status') = 'active' ...)
  Planning Time: 0.234 ms
  Execution Time: 3847.123 ms

인덱스를 사용하지 않고 풀 테이블 스캔을 했습니다. GIN은 ->>(텍스트 추출) 다음의 형변환(::text, ::timestamp)을 최적화하지 못했습니다.

Expression B-tree 인덱스 생성 후:

CREATE INDEX idx_profile_status ON profiles 
  USING btree ((data->>'status'));
CREATE INDEX idx_profile_updated ON profiles 
  USING btree ((data->>'updated_at')::timestamp DESC);

동일 쿼리의 EXPLAIN:

Index Scan using idx_profile_status on profiles (cost=0.42..1823.45 rows=1200)
  Index Cond: ((data->>'status') = 'active')
  Planning Time: 0.089 ms
  Execution Time: 47.234 ms

응답 시간이 3,847ms → 47ms로 약 80배 개선되었습니다. 동시성 환경에서는 개별 쿼리 시간뿐 아니라 락 경합도 크게 줄었습니다.

정규화 판단: 언제 JSONB를 버려야 하나?

3주차 분석에서 "hot fields"를 식별했습니다. status, tier, updated_at, last_login이 전체 쿼리의 87%를 차지했습니다. 이들을 JSONB에서 빼내기로 결정했습니다.

마이그레이션 전략:

-- 1단계: 정규화 컬럼 추가
ALTER TABLE profiles ADD COLUMN 
  status TEXT NOT NULL DEFAULT 'pending';
ALTER TABLE profiles ADD COLUMN 
  tier TEXT NOT NULL DEFAULT 'free';
ALTER TABLE profiles ADD COLUMN 
  updated_at TIMESTAMP NOT NULL DEFAULT now();

-- 2단계: 기존 JSONB 데이터로 채우기
UPDATE profiles SET status = (data->>'status'),
       tier = (data->>'tier'),
       updated_at = (data->>'updated_at')::timestamp;

-- 3단계: 인덱스 생성 (Expression 아닌 일반 B-tree)
CREATE INDEX idx_profile_status ON profiles(status);
CREATE INDEX idx_profile_tier ON profiles(tier);
CREATE INDEX idx_profile_updated ON profiles(updated_at DESC);

-- 4단계: JSONB는 "long-tail 필드"만 유지
-- ALTER TABLE profiles DROP COLUMN data;
-- 아직 data에는 부가 메타데이터(notes, custom_attrs 등) 남김

결과:

INSERT: 2,000 QPS → 7,800 QPS (회복)
인덱스 크기: 2.8GB GIN 3개 → 정규화 인덱스 240MB + JSONB 보조 인덱스 60MB
쓰기 지연: 8.5ms → 1.1ms
읽기 응답(p50): 270ms → 23ms

정규화 기준:

추출할 것: WHERE/ORDER BY에 주기적으로 등장, 범위 검색 필요, 업데이트 빈도 높음
JSONB에 남길 것: 변동성 높음, 조회 빈도 낮음, 스키마 불안정적 (polymorphic 필드)

우리 사례에서는 약 30%의 데이터 구조만 정규화했고, 70%의 "long-tail" 메타필드는 여전히 JSONB에 두었습니다.

락과 동시성: JSONB 부분 업데이트의 함정

한 가지 더 발견한 문제: JSONB의 부분 업데이트(jsonb_set())도 행 전체 락을 발생시킵니다.

-- 이 쿼리도 해당 행 전체에 ExclusiveLock 발생
UPDATE profiles SET data = jsonb_set(data, '{notes}', '"new note"')
WHERE id = 123;

pg_locks 모니터링 결과, 동시에 5개 이상의 JSONB 부분 업데이트가 들어오면 락 대기가 누적되었습니다. 읽기 쿼리들까지 blocked state에 빠졌습니다.

해결 방식:

정규화 필드는 별도 UPDATE (독립적 락)
JSONB는 자주 변하지 않는 필드에만 사용
필요하면 분리된 테이블(1:N)로 구조화

핵심 정리

인덱스 선택은 쿼리 패턴 분석이 먼저: 고정 키 조회는 Expression B-tree(GIN 대비 인덱스 20분의 1, 쓰기 7배 빠름), 동적/포함 검색만 GIN 사용
정규화 기준: 2~3주 프로덕션 데이터 분석 → WHERE/ORDER BY 히트율 높은 필드 추출 → hot fields는 정규화, long-tail은 JSONB 유지
JSONB 부분 업데이트는 행 전체 락: 부분 수정 빈도가 높다면 독립적 테이블 분리 검토
모니터링 필수: pg_stat_user_indexes, EXPLAIN ANALYZE BUFFERS, pg_locks로 인덱스 히트율과 락 경합 주기적 검증
트레이드오프 문서화: 정규화 vs JSONB 선택의 근거(읽기/쓰기 비율, 스키마 변동성)를 팀과 공유하고, 분기마다 재평가