mybook

データベース設計 — RDBの基礎

スロークエリの悪夢

EchoTaskのユーザーが5万人を突破したある日、ハルトはダッシュボードを見て固まった。タスク一覧を表示するAPIが、平均2秒かかっていた。

$ grep "duration" /var/log/postgresql/postgresql.log | tail -5
2024-05-01 09:23:15 JST LOG: duration: 2341.234 ms
  statement: SELECT tasks.*, users.name FROM tasks
             LEFT JOIN users ON tasks.user_id = users.id
             WHERE tasks.project_id = 123
             ORDER BY tasks.created_at DESC LIMIT 20

インデックスがない。正規化が適切でない。クエリが非効率だ。「データベース設計を最初からやり直す必要がある」——ハルトは覚悟を決めた。

正規化（Normalization）

正規化とは、データの冗長性を排除し、整合性を保ちやすくする設計原則だ。第1・第2・第3正規形（1NF/2NF/3NF）を段階的に適用する。

正規化前（問題のある設計）

-- 非正規化テーブル（悪い例）
CREATE TABLE tasks (
  id           BIGINT PRIMARY KEY,
  title        VARCHAR(255),
  user_id      BIGINT,
  user_name    VARCHAR(100),   -- usersテーブルと重複
  user_email   VARCHAR(255),   -- 同じく重複
  project_name VARCHAR(100),   -- projectsテーブルと重複
  tag1         VARCHAR(50),    -- タグが増えたら対応不能
  tag2         VARCHAR(50),
  tag3         VARCHAR(50)
);

問題点：ユーザー名を変更すると全行UPDATE必須。タグが4個以上になるとスキーマ変更が必要。

第1正規形（1NF）— 繰り返し列を排除する

1NFの条件は「各セルに単一の値のみを持つこと」だ。タグ列を別テーブルに切り出す。

CREATE TABLE tags (
  id   BIGSERIAL PRIMARY KEY,
  name VARCHAR(50) NOT NULL UNIQUE
);
 
CREATE TABLE task_tags (
  task_id BIGINT NOT NULL REFERENCES tasks(id) ON DELETE CASCADE,
  tag_id  BIGINT NOT NULL REFERENCES tags(id)  ON DELETE CASCADE,
  PRIMARY KEY (task_id, tag_id)
);

第2正規形（2NF）— 部分関数従属を排除する

2NFは「すべての非キー列が主キー全体に依存していること」。複合主キーのテーブルで問題が起きやすい。

-- 悪い例: project_name は project_id にのみ依存（部分依存）
CREATE TABLE task_assignments (
  task_id      BIGINT,
  project_id   BIGINT,
  project_name VARCHAR(100),  -- ← 部分依存
  assigned_at  TIMESTAMPTZ,
  PRIMARY KEY (task_id, project_id)
);
 
-- 修正: project_name は projects テーブルで管理する
CREATE TABLE task_assignments (
  task_id     BIGINT NOT NULL REFERENCES tasks(id),
  project_id  BIGINT NOT NULL REFERENCES projects(id),
  assigned_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  PRIMARY KEY (task_id, project_id)
);

第3正規形（3NF）— 推移的従属を排除する

3NFは「非キー列が他の非キー列に依存していないこと」だ。

-- 悪い例: department_name は department_id を介して依存（推移的依存）
CREATE TABLE users (
  id              BIGSERIAL PRIMARY KEY,
  name            VARCHAR(100),
  department_id   BIGINT,
  department_name VARCHAR(100)  -- ← 推移的依存
);
 
-- 修正: departments を独立させる
CREATE TABLE departments (
  id   BIGSERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL UNIQUE
);
 
