Are y'all hiring? [1]
I did 15 months at AWS and consider it the worst career move of my life. I much prefer working with self-hosting where I can actually optimize the entire hardware stack I'm working with. Infrastructure is fun to tinker with. Cloud hosting feels like a miserable black box that you dump your software into and "hope"
Funny, I couldn't find a new job for a while because I had no cloud experience, finally and ironically I got hired at AWS. Every now and then these days I get headhunters unsure about my actual AWS experience because of my lack of certifications.
The answers to all of your questions are a hard: it depends. What are your engineering objectives? What are your business requirements? Uptime? Performance? Cost constraints and considerations? The cloud doesn't take away the need to answer these questions, it's just that self-hosting actually requires you to know what you are doing versus clicking a button and just hoping for the best.
But that said, you can afford a lot more hardware if you’re not using RDS, so the tuning doesn’t need to be perfect.
Being a bit obtuse to tune doesn't really justify going all-in on cloud. It's all there in the documentation.
I've found that it's almost impossible to even hire people who aren't terrified of the idea of self-hosting. This is deeply bizarre for someone who installed Linux from floppy disks in 1994, but most modern devs have fully swallowed the idea that cloud handles things for them that mere mortals cannot handle.
This, in turn, is a big reason why companies use cloud in spite of the insane markup: it's hard to staff for anything else. Cloud has utterly dominated the developer and IT mindset.