SQLite supports two journaling modes: rollback journal and WAL mode. Modern deployments often prefer WAL mode because it improves both write throughput and concurrency.
Both modes share the same goal of ensuring that the database can be safely recovered while maintaining data consistency in the event of a crash. To achieve this, both approaches write page data to a separate file in addition to the main database file. The main difference between the two is that rollback journal mode stores pages before they are modified (old pages), whereas WAL mode stores pages after they are modified (new pages).
In rollback journal mode, SQLite writes the original version of a page to a journal file before modifying the page. As a result, two files are associated with the database, the main database file and the journal file. The transaction flow is roughly as follows:
- transaction start
- read old page
- write journal
- fsync journal
- write database
- fsync database
- commit
For example, suppose the server crashes during step 5. At this point, the write operation to the database file may have only been partially completed, leaving the page in a corrupted state. However, because the journal file contains the original version of the page, SQLite can use it to restore the database to its consistent state. This process is called rollback.
In WAL (Write-Ahead Log) mode, SQLite writes modified pages to a WAL file instead of updating the database file directly. In addition to the main database file and the WAL file, SQLite also creates a shared-memory file that stores the WAL index used to locate records efficiently within the WAL. For simplicity, this section focuses on the database file and the WAL file only. The transaction flow is roughly as follows:
- transaction start
- write modified page to WAL
- fsync WAL
- commit
In WAL mode, the database file is not modified while a transaction is in progress. Instead, modified pages are appended to the WAL file. At some point after the transaction commits, SQLite copies the committed pages from the WAL file back to the database file. This process is called checkpointing.
For read operations, SQLite consults both the database file and the WAL file in order to present a consistent view of the data. Even if a crash occurs during checkpointing, committed pages are still preserved in the WAL file. Therefore, SQLite can use the WAL to reconstruct a consistent database state.
During normal write transactions, SQLite appends modified pages to the WAL file instead of overwriting the database file, although the WAL file may later be reset or reused after checkpointing.
Comparing the two approaches, WAL requires fewer file I/O operations during a transaction than rollback journal mode. Rollback journal mode must synchronize both the journal file and the database file before a transaction can complete. In contrast, WAL only needs to synchronize the WAL file. As a result, WAL generally achieves higher write throughput.
In addition, because writers append new pages beyond each reader's end mark, they do not overwrite pages that are visible to active readers. This allows readers to establish a snapshot at the beginning of a read operation (technically, an endmark in the WAL). While reading, SQLite combines the database file with WAL records up to that end mark to present a consistent view of the data. In contrast, in rollback journal mode, writers modify the database file directly. To ensure that pages being read are not modified concurrently, SQLite must use stronger locking mechanisms. As a result, WAL generally provides better concurrency than rollback journal mode.