Speed up SQLite with Write-Ahead Logging (WAL)

When you update a SQLite Database within a transaction, the standard behavior is that the original data is copied to a separate rollback file and the new data is written directly to the DB file. This is reliable, but it results in two disk writes for every DB change. When you COMMIT, the rollback file is removed since it is no longer needed. Should you instead ROLLBACK, then the data is restored to the DB file from the rollback file.

To make SQLite even faster you can enable a feature called Write-Ahead Logging (aka WAL). WAL can be faster because it reverses the behavior described above. With WAL, instead of modifying the DB file itself and maintaining a separate rollback file, each change to the SQLite Database is written to a separate "write-ahead logging file" (which typically ends in "-wal"). This results in just a single disk write.

Additionally, because of the separate files, an app with multiple threads is able to read from the DB while it is being written to. And vice versa. This is an especially nice benefit for web apps that need a DB, but do not require a full-featured database server.

To enable WAL, you can use the SQLite Pragma command:

PRAGMA journal_mode=WAL;

In Xojo code, you send the Pragma command to the SQLite DB using the SQLExecute method:

DB.SQLExecute("PRAGMA journal_mode=WAL;")

Or more simply you can set the SQLiteDatabase.MultiUser property to True:

DB.MultiUser = True

The data in the separate WAL file will at some point have to be transferred back to the original database. This is called a "checkpoint". You can do these manually or let SQLite handle them automatically, which according to the SQLite docs happens when the WAL file reaches a threshold of 1000 pages.

You can manually initiate a checkpoint using the wal_checkpoint Pragma command:

PRAGMA schema.wal_checkpoint;

In Xojo code, you can send this Pragma command to the DB using the SQLExecute method:

DB.SQLExecute("PRAGMA schema.wal_checkpoint;")

There are downsides to using WAL. Performance for reads can suffer when there is a large WAL file because data has to be searched in two places. The official SQLite docs on WAL also list some of the other downsides.

But for most types of usage, WAL is worth using. Try it with your apps to see if they get a benefit.

This article original appeared here: SQLite Write-Ahead Logging