database-design-patterns

データベース設計パターン

リレーショナルデータベースのスキーマ設計に関する専門知識を提供します。正規化の判断、インデックスの選択、マイグレーションの安全性、コネクションプーリングを扱います。これらは、データベースが大規模環境でも高いパフォーマンスを発揮するか、保守の負担になるかを左右する構造上の基盤です。

使用するタイミング

以下の場合に使用します。

• 新しいスキーマを設計する、または既存のスキーマをリファクタリングする
• 特定のクエリパターンに対して、正規化するか非正規化するかを判断する
• インデックスの種類（B-tree、GIN、GiST、hash、partial、covering）を選ぶ
• 稼働中の本番システムを停止させずに実施するマイグレーションを計画する
• 論理削除、多態的な関連付け、複合キーを実装する
• PgBouncer または Prisma のコネクションプールを設定する

以下の場合には使用しません。

• EXPLAIN ANALYZE を実行する、またはクエリプランを読み解く → postgresql-optimization を使用してください
• ドキュメントモデルの設計（MongoDB、DynamoDB） → NoSQL のスキルを使用してください
• データベースのプロビジョニング、レプリカ、インフラストラクチャ → クラウド／インフラストラクチャのスキルを使用してください
• ORM 固有のコード生成 → 該当する ORM のスキルを使用してください

---

正規化と非正規化の判断ツリー

flowchart TD
    A[New data or query performance problem?] --> B{New data design?}
    B -->|Yes| C[Start normalized: 3NF]
    B -->|No — query too slow| D{Measured with EXPLAIN?}
    D -->|No| E[Measure first. Never guess.]
    D -->|Yes — proven join bottleneck| F{Read-heavy or write-heavy?}
    F -->|Read-heavy, joins are the bottleneck| G[Controlled denormalization:\nmaterialized view or cached column]
    F -->|Write-heavy or balanced| H[Keep normalized.\nOptimize query or add index first.]
    C --> I{Any repeated groups in a row?}
    I -->|Yes| J[1NF: Move to child table]
    I -->|No| K{Non-key cols depend on part of PK?}
    K -->|Yes| L[2NF: Extract to separate table]
    K -->|No| M{Transitive dependencies?}
    M -->|Yes| N[3NF: Extract lookup table]
    M -->|No| O[Schema is 3NF — ship it]

原則：まず 3NF から始めます。非正規化するのは、計測後に、遅すぎることが実証された特定の結合に限ります。推測で非正規化してはいけません。

---

インデックス選択の判断ツリー

flowchart TD
    A[Which index type?] --> B{Data type and query pattern}
    B -->|Equality or range on scalar| C[B-tree — default choice]
    B -->|Full-text search, arrays, JSONB| D[GIN — inverted index]
    B -->|Geometric / PostGIS types| E[GiST — generalized search]
    B -->|Exact equality only, very high cardinality| F[Hash — rare, limited utility]
    C --> G{Subset of rows frequently queried?}
    G -->|Yes, e.g. status = 'active'| H[Partial index:\nWHERE status = 'active']
    G -->|No| I{Query selects only indexed columns?}
    I -->|Yes| J[Covering index:\nINCLUDE additional columns]
    I -->|No| K[Standard B-tree index]

必ずインデックスを作成する対象：

• すべての外部キー列（結合時の全表スキャンを防ぎます）
• 頻繁に実行されるクエリの WHERE、ORDER BY、JOIN ON 句で使用される列
• 複合インデックス：最も選択性の高い列を先頭に配置します

部分インデックスとカバリングインデックスのどちらを選ぶか、または複数列インデックスの列順を調整する場合は、references/indexing-strategies.md を参照してください。

---

マイグレーションの安全性に関する判断ツリー

flowchart TD
    A[Schema change needed] --> B{Breaking change?}
    B -->|No: add nullable column, add index| C[Single migration, safe to run]
    B -->|Yes: rename column, change type, drop column| D[Expand-Contract pattern]
    D --> E[Phase 1 — Expand:\nAdd new column/table, keep old]
    E --> F[Deploy app: write to both old and new]
    F --> G[Backfill existing rows to new column]
    G --> H[Phase 2 — Contract:\nRemove old column once all reads use new]
    H --> I[Deploy app: read only from new]
    I --> J[Drop old column in final migration]
    C --> K{Large table?}
    K -->|Yes| L[CREATE INDEX CONCURRENTLY\nALTER TABLE with minimal lock]
    K -->|No| M[Standard migration]

