I use to swear on RDS being an AWS certified person. AWS taugh us managed is the best and always put warning when you want to self hosted.
Then recently at my small company(an email forwarding app that process 500K email daily https://hanami.run) I want to cut cost and go with dedicated server for our Postgres database.
At that moment, so many cool thing appear. You suddenly give super power to tune yoru database however you like.
On the other hand, RDS and multi-az deployment is just DNS failure at the end of day. You still have to handle stuff like restarting the app to make sure it got the right DNS after RDS failover.
Then recently at my small company(an email forwarding app that process 500K email daily https://hanami.run) I want to cut cost and go with dedicated server for our Postgres database.
At that moment, so many cool thing appear. You suddenly give super power to tune yoru database however you like.
On the other hand, RDS and multi-az deployment is just DNS failure at the end of day. You still have to handle stuff like restarting the app to make sure it got the right DNS after RDS failover.
So at the end of day, pick a hosting provider with reliable uptime(look at their history status page about outage in term of network/power). Tune postgres use tool like https://www.pgconfig.org/#/?max_connections=100&pg_version=1...
Add 2 read replicas(it takes like 10 commands to do that in modern postgres) for streaming replication.
Practice failover(promote read replica to master) once per month.
Then you will be good.