Why WeChat Stores Billions of Messages in SQLite: The Mobile Database Trade-offs

No Ops on Mobile: The Database Must Manage Itself

On the server side, MySQL and Redis are monitored by ops teams who can scale or restart on failure. Mobile devices lack that luxury. The database must be a library linked into the app bundle — no separate process, no network layer — so every function call writes directly to a local file. SQLite is purpose-built for exactly this model.

ACID Guarantees for Message Integrity

Chat records must never be lost or reordered. SQLite's full ACID transaction support means an INSERT either persists to disk or rolls back automatically; there is no state where the remote side has the message but the local store does not. For pagination across millions of rows, WeChat relies on B+tree indexes. A typical per-session table schema looks like this:

-- Each session gets its own table; table name is the talker's MD5 to avoid special characters in group names
CREATE TABLE Msg_9f3c... (
  msgId      INTEGER PRIMARY KEY,
  talker     TEXT,      -- Counterparty; group chats use xxx@chatroom
  isSend     INTEGER,   -- 0 = received, 1 = sent
  createTime INTEGER,   -- Millisecond timestamp; all sorting depends on it
  type       INTEGER,   -- Text, image, voice, video
  content    TEXT       -- Body; non-text content stored as XML or file path
);

-- "Session + time" is the hottest query pattern; a composite index is mandatory
CREATE INDEX idx_talker_time ON Msg_9f3c... (talker, createTime);

With the composite index, range scans finish in hundreds of milliseconds without a full table scan. Building equivalent indexing and concurrency control from scratch would require re-implementing both from the ground up.

Single File + WAL: The Real Reason It Survives on Mobile

The entire database is one file. When it exceeds 240 MB, WeChat splits it into msg_1.db, msg_2.db, etc. Device migration copies the whole directory, which is encrypted with SQLCipher using a key derived from the IMEI and device identifier; the on-disk format remains SQLite but appears as ciphertext.

Writes use Write-Ahead Logging (WAL). New messages are appended to the WAL file first, allowing foreground scrolling and background message receipt to run concurrently. The WAL pre-allocates a fixed 4 MB, so file-size changes cannot be used to detect new messages during recovery.

Limitations and Mitigations

SQLite enforces a single-writer model. WeChat shards data by session, distributing write load across many small databases. For high-concurrency write scenarios on mobile, SQLite is not the answer.

Practical Takeaway: Build Local-First

For local-first applications — notes, IM, offline tools — adopt SQLite with WAL and proper indexing before reaching for a server-side database. Mastering these primitives saves enormous architectural effort.