Most major credit cards come with a good amount of rental car insurance.
One of the few cards that provides primary rental car insurance is the Chase United Explorer card. If you rent cars regularly it can be worth the annual fee for that feature alone.
(Assuming you have car insurance to begin with -- if you don't, then there is no difference between primary and secondary coverage)
Here in Europe, for almost all car rentals the maximum you will pay even if you rear end another car is around €1500 (may be higher depending on the class of the car). However as I understand, they are well within their rights to charge you that even for a tiny mark on the bumper.
It is mandatory for all vehicles (rental or not) to have insurance that covers damages to the other party in the event of a RTI - this is on the responsibility of the vehicle owner and covers anyone authorised to drive it. We don't have crazy medical bills so this is usually quite cheap (I pay €120/year). This covers everything, so if you hit a brand new Rolls Royce which ends up being totalled you aren't on the line to pay anything extra. Of course you will need to pay to fix your car.
There is also comprehensive insurance that covers damages to your own vehicle. This takes into account your driving experience and value of your car, so can range from a few hundred to thousands (I pay €600/year for a 12yo Toyota). If I decide one day I feel like driving into a tree, I just need to pay a €100 access fee and the insurance company will fix my car.
The €1500 is the equivalent of that access fee. Paying for the extra rental insurance reduces how much of that €1500 you need to pay (well, I think you still need to pay, but can claim it back). There is also third party car rental insurance providing the same cover, which last time I took it, was something like €50/year.
My perception is that the rental companies are incredibly vigilant about minuscule scratches - my concern would be being charged €hundreds for a tiny scratch caused by a chipped-up stone. I wonder if the companies providing excess insurance are able to negotiate more robustly with the rental companies than the typical private individual?
As an insurance company, I might handle thousands of cases like that a year. It makes financial sense to set up a way to efficiently get rid of those, and once I have that set up, it stops making sense for the car rental place to try to get me to pay. They know I'm just going to sue them. They probably still try, and then voluntarily drop it as soon as they see a response from another insurance company.
Plus I strongly suspect part of the reason they're so stringent about scratches is to push people into paying for the insurance. If someone is already using another insurance company, they're going to be harder to convince to use your insurance than someone who simply has no rental insurance.