Taking control of the SQLite WAL – Philip O'Toole

Skip to content

No results

Search

Philip O'Toole

Software engineering, distributed systems, databases, and the teams that build them

Menu

rqlite is a lightweight, open-source, fault-tolerant relational database built on SQLite and Raft. Version 10 is out now.

rqlite uses SQLite as its storage engine and has a particular relationship with the SQLite Write-Ahead Log (WAL). SQLite, left to itself, manages its own WAL: it checkpoints when the WAL grows, it checkpoints when the last connection closes, and it decides when frames move from the WAL into the main database file.

That is a problem for rqlite. In rqlite, the WAL is not just a SQLite implementation detail. It is a core part of how rqlite operates. If SQLite checkpoints the WAL at the wrong time, rqlite can no longer rely on the WAL as the incremental state it needs to track.

To understand better why rqlite works the way it does, and also learn something about SQLite WAL internals, let’s dig into the problem rqlite has to solve. It’s an interaction between SQLite and the Raft Consensus system that took years to get right.

Raft has One Goal

Conceptually Raft has one goal: create a log of changes to a State Machine and ensure that that log is perfectly replicated across a group of machines. That’s it. Everything else flows from this fact.

But if this log is going to store every event — and in rqlite an event is a SQL statement and the State Machine is the SQLite database — what prevents the log growing without bound? Raft has an answer for that — it’s called snapshotting. Snapshotting means that Raft periodically requests a copy of the State Machine, persists it to disk, and then deletes all the logs reflected in that copy. Every practical Raft-based system must implement a snapshotting mechanism.

rqlite is built on the HashiCorp Raft library. The HashiCorp library provides a default snapshotting approach but lets applications supply their own. rqlite once used the default. It no longer does, and that is where the SQLite WAL comes in.

Snapshotting SQLite

Early versions of rqlite snapshotted SQLite in a simple way. When Raft requested a snapshot rqlite provided it with a consistent copy of the entire SQLite database. This was very robust, but had one obvious shortcoming: if you have a 2GB database and change a few hundred rows, copying the entire 2GB to capture just those changes in the snapshot is pretty wasteful. As rqlite deployments grew into the multi-gigabyte range, this approach became impractical.

Fortunately SQLite provides a solution: the WAL. When running in WAL mode, SQLite writes all changes to the WAL, as a sequence of frames — each frame holding one modified database page. The data only moves from the WAL back into the main database file when SQLite performs a checkpoint. Between checkpoints, the WAL contains exactly the changes made since the last checkpoint.

rqlite takes advantage of the WAL as follows: when Raft requests a snapshot, rqlite copies the current WAL and hands that copy to Raft. rqlite then checkpoints the WAL into the main SQLite database file. The WAL starts empty again, ready to accumulate the next batch of changes. So at any moment, the WAL contains exactly the unsnapshotted SQLite state relative to the last accepted Raft snapshot. That does mean we need a Snapshot Storage system that can receive a sequence of WAL files, as opposed to self-contained copies of the database. That’s a distinct challenge, but one I will leave for a future post.

None of this happens automatically. SQLite, left to itself, would manage the WAL on its own schedule — and that schedule is the wrong one for rqlite.

rqlite system design<br>How rqlite takes control of the SQLite WAL

SQLite is well-designed software: it works perfectly for most use cases as-is, but it still allows rqlite to exercise the control it needs. That does not require patching SQLite. It requires configuring SQLite so that checkpointing only happens when rqlite asks for it, and preventing users from issuing commands that would violate that contract:

Disabling all automatic checkpointing, so SQLite does not move WAL frames into the database file without rqlite knowing.

Trapping any user-issued PRAGMA that would checkpoint or change the WAL mode, and instead returning an error.

Explicitly disabling checkpoint-on-close. While not strictly necessary, doing so permits fast restart times — how this works will be explained later.

From then on rqlite drives checkpointing itself. It issues explicit checkpoints as part of the snapshotting process, always requesting a TRUNCATE checkpoint but being ready for that to fail. What is a TRUNCATE checkpoint? It is a checkpoint operation that truncates the WAL file to zero bytes after a successful checkpoint. But this operation can fail to complete. How rqlite prepares for that failure is one of the more interesting parts of rqlite’s database layer.

Welcome to the Real...