Hacker News Reader

Comment by solid_fuel | Hacker News Reader

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.

View on HN

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

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)

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.

Reply on news.ycombinator.com