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

PostgreSQL JSONB 인덱싱은 테이블에 유연한 속성이 있지만 쿼리 속도가 여전히 빨라야 할 때 도움이 됩니다. JSONB는 제품 메타데이터, 이벤트 페이로드, 프로필 설정 및 기타 반정형 데이터에 잘 작동하며, 실제로 사용하는 연산자와 일치하는 인덱스를 선택하기만 하면 됩니다.

JSONB vs JSON

JSON (텍스트 저장)

정확한 텍스트 표현을 저장합니다.
더 빠른 삽입 (처리 없음)
더 느린 쿼리
공백과 키 순서를 유지합니다.
네이티브 JSONB 스타일 GIN 연산자 클래스가 없습니다. 추출된 값에 대한 표현식 인덱스는 여전히 가능합니다.

JSONB (이진 저장)

분해된 이진 형식으로 저장됩니다.
약간의 삽입 오버헤드
일반적으로 json보다 쿼리 속도가 빠르며, 특히 인덱싱된 경우 더욱 그렇습니다.
공백을 유지하지 않습니다.
여러 유용한 인덱스 전략을 지원합니다.
대부분의 쿼리 가능한 JSON 데이터에 권장됩니다.

기본 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'];

-- 포함: @>
SELECT * FROM products WHERE metadata @> '{"brand": "Apple"}';

-- 포함됨: <@
SELECT * FROM products WHERE '{"brand": "Apple"}' <@ metadata;

JSONB 인덱싱 전략

1. GIN 인덱스 (일반화된 역 인덱스)

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

-- 전체 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 쿼리 성능:

더 큰 인덱스 크기
포함 쿼리에 탁월함
키 존재 확인에 적합
더 느린 업데이트 (전체 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';  -- 인덱스를 사용하지 않음

장점:

기본 JSONB GIN 인덱스보다 종종 더 작음
포함 쿼리에 대해 종종 더 빠름
쓰기 집약적 워크로드에 더 적합

사용 시기:

주로 @> 연산자 사용
더 작은 인덱스 필요
높은 삽입/업데이트 속도

3. 특정 키에 대한 표현식 인덱스

최대 성능을 위해 특정 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-트리 인덱스 이점 (범위 쿼리, 정렬)

사용 시기:

자주 쿼리되는 특정 필드
범위 쿼리 또는 정렬 필요
최소한의 인덱스 오버헤드 원함

4. 부분 인덱스

데이터의 관련 하위 집합만 인덱싱합니다.

-- '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;

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

장점:

훨씬 더 작은 인덱스
인덱싱되지 않은 행에 대한 더 빠른 업데이트
저장 공간 요구 사항 감소

5. 복합 인덱스

JSONB를 일반 열과 결합합니다.

-- 카테고리 + JSONB 메타데이터 인덱싱
CREATE TABLE products_v2 (
    id SERIAL PRIMARY KEY,
    category VARCHAR(50),
    metadata JSONB
);

CREATE INDEX idx_products_v2_category ON products_v2 (category);
CREATE INDEX idx_products_v2_metadata_gin ON products_v2 USING GIN (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"}';
-- 대규모 테이블에서 순차 스캔이 예상됩니다.

-- GIN 인덱스
CREATE INDEX idx_gin ON test_jsonb USING GIN (data);
EXPLAIN ANALYZE
SELECT * FROM test_jsonb WHERE data @> '{"city": "NYC"}';
-- 플래너가 유용하다고 판단할 때 비트맵 인덱스 스캔이 예상됩니다.

-- 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"}';
-- 기본 JSONB GIN opclass보다 더 작은 인덱스를 자주 사용합니다.

-- 표현식 인덱스
DROP INDEX idx_gin_ops;
CREATE INDEX idx_city ON test_jsonb ((data->>'city'));
EXPLAIN ANALYZE
SELECT * FROM test_jsonb WHERE data->>'city' = 'NYC';
-- 자주 필터링되는 하나의 키에 대해 가장 효율적인 옵션인 경우가 많습니다.

정확한 타이밍은 하드웨어, PostgreSQL 설정, 행 너비, 캐시 상태 및 데이터 분포에 따라 다릅니다. 자신의 워크로드에서 EXPLAIN (ANALYZE, BUFFERS)를 진실의 원천으로 취급하십시오.

고급 쿼리 패턴

배열 연산

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'];

-- 배열 요소 확장
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의 전체 텍스트 검색

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 열에서 순차 스캔이 있는 테이블 찾기
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;

인덱스 크기 확인

SELECT 
    indexname,
    pg_size_pretty(pg_relation_size(indexrelid)) AS size,
    idx_scan,
    idx_tup_read
FROM pg_stat_user_indexes
WHERE schemaname = 'public'
  AND indexname LIKE '%jsonb%';

GIN 인덱스의 경우 재구축 여부를 결정하기 전에 인덱스 크기, 스캔 수, 쓰기 비용 및 쿼리 계획부터 시작하십시오.

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()

모범 사례 요약

JSON이 아닌 JSONB 사용: 이진 형식으로 인덱싱 가능
올바른 인덱스 유형 선택: 유연성을 위한 GIN, 특정 필드를 위한 표현식 인덱스
하이브리드 스키마 설계: 자주 쿼리되는 필드를 열로 추출
깊은 중첩 피하기: JSONB 구조를 합리적으로 평평하게 유지
제약 조건 사용: JSONB 구조 및 값 검증
쿼리 패턴 모니터링: 실제 사용량을 기반으로 인덱스 생성
정기적인 유지 관리: 최적의 성능을 위한 VACUUM 및 REINDEX
프로덕션 전 벤치마킹: 실제 데이터 볼륨으로 테스트
파티셔닝 고려: 매우 큰 JSONB 테이블의 경우
스키마 문서화: 예상 JSONB 구조 문서 유지

핵심 내용

유연한 포함 및 키 존재 검색이 필요할 때 기본 GIN 인덱스로 시작하십시오. 포함 중심 워크로드에는 jsonb_path_ops를 사용하고, 하나의 JSONB 키가 핫 필터 또는 정렬 필드가 될 때 표현식 인덱스를 사용하십시오. 필드가 애플리케이션의 핵심인 경우 실제 열로 승격시키십시오.