In SQLite, what are the -wal and -shm files that appear beside a database file, and why must you never delete them by hand?
answer
- a second way to journal
- changes appended, not overwritten
- commits can live only in -wal
- -shm indexes the -wal
- a checkpoint copies pages home
basics
~20 sIn WAL mode the -wal file holds committed changes not yet copied into the main database file, and the -shm file is a shared-memory index over it. Deleting the -wal by hand can lose committed transactions or corrupt the database.
solid answer
~50 s`PRAGMA journal_mode=WAL` switches SQLite from a rollback journal to a write-ahead log. Instead of saving original pages to a `-journal` and overwriting the database, a writer appends changed pages and a commit record to `app.db-wal`, so readers can keep reading the unchanged pages in the main file. `app.db-shm` is the **wal-index**: a small memory-mapped file that tells readers where the newest copy of each page sits in the `-wal`. A **checkpoint** later copies those pages back into the main file, automatically once a commit brings the WAL to 1000 pages, and the last connection to close checkpoints and deletes both files. Until then the `-wal` is part of the database: a committed transaction may exist only there, so deleting it, or copying `app.db` without it, can drop transactions or corrupt the file. To clear a leftover `-wal`, open the database with SQLite and close it.
go deeper
Recall the three files: the database, the -wal holding committed changes, and the -shm index over it. Say clearly that the -wal can hold the only copy of recent commits.
Explain the inversion: WAL appends changed pages and a commit record instead of overwriting the database, and a checkpoint at 1000 pages copies them back. Mention that WAL mode persists in the file.
Show the operational habit: never move, sync or delete the database file apart from a live -wal, clear a leftover -wal by opening and closing the database, and keep WAL off network filesystems.
Treat WAL as a standing deployment choice: it changes what a backup must capture, needs all processes on one host, and turns one file into two or three while open, which affects tooling and runbooks.
## What WAL mode changes SQLite has two ways to make a transaction atomic. The default is a **rollback journal**: before a writer changes a page in the database file, it copies the page's original content into a separate `-journal` file, and if anything goes wrong that original content is played back. **WAL mode**, enabled with `PRAGMA journal_mode=WAL`, inverts this. The database file keeps the original pages, and every changed page is **appended** to a separate write-ahead log file instead. A transaction commits when a commit record is appended to that log, so a commit never has to touch the main file at all. That inversion is why WAL mode exists: - readers keep reading the unchanged pages in the main file while a writer appends to the log, so a reader and the writer do not block each other (how many of each can run at once is a locking subject of its own); - a write transaction writes each changed page once, where a rollback journal writes it twice, and the writes are sequential appends; - a commit needs many fewer `fsync()` calls than with a rollback journal. ## The three files For a database called `app.db` in WAL mode, up to three files exist on disk. | File | What it holds | When it exists | |---|---|---| | `app.db` | The database pages, as of the last checkpoint | Always | | `app.db-wal` | Changed pages and commit records appended since the log was last reset | While any connection has the database open, and after a crash | | `app.db-shm` | The **wal-index**: where the newest copy of each page sits in the `-wal` | While any connection has the database open, and possibly after a crash | The `-wal` file holds real data: a transaction that has committed but has not yet been checkpointed exists **only** there. The `-shm` file holds no rows. It is an index that lets a reader find the newest copy of a page in the log without scanning the whole log, implemented as an ordinary file in the database's directory that every process maps into memory. It rarely exceeds 32 KiB and is never synced. Because it is shared memory, every process using the database must run on the same host, which is why WAL mode does not work over a network filesystem. ## Checkpoints bring the changes home Copying logged pages back into the main file is called a **checkpoint**. By default SQLite runs one automatically whenever a commit brings the log to 1000 pages or more, and the last connection to close the database runs a final checkpoint and then deletes both the `-wal` and the `-shm` file. That is why a cleanly closed WAL database is, once again, a single file. Once a checkpoint has copied everything and no reader still needs the log, the next writer starts again at the beginning of the `-wal`, reusing it rather than growing it forever. ## Why deleting or separating the files is dangerous SQLite's documentation is blunt: 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, transactions that were previously committed might be lost, or the database might become corrupted. The usual ways people do it: 1. deleting `app.db-wal` because it looks like a temporary log and has grown large; 2. copying or syncing only `app.db` to another machine or a backup folder; 3. restoring an older `app.db` while the live file's newer `-wal` is still next to it. After a crash the leftover `-wal` is a **hot WAL file**: the next connection to open the database uses it to recover. The only safe way to remove a WAL file, according to the documentation, is to open the database with an SQLite connection and then close it, letting SQLite checkpoint and clean up. ## Turning WAL mode on and off Unlike every other journal mode, WAL is **persistent**. Once one connection sets it, the database stays in WAL mode for every connection and after it is closed and reopened. SQLite records this in the database header: bytes 18 and 19, the file-format write and read versions, change from 1 to 2, so that a library older than 3.7.0, which does not understand WAL recovery, reports an error along the lines of "file is encrypted or is not a database" instead of making matters worse. `PRAGMA journal_mode=DELETE` switches back and resets those bytes to 1. Practical consequences: - `PRAGMA journal_mode=WAL` returns the mode actually in force; if it returns the previous mode, such as `delete`, the conversion failed, for example because the VFS cannot provide shared memory; - a WAL-mode database on read-only media can be read only if its `-wal` and `-shm` files already exist, or can be created in its directory, or it is opened as immutable, so convert a copy to `DELETE` mode before shipping it read-only; - a cleanly closed WAL database is a single file again, but a database that is still open, or whose last user crashed, is two or three files that belong together.
- Why does SQLite keep the wal-index in a -shm file next to the database instead of anonymous shared memory?Every process must see the same shared memory. Early versions put it in places like /dev/shm, where processes with a different root directory saw different files and corrupted the database, and nameless shared memory is not portable. Memory-mapping a file in the database's own directory is the only way SQLite found to guarantee that. The file is small, never synced and deleted at the last close.
- What happens if a library older than 3.7.0 opens a SQLite database left in WAL mode?WAL mode sets header bytes 18 and 19 to 2, which pre-3.7.0 libraries do not accept, so they report an error such as 'file is encrypted or is not a database' rather than trying a recovery they cannot do. Running PRAGMA journal_mode=DELETE with a newer library sets the bytes back to 1 and makes the file readable by old versions again.
A printed ledger with a stack of sticky-note amendments clipped to it. Readers check the notes before trusting a printed page, and now and then a clerk copies the amendments into the ledger and clears the notes. Throw the notes away early and the amendments they held are simply gone.
- The -wal file is just a temporary log, so deleting it frees space safely.
- The -shm file holds rows that have not reached the main file yet.
- WAL mode has to be switched on again by every new connection.
- In WAL mode, copying only the .db file captures every committed transaction.
- WAL mode works fine for processes on different hosts sharing a network drive.