WTH What are my hardware and installation options and how do I make sense of it?

From the start I’ve been using Home Assistant with Home Assistant OS on a Raspberry Pi.
I have found several peculiarities over the years. I had to watch one of my SD cards die due to constant usage. I’ve seen the Raspberry struggling to cope with heavy load. I wasn’t able to install the VS Code plugin, because the CPU architecture didn’t match. I found some clues in YouTube videos about running HA with MariaDB on an x86 machine for high performance. But then I learned that HA Amber is based on Raspberry and seems to be state of art.

All in all I am quite confused about the options I have, the support they offer and the different outcomes in terms of compatibility, performance and such. Why is there no guide to this? It would help a great deal.