PostgreSQL의 jsonb와 인덱스 — 문서 DB처럼 쓰는 법 (MySQL과 비교)

반정형 데이터를 많이 다루는 요즘, PostgreSQL의 [jsonb]는 강력한 무기입니다.

단순히 JSON을 저장하는 수준을 넘어, 인덱싱과 연산자를 활용하면 “문서 DB처럼” 빠른 조회가 가능합니다.

이 글에서는 jsonb의 핵심 개념, 쿼리/연산자, 인덱싱 전략(GIN/GiST/표현식/부분 인덱스), 운영 주의점, 그리고 MySQL과의 접근 차이를 실무 관점에서 정리합니다.

---

1. jsonb 기본 개념

• 저장 포맷: json은 텍스트, jsonb는 바이너리(정규화된) 포맷. jsonb가 일반적으로 검색/인덱싱에 유리.
• 연산자:
  • ->(JSON), ->>(텍스트), #>(경로 JSON), #>>(경로 텍스트)
  • 존재/포함: ?(키/요소 존재), ?|/?&(여러 키/요소 중 하나/모두), @>(우변 JSON이 좌변에 포함되는가)
  • JSONPath: @?(경로 매칭), @@(경로 필터)

팁: 성능 관점에서 @>(containment)와 키 존재 연산자들은 GIN 인덱스를 가장 잘 활용합니다.

 

---

2. 기본 테이블 & 샘플 데이터

CREATE TABLE events (

id bigserial PRIMARY KEY,

ts timestamptz NOT NULL DEFAULT now(),

data jsonb NOT NULL

);

 

-- 샘플: 주문 이벤트

INSERT INTO events(data) VALUES

('{"type":"order","user_id":"u123","amount": 9900, "tags":["vip","mobile"]}'),

('{"type":"order","user_id":"u124","amount":12000, "tags":["web"]}'),

('{"type":"refund","user_id":"u123","reason":"duplicate"}');

 

 

---

3. 쿼리 패턴 — 무엇을 어떻게 찾는가

-- 포함: 특정 구조를 포함하는 문서

SELECT * FROM events

WHERE data @> '{"type":"order"}';

 

-- 키 존재: 특정 키가 있는 문서

SELECT * FROM events

WHERE data ? 'user_id';

 

-- 배열 포함(문자열 배열인 경우)

SELECT * FROM events

WHERE data->'tags' ? 'vip';

 

-- 값 비교: 텍스트로 꺼내서 비교

SELECT * FROM events

WHERE data->>'user_id' = 'u123';

 

-- 숫자 비교: 캐스팅 필요

SELECT * FROM events

WHERE (data->>'amount')::numeric >= 10000;

 

-- JSONPath (복잡한 조건)

SELECT * FROM events

WHERE data @? '$.tags ? (@ == "mobile")';

 

---

4. 인덱싱 전략 — 업무별로 고르는 법

4-1. GIN 인덱스 (기본)

@>(containment), 키/요소 존재 ?, ?|, ?&를 빠르게 만듭니다.

-- 기본 GIN (다목적)

CREATE INDEX idx_events_data_gin

ON events USING gin (data);

 

-- 경량화(작은 인덱스가 필요): containment 중심이면 path_ops

CREATE INDEX idx_events_data_path_ops

ON events USING gin (data jsonb_path_ops);

 

• 기본 GIN: 다양한 연산자를 지원(범용)
• jsonb_path_ops: 인덱스 크기가 작고 빠를 수 있으나, 주 사용처는 @> 중심. 키 존재/JSONPath 등은 제한될 수 있습니다.

4-2. 표현식(Functional) + B-tree 인덱스

값 비교(=, <, >), 정렬/그룹핑에 유리합니다.

-- 텍스트로 꺼낸 user_id에 B-tree 인덱스

CREATE INDEX idx_events_user_id

ON events ((data->>'user_id'));

 

-- 숫자 캐스팅 인덱스: 금액 비교 최적화

CREATE INDEX idx_events_amount_num

ON events (((data->>'amount')::numeric));

 

4-3. 생성(Stored Generated) 컬럼 + 전통 인덱스

자주 필터링하는 키를 정규 컬럼으로 끌어내 저장하고 B-tree로 인덱싱.

ALTER TABLE events

ADD COLUMN user_id text GENERATED ALWAYS AS (data->>'user_id') STORED,

ADD COLUMN amount numeric GENERATED ALWAYS AS ((data->>'amount')::numeric) STORED;

 

CREATE INDEX idx_events_user_id_btree ON events (user_id);

CREATE INDEX idx_events_amount_btree ON events (amount);

 

