I'll also note that I'm old hehe, and one of my first jobs we had a decent sized data center on site. Dealing with SANs, tape drives (auto tape rotators at the time were garbage), servers, etc... was a huge PITA. Packing up tapes and shipping them to another office for location redundancy was always fun.
The particular application I manage really suffers from low GHz and not having all data in memory. Ive run the benchmarks on EC2, certain reports that finish in ~5 seconds can take more than a minute on a comparable EC2 instance that costs about 10x as much. This application really needs the raw CPU. and yes we have a pretty large engineering team that has optimized all the queries, índices etc.
As far as replication, backups, etc. I set all that up, and honestly it wasn't a big deal. It's a couple short chapters in the Postgres book that explain it all very simply, how to configure, continuously (and automatically) test, etc.
I do agree that SANs are a nightmare. Thats why I ship all my WALs (PG backup files) to S3 (and eventually Glacier). That way I don't have to think about losing those files and it's dirt cheap.
I think there's a misconception that these kinds of configurations are too complicated for a single engineer to setup, with never ending maintenance required. In reality you can set it all up in less than a week and it only really needs maintenance when you want to upgrade Postgres (some settings may have changed). I'd estimate it takes about 5 hours per year of maintenance.
Self-serve infrastructure appears likely to become increasingly viable as we continue improving last mile delivery and expand fiber access. Will cloud become self-cannibalizing? Definitely maybe.
What cloud gets you is the ability to put your data/workload right at the core without having to make special deals with your local isp, and with a lot more resilience then you would likely afford unless your at least at the multiple 20 foot container full of servers scale of compute need.
https://www.cloudflare.com/products/tunnel/
https://github.com/cloudflare/cloudflared
https://developers.cloudflare.com/cloudflare-one/connections...
Edit: If anyone is interested in self-hosting, it's stupid simple with cloudflared. I have a 2017 Google Pixelbook running Ubuntu on custom firmware that's serving a Flask-based website. It sits on my desk charging while connected to a guest wifi network. It receives a 100/100 Mobile PageSpeed score and takes 0.8 seconds to fully load.