Boost SQLite Performance: WAL Mode and Asynchronous Checkpoint Explained

WAL and Asynchronous Checkpoint

SQLite introduced Write‑Ahead Logging (WAL) in version 3.7.0 to improve write performance and concurrency, but it is disabled by default, so many applications still use the slower rollback journal mode.

WeChat enabled WAL and added an asynchronous checkpoint. A/B testing showed more than a 70% reduction in write latency compared with the traditional rollback mode and a slight decrease in database corruption rate.

Rollback Journal vs. WAL

The default atomic commit and rollback mechanism uses a separate rollback journal file ("-journal"). Before modifying the main database file, SQLite copies the original pages to the journal, writes the new pages, and clears the journal after the transaction commits.

Each write to the journal or the main file requires an fsync to ensure data is flushed to disk, which is time‑consuming. The journal also holds an exclusive lock on the database, preventing concurrent reads and writes.

In WAL mode, writes are appended to a separate "-wal" file and indexed via a shared memory "-shm" file. Reads combine the main database file with the uncheckpointed portion of the -wal file, allowing reads and writes to proceed concurrently.

Periodically, a checkpoint merges the -wal contents back into the main file. SQLite’s default checkpoint threshold is 1000 pages (Android uses 100 pages). Checkpointing can block reads, but its impact is limited because it is less time‑critical.

Optimization Ideas

Skip fsync when writing to the -wal file, because corruption of the -wal only affects uncheckpointed data, not the entire database.

Perform the costly fsync during checkpoint in a separate thread, allowing the write transaction to return quickly.

This forms the basis of asynchronous checkpoint , which reduces and moves expensive fsync operations, improving write performance and reducing occasional stalls without increasing the overall corruption rate.

Strategy Selection and A/B Test

The key parameter is the checkpoint threshold. A lower threshold triggers frequent checkpoints, keeping the -wal file small and improving read performance, but increasing disk writes. A higher threshold does the opposite.

We first tried a threshold of 0 (checkpoint after every commit). This caused frequent checkpoint failures under sustained load, leading to -wal growth and performance degradation. Adding a blocking threshold —pausing other operations when the -wal size exceeds a limit—solved the issue. After testing, we settled on a configuration called ACP(0/100) (asynchronous checkpoint with normal threshold 0 and blocking threshold 100).

Another configuration, ACP(100/300) , keeps the normal threshold at 100 (similar to Android’s default) and sets the blocking threshold to 300.

Because each commit already performs a checkpoint, we also set PRAGMA SYNCHRONOUS=NORMAL to avoid an extra fsync on write operations.

Performance Results

Online A/B testing with WCDB for Android 1.0.5 compared three configurations: traditional Rollback, default WAL, and WAL + ACP(0/100). Metrics focused on operation time (actual processing time) and wait time (time spent acquiring locks), for both reads and writes.

Write operation results (ms):

Rollback: 13.022 ms (wait 0.246 ms)

WAL: 5.894 ms (wait 0.160 ms)

ACP: 3.577 ms (wait 0.269 ms)

Read operation results (ms):

Rollback: 1.294 ms (wait 0.239 ms)

WAL: 1.394 ms (wait 0.025 ms)

ACP: 1.436 ms (wait 0.025 ms)

WAL reduced write latency by over 50% compared with Rollback, and enabling asynchronous checkpoint added roughly another 40% improvement. While single‑thread read performance of WAL is slightly lower, its superior concurrency makes overall read throughput much better.

Corruption Rate Comparison

We also measured database corruption frequency (failures per 10 billion writes). Results showed that WAL and ACP(100/300) have similar low corruption rates, whereas Rollback and ACP(0/100) exhibit 1.5‑2× and 3‑4× higher rates respectively. Estimated daily corruption probabilities are about 1/30 000 for WAL/ACP(100/300) and 1/10 000 for ACP(0/100).

The difference stems from how often the main database file is written and fsynced: more writes to the main file increase the chance of incomplete writes during power loss or OS crashes, raising corruption risk.

Using WAL and Asynchronous Checkpoint in WCDB

WAL and asynchronous checkpoint are key optimizations in WCDB. iOS enables them by default; Android can enable them as follows:

// Open DB in WAL mode
SQLiteDatabase db = SQLiteDatabase.openOrCreateDatabaseInWalMode(path, password, cipher, null);

// Enable asynchronous checkpoint
db.setAsyncCheckpointEnabled(true);

Or via SQLiteOpenHelper:

SQLiteOpenHelper helper = new SQLiteOpenHelper() {
    // implement onCreate, onUpgrade, etc.
};
helper.setWriteAheadLoggingEnabled(true);
SQLiteDatabase db = helper.getWritableDatabase();
db.setAsyncCheckpointEnabled(true);

Enabling these features in WCDB Android apps is recommended to gain the performance and reliability benefits described above.

Read the original article on the WCDB GitHub page for more details.