CREATE TABLE users (
  id            BIGSERIAL PRIMARY KEY,
  name          VARCHAR(100) NOT NULL,
  email         VARCHAR(255) NOT NULL UNIQUE,
  department_id BIGINT REFERENCES departments(id),
  created_at    TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at    TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

正規化後のEchoTaskスキーマとER図

CREATE TABLE projects (
  id          BIGSERIAL PRIMARY KEY,
  user_id     BIGINT NOT NULL REFERENCES users(id),
  name        VARCHAR(255) NOT NULL,
  tasks_count INTEGER NOT NULL DEFAULT 0,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
 
CREATE TABLE tasks (
  id          BIGSERIAL PRIMARY KEY,
  project_id  BIGINT NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
  assignee_id BIGINT REFERENCES users(id) ON DELETE SET NULL,
  title       VARCHAR(255) NOT NULL,
  status      SMALLINT NOT NULL DEFAULT 0,
  priority    SMALLINT NOT NULL DEFAULT 1,
  due_date    DATE,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
Loading diagram...

INFO

正規化は「どこまでやるか」が重要

第3正規形まで正規化するのが基本だが、パフォーマンスのために意図的に非正規化（denormalization）するケースもある。重要なのは「なぜ非正規化しているか」を明示的に理解した上で決断することだ。

インデックス設計

B-treeインデックスの仕組み

PostgreSQLのデフォルトはB-tree（Balanced Tree）構造だ。

Loading diagram...
  • 等値検索 WHERE id = 123 → O(log n) でルートから葉まで辿る
  • 範囲検索 WHERE id BETWEEN 100 AND 200 → 開始点を特定し葉ノードを横断
  • ソート ORDER BY id → すでにソート済みなのでそのまま使える

インデックスを追加する

-- インデックスなし
EXPLAIN SELECT * FROM tasks WHERE project_id = 123;
-- Seq Scan on tasks  (cost=0.00..4832.00 rows=20 width=100)
-- → 100万行を全スキャン
# db/migrate/20240101000010_add_indexes_to_tasks.rb
class AddIndexesToTasks < ActiveRecord::Migration[7.1]
  def change
    add_index :tasks, :project_id
    add_index :tasks, :assignee_id
    add_index :tasks, [:project_id, :status]
 
    # 未完了タスクのみを対象にした部分インデックス
    add_index :tasks, [:due_date, :status],
              where: "status NOT IN (2, 3)",
              name: "index_tasks_on_due_date_active"
  end
end
-- インデックス追加後
EXPLAIN SELECT * FROM tasks WHERE project_id = 123;
-- Index Scan using index_tasks_on_project_id on tasks
--   (cost=0.43..8.45 rows=20 width=100)
-- → コスト 4832 → 8.45 に激減

WARNING

インデックスは万能ではない

インデックスは読み取りを速くするが、書き込み（INSERT/UPDATE/DELETE）を遅くしストレージを消費する。クエリパターンを分析してから追加すること。カーディナリティ（種類数）が低いカラム（booleanなど）へのインデックスは効果が薄い。

EXPLAINの読み方

EXPLAIN ANALYZE はクエリの実行計画と実測値の両方を示す最強のデバッグツールだ。

EXPLAIN (ANALYZE, BUFFERS)
SELECT t.id, t.title, t.status, u.name AS assignee_name
FROM tasks t
LEFT JOIN users u ON t.assignee_id = u.id
WHERE t.project_id = 123 AND t.status IN (0, 1)
ORDER BY t.due_date ASC NULLS LAST
LIMIT 20;
Limit  (cost=1.15..5.42 rows=20 width=120)
       (actual time=0.312..0.789 rows=20 loops=1)
  ->  Nested Loop Left Join
      (actual time=0.308..0.761 rows=20 loops=1)
        ->  Index Scan using index_tasks_on_project_id_and_status on tasks t
            (actual time=0.289..0.690 rows=20 loops=1)
              Index Cond: (project_id = 123) AND (status = ANY ('{0,1}'))
        ->  Index Scan using users_pkey on users u
            (actual time=0.003..0.003 rows=1 loops=20)
              Index Cond: (id = t.assignee_id)
Planning Time: 1.234 ms
Execution Time: 0.912 ms
キーワード意味
cost=X..Y最初の行..全行を返すコスト（推定）
actual time=X..Y実際にかかったミリ秒
rows=N推定行数（actualと大きくズレていたら統計が古い）
Seq Scan全テーブルスキャン（大テーブルで出たら要注意）
Index Scanインデックスを使ったスキャン

VACUUM・ANALYZE・インデックス監視

-- デッドタプル数を確認
SELECT relname, n_live_tup, n_dead_tup, last_vacuum, last_autovacuum
FROM pg_stat_user_tables
WHERE relname = 'tasks';
 
-- 手動VACUUM + 統計更新
VACUUM ANALYZE tasks;
 
-- インデックスの使用状況を確認
SELECT
  indexrelname AS index_name,
  idx_scan     AS times_used,
  idx_tup_read AS rows_read
FROM pg_stat_user_indexes
WHERE relname = 'tasks'
ORDER BY idx_scan DESC;

times_used = 0 のインデックスは不要だ。削除することでINSERT/UPDATEのオーバーヘッドを減らせる。

-- 使われていないインデックスをロックなしで削除
DROP INDEX CONCURRENTLY index_tasks_on_updated_at;

pg_stat_statementsでスロークエリを特定

SELECT
  LEFT(query, 80)                            AS query_snippet,
  calls,
  ROUND(mean_exec_time::numeric, 2)          AS avg_ms,
  ROUND(total_exec_time::numeric / 1000, 2) AS total_sec
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

N+1問題とcounter_cache

# 問題: タスクごとにSELECTが発生（N+1）
@tasks = Task.where(project_id: params[:project_id])
@tasks.map { |t| t.assignee&.name }  # 20タスク → 21クエリ
 
# 解決: includes で関連を一括ロード
@tasks = Task
  .where(project_id: params[:project_id])
  .includes(:assignee, :tags)         # 合計3クエリで済む

counter_cacheでCOUNTを排除する

# app/models/task.rb
class Task < ApplicationRecord
  belongs_to :project, counter_cache: true
  # projects.tasks_count が自動更新される
end
 
# db/migrate/20240601000001_add_tasks_count_to_projects.rb
class AddTasksCountToProjects < ActiveRecord::Migration[7.1]
  def change
    add_column :projects, :tasks_count, :integer, default: 0, null: false
    reversible do |dir|
      dir.up { Project.find_each { |p| Project.reset_counters(p.id, :tasks) } }
    end
  end
end

Golangでのデータベースアクセス（pgx）

// internal/repository/task_repository.go
package repository
 
import (
	"context"
	"fmt"
 
	"github.com/jackc/pgx/v5"
	"github.com/jackc/pgx/v5/pgxpool"
)
 
type Task struct {
	ID           int64
	ProjectID    int64
	Title        string
	Status       int
	AssigneeName *string
}
 
type TaskRepository struct{ pool *pgxpool.Pool }
 
func (r *TaskRepository) ListByProject(ctx context.Context, projectID int64) ([]Task, error) {
	const query = `
		SELECT t.id, t.project_id, t.title, t.status, u.name AS assignee_name
		FROM tasks t
		LEFT JOIN users u ON t.assignee_id = u.id
		WHERE t.project_id = $1 AND t.status NOT IN (2, 3)
		ORDER BY t.priority DESC, t.due_date ASC NULLS LAST
		LIMIT 50`
 
	rows, err := r.pool.Query(ctx, query, projectID)
	if err != nil {
		return nil, fmt.Errorf("ListByProject: %w", err)
	}
	defer rows.Close()
 
	return pgx.CollectRows(rows, pgx.RowToStructByName[Task])
}

INFO

pgx v5 の pgxpool を使う理由

database/sql は汎用インターフェースだが、pgx は PostgreSQL 固有の型（UUID, JSONB, ARRAY）を効率的に扱える。pgxpool はコネクションプールを内包しており、並行リクエストが多い環境で安全に使える。

AWS RDSの設定

# CloudFormation: RDSインスタンス
RDSInstance:
  Type: AWS::RDS::DBInstance
  Properties:
    DBInstanceClass: db.t3.medium
    Engine: postgres
    EngineVersion: "16.3"
    DBName: echo_task_production
    MasterUsername: !Sub "{{resolve:secretsmanager:echo-task-db-secret:SecretString:username}}"
    MasterUserPassword: !Sub "{{resolve:secretsmanager:echo-task-db-secret:SecretString:password}}"
    AllocatedStorage: 100
    StorageType: gp3
    StorageEncrypted: true
    MultiAZ: true
    DeletionProtection: true
    EnablePerformanceInsights: true
    PerformanceInsightsRetentionPeriod: 7
    BackupRetentionPeriod: 7
    PreferredBackupWindow: "17:00-18:00"
    VPCSecurityGroups:
      - !Ref RDSSecurityGroup
    DBSubnetGroupName: !Ref DBSubnetGroup

RDS Proxyで接続数を制御する

ECSタスクが増えるとDB接続数が爆発する。RDS Proxyがコネクションプーリングで解決する。

【RDS Proxy なし】
100 ECSタスク × 5 Pumaスレッド = 500接続 → RDS上限超え

【RDS Proxy あり】
ECSタスク → Proxy（500接続受け付け）→ RDS（10〜20接続のみ）

RDSProxy:
  Type: AWS::RDS::DBProxy
  Properties:
    DBProxyName: echo-task-proxy
    EngineFamily: POSTGRESQL
    RoleArn: !GetAtt RDSProxyRole.Arn
    Auth:
      - AuthScheme: SECRETS
        SecretArn: !Ref DBSecret
        IAMAuth: DISABLED
    VpcSubnetIds: [!Ref PrivateSubnet1, !Ref PrivateSubnet2]
    VpcSecurityGroupIds: [!Ref RDSProxySecurityGroup]
    ConnectionPoolConfig:
      MaxConnectionsPercent: 90
      MaxIdleConnectionsPercent: 50
      ConnectionBorrowTimeout: 120
# config/database.yml
# DATABASE_URL の接続先を Proxy エンドポイントに変更するだけ
# echo-task-proxy.proxy-xxxxxx.ap-northeast-1.rds.amazonaws.com
production:
  adapter: postgresql
  pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>
  url: <%= ENV['DATABASE_URL'] %>

無停止マイグレーション戦略

カラム追加は安全（PostgreSQL 11+）

class AddPinnedToTasks < ActiveRecord::Migration[7.1]
  def change
    add_column :tasks, :pinned, :boolean, default: false, null: false
    # PostgreSQL 11+ では NOT NULL + DEFAULT の追加は即時完了
  end
end

カラムリネームは段階的に行う

rename_column は既存コードが旧名前を参照している間にエラーを起こす。

# Step 1: 新カラムを追加し、両方に書き込む
add_column :tasks, :title_v2, :string
# モデルで before_save { self.title_v2 = title } を追加
 
# Step 2: 既存データをバックフィル
Task.find_in_batches(batch_size: 1000) do |batch|
  Task.where(id: batch.map(&:id)).update_all("title_v2 = title")
end
 
# Step 3: 読み取りを title_v2 に切り替えてデプロイ
 
# Step 4: 旧カラムを削除
remove_column :tasks, :title, :string

大テーブルへのインデックスはCONCURRENTLYで

class AddIndexTasksOnDueDate < ActiveRecord::Migration[7.1]
  disable_ddl_transaction!  # CONCURRENTLY に必要
 
  def change
    add_index :tasks, :due_date,
              algorithm: :concurrently,
              name: "index_tasks_on_due_date"
  end
end

WARNING

strong_migrations を導入する

strong_migrations gem を入れると危険なマイグレーション（デフォルトなしNOT NULL追加、rename_column 等）を検出して警告してくれる。本番DBを持つすべてのRailsプロジェクトに必須だ。

gem 'strong_migrations'

パフォーマンスモニタリング

Performance Insightsの主要待機イベント

待機イベント意味と対処
io/datafilereadディスクI/O多発 → インデックス不足を疑う
Lock:tuple行ロック競合 → トランザクション設計を見直す
Client:ClientReadアプリ側が遅い → ネットワークかアプリのボトルネック
CPUソート・集計が多い → クエリ最適化かスペックアップ

Rakeタスクでスロークエリを定期確認

# lib/tasks/db_stats.rake
namespace :db do
  desc "スロークエリTOP10を表示"
  task slow_queries: :environment do
    rows = ActiveRecord::Base.connection.execute(<<~SQL)
      SELECT LEFT(query, 100) AS q, calls,
             ROUND(mean_exec_time::numeric, 2) AS avg_ms
      FROM pg_stat_statements
      ORDER BY mean_exec_time DESC LIMIT 10;
    SQL
    rows.each { |r| puts "#{r['avg_ms']}ms (#{r['calls']}calls): #{r['q']}" }
  end
end

結果

インデックス、クエリ最適化、RDS Proxy適用の結果：

Before:  2341ms（Full Table Scan + N+1クエリ）
After:     43ms（インデックス + eager_load + Proxy）
改善倍率: 約54倍

しかし、ユーザーが10万人を超えたとき、次の問題が現れ始めた。

「読み取りクエリが多すぎて、1台のRDSではさばけなくなってきた。」

次の章では、データベースの水平スケーリング——レプリケーションとシャーディングを学ぶ。

INFO

この章のキーポイント

  • 1NF→2NF→3NF を段階的に適用し、冗長性と更新異常を排除する
  • B-treeインデックスはO(log n)の検索を実現するが、書き込みコストとのトレードオフがある
  • EXPLAIN ANALYZE で実行計画と実測値を比較し、pg_stat_user_indexes で不要インデックスを整理する
  • カラムリネーム・大テーブルへのインデックス追加は段階デプロイ＋ CONCURRENTLY で無停止にする
  • RDS ProxyはECSコンテナ増加時のコネクション爆発を防ぐ必須コンポーネントだ
  • pg_stat_statements + Performance Insightsでボトルネックを継続的に監視する
前の章ロードバランシング — トラフィックを分散する
次の章データベーススケーリング — レプリケーションとシャーディング