Does SQLite still not support remote connections? Without that, it's hard for me to take it seriously for most RDBMS use cases.
It's like asking if Python still doesn't support running without a garbage collector.
It's a serious database for anything that doesn't require concurrent multiuser access.
sqlite.com seems a pretty good cut off traffic volume, I would guess between 95-99% of all web apps are probably consistently getting less traffic than sqlite.com
Though a cache may have been what most of the traffic is hitting.
Most of the dynamic content is generated by the version control system, Fossil (https://fossil-scm.org/). For example: https://sqlite.org/src/timeline or https://sqlite.org/forum/forum
Fossil is hosted on the same machine as SQLite. Fossil is self-hosting and the Fossil website is 100% dynamically generated. Every HTTP request against https://fossil-scm.org/ does about 200 SQLite queries (give or take - depending on the page).
Since only a single process may write to a database, it would be best if that process were local.
NFSv4 has built-in file locking, which is likely the most preferable protocol. SMBv2 is a massive rewrite of that protocol, and I expect any file locking problems would be addressed (especially as Microsoft sponsored SQLite changes specifically for Windows 10).
Remote readers in explicit read-only mode likely won't hurt, but there is a deeper discussion below.