I am generally not onboard with the “all you need is SQLite” articles that pop up on HN regularly.
But I recently looked into self hosting and several things each wanted their own Postgres container. Immich wants a Postgres and a redis instance.
If you’re building apps targeted at self hosters, SQLite is really all you need.
I would rather keep things on postgres but everything wanting its own postgres container is really annoying. Postgres already has support for multiple databases, users, and access controls. I would personally prefer to just set up one postgres instance with proper backups and have everything run against that, unfortunately some self-hosting projects have fairly poor support for that.
You could sometimes get there. The problem is in an effort to simplify the getting-started with postgres (or other databases) most projects assume they would also automate the management of that postgres instance.
The getting started would be a lot different if it first instructed you to install postgres, run a script or create a database and users and configure access controls. Also certain things become easier if you assume you can also manage the postgres instance, like on an upgrade, maybe you just create a completely new database and drop the old one, or isolate tenants within one application into different databases, etc.
In general, I agree with OP that sqlite is all you need for selfhosted applications (most of the time at least)
I prefer a separate DB instance for each app. Each app is on a separate ZFS filesystem so I can snapshot/backup the app data and DB together as one.
A possible restore only affects that app. Plus I can guarantee the files and DB were snapshotted at the same moment this way.
It may just be a way for me to avoid managing the DB.
That sounds like a mess. What do you do when the open source projects you're running need different versions of postgres?
How do backups work with SQLite? Postgres is nice in that it can run them live without any corruption issues for writes in the middle of the backup.
https://litestream.io/
SQLite has a bunch of cool stuff happening around it right now too like Turso and Litestream.
My experience with this is that all of the projects that are worthwhile, continue to grow and then at some point some kind of bottleneck is hit. Migrations become slow or overly complicated, lookups start taking forever, etc and the app just starts to slowly become unusable the more you try to use it.
You end up in a situation where you are following some complex guide (hopefully written by the actual developer) where you make a huge backup, have to tear everything down, and then hope the migration from SQLite or MariaDB doesn't hit any issues. Postgres is heavier but at the scales of self hosting it should not matter on any modern system. Multiple docker containers even on a Pi isn't something you have to worry about. Solutions like Immich are usually run on a NAS where there would never be an issue
Also you don't always know who your users are gonna be. You could create a cool personal photo backup app for your <20k library, then get people with a dozen terabytes of photos wondering why is it so slow?