Ask HN: What is your distributed and fault-tolerant PostgreSQL setup?

How cash strapped?

Personally, I would just use something managed like AWS's RDS for PostgreSQL

https://aws.amazon.com/rds/postgresql/

Then you don't need to worry too much about administrative tasks. As a bonus, you can start out small and easily scale as you grow, versus self-managed.

It doesn't have to be AWS. You can find similar offerings from pretty much any cloud provider.

> How cash strapped?

Zero budget. Hosting on services like AWS can cost a lot.

How about free tier? https://aws.amazon.com/rds/free/?nc1=h_ls

HaProxy -> pgbouncer-> Postgres

Setup a flask or other service that can be queried and get a response back if a db is a replica or not, set up 2 groups in haproxy one for the master and another for replicas. Point your app to haproxy,you should now be able to promote the replica and not have to worry about updating your apps settings.

Use barman for backups.

It's quite hard to give advice based on the information given.

There are definitely some alternatives that are setup fire and forget, but usually come with some cost. Self hosting a clustered postgres or similar will not be hastle free.

the simple alternative is to throw money at the problem, but based on your comment that doesn't seem to be an option.

There are some pretty cool tools surrounding SQLight to maintain a continuous backup as well as some simple read-replcas for scale up.

Another simple solution would be to use some managed no SQL, but cost can rise quite quickly with increased usage.

I really think foundationDB would be a misstep, it's a lot of work and requires a really good understanding of the db in excess of how to use it properly.

I second to investigate into SQLite first. Someone with no budget very rarely needs a DB that can scale a lot. Beginning with Litestream may be an option.

https://litestream.io/

You can self host cockroach db which scales horizontally well. But I think going "fire and forget" while also having this much concern about "scaling up" on some level feels at odds with each other. Lots of DBs can just scale up vertically or horizontally pretty easily, but as it gets bigger you will want to know more and more about how to administer it properly. As you scale, risk gets bigger. So either "fire and forget", don't learn much, and cross your fingers. Or, plan for the future and plan on getting good at some database.

Sadly CockroachDB is not open source anymore. Maybe take a look at TiDB.

Not FOSS, but the core parts are 1. free and 2. on GitHub.

https://www.cockroachlabs.com/docs/stable/licensing-faqs.htm...

https://github.com/cockroachdb/cockroach

Both of the kubernetes operators are extremely good. Zalando's uses Patroni for HA, and CrunchyData's has their own HA impl. Both have great backup systems built in.

> Any way the Postgres DB can be setup in a fire and forget setup and scaled just by adding new nodes for read and write?

Generally I'd reply yes and no, yes, adding readers is pretty easy with these operators. No, postgres is generally single writer.

But the CrunchyData k8s operator (pgo) has active-active mode. I dont know much scalability this really buys, since ultimately everyone needs to soak the write load. There is sharding too, built into postgres, but I count myself as blessed that so far straight up verticle scaling has tackled every problem I've ever had and sharding hasnt beem required. Anyways here's a post on setting up active active in pgo, https://www.crunchydata.com/blog/multi-cluster-postgres-fede...

One thing missing from your post is any information on workload. What kind of data are you storing, what kind of use are you expecting,... it's hard to make recommendatuons in the abstract.

> One thing missing from your post is any information on workload. What kind of data are you storing, what kind of use are you expecting,... it's hard to make recommendatuons in the abstract.

I am designing an entity-relation type system that is append-only for audit purposes. The system will have various apps and users will be authorised to use certain apps.

I might suggest trying out the timescale extension's hypertable, which can greatly ease sharding over time (plus optionally one extra dimension). If you have infinitely growing data, having the data sharded might help managability & perhaps performance.

https://docs.timescale.com/timescaledb/latest/overview/core-...

+1 on AWS and RDS. You can get $1k credits via AWS Activate[0] just by having a website. There is a free tier for RDS (take note of the instance type and size!) and even then a T4G micro is $12 / mo [1]. E=

Setting this up with CDK is easier than ever: https://docs.aws.amazon.com/cdk/api/v2/docs/aws-cdk-lib.aws_...

Also easy to setup read replicas etc down the line, but don't focus on any of that for now. Build something people want ;)

[0] https://aws.amazon.com/activate/ [1] https://aws.amazon.com/rds/postgresql/pricing/

Why specifically PostgreSQL compared to MySQL? Planetscale has a very modern managed offering of MySQL scaled with Vitess, you won't even be adding nodes.

Managed service of Google, never had an issue and has ability to auto-backup in multi-region without any setup needed. But maybe I missed something.

Fly.io has support for launching a preconfigured high availability postgres cluster. You do have to manage it yourself but most of the hard parts are taken care of. The pricing is also pretty competitive.

I just recently migrated my postgres instance from DO to Fly and it was super easy.

AWS Aurora?