고성능 NoSQL 스타일 쿼리를 위한 PostgreSQL JSONB 인덱싱 전략 구현

서론

PostgreSQL JSONB(JSON Binary)는 문서 데이터베이스의 유연성과 관계형 SQL의 강력함을 결합합니다. 기존의 JSON 저장 방식과 달리, JSONB는 효율적인 인덱싱과 쿼리를 가능하게 하는 분해된 이진 포맷으로 저장됩니다. 이 가이드에서는 ACID 보장을 유지하면서 NoSQL 수준의 성능을 달성하기 위한 고급 JSONB 인덱싱 전략을 다룹니다.

JSONB vs JSON

JSON (텍스트 저장)

• 정확한 텍스트 표현을 저장
• 삽입 속도가 빠름 (처리 과정 없음)
• 쿼리 속도가 느림
• 공백 및 키 순서 보존
• 인덱싱 지원 안 함

JSONB (이진 저장)

• 분해된 이진 형태로 저장
• 약간의 삽입 오버헤드 발생
• 쿼리 속도가 훨씬 빠름
• 공백 보존 안 함
• 전체 인덱싱 지원
• 대부분의 사용 사례에서 권장됨

기본 JSONB 연산

테이블 설정

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(200),
    metadata JSONB,
    created_at TIMESTAMP DEFAULT NOW()
);

-- 샘플 데이터 삽입
INSERT INTO products (name, metadata) VALUES
    ('Laptop', '{"brand": "Dell", "price": 999, "specs": {"ram": 16, "cpu": "i7"}}'),
    ('Phone', '{"brand": "Apple", "price": 899, "specs": {"ram": 6, "storage": 128}}'),
    ('Tablet', '{"brand": "Samsung", "price": 599, "specs": {"ram": 8, "storage": 256}}');

쿼리 연산자

-- 키로 접근: ->
SELECT name, metadata->'brand' AS brand FROM products;

-- 텍스트로 접근: ->>
SELECT name, metadata->>'brand' AS brand_text FROM products;

-- 중첩 구조 접근
SELECT name, metadata->'specs'->>'ram' AS ram FROM products;

-- 키 존재 여부 확인: ?
SELECT * FROM products WHERE metadata ? 'brand';

-- 나열된 키 중 하나라도 존재하는지 확인: ?|
SELECT * FROM products WHERE metadata ?| ARRAY['brand', 'manufacturer'];

-- 모든 키가 존재하는지 확인: ?&
SELECT * FROM products WHERE metadata ?& ARRAY['brand', 'price'];

-- 포함 여부 확인 (Contains): @>
SELECT * FROM products WHERE metadata @> '{"brand": "Apple"}';

-- 포함됨 여부 확인 (Contained by): <@
SELECT * FROM products WHERE '{"brand": "Apple"}' <@ metadata;

JSONB 인덱싱 전략

1. GIN 인덱스 (Generalized Inverted Index)

기본적이며 가장 다재다능한 인덱싱 방법입니다.

-- 전체 JSONB 컬럼에 GIN 인덱스 생성
CREATE INDEX idx_products_metadata ON products USING GIN (metadata);

-- 지원하는 연산자: @>, ?, ?|, ?&
SELECT * FROM products WHERE metadata @> '{"brand": "Apple"}';
SELECT * FROM products WHERE metadata ? 'brand';

인덱스 크기 vs 쿼리 성능:
- 인덱스 크기가 큰 편임
- 포함(Containment) 쿼리에 매우 뛰어남
- 키 존재 여부 확인에 적합
- 업데이트가 느림 (전체 JSON이 재인덱싱됨)

2. jsonb_path_ops를 사용한 GIN 인덱스

포함 쿼리 전용으로 최적화된 방식입니다.

CREATE INDEX idx_products_metadata_ops ON products USING GIN (metadata jsonb_path_ops);

