Transactions and performance
Use write-ahead logging when it helps
Understand WAL concurrency, sidecar files, local-filesystem requirements, and checkpoints.
8 minute lesson
Enable write-ahead logging with:
PRAGMA journal_mode = WAL;SQLite stores new changes in notes.db-wal. Connections coordinate through notes.db-shm. These sidecar files are part of the active database, so do not delete, move, or copy them separately.
WAL mode lets readers keep using an existing snapshot while one writer appends changes. It does not create multiple writers.
A checkpoint moves committed pages from the WAL file back into the main database. SQLite normally checkpoints automatically. You can request a non-blocking checkpoint with:
PRAGMA wal_checkpoint(PASSIVE);Keep a WAL database on local storage. WAL needs shared memory between processes on the same machine and does not work over a network filesystem.
Lesson completed