As someone who has tried at least 15 or more commercial home AVs over the years, I can say that there are indeed those that are difficult to configure, have an impact on the system, and can cause inconvenience to the user. However, there are security solutions that are the opposite, and their presence on modern computers is almost invisible if we look at CPU or RAM usage.
For example, I will say something about my experience with Norton Security, which I consider almost perfect. RAM consumption is generally less than 100 MB, while CPU goes from mostly 2-5%. By comparison, Firefox with 4 open tabs consumes over 1 GB of RAM and close to 10% CPU. For a computer with 8 GB of RAM and a modern processor, no AV will be a problem with resource consumption.
I agree that protection depends on what the antivirus definition database is and how often it is updated - but also on how good
heuristic analysis it has, which means that it can fight against those threats that have not yet been added to the antivirus database.
See, I've found Norton to be quite intrusive, the thing is as computers advance, so does their hardware, so the effect of the program isn't noticed as much as time goes on. Put Norton on a older system, which doesn't have the latest ram, and CPU, and you'll see an issue. Although, I can't personally claim either way if its good or not in terms of resources, since I haven't seen it personally.
Although, why I find it intrusive is because it constantly tries to push additional products onto you. For example, I have friends that use it, and I've seen that they try to push Norton's own version of a VPN. The only saving grace is that they do claim not to keep logs for their VPN's. Although, according to
this they do collect some data, which is too broad to see if any of that information could be deemed invasive. However, I'm not a fan of the advertising that these services do, even if it is their own products. I believe they're enabled by default, and can be turned off through the settings.
As for their capabilities in protecting its users, I'd have to see some compelling data to change my mind on it. Heuristic_analysis generally is very limited, and more often than not identifies false positives. Most operating systems have built in systems these days, Windows has Windows Defender, which practically does the same thing, a part from a few variations, and Linux is built a little bit differently which protects somewhat against common threats, although at least with Linux there's less of a threat due to not as many users using it. Plus, most invasive threats need permissions which Linux doesn't have unless elevated.