12 データベース設計 - ソーシャルメディアプラットフォーム

GitHub Repository

大規模ソーシャルメディアプラットフォームを想定したデータベース設計課題です。実践的な DB 設計手法、正規化、インデックス設計、パフォーマンス最適化など、実務で必要なスキルを包括的に学習します。

この演習で学べること

データベース正規化（第 3 正規形）と戦略的非正規化
インデックス設計とパフォーマンスチューニング
パーティショニングによる大規模データ管理
トリガーとストアドプロシージャの活用
スケーラビリティとセキュリティ対策

演習の目標

100 万人規模のソーシャルメディアプラットフォームに対応できるデータベースを設計します。パフォーマンス、拡張性、セキュリティを考慮した実用的な DB 設計スキルを身につけます。

前提条件

PostgreSQL 15.0 以上
SQL 基本文法の理解
データベース設計の基礎知識
正規化理論の基本概念
インデックスの基本理解

クイックスタート

# 1. ディレクトリに移動
cd repos/12

# 2. データベース作成
createdb -U postgres social_media

# 3. 拡張機能の有効化
psql -U postgres -d social_media -c "CREATE EXTENSION IF NOT EXISTS pg_stat_statements;"
psql -U postgres -d social_media -c "CREATE EXTENSION IF NOT EXISTS pg_trgm;"

# 4. スキーマの適用
psql -U postgres -d social_media -f schema.sql

# 5. サンプルデータの投入
psql -U postgres -d social_media -f sample-data.sql

# 6. パフォーマンステストの実行
psql -U postgres -d social_media -f performance-tests.sql

プロジェクト構成

/
├── schema.sql              # DDL（テーブル定義、インデックス、トリガー）
├── sample-data.sql         # テストデータ投入スクリプト
├── sample-queries.sql      # サンプルクエリ集
├── performance-tests.sql   # パフォーマンステスト
├── migrations/             # マイグレーションスクリプト
├── docs/                   # 設計ドキュメント
│   ├── er-diagram.md       # ER図
│   ├── indexing-strategy.md # インデックス戦略
│   └── scaling-plan.md     # スケーリング計画
└── README.md              # このファイル

想定規模

項目	規模	増加率
ユーザー数	100 万人	月 10 万増
投稿数	1000 万件	日 10 万増
コメント数	5000 万件	日 50 万増
フォロー関係	5000 万件	日 30 万増

技術仕様

データベース: PostgreSQL 15+
文字セット: UTF-8
設計手法: 正規化（第 3 正規形）+ 戦略的非正規化
パーティショニング: 時系列データの月次分割
インデックス: B-tree, GIN, 部分インデックス
全文検索: PostgreSQL 標準の日本語対応

開発の進め方

ステップ 1: 基本的なテーブル設計

ユーザー、投稿、フォロー関係の基本スキーマを作成します。

