How WeChat Cut SQLite Corruption Rate in Half with Space, Sync, and Master Table Backups

1. Introduction

WeChat does not store chat records on its backend servers; all records reside in a local SQLite database on the mobile client. When this database becomes corrupted, years of chat history can be lost. The observed corruption rate in production is 0.02% (2 out of 10,000 users), and the official repair algorithm succeeds only about 30% of the time.

2. SQLite Corruption Causes and Optimizations

The SQLite website lists many potential causes, but analysis of real cases shows three primary reasons on mobile devices:

Insufficient storage space

Device power loss

File sync failure

2.1 Optimize Space Usage

WeChat introduced proactive space management: business files must be requested before use, each file declares an expiration (day, week, month, or permanent), and expired files are automatically cleaned. For non‑WeChat files (photos, videos, other apps), the app can prompt users to clean up space.

2.2 Optimize File Synchronization

synchronous = FULL : forces every transaction to flush writes to the file.

fullfsync = 1 : on iOS this option guarantees that the order of writes matches the order of commits, preventing corruption after power loss.

2.3 Optimization Results

After applying these measures, the corruption rate dropped by more than half, though some damage remains and repair success still needs improvement.

3. SQLite Repair Logic Optimization

3.1 Master Table

SQLite stores each table as a B+ tree; metadata for all tables resides in the sqlite_master table, which itself is a B+ tree.

3.2 Why the Official Repair Algorithm Fails

The algorithm reads each table’s schema from the master table and copies data to a new database. If any node of the master table is corrupted, the tables under that node cannot be recovered; if the root page (page 0) is damaged, the entire database cannot be repaired, leading to a low success rate.

3.3 Backing Up the Master Table

We periodically poll the master table and serialize it as an array for backup.

Backup Timing

Analysis shows the master table’s root node only changes when a table is created or dropped, so a simple timed poll is sufficient.

Backup File Validity

A dual‑backup scheme stores two copies, each with a CRC checksum. When backing up, the system overwrites the older or corrupted copy, ensuring at least one valid backup. To handle low‑space scenarios, space is pre‑allocated (initially 32 KB, enough for ~750 tables) and grown in 32 KB increments.

3.4 Repair Results

Backing up the master table doubled the repair success rate.

4. Other Notes

These optimizations improve the reliability of WeChat’s chat‑record storage and will be merged into the upcoming open‑source WCDB (WeChat Database) component, slated for release in mid‑2017.