Posted inデータベース

PostgreSQLデータベース設計パターン：柔軟なスキーマのためのポリモーフィック関連 vs EAV vs JSONB

Database tutorial - IT technology blog
Database tutorial - IT technology blog

深夜2時に発覚したスキーマの問題

アラートが鳴ったのは午前2時14分だった。プロダクトチームが、ユーザーが注文にカスタム属性（サイズ・カラー・素材・保証期間など）を付与できる機能をリリースしたばかりで、それを処理するクエリが30秒でタイムアウトしていた。テーブルは4000万行にまで膨れ上がっており、EAVを使っていたのが原因だった。典型的なミスだ。

MySQL、PostgreSQL、MongoDBを使った多くのプロジェクトを経験してきた私は、この壁に何度もぶつかってきた。PostgreSQLにおいて、ポリモーフィック関連・EAV・JSONBのどれを選ぶかという判断は、何年にもわたって影響を及ぼす。最初に誤ったパターンを選べば、本番環境のプレッシャーの中でクエリを書き直す羽目になる。正しく選べば、その選択は意識することなく機能し続ける。

ここに書くことはすべて、あの深夜2時のインシデントとその後のリファクタリングから得た知見だ。

3つのアプローチの概要

3つのパターンがある。まず概要から：

  • ポリモーフィック関連：単一のテーブルがtypeカラムと外部キーを使って複数の親テーブルを参照する。RailsスタイルのORMでよく見られる。
  • EAV（エンティティ・属性・値）：動的な属性を行として保存し、各属性は名前/値のペアとして1行ずつ格納される。非常に柔軟だが、クエリが書きにくいことで有名。
  • JSONB：動的な属性をJSONブロブとして単一カラムに保存する。PostgreSQLネイティブのアプローチで、インデックスサポートあり。

各アプローチの比較

ポリモーフィック関連

このパターンは、単一のテーブルが複数の親テーブルを参照する必要があるときに登場する。典型的な例は、postにもvideoにも属せるcommentsテーブルだ。commentable_typeとcommentable_idカラムを追加するだけで一見解決する。

-- ポリモーフィック関連のセットアップ
CREATE TABLE comments (
  id          SERIAL PRIMARY KEY,
  body        TEXT NOT NULL,
  commentable_type VARCHAR(50) NOT NULL,  -- 'post' または 'video'
  commentable_id   INTEGER NOT NULL
);

-- 実際の外部キー制約はここにない — これが問題だ
CREATE INDEX idx_comments_poly ON comments(commentable_type, commentable_id);

実際の外部キー制約がない — これが最初の警告サインだ。PostgreSQLは単一のカラムペアで複数テーブルにまたがる参照整合性を強制できない。データベースの保証を柔軟性と引き換えにしているのだ。

EAV（エンティティ・属性・値）

EAVは、レコードごとに属性セットが大きく異なる場合に正解のように思えてしまう。物理商品にはcolor、デジタル商品にはdownload_limit、サブスクリプションにはbilling_cycleがある。そこですべてをキー・バリューテーブルに突っ込んで先に進もうとする。

-- EAVのセットアップ
CREATE TABLE product_attributes (
  id         SERIAL PRIMARY KEY,
  product_id INTEGER NOT NULL REFERENCES products(id),
  attr_name  VARCHAR(100) NOT NULL,
  attr_value TEXT
);

CREATE INDEX idx_product_attr ON product_attributes(product_id, attr_name);

-- 複数の属性を持つ商品のクエリは非常に辛い
SELECT
  p.name,
  MAX(CASE WHEN pa.attr_name = 'color' THEN pa.attr_value END) AS color,
  MAX(CASE WHEN pa.attr_name = 'size'  THEN pa.attr_value END) AS size
FROM products p
LEFT JOIN product_attributes pa ON pa.product_id = p.id
GROUP BY p.id, p.name;

あのピボットクエリが深夜2時のインシデントを引き起こした。4000万行の属性データでは、GROUP BY + MAX(CASE WHEN)の組み合わせがパフォーマンスを完全に破壊していた — しかもこれはシンプルなクエリだ。2つの属性を同時にフィルタリングするとさらにひどいことになった。

JSONB

