My impression is that its invention was for the sole purpose of eradicating the idea that Windows is insecure and prone to viruses, which explains why it can be overzealous and CPU hungry.
I would only enable it for family members who don't know what they are doing. For some reason, I haven't needed any form of active virus scanning in something like 15 years. If it turns out I've been infected this entire time, the criminals sure are taking their time stealing my money, etc.
The problem is that this also includes most people who think they know what they’re doing. We’re in the middle of a big change in how general purpose computers work and it’s basically driven by accepting that people make mistakes, trusted sites or things like their URL shorteners or social media are compromised periodically, etc. Maybe you’re really good at never visiting dodgy websites, always use an ad blocker, etc. … but have you never installed the wrong Python, NPM, etc. package by mistake?
Short term, something like Defender makes sense for most devices used for web or email. Longer term, I think we need more focus on sandboxing, hardware MFA, etc. so we aren’t using systems so brittle that everything just falls apart if you make a mistake. I don’t want the entire world to be iOS but the status quo sucked more.