database-architect

データベースアーキテクトスキル

データベースアーキテクトスキル - リレーショナルデータベース（PostgreSQL、MySQL）と NoSQL データベース（MongoDB、Redis）の両方について、効率的なスキーマの設計、クエリの最適化、インデックスの計画、移行戦略の策定を行います。 - 正規化および非正規化スキーマの設計 - クエリの最適化と実行計画の分析 - インデックス戦略の策定 - データモデリング（ER 図、リレーションシップ） - 移行の計画とバージョン管理 - パフォーマンスのトラブルシューティング

ステップ 1：データ要件の把握

要件を収集します。

1. エンティティ：どのデータを保存する必要がありますか？
2. リレーションシップ：エンティティはどのように関連していますか（1:1、1:N、N:M）？
3. アクセスパターン：データはどのようにクエリされますか？
4. データ量：予想されるデータサイズと増加率
5. 一貫性：ACID 要件と結果整合性のどちらが必要ですか？

ステップ 2：スキーマの設計

リレーショナルデータベースの場合：

1. 正規化：冗長性を減らすため、3NF から始めます
2. 主キーの定義：代理キー（UUID/SERIAL）または自然キーを使用します
3. 外部キーの定義：参照整合性を確立します
4. 非正規化の検討：パフォーマンス上の必要性が実証されている場合に限ります

NoSQL データベースの場合：

1. クエリを基準にモデリング：アクセスパターンに合わせてドキュメント／コレクションを設計します
2. 埋め込みと参照の使い分け：1:1／1:少数には埋め込み、1:多数には参照を使用します
3. シャードキーの選択：均等に分散されるキーを選びます

ステップ 3：インデックスの計画

クエリパターンに基づいてインデックス戦略を立てます。

-- Example: Users table with common queries
CREATE INDEX idx_users_email ON users(email);           -- Exact match
CREATE INDEX idx_users_name ON users(last_name, first_name);  -- Range/sort
CREATE INDEX idx_users_created ON users(created_at DESC);     -- Ordering

インデックスのガイドライン：

• WHERE、JOIN、ORDER BY で使用される列にインデックスを作成します
• 複数列のクエリでは複合インデックスを検討します
• インデックスの過剰な作成は避けます（書き込みが遅くなります）
• 読み取り負荷の高いクエリにはカバリングインデックスを使用します

ステップ 4：移行の計画

バージョン管理された移行を作成します。

migrations/
  001_create_users.sql
  002_add_email_index.sql
  003_create_orders.sql

移行のベストプラクティス：

• 必ず up と down の移行を含めます
• 本番環境に近いデータで移行をテストします
• ダウンタイムゼロの移行を計画します
• 移行を実行する前にバックアップを取ります

ステップ 5：クエリの最適化

遅いクエリを分析し、改善します。

1. EXPLAIN ANALYZE を使用：実行計画を把握します
2. テーブルスキャンを特定：インデックススキャンに置き換えます
3. JOIN を最適化：結合列にインデックスがあることを確認します
4. 操作をバッチ処理：一括挿入／更新を使用します
5. コネクションプーリング：接続のオーバーヘッドを削減します

ステップ 6：PostgreSQL 17 の機能（2024–2026）

該当する場合は、PostgreSQL 17 の機能を活用します。

パフォーマンスの向上：

• 新しい VACUUM メモリ管理 — メモリ使用量を最大 20 分の 1 に削減。負荷の高いシステムでの vacuum がより高速になりました
• ストリーミング I/O インターフェースにより、大規模データセットのシーケンシャルスキャンが高速化
• BRIN インデックスの並列ビルドに対応
• IN 句のクエリで B-tree インデックスがより効率的に
• CTE（共通テーブル式）の計画を最適化

SQL/JSON の拡張機能（PG 17）：

• JSON_TABLE() — JSON データをリレーショナルテーブル形式に変換
• JSON コンストラクターと ID 関数（JSON()、JSON_SCALAR()、JSON_ARRAY()、JSON_OBJECT()）
• JSONB 列に対する表現力豊かなパスベースのクエリには jsonpath を使用

