WAL mode, backups, and when not to use SQLite
What the write-ahead log changes, how to take a consistent backup of a live database, and the workloads where another engine is the right answer.
WAL mode in practice
In the default rollback-journal mode a writer takes an exclusive lock that also blocks readers. WAL mode writes new pages to a separate log and lets readers keep using the last consistent snapshot, so reads and writes stop blocking each other — at the cost of one extra file to manage.
PRAGMA journal_mode = WAL; -- persistent: stored in the file header
PRAGMA synchronous = NORMAL; -- safe against process crash, not against power loss
PRAGMA wal_autocheckpoint = 1000; -- pages, about 4 MB at the default page size
PRAGMA wal_checkpoint(TRUNCATE); -- force a full checkpoint and shrink the log| Mode | Reader while writing | Writer while reading | Extra files |
|---|---|---|---|
| Rollback journal | Blocked | Blocked | None |
| WAL | Yes — reads the snapshot | Yes, unless it needs a checkpoint | -wal and -shm |
| WAL on network storage | Not reliable — shared memory is required | Not reliable | Locking breaks |
- A checkpoint copies WAL pages back into the main database. It happens automatically, and the WAL file grows between checkpoints.
- The database is only as durable as the WAL file, so copying the main file alone while WAL mode is active gives you a torn backup.
- WAL requires a local filesystem; on network shares, keep the rollback journal.
synchronous = NORMALis the usual pairing: with WAL it protects against application crashes, at the cost of possibly losing recent commits on a power cut.
⚠️
Never copy a live SQLite database with
cp. Copy the file while writes are in flight and you get a snapshot that may not be recoverable, especially in WAL mode where the newest data lives in the -wal file.Backup and recovery
# online backup that is safe while writers are running
sqlite3 app.db ".backup 'app-backup.db'"
sqlite3 app.db "VACUUM INTO 'app-$(date +%F).db'"
# verify before trusting it
sqlite3 app-backup.db "PRAGMA integrity_check;"
sqlite3 app-backup.db "SELECT count(*) FROM measurements;"
# logical backup for diffs and review
sqlite3 app.db ".dump" > app.sql
# restore
sqlite3 restored.db < app.sql| Method | Consistent while live | Notes |
|---|---|---|
.backup / backup API | Yes | Copies page by page with the database locked correctly; the safe default |
VACUUM INTO 'file' | Yes | Also compacts; needs free space equal to the database size |
.dump | Yes | Text SQL, portable across versions, slow for very large files |
| File copy with no writers | Yes if truly idle | Also copy -wal and -shm, or checkpoint first |
| Snapshot of the volume | Depends | Only crash-consistent if the filesystem freezes I/O |
- Run
PRAGMA integrity_checkon every backup as part of the job — a backup nobody verified is a guess. - For continuous replication, tools such as Litestream stream the WAL to object storage and can restore to any point in time.
- Deleting a database means the main file, the
-walfile and the-shmfile; leaving the WAL behind can resurrect data.
When not to use SQLite
| Situation | Why SQLite is the wrong tool | Reach for |
|---|---|---|
| Many concurrent writers | Writes are serialised for the whole file | PostgreSQL or MySQL |
| Several application servers | One file cannot be shared safely over a network | A client/server database |
| Network filesystem or NFS | Locking and shared memory are unreliable | A client/server database |
| Fine-grained permissions | Access control is at the file level only | PostgreSQL roles and grants |
| Unattended remote access | No network protocol and no user accounts | A server database with TLS and auth |
| Very large binary assets | The whole file is copied by most backup tools | Object storage plus a path reference |
| Heavy schema churn | ALTER TABLE is limited; changes copy the table | A database with richer DDL |
- SQLite excels at embedded, read-mostly, single-process workloads: desktop apps, mobile apps, test suites, local analytics, edge devices.
- It also works well as an application server's database when that server is one process on one machine — a pattern that covers far more services than people assume.
- If you need horizontal scale, point-in-time recovery managed for you, or per-user access control, choose a client/server engine from the start.
Choose by access pattern, not by size of data. A few gigabytes used by one process is a perfect fit; a few hundred megabytes shared by ten application servers is not, no matter how small the numbers look.
FAQ
Is WAL mode always better?
For most local applications, yes: readers no longer block and writes are faster. The exceptions are read-only media and filesystems where the required shared-memory coordination is unavailable.
How do I shrink the database file after deleting rows?
DELETE frees pages for reuse inside the file but does not return them to the filesystem. Run VACUUM to rebuild it compactly — or VACUUM INTO a new file, which needs working space but does not disturb the original.Related
Using SQLite from application code The database in a file, and the sqlite3 CLI
Last refreshed 2026-09-18.