FoldedFolded01Three primitives, not two
The plain explanation
Durability: once a write returns to the caller, the data survives a crash, a power cut, or a hard reset. Not “written”, but the disk promised us. Atomicity is its twin: a bundle of changes either all happen or none do, even if the machine dies halfway.
Both come from one trick.
Modifying the database in place risks dying mid-write. The classic rollback journal (SQLite’s default, journal_mode=DELETE) copies the original pages to a journal file, modifies the database, then deletes the journal. After a crash, the next process finds a “hot journal” and replays the originals back [6].
Write-ahead logging inverts it: the database stays untouched, new pages go to a separate log file, and the commit itself is a record in that log.
“A COMMIT occurs when a special record indicating a commit is appended to the WAL. Thus a COMMIT can happen without ever writing to the original database.”
SQLite, Write-Ahead Logging, §2 — fetched from sqlite.org [1]The .db you read is a view over two files: the main .db and the log. Four terms to fix:
the WAL file — a separate file beside the database,
state.db-walforstate.db. Append-only. Here:walmode, 4096-byte pages, autocheckpoint every 1000 pages.
the
-shmfile / wal-index — a shared map of where each page lives in the log, or every reader would scan the whole thing; SQLite builds it in a memory-mapped file,state.db-shm. It is derived: deleted on last close, rebuilt from the log. WAL needs shared memory, so “WAL does not work over a network filesystem.” [1]
a reader — any process with the database open, running a query: the Hermes gateway,
cron.scheduler, a dashboard script. On this boxlsofshows two holdingstate.dbopen right now (§03).
a checkpoint — copying the log’s pages back into the main database so the log can be reused from the beginning. SQLite’s framing is why it is a third primitive: “in the rollback-journal approach, there are two primitive operations, reading and writing, whereas with a write-ahead log there are now three primitive operations: reading, writing, and checkpointing.” [1]
The default works: SQLite auto-checkpoints at 1000 pages (~3.9 MiB at 4096-byte pages), and deletes the log on last close. The two cases it misses: a reader that never leaves, and a copy made at the wrong moment.
How it shows up in practice
The stack’s long-lived stores are all SQLite-in-WAL, each with sidecars (read ):
/root/.hermes/state.db 1,061,171,200 B (1012 MiB, measured)
/root/.hermes/state.db-wal 9,389,512 B (8.95 MiB)
/root/.hermes/state.db-shm 1,966,080 B
/root/.hermes/mnemosyne/data/mnemosyne.db 19,742,720 B
/root/.hermes/mnemosyne/data/mnemosyne.db-wal 4,190,072 B
/opt/open-design/.od/app.sqlite-wal 4,367,232 B
/opt/uptime-kuma/data/kuma.db-wal 1,227,792 BThe rule is about file sets: a SQLite database is three files while anything has it open. Copy one and you have copied a lie (§04).
Why the ordering matters. Every mechanism below sits on one rule: flush the log before the page. CMU 15-445 compares it against STEAL/NO-FORCE and the recovery each policy owes [3] [4]; Wikipedia routes to ARIES [15]. Everything below is one failure of that ordering or one consequence of it.
Folded02The four checkpoint modes
The plain explanation
A checkpoint is four behaviours; the difference is how much the checkpointer may wait. From PRAGMA wal_checkpoint [2]:
| Mode | What it does | Who it blocks |
|---|---|---|
| PASSIVE | “Checkpoint as many frames as possible without waiting for any database readers or writers to finish.” The busy-handler is never invoked. | Nobody. It gives up early. |
| FULL | “blocks … until there is no database writer and all readers are reading from the most recent database snapshot. It then checkpoints all frames in the log file and syncs the database file.” | Writers. Readers proceed. |
| RESTART | FULL, plus it waits “until all readers are finished with the log file… This ensures that the next client to write to the database file restarts the log file from the beginning.” |
Writers; waits on readers. |
| TRUNCATE | RESTART, plus “the WAL file is truncated to zero bytes upon successful completion.” |
As RESTART. |
The pragma returns one row of three integers — the number line you will read on the box:
- Column 1 (“busy”) — “0 if the equivalent call to
sqlite3_wal_checkpoint_v2()would have returnedSQLITE_OKor 1 if the equivalent call would have returnedSQLITE_BUSY”, set “if aRESTARTorFULLorTRUNCATEcheckpoint was blocked from completing, for example because another thread or process was actively using the database.” - Column 2 — modified pages written to the log.
- Column 3 — pages moved back into the database file.
Column 1 is often called “the number that says a reader is pinning you.” True only in a blocking mode: the nightly check here uses PASSIVE, which never invokes the busy handler, so its column 1 is 0 either way. For PASSIVE the tell is column 2 > column 3, frames left in the log.
Read both numbers; never trust column 1 alone.
How it shows up in practice
state-db-health.sh (/root/.hermes/scripts/, root cron 19:30) runs PRAGMA wal_checkpoint(PASSIVE) and logs the raw reply. All six nights since the repair:
2026-09-21T19:30:51+00:00WAL: 8 MB -> 8 MB (checkpoint replied: 0|523|523)
2026-09-22T19:30:31+00:00WAL: 8 MB -> 8 MB (checkpoint replied: 0|872|872)
2026-09-23T19:30:32+00:00WAL: 8 MB -> 8 MB (checkpoint replied: 0|59|59)
2026-09-24T19:30:32+00:00WAL: 8 MB -> 8 MB (checkpoint replied: 0|308|308)
2026-09-25T19:30:33+00:00WAL: 8 MB -> 8 MB (checkpoint replied: 0|56|56)
2026-09-26T19:30:34+00:00WAL: 8 MB -> 8 MB (checkpoint replied: 0|801|801)Read the three numbers as a sentence: col2 == col3 nightly means every frame made it back, the checkpoint completed, nothing starved. Live while this page was built: 0|407|407.
The 0 is the boring part.
The equal pair is the healthy part.
HeldHeld03Checkpoint starvation — the WAL that grows without bound
The plain explanation
Here is the mechanism, in the docs’ own words.
“A checkpoint can run concurrently with readers, however the checkpoint must stop when it reaches a page in the WAL that is past the end mark of any current reader. The checkpoint has to stop at that point because otherwise it might overwrite part of the database file that the reader is actively using.”
SQLite, Write-Ahead Logging, §6 — fetched from sqlite.org [1]The end mark is where the last commit record sat when a reader started; a reader reads only up to its own end mark. Note any: the oldest live reader sets the ceiling, not the newest. One long read transaction is a wall no checkpoint can pass.
SQLite remembers how far it got and resumes. The docs, again:
“if a database has many concurrent overlapping readers and there is always at least one active reader, then no checkpoints will be able to complete and hence the WAL file will grow without bound.”
“This scenario can be avoided by ensuring that there are ‘reader gaps’: times when no processes are reading from the database and that checkpoints are attempted during those times.”
SQLite, Write-Ahead Logging, §6 [1]The half everyone gets wrong: a successful checkpoint does not make the log smaller. Again from §6:
“The checkpoint does not normally truncate the WAL file (unless the journal_size_limit pragma is set). Instead, it merely causes SQLite to start overwriting the WAL file from the beginning. This is done because it is normally faster to overwrite an existing file than to append.”
So the log has a high-water mark: it grows to its worst case, then stays, overwritten in place. Two consequences that only look contradictory:
- A WAL bigger than the 1000-page autocheckpoint threshold may just be a high-water mark.
- A WAL growing night after night is starvation.
Both are live here. wal_autocheckpoint is 1000 pages; at page_size = 4096 that is 4,096,000 bytes. The current log, 9,389,512 bytes, is 2.29× the threshold while the checkpoint moves every frame back: a high-water mark from the Sep 21 rebuild, not a leak. The pre-incident 223,720,152-byte log, 54.6× the threshold against a 1,062,850,560-byte database, is starvation on disk.
The only lever that reclaims the space is PRAGMA journal_size_limit = N: “Each time a transaction is committed or a WAL file resets, SQLite compares the size of the… WAL file left in the file-system to the size limit set by this pragma and if the journal or WAL file is larger it is truncated to the limit.” Default -1 (no limit); 0 always truncates to minimum. [2]
How it shows up in practice
Who is a reader. lsof /root/.hermes/state.db:
hermes 3969818 root 10ur, 13rr, 19rr, 71ur, 84rr, 99rr /root/.hermes/state.db
python 1050255 root 6ur, 24rr, 25rr /root/.hermes/state.dbTwo processes. hermes 3969818 is the gateway, up 6 days 11 hours 48 minutes, so a read handle has plausibly been open across every nightly checkpoint since Sep 21. python 1050255 is cron.scheduler --external-worker-file …. Nine handles, two processes, one a daemon that almost never exits: the stack’s own architecture is the permanently-present reader the docs warn about. The WAL starves only when the long reads outlast the gaps.
The incident. /root/hermes-recovery-20260918/orig/ preserved the three files as found on Sep 18 08:54:
state.db 1,062,850,560 B (1013 MiB)
state.db-wal 223,720,152 B (213 MiB / 223.7 MB decimal)
state.db-shm 458,752 Band quick_check at 08:57 named the damage:
Error: stepping, database disk image is malformed (11)
Tree 27 page 245417: btreeInitPage() returns error code 11
Tree 16 page 244068 cell 0: Rowid 824633720834 out of order
Tree 16 page 252561 cell 0: invalid page number 218103808
Tree 3 page 42893: btreeInitPage() returns error code 11The 213 MiB log is evidence of the starvation mechanism; the corruption is real and measured. SQLite documents starvation’s costs as disk space and slow queries, not corruption. Both hit the same file the same week; that one caused the other is not something this box can show, and a tidy story that isn’t true is worse than an open question.
Treat “WAL growing without bound” as a leading indicator that something holds the database open, not a diagnosis of corruption.
The same failure, other nouns. PostgreSQL: “Checkpoints are points in the sequence of transactions at which it is guaranteed that the heap and index data files have been updated with all information written before that checkpoint,” [11] and pg_wal cannot recycle segments a consumer still needs: “A slow or failed standby server that uses a replication slot will have the same effect” as never checkpointing [12]. WAL starvation is not an SQLite quirk: it hits every design where the log advances only as far as the slowest reader.
The SQLite changelog records a WAL-reset bug from 3.7.0 through 3.51.2, fixed in 3.51.3 (): it “only affects databases in WAL mode when there are two or more database connections open on the same file, in separate threads or processes, and when those two connections attempt to write or checkpoint at the same instant” — symptom, corruption [16]. SQLite rates it at or below SSD-failure rates, and still advises upgrading.
This box has 3.45.1 installed (the recovery used 3.50.4), and the nightly check opens a second connection and checkpoints while the gateway writes: the exact pattern the bug needs. Boring fix: upgrade the sqlite3 that runs checkpoints here to ≥ 3.51.3.
What to DO
- Log the three columns, every night, forever.
col2 > col3on two consecutive nights is a starving checkpoint. Already in place, costs nothing. - Establish reader gaps deliberately. Checkpoint in a quiet window (here: 19:30, after the cron burst), not the busiest hour.
- When you need certainty, escalate the mode.
RESTARTorTRUNCATEwill complete, at the price of readers blocking; the option for the process that owns the schedule, not the default. - Set
journal_size_limitonly if disk pressure is real. Otherwise accept a bigger-than-threshold-walas a high-water mark and stop alerting on size alone. - Hunt the long reader with
lsof. The failing component is a process, andlsofnames it.
Held04A .db file on its own is not a backup (torn copy)
The plain explanation
A torn copy is a copy of a thing that changed while you copied it: half Tuesday, half Wednesday. For a live SQLite database the hazard is worse, because the database is not one file (§01).
sidecar / hot journal — files that carry part of the database’s state:
-waland-shmin WAL mode,-journalin rollback mode. A journal is “hot” when it holds an unfinished transaction: load-bearing, not garbage.
SQLite is unambiguous in the WAL documentation:
“The WAL file is part of the persistent state of the database and should be kept with the database if the database is copied or moved. If a database file is separated from its WAL file, then transactions that were previously committed to the database might be lost, or the database file might become corrupted. The only safe way to remove a WAL file is to open the database file using one of the sqlite3_open() interfaces then immediately close the database using sqlite3_close().”
The corruption doc lists the failure under “Mispairing database files and hot journals”: “Copying a database file without also copying its journal.” It also spells out the safe cases: “It is also safe to make a copy of an SQLite database file as long as there are no transactions in progress while the copy is taking place. If the previous write transaction failed, then it is important that any rollback journal (the *-journal file) or write-ahead log (the *-wal file) be copied together with the database file itself.” [5]
A copy is a snapshot, and a snapshot must be one instant. cp at 03:00 gives three files from three instants; whether the set recovers is luck about where the copy landed relative to a commit. Snapshot a live database with the SQLite backup API [7], VACUUM INTO 'file.db' [8], or sqlite3_rsync [9].
How it shows up in practice
The same damage has a second name. A torn page is a page half-written when the machine died. PostgreSQL keeps a whole copy of a page in the log the first time it is modified after a checkpoint, full_page_writes, for exactly that reason [13]: the log is what can be replayed, so the log must be complete.
Two real artifacts here illustrate the failure:
# 1. the Sep-18 rescue copy — state.db, and nothing else
/root/hermes-state-backup-20260918/state.db 1,062,850,560 B (no -wal, no -shm)
# 2. the pre-update zip, listed directly
unzip -l /root/.hermes/backups/pre-update-2026-09-21-025409.zip | grep state.db
1013002240 2026-09-21 02:54 state.db
(no state.db-wal, no state.db-shm in the archive)The second is more instructive: taken at 02:54 on Sep 21 against a live WAL, and it captured the .db alone. Had that log held committed transactions not yet checkpointed, restoring the zip would silently lose them or leave a main file and log that disagree.
The brief said the Sep 18 backup folder held both state.db (1.06 GB) and state.db-wal (64 MB). On disk: state.db alone at 1,062,850,560 B (1014 MiB, no sidecar), while the 223,720,152-byte WAL (not 64 MB) sits paired beside its .db in /root/hermes-recovery-20260918/orig/.
The pairing is what matters, and this box holds both the good example (orig/, three files together) and the bad one (the backup folder, .db alone). A fact remembered from last week was wrong in two of its four numbers. Check the disk.
And a live, weekly one. /root/bin/backup.sh (root cron 0 3 * * *) runs restic backup /root /etc to an sftp repo; twelve snapshots exist, the newest ending 2026-09-27 03:00:51. Nothing excludes state.db, so every night restic reads state.db, state.db-wal and state.db-shm as three files at three moments. Restic is correct; the database is mishandled, because “one path per file” is not “one instant.”
quick_check vs integrity_check — and the check that proves nothing
Two pragmas, one difference:
PRAGMA integrity_check— full structural and logical audit, O(N log N) in rows.PRAGMA quick_check— “like integrity_check except that it does not verify UNIQUE constraints and does not verify that index content matches table content. By skipping UNIQUE and index consistency checks, quick_check is able to run faster. PRAGMA quick_check runs in O(N) time.” [2]
The health script runs quick_check six nights a week, integrity_check on Sundays: a sensible cost trade. Note what the cheap nights cannot see: index content matching table content, the hinge to §05.
What to DO
- Take snapshots, not file copies.
VACUUM INTO '/root/backups/state.db.$(date +%F)';runs live, yields one consistent file with no sidecars, and needs no downtime. - If you must
cp, stop the writers and copy all three files — or copy none. - Verify before you trust. A backup never restored is a hypothesis. Run
PRAGMA integrity_check(notquick_check) on the restored copy and confirmok. The atomic-commit doc explains why the hardware assumptions underneath are assumptions [6]. - Never delete a
-walby hand. Open the database and close it cleanly. - Count backups as file sets. Every path-based tool lists
state.dbas a unit. That is a torn-copy risk, not a backup.
VACUUM INTO '/root/backups/state.db.$(date +%F)' — from a live process, one consistent file, no sidecars, documented safe on a running database. Put it next to the nightly restic run and the torn-copy class of failure stops being available to you.
That is a recommendation, not a measurement: nothing on this box runs it yet.
DerivedDerived05The index that isn’t the data
The plain explanation
An index is a structure built beside your data so lookups don’t have to read the data: the back-of-the-book kind. The book is the data, the index maps where words appear, and lose the index and the book is unharmed, you print another one.
A full-text index is that for searching inside text, and SQLite’s module for it is FTS5 [10]. For each token it stores where the token occurs, in shadow tables named after the table they index:
messages_fts messages_fts_data messages_fts_idx
messages_fts_content messages_fts_docsize messages_fts_config
messages_fts_trigram ... plus the same six for the trigram variant
triggers: messages_fts_insert / _delete / _update (and the trigram equivalents)Two consequences, and they are the whole lesson:
- The index is derivable: it holds no fact absent from the
messagestable. - So it can be thrown away and rebuilt, costing time, not truth: “This command first deletes the entire full-text index, then rebuilds it based on the contents of the table or content table”, invoked as
INSERT INTO messages_fts(messages_fts) VALUES('rebuild');[10]
The counterpart is degraded mode: if the index is broken and the canonical rows are fine, keep accepting writes (never reject a user’s message over a search index) and run with search detached until a rebuild lands. That is what this stack does, and its log says so.
How it shows up in practice
Measure the ratio before you believe it. SELECT name, COUNT(*) FROM dbstat GROUP BY name, on the live state.db:
bucket pages size
FTS shadow (derived) 188,332 735 MB <- messages_fts* + messages_fts_trigram*
canonical (messages) 63,135 246 MB
everything else 7,450 29 MB
---------------------------------------------------
whole database 258,947 1011 MB735 MB of the database, 72.7 %, is rebuildable index. Re-run at : derived 188,471 pages (736 MB), canonical and other 70,636 pages (275 MB), 259,107 counted — 188,471 / 259,107 = 72.7 %. The largest object is messages_fts_trigram_data at 90,054 pages (351.8 MB), one of two full-text indexes over the same rows. Those rows numbered 75,059 when the lecture was written, 75,120 at 15:00 UTC. The canonical table is 24 % of the file; the rest regenerates from it.
Rebuild vs restore, actually done here. During the Sep 18–21 repair, /root/hermes-recovery-20260918/repair.sh did not restore a working index; it rebuilt one from surviving tables:
step 2/6: rebuilding messages_fts index -> INSERT INTO messages_fts(messages_fts) VALUES('rebuild');
step 3/6: rebuilding messages_fts_trigram index -> ... VALUES('rebuild');
step 4/6: reconciling FTS rows with messages
fts_orphans_deleted=6 trigram_orphans_deleted=3
fts_rows_added=97 trigram_rows_added=92
step 5/6: analyze + vacuum + WAL mode -> ANALYZE; VACUUM; PRAGMA journal_mode=WAL;
step 6/6: integrity checks -> ok / fk_check_done
repair completeStep 4 defines the boundary. Orphans (rowid NOT IN (SELECT id FROM messages)) were deleted: the index claimed documents the canonical table lacked. Missing rows (m.id NOT IN (SELECT id FROM messages_fts_content)) were added. Then rebuild re-derived the structure from canonical rows. No backup was needed, because the index is not the data.
Degraded mode, live. From gateway.log, repeated through Sep 17:
ERROR hermes_state: Automatic rebuild of stale FTS indexes failed (database disk image is
malformed); canonical writes remain enabled with FTS detached.One sentence, four decisions: the index is stale (something noticed), the rebuild failed (the repair path hit the corruption), canonical writes remain enabled (fail-open toward the user’s data), and FTS [is] detached (the derived thing switched off rather than smearing wrong results over good rows). A lock file, state.db.fts_rebuild.lock, serializes rebuild attempts between processes.
What to DO
- Classify your stores: canonical or derived.
messages,sessions,system_promptsare canonical.messages_fts*,messages_fts_trigram*,-shm, embeddings in Mnemosyne, caches, generated dashboards are derived: a rebuild path, not a backup schedule. - Test the rebuild path when nothing is broken. A rebuild you have never run is a rumor; it is one
INSERT INTO … VALUES('rebuild')per index. - Watch the degraded state’s clock, not just its existence. That log line repeated: fail-open worked, but nothing tracked how long search stayed detached. Fail-open without an owner and a deadline is a slow failure with good manners.
- Check the index specifically.
quick_checkcannot see index-vs-table divergence (§04). FTS5 can:INSERT INTO messages_fts(messages_fts) VALUES('integrity-check', 1);— “used to verify that the full-text index is internally consistent, and, optionally, that it is consistent with any external content table.” O(index), not O(database), so it belongs in the nightly check. - Constrain the index’s footprint. Two full-text indexes at 351 MB and 165 MB make “how big may my index get before I drop one?” a question with a real answer.
The general rule: an index is a bet that regenerating it is cheaper than losing it.
Two full-text indexes over the same rows cost 735 MB to keep and about half a minute to rebuild (the two rebuild steps ran 00:26:19 → 00:26:56). Worth making.
Making it unconsciously, with no rebuild path, no integrity check, and no alert on how long search was degraded, is how a derived file becomes a single point of failure.
06Decision table: symptom → what it means → what to do
Nine symptoms you can see on this box or in a log, each with its reading. Nothing here is a feeling.
| Symptom | What it means | What to do |
|---|---|---|
-wal file larger than the 1000-page threshold, stable for nights |
High-water mark (§03). The checkpoint ran; the file is not truncated by design. | Nothing, unless it grows night over night. |
-wal grows every night; wal_checkpoint(PASSIVE) returns col2 > col3 |
Checkpoint starvation (§03): a reader is pinned. | lsof <db> to name the reader; create a reader gap; checkpoint in the quiet window. |
wal_checkpoint(FULL/RESTART/TRUNCATE) returns column 1 = 1 |
The blocking checkpoint was blocked (§02). | Same as above, plus decide whether readers may wait. |
| Log said “WAL: 223 MB” and never shrank | Starvation at its ceiling (§03). | Preserve .db + -wal + -shm together before touching anything. |
A backup folder contains state.db but no -wal/-shm |
Torn copy (§04). | Restore nothing from it until you know the writers were stopped; switch to VACUUM INTO / backup API. |
quick_check says ok but search returns nothing / stale |
quick_check cannot see index-vs-table divergence (§04, §05). |
Run the FTS5 integrity-check, then rebuild. |
canonical writes remain enabled with FTS detached |
Fail-open by design (§05). | Confirm writes are landing, then own the rebuild and time the degraded window. |
database disk image is malformed for a file whose own integrity_check returns ok |
The message names a file, not a cause; here it recurs for mnemosyne.db via a stale -wal/-shm handle. |
Check the file directly; don’t let a recurring benign message train you to ignore the class. |
| A DB opened by two processes, both writing/checkpointing, on sqlite3 ≤ 3.51.2 | Inside the documented WAL-reset bug window (§03). | Upgrade to ≥ 3.51.3 (or 3.44.6 / 3.50.7 backports); keep the checkpoint on one connection. |
The last row’s version caveat is where this box sits: sqlite3 --version reports 3.45.1; the recovery ran on 3.50.4, both inside 3.7.0 → 3.51.2.
07Key takeaway
Durability is not “the data was written”; it is knowing which bytes you cannot lose and which you can regenerate. The write-ahead log is the bytes you cannot lose, which is why the .db alone is never a backup and a checkpoint that cannot advance is an incident, not a chore. The search index is the bytes you can regenerate, which is why the right response to a broken index is to keep writing, detach search, and rebuild, never to refuse the user’s data.
The WAL rules are stated as three obligations in chapter 5 of Hellerstein, Stonebraker and Hamilton’s Architecture of a Database System [14], the shortest route from “this sounds like folklore” to “this is a contract”. Which situation you are in is not a feeling: it is PRAGMA wal_checkpoint’s second and third columns, and a page count of canonical versus derived.
08Go deeper — the references, keyed to the sentences that use them
Sixteen sources, in one continuous run. Every bracketed number in the body above resolves to an entry here, and every cited entry says so; if a key does not go both ways, the page has a bug in it.
Read first — the two that carry §01–§03-
01
sqlite.org/wal.html
If you read one thing: §2.1 and §6. About 1,600 words; the primary source for §01–§03, and §6 is the checkpoint-starvation section by the implementers.
cited in §01, §03, §04 -
02
SQLite, PRAGMA statements —
sqlite.org/pragma.html#pragma_wal_checkpointwal_checkpoint,journal_size_limit,quick_check,integrity_checkThe modes and the numbers: PASSIVE / FULL / RESTART / TRUNCATE, and the three integers the pragma returns. Also
cited in §02, §03, §04journal_size_limit(§03) and thequick_checkwording (§04).
-
03
CMU 15-445/645 (Andy Pavlo), lecture notes — Logging Schemes
15445.courses.cs.cmu.edu/spring2025/notes/20-logging.pdfFree PDF. Where “flush the log before the page” becomes the rule that makes WAL correct, with STEAL/NO-FORCE deciding how much recovery you owe.
cited in §01 -
04
CMU 15-445/645, lecture notes — Database Recovery
15445.courses.cs.cmu.edu/spring2025/notes/21-recovery.pdfFree PDF. ARIES, and checkpointing as a named design choice rather than a background chore.
cited in §01 -
14
Hellerstein, Stonebraker & Hamilton, Architecture of a Database System (2007)
dsf.berkeley.edu/papers/fntdb07-architecture.pdfFree PDF. The WAL rules as three obligations, in chapter 5 — the chapter to quote when someone asks whether any of this is a real invariant.
cited in §07 -
15
Wikipedia, Write-ahead logging · Wikipedia, ACID
en.wikipedia.org/wiki/Write-ahead_logging · en.wikipedia.org/wiki/ACIDShort, and the fastest route to the ARIES pointer before reading the CMU notes.
cited in §01
-
05
SQLite, How To Corrupt An SQLite Database File
sqlite.org/howtocorrupt.html§1.2 “Backup or restore while a transaction is active” and §1.4 “Mispairing database files and hot journals”: the canonical list of how a
cited in §04.dbon its own betrays you. -
06
SQLite, Atomic Commit In SQLite
sqlite.org/atomiccommit.htmlSector writes, hot journals, and why the hardware assumptions underneath all of this are assumptions.
cited in §01, §04 -
07
sqlite.org/backup.html
The supported way to snapshot a live database from inside a process: one instant, one file, no sidecars.
cited in §04 -
08
sqlite.org/lang_vacuum.html#vacuuminto
The one-command version of the same guarantee:
cited in §04VACUUM INTO '/root/backups/state.db.$(date +%F)'. -
09
sqlite.org/rsync.html
The 3.47+ tool for copying a live database to another host: the answer when the snapshot must cross a network.
cited in §04 -
13
postgresql.org/docs/current/runtime-config-wal.html#GUC-FULL-PAGE-WRITES
The torn-page problem in another engine: PostgreSQL keeps a whole copy of a page in the log the first time it is modified after a checkpoint.
cited in §04
-
10
sqlite.org/fts5.html
§4.4 (external content tables and the index-vs-content-table inconsistency analysis), §6.7
cited in §05integrity-check, §6.12rebuild. -
11
PostgreSQL, Write-Ahead Logging (WAL)
postgresql.org/docs/current/wal-intro.htmlThe same idea with different nouns; note “roll-forward recovery, also known as REDO”.
cited in §03 -
12
PostgreSQL, WAL Configuration · replication slots
postgresql.org/docs/current/wal-configuration.html · …/warm-standby.html#STREAMING-REPLICATION-SLOTSCheckpoints,
cited in §03checkpoint_timeout,max_wal_size, and the replication-slot case where a pinned consumer prevents WAL recycling: starvation in PostgreSQL’s clothes. -
16
Phil Eaton, “Another look at SQLite’s WAL-Reset bug” ()
theconsensus.dev/p/2026/08/23/another-look-at-sqlite-wal-reset.html An independent reproducer, not the vendor’s changelog: cite the changelog for the version range, this for what the conditions look like in practice.The independent reproducer the SQLite docs link to, useful because it states the concurrency conditions plainly instead of in changelog shorthand.
cited in §03
All sixteen URLs were fetched and returned HTTP 200 on UTC. The lecture’s own note about a 418 response applies to a different list: freedesktop.org serves an anti-scraper challenge to scripted fetches, which is why the systemd man page in 301-09 was cited from the local copy instead.
09Appendix — reproducing the evidence on this host
Read-only except where noted. Nothing here starts, stops, or restarts anything.
# 1. Is it WAL mode, and what is the checkpoint threshold? (§01, §03)
sqlite3 /root/.hermes/state.db \
"PRAGMA journal_mode; PRAGMA page_size; PRAGMA wal_autocheckpoint; PRAGMA synchronous; PRAGMA page_count;"
# 2. The three checkpoint columns, live. NOOP checkpoints nothing and is diagnostic-only. (§02)
sqlite3 /root/.hermes/state.db "PRAGMA wal_checkpoint(NOOP);"
sqlite3 /root/.hermes/state.db "PRAGMA wal_checkpoint(PASSIVE);" # <- what the nightly cron runs
# 3. The night-by-night record, and the reply that proves it completed. (§02)
grep -E "WAL:|quick_check|integrity_check" /root/.hermes/logs/state-db-health.log | tail -20
# 4. Who is a reader? Nominate the process, don't theorise. (§03)
lsof /root/.hermes/state.db
ps -o pid,etimes,etime,cmd -p 3969818 1050255
# 5. The starvation evidence, preserved as three files. (§03, §04)
ls -la /root/hermes-recovery-20260918/orig/
cat /root/hermes-recovery-20260918/quick_check.log
# 6. Canonical vs derived, in pages. (§05)
sqlite3 -readonly /root/.hermes/state.db \
"SELECT CASE WHEN name LIKE 'messages_fts%' THEN 'derived' ELSE 'canonical/other' END AS bucket,
COUNT(*) AS pages, printf('%.0f MB', COUNT(*)*4096/1048576.0) AS size
FROM dbstat GROUP BY bucket;"
# 7. The rebuild path, and the index-specific integrity check. (§05)
# NOTE: these WRITE. The integrity-check INSERT touches the index only; 'rebuild' re-derives it.
sqlite3 /root/.hermes/state.db "INSERT INTO messages_fts(messages_fts) VALUES('integrity-check', 1);"
# sqlite3 /root/.hermes/state.db "INSERT INTO messages_fts(messages_fts) VALUES('rebuild');"
# 8. Every SQLite file on the box with a live sidecar — your real backup surface. (§04)
find /root /opt -name "*-wal" -type f -size +1M -printf '%10s %p\n' 2>/dev/null | sort -rnTwo honest gaps
- The
223 MBWAL is not in the Sep 18 backup folder. The brief says that folder heldstate.db(1.06 GB) andstate.db-wal(64 MB). On disk:state.dbalone (1,062,850,560 B), while a 223,720,152-byte WAL is preserved, correctly paired with its.db, in/root/hermes-recovery-20260918/orig/. A mismatch left visible is §04’s lesson. - Causation between the large WAL and the corruption is not established here. SQLite documents starvation as a disk-space and read-performance problem, not a corruption cause. The corruption is proven by
quick_check; the starvation by the WAL size; the link is a hypothesis this lecture declines to assert. The mechanisms that could produce those symptoms — including the WAL-reset bug (3.7.0 through 3.51.2, i.e. the 3.45.1 installed here) — are named in §03.