`matrix-sdk-sqlite` never sets `PRAGMA synchronous`, so every store commit fsyncs (WAL + `synchronous=FULL`)

[ajkessel]

Summary

matrix-sdk-sqlite opens its stores in WAL mode but never sets PRAGMA synchronous. Every connection therefore inherits SQLite's compiled default, FULL, which in WAL mode means an fsync of the WAL on every commit. The widely recommended pairing for WAL is synchronous=NORMAL, which syncs only at checkpoints.

There is currently no way for a downstream consumer to change this: synchronous is per-connection and not persisted in the database file, so setting it on our own connection has no effect on the pool the SDK opens, and SqliteStoreConfig/RuntimeConfig expose only optimize, cache_size, and journal_size_limit.

Evidence

journal_mode = wal is set explicitly (crypto_store.rs:280, and equivalently for the other stores), but:

$ grep -rn 'synchronous' matrix-sdk-sqlite-0.18.0/src/ | wc -l
0

Note that reading PRAGMA synchronous with the sqlite3 CLI against a store file is not a valid check here — it reports the CLI connection's own value, not the SDK pool's. The absence in source is the actual evidence.

Why NORMAL is the right default for these stores

From the SQLite WAL documentation:

... with synchronous=NORMAL ... transactions 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. The synchronous=NORMAL setting is a good choice for most applications running in WAL mode.

The failure mode under NORMAL is bounded and benign for this SDK specifically:

• No corruption risk. WAL mode is corruption-safe at NORMAL; the risk is losing recently committed transactions, not a damaged database.
• Application crashes are still fully durable. Only OS crash or power loss can lose a commit.
• The data is re-derivable. The state store, event cache, and media store are caches of homeserver state and re-sync on next start. That is precisely the profile SQLite describes NORMAL as suited to.

The crypto store is the one that warrants discussion, since losing a committed olm/Megolm write is more than a cache miss. That may argue for keeping FULL there specifically while relaxing the others — but currently the choice isn't available at all, for any store.

Impact

This matters most for deployments the SDK's defaults weren't tuned for. I'm running a self-hosted personal server on the SDK; its data directory sat on a 4-disk RAID5 array, where sub-stripe writes cost read-old-data + read-old-parity + write-data + write-parity. I measured 3.6x write amplification — 12.9 KB/s logical at the md device became ~46 KB/s across the four member disks. An fsync per commit is the worst possible pairing with parity RAID or spinning disks.

I can't quantify the FULL → NORMAL delta, precisely because the setting isn't reachable from outside the SDK. The argument here is structural rather than benchmarked. (Separately, I found the cross-process lock's lease renewal was committing ~4–7 times/sec on an idle server — see #6114, where I've posted measurements. That determined commit frequency; synchronous determines the cost of each one.)

Proposal

In rough order of preference:

1. Add synchronous to RuntimeConfig, alongside cache_size and journal_size_limit, with a SqliteStoreConfig::synchronous(...) setter. This unblocks downstream consumers regardless of what default the SDK picks.
2. Default to NORMAL for the state, event cache, and media stores, given WAL is already the journal mode and those stores are re-derivable caches.
3. Decide the crypto store separately — a case can be made for leaving it at FULL, and having the knob from (1) means that decision can be revisited without another release cycle.

Happy to open a PR for (1) if the approach sounds reasonable, and to test any of this on real spinning-disk / parity-RAID hardware.

Environment

• matrix-sdk / matrix-sdk-sqlite 0.18.0
• Ubuntu 26.04 LTS, ext4 on 4-disk RAID5 (mdadm), also reproduced against NVMe
• Single-process server consumer, SQLite stores, sliding sync

[Hywan]

Link to the SQLite specification: https://sqlite.org/pragma.html#pragma_synchronous