Hacker News Reader

Comment by mikeocool | Hacker News Reader

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.

View on HN

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.

I do the same, a single app on a zfs filesystem I can snapshot/send/receive

Isn't a snapshot of the db a bad idea? At least you won't always be able to restore from that. You'll need a dump for that to work.

A filesystem snapshot shouldn’t be much different than pulling the plug and powering it back on.

Does a DB fall apart every time that happens? It does happen, but I might argue it shouldn’t happen, especially in simple configurations.

I do have an automatic daily dump in case of emergencies.

Plus sometimes these apps have specific requirements. One thing needs Postgres 16, the other needs Postgres 18... maybe it works to point both at 18, maybe it doesn't. Easier to just give everything its own instance and use whatever the recommended Postgres version is. I doubt there is any major difference in resource usage

That sounds like a mess. What do you do when the open source projects you're running need different versions of postgres?

That's a large part of why I lean more and more into just preferring sqlite for applications.

Even in work environments for apps that likely won't have thousands of simultaneous users. Modern SSD drives are so fast that so many apps don't need the advanced features of a full blown rdbms. And I think options like Turso and Cloudflare D1 fill the in between as a hosted option.

I do wish that sqlite had a couple more native types, it works really well as it stands.

Immich uses postgres because it relies on VectorChord for smart search.

It's what powers the ability to search your photos for "a dog on a beach".

It's also a fairly sensible decision.

There exists https://github.com/asg017/sqlite-vec

And for small enough vector sets, holding them in memory is reasonable. Most personal photo collections would fit that scale.

I actually always find SQLite more annoying than Postgres. Postgres I just run on the host and let all my containers talk to the same instance, one thing to backup. With SQLite it's always about volumes, permissions, backing up individual files for multiple projects.

It also makes it much easier to just point my Postgres GUI client to my remote server (Behind Tailscale of course) and query the database there without copying over the SQLite file to my local machine, or mount it somehow. Even for simple projects that I start I always prefer Postgres.

If you're comfortable doing that, and plenty of folks are (especially us oldies who remember the shared hosting days), then that's a great way to go. I think that many who pull a docker compose file probably aren't ready to also be a PG admin, and if not shared, Postgres is probably overkill for most things. But, I've also had bouts of "just use a single PG/MySQL instance" myself. I get it.

SQLite has a bunch of cool stuff happening around it right now too like Turso and Litestream.

> several things each wanted their own Postgres container. Immich wants a Postgres and a redis instance.

Those things are dirt cheap. A Postgres container on my machine uses 200MB of memory. Redis is nearly free. It's a small price to pay to not have services cross paths with each other and mucking everything up.

Also you don't need a separate container for each of them. Why not run one and have multiple databases in there?

Postgres can use less RAM than Redis, depending on how much you're putting in Redis. I've got ~150MB database sitting in a Postgres container that's hovering around 13-25MB memory.

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?

Yes very much so! Also has the happy side-effect of making your architecture so much simpler to reason about[0]

In the "end" (after a couple of months actually) I did add support for MySQL and Postgres because I found it massively helps adoption for those self-hosters that already have those (and associated stuff, e.g. backups) in place.

[0] https://www.bugsink.com/blog/database-transactions/

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/

Much simpler since you just need to backup a single file, if you're not running a public service with heavy traffic it's safe to assume that your db state is always consistent

Wanted their own postgres container or database? Why should the service know/care how postgres is running?

Reply on news.ycombinator.com