• 장점: 통계 수집/카디널리티 추정이 좋아져 플래너의 선택이 안정적.
• 단점: 스키마가 늘어나고 업데이트 비용이 증가.

4-4. 부분(Partial) 인덱스

쿼리의 전형적 조건을 인덱스에 내장해 크기↓, 효율↑.

-- 주문만 자주 조회한다면

CREATE INDEX idx_events_order_only

ON events USING gin (data)

WHERE data @> '{"type":"order"}';

 

4-5. GiST 인덱스 (특수 케이스)

GiST는 범위/공간/유사도 등 특화 인덱스. JSONB에도 적용할 수 있으나, 일반적인 @>/존재 쿼리는 GIN이 보통 더 적합합니다.

---

5. 운영/성능 팁

1. 쿼리/인덱스 정합: 사용 연산자에 맞는 인덱스를 선택합니다. @>/? → GIN, 값 비교/정렬 → B-tree(표현식/생성 컬럼).
2. -> vs ->>:
   • ->는 JSON, ->>는 텍스트.
   • 숫자 비교는 캐스팅을 명시하세요: ((data->>'amount')::numeric).
3. GIN 튜닝: fastupdate(기본 ON) 사용, 대량 쓰기 후 gin_pending_list_limit 모니터링. 필요 시 VACUUM/REINDEX CONCURRENTLY.
4. 대형 문서 주의: 매우 큰 jsonb는 TOAST로 분리 저장. 업데이트 빈도가 높으면 페이지 분할/쓰기 증폭 발생 → 핫 키는 정규 컬럼화 검토.
5. 통계/플래너: 표현식/생성 컬럼에 대해 ANALYZE가 통계를 수집하도록 하고, 빈번한 키는 default_statistics_target 상향을 고려.
6. 설계 패턴:
   • 하이브리드 스키마: 자주 필터링하는 키는 정규 컬럼 + 나머지는 jsonb.
   • 검증: CHECK 제약으로 최소한의 스키마 유효성(타입/필수 키) 검증.

---

6. MySQL과의 비교 — 왜 접근이 다른가

• MySQL: JSON 자체에 대한 범용 인덱스(예: GIN)는 없고, 보통 생성 컬럼(Generated Column) + 표현식 인덱스로 특정 경로를 인덱싱합니다.
  • 예) user_id 인덱싱: ALTER TABLE ... ADD COLUMN user_id GENERATED ALWAYS AS (JSON_UNQUOTE(JSON_EXTRACT(doc,'$.user_id'))) STORED, ADD INDEX (user_id);
  • 배열 포함/중첩 구조는 인덱싱 경로를 명확히 지정해야 하며, 범용성은 PostgreSQL의 GIN보다 좁습니다.
• PostgreSQL: jsonb 전체에 대한 GIN 인덱스 1개로 다양한 연산자(@>, ?, ?|, ?&)를 가속할 수 있어, 변화가 많은 스키마나 다양한 검색 패턴에서 유연성이 높습니다.

---

7. 실무 레시피 — 자주 쓰는 조합

• 로그/이벤트 스트림: jsonb + 기본 GIN + 주문 전용 Partial GIN + user_id(생성 컬럼) B-tree.
• 카탈로그/메타데이터: 빈번한 속성은 정규 컬럼, 나머지는 jsonb. 다국어/옵션 필드는 jsonb.
• 검색형 서비스: 태그/속성 필터는 @>/?로, 정렬/페이지네이션은 생성 컬럼 B-tree.

---

8. 체크리스트 (실무 적용 전 확인할 것)

• 쿼리에서 자주 쓰는 연산자(@>, ?, ->>, JSONPath 등)를 파악했는가?
• 해당 연산자에 최적화된 인덱스를 설계했는가? (GIN/표현식/B-tree/Partial)
• 자주 필터링하는 키는 생성 컬럼으로 승격했는가?
• GIN 인덱스의 pending list와 Autovacuum 상태를 주기적으로 점검하고 있는가?
• EXPLAIN (ANALYZE, BUFFERS)로 인덱스 효과를 실제로 검증했는가?

 

---

결론

• jsonb는 PostgreSQL을 문서 지향 저장소처럼 활용하게 해주는 핵심 기능입니다.
• GIN(또는 path_ops) + 표현식/B-tree + 부분 인덱스를 상황에 맞게 조합하면, 정형/반정형 데이터를 한 DB에서 안정적으로 처리할 수 있습니다.
• MySQL도 JSON은 잘 다루지만, 인덱싱 전략은 경로 지정(생성 컬럼/표현식) 중심으로 설계해야 하며, 범용성/유연성 측면에선 PostgreSQL의 jsonb + GIN 조합이 유리한 경우가 많습니다.

---