-- 지원하는 연산자: @> 만 지원
SELECT * FROM products WHERE metadata @> '{"brand": "Apple"}';
SELECT * FROM products WHERE metadata @> '{"specs": {"ram": 16}}';

-- 지원 안 함: ?, ?|, ?&
-- SELECT * FROM products WHERE metadata ? 'brand';  -- 인덱스를 사용하지 않음

장점:
- 인덱스 크기가 50-70% 더 작음
- 포함 쿼리 속도가 더 빠름
- 쓰기 작업이 많은 워크로드에 더 적합

사용 시점:
- 주로 @> 연산자를 사용하는 경우
- 더 작은 인덱스가 필요한 경우
- 높은 삽입/업데이트 빈도

3. 특정 키에 대한 표현식 인덱스 (Expression Indexes)

최고의 성능을 위해 특정 JSONB 경로를 인덱싱합니다.

-- 특정 텍스트 값 인덱싱
CREATE INDEX idx_products_brand ON products ((metadata->>'brand'));

-- 특정 숫자 값 인덱싱
CREATE INDEX idx_products_price ON products (((metadata->>'price')::NUMERIC));

-- 중첩된 값 인덱싱
CREATE INDEX idx_products_ram ON products (((metadata->'specs'->>'ram')::INTEGER));

-- 쿼리에서 이러한 인덱스를 효율적으로 사용
SELECT * FROM products WHERE metadata->>'brand' = 'Apple';
SELECT * FROM products WHERE (metadata->>'price')::NUMERIC > 500;

장점:
- 인덱스 크기가 가장 작음
- 특정 필드에 대해 가장 빠른 쿼리 성능
- 표준 B-tree 인덱스의 이점 활용 가능 (범위 쿼리, 정렬)

사용 시점:
- 특정 필드를 자주 쿼리하는 경우
- 범위 쿼리나 정렬이 필요한 경우
- 최소한의 인덱스 오버헤드를 원하는 경우

4. 부분 인덱스 (Partial Indexes)

데이터의 관련 서브셋만 인덱싱합니다.

-- 'brand' 키가 있는 제품만 인덱싱
CREATE INDEX idx_products_branded ON products USING GIN (metadata)
    WHERE metadata ? 'brand';

-- 고가 제품만 인덱싱
CREATE INDEX idx_expensive_products ON products USING GIN (metadata)
    WHERE (metadata->>'price')::NUMERIC > 500;

-- 특정 브랜드의 사양(specs)만 인덱싱
CREATE INDEX idx_apple_specs ON products USING GIN (metadata->'specs')
    WHERE metadata->>'brand' = 'Apple';

장점:
- 인덱스 크기가 훨씬 작음
- 인덱싱되지 않은 행의 업데이트 속도가 빠름
- 저장 공간 요구 사항 감소

5. 복합 인덱스 (Composite Indexes)

JSONB와 일반 컬럼을 결합합니다.

-- category + JSONB metadata 인덱싱
CREATE TABLE products_v2 (
    id SERIAL PRIMARY KEY,
    category VARCHAR(50),
    metadata JSONB
);

CREATE INDEX idx_category_metadata ON products_v2 (category, metadata jsonb_path_ops);

-- 두 조건을 모두 결합한 효율적인 쿼리
SELECT * FROM products_v2 
WHERE category = 'electronics' 
  AND metadata @> '{"brand": "Apple"}';

성능 비교

테스트 데이터셋 설정

CREATE TABLE test_jsonb (
    id SERIAL PRIMARY KEY,
    data JSONB
);

-- 100만 건의 레코드 삽입
INSERT INTO test_jsonb (data)
SELECT jsonb_build_object(
    'user_id', generate_series(1, 1000000),
    'name', 'User ' || generate_series(1, 1000000),
    'age', (random() * 60 + 18)::INTEGER,
    'city', (ARRAY['NYC', 'LA', 'Chicago', 'Houston', 'Phoenix'])[floor(random() * 5 + 1)],
    'active', random() > 0.5,
    'metadata', jsonb_build_object(
        'score', (random() * 1000)::INTEGER,
        'level', floor(random() * 10 + 1)
    )
);