PostgreSQLのJSONB型はJSONをバイナリ形式で保存するため、読み取りが高速で、キーと値に対するGINインデックスが使える。柔軟なスキーマに対するデータベース自身の解答だ。

-- JSONBのセットアップ
CREATE TABLE products (
  id         SERIAL PRIMARY KEY,
  name       VARCHAR(255) NOT NULL,
  attributes JSONB DEFAULT '{}'
);

-- GINインデックスでキー/値の高速検索が可能
CREATE INDEX idx_products_attrs ON products USING GIN(attributes);

-- 動的属性を持つ商品を挿入
INSERT INTO products (name, attributes)
VALUES (
  'メカニカルキーボード',
  '{"color": "ブラック", "switch_type": "Cherry MX Red", "backlit": true, "warranty_years": 2}'
);

-- 特定の属性値でクエリ
SELECT name, attributes->>'color' AS color
FROM products
WHERE attributes @> '{"switch_type": "Cherry MX Red"}';

最後のクエリはGINインデックスを使う。200万行でも、普通のハードウェアで5ms以内で実行できる。

メリットとデメリット

ポリモーフィック関連

  • メリット：セットアップが簡単で、ORMのリレーションシップ（ActiveRecord、Django GenericForeignKey）に自然にマッピングできる
  • メリット：親タイプのセットが少なく安定している場合は有効
  • デメリット：データベースレベルの参照整合性がない — 孤立した行が実際に発生するリスクがある
  • デメリット：親テーブルのデータが必要なときJOINが複雑になる
  • デメリット：PostgreSQLの制約システムとの相性が悪い — データベースではなくORMの仕事をさせている

EAV

  • メリット：最大限の柔軟性 — どんな属性・どんなエンティティにも対応でき、スキーママイグレーション不要
  • メリット：MagentoやレガシーなWordPressのメタテーブルなど本番システムで使われている
  • デメリット：複数属性を同時にクエリするのが本当に辛い — SQLがすぐ複雑になる
  • デメリット：型の強制がない — すべてTEXTなので、数値比較がサイレントに失敗する
  • デメリット：テーブルが肥大化しやすい — 10属性の商品が1行ではなく10行になる
  • デメリット：インデックスは助けになるが、大規模なピボットクエリの問題は解決できない

JSONB

  • メリット：PostgreSQLネイティブサポートで、キー/値ペアに対するGINインデックスが使える
  • メリット：EAVのような行の爆発なしに柔軟性を確保できる
  • メリット：ネストされた構造・配列・ブール値をネイティブに扱える
  • メリット：jsonb_set()、jsonb_strip_nulls()などの組み込み関数がほとんどの更新シナリオをカバー
  • デメリット：JSON内部にカラムレベルの制約は適用できない — キーごとのNOT NULL・CHECK・外部キーは使えない
  • デメリット：深くネストされたデータへの複雑なクエリはすぐに冗長になる
  • デメリット：すべてのクエリがJSONBキーでフィルタリングするなら、そのキーは実際のカラムに昇格すべき

推奨アプローチ

あのインシデント後に私が立てたルール：まずJSONBで始め、フィールドが主要なフィルタになったときに実際のカラムに昇格させる。

より具体的には：

  • JSONBを使う：属性が本当に動的でスパースな場合 — レコードごとにキーのセットが全く異なる場合。商品カタログ・ユーザー設定・フィーチャーフラグはすべて良い適用例だ。
  • ポリモーフィック関連はリレーションシップ中心の機能（コメント・添付ファイル・アクティビティフィード）にのみ使う — 欠落しているFK制約を明示的にドキュメント化し、次のエンジニアが驚かないようにする。
  • EAVはPostgreSQLでは、レガシーマイグレーションで強制されない限り避けること。ほぼ必ずより良い選択肢がある。

JSONBキーがWHERE句の80%以上に登場するようになったら、実際のカラムに切り出す：

-- 頻繁に使われるJSONBキーを実際のカラムに昇格
ALTER TABLE products ADD COLUMN color VARCHAR(50)
  GENERATED ALWAYS AS (attributes->>'color') STORED;

CREATE INDEX idx_products_color ON products(color);

これにより、JSONBの柔軟性を保ちながら、クエリプランナーが使える実際のインデックスを提供できる。

