SQLite WAL mode: how it works, checkpoints and tuning

Frames appended after the end mark are invisible to the reader, so it sees the database as it was when its transaction began, however many commits happen meanwhile.

To tell checkpoints what is still in use, each reader takes a shared lock on one of five read marks in the wal-index header. Readers share marks, so five is not a limit on reader count. Read mark 0 means "I read only from the database file and ignore the WAL", which is possible when everything has been checkpointed. The others hold a frame number, and a reader holding one is promising to use the WAL for pages changed up to that frame.

Coordination uses byte-range locks on the -shm file:

Readers and the writer take different locks, so they do not block each other. Two writers want the same lock, so WAL still allows only one write transaction at a time. See SQLITE_BUSY error: what it means and what causes it for how that surfaces.

What a checkpoint does

A checkpoint copies pages from the WAL into app.db, in page order, keeping only the newest version of each page. It stops at the lowest read mark any connection holds, because writing later pages into app.db would change data underneath a reader that expects to find its snapshot there. The progress counter (nBackfill in the wal-index header) records how far it got, so the next checkpoint resumes from that point.

When nBackfill equals mxFrame and no reader is using the WAL, the next writer resets the WAL: it starts writing frames from the beginning of the file again. The file is reused, not shortened. This is the mechanism that keeps the WAL bounded, and it only fires when a checkpoint has fully caught up.

A checkpoint is also where the fsync cost lands. The WAL is synced before pages are copied, and the database file is synced before the WAL can be reset.

Checkpoint modes

Run one from SQL:

PRAGMA wal_checkpoint(TRUNCATE);

The pragma returns one row with three columns:

A result such as 0|9292|117 from a PASSIVE checkpoint says the WAL has 9,292 frames and only 117 could be copied. Some reader is holding a snapshot at frame 117.

Three behaviours to know:

• The waiting in FULL, RESTART and TRUNCATE is done by the busy handler. On a connection with no busy_timeout they give up immediately and behave like PASSIVE, returning 1 in the first column. Set a timeout on the connection that runs them.
• Only one checkpoint can run at a time. A second one gets SQLITE_BUSY without the busy handler being called.
• A connection cannot checkpoint from inside its own open transaction. The attempt fails with SQLITE_LOCKED.

Automatic checkpoints

Every connection starts with wal_autocheckpoint set to 1000. After a commit that leaves 1000 or more frames in the WAL, that connection runs a PASSIVE checkpoint in the same thread, before the commit call returns. With the default 4096-byte page size the WAL therefore levels off at about 4 MB.

PRAGMA wal_autocheckpoint;          -- read the current threshold, in pages
PRAGMA wal_autocheckpoint = 4000;   -- checkpoint less often
PRAGMA wal_autocheckpoint = 0;      -- disable; you must checkpoint yourself

The setting is per connection and is not stored in the database. An occasional commit is much slower than the rest, because it pays for the checkpoint. And automatic checkpoints, being PASSIVE, never wait for a reader, so they cannot fix a WAL that a long reader is pinning.

A larger threshold means fewer checkpoints and faster writes on average, but slower reads, because read cost rises with WAL size. If commit latency spikes matter, disable automatic checkpoints on the request-serving connections and run checkpoints from a background thread or process on its own connection.

Why the WAL grows without bound

The SQLite documentation lists three causes.

Automatic checkpoints are disabled and nothing else runs them.

Checkpoint starvation. If there is always at least one read transaction open, no checkpoint ever reaches the end of the WAL, so the WAL is never reset. It does not take one long query. A pool of connections serving overlapping short reads has the same effect, as does a single cursor that was never read to the end.

A very large write transaction. The WAL cannot be reset in the middle of a transaction, so one transaction that rewrites most of a large table produces a WAL of similar size.

The fixes, in order of preference:

1. Remove the long-lived read transactions. Close cursors, and do not hold a transaction open across a request boundary or while waiting on the network.
2. Schedule PRAGMA wal_checkpoint(RESTART) or TRUNCATE on a connection with a busy timeout. These block new writers while they wait, which creates the gap in which the checkpoint can finish. Existing readers keep running, though the WAL documentation warns that readers might block while such a checkpoint runs.
3. Break huge writes into several transactions.

A WAL that has grown stays that size on disk after a successful checkpoint, because the file is reused rather than shortened. To cap what is left behind:

PRAGMA journal_size_limit = 67108864;   -- bytes; here 64 MiB

Each time the WAL is reset, SQLite truncates it to this limit if it is larger. The default is -1, meaning no limit. Zero truncates it to the minimum every time. It does not stop the WAL growing while a reader blocks checkpoints. Like wal_autocheckpoint, it is per connection.

synchronous: what NORMAL gives up

With NORMAL you keep atomicity, consistency and isolation and give up durability for the most recent transactions. How many is not fixed: it is whatever had been committed and not yet synced when the power failed. A crash of the application alone loses nothing in any mode, because the data had already been handed to the operating system.

That is acceptable for most web applications and unacceptable when a commit acknowledges something that cannot be replayed, such as a payment. synchronous is a per-connection setting, so set it on every connection. Some builds change the default for WAL databases with the SQLITE_DEFAULT_WAL_SYNCHRONOUS compile-time option, so check with PRAGMA synchronous; instead of assuming.

On macOS an ordinary fsync() does not guarantee that the drive has flushed its own cache. PRAGMA fullfsync = ON makes SQLite use F_FULLFSYNC for all syncs, and PRAGMA checkpoint_fullfsync = ON for checkpoint syncs only. Both are off by default.

Backups and copying files

Copying app.db with cp while the application runs is wrong twice over: recent commits are only in the WAL, and the copy can catch the file in the middle of a checkpoint. Three methods are safe on a live database:

sqlite3 app.db ".backup '/backups/app.db'"          # online backup API
sqlite3 app.db "VACUUM INTO '/backups/app.db'"      # compacted snapshot; target must not exist
sqlite3_rsync app.db user@host:/backups/app.db      # SQLite 3.47.0 and later

Notes on each:

• The backup API copies page by page and yields a WAL-mode copy of a WAL-mode database.
• VACUUM INTO writes a consistent, defragmented snapshot. The output file is in rollback-journal mode even when the source is in WAL mode, so run PRAGMA journal_mode = WAL on it if you restore from it.
• sqlite3_rsync copies over SSH with a bandwidth-efficient protocol, and other programs can keep writing to the origin while it runs.

A plain file copy is safe only when no transaction is in progress for its whole duration, and the -wal file must be copied with the database if it exists. The simplest guarantee is to stop the application so the last connection closes cleanly and removes the WAL. An atomic filesystem or volume snapshot that captures app.db and app.db-wal at the same instant is equivalent to a power-loss image, which SQLite recovers from when the copy is next opened.

Read-only access

Reading a WAL-mode database still involves the -shm file, so "read-only" needs care. From SQLite 3.22.0, a database that cannot be written can still be read if one of these holds:

• The -wal and -shm files already exist and are readable.
• The directory is writable, so they can be created.
• The connection is opened with the immutable=1 URI parameter.

Otherwise the open fails with "attempt to write a readonly database". A read-only connection (file:app.db?mode=ro) creates the two files if it has to, and does not remove them when it closes.

immutable=1 tells SQLite the file cannot change, so it skips all locking and change detection. Use it only for files that truly cannot change, such as a read-only image. On a database that something is writing, it can return wrong results or corruption errors. Before shipping a database on read-only media, switch it to journal_mode = DELETE.

The shared-memory requirement

Every process using a WAL database must map the same -shm file, so they must all run on the same host. That rules out network filesystems: clients on different machines cannot share memory, whatever their locking does.

One escape hatch exists for a VFS without shared-memory support. If a connection sets PRAGMA locking_mode = EXCLUSIVE before first touching the WAL database (SQLite 3.7.4 and later), it keeps the wal-index in heap memory and no -shm file is created. The cost is that no other connection can open the database at all.

Monitoring

• Size of the -wal file. Alert when it exceeds a multiple of the expected ceiling (wal_autocheckpoint × page size).
• The gap between columns 2 and 3 of PRAGMA wal_checkpoint(PASSIVE). A persistent gap means a reader is pinning the WAL.
• Blocked checkpoints. Log when column 1 of a scheduled RESTART or TRUNCATE returns 1.

A small job that reports all three:

import os, sqlite3

def checkpoint(path, limit_bytes=64 * 1024 * 1024):
    """Checkpoint; escalate to TRUNCATE when the WAL is over limit_bytes."""
    wal = path + "-wal"
    size = os.path.getsize(wal) if os.path.exists(wal) else 0
    mode = "TRUNCATE" if size > limit_bytes else "PASSIVE"
    conn = sqlite3.connect(path, timeout=10.0, isolation_level=None)
    try:
        busy, log, done = conn.execute(f"PRAGMA wal_checkpoint({mode})").fetchone()
    finally:
        conn.close()
    return {"wal_bytes": size, "mode": mode, "blocked": bool(busy),
            "wal_frames": log, "checkpointed_frames": done}

print(checkpoint("app.db"))

In C, sqlite3_wal_hook() gives a callback after every commit with the current WAL size in pages. The automatic checkpoint is itself implemented with that hook, so registering your own replaces it.

A version to check

SQLite versions 3.7.0 through 3.51.2 contain a rare data race, which the project calls the WAL-reset bug, that can corrupt a WAL-mode database when two connections write or checkpoint at the same instant. It is fixed in 3.51.3, with backports in 3.44.6 and 3.50.7. The developers describe it as unlikely in practice and not an emergency. Check which version your driver bundles with SELECT sqlite_version();.

Common mistakes

• Deleting -wal and -shm to "clean up". That can discard committed data. Close the last connection and SQLite removes them.
• Scheduling PASSIVE checkpoints to shrink a WAL. Automatic checkpoints are already PASSIVE. Only RESTART or TRUNCATE wait for readers.
• Running RESTART or TRUNCATE without a busy timeout, then wondering why it reports blocked every time.
• Trying to change page_size in WAL mode. It cannot be changed there, even with VACUUM. Switch to a rollback mode first.

For the lock-error side of WAL, How to fix SQLITE_BUSY: a step-by-step playbook has the procedure, and SQLite in production: WAL mode and knowing when it's enough covers when to move to a client/server database.

Checklist

1. The database, -wal and -shm live on a local filesystem and are always moved together.
2. PRAGMA synchronous is set deliberately on every connection, and you know what a power loss costs.
3. No read transaction stays open longer than a request; cursors are closed.
4. A scheduled RESTART or TRUNCATE checkpoint runs on a connection with a busy timeout, and its result row is logged.
5. journal_size_limit caps the WAL left on disk.
6. -wal file size is monitored.
7. Backups use the backup API, VACUUM INTO or sqlite3_rsync, never a bare copy of the main file.
8. The bundled SQLite is 3.51.3 or later, or a patched backport.

References

• Write-ahead logging (sqlite.org)
• WAL-mode file format: the wal-index, read marks and locks
• Database file format: the write-ahead log
• sqlite3_wal_checkpoint_v2(): checkpoint modes
• PRAGMA wal_checkpoint, wal_autocheckpoint, journal_size_limit and synchronous
• sqlite3_wal_hook()
• SQLite backup API, VACUUM INTO and sqlite3_rsync
• URI filenames: mode=ro and immutable
• How to corrupt an SQLite database file

SQLITE_BUSY error: what it means and what causes it

What SQLITE_BUSY means, where it is returned in rollback-journal and WAL mode, why a timeout sometimes does nothing, the extended codes, and how to find who holds the lock.

#concurrency#internals#locking