At what point does drone insurance become worth it?

Insurance isn't much good for events that probably will happen sooner or later, like crashing a drone. The insurer will estimate how often it happens and how much it will cost, and adjust the cost of insurance to match, plus a bit so they make a profit and cover their admin costs.

Where it is useful is for events that are unlikely to occur but would be ruinously expensive if it did. Maybe you crash into a road and cause multiple cars to collide, leaving someone unable to work for the rest of their life or with large medical bills. Or maybe your crash causes a fire that burns down a large building. In either case, you'd be liable for millions, which would effect you for the rest of your life. Even if you just crash into a newish car and cause a small dent, you could be liable for a few thousand to fix it.

In these cases, the insurer spreads the risk over thousands of people and you all pay for it gradually over several years, so it doesn't cost much to buy several millions of cover.

Here in the UK, the land owner of the school field where we fly (the County Council) requires us to have £25 million of cover and it costs about £25 per year, which seems like a great deal. I believe clubs that fly on Ministry of Defence land (i.e. military airfields) have a similar requirement.

You should have insurance for damage to people and property. This is less true for whoops but goes up exponentially with the size/weight/power of the drone.

Insuring the drone itself depends upon the value of the drone. Most people with a drone valued below $500 probably don't have insurance. Drones valued in the thousands of USD may be worth considering insurance - although I don't think this is common.

The AMA offers plans (as of today) with $25,000 medical and 2,500,000 liability or just $500,000 liability (USD).

I have paid for this in the past - I think I'm currently expired but haven't been flying around people or expensive property.

There may be regulatory variations by jurisdiction - as you haven't indicated a specific location, I've referred to UK regulations which I am most familiar with.

There are two insurance considerations - first party (where you insure your drone) and third party (where you insure against damage or injury to others.)

Private

Third party insurance is a good idea (but not required in the UK) if you are flying anywhere you may affect someone else - this could be a person or their property. The potential medical or legal fees can be huge, so having protection against this is worthwhile. Some model aircraft organisations offer third-part insurance as part of their membership benefits - for example the BMFA.
However, your risk estimation will also consider what you are flying - a <250g toy is unlikely to cause major damage or injury compared to a 20kg scale model aircraft.

Whether you first-party insure depends on your own personal financial situation - and the importance you place on your aircraft. It is an unfortunate fact of the hobby that your aircraft might be damaged, even through no fault of your own.

As a side note, for storage purposes the drone may be covered by other services like home and contents insurance - although you may need to nominate high-value items in advance. You should check with your provider, where applicable.

Commercial Operations

As you expected, insurance is often required for commercial use. In the United Kingdom, third party insurance is required - the specifics of which are laid out in regulation (EC) 785/2004. I believe, at present, this is at least €1,000,000 per accident.

Insurance for the aircraft will depend on the operator. Larger companies might be able to accept the cost of replacing a few thousand pounds of drone every now and then, but a small photography business might be hugely reliant on their aircraft for their livelihood - making insurance worthwhile.