You need to add indexes on cloud-hosted instances too, perform migrations there as well, and launch new features in your application in both cases. Almost everything you're talking about has little to do with whether you're self-hosting or using a cloud provider. You still have to deal with most of it either way.

EDIT: Don't get me wrong, I've also managed Kafka clusters, ClickHouse clusters, Elasticsearch clusters, etc. and I have my share of Zookeeper horror stories. Some of the tools I just mentioned are definitely not maintenance-free. But in my experience, you can't really compare PostgreSQL to them.