-- ユーザーテーブル
CREATE TABLE users (
    user_id BIGSERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    display_name VARCHAR(100),
    bio TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- 投稿テーブル
CREATE TABLE posts (
    post_id BIGSERIAL PRIMARY KEY,
    user_id BIGINT REFERENCES users(user_id),
    content TEXT NOT NULL,
    visibility VARCHAR(20) DEFAULT 'public',
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

ステップ 2: インデックス設計

主要なクエリパターンに対応するインデックスを作成します。

-- 基本インデックス
CREATE INDEX idx_posts_user_created ON posts(user_id, created_at DESC);
CREATE INDEX idx_posts_created ON posts(created_at DESC);

-- 部分インデックス（条件付き）
CREATE INDEX idx_posts_public ON posts(created_at DESC)
WHERE visibility = 'public';

ステップ 3: パーティショニングの実装

大量データを効率的に管理するためのパーティション設計を行います。

-- アクティビティログのパーティショニング
CREATE TABLE activity_logs (
    log_id BIGSERIAL,
    user_id BIGINT,
    action VARCHAR(50),
    created_at TIMESTAMP WITH TIME ZONE
) PARTITION BY RANGE (created_at);

-- 月次パーティションの作成
CREATE TABLE activity_logs_2024_01 PARTITION OF activity_logs
    FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');

データベース設計

1. 主要テーブル構成

ユーザー関連

users - ユーザー基本情報
user_profiles - 拡張プロフィール情報
user_sessions - セッション管理

投稿関連

posts - 投稿データ
post_media - メディア添付ファイル
comments - コメント（階層構造対応）

インタラクション

likes - いいね機能
comment_likes - コメントいいね
follows - フォロー関係
blocks - ブロック機能

検索・分類

hashtags - ハッシュタグマスター
post_hashtags - 投稿ハッシュタグ関連

メッセージング

chat_rooms - チャットルーム
chat_room_members - ルームメンバー
messages - メッセージ

システム機能

notifications - 通知管理
reports - 報告・モデレーション
activity_logs - アクティビティログ（パーティション）

2. 正規化戦略

第 3 正規形の適用

-- 正規化例：投稿とメディアの分離
CREATE TABLE posts (
    post_id BIGINT PRIMARY KEY,
    user_id BIGINT REFERENCES users(user_id),
    content TEXT,
    created_at TIMESTAMP WITH TIME ZONE
);

CREATE TABLE post_media (
    media_id BIGINT PRIMARY KEY,
    post_id BIGINT REFERENCES posts(post_id),
    media_url TEXT,
    media_type VARCHAR(20)
);

戦略的非正規化

-- パフォーマンス向上のための非正規化
ALTER TABLE posts ADD COLUMN likes_count INTEGER DEFAULT 0;
ALTER TABLE posts ADD COLUMN comments_count INTEGER DEFAULT 0;
ALTER TABLE users ADD COLUMN followers_count INTEGER DEFAULT 0;

3. インデックス設計

基本インデックス

-- 主要検索パターンに対応
CREATE INDEX idx_posts_user_created ON posts(user_id, created_at DESC);
CREATE INDEX idx_follows_follower ON follows(follower_id, created_at DESC);
CREATE INDEX idx_likes_post_created ON likes(post_id, created_at DESC);

複合インデックス

-- 複数条件での検索最適化
CREATE INDEX idx_posts_created_visibility ON posts(created_at DESC, visibility);
CREATE INDEX idx_notifications_unread ON notifications(user_id, is_read) WHERE is_read = FALSE;

全文検索インデックス

-- PostgreSQL GINインデックス
CREATE INDEX idx_posts_content_search ON posts USING gin(to_tsvector('japanese', content));

4. パーティショニング

時系列パーティション（ログテーブル）

CREATE TABLE activity_logs (
    log_id BIGINT,
    user_id BIGINT,
    action VARCHAR(50),
    created_at TIMESTAMP WITH TIME ZONE
) PARTITION BY RANGE (created_at);

-- 月次パーティション
CREATE TABLE activity_logs_2024_01 PARTITION OF activity_logs
    FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');

必須要件

第 3 正規形に基づいた基本スキーマ設計
適切なインデックスの設計と実装
パーティショニングによる大規模データ対応
トリガーによる自動カウント更新
全文検索（日本語対応）の実装
セキュリティを考慮した権限設定

追加課題（オプション）

マテリアライズドビューの活用
ストアドプロシージャによる複雑な処理
レプリケーション構成の設計
Redis 連携によるキャッシュ戦略
GraphQL スキーマの生成
イベントソーシングの実装
包括的なマイグレーションスクリプト

主要機能の実装

1. ユーザータイムライン

フォローユーザーの投稿取得

WITH user_following AS (
    SELECT following_id
    FROM follows
    WHERE follower_id = $1 AND status = 'active'
)
SELECT
    p.post_id,
    p.content,
    p.created_at,
    u.username,
    u.display_name,
    EXISTS(SELECT 1 FROM likes l WHERE l.post_id = p.post_id AND l.user_id = $1) as is_liked
FROM posts p
JOIN users u ON p.user_id = u.user_id
WHERE p.user_id IN (SELECT following_id FROM user_following)
    AND p.visibility IN ('public', 'followers')
ORDER BY p.created_at DESC
LIMIT 20;

2. 人気投稿アルゴリズム

エンゲージメントスコア計算

SELECT
    p.*,
    (p.likes_count * 1.0 + p.comments_count * 2.0 + p.reposts_count * 1.5) *
    EXP(-EXTRACT(EPOCH FROM (CURRENT_TIMESTAMP - p.created_at)) / 86400.0) as weighted_score
FROM posts p
WHERE p.created_at >= CURRENT_TIMESTAMP - INTERVAL '7 days'
ORDER BY weighted_score DESC;

3. 検索機能

全文検索（日本語対応）

SELECT
    p.post_id,
    p.content,
    ts_rank(to_tsvector('japanese', p.content), plainto_tsquery('japanese', $1)) as rank
FROM posts p
WHERE to_tsvector('japanese', p.content) @@ plainto_tsquery('japanese', $1)
ORDER BY rank DESC;

ハッシュタグ検索

SELECT p.*
FROM posts p
JOIN post_hashtags ph ON p.post_id = ph.post_id
JOIN hashtags h ON ph.hashtag_id = h.hashtag_id
WHERE h.normalized_name = LOWER($1);

4. リアルタイム統計

トリガーによる自動カウント更新

CREATE OR REPLACE FUNCTION update_post_counts()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        UPDATE posts SET likes_count = likes_count + 1 WHERE post_id = NEW.post_id;
    ELSIF TG_OP = 'DELETE' THEN
        UPDATE posts SET likes_count = likes_count - 1 WHERE post_id = OLD.post_id;
    END IF;
    RETURN COALESCE(NEW, OLD);
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_likes_count
    AFTER INSERT OR DELETE ON likes
    FOR EACH ROW
    EXECUTE FUNCTION update_post_counts();

パフォーマンス最適化

1. クエリ最適化

インデックスの効果的な使用

-- 良い例：インデックスを活用
EXPLAIN ANALYZE
SELECT * FROM posts
WHERE user_id = 123
ORDER BY created_at DESC
LIMIT 20;

-- 悪い例：フルテーブルスキャン
EXPLAIN ANALYZE
SELECT * FROM posts
WHERE EXTRACT(YEAR FROM created_at) = 2024;

EXPLAIN ANALYZE の活用

-- 実行計画の確認
EXPLAIN (ANALYZE, BUFFERS)
SELECT p.*, u.username
FROM posts p
JOIN users u ON p.user_id = u.user_id
WHERE p.created_at >= '2024-01-01';

2. 統計情報の更新

定期的な ANALYZE 実行

-- 統計情報更新
ANALYZE users;
ANALYZE posts;
ANALYZE follows;

-- 自動VACUUM設定確認
SELECT schemaname, tablename, last_vacuum, last_autovacuum, last_analyze
FROM pg_stat_user_tables;

3. 接続プール

pgBouncer の活用

# pgbouncer.ini
[databases]
social_media = host=localhost port=5432 dbname=social_media

[pgbouncer]
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 20

採点基準

項目	配点	評価内容
スキーマ設計	40 点	正規化、データ型、制約の適切性
インデックス	30 点	クエリパターンに応じた最適化
パフォーマンス	20 点	パーティション、トリガー、最適化
セキュリティ	10 点	権限管理、暗号化、インジェクション対策

トラブルシューティング

インデックスが使われない

問題: EXPLAIN で確認すると Seq Scan になる 解決方法:

-- 統計情報の更新
ANALYZE table_name;

-- インデックスのヒント
SET enable_seqscan = off; -- テスト用

-- インデックスの再構築
REINDEX INDEX index_name;

パーティションエラー

問題: パーティションキーがない 解決方法:

パーティションキーは必須
範囲が重複しないよう注意
自動作成スクリプトを用意

パフォーマンス劣化

問題: クエリが遅い 解決方法:

-- 実行計画の確認
EXPLAIN (ANALYZE, BUFFERS) SELECT ...;

-- 統計情報の確認
SELECT * FROM pg_stat_user_tables;

-- VACUUMの実行
VACUUM ANALYZE table_name;

参考資料

スケーラビリティ対応

1. 読み取りレプリカ

マスター/スレーブ構成

-- 読み取り専用クエリをスレーブに振り分け
-- アプリケーション側でのルーティング実装が必要
SELECT p.* FROM posts p
WHERE p.visibility = 'public'  -- READ REPLICA
ORDER BY p.created_at DESC;

INSERT INTO posts (user_id, content)  -- MASTER
VALUES (123, 'New post content');

2. パーティショニング戦略

水平パーティション（シャーディング）

-- ユーザーIDによるハッシュ分割
CREATE TABLE posts_shard_1 (
    CHECK (user_id % 4 = 0)
) INHERITS (posts);

CREATE TABLE posts_shard_2 (
    CHECK (user_id % 4 = 1)
) INHERITS (posts);

3. キャッシュ戦略

Redis 活用例

# Python例：タイムライン
import redis
import json

def get_user_timeline(user_id):
    cache_key = f"timeline:{user_id}"
    cached = redis_client.get(cache_key)

    if cached:
        return json.loads(cached)

    # DBから取得
    timeline = fetch_timeline_from_db(user_id)
    redis_client.setex(cache_key, 300, json.dumps(timeline))
    return timeline

セキュリティ対策

1. 権限管理

ロールベースアクセス制御

-- アプリケーション用ロール
CREATE ROLE social_media_app;
GRANT SELECT, INSERT, UPDATE, DELETE ON users, posts, likes TO social_media_app;

-- 分析用読み取り専用ロール
CREATE ROLE analytics_readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO analytics_readonly;

2. データ暗号化

機密データの暗号化

-- 暗号化拡張
CREATE EXTENSION IF NOT EXISTS pgcrypto;

-- パスワードハッシュ化
UPDATE users SET password_hash = crypt(password_plain, gen_salt('bf', 12));

-- 復号化
SELECT * FROM users WHERE password_hash = crypt($1, password_hash);

3. SQL インジェクション対策

パラメータ化クエリの使用

-- 安全：パラメータ化
PREPARE get_user_posts (BIGINT) AS
SELECT * FROM posts WHERE user_id = $1;

-- 危険：文字列結合
-- SELECT * FROM posts WHERE user_id = ' + user_input + ';

モニタリング

1. パフォーマンスメトリクス

重要な監視項目

-- アクティブ接続数
SELECT count(*) FROM pg_stat_activity WHERE state = 'active';

-- 長時間実行クエリ
SELECT pid, now() - pg_stat_activity.query_start AS duration, query
FROM pg_stat_activity
WHERE (now() - pg_stat_activity.query_start) > interval '5 minutes';

-- データベースサイズ
SELECT pg_size_pretty(pg_database_size('social_media'));

2. スロークエリログ

postgresql.conf の設定

# スロークエリログ有効化
log_min_duration_statement = 1000  # 1秒以上のクエリをログ
log_statement = 'all'
log_duration = on

3. 統計情報

pg_stat_statements

-- 最も時間のかかるクエリ
SELECT query, calls, total_time, mean_time
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 10;

バックアップ・復旧

1. 論理バックアップ

pg_dump の活用

# 全体バックアップ
pg_dump -U postgres -h localhost social_media > backup_$(date +%Y%m%d).sql

# 特定テーブルのみ
pg_dump -U postgres -t users -t posts social_media > users_posts_backup.sql

2. 物理バックアップ

pg_basebackup によるベースバックアップ

# ベースバックアップ作成
pg_basebackup -h localhost -D /backup/base -U postgres -P -W

# WALアーカイブ設定
# postgresql.conf
archive_mode = on
archive_command = 'cp %p /backup/wal/%f'

3. ポイントインタイム復旧

WAL を使用した復旧

# 特定時点への復旧
pg_ctl stop -D /var/lib/postgresql/data
rm -rf /var/lib/postgresql/data/*
cp -R /backup/base/* /var/lib/postgresql/data/

# recovery.conf作成
echo "restore_command = 'cp /backup/wal/%f %p'" > /var/lib/postgresql/data/recovery.conf
echo "recovery_target_time = '2024-03-15 14:30:00'" >> /var/lib/postgresql/data/recovery.conf

pg_ctl start -D /var/lib/postgresql/data

今後の拡張計画

1. 機能拡張

ライブストリーミング機能
AI 推薦アルゴリズム
多言語対応
ジオロケーション検索強化

2. 技術的改善

時系列データベース（TimescaleDB）導入
GraphQL API 対応
マイクロサービス分割
イベントソーシング実装

3. インフラ最適化

Kubernetes 環境への移行
クラウドネイティブ構成
CDN 統合
エッジコンピューティング

トラブルシューティング

よくある問題と解決策

1. パフォーマンス問題

-- 実行計画の確認
EXPLAIN (ANALYZE, BUFFERS) SELECT ...;

-- インデックス使用状況
SELECT * FROM pg_stat_user_indexes WHERE idx_scan = 0;

-- テーブル統計情報の更新
ANALYZE table_name;

2. デッドロック

-- デッドロック監視
SELECT * FROM pg_stat_database WHERE datname = 'social_media';

-- ロック状況確認
SELECT * FROM pg_locks WHERE NOT granted;

3. 接続数枯渇

-- 現在の接続数確認
SELECT count(*) FROM pg_stat_activity;

-- 設定確認
SHOW max_connections;

頑張って実用的なデータベース設計を完成させてください！

課題提出方法

このリポジトリをフォーク
submission/[あなたの名前] ブランチを作成
データベース設計を実装
プルリクエストを作成
自動採点の結果を確認
必要に応じて修正

詳細な提出手順

1. リポジトリのフォーク

# GitHub で Fork ボタンをクリック後
git clone https://github.com/[あなたのユーザー名]/exercise-db-aws.git
cd exercise-db-aws

2. ブランチ作成

git checkout -b submission/taro-yamada

3. 実装とコミット

# 基本的なスキーマ設計
git add schema.sql
git commit -m "feat: ユーザー、投稿、フォロー関係の基本スキーマを作成"

# インデックス設計
git add schema.sql
git commit -m "feat: パフォーマンス最適化のためのインデックスを追加"

# パーティショニング実装
git add schema.sql
git commit -m "feat: アクティビティログの月次パーティショニングを実装"

# トリガーとストアドプロシージャ
git add schema.sql
git commit -m "feat: 統計情報自動更新のトリガーを実装"

# サンプルクエリ集
git add sample-queries.sql
git commit -m "docs: タイムライン取得等の主要クエリサンプルを追加"

4. プッシュと PR 作成

git push origin submission/taro-yamada

GitHub でプルリクエストを作成：

タイトル: [提出] 演習12: データベース設計 - 山田太郎
本文: 設計の概要、正規化方針、インデックス戦略を記載

開発のヒント

正規化のポイント

繰り返しグループを別テーブルに
部分関数従属を排除
推移的関数従属を排除
必要に応じて戦略的に非正規化

インデックス設計

-- 複合インデックスの順序が重要
CREATE INDEX idx_posts_user_created
ON posts(user_id, created_at DESC);

-- 部分インデックスで効率化
CREATE INDEX idx_active_users
ON users(created_at)
WHERE is_active = true;

パーティショニング戦略

時系列データは範囲パーティション
ユーザーデータはハッシュパーティション
自動パーティション作成スクリプトを用意

注意事項

設計の基本：
- 第 3 正規形を基本とし、必要に応じて戦略的に非正規化
- 外部キー制約を適切に設定
- データ型は適切なサイズを選択（BIGINT の使い分け等）
- NULL 許可は慎重に判断
パフォーマンス対策：
- 主要なクエリパターンを想定したインデックス設計
- 部分インデックスやカバリングインデックスの活用
- EXPLAIN ANALYZE による実行計画の確認
- 統計情報の適切な更新戦略
スケーラビリティ：
- 大量データを想定したパーティショニング設計
- 読み取り負荷分散を考慮した設計
- キャッシュ戦略の検討（Redis との連携等）
- 将来的なシャーディングを見据えた設計
セキュリティ：
- 適切な権限設定（GRANT/REVOKE）
- センシティブデータの暗号化
- SQL インジェクション対策
- 監査ログの実装
追加の評価ポイント：
- ER 図やドキュメントの充実度
- マイグレーションスクリプトの提供
- バックアップ・復旧戦略の文書化
- 全文検索の実装（日本語対応）
- マテリアライズドビューの活用

Name		Name	Last commit message	Last commit date
Latest commit History 1 Commit
.github		.github
schema-before		schema-before
.gitignore		.gitignore
README.md		README.md
sample-queries.sql		sample-queries.sql
schema.sql		schema.sql

studying-tech/exercise-db-aws

Folders and files

Latest commit

History

Repository files navigation

12 データベース設計 - ソーシャルメディアプラットフォーム

この演習で学べること

演習の目標

前提条件

クイックスタート

プロジェクト構成

想定規模

技術仕様

開発の進め方

ステップ 1: 基本的なテーブル設計

ステップ 2: インデックス設計

ステップ 3: パーティショニングの実装

データベース設計

1. 主要テーブル構成

ユーザー関連

投稿関連

インタラクション

検索・分類

メッセージング

システム機能

2. 正規化戦略

第 3 正規形の適用

戦略的非正規化

3. インデックス設計

基本インデックス

複合インデックス

全文検索インデックス

4. パーティショニング

時系列パーティション（ログテーブル）

必須要件

追加課題（オプション）

主要機能の実装

1. ユーザータイムライン

フォローユーザーの投稿取得

2. 人気投稿アルゴリズム

エンゲージメントスコア計算

3. 検索機能

全文検索（日本語対応）

ハッシュタグ検索

4. リアルタイム統計

トリガーによる自動カウント更新

パフォーマンス最適化

1. クエリ最適化

インデックスの効果的な使用

EXPLAIN ANALYZE の活用

2. 統計情報の更新

定期的な ANALYZE 実行

3. 接続プール

pgBouncer の活用

採点基準

トラブルシューティング

インデックスが使われない

パーティションエラー

パフォーマンス劣化

参考資料

スケーラビリティ対応

1. 読み取りレプリカ

マスター/スレーブ構成

2. パーティショニング戦略

水平パーティション（シャーディング）

3. キャッシュ戦略

Redis 活用例

セキュリティ対策

1. 権限管理

ロールベースアクセス制御

2. データ暗号化

機密データの暗号化

3. SQL インジェクション対策

パラメータ化クエリの使用

モニタリング

1. パフォーマンスメトリクス

重要な監視項目

2. スロークエリログ

postgresql.conf の設定

3. 統計情報

Packages