You're writing this as if LiteFS and Postgres were basically the same thing, and the selection criteria just boils down to the Postgres pedigree and maybe all the Postgres-specific SQL features. But that's not the case at all. The difference between replicated SQLite and Postgres is that SQLite is in-core: it doesn't have to round-trip on the network to fetch data; it can burn through a large set of N+1 queries faster than Postgres can handle a single select. The difference is that SQLite is much faster.
You sacrifice things to get that speed (Postgres features, set-and-forget write concurrency). Nobody is saying there's no reason to use Postgres, or maybe even that Postgres is the right call most of the time. But the idea that SQLite is rarely appropriate for concurrent serverside applications? It's received wisdom and it's wrong.
Somebody across the thread actually suggested that WordPress was an example of the kind of application that SQLite wouldn't work for, that needed an n-tier database. (Leave aside the fact that WordPress doesn't support SQLite, has instead a longstanding MySQL dependency). WordPress! WordPress is a perfect example of a concurrent serverside application that probably should almost exclusively use SQLite. As I said in a different comment: the whole movement towards static site generators is, in large part, a reaction to how bad n-tier databases are for a very large, popular class of concurrent serverside applications.
My second paragraph was specifically talking about the gigantic difference between operating a stateful application vs. a more typical stateless app + stateful DB. Most people aren’t deploying to Fly.io (yet!), so making Litestream work sounds pretty high risk to me :) combined with the lack of pedigree I led with, I just don’t see litestream as a good answer to “I need a HA database solution” for most people.
For most of the world (outside silicon valley) stateless is not the typical. The typical is stateful app + stateful DB run on stateful VMs.
For most enterprises, running a stateful app with SQLite is not only possible, it's easier than running stateless apps as their entire IT infrastructure is setup to support stateful apps and it's what their sysadmins know how to do.
I have tried to deploy stateless docker container apps into a typical enterprise network and it's a nightmare. The people done understand it, are not interested in understanding it, none of the infrastructure supports it and if you want to setup something like Kubernetes then you end up in configuration hell as none of the easy to deploy standard setups actually work out of the box in an enterprise network, everything ends up needing highly customized configuration to work.
I'm sorry, I don't understand any word of that sentence (for instance: we don't run containers?). We sponsor Litestream and LiteFS (in the sense that we pay Ben to work on it full time), but no part of Litestream is baked into or in any way coupled with our platform. If you can run Litestream on Fly.io, you can run it anywhere else.
You "transmogrify container images into Firecracker micro-VMs" - surely you knew what I meant?
And there is no denying that fly.io is a _particularly_ well suited platform to run stateful SQLite apps (with or without Litestream), since fly makes running stateful applications easy. I think thats awesome.
I'm really confused why you seem to be trying to distance yourself from that, and why you are latching on so hard to my passing mention of fly. I wasn't making a dig, just pointing out that most people are stuck with (arguably worse) platforms that don't lend themself to stateful, "in-core" databases.
My confusion is that there's nothing container-y about Litestream. It works anywhere SQLite does. I like our platform a lot, but I mean, Litestream works just as well on AWS.
If your application exceeds the capacity of a single server (e.g. a Java Web Service that is backed by a database), do you see SQLite as a useful solution for that case?
That seems like when you'd need a standalone database, no?
If you think the capacity will never exceed that one standalone database. If not, you'll need to plan for sharding anyway, in which case sqlite can work just fine.
You sacrifice things to get that speed (Postgres features, set-and-forget write concurrency). Nobody is saying there's no reason to use Postgres, or maybe even that Postgres is the right call most of the time. But the idea that SQLite is rarely appropriate for concurrent serverside applications? It's received wisdom and it's wrong.
Somebody across the thread actually suggested that WordPress was an example of the kind of application that SQLite wouldn't work for, that needed an n-tier database. (Leave aside the fact that WordPress doesn't support SQLite, has instead a longstanding MySQL dependency). WordPress! WordPress is a perfect example of a concurrent serverside application that probably should almost exclusively use SQLite. As I said in a different comment: the whole movement towards static site generators is, in large part, a reaction to how bad n-tier databases are for a very large, popular class of concurrent serverside applications.