📘

RDS for PostgreSQL の jsonb で柔軟なタグ機能を実装した話

に公開
PostgreSQL
Database
RDB
jsonb
tech

RDS for PostgreSQL の jsonb で柔軟なタグ機能を実装した話

WEBシステムの機能実現にあたり、はじめて RDS for PostgreSQL の jsonb 列を採用しました。
ここでは仮に パン屋システム を題材として、経験から得た学び（設計の考え方／実装の要点／ハマりどころ／見えてきた改善案）をまとめます。
この記事は学びを再構築したものであり、実際のシステムの内容とは異なります。

TL;DR

  • タグ（分類軸＝カテゴリ と 項目）を可変としたいといった要件に対し、完全正規化だけだと運用・変更コストが増える懸念があった。
  • パン（対象エンティティ）側に jsonb の タグマップ を持たせ、**検索は @> **で対応。
  • 更新は“上書き方式にして JSON の部分更新を避け、実装とテストの複雑さを低減。
  • breads.tags 側には外部キー制約をかけられないため、RDB 的な完全性（外部キー）は弱まる。今後の利用状況によって、トリガ／バッチなどでの整合性維持を検討。

前提と要件

  • 形態：複数のパン屋さんに導入するWebシステム
  • DB：AWS RDS for PostgreSQL

タグ要件（パン屋システムの例）：

  • カテゴリ例：パン種、キャンペーン
  • 項目例：
    • パン種 → 菓子パン／総菜パン
    • キャンペーン → 夏パン／秋パン
  • パンに対して、未設定も可、設定済みタグの削除も可
  • 制約（上限）：カテゴリ最大 10 件／各カテゴリの項目最大 10 件

なぜ jsonb にしたか（設計の意図）

  • 導入企業ごとにカテゴリ・項目が増減する＝スキーマ可変の性質。
  • 多対多の中間テーブルで厳密にモデリングすると、追加・削除が煩雑になりがち。
  • チームに json/jsonb 経験者がいたこと、学習投資の価値も高いことから採用。

データモデル

マスタ（正規化は維持）

-- タグカテゴリ
CREATE TABLE tag_categories (
  id   bigserial PRIMARY KEY,
  name text NOT NULL UNIQUE  -- 例: `パン種`, `キャンペーン`
);

-- タグ項目
CREATE TABLE tag_items (
  id           bigserial PRIMARY KEY,
  category_id  bigint NOT NULL REFERENCES tag_categories(id) ON DELETE CASCADE,
  name         text NOT NULL  -- 例: `菓子パン`, `総菜パン`
);

パン（トランザクション側）

tags は [{"Key": <カテゴリID>, "Value": <項目ID>}] 形式で統一

