Choosing WAL vs Rollback Journal for High‑Frequency Small Transactions

Answer

For a multi‑threaded SQLite workload with many concurrent readers and a high rate of small write transactions, WAL mode usually gives a sustainable performance advantage over the legacy rollback journal.

Confirmed facts

• WAL allows readers to proceed while a single writer appends to the log file.
• Rollback journal forces an exclusive lock for each write, blocking all readers until the transaction commits.
• Appending to a WAL file is cheaper than the create‑write‑delete cycle of a rollback journal for small transactions.

Likely explanation / conditions

When the write rate is high but each transaction touches only a few pages, the WAL file grows steadily. If the autocheckpoint threshold (default 1000 pages) is reached often, a checkpoint will copy pages from the WAL back to the main database, causing a latency spike for the writer and temporarily increasing I/O.

If the workload is read‑heavy (reads ≫ writes) the benefit of non‑blocking reads outweighs occasional checkpoint stalls. In a write‑only or write‑dominant scenario the checkpoint overhead can neutralize the gain.

Steps to verify and tune for your case

1. Set the journal mode: PRAGMA journal_mode=WAL;
2. Reduce sync overhead: PRAGMA synchronous=NORMAL; (maintains durability for most crashes).
3. Monitor the WAL file size during peak load: PRAGMA wal_checkpoint; or check the .wal file on disk.
4. If the .wal file frequently exceeds the autocheckpoint limit, raise the threshold: PRAGMA wal_autocheckpoint=2000; (or disable autocheckpoint and checkpoint manually during low‑activity periods).
5. Measure read latency and write throughput under a realistic load (e.g., using sqlite3 command‑line tool or a simple Python loop) for both journal_mode=WAL and journal_mode=DELETE.
6. Compare the results; if read latency stays low and write throughput is higher with WAL, keep it; otherwise consider staying with DELETE or tuning the checkpoint frequency.

Missing diagnostic detail

To decide definitively, I need to know the approximate write transaction rate (transactions per second) and the read‑to‑write ratio during peak load. If the write rate is so high that the WAL file hits the checkpoint threshold every few milliseconds, the latency spikes may outweigh the benefit; otherwise WAL is likely the better choice.