인덱스 유형별 벤치마크

-- 인덱스 없음
EXPLAIN ANALYZE
SELECT * FROM test_jsonb WHERE data @> '{"city": "NYC"}';
-- 결과: ~200ms, Seq Scan

-- GIN 인덱스
CREATE INDEX idx_gin ON test_jsonb USING GIN (data);
EXPLAIN ANALYZE
SELECT * FROM test_jsonb WHERE data @> '{"city": "NYC"}';
-- 결과: ~5ms, Bitmap Index Scan

-- GIN jsonb_path_ops
DROP INDEX idx_gin;
CREATE INDEX idx_gin_ops ON test_jsonb USING GIN (data jsonb_path_ops);
EXPLAIN ANALYZE
SELECT * FROM test_jsonb WHERE data @> '{"city": "NYC"}';
-- 결과: ~3ms, Bitmap Index Scan (인덱스 크기 40% 감소)

-- 표현식 인덱스
DROP INDEX idx_gin_ops;
CREATE INDEX idx_city ON test_jsonb ((data->>'city'));
EXPLAIN ANALYZE
SELECT * FROM test_jsonb WHERE data->>'city' = 'NYC';
-- 결과: ~2ms, Bitmap Index Scan (가장 작은 인덱스 크기)

고급 쿼리 패턴

배열 연산

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    profile JSONB
);

INSERT INTO users (profile) VALUES
    ('{"name": "Alice", "tags": ["developer", "golang", "postgresql"]}'),
    ('{"name": "Bob", "tags": ["designer", "figma", "ui"]}');

-- 배열에 특정 요소가 있는지 확인
SELECT * FROM users WHERE profile->'tags' ? 'golang';

-- 배열에 나열된 요소 중 하나라도 있는지 확인
SELECT * FROM users WHERE profile->'tags' ?| ARRAY['golang', 'python'];

-- 배열에 모든 요소가 있는지 확인
SELECT * FROM users WHERE profile->'tags' ?& ARRAY['developer', 'golang'];

-- 배열 요소 확장 (Unnesting)
SELECT 
    id,
    profile->>'name' AS name,
    jsonb_array_elements_text(profile->'tags') AS tag
FROM users;

집계 및 그룹화

-- JSONB 필드별 카운트
SELECT 
    data->>'city' AS city,
    COUNT(*) AS user_count
FROM test_jsonb
GROUP BY data->>'city'
ORDER BY user_count DESC;

-- 중첩된 숫자 필드의 평균값
SELECT 
    data->>'city' AS city,
    AVG((data->'metadata'->>'score')::INTEGER) AS avg_score
FROM test_jsonb
GROUP BY data->>'city';

-- JSONB 객체 집계
SELECT 
    data->>'city' AS city,
    jsonb_agg(data->'name') AS user_names
FROM test_jsonb
WHERE (data->>'age')::INTEGER > 30
GROUP BY data->>'city';

JSONB에서의 전체 텍스트 검색 (Full-Text Search)

CREATE TABLE documents (
    id SERIAL PRIMARY KEY,
    content JSONB
);

INSERT INTO documents (content) VALUES
    ('{"title": "PostgreSQL Guide", "body": "Learn PostgreSQL JSONB indexing"}'),
    ('{"title": "NoSQL vs SQL", "body": "Comparison of database paradigms"}');

-- 텍스트 검색 인덱스 생성
CREATE INDEX idx_documents_fts ON documents 
    USING GIN (to_tsvector('english', content->>'body'));

-- 전체 텍스트 검색 실행
SELECT * FROM documents
WHERE to_tsvector('english', content->>'body') @@ to_tsquery('PostgreSQL & indexing');

JSONB 필드 업데이트

