I need r/w access to a sqlite database of several GBytes. Putting it on the SD card is out of the question as the writing would wear out the SD too quickly. This is why I was thinking of network file systems, like sshfs, samba or nfs. Now, I know this is a no-no for sqlite, due to latency and locking issues, but I'd be surprised if some of hadn't tried nonetheless. Which of the available network file systems on the RPi works best with sqlite, even though it's a no-no to use one? Please, don't suggest switching away from sqlite.
closed as primarily opinion-based by Steve Robillard, Milliways, Ingo, goldilocks♦ Dec 4 '18 at 19:13
Many good questions generate some degree of opinion based on expert experience, but answers to this question will tend to be almost entirely based on opinions, rather than facts, references, or specific expertise. If this question can be reworded to fit the rules in the help center, please edit the question.
As far as network file system go, reasonable options are AFAIK limited to NFS and SMB, and unless you need SMB features (like Windows compatibility) you should really go with NFS. It's much faster (and CPU time is something you'd rather spare on an RPi) and somewhat more stable than SMB. NFS v4 supports file locking.
Attaching a USB drive seems a waste of money to me
When I have a problem that can be solved by throwing some $30 on it, I usually buy my way out.
I log data to an SQLite3 database on a NFS filesystem with no problems. The only rule is one writer, multiple readers (to avoid the locking/locked database problem). To enforce that my reader programs use
There's some code at https://github.com/DougieLawson/RaspberryPi that demonstrates that.