高性能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インデックス（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';

インデックスサイズとクエリパフォーマンス：

• インデックスサイズが大きい
• 包含クエリに優れている
• キー存在確認に適している
• 更新が遅い（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-treeインデックスの利点（範囲クエリ、ソート）

使用するタイミング：

• 頻繁にクエリされる特定のフィールドがある場合
• 範囲クエリやソートが必要な場合
• インデックスのオーバーヘッドを最小限に抑えたい場合

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';
-- 頻繁にフィルタリングされる1つのキーに対して、最も効率的なオプションであることが多い。

正確なタイミングは、ハードウェア、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()

ベストプラクティスのまとめ

1. JSONではなくJSONBを使用する: バイナリ形式によりインデックスが可能
2. 適切なインデックスタイプを選択する: 柔軟性にはGIN、特定のフィールドには式インデックス
3. ハイブリッドスキーマ設計: 頻繁にクエリされるフィールドは列に抽出する
4. 深いネストを避ける: JSONB構造は適度にフラットに保つ
5. 制約を使用する: JSONBの構造と値を検証する
6. クエリパターンを監視する: 実際の使用状況に基づいてインデックスを作成する
7. 定期的なメンテナンス: 最適なパフォーマンスのためにVACUUMとREINDEXを実行する
8. 本番環境の前にベンチマークを取る: 現実的なデータ量でテストする
9. パーティショニングを検討する: 非常に大きなJSONBテーブルの場合
10. スキーマを文書化する: 期待されるJSONB構造のドキュメントを維持する

まとめ

柔軟な包含検索やキー存在確認が必要な場合は、デフォルトのGINインデックスから始めてください。包含クエリが中心のワークロードにはjsonb_path_opsを使用し、特定のJSONBキーが頻繁にフィルタリングやソートに使用される場合は式インデックスを使用してください。フィールドがアプリケーションの中心的な要素である場合は、実際の列に昇格させることを検討してください。