増分バックアップ：

• pg_basebackup が増分バックアップに対応。pg_upgrade と組み合わせることで、データ損失ゼロのメジャーバージョンアップグレードが可能

論理レプリケーションの改善：

• 論理レプリケーションスロットのフェイルオーバー制御
• pg_createsubscriber により、物理スタンバイから論理レプリカを作成
• pg_upgrade がメジャーバージョンアップグレード時に論理レプリケーションスロットを保持するようになりました

セキュリティ：

• 新しい MAINTAIN 権限 — スーパーユーザーの完全なアクセス権限を与えずに、対象を絞ったメンテナンス権限を付与
• 直接 TLS ハンドシェイクを行う sslnegotiation=direct クライアントオプション（往復通信を回避）

COPY の改善：

• COPY ... ON_ERROR ignore — インポートを中止せず、行単位のエラーを無視して続行

ステップ 7：AI 埋め込み用の pgvector

リレーショナルデータと並べてベクトル埋め込みを保存・検索し、別のベクトルデータベースが不要になるようにします：

-- Install extension
CREATE EXTENSION IF NOT EXISTS vector;

-- Table with embedding column (1536 dims for OpenAI text-embedding-3-small)
CREATE TABLE documents (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    content TEXT NOT NULL,
    embedding vector(1536),
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- IVFFlat index for approximate nearest neighbor (ANN) search
-- lists = sqrt(row_count) is a good starting value
CREATE INDEX idx_documents_embedding ON documents
    USING ivfflat (embedding vector_cosine_ops) WITH (lists = 100);

-- HNSW index (faster queries, more memory; preferred for < 1M vectors)
CREATE INDEX idx_documents_embedding_hnsw ON documents
    USING hnsw (embedding vector_cosine_ops) WITH (m = 16, ef_construction = 64);

-- Similarity search (cosine distance)
SELECT id, content, 1 - (embedding <=> $1::vector) AS similarity
FROM documents
ORDER BY embedding <=> $1::vector
LIMIT 10;

pgvector と専用ベクトル DB の使い分け：

• 約 1,000 万件以下のベクトル：pgvector で十分です（HNSW インデックスを使用したクエリは 50 ミリ秒未満）
• 1,000 万件を超えるベクトル、または専用 ANN アルゴリズムが必要な場合：Pinecone、Weaviate、Qdrant を検討してください
• pgvector の利点：PostgreSQL の他の機能と同じバックアップ、レプリケーション、コネクションプーリングを利用できます

ステップ 8：テーブルパーティショニング戦略

使用可能な RAM を超えると予想されるテーブルには、宣言的パーティショニングを使用します：

-- Range partitioning by date (common for time-series / logs)
CREATE TABLE events (
    id BIGSERIAL,
    created_at TIMESTAMPTZ NOT NULL,
    event_type TEXT NOT NULL,
    payload JSONB
) PARTITION BY RANGE (created_at);

-- Monthly partitions
CREATE TABLE events_2025_01 PARTITION OF events
    FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');
CREATE TABLE events_2025_02 PARTITION OF events
    FOR VALUES FROM ('2025-02-01') TO ('2025-03-01');

-- Hash partitioning for even distribution (e.g., multi-tenant)
CREATE TABLE orders (
    id UUID NOT NULL,
    tenant_id UUID NOT NULL,
    total DECIMAL(12,2)
) PARTITION BY HASH (tenant_id);

CREATE TABLE orders_p0 PARTITION OF orders FOR VALUES WITH (modulus 4, remainder 0);
CREATE TABLE orders_p1 PARTITION OF orders FOR VALUES WITH (modulus 4, remainder 1);
CREATE TABLE orders_p2 PARTITION OF orders FOR VALUES WITH (modulus 4, remainder 2);
CREATE TABLE orders_p3 PARTITION OF orders FOR VALUES WITH (modulus 4, remainder 3);

パーティションプルーニング： WHERE にパーティションキーが含まれている場合、PostgreSQL は該当しないパーティションを自動的にスキップします。クエリには必ずパーティションキーを含めてください。

パーティション化テーブルのインデックス： 親テーブルに作成されたインデックスは、すべての子パーティションにも自動的に作成されます。

ステップ 9：大規模環境での JSONB パターン

-- Generated columns promote hot JSONB fields to indexed native columns
CREATE TABLE customers (
    id BIGSERIAL PRIMARY KEY,
    data JSONB NOT NULL,
    -- Promote frequently filtered fields to B-tree indexed generated columns
    country TEXT GENERATED ALWAYS AS (data->>'country') STORED,
    signup_date DATE GENERATED ALWAYS AS ((data->>'signup_date')::DATE) STORED
);
CREATE INDEX idx_customers_country ON customers (country);
CREATE INDEX idx_customers_signup ON customers (signup_date);

-- GIN index for containment / key-existence queries
CREATE INDEX idx_customers_data_gin ON customers USING GIN (data);

-- Partial GIN index for large tables (index only active records)
CREATE INDEX idx_customers_data_active ON customers
    USING GIN (data) WHERE (data->>'status') = 'active';

-- jsonpath query example (PG 17)
SELECT * FROM customers
WHERE data @? '$.tags[*] ? (@ == "premium")';

ステップ 10：コネクションプーリング

すべての本番環境のデプロイで、PostgreSQL の前段にコネクションプーラーを配置します：

PgBouncer（軽量で、実績があります）：

# pgbouncer.ini
[databases]
mydb = host=127.0.0.1 port=5432 dbname=mydb

[pgbouncer]
pool_mode = transaction       ; transaction pooling for stateless apps
max_client_conn = 1000
default_pool_size = 25
server_pool_size = 5

Supavisor（クラウドネイティブ、マルチテナント対応、Elixir ベース）：

• 数千の短命な接続を扱うサーバーレス／エッジ関数向けに設計されています
• セッションプーリングモードとトランザクションプーリングモードの両方をサポートします
• Supabase のデプロイではデフォルトで使用され、セルフホスト版も利用できます

プーリングモード：

モード	用途	備考
セッション	長時間の接続、LISTEN/NOTIFY	1 クライアント = 1 サーバー接続
トランザクション	ステートレス API（推奨されるデフォルト）	最も効率的です。SET／プリペアドステートメントは使用できません
ステートメント	ほとんど必要ありません	各ステートメントで異なるサーバー接続を使用できます

</execution_process>

<best_practices>

1. まず正規化する：データに基づいて後から最適化します
2. 慎重にインデックスを設定する：実際のクエリパターンに基づいて設定します
3. マイグレーションを使用する：スキーマを直接変更しないでください
4. パフォーマンスを監視する：データベースのプロファイリングツールを使用します
5. スケールを考慮する：大規模なテーブルにはパーティショニングを検討します
6. PostgreSQL 17 にアップグレードする：新しい VACUUM メモリーマネージャー、SQL/JSON 関数、増分バックアップのサポートを活用できます
7. AI には pgvector を使用する：専用のベクトルデータベースを検討する前に、HNSW または IVFFlat インデックスを使用して PostgreSQL に埋め込みを保存します
8. 大規模環境で JSONB を活用する：頻繁に使用する JSONB フィールドは B-tree インデックス付きの生成列に昇格させ、包含クエリには GIN を使用します
9. 常に接続をプールする：すべての本番 PostgreSQL デプロイで、PgBouncer（トランザクションモード）または Supavisor を使用します
10. 大規模なテーブルをパーティション化する：サーバー RAM を超えると予測されるテーブルには、レンジ（時系列）、リスト（カテゴリ）、またはハッシュ（マルチテナント）パーティショニングを適用します

</best_practices>

**スキーマ設計の依頼**：

Design a schema for an e-commerce orders system

回答例：

-- Users table
CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email VARCHAR(255) UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT NOW()
);

