In today's world there are countless Postgres providers (Supabase, RDS, DigitalOcean, GCP, Azure, etc) so there is plenty of choice now.
Although I don't see why anyone would choose niche providers today other than the cloud provided ones like RDS now.
So you’d assume most people are already dealing with the AWS behemoth.
And if the cloud provider is providing a competing Database as a Service then it’s almost impossible to compete.
This is one of the things I quite liked about Azure, funnily enough. They basically give you a connection string. It's not ideal, and I think for production we did something slightly different with Azure identities, but it got us going quickly, and the identities thing devops just swapped in later behind the scenes. Other than that it was just regular Postgres.
If you are using AWS anyway and are accustomed to it, obviously, RDS is a good choice. But I don't want to learn what DNS and Filesystem are called in AWS-speak just to shoehorn myself into getting stuck with AWS forever, when all I need is a hosted database so I don't have to worry about backups, etc.
My typical situation sees small to medium (virtual) servers that run software for tens to possibly thousands of users. There are plenty of use cases that aren't "google scale" and can be operated on a simple machine and can profit from a "simple" hosted DB.
Rant: Same goes for things like CDNs, I'd guess that 99% of websites don't get nearly the amount of traffic that a simple linux server couldn't handle out of the box. Let alone need sub 100ms ping responses, yet there is this weird cargo cult that requires putting your terraformed, docker stuff running javascript from with a python container with multiple layers of caching behind cloudflare because people don't dare consider simple architectures.
Compared to this, stateless application server part is usually quite easy. If you would like to be cost effective, with small team, you could outsource the database part and manage the applications on dedicated servers.