Why use Postgres if all you need is sqlite? Postgres is way overkill for a simple app with few users and no advanced database functionality.
People care about things like long-term support. Postgres 13, from 2020, is still officially supported. Litestream 0.1.0 was the first release, also from 2020, but I can't tell if it is supported still. Worrying about the maturity, stability, and support of an application database is very reasonable in risk adverse projects.
Besides the question wasnt litestream vs postgres backup apps. It was sqlite vs postgres.
More than once I've started a project with sqlite and then had to migrate to postgres. In my experience it's because of the way sqlite has to lock the whole database file while writing to it, blocking concurrent reads - this isn't a problem in postgres. (There's WAL mode, but it still serialises all writes, and requires periodic explicit checkpointing IME)
You may also find you really want a feature postgres has, for example more advanced data types (arrays, JSON), more advanced indices (GIN inverted index on array members), replication...
If you’re building something as a hobby project and you know it will always fit on one server, sqlite is perfect.
If it’s meant to be a startup and grow quickly, you don’t want to have to change your database to horizontally scale.
Deploying without downtime is also much easier with multiple servers. So again, it depends whether you’re doing something serious enough that you can’t tolerate dropping any requests during deploys.
It's definitely really nice though that if you do choose sqlite initially to keep things as small and simple as possible, you don't immediately need to switch databases if you want to scale.
For sure downtime is easier with kubernete etc but again overkill for 99,99% of apps.
On the other side, why not just store everything in memory and flush to a local json file if you won't have any users? sqlite is overkill!
SQLite is designed for one local client at a time. Client-server relational databases are designed for many clients at a time.
If you have lots of concurrent writes SQLite isn't the right solution. For concurrent reads it's fine.
SQLite also isn't a network database out-of-the-box. If you want to be able to access it over the network you need to solve that separately.
(Don't try and use NFS. https://sqlite.org/howtocorrupt.html#_filesystems_with_broke... )
That becomes an instant problem if users ever write to your database. You can't duplicate the environment unless it's read-only.
And even if the database is read-only for users, the fact that every time you update it you need to redeploy the database to every client, is pretty annoying.
That's why it's usually better to start with Postgres or MySQL. A single source of truth for data makes everything vastly easier.
If you have a read-heavy app (99% of saas) that runs on one server and dont have millions of users then sqlite is a great option.
Usually you want to be able to run multiple webservers against a single database though, since that's the first thing you'll usually need to scale.
Now if I'm building a local app then absolutely sqlite makes the most sense but I don't see it otherwise.
dpkg -i litestream.deb
systemctl enable litestream
systemctl start litestream
Systemctl's only in there to restart it if it crashes; litestream itself is (iirc) a single cli binary.
I love postgres but in no way is it as simple to run as sqlite (pretty sure even postgres core team would agree that postgres is more complex than sqlite).