主要エンティティ: users, posts, follows, likes, comments, notifications を中心に設計しよう。N

ER図（主要エンティティ）

[users] ──1:N──< [posts] >──1:N──< [comments]
   │                │                    │
   │                └──1:N──< [likes]    │
   │                                     │
   └──N:M（follows）──> [users]          │
   │                                     │
   └──1:N──< [notifications]             │
                                         │
   [users] ──────────────────────────────┘

テーブル定義:

-- ユーザー（3NF）
CREATE TABLE users (
  id BIGSERIAL PRIMARY KEY,
  username VARCHAR(30) NOT NULL UNIQUE,
  email VARCHAR(255) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  display_name VARCHAR(100) NOT NULL,
  bio TEXT,
  avatar_url VARCHAR(500),
  is_verified BOOLEAN NOT NULL DEFAULT false,
  status VARCHAR(20) NOT NULL DEFAULT 'active'
    CHECK (status IN ('active', 'suspended', 'deleted')),
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- 投稿（3NF、非正規化: like_count, comment_count）
CREATE TABLE posts (
  id BIGSERIAL PRIMARY KEY,
  user_id BIGINT NOT NULL REFERENCES users(id),
  body VARCHAR(280) NOT NULL,
  image_urls TEXT[],  -- 最大4枚
  like_count INT NOT NULL DEFAULT 0,       -- 非正規化: パフォーマンス
  comment_count INT NOT NULL DEFAULT 0,    -- 非正規化: パフォーマンス
  is_deleted BOOLEAN NOT NULL DEFAULT false,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- フォロー関係
CREATE TABLE follows (
  follower_id BIGINT NOT NULL REFERENCES users(id),
  following_id BIGINT NOT NULL REFERENCES users(id),
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  PRIMARY KEY (follower_id, following_id),
  CHECK (follower_id != following_id)  -- 自分自身のフォローを防止
);

-- いいね
CREATE TABLE likes (
  user_id BIGINT NOT NULL REFERENCES users(id),
  post_id BIGINT NOT NULL REFERENCES posts(id),
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  PRIMARY KEY (user_id, post_id)
);

-- コメント
CREATE TABLE comments (
  id BIGSERIAL PRIMARY KEY,
  post_id BIGINT NOT NULL REFERENCES posts(id),
  user_id BIGINT NOT NULL REFERENCES users(id),
  body VARCHAR(500) NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- 通知
CREATE TABLE notifications (
  id BIGSERIAL PRIMARY KEY,
  user_id BIGINT NOT NULL REFERENCES users(id),  -- 通知先
  actor_id BIGINT NOT NULL REFERENCES users(id),  -- 通知元
  type VARCHAR(20) NOT NULL
    CHECK (type IN ('follow', 'like', 'comment', 'mention')),
  post_id BIGINT REFERENCES posts(id),
  is_read BOOLEAN NOT NULL DEFAULT false,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- ユーザー統計（非正規化テーブル）
CREATE TABLE user_stats (
  user_id BIGINT PRIMARY KEY REFERENCES users(id),
  follower_count INT NOT NULL DEFAULT 0,
  following_count INT NOT NULL DEFAULT 0,
  post_count INT NOT NULL DEFAULT 0
);

非正規化の理由:

• posts.like_count, posts.comment_count: 毎回COUNTクエリを実行するのはパフォーマンス上問題。いいね/コメント時にインクリメント。
• user_stats: フォロワー数・フォロー数はプロフィール表示で毎回必要。COUNT(*)を避ける。

タイムライン表示、ユーザー検索、通知取得の3つの主要パターンに対応するインデックスを考えよう。

-- タイムライン: フォロー中ユーザーの投稿を取得
CREATE INDEX idx_posts_user_created ON posts(user_id, created_at DESC)
  WHERE is_deleted = false;

-- フォロー一覧
CREATE INDEX idx_follows_follower ON follows(follower_id, created_at DESC);
CREATE INDEX idx_follows_following ON follows(following_id, created_at DESC);

-- いいね確認（特定ユーザーが特定投稿にいいねしたか）
-- PKで対応: PRIMARY KEY (user_id, post_id)

-- コメント取得
CREATE INDEX idx_comments_post_created ON comments(post_id, created_at DESC);

-- 通知取得
CREATE INDEX idx_notifications_user_unread ON notifications(user_id, created_at DESC)
  WHERE is_read = false;
CREATE INDEX idx_notifications_user_all ON notifications(user_id, created_at DESC);

-- ユーザー検索
CREATE INDEX idx_users_username_trgm ON users
  USING GIN(username gin_trgm_ops);  -- 部分一致検索
CREATE INDEX idx_users_display_name_trgm ON users
  USING GIN(display_name gin_trgm_ops);

-- 投稿の全文検索
CREATE INDEX idx_posts_body_search ON posts
  USING GIN(to_tsvector('simple', body));

-- 通知の自動削除（90日）
-- パーティショニング検討（後述）

タイムラインのキャッシュ、セッション管理、リアルタイム通知でRedisの活用を検討しよう。

// 1. タイムラインキャッシュ（Redis List）
class TimelineCache {
  private redis: Redis;
  private readonly MAX_TIMELINE_SIZE = 800;

  // ファンアウト: 投稿時にフォロワーのタイムラインに追加
  async fanOutPost(postId: string, followerIds: string[]): Promise<void> {
    const pipeline = this.redis.pipeline();
    for (const followerId of followerIds) {
      pipeline.lpush(`timeline:${followerId}`, postId);
      pipeline.ltrim(`timeline:${followerId}`, 0, this.MAX_TIMELINE_SIZE - 1);
    }
    await pipeline.exec();
  }

  // タイムライン取得（カーソルベース）
  async getTimeline(userId: string, offset: number = 0, limit: number = 20): Promise<string[]> {
    return this.redis.lrange(`timeline:${userId}`, offset, offset + limit - 1);
  }
}

// 2. セッション管理（Redis Hash）
class SessionStore {
  async create(sessionId: string, userId: string): Promise<void> {
    await this.redis.hset(`session:${sessionId}`, {
      userId,
      createdAt: new Date().toISOString(),
    });
    await this.redis.expire(`session:${sessionId}`, 86400); // 24時間
  }
}

// 3. いいね数のリアルタイムカウンター（Redis）
class LikeCounter {
  async increment(postId: string): Promise<number> {
    return this.redis.incr(`likes:${postId}`);
  }

  async decrement(postId: string): Promise<number> {
    return this.redis.decr(`likes:${postId}`);
  }

  // 定期的にRDBに同期
  async syncToDatabase(): Promise<void> {
    const keys = await this.redis.keys('likes:*');
    for (const key of keys) {
      const postId = key.split(':')[1];
      const count = await this.redis.get(key);
      await db.query(
        'UPDATE posts SET like_count = $1 WHERE id = $2',
        [parseInt(count ?? '0'), postId]
      );
    }
  }
}

// 4. リアルタイム通知（Redis Pub/Sub）
class NotificationService {
  async notify(userId: string, notification: NotificationData): Promise<void> {
    // RDBに永続化
    await db.query(
      'INSERT INTO notifications (user_id, actor_id, type, post_id) VALUES ($1, $2, $3, $4)',
      [userId, notification.actorId, notification.type, notification.postId]
    );

    // リアルタイム配信
    await this.redis.publish(`notifications:${userId}`, JSON.stringify(notification));

    // 未読カウンター更新
    await this.redis.incr(`unread:${userId}`);
  }
}

// 5. 投稿の全文検索（Elasticsearch検討）
// 規模が大きい場合はElasticsearchに投稿データを同期

データストアの使い分け:

いいね処理はDB トランザクション + Redisの非同期更新。通知テーブルは Range パーティショニングで月ごとに分割。

1. いいね処理のトランザクション

async function likePost(userId: number, postId: number): Promise<void> {
  // RDBトランザクション: いいね作成 + カウンター更新
  await prisma.$transaction(async (tx) => {
    // いいねレコード作成（重複はPK制約で防止）
    await tx.like.create({
      data: { userId, postId },
    });

    // カウンター更新
    await tx.post.update({
      where: { id: postId },
      data: { likeCount: { increment: 1 } },
    });
  });

  // Redis: リアルタイムカウンター更新（非同期、失敗してもRDBが正）
  await redis.incr(`likes:${postId}`).catch(console.error);

  // 通知送信（非同期）
  const post = await prisma.post.findUnique({ where: { id: postId } });
  if (post && post.userId !== userId) {
    await notificationService.notify(post.userId.toString(), {
      type: 'like',
      actorId: userId,
      postId,
    });
  }
}

2. 通知テーブルのパーティショニングマイグレーション

-- Phase 1: 新しいパーティションテーブルを作成
CREATE TABLE notifications_partitioned (
  id BIGSERIAL,
  user_id BIGINT NOT NULL,
  actor_id BIGINT NOT NULL,
  type VARCHAR(20) NOT NULL,
  post_id BIGINT,
  is_read BOOLEAN NOT NULL DEFAULT false,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (created_at);

-- 月別パーティション作成
CREATE TABLE notifications_2024_01 PARTITION OF notifications_partitioned
  FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');
-- ... 各月のパーティション

-- Phase 2: データ移行（バッチ）
INSERT INTO notifications_partitioned
SELECT * FROM notifications
WHERE created_at >= '2024-01-01'
ORDER BY id
LIMIT 10000;
-- バッチを繰り返す

-- Phase 3: アプリ切り替え（Dual Write → 新テーブルのみ）

-- Phase 4: 旧テーブルをリネーム保持
ALTER TABLE notifications RENAME TO notifications_old;
ALTER TABLE notifications_partitioned RENAME TO notifications;

-- 90日以上前のパーティションを自動削除
-- cron ジョブで毎月実行
DROP TABLE IF EXISTS notifications_2023_10;