What is your distributed and fault-tolerant PostgreSQL setup?

Question

I am going back and forth on which DB to choose for my project. I really want to choose Postgres but as I have no experience with database administration I hesitate to select Postgres. If something blows up or if data is corrupted then I will be at a loss. It will take me considerable amount of time to fix things. I want a fire and forget type of DB where my involvement should be to just add new nodes when scaling is required. I don't mind doing simple configuration when required. But being solo and cash strapped I do not want to use third-party solutions but also want to concentrate on the actual app rather than spend lot of time administering the DB and frameworks.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?If that is not possible then my other option is FoundationDB. It will be some work on the application layer side, but I hope I will have to spend less time worrying about the DB administration.What are your thoughts?

bsuvc · Accepted Answer

How cash strapped?Personally, I would just use something managed like AWS's RDS for PostgreSQLhttps://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.

hitpointdrew · Answer

HaProxy -> pgbouncer-> PostgresSetup 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.

drpyth0 · Answer

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.

slively · Answer

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.

rektide · Answer

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.

josevalerio · Answer

+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/

reducesuffering · Answer

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.

s-xyz · Answer

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.

sosodev · Answer

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.

orf · Answer

AWS Aurora?