高性能な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'];

-- 包含（内包）しているか: @>
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';

インデックスサイズ 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';  -- インデックスは使用されません

利点:
- インデックスサイズが50〜70%縮小
- 包含クエリがより高速
- 書き込み負荷の高いワークロードに適している

使用すべき場面:
- 主に @> 演算子を使用する場合
- インデックスサイズを小さく抑えたい場合
- 挿入・更新頻度が高い場合

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. 部分インデックス (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;

-- 特定ブランドのスペックのみをインデックス化
CREATE INDEX idx_apple_specs ON products USING GIN (metadata->'specs')
    WHERE metadata->>'brand' = 'Apple';

利点:
- インデックスが非常に小さくなる
- インデックス対象外の行の更新が高速
- ストレージ要件の削減

5. 複合インデックス (Composite Indexes)

JSONBを通常のカラムと組み合わせます。

-- カテゴリ + JSONBメタデータのインデックス
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'];

-- 配列要素の展開
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への制約（Constraint）の使用

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;

インデックスの肥大化（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構造は適度にフラットに保ちます。
5. 制約（Constraints）を使用する: JSONBの構造と値を検証します。
6. クエリパターンを監視する: 実際の使用状況に基づいてインデックスを作成します。
7. 定期的なメンテナンス: 最適なパフォーマンスを維持するために VACUUM と REINDEX を行います。
8. 本番導入前のベンチマーク: 現実的なデータ量でテストします。
9. パーティショニングを検討する: 非常に大規模なJSONBテーブルの場合に有効です。
10. スキーマを文書化する: 期待されるJSONB構造のドキュメントを維持します。

結論

PostgreSQLのJSONBは、NoSQLの柔軟性とSQLの強力な機能を同時に提供します。

• 柔軟なスキーマ: 半構造化データを保存可能
• 高速なクエリ: 複数のインデックス戦略が利用可能
• ACID保証: 完全なトランザクションサポート
• 豊富な演算子: 強力なクエリ作成能力
• ハイブリッドアプローチ: リレーショナル設計との融合

一般的な用途にはGINインデックスから始め、トラフィックの多い特定のクエリには式インデックスで最適化を行い、最高のパフォーマンスを得るためにハイブリッドなスキーマ設計を維持しましょう。