WAL Frames, Read Marks, and What a Checkpoint Can Move

In rollback-journal mode a transaction that changes one row must first copy the original page to a journal and then overwrite the page in the database file. Every commit therefore touches the same page twice and every reader of that page pays for the writer’s progress. Write-ahead logging inverts this: the new page image is appended to a separate log, and the database file is left alone until a checkpoint copies a contiguous prefix of the log back into it. The main file becomes a snapshot that is always behind, and the log is the authority.

The inversion buys concurrent readers alongside a single writer, and it costs a second file whose growth is governed by readers rather than by writers. That last property is the one that causes trouble on a phone, where a long-lived read transaction can hold the log open for hours.

Frames are appended, never overwritten

The log begins with a 32-byte header: a magic number, a format version, the page size, a checkpoint sequence number, two salt values, and two checksum words. After the header come frames. A frame is a 24-byte header followed by exactly one page of data, so at a 4096-byte page size each frame occupies 4120 bytes, and the page data starts at a 24-byte offset that is not a multiple of the page size.

The frame header carries the page number it belongs to, the size the database would have after this frame’s transaction commits (zero for frames that are not the last of a commit), the two salts copied from the log header, and a running checksum. A transaction that dirties n pages appends n frames and then, in the final frame, records the new database size. Nothing marks which frames belong together other than that field: a frame with a nonzero database-size value ends a transaction, and the log is scanned forward from the last valid commit to determine what is durable.

Because the checksum is cumulative over all preceding frames including the header, a torn or truncated tail is detectable by arithmetic alone. On recovery the reader walks frames from the header forward, verifying each checksum; the first failure terminates the scan and everything after it is discarded. That is why an interrupted append is harmless: an uncommitted transaction leaves frames whose checksum chain ends mid-transaction with no commit frame, and those frames are simply ignored.

Two salts exist so that a log can be invalidated without erasing it. When the log is reset, the writer increments the salt; frames from the previous generation no longer match the header and are skipped. On a device that must avoid rewriting a whole file, this is cheaper than zeroing.

The wal-index and the read mark

A reader cannot afford to scan the log on every query. Beside the log sits a shared-memory index (the -shm file), which holds a hash table mapping page numbers to the most recent frame containing each page, plus the frame number of every commit, plus a small set of read marks. The index is derived data: it can be rebuilt from the log after a crash, and it is never the only copy of anything.

Each reader registers a read mark, which is a frame number below which the reader promises not to need older data. A reader that starts now takes the largest existing read mark that is not in use, or creates a new one at the current end of the log. It then reads pages by consulting the hash table for the highest frame at or below its mark. This is what gives readers snapshot isolation without copying anything: the snapshot is a number.

A writer must know that no reader is still using a frame before it may overwrite that frame’s page in the main database. That condition is mxFrame minus the smallest active read mark, and it directly bounds the checkpoint.

The structure also explains a hard constraint. Because the index lives in shared memory that must be coherent across processes, write-ahead logging requires a filesystem that supports shared memory and correct locking. It does not work over most network filesystems, and on those the database must fall back to a rollback journal. On a mobile device this rarely bites, but a database placed on a user-visible shared directory synced by a desktop client is exactly that case.

Checkpointing is a bounded copy

A checkpoint copies frames from the log back into the database file, in page order, starting from the oldest frame not yet copied. It writes each page at its proper offset, flushes, and then advances the log’s checkpoint sequence number. It does not delete frames; it only marks how far it got.

Four modes differ in how much they insist.

A passive checkpoint copies as many frames as it can without blocking anyone, stops at the smallest active read mark, and returns without waiting. It never waits for readers and never truncates the log. A full checkpoint waits for writers to finish and for readers to advance, then copies every frame it is allowed to. A restart checkpoint goes further: it waits until it can copy the entire log, so that the next writer may reset the log to the beginning. A truncate checkpoint does the same and then truncates the log file to zero bytes, releasing the space to the filesystem.

PRAGMA journal_mode = WAL;
PRAGMA wal_autocheckpoint = 1000;   -- pages, not bytes
-- Explicit control, returning busy, log frames, and frames checkpointed:
PRAGMA wal_checkpoint(PASSIVE);
PRAGMA wal_checkpoint(TRUNCATE);

The automatic threshold is 1000 pages by default, meaning a checkpoint is attempted once the log exceeds roughly 1000 pages, about 4 MB at a 4096-byte page size. The number is a page count, not a byte count, so the same setting yields a 64 MB threshold when the page size is 64 KiB.

The important asymmetry: a passive checkpoint triggered by the writer runs on the writer’s thread, and it can fail to make progress entirely if a reader is holding an old mark. The log then keeps growing. On a phone, a UI thread that opens a cursor and leaves the transaction open while an animation runs is enough to do this. The log grows at the rate of the write traffic, and since the log is a file in the same container, it consumes the same storage budget as the database.

Why the log can grow without bound

Log growth is the signature failure of this design, and it has three distinct causes.

An open read transaction sets a floor. The checkpoint cannot copy any frame at or above the smallest active read mark, so the reachable prefix is fixed for the lifetime of that transaction. A reader that never commits pins the log at whatever size it had when it started.

A long-running writer has a similar effect from the other side. Only one write transaction exists at a time, and while it holds the write lock the log’s end does not advance past its uncommitted frames, but its own frames are already in the log. A writer that dirties many pages in one transaction (a bulk import, a schema migration, a large DELETE) appends one frame per dirty page and cannot checkpoint them until it commits.

A blocked checkpoint also means the database file and the log diverge. The main file may be hundreds of megabytes behind. A subsequent crash replays the log forward at open, which is correct but slow: recovery is proportional to the number of frames after the last checkpoint. On a device where the process may be killed at any moment, a long recovery at startup is indistinguishable from a hang, and the platform’s watchdog may kill the process again before it finishes.

The remedy is a policy, not a flag. Transactions should be short enough that a checkpoint can interleave; readers should be closed promptly rather than held across rendering; and the application should be able to state how large the log is allowed to become, checking the log’s frame count through the checkpoint pragma’s return values rather than by measuring files.

Where this meets the rest of the storage stack

The log’s frames are written with the same durability question as any other write: whether a commit survives a power cut depends on whether the log was flushed to the medium and whether the medium honours the flush. Fsync cost and journal modes on flash covers that boundary, and it is where the synchronous setting actually changes behaviour rather than the page layout.

The log also has a size cost that interacts with the platform’s storage accounting. A database plus its log plus the shared-memory index is three files, and a backup that copies the database without the log copies a file that may be arbitrarily stale. The safe procedure is to checkpoint into the main file first, which is also why atomic file replacement has to treat the log as part of the unit being replaced rather than as a separate artefact.

One structural limitation is worth stating plainly: the log is not segmented and the checkpoint is a prefix copy. There is no background compaction that rewrites only live frames, as an LSM tree would. The cost of a checkpoint is therefore proportional to the number of frames since the last one, not to the number of pages that actually changed in the database, which means a workload that repeatedly updates the same page pays to copy that page once per checkpoint regardless of how few distinct pages it touches.