-- 전체 JSONB 업데이트
UPDATE products SET metadata = '{"brand": "HP", "price": 1099}'::JSONB
WHERE id = 1;

-- jsonb_set을 사용한 특정 키 업데이트
UPDATE products 
SET metadata = jsonb_set(metadata, '{price}', '1199')
WHERE id = 1;

-- 중첩된 값 업데이트
UPDATE products
SET metadata = jsonb_set(metadata, '{specs,ram}', '32')
WHERE id = 1;

-- 새로운 키 추가
UPDATE products
SET metadata = metadata || '{"warranty": "2 years"}'::JSONB
WHERE id = 1;

-- 키 삭제
UPDATE products
SET metadata = metadata - 'warranty'
WHERE id = 1;

-- 중첩된 키 삭제
UPDATE products
SET metadata = metadata #- '{specs,storage}'
WHERE id = 1;

스키마 설계 베스트 프랙티스

1. 자주 쿼리되는 필드 추출

나쁜 예: 모든 것을 JSONB에 저장

CREATE TABLE orders_bad (
    id SERIAL PRIMARY KEY,
    data JSONB  -- order_date, customer_id, status, total, items 등을 포함
);

-- 느린 쿼리
SELECT * FROM orders_bad 
WHERE data->>'status' = 'pending'
  AND (data->>'order_date')::DATE > '2024-01-01';

좋은 예: 하이브리드 접근 방식

CREATE TABLE orders_good (
    id SERIAL PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    status VARCHAR(20) NOT NULL,
    order_date DATE NOT NULL,
    total DECIMAL(10,2),
    metadata JSONB,  -- 자주 쿼리되지 않는 필드들
    CONSTRAINT fk_customer FOREIGN KEY (customer_id) REFERENCES customers(id)
);

CREATE INDEX idx_orders_status ON orders_good(status);
CREATE INDEX idx_orders_date ON orders_good(order_date);

-- 빠른 쿼리
SELECT * FROM orders_good
WHERE status = 'pending' AND order_date > '2024-01-01';

2. 반복되는 데이터 정규화

나쁜 예: JSONB 내 비정규화

CREATE TABLE logs_bad (
    id SERIAL PRIMARY KEY,
    log_data JSONB
);

-- 모든 로그에 반복되는 사용자 정보
INSERT INTO logs_bad (log_data) VALUES
    ('{"user": {"id": 1, "name": "Alice", "email": "[email protected]"}, "action": "login"}'),
    ('{"user": {"id": 1, "name": "Alice", "email": "[email protected]"}, "action": "view_page"}');

좋은 예: ID로 참조

CREATE TABLE logs_good (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL,
    action VARCHAR(50),
    metadata JSONB,  -- 액션 전용 데이터만 저장
    FOREIGN KEY (user_id) REFERENCES users(id)
);

3. JSONB에 제약 조건 사용

CREATE TABLE products_constrained (
    id SERIAL PRIMARY KEY,
    name VARCHAR(200),
    metadata JSONB,

    -- 특정 키 존재 보장
    CONSTRAINT metadata_has_brand CHECK (metadata ? 'brand'),

    -- 가격이 양수인지 확인
    CONSTRAINT positive_price CHECK ((metadata->>'price')::NUMERIC > 0),

    -- 중첩 구조 존재 확인
    CONSTRAINT has_specs CHECK (metadata ? 'specs')
);

모니터링 및 최적화

JSONB 쿼리 성능 분석

-- 타이밍 활성화
\timing on

-- 쿼리 분석
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT * FROM products WHERE metadata @> '{"brand": "Apple"}';

-- 인덱스 사용량 확인
SELECT 
    schemaname,
    tablename,
    indexname,
    idx_scan,
    idx_tup_read,
    idx_tup_fetch
FROM pg_stat_user_indexes
WHERE tablename = 'products';

누락된 인덱스 식별

