I would agree that the complexity is compounded, having gone through the work automate various operators in kubernetes and the requisite deploy projects for the actual app/service (database) clusters, etc.

The problem is often that the actual costs of maintaining solutions like this isn't always clear and easy to budget for, and perhaps more importantly--explain to management--this includes the continued costs for engineering time to architect H/A solutions, maintain, research solutions, etc.
Add to this the abstraction and compounding of complexity and the plethora of hand-waving blogs, etc.

IMHO, the real problems arise when you deploy a PostgreSQL (via Kubernetes Operator) into an existing multi-AZ cloud-based kubernetes cluster--without knowing and understanding all of the requisite requirements and restrictions. At the time when I was working on deploying postgres clusters with the operator (mid 2019 AIR) there was not a lot (much at all) in the strimzi kafka operator docs about handling multi-AZs in kube with the Kubernetes autoscaler and using cluster ASG's, etc. Note that persistentvolumes and persistentvolumeclaims in the cloud cannot span multiple AZ's--this is a critical concern, especially when you throw in Kubernetes and an ASG (autoscaling group). What this means if you have some app/service running in a specific AZ that has persistentvolumes and claims in that AZ, you must ensure that that app/service stays in that AZ and all of its requisite storage resources must also remain available in that AZ. The complexity that is required to manage this is not trivial for most teams. I.e. some helm charts that I installed (after `helm templating` in our IAC code), configured nodelablels on the existing kube clusters worker nodes--but note that this was not documented in the helm chart BTW. So, when we later did a routine upgrade of the Kubernetes version and the ASGs spawned new worker nodes, that left those aps/services processes effectively hard-coded to use nodes that were terminated by the ASG (as they were older versions that were replaced by the newer versions during the upgrade) and their PV's were in a specific AZ, as noted above.

To do it right, I think you'd need to define AZ-specific storage classes and then ensure that when you are deploying apps/services into kubernetes you ensure that you manage those.
Again, from my past experience, when you have Kubernetes in the cloud, with the kubernetes autoscaler, and cloud-based ASG (autoscaling groups), running in an H/A (high-availability i.e. multi-AZ), and now add in stateful requirements using PV's, and now add in very resource intensive apps and services, now this starts to get a bit tricky to maintain--again--despite what the "experts" might be blogging about. Keep in mind that the companies sponsoring the experts might have teams of 10-15 DevOps Kubernetes engineers managing a cluster. This is something we definitely don't have.

I'm sure it will get better with time, but for now, we are doing all we can to maintain stateful apps/services externally--i.e.: and per your initial post, this would be PostgreSQL with RDS.
IMHO, RDS does a fantastic job and allows us to abstract all of this, and we simply deploy our clusters with IAC and forget about them to some degree.
For the cost point and specifically regarding resource contention, I think it's an ideal ROI to have the cloud provider worry about failover, H/A database internals, scaling with multi-AZ storage, etc.