I think you're focusing on the wrong parts of the comment.
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.
Litestream is just a backup solution. Should probably be compared to a backup solution for postgres that does automated backups over the network etc. That isnt part of postgres.
Besides the question wasnt litestream vs postgres backup apps. It was sqlite vs postgres.
this is essentially the "no one ever got fired for buying IBM" statement. One counter is why buy & manage a rack-mounted server when all you need is a raspberry Pi?
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...
Both are mature. There are way more sqlite databases running than postgres. The code base is smaller and has less new features added to it every year. What is unstable with sqlite?
For a cloud service, I think it comes down to whether you’ll ever want more than one app server.
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.
This is the idea behind LiteFS --- to transparently scale out SQLite (in some very common configurations and workloads) to support multiple app servers. It's still there and it works! It's just a little ahead of its time. :)
That makes sense, and it seems really cool from a tech perspective. I guess I'm just inherently skeptical about using something shiny and new vs. battle hardened databases that were designed from the beginning to be client-server.
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.
I think that's very fair. But the use case for Litestream is much simpler and you can get your head around it immediately. It also doesn't ask you to do anything that would commit you to SQLite rather than switching to Postgres later. It's just a way of very easily getting a prod caliber backend up for an app without needing a database server.
Serving users is one thing. Then you want to run some interactive analytics or cronjobs for cleanup etc on the db. Even if the load can manage it, how would the admin jobs connect to the database. I’ve never seen a db with only one client. There is always some auxiliary thing, even when you don’t consider yourself a microservice shop.
agree - with SQLite and DuckDB I've really switched my mindset from one perfect, pristine DB to multiple copies and a medallion architecture that looks more like participation ribbons for everyone! The resources required are so cheap & lightweight when you avoid the big "BI focused" tech stacks.
you can also scale out across unlimited tiny servers, because the entire stack is so lightweight and cheap. This will also force you to focus on devops, which otherwise can become a grind with this approach. The only challenge is when you have cross-DB concerns, either data or clients.
Right, but if your goal is to have a lot of users (and minimal downtime), there's no point in putting a big avoidable obstacle in your path when the alternative is just as easy.
If your goal is to serve billions of users you should probably use cassandra etc. Why limit yourself to postgres if your goal is to have a billion users online at the same time?
I'd argue that anything larger than a desktop app should not use SQLite. If you need Litestream for replication and backup it is probably better to just use Postgres. There are a ton of one-click deployment offerings for proper databases, Fly.io actually offers managed Postgres.
That's not entirely true. SQLite is designed to support many processes reading the same file on disk at once. It only allows one process to write at a time, using locks - but since most writes finish in less than a ms in most cases having a process wait until another process finishes their write isn't actually a problem.
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.
the reality is very few workloads have access patterns that SQLite can't support. I would much rather start with a strategy like 1. use sqlite for my beta / single client, 2. duplicate the entire environment for the next n clients, 3. solve the "my application is wildly successful" and SQLite is no longer appropriate problem at a future date. Spoiler: you're never going to get to step #3.
> 2. duplicate the entire environment for the next n clients
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.
Let's say I'm building a small app that I'm hosting on some shared vps, if I think about the effort involved in setting up sqlite with litestream and just getting a $5 (or free) postgres provider I don't think sqlite makes my life easier.
Now if I'm building a local app then absolutely sqlite makes the most sense but I don't see it otherwise.
Litestream is dead simple to setup. You make an S3 bucket (or any compatible storage bucket), paste the access keys and the path to your db file in /etc/litestream, and then run
Effort of setting up litestream and sqlite is less time than you spend signing up for supabase. And you can have 100 apps with their own databases for almost free (just a few cents of storage) vs 5*100 for postgres.
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).
