データベース設計

スキーマ設計の原則

正規化ガイドライン

-- 1NF: アトミック値、重複するグループなし
-- 2NF: 複合キーへの部分的依存なし
-- 3NF: 推移的依存なし

-- ユーザーテーブル（正規化済み）
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  email VARCHAR(255) UNIQUE NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- アドレステーブル（独立したエンティティ）
CREATE TABLE addresses (
  id SERIAL PRIMARY KEY,
  user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
  street VARCHAR(255),
  city VARCHAR(100),
  country VARCHAR(100),
  is_primary BOOLEAN DEFAULT false
);

パフォーマンスのための非正規化

-- 読み取りパフォーマンスが書き込み一貫性より重要な場合
CREATE TABLE order_summaries (
  id SERIAL PRIMARY KEY,
  order_id INTEGER REFERENCES orders(id),
  customer_name VARCHAR(255),  -- customersテーブルから非正規化
  total_amount DECIMAL(10,2),
  item_count INTEGER,
  last_updated TIMESTAMPTZ DEFAULT NOW()
);

インデックス設計

一般的なインデックスパターン

-- B-tree（デフォルト）：等値クエリと範囲クエリ向け
CREATE INDEX idx_users_email ON users(email);

-- 複合インデックス（順序が重要！）
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at DESC);

-- 部分インデックス（特定の条件用）
CREATE INDEX idx_active_users ON users(email) WHERE deleted_at IS NULL;

-- GIN インデックス（配列/JSONBカラム向け）
CREATE INDEX idx_posts_tags ON posts USING GIN(tags);

-- カバリングインデックス（追加カラムを含む）
CREATE INDEX idx_orders_covering ON orders(user_id) INCLUDE (total, status);

インデックス分析

-- インデックス使用状況を確認
SELECT
  schemaname, tablename, indexname,
  idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes
ORDER BY idx_scan DESC;

-- 不足しているインデックスを見つける
SELECT
  relname, seq_scan, seq_tup_read,
  idx_scan, idx_tup_fetch
FROM pg_stat_user_tables
WHERE seq_scan > idx_scan
ORDER BY seq_tup_read DESC;

マイグレーションパターン

安全なマイグレーションテンプレート

-- 常にトランザクションを使用
BEGIN;

-- カラムを追加（PG 11以降ではノンブロッキング）
ALTER TABLE users ADD COLUMN status VARCHAR(20) DEFAULT 'active';

-- インデックスを並行して作成（テーブルをロックしない）
CREATE INDEX CONCURRENTLY idx_users_status ON users(status);

-- バッチでデータをバックフィル
UPDATE users SET status = 'active' WHERE status IS NULL AND id BETWEEN 1 AND 10000;

COMMIT;

ゼロダウンタイムマイグレーション

1. 新しいカラムを追加（null許容）
2. 両方のカラムに書き込むコードをデプロイ
3. 古いデータをバックフィル
4. 新しいカラムから読み取るコードをデプロイ
5. 古いカラムを削除

クエリ最適化

EXPLAIN分析

-- 常にEXPLAIN ANALYZEを使用
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM orders WHERE user_id = 123 AND status = 'pending';

-- 監視すべき主要メトリクス：
-- - Seq Scan vs Index Scan
-- - 実際の行数 vs 推定行数
-- - Buffers: shared hit vs read

一般的な最適化

-- 大規模セットの場合はINの代わりにEXISTSを使用
SELECT * FROM users u
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

-- OFFSETの代わりにキーセット（カーソル）でページネーション
SELECT * FROM posts
WHERE created_at < '2024-01-01'
ORDER BY created_at DESC
LIMIT 20;

-- 複雑なクエリにはCTEを使用
WITH active_users AS (
  SELECT id FROM users WHERE last_login > NOW() - INTERVAL '30 days'
)
SELECT * FROM orders WHERE user_id IN (SELECT id FROM active_users);

制約とデータ整合性

-- プライマリキー
ALTER TABLE users ADD PRIMARY KEY (id);

-- 外部キー（カスケード削除）
ALTER TABLE orders ADD CONSTRAINT fk_orders_user
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE;

-- チェック制約
ALTER TABLE products ADD CONSTRAINT chk_price_positive
  CHECK (price >= 0);

-- ユニーク制約
ALTER TABLE users ADD CONSTRAINT uniq_users_email UNIQUE (email);

-- 除外制約（重複する範囲なし）
ALTER TABLE reservations ADD CONSTRAINT excl_no_overlap
  EXCLUDE USING gist (room_id WITH =, tsrange(start_time, end_time) WITH &&);

ベストプラクティス

• 公開IDにはUUID、内部用にはSERIAL/BIGSERIALを使用
• 常にcreated_atとupdated_atタイムスタンプを追加
• 重要なデータにはソフト削除（deleted_at）を使用
• 分散システムでは結果整合性を考慮した設計を
• マイグレーションファイルにスキーマの決定事項を記録
• デプロイ前に本番サイズのデータでマイグレーションをテスト