拡張・縮小のテンプレート、ロックタイムアウトの設定、ロールバック戦略については、references/migration-patterns.md を参照してください。

---

正規化のリファレンス

1NF — 繰り返しグループなし

各列には値を 1 つだけ格納します。列内にカンマ区切りのリストを格納してはいけません。

-- Bad: tags stored as CSV
CREATE TABLE articles (
  id SERIAL PRIMARY KEY,
  tags TEXT  -- "sql,indexing,performance"
);

-- Good: normalized to child table
CREATE TABLE article_tags (
  article_id INT REFERENCES articles(id),
  tag TEXT NOT NULL,
  PRIMARY KEY (article_id, tag)
);

2NF — 部分関数従属なし（複合主キーのみ）

すべての非キー列は、主キーの一部だけでなく、主キー全体に依存します。

-- Bad: product_name depends only on product_id, not on (order_id, product_id)
CREATE TABLE order_items (
  order_id INT,
  product_id INT,
  product_name TEXT,  -- should be in products table
  quantity INT,
  PRIMARY KEY (order_id, product_id)
);

3NF — 推移的関数従属なし

非キー列は、互いではなく主キーのみに依存します。

-- Bad: zip_code determines city/state (transitive)
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  zip_code TEXT,
  city TEXT,    -- derivable from zip_code
  state TEXT    -- derivable from zip_code
);

-- Good: extract lookup table
CREATE TABLE zip_codes (
  zip TEXT PRIMARY KEY,
  city TEXT,
  state TEXT
);

---

主な設計上の判断

サロゲートキーと複合キー

以下の場合はサロゲートキー（serial／UUID）を使用します。

• ナチュラルキーが複数列で構成され、子テーブルで外部キーとして繰り返し使われる
• ナチュラルキーが変更される可能性がある（メールアドレス、ユーザー名など）
• 多数の他のテーブルから参照されるテーブルである

以下の場合は複合主キーを使用します。

• 追加属性を持たない、純粋な結合／関連付けテーブルである
• キーの組み合わせが真に安定しており、グローバルに一意である

-- Pure join table: composite PK is correct
CREATE TABLE user_roles (
  user_id INT REFERENCES users(id),
  role_id INT REFERENCES roles(id),
  PRIMARY KEY (user_id, role_id)
);

-- Association with attributes: add surrogate key
CREATE TABLE user_project_memberships (
  id SERIAL PRIMARY KEY,
  user_id INT REFERENCES users(id),
  project_id INT REFERENCES projects(id),
  joined_at TIMESTAMPTZ DEFAULT NOW(),
  role TEXT
);

論理削除

-- Pattern: deleted_at nullable timestamp
ALTER TABLE orders ADD COLUMN deleted_at TIMESTAMPTZ;

-- Partial index makes "active" queries fast
CREATE INDEX idx_orders_active ON orders (user_id, created_at)
WHERE deleted_at IS NULL;

-- View hides soft-deleted rows for application code
CREATE VIEW active_orders AS
  SELECT * FROM orders WHERE deleted_at IS NULL;

警告：論理削除を使用すると、一意制約が複雑になります。一意なメールアドレス列では、そのメールアドレスを持つ削除済みユーザーは 1 人しか登録できません。部分一意インデックスを使用してください。

CREATE UNIQUE INDEX idx_users_email_active ON users (email)
WHERE deleted_at IS NULL;

多態的な関連付け

アプローチは 2 つあります。素朴なパターンは避けてください。

-- Bad: nullable FK columns for each possible parent type
CREATE TABLE comments (
  id SERIAL PRIMARY KEY,
  post_id INT REFERENCES posts(id),      -- nullable
  article_id INT REFERENCES articles(id), -- nullable
  video_id INT REFERENCES videos(id),     -- nullable
  body TEXT
);

-- Good: separate association tables (referential integrity preserved)
CREATE TABLE post_comments (
  comment_id INT REFERENCES comments(id),
  post_id INT REFERENCES posts(id),
  PRIMARY KEY (comment_id, post_id)
);