CREATE TABLE breads (
  id    bigserial PRIMARY KEY,
  name  text NOT NULL,
  -- タグ: [{"Key": <カテゴリID>, "Value": <項目ID>"}]
  tags  jsonb 
);

画面と API の仕様（ざっくり）

  • 表示 ：breads.tags を jsonb_array_elements で展開し、tag_categories ／ tag_items と JOIN して名称を解決。
  • 検索 ：UI でカテゴリごとにプルダウン → 選択結果を JSON で API に送信 → そのまま @> の右辺に使用。
  • 登録／更新 ：UI で選択 → JSON を丸ごと上書き保存 。部分編集は行わない。件数的に上書きのほうがシンプルで十分と判断。

主要クエリ例

  • 表示：タグ名称の解決
    パンに付与されたタグ（カテゴリID・項目ID）を展開し、カテゴリ名・項目名を解決する例です。
    jsonb_array_elements では直接 (cat_id, item_id) と展開できないため、jsonb_to_recordset を使って JSON の Key/Value をテーブルとして展開します。
SELECT
  b.id,
  b.name,
  c.name AS category_name,
  i.name AS item_name
FROM breads AS b
CROSS JOIN LATERAL jsonb_to_recordset(b.tags) AS kv("Key" int, "Value" int)
JOIN tag_categories AS c ON c.id = kv."Key"
JOIN tag_items      AS i ON i.id = kv."Value" AND i.category_id = c.id
WHERE b.id = $1;

jsonb_to_recordset によって [{"Key":1,"Value":2}, …] の配列を行に展開し、カテゴリID・項目IDを整数として扱えるようにしています。

  • 検索：特定のカテゴリ＝項目に一致
-- 例: カテゴリID 3 の「項目ID 12」が付いているパン
SELECT *
FROM breads
WHERE tags @> `[{"Key":3, "Value":12}]`::jsonb;
  • 複数条件の OR
--パン種=菓子パン(1→2) または キャンペーン=夏パン(3→8)
SELECT *
FROM breads
WHERE tags @> `[{"Key":1,"Value":2}]`::jsonb
   OR tags @> `[{"Key":3,"Value":8}]`::jsonb;

OR は SQL 側で明示する必要がある。複数要素を渡すと AND 条件になる。

  • 更新：上書き方式
-- $1: `[{"Key": 1, "Value": 2} ,{"Key": 3,"Value": 8}]` など（アプリ側でバリデート済み）
UPDATE breads
SET tags = $1::jsonb
WHERE id = $2;

上書きを選んだ理由：jsonb_set 等の局所更新はテスト分岐が増えやすく、「画面で選んだもの＝保存内容」を一致させる方が単純で安全。

運用と整合性（割り切りと対策）

  • タグ削除時の扱い ：当面は「パン側の tags を即時更新しない」方針で、一時的な不整合を許容。
    利用頻度がまだ見えない段階なので今後の運用状況に応じて対応と判断（YAGNI）。
    特にパンとは直接関係ないタグ情報だからこその判断。
    検討中の代替策
  • tag_items ／ tag_categories の 削除トリガでbreads.tags から該当キーを除去。
  • 夜間バッチでの一括クレンジング。
  • タグが任意で設定可能な項目であり、現時点でどのように使われるかの見通しが立てづらいことから、パフォーマンスに対する考慮は割愛。

難しかったところ

  • UI の可変レイアウト：カテゴリが 0〜10 件で増減するため、動的フォームの安定化に工夫。
  • 見た目は複数・実体は1つ：画面では複数カテゴリだが、DB は 1 つの JSON。変換責務（UI／API／DB）を明確化してから安定。
  • 連携仕様の曖昧さ：JSON 形や型（数値／文字列）を曖昧にしたまま進め、結合テストでバグ → API 契約（JSON スキーマ）を先に固定して解決。

工夫したところ

  • 上書き保存で JSON の部分編集を回避 → 実装とテストの複雑さを削減。
  • マスタは正規化を維持し、可変部分のみを jsonb に寄せて責務分離。

いま見えている改善案

  • 整合性の自動回復：削除トリガ、または夜間バッチでのクレンジング。
  • 段階的な正規化への移行パス：必要に応じ bread_tags(bread_id, category_id, item_id) に分離し、外部キーによる完全性と監査容易性を確保。
  • 契約テスト／可観測性：検索 JSON の形を 契約テストで固定し、不整合率やクリーン件数をメトリクス化してアラート。チーム開発の文脈上APIファーストを意識する必要あり。

よかったこと

  • プロダクト／チームとして 未経験技術を小さく試し、本番導入できた。
  • 実装・テストを複数メンバーで同期しながら進めることでチームとしての新しい技術に対する体験がつむことができた。
  • 正規化一辺倒では見落としがちな、「可変要件×現実の開発体験」 から多くの学びが得られた。

まとめ

  • ユーザー定義の可変な分類軸には jsonb が有効。
  • その一方で 外部キーの強制力は弱まるため、運用での整合性維持策が鍵。
  • 今回は 検索容易性（@>）× 実装単純さ（上書き） に重心を置いて安全に回し、将来の要件次第で 分離テーブルやトリガ／バッチを導入して堅牢化する。

Discussion