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.
99,99% of apps dont need more than one app server. You can serve a lot of traffic on the larges instances.
For sure downtime is easier with kubernete etc but again overkill for 99,99% of apps.
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.
For analytics the solution is very simple. Just copy the database and run the queries on the copy.
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?
Because cassandra isn't easy to set up and has all kinds of tradeoffs on consistency, transactions, et al compared to an SQL db.
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!
your goal should be to solve this problem when you have a million or 10M concurrent users. YAGNI