SQLite's synchronous = NORMAL, And When Not To Use It

Postage, my Mac email client, keeps two SQLite databases. emails.db is a cache of mail that can always be downloaded again, and rules.db holds data that exists nowhere else. Both run in WAL mode, and only the cache uses synchronous = NORMAL:

db = Self.open(directory.appendingPathComponent("emails.db").path)
// Everything in here can be re-downloaded.
Self.exec(db, "PRAGMA synchronous = NORMAL;")
rulesDb = Self.open(directory.appendingPathComponent("rules.db").path)

open sets journal_mode = WAL on both. journal_mode is stored in the database file, but synchronous belongs to the connection, so it has to be set every time you open one.

At the default FULL, each commit syncs the WAL to disk. At NORMAL, commits skip that sync and only checkpoints do it. For 500 single-row commits on my Mac, that took the cache from 0.49 seconds to 0.23 seconds. Postage commits for every loaded message body and flag change, so those add up. Work you already batch into one transaction gains much less, because it only has one commit to begin with.

What You Give Up#

The SQLite docs are direct about the trade. In WAL mode, NORMAL can’t corrupt the database, but it does lose durability: a transaction that committed just before a power loss or hard reboot can roll back. If only your app crashes, the data is safe, because the operating system still has the writes.

For the mail cache, that means the newest few writes might disappear, and the next refresh fetches them again.

When To Keep FULL#

I ask whether the data could come back from somewhere else after a power cut. The mail is still on the IMAP server, so the cache gets NORMAL. Rules and their log exist only in that file, so rules.db keeps FULL.

The same goes for any write you’ve confirmed to someone else, like a payment record, where losing it means two systems disagree and nobody finds out.

Check your journal mode before you copy this line. Outside WAL, NORMAL carries a small risk of corruption from a badly timed power loss on older filesystems, so this reasoning only holds with journal_mode = WAL.