Blog
4 min read

SQLite WAL Mode: Faster Writes and Readers That Don't Block

Write-ahead logging (WAL) mode is the single most important setting for using SQLite in a web app: readers no longer block the writer, writes get faster, and "database is locked" errors mostly disappear. How to turn it on, the companion pragmas, checkpoints, the -wal and -shm files, safe backups, and its limits.

SQLite's default journal mode works well for a desktop app with one user. On a web server, readers and a writer can overlap during parts of a transaction, but committing a write needs an exclusive lock that can cause contention and database is locked errors. WAL mode improves that concurrency. (SQLite in Production)

Turn it on

PRAGMA journal_mode = WAL;

Unlike most pragmas, this one is persistent — it's stored in the database file, so you only need to set it once (though setting it on every connection is harmless).

The companion settings, applied on every connection when your app starts:

PRAGMA busy_timeout = 5000;       -- wait up to 5s for a lock instead of failing immediately
PRAGMA synchronous = NORMAL;      -- faster; recent commits can be lost on power failure
PRAGMA foreign_keys = ON;         -- off by default in SQLite!

In WAL mode, NORMAL preserves database consistency, but a power failure or operating-system crash can lose recent committed transactions. Use synchronous = FULL when every acknowledged commit must survive those failures. An application crash alone does not have the same durability trade-off. See SQLite's synchronous documentation.

In Node.js with better-sqlite3:

import Database from 'better-sqlite3'
const db = new Database('app.db')
db.pragma('journal_mode = WAL')
db.pragma('busy_timeout = 5000')
db.pragma('synchronous = NORMAL')
db.pragma('foreign_keys = ON')

What WAL changes

Rollback journal (default): to change the database, SQLite copies the original pages to a journal file, then writes changes directly into the database file. The writer needs an exclusive lock before modifying the main file and completing the commit, so readers can delay it and be blocked during that phase.

WAL: changes are appended to a separate -wal file. Readers keep reading the main file plus whatever was committed to the WAL when their read started. So:

  • Readers don't block the writer, and the writer doesn't block readers.
  • Writes are faster — appending is cheaper than rewriting pages in place, and with synchronous = NORMAL there are fewer disk syncs.
  • Still one writer at a time. WAL doesn't allow parallel writes; concurrent writers queue (that's what busy_timeout is for). Keep write transactions short.

The extra files

In WAL mode you'll see:

  • app.db — the main database.
  • app.db-wal — recent committed changes not yet copied back.
  • app.db-shm — a shared-memory index for the WAL.

Never delete the -wal file while the database is in use — it may contain committed data that hasn't been copied into the main file yet.

Checkpoints

Periodically, SQLite copies changes from the WAL back into the main file — a checkpoint. By default this happens automatically when the WAL reaches about 1000 pages. If a long-running read transaction is always open, checkpoints can't complete and the WAL file keeps growing. Watch for that, and you can trigger one manually:

PRAGMA wal_checkpoint(TRUNCATE);

Backups: don't just copy the file

Copying app.db with cp while the app is running can miss data still in the WAL, or catch a half-written state. Use SQLite's own tools:

sqlite3 app.db ".backup 'backup-2026-10-09.db'"
# or
sqlite3 app.db "VACUUM INTO 'backup-2026-10-09.db'"

For continuous off-site backup, tools like Litestream stream the WAL to object storage. (Backups for Beginners, The 3-2-1 Backup Rule)

Limits

  • Same machine only. WAL relies on shared memory, so it doesn't work on network file systems (NFS, SMB). All processes must be on one host.
  • One writer. Heavy, sustained concurrent writes are where Postgres pulls ahead. (SQL vs NoSQL, Postgres vs MySQL)
  • Very large transactions make the WAL file large until the next checkpoint.

When to move to Postgres

SQLite in WAL mode handles a surprising amount of traffic for read-heavy apps on one server. Move to Postgres when you need multiple app servers, heavy concurrent writes, or features like row-level security, LISTEN/NOTIFY and rich extensions. (Best Database for an AI-Generated App)

The summary

  • PRAGMA journal_mode = WAL lets readers and the writer work at the same time.
  • Also set busy_timeout, synchronous = NORMAL and foreign_keys = ON per connection.
  • Keep the -wal file, back up with .backup or VACUUM INTO, and stay on one machine.

EasySpawn gives each app a persistent server with fast local disk and daily backups — a good home for SQLite, with Postgres right there when you outgrow it. See how it works or join the waitlist.

Related: What Is a Database? · Database Transactions Explained · Postgres MVCC Explained · fsync Explained

Keep reading