Quick Start: Basic Schema in 5 Minutes
Want to build a chat app like Telegram or Messenger? Don’t rush into overcomplicating your schema. For starter projects using PostgreSQL or MySQL, these 4 core tables are enough to run smoothly.
-- User table
CREATE TABLE users (
id SERIAL PRIMARY KEY,
username VARCHAR(50) UNIQUE NOT NULL,
avatar_url TEXT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- Conversation management (Used for both 1-1 and group chats)
CREATE TABLE conversations (
id SERIAL PRIMARY KEY,
is_group BOOLEAN DEFAULT FALSE,
title VARCHAR(255),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- Track members in each conversation
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)
);
-- Message storage
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
);This structure handles message sending and distinguishes between private and group chats well. However, once the system hits the 1 million message mark, performance issues will start to surface.
Solving Tough Problems at Scale
1. 1-1 and Group Chats: Don’t Separate Tables
A common mistake for junior developers is separating private_messages and group_messages tables. This makes global message search a nightmare. Instead, combine them into the messages table and use the participants table to control access.
The participants table acts as the “control station.” Here, you can easily add columns like is_admin, is_muted, or last_read_at without affecting the core message logic.
2. Managing Online Status
Never update online status directly in SQL every time a user performs an action. If you have 10,000 concurrent users, the massive volume of UPDATE queries will crash your database instantly.
The optimal solution is using Redis with a Heartbeat mechanism. When a client connects via Socket, run the command: SET user:1:status online EX 60. Every 30 seconds, the client sends a “ping” signal to renew the key. If Redis can’t find the key, the system knows the user is offline.
3. Optimizing Chat History Queries
As the messages table grows, SELECT statements with ORDER BY will gradually slow down. To handle this, you need to create an Index for the (conversation_id, created_at) pair.
CREATE INDEX idx_messages_conversation_time ON messages (conversation_id, created_at DESC);In practice, a proper index can reduce query time from 2-3 seconds down to a few milliseconds. However, remember that the more indexes you have, the slower the INSERT speed becomes. You need to balance the read and write experience.
Advanced: When SQL Starts to Overload
If your app is lucky enough to reach the scale of Zalo or Slack, a single SQL database will hit a bottleneck. This is when you need heavier artillery.
Switching to NoSQL for Messages
Messages are typically write-heavy but rarely updated. MongoDB or Cassandra are excellent choices for this problem. The document structure allows you to store dozens of reactions (hearts, icons) directly within the message without performing complex JOINs.
Database Sharding
With PostgreSQL, you can apply Sharding to split data across multiple servers. For example: Server A stores conversations from ID 1 to 1 million, and Server B stores the rest. The system will handle the load better, but in return, operations and backups will become significantly more complex.
Practical Tips for Backend Developers
Handling Read Receipts
Don’t create a separate table for every “view,” as the number of records will grow exponentially. A small tip: just store last_read_message_id in the participants table. To calculate the number of unread messages, simply count the messages with an ID greater than the last one the user viewed.
Paginating with Cursor-based Pagination
Don’t use OFFSET when paginating through old messages. OFFSET forces the database to scan through all previous rows, which is extremely resource-intensive. Use a Cursor instead: the client sends the ID of the oldest message it has, and the server fetches 20 messages with an ID smaller than that.
-- The standard way:
SELECT * FROM messages
WHERE conversation_id = 1 AND id < 12345
ORDER BY id DESC
LIMIT 20;Designing a chat database is a balancing act between write speed and retrieval performance. Hopefully, these real-world experiences will help you feel more confident when building your own system.