-- Orders table
CREATE TABLE orders (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID REFERENCES users(id),
    status VARCHAR(20) DEFAULT 'pending',
    total_amount DECIMAL(10,2),
    created_at TIMESTAMP DEFAULT NOW()
);

-- Order items (1:N relationship)
CREATE TABLE order_items (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    order_id UUID REFERENCES orders(id) ON DELETE CASCADE,
    product_id UUID NOT NULL,
    quantity INTEGER NOT NULL,
    unit_price DECIMAL(10,2)
);

-- Indexes for common queries
CREATE INDEX idx_orders_user ON orders(user_id);
CREATE INDEX idx_orders_status ON orders(status);
CREATE INDEX idx_order_items_order ON order_items(order_id);

</usage_example>

ルール

• 非正規化は、パフォーマンスデータに基づいて必ず正当化してください
• すべてのマイグレーションにロールバック戦略を含めてください
• リレーションシップと制約を文書化してください

関連ワークフロー

このスキルには、複雑なマルチエージェントシナリオに対応するワークフローがあります：

• ワークフロー：.claude/workflows/database-architect-skill-workflow.md
• ワークフローを使用する場合：要件分析、スキーマ設計、クエリ最適化、マイグレーション計画、テストを含む包括的なデータベース設計（複数フェーズ、複数エージェント）
• スキルを直接使用する場合：簡易なスキーマレビュー、または単一エージェントで行うデータベースタスク

