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.
On that note, Hetzner has a pretty good reputation and pricing:
https://www.hetzner.com/dedicated-rootserver/matrix-ax
Note - not affiliated, just have used them for a while and they've been good. :)
I then decided to go back to running my own database but this time in a docker container on k3s on bare metal. A few hours fiddling with the config upon launch and that's it. Rock solid, way more performant because I can tweak the config to my specific needs.
In the 2 years I have been running a MySQL server on k3s, I have about 60-90 seconds of down time. Caused by 3 separate instances where the db became unresponsive and k3s rebooted it. Most likely just some tweaks i need to make with the health check timeouts.
It has been more set it and forget than a managed solution and about 20% the cost.
If I needed to use multiple servers it would admittedly get a bit more complicated. But only if i needed to replicate persistent data like the database.