SQLite Forum

Network filesystems for sqlite DBs
> Quite a bit of it is stored once and then only read later and I didn't really see that as the problem

That will perform much better on an SSD than your test predicts.

> We also have a fair amount of persistent state for a client application, state that is constantly being modified to reflect what the user is doing

I can tell you from experience that you want to batch that up and save many state changes all at once. You might have an event that fires once a second which gathers all of the "dirty" state records and shoves them into a single transaction. Committing that transaction will carry a *lot* less overhead than changing one POD value (int, char, bool, string...) per DB hit.

> they expect to be preserved if they move away from the native app and use the same account on the web-app.

That's where my "1 second" value above comes from: long enough that you're likely to get multiple users' updates in each pass, yet short enough that there's no way a user is going to switch back and forth fast enough to notice a discrepancy.

You might push it to 5-15 seconds, depending on testing.

> if the database-locking was problematic over a network filesystem, though, that this might be a test that would show that up.

Only if you run it from multiple nodes. Two processes running their requests through a single node's NFS implementation is going to make that single NFS stack honor locking and such for both processes. It isn't until you force coordination of locking through the NFS *server* on the other end of the network pipe that you test the actual issue here, the one [the FAQ warns against](https://sqlite.org/faq.html#q5).

(drh is one of those graybeards I was speaking of. Why are you second-guessing him on this?)

> It still seems odd though.

Informed guess: you aren't testing against NVMe on the server side of the NFS connection.

It's an apples-to-zebras test to compare NFS vs NVMe unless what you actually mean is NVMe via NFS vs local NVMe. You get sent off into the weeds when you try to reason from your observation that the apples lack stripes and that the zebras, while they *do have* stripes, do not exhibit the expected deep red and pale yellow stripe pattern.

> covid and the entire world burning means I'm working at home

There are several organizations that will happily rent you a Gluster or Ceph cluster for an hour or two.