compute
guide
13 Oct 2026
One SQLite database, seven durability settings, and the 149x nobody turns on
The same 100,000 rows took 0.1 seconds in one transaction under every setting tested, and anywhere from 0.14 to 41.7 seconds in five thousand. The pragma pair is free until you commit.
By Purvansh Parmar
Checked 2026-10-21
Read 8 min
Entries 7
Every SQLite guide says to turn on WAL. Almost none of them tell you that turning on WAL alone, on a stock build, leaves the setting that actually costs you the time exactly where it was. I wrote the same hundred thousand rows seven different ways and the gap between the slowest configuration and the fastest was 299x. The gap between the default and the thing most people change it to was 3.6x. The rest of it was sitting behind a second pragma that the first one does not touch.
How this was checkedWrote the same 100,000 rows into SQLite 3.37.2 under seven pragma combinations, twice each: once inside a single transaction and once across five thousand transactions of twenty rows. In one transaction every configuration finished in about a tenth of a second and the settings were indistinguishable. Across five thousand commits the spread was 120 commits per second against 35,903. Also checked what this build does when you switch to WAL and change nothing else: synchronous stays at FULL, so the fsync per commit stays too.
What I compared on
- commits per second across five thousand transactions, which is the case the settings are charged in
- the same rows in one transaction, to show what the settings do not cost
- what each configuration actually promises if the power goes out
The pair the SQLite documentation describes as a reasonable choice for most applications.
Wins
It is the entire avaliable win minus a rounding error, and the database still cannot be corrupted by a power cut. Of every change on this list it is the one with the best ratio of speed gained to promise given up.
Loses
It gives up durability of the most recent commits: a power loss or kernel panic can roll back transactions that already returned success. An application crash cannot, but the distinction is easy to forget when writing the incident report.
17,861 commits/sec149x the default0.28 s
What you get on a stock build when you turn on WAL and change nothing else, because synchronous does not follow.
Wins
It is strictly durable and strictly faster than the default, so it is the right destination if you need a committed transaction to survive a power cut and want the best available under that constraint.
Loses
This is the trap. On this build, journal_mode=WAL leaves synchronous at FULL, so the tutorial that says turn on WAL hands you 3.6x and lets you believe you took the 149x.
428 commits/sec3.6x the defaultsynchronous stays at 2
WAL with the sync removed entirely, so commits return as soon as the write reaches the operating system.
Wins
For data you can regenerate, this is free money: a build cache, a scratch index, a derived table that gets rebuilt on startup anyway. The run is almost as fast as not committing at all.
Loses
A power cut here can corrupt the database rather than merely losing the last writes, so it is only defensible when losing the whole file is acceptable. Double the speed of NORMAL for a qualitatively worse failure.
35,903 commits/sec299x the default0.14 s
No rollback journal at all, which also means no atomic transactions and no rollback.
Wins
It is the right answer for a throwaway in-process database you are about to delete, and it makes the intent obvious to the next reader in a way that a pragma pair does not.
Loses
It is slower than the less dangerous option above it while giving up atomicity as well, so there is no configuration where this is the best available choice. A crash mid write leaves a corrupt file with no way back.
32,369 commits/secslower than WAL plus OFFno rollback
Rollback journal held in RAM, which sounds like it should remove most of the disk work.
Wins
There is one narrow case: it keeps rollback semantics while avoiding journal file creation and deletion, which matters on a filesystem where metadata operations are expensive.
Loses
It takes on real risk for no measurable gain. A crash during a write leaves the database corrupt because the journal needed to undo it was in memory, and it is still paying a sync on the database file itself.
438 commits/sec41x slower than WAL plus NORMALcorrupt on crash
The default journal mode with the sync setting lowered, which is the other half of the advice people usually follow.
Wins
It shows exactly where the win actually lives. The same pragma, the same value, is worth 1.3x in one journal mode and 42x in the other, which is the clearest argument against treating these as two independent dials.
Loses
It gives up durability on recent commits and gets almost nothing back, because the rollback journal still has to be created, written and deleted around every single commit regardless of the sync setting.
159 commits/sec1.3x the defaultsame pragma, 42x in WAL
A fresh database with no pragmas set at all: rollback journal, synchronous FULL.
Wins
It is the safest thing on the list and it is correct without anyone reading documentation, which is the right call for a library embedded in billions of devices. For bulk loads it costs nothing at all.
Loses
It is the slowest configuration available by a factor of 299, and nothing in the behaviour hints at that until you move from one transaction to many. The cost is per commit, so it stays invisible exactly until production traffic finds it.
120 commits/sec41.73 s0.12 s as one transaction
Side by side
| 1 txn | 5,000 txns | Commits/sec | vs default |
|---|
| WAL + OFF | 0.09 s | 0.14 s | 35,903 | 299x |
| journal OFF + OFF | 0.09 s | 0.15 s | 32,369 | 270x |
| WAL + NORMAL | 0.10 s | 0.28 s | 17,861 | 149x |
| MEMORY + NORMAL | 0.10 s | 11.43 s | 438 | 3.7x |
| WAL + FULL | 0.10 s | 11.69 s | 428 | 3.6x |
| delete + NORMAL | 0.11 s | 31.35 s | 159 | 1.3x |
| defaults (delete + FULL) | 0.12 s | 41.73 s | 120 | baseline |
Verdict
Use WAL plus synchronous NORMAL, which means setting both pragmas rather than one. It measured 149x the default while keeping the guarantee that the file cannot be corrupted. Setting only the first one, which is what most advice says and what every stock build leaves you with, gets you 3.6x and the comfortable feeling of having fixed it. The other thing worth internalising is where the cost lives. Every configuration here wrote a hundred thousand rows in about a tenth of a second when they went in as one transaction, so if your bulk import is slow, these pragmas are not the problem and batching is.
Sources