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.
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.
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.
2 comments
[ 4.2 ms ] story [ 14.9 ms ] threadhttps://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.