MEASUREMENT

What SQLite's durability settings cost on a home server

Thirty-six configurations measured, and one flag that changes the answer by a factor of ninety.

By Mikhaila Molina · 21 August 2026 · script and raw output

If you run anything at home, you run SQLite. Home Assistant keeps its recorder history in it. Immich holds thumbnails and job state around it. Paperless, Miniflux, Vaultwarden, Gitea in its default setup, most of the smaller things on your box — all SQLite, and all of them chose journal_mode and synchronous on your behalf, usually without telling you.

Those two settings decide what happens to the last few minutes of your data when the power goes out. They also decide how fast the application writes, and the gap between the safe setting and the fast one is much wider than most defaults suggest. This is what each combination actually costs.

What the settings promise

SQLite's own documentation is unusually direct about the trade, so it is worth quoting rather than paraphrasing. On synchronous=NORMAL in write-ahead-log mode:

WAL mode is always consistent with synchronous=NORMAL, but WAL mode does
lose durability. A transaction committed in WAL mode with synchronous=NORMAL
might roll back following a power loss or system crash. Transactions are
durable across application crashes regardless of the synchronous setting
or journal mode.

That last sentence is the one people miss. If the application crashes — the container is killed, the process segfaults, you restart the stack — your committed data is safe at every setting. Durability only comes into question when the machine stops: a power cut, a kernel panic, a yanked plug. The pragma documentation gives the full matrix: with a rollback journal, FULL is "maybe not durable" and you need EXTRA; in WAL mode, FULL is fully ACID and NORMAL is "maybe not durable".

There is a third flag almost nobody sets, and on a Mac it is the one that matters most. PRAGMA fullfsync selects Apple's F_FULLFSYNC instead of a plain fsync(). The difference is that fsync() on macOS returns once the data has been handed to the drive, not once the drive has committed it to stable storage; F_FULLFSYNC asks the drive to actually flush. SQLite's documentation states the default plainly: "The default value of the fullfsync flag is off."

So on macOS, out of the box, synchronous=FULL does not mean what its name implies. The obvious question is what it would cost if it did.

How this was measured

Results

Three things the numbers say

1. Transaction size beats every other setting, by a lot

The safest configuration measured here — rollback journal, synchronous=FULL, fullfsync=ON — does 68 rows per second one row at a time, and 66,188 rows per second in batches of a thousand. Same durability guarantee, same disk, nearly a thousand times the throughput. Every commit is a sync; batching a thousand rows means one sync instead of a thousand.

This is why an app that feels slow on a Raspberry Pi is usually not suffering from the SD card. It is committing per row. Nothing you change in synchronous will recover what an autocommit loop is throwing away.

2. WAL is worth turning on before anything else

At one row per transaction with the same synchronous=FULL promise, WAL does 19,161 rows per second against the rollback journal's 4,781 — four times faster for a strictly stronger guarantee, since FULL is fully ACID in WAL mode and only "maybe durable" with a rollback journal. The one real cost is the one SQLite lists first among WAL's disadvantages: it does not work over a network filesystem. If your self-hosted app's database lives on an NFS or SMB share — a common enough arrangement when someone points a container's data directory at the NAS — WAL is not available to you, and that is a reason to move the database onto local storage rather than a reason to leave WAL off.

3. On macOS, synchronous=FULL costs seventy times more than it appears to

This is the result worth the measurement. With the default fullfsync=OFF, rollback journal plus synchronous=FULL writes 4,781 rows per second. Turn on fullfsync — that is, ask the drive to genuinely flush, which is what a reader assumes FULL already means — and the same configuration writes 68. In WAL mode the gap is wider still: 19,161 down to 211, a factor of ninety.

The control case confirms the flag is doing what it claims rather than adding noise. At synchronous=OFF, where SQLite issues no syncs at all, turning fullfsync on changes nothing measurable: 8,200 against 8,632 rows per second on a rollback journal, 116,083 against 119,920 in WAL. The flag only costs something where a sync was going to happen anyway, which is exactly the behaviour the documentation describes.

The practical reading is not "turn fullfsync on". It is that on a Mac the default performance of synchronous=FULL is quietly bought with a weaker guarantee than the name suggests, and if you actually need the strong one, the price is a factor of seventy and you should be batching your writes before you pay it.

This is a macOS-specific mechanism. On Linux the equivalent question is whether the drive honours a cache flush and whether the filesystem was mounted with barriers, which is a different measurement and is not one this run makes any claim about.

What to set

For a self-hosted app on local storage, in order of how much it buys you:

• Batch your writes if you control the code. This is worth more than the other two settings combined.
• journal_mode=WAL unless the database file lives on a network share. Faster and a stronger guarantee at the same time, which is rare enough to take when it is offered.
• synchronous=NORMAL in WAL mode. This is SQLite's own recommendation — "the best balance between performance and safety for most applications running in WAL mode" — and it measured at 53,969 rows per second against FULL's 19,161. What you give up is the last transaction or two if the machine loses power. For a photo library or an RSS reader that is nothing. For a password vault, take FULL.
• A UPS makes this whole argument mostly academic, and costs less than the time spent tuning around its absence.

What this does not tell you

• It measures speed, not crash safety. Nothing here was tested by cutting power. The durability claims above are SQLite's, from its documentation, not results of an experiment run here. Verifying them properly means pulling a plug repeatedly, which is a different piece of work.
• One machine, one disk. An M4 with internal NVMe is not a Raspberry Pi with an SD card or a NAS with spinning disks. The absolute numbers will be much lower on those; the ordering between configurations should hold, because it comes from how many syncs each one performs rather than from how fast this particular drive is.
• SQLite 3.38.4, the version bundled with the Python in use here — not the newest. The pragmas measured have behaved this way for many versions, but a newer build could differ.
• One run per configuration. With differences of 70× that is enough to rank them; it is not enough to argue about a 5% gap between two neighbouring rows.
• Writes only. Read performance, concurrency under mixed load, and checkpoint stalls on a large WAL are all real concerns for a busy self-hosted app and none of them are measured here.

The script and the full JSON output are on the raw data page. If it reproduces differently on your hardware, that result is more interesting than this one — send it over.