-- Or: single-table inheritance with a type column + CHECK constraint
CREATE TABLE comments (
  id SERIAL PRIMARY KEY,
  parent_type TEXT NOT NULL CHECK (parent_type IN ('post', 'article', 'video')),
  parent_id INT NOT NULL,
  body TEXT
);
CREATE INDEX idx_comments_parent ON comments (parent_type, parent_id);

コネクションプーリング

一般的な Web アプリケーション向けの PgBouncer 設定：

[pgbouncer]
pool_mode = transaction        ; Best for short-lived web requests
max_client_conn = 1000         ; Total client connections pooler accepts
default_pool_size = 20         ; DB connections per database/user pair
server_idle_timeout = 600      ; Close idle server connections after 10 min

PgBouncer と Prisma を使用する場合は、接続 URL に pgbouncer=true を設定します。

DATABASE_URL="postgresql://user:pass@host:6432/db?pgbouncer=true&connection_limit=1"

注：PgBouncer のトランザクションモードは、プリペアドステートメント、SET、LISTEN/NOTIFY をサポートしていません。ORM がプリペアドステートメントを必要とし、プールサイズに余裕がある場合は、セッションモードを使用してください。

---

アンチパターン

アンチパターン：早すぎる非正規化

初心者：「結合は遅いので、避けるためにデータをメインテーブルにコピーします。」

エキスパート：インデックスがあれば、結合は高速です。データをコピーすると更新時の異常が発生します。同じ事実が2か所に保存され、内容が食い違う可能性があります。正しい手順は、まず正規化し、実際の負荷の下でクエリ時間を測定し、EXPLAIN ANALYZEで具体的な結合のボトルネックを特定してから、最後の手段としてマテリアライズドビューまたは単一のキャッシュ済み非正規化カラムを検討することです。

検出方法：usersテーブルへのuser_id FKがあるordersテーブルに、user_nameのようなカラムがないか確認します。users.nameが変更可能な場合、orders.user_nameの値はずれていきます。

LLMの誤り：学習データには、「パフォーマンス最適化」として早い段階で非正規化するチュートリアルが多数含まれています。これらは、インデックスを意識したORMが広く普及する前のもので、手動でクエリを作成することを前提としています。

---

アンチパターン：外部キーのインデックス不足

初心者：「データベースが結合方法を判断してくれるので、FK制約だけ設定すれば十分です。」

エキスパート：外部キー制約は参照整合性を保証しますが、インデックスは作成しません。orders.user_idにインデックスがない状態でJOIN orders ON orders.user_id = users.idを実行すると、ユーザーごとにordersテーブル全体をシーケンシャルスキャンすることになります。数百万行あるテーブルでは、これは致命的です。

検出方法：

-- Find FK columns with no index (PostgreSQL)
SELECT
  tc.table_name,
  kcu.column_name
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu
  ON tc.constraint_name = kcu.constraint_name
LEFT JOIN pg_indexes pi
  ON pi.tablename = tc.table_name
  AND pi.indexdef LIKE '%' || kcu.column_name || '%'
WHERE tc.constraint_type = 'FOREIGN KEY'
  AND pi.indexname IS NULL;

修正方法：すべてのFKカラムにインデックスを追加します。稼働中のテーブルでは、必ずCONCURRENTLYを指定してください。

---

アンチパターン：本番クエリでのSELECT *

初心者：「SELECT *で問題ありません。データベースは必要なデータだけを取得してくれます。」

エキスパート：SELECT *は、使用しない大きなTEXT、JSONB、BYTEAカラムも含め、すべてのカラムを取得します。インデックスのみのスキャンができなくなり（インデックスでクエリをカバーできる場合でも、ヒープへのアクセスが必要になります）、位置によるカラムのバインドに依存するORMでは、カラムの追加や順序変更によって動作しなくなることがあります。必ずカラム名を明示してください。

検出方法：アプリケーションコードで、ホットパスにあるクエリ内のSELECT *を検索します。ORMでは、.findAll()などの同等のメソッドがデフォルトですべてのカラムを選択していないか確認し、フィールドを明示的に選択してください。

---

参考資料

• references/indexing-strategies.md — B-tree、GIN、GiST、hash、partial、coveringインデックスの選択時に参照してください。インデックスのみのスキャンの前提条件や、複数カラムインデックスの順序規則も含まれています。
• references/migration-patterns.md — ダウンタイムなしの移行を計画する際に参照してください。expand-contractパターン、ロックタイムアウト設定、バックフィルの分割、ロールバック戦略について説明しています。