厳守事項

1. 絶対に、UP と DOWN の両方のスクリプトを含むバージョン管理されたマイグレーションなしでスキーマを変更しないでください。本番環境での手動 DDL は復旧できません。
2. 必ず、非正規化を検討する前に少なくとも第 3 正規形（3NF）に正規化してください。実測したパフォーマンスの証拠がないまま、時期尚早に最適化しないでください。
3. 必ず、EXPLAIN ANALYZE による実際のクエリパターンに基づいてインデックスを計画してください。実際のワークロードをプロファイリングする前に、推測でインデックスを追加しないでください。
4. 絶対に、本番環境に近いデータに対して実行せずにマイグレーションをテストまたはデプロイしないでください。スキーマの問題は、空のテーブルではなく現実的なデータ量で表面化します。
5. 必ず、本番環境ではコネクションプーリング（Supavisor または PgBouncer）を使用してください。サーバーレス関数からの直接接続は、負荷がかかるとデータベースの接続上限に達します。

アンチパターン

アンチパターン	問題点	正しい方法
本番環境での手動 DDL	ロールバック手段がなく、マイグレーション履歴が壊れます	必ず DOWN スクリプトを含むバージョン管理されたマイグレーションを使用します
時期尚早な非正規化	プロファイリング前に複雑さを増やし、多くの場合、測定可能な効果がありません	まず正規化し、EXPLAIN ANALYZE でボトルネックが明らかになった場合にのみ非正規化します
すべての列へのインデックス作成	書き込みが遅くなり、ストレージを浪費し、クエリプランナーを誤らせます	WHERE、JOIN、ORDER BY 句に登場する列のみにインデックスを作成します
デフォルト値なしでの NOT NULL 列の追加	大規模なデータセットでは、マイグレーション中にテーブル全体がロックされます	NULL 許容列を追加し、バッチでバックフィルしてから、NOT NULL 制約を追加します
サーバーレス関数からの直接接続	負荷の急増時に接続上限に達します	トランザクションプーリングモードで PgBouncer または Supavisor を使用します

メモリプロトコル（必須）

開始前：

cat .claude/context/memory/learnings.md

完了後：

• 新しいパターン -> .claude/context/memory/learnings.md
• 発見した問題 -> .claude/context/memory/issues.md
• 決定事項 -> .claude/context/memory/decisions.md

中断を想定してください。コンテキストがリセットされる場合があります。メモリに記録されていなければ、実施されていないものとみなします。