I would argue that correctly tuning a database is significantly more difficult than most services one would self host.
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.
Not... really? It's no more difficult than finding the correct buffer sizes for nginx, or finding the correct sizes for the ebpf connection table tracking map if you're using cilium on k8s, or kernel tcp buffers or any other other myriad services one could run.
Being a bit obtuse to tune doesn't really justify going all-in on cloud. It's all there in the documentation.
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.