the honest answer is: rds wins until your aws bill genuinely hurts, and by the time self-hosted actually pencils out, you'd better already have a dba on staff. otherwise you're just trading money for 3am wake-up calls

I think self-hosted should be cost effective if using terabytes of storage and/or high data transfer, as raw storage attached to a server is cheap, and many servers come with unlimited bandwidth (unlimited in data amount, not in transfer speed) I'm self-hosting a PostgreSQL database but it's not because managed DB didn't scale, I did this choice because I have all the needed technical skills, I don't expect the required resources to double overnight, and prefer paying a fixed amount per month to rent a server rather than a pay-as-you-go managed DB