Once you start running "real" things, CPU and RAM use start climbing - mostly RAM in my experience. I run a Matrix homeserver - Synapse really struggle with <4GB RAM on the system, so it's one of a few things on a VM with 16GB. I've got a Minecraft and Valheim server running for friends and family - 8-12GB is reasonable for both. You certainly can do it in less, and I've done it, but a lot of problems go away when you can just throw RAM at the problem.
Also, most of the cheap VPS boxes are badly IOPS limited on disk - unless you start paying a good bit more for faster disk. I split my box between a 2TB NVMe mirror for VM root drives and "high write traffic" stuff (the gameserver drives and the like), and my large, slow, spinning rust array for backup storage and "big stuff." Again, you can do it with cloud, but storage adds up in a hurry - 24TB of "slow" disk is a grand a month.
It just depends on what you need. I really like cloud boxes for light use "toy" boxes, and if that's what you want, they're great. I just cannot wrap my head around the merits of spending a lot of money for "serious cloud compute" when I can build and rack a similarly capable box for 2-3 months of the cloud spend on a similar system. I understand opex vs capex issues for business, but, man, it sure seems stupid to me.
Then again, I'm the kind of person who's had a box racked up a colo (or somewhere similar - random basements on a good connection are nice) for the majority of my adult life. So I'm pretty clearly biased. But I did do a few years of "Hey, cloud, this is cool" - and eventually sat down, evaluated my needs and the costs, and went back to a colo'd box.
I think for a lot of people, a nuc with dual m.2 drives is probably a much more affordable & realistic entry point that would probably survive even your fairly demanding gameserving use cases. And cost less than half as much.
Overall I'm very interested in a future where we do have scale out edge caches, where we do just need modest resources to maintain wide-scale connectivity. Many personal services are not at this scale or not this architecturally complex, but being able to have efficient scalable personal systems would be an interesting ends to move towards. Matrix would be a good example of a service that would be nice to have good fan-out for.
People should rack their own rpi5 or nucs, at least. There's various providers for both. And consider moving up from there. The remaining folks who will accept & rack a mid tower box for under $75 are saints.