> https://www.sqlite.org/useovernet.html
It's not what this page says. This page suggests PostgreSQL if accessing the SQLite file from the application would induce a network connection (because of a network filesystem). Not if the application is accessed over the network.
Using SQLite for a network service when the app runs on the same machine as the SQLite file is perfectly fine.
> (unless you use WAL or rollback, in which case why not just use Postgres?)
Again. WAL / rollback is how SQLite works by default. The page does not suggest using WAL, it suggests using WAL, "but do all reads and writes from processes on the same machine that stores the database file", is you use a networked FS. And yes, in this case, you might as well use PostgreSQL indeed.
> In fact, I’d argue there’s no networked use case in which SQLite is cheaper, easier to deploy, maintain or run than Postgres.
I disagree. I now use SQLite whenever I can for low traffic services, unless the app developers strongly advise something else.I'd say there's no reason to bother with a client/server database like PostgreSQL / MariaDB / MySQL if you can help it. It's one less database to administer, one less user and password to create and manage, and the SQLite file is saved using my regular backup routine. It works great and it's less work for me. You also get to use the database engine with probably the most extensive and comprehensive test suite in the world , by far[1]. It is probably more robust than anything else. SQLite is probably capable of handling high traffic too, especially if the majority of accesses are reads and there are only a few writes.
Of course, Postgres is a really good choice too and you can't really be wrong when choosing it. It's very robust and works very well too, and does not have loosy typing.
https://www.postgresql.org/docs/current/continuous-archiving... (section 26.3.3.3)
You might want to configure the database to put binary logs into a separate directory (or you might want to leave them there, depends on your requirements).
If preventing data loss is important for your app then this would be more hassle than using a networked db because now you have to replicate SQLite across a network to another machine.
How so? A networked database requires a persistent connection, while SQLite backups work fine with an intermittent connection. The best case (100% network uptime) and worst case (total network outage) are the same, but in the middle, SQLite-with-backups is more resilient to brief outages.
Of course, if your coder already knows and does this, your last resort will be moving the DB closer to the app (the last 20% of optimisation).
So, yes, in an n-tier database architecture, 80% of your optimization work might go into meticulously minimizing your query load with better-designed queries. The point is: it doesn't have to be that way.
The more important thing I think is, again, thinking separately about reads and writes.
The issue is most apparent in edge-optimized applications, almost all of which are overwhelmingly read-heavy. You've got a (say) 100ms budget for the whole request, and "edge-optimized" with traditional n-tier databases practically implies that your database isn't always in the same data center as your app, so you can eat that budget up real, real quick going back and forth with Postgres. Even inside a database, you can be looking at a couple milliseconds per database hit, which adds up as you do multiple queries.
SQLite with replication is a nice solution for that problem. Writes get handled roughly the way they would with Postgres, serialized into a single write master; reads get satisfied instantaneously from NVMe.
If not then that is the use-case for SQLite.
It could still collect data to the server on a delayed basis for the benefit of the app-owners and for back-ups but that is not time-critical.
Lots of things predictably don't. It seems like bad engineering to operate as though everything will. Especially in appliances/"IoT" and other embedded use cases, that line of thinking can drive production costs up a lot.
In the context of embedded things of course you should use something like SQLite as opposed to postgres, because there aren't going to suddenly be millions of people using your CO2 monitor (for example). But for web stuff you can plausibly have user counts that span 6 or 7 orders of magnitude, so you want to be prepared for that.
In fact, I’d argue there’s no networked use case in which SQLite is cheaper, easier to deploy, maintain or run than Postgres.
All that being said, absurdsql is great. There should be some optimizations in mirroring network state to IndexedDB that could be queried via SQl(ite).
The SQLite documentation literally says use Postgres and not it for networked use cases (unless you use WAL or rollback, in which case why not just use Postgres?)
https://www.sqlite.org/useovernet.html
—-
As an aside, I think we need a new storage primitive. In the 2000s desktop apps were rampant and SQLite was more or less the standard.
We then moved over to networked apps where RDBMS had its day. There was a moment where nosql was booming for the scaling but it turned out people like relations.
We need an open source, distributed and relational store that’s easy to maintain build this decade.