SQLite vs. the File System

You have to call fsync after you write the temporary file to avoid data corruption unless btrfs is mounted with the flushoncommit option. Without it btrfs flushes, but without waiting for completion.

https://git.kernel.org/pub/scm/linux/kernel/git/torvalds/lin...

I guess an approach like SQLite's WAL is faster for this.

https://www.sqlite.org/wal.html

The only thing to keep in mind is that you can cause SQLite's WAL to keep growing with concurrent writes, as the checkpointer can checkpoint the WAL, but cannot overwrite it. There is a WAL2 branch of SQLite that tries to fix this.

I threw together a little test today to see which was faster SQLite or the file system (btrfs on Fedora). The original claims of SQLite outperformance are pretty dated. TL;DR; depending on your usage, SQLite is probably a bit faster, so the claims seem to hold up.