-- JSONB 컬럼에 순차 스캔(Sequential Scan)이 발생하는 테이블 찾기
SELECT 
    schemaname,
    tablename,
    seq_scan,
    seq_tup_read,
    idx_scan,
    seq_tup_read / NULLIF(seq_scan, 0) AS avg_seq_read
FROM pg_stat_user_tables
WHERE seq_scan > 1000
  AND tablename IN (
      SELECT tablename 
      FROM information_schema.columns 
      WHERE data_type = 'jsonb'
  )
ORDER BY seq_scan DESC;

인덱스 팽창(Bloat) 확인

CREATE EXTENSION pgstattuple;

SELECT 
    indexname,
    pg_size_pretty(pg_relation_size(indexrelid)) AS size,
    100 - pgstatindex(indexrelid).avg_leaf_density AS bloat_pct
FROM pg_stat_user_indexes
WHERE schemaname = 'public'
  AND indexname LIKE '%jsonb%';

NoSQL에서 PostgreSQL JSONB로의 마이그레이션

MongoDB에서 PostgreSQL로

// MongoDB 문서 쿼리
db.products.find({
    "brand": "Apple",
    "specs.ram": { $gte: 8 }
})

-- PostgreSQL 동일 쿼리
SELECT * FROM products
WHERE metadata @> '{"brand": "Apple"}'
  AND (metadata->'specs'->>'ram')::INTEGER >= 8;

마이그레이션 스크립트 작성

import psycopg2
from pymongo import MongoClient
import json

# MongoDB 연결
mongo_client = MongoClient('mongodb://localhost:27017/')
mongo_db = mongo_client['mydb']
mongo_collection = mongo_db['products']

# PostgreSQL 연결
pg_conn = psycopg2.connect(
    host="localhost",
    database="mydb",
    user="postgres",
    password="password"
)
pg_cursor = pg_conn.cursor()

# 데이터 마이그레이션
for doc in mongo_collection.find():
    # MongoDB _id 제거
    doc.pop('_id', None)

    # PostgreSQL에 삽입
    pg_cursor.execute(
        "INSERT INTO products (name, metadata) VALUES (%s, %s)",
        (doc.get('name'), json.dumps(doc))
    )

pg_conn.commit()
pg_cursor.close()
pg_conn.close()

베스트 프랙티스 요약

1. JSON 대신 JSONB 사용: 이진 포맷만이 인덱싱을 지원함
2. 적절한 인덱스 유형 선택: 유연성을 위해서는 GIN을, 특정 필드에는 표현식 인덱스를 사용
3. 하이브리드 스키마 설계: 자주 쿼리되는 필드는 별도 컬럼으로 추출
4. 깊은 중첩 피하기: JSONB 구조를 가급적 평탄(flat)하게 유지
5. 제약 조건 사용: JSONB 구조와 값의 유효성을 검증
6. 쿼리 패턴 모니터링: 실제 사용량에 기반하여 인덱스 생성
7. 정기적인 유지보수: 최적의 성능을 위해 VACUUM 및 REINDEX 수행
8. 운영 전 벤치마크: 실제 데이터 규모로 테스트
9. 파티셔닝 고려: 매우 큰 JSONB 테이블의 경우
10. 스키마 문서화: 기대되는 JSONB 구조에 대한 문서 유지

결론

PostgreSQL JSONB는 SQL의 강력함과 NoSQL의 유연성을 동시에 제공합니다.

• 유연한 스키마: 반정형 데이터 저장 가능
• 빠른 쿼리: 다양한 인덱싱 전략 지원
• ACID 보장: 완전한 트랜잭션 지원
• 풍부한 연산자: 강력한 쿼리 기능
• 하이브리드 접근: 관계형 설계와 결합 가능

일반적인 용도에는 GIN 인덱스로 시작하고, 트래픽이 높은 특정 쿼리에는 표현식 인덱스로 최적화하며, 최상의 성능을 위해 하이브리드 스키마 설계를 유지하십시오.