SQLite mentions NFS as problematic because SQLite makes a lot of use of advisory locking. Which is supposed to work on NFS, but apparently is often buggy.

Writing to a SQLite file on a remote filesystem only works with a heap of caveats: don't use WAL, and disable SHM.

SQLite is crazy fast in WAL mode, but it's because they use a memory-mapped file to coordinate consistency across multiple processes/threads. You can't mmap a remote file.

(Source: hacking in a DB replica workaround to PhotoStructure so people can store their library on arbitrary filesystems... BTW, I'd love to be wrong here: if someone's figured out how to do this, please share!)

I'm idly curious why you need to use replicas in a workaround context, and why arbitrary filesystems are a problem.

The local-replica approach is only applied when your library database is on a remote filesystem, which SQLite can't read/write to (due to the absence of shm and flock).

I think I get it now: you want people to be able to point PhotoStructure at a NAS and have it Just Work. That was the context I was missing.

I was wondering if maybe the cute hack that was posted recentlyish (https://news.ycombinator.com/item?id=27016630) to enable SQLite3 to access large remote databases over HTTP might be relevant, but that approach requires a small server to be sitting at the remote, and only implements read-only access in any case.

Now I'm wondering what an NFS/SMB translation of the above idea (which uses SQLite3's VFS hooks) might look like: ramp internal buffering to the max, use separate transient files (with eg date-based unique names) for each in-flight request, etc. Append-only journal files also come to mind...

It's crazy how flaky network filesystems are at the lower levels. Like, I use NFS at home every day, without Kerberos (which lets me enable encryption, which IIUC provides AEAD), all while knowing my data integrity entirely depends on the IP checksum computed by my 15 year old ISP router. :D

(I already know I need to upgrade. Shhh.)

Also super interested in hearing more.

Also super interested, specially handling migrations and structure/schema syncs.

Interested in more info as well.

Also, how do you handle migrations, both schema as well as data?