SQLite has been the go-to single-user database for over 20 years. Recent advancements in hardware and distributed consensus have opened up SQLite to more use cases - in particular, SQLite’s ease of use and performance characteristics make it an increasingly attractive option for web-scale applications.
But if you try spinning up an app with SQLite’s default settings, you’ll hit a wall (pun intended) rather quickly. This is because the defaults in SQLite are geared towards its original use case of small-scale data operations on embedded systems.
Today, we’ll take a look at how to enable non-blocking writes and concurrency in SQLite via the journal_mode pragma.
What is a PRAGMA in SQLite?
In SQLite, PRAGMA statements are used to modify the behavior of the SQLite database engine. You can use PRAGMA statements to fine-tune the database according to their specific needs, without altering the underlying code. PRAGMA statements can control memory usage, manage table properties, adjust logging, and more.
What is a Journal in SQLite?
A journal in SQLite is a file used as part of the process to ensure that database transactions are atomic, consistent, isolated, and durable (ACID). It acts as a failsafe - before any changes are committed to the database file, they are recorded in the journal. This way, if a crash or power failure occurs during a transaction, SQLite can use the journal to roll back or complete transactions that were in progress, ensuring data integrity is maintained.
Journal Mode: Rollback
The default journal mode in SQLite is the Rollback Journal. In this mode, before any changes are written to the database, a copy of the original unchanged data is stored in a separate rollback journal file. This copy includes all the data pages that the transaction modifies. If the system crashes mid-transaction, SQLite can use this rollback journal to restore the database to its previous state, ensuring that no partial updates are applied.
Rollback journal mode has a few significant limitations. For one, it locks the database during writes, which can lead to worse read performance. Additionally, once a transaction is committed in rollback mode, the rollback journal is deleted, adding some overhead to the operation.
To manually set your SQLite database to rollback mode, use the following command:
PRAGMA journal_mode=DELETE;
Journal Mode: Write-Ahead Logging (WAL)
Write-Ahead Logging (WAL) mode is the most sophisticated journaling method in SQLite. It significantly enhances database read and write performance, especially in environments with high concurrency or where reads are more common than writes. Unlike traditional rollback journal modes that lock the entire database for writing, WAL allows reads and writes to proceed concurrently, eliminating a huge bottleneck.
In WAL mode, changes to the database are first written to a separate WAL file, not directly to the database file. This file records all changes to be made to the database as a continuous log. Only after a checkpoint operation, which occurs automatically or can be triggered manually, are these changes propagated from the WAL file to the main database file.
WAL mode requires careful tuning of the checkpoint frequency to balance between performance and disk space usage. The checkpoint operation, which merges changes from the WAL file into the main database file, is crucial for controlling the size of the WAL file and ensuring data is permanently written to the main database. One benefit of checkpointing over the default journal mode is that it batches writes from the journal to your database file, lowering the overhead on a per-transaction basis.
For web applications, concurrency and high availability are non-negotiable, which makes WAL mode the best option for production applications.
To set your SQLite database to WAL mode, you can run the following command:
PRAGMA journal_mode=WAL;
Journal Modes: Everything Else
While rollback and WAL are the most common journal mode choices, there are a few others that may be useful in specific situations.
TRUNCATE
In truncate mode, instead of deleting the journal file at the end of each transaction, SQLite truncates it to zero length. The journal file itself remains in existence on the disk but with no content after the transaction completes. This approach is primarily used to minimize the file system overhead involved in deleting and recreating files.
In environments where the file deletion operation is expensive or slow, truncating a file may be faster than deleting and recreating it. But on modern file systems or SSDs where deletion is highly optimized, the benefits of truncate mode may be less significant. You’ll see some performance gains over delete mode, but there are better options available.
PERSIST
In persist mode, when a transaction is completed, SQLite does not delete the journal file. Instead, it leaves the file on the disk but marks its contents as inactive. This means the file remains intact but is effectively empty as far as SQLite is concerned, until it is needed again for another transaction.
For applications where transactions are frequent and closely spaced in time, persist mode can reduce the overhead associated with file operations. Since the journal file is not deleted but merely marked inactive, it can be quickly reactivated for a new transaction without needing to undergo the filesystem operations associated with file creation.
MEMORY
In memory mode, SQLite maintains the journal entirely in RAM. This approach means that any changes that are part of a transaction are recorded in volatile memory. As a result, this mode provides the highest possible performance for transactions because it avoids disk latency completely.
This mode significantly speeds up transactions by eliminating I/O operations associated with disk access for journaling purposes, but it comes with trade-offs regarding durability and crash recovery.
This mode is best suited for development or testing environments.
OFF
In off mode, there is no journal to assist with recovery/rollback, and transactions are no longer atomic. While this leads to the fastest possible IO performance, it should not be used in a production environment.
Wrapping up
We hope you now have a solid understanding of the different journal modes available to you in your SQLite environment. At SQLite Cloud, our databases are set to WAL mode for maximum durability and performance.
For more SQLite content, subscribe to our newsletter. If you’re interested in a lightning-fast, local-first backend, create a SQLite Cloud account and get started for free.