実装ガイド

ハイブリッドJSONBスキーマのセットアップ

すべてのWHERE句に登場する安定したフィールドは実際のカラムにする。それ以外はすべてJSONBに入れる。これがパターンの全体像だ。

CREATE TABLE products (
  id           SERIAL PRIMARY KEY,
  name         VARCHAR(255) NOT NULL,
  category_id  INTEGER NOT NULL REFERENCES categories(id),
  price        NUMERIC(10, 2) NOT NULL,
  status       VARCHAR(20) NOT NULL DEFAULT 'active',
  attributes   JSONB NOT NULL DEFAULT '{}',
  created_at   TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- 任意のキー検索用GINインデックス
CREATE INDEX idx_products_attrs_gin ON products USING GIN(attributes);

-- よく使われるキー用の通常インデックス
CREATE INDEX idx_products_attr_brand
  ON products ((attributes->>'brand'));

PythonでのJSONBの安全な更新

よく見かけるミス：1つのキーだけ変更が必要なのにJSONBカラム全体を上書きしてしまうこと。代わりにjsonb_set()を使おう：

import psycopg2
import json

conn = psycopg2.connect("dbname=mydb user=postgres")
cur = conn.cursor()

product_id = 42
new_color = "ミッドナイトブルー"

# 安全: attributes内の'color'キーのみを更新する
cur.execute("""
    UPDATE products
    SET attributes = jsonb_set(attributes, '{color}', %s::jsonb)
    WHERE id = %s
""", (json.dumps(new_color), product_id))

conn.commit()
cur.close()
conn.close()

CHECK制約によるJSONBスキーマの検証

JSONBの内部ではカラムレベルの型強制は機能しないが、CHECK制約で必須キーを強制できる：

ALTER TABLE products
  ADD CONSTRAINT chk_attributes_required_keys
  CHECK (
    (attributes ? 'brand') AND
    (jsonb_typeof(attributes->'price_override') IN ('number', 'null'))
  );

型付きカラムほど厳密ではないが、まったく制約のないブロブよりはましだ。フルスキーマをロックダウンせずにチーム間で契約が必要なときに便利だ。

EAVからJSONBへの移行

EAVテーブルを引き継いだ場合、あの深夜2時のインシデント後に私たちが実行した正確な移行手順を紹介する：

-- ステップ1: EAVの行をエンティティごとにJSONに集約
CREATE TABLE product_attributes_jsonb AS
SELECT
  product_id,
  jsonb_object_agg(attr_name, attr_value) AS attributes
FROM product_attributes
GROUP BY product_id;

-- ステップ2: productsにJSONBカラムを追加
ALTER TABLE products ADD COLUMN attributes JSONB DEFAULT '{}';

-- ステップ3: バックフィル
UPDATE products p
SET attributes = paj.attributes
FROM product_attributes_jsonb paj
WHERE paj.product_id = p.id;

-- ステップ4: GINインデックスを追加
CREATE INDEX idx_products_attrs ON products USING GIN(attributes);

-- ステップ5: 検証後、古いEAVテーブルを削除
-- DROP TABLE product_attributes;

移行後、最も遅かったクエリは30秒から180msに短縮された。あのテーブルから深夜2時のアラートが来ることはもうない。

最終的な判断フレームワーク

新しい機能を設計する際、私は次のチェックリストを確認する：

  1. 属性が事前にわかっており、頻繁にクエリされる？ → 実際のカラムを使う。
  2. 属性が動的だが、常に1つのエンティティタイプに紐付く？ → JSONBを使う。
  3. 単一のエンティティと複数の親テーブルの間のリレーションシップが必要？ → ポリモーフィック関連を使う。ただし、欠落しているFK制約を明示的にドキュメント化すること。
  4. EAVを使いたくなっている？ → 代わりにJSONBを使おう。本当に。

PostgreSQLのJSONBサポートは成熟している。EAVは新しいシステムにとってほぼ正解になることはない。インデックスは機能する。クエリ構文は合理的だ。組み込み関数は一般的なケースを網羅している。JSONBは、そもそもPostgreSQLを選ぶ理由となるリレーショナルの保証を捨てることなく、必要な柔軟性を提供してくれる。

Share:
Tags: