Posted inDatabase

チャットアプリのデータベース設計：ゼロから100万ユーザー規模のスケールまで

Database tutorial - IT technology blog
Database tutorial - IT technology blog

クイックスタート：5分でわかる基本スキーマ

TelegramやMessengerのようなチャットアプリを作りたいですか？最初から複雑なスキーマを設計する必要はありません。PostgreSQLやMySQLを使用した初期プロジェクトなら、以下の4つのコアテーブルだけでスムーズに運用できます。

-- ユーザーテーブル
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    avatar_url TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 会話管理（1対1およびグループチャット用）
CREATE TABLE conversations (
    id SERIAL PRIMARY KEY,
    is_group BOOLEAN DEFAULT FALSE,
    title VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 各会話の参加者を追跡
CREATE TABLE participants (
    conversation_id INT REFERENCES conversations(id),
    user_id INT REFERENCES users(id),
    joined_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (conversation_id, user_id)
);

-- メッセージ保存
CREATE TABLE messages (
    id BIGSERIAL PRIMARY KEY,
    conversation_id INT REFERENCES conversations(id),
    sender_id INT REFERENCES users(id),
    content TEXT NOT NULL,
    message_type ENUM('text', 'image', 'file') DEFAULT 'text',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

この構造は、メッセージ送信や個人/グループチャットの区別を適切に処理できます。しかし、システムが100万メッセージに達すると、パフォーマンスの問題が顕在化し始めます。

スケーリング時の難題を解決する

1. 1対1チャットとグループチャット：テーブルを分けない

ジュニアエンジニアが陥りやすい間違いは、private_messagesとgroup_messagesのテーブルを分けてしまうことです。この方法では、グローバル検索（Global Search）機能の実装が悪夢になります。messagesテーブルに統合し、participantsテーブルでアクセス権限を制御しましょう。

participantsテーブルはいわば「コントロールステーション」です。ここで、メッセージのコアロジックに影響を与えることなく、is_admin、is_muted、last_read_atなどのカラムを簡単に追加できます。

2. オンライン状態（Online Status）の管理

ユーザーが操作するたびにSQLのオンライン状態を直接更新するのは絶対に避けてください。1万人のアクティブユーザーが同時に操作した場合、膨大な数のUPDATEクエリが発生し、データベースが即座にダウンしてしまいます。

最適な解決策は、Redisとハートビート（Heartbeat）メカニズムを使用することです。クライアントがSocketに接続した際に、SET user:1:status online EX 60を実行します。クライアントは30秒ごとに「ping」信号を送信してキーの期限を更新します。Redisにキーが見つからない場合、システムはユーザーがオフラインであると判断します。

3. チャット履歴クエリの最適化

メッセージテーブルが肥大化すると、ORDER BYを含むSELECTステートメントは徐々に遅くなります。これを解決するには、(conversation_id, created_at)のペアにインデックス（Index）を貼る必要があります。

CREATE INDEX idx_messages_conversation_time ON messages (conversation_id, created_at DESC);

実際、適切なインデックスを貼ることで、クエリ時間を2〜3秒から数ミリ秒に短縮できます。ただし、インデックスが増えるほどINSERTの速度が低下することを忘れないでください。読み取りと書き込みのバランスを考える必要があります。

応用：SQLが限界に達したとき

アプリケーションがZaloやSlackのような規模に達した場合、単一のSQLデータベースではボトルネックが発生します。ここからは、より強力な武器が必要になります。

メッセージ用にNoSQLへ移行する

メッセージは「書き込みが多く、更新が極めて少ない」という特性があります。MongoDBやCassandraはこのユースケースに最適です。ドキュメント構造により、複雑なJOINを行わずに、メッセージ内に多数のリアクション（ハート、アイコンなど）を保存できます。

データベース・シャーディング（Database Sharding）

PostgreSQLでは、シャーディングを適用してデータを複数のサーバーに分散できます。例えば、サーバーAにはID 1から100万までの会話を、サーバーBにはそれ以降を保存します。システムはより高い負荷に耐えられるようになりますが、運用やバックアップの複雑さは大幅に増します。

バックエンドエンジニアのための実践的なヒント

「既読」状態（Read Receipts）の処理

既読のたびに個別のテーブルレコードを作成しないでください。レコード数が指数関数的に増加してしまいます。コツは、participantsテーブルにlast_read_message_idを保存することです。未読数を計算する必要がある場合は、ユーザーが最後に読んだIDよりも大きいIDを持つメッセージをカウントするだけです。

カーソルベースのパジネーション（Cursor-based Pagination）

古いメッセージを読み込む際にOFFSETを使用しないでください。OFFSETはデータベースにそれまでの全行をスキャンさせるため、リソースを極端に消費します。カーソル（Cursor）を使用しましょう。クライアントが現在持っている最も古いメッセージのIDを送信し、サーバーはそのIDより小さいIDを持つメッセージを20件取得します。

-- 推奨される方法: 
SELECT * FROM messages 
WHERE conversation_id = 1 AND id < 12345 
ORDER BY id DESC 
LIMIT 20;

チャットのデータベース設計は、書き込み速度と検索性能のバランスをどう取るかという問題です。これらの実践的な経験が、独自のシステムを構築する際の自信に繋がれば幸いです。

Share:
Tags: