SQLite Durability Settings: WAL, synchronous, and When to Use FULL

SQLite lets you trade durability for speed, and most default advice ("use WAL, synchronous=NORMAL, done") hides an important decision. Here is what the settings actually do, and when to reach for the stricter one.

WAL mode

WAL (write-ahead logging) changes how commits are written: new data goes to a -wal file and is merged into the main file later. The wins:

• Readers and a writer do not block each other. Reads keep working while a write is in flight.
• Small writes are cheap. The main file is not rewritten on every commit.

For a web service, WAL is almost always the right choice.

synchronous levels

synchronous controls how much the database insists the OS actually flushed data to disk before a commit returns:

• synchronous=NORMAL (the WAL-mode default): the transaction is safe from application crashes and power loss only up to a point — the WAL is not fsynced on every commit, but on checkpoint. You can lose the last few commits on a power failure.
• synchronous=FULL: every commit is fsynced before returning. Slower, but a commit that reported success will survive a power loss.

How I choose

• Default: WAL + NORMAL. For most services this is the sweet spot — fast, and crash-safe enough that losing the tail of a write log is an acceptable edge case.
• Switch to FULL when you cannot afford to lose even the last committed write — financial records, orders, anything with real consequences. The slowdown is real but bounded.
• And still back up either way. Durability settings reduce the window of data loss; they do not replace backups. If your data matters, FULL plus a VACUUM INTO schedule is the honest combination.

The settings are one line in your driver's DSN. Measure the performance cost on your workload before you decide. And size the database you are protecting with the estimator below.