Some decent answers and some appallingly misleading and/or blatantly incorrect answers.

Here's the real answer:
Different octanes of fuel are designed to be resist detonation (pre-ignition) at different pressure and heat levels.

Normal cars run on 87RON whereas high compression or forced induction engines need a higher octane due to the higher pressures in the cylinder. The higher presures can cause lower octane fuel to ignite before the desired position of the engine internals. This can cause extreme stresses and catastrophic damage.

Yes, your car is designed to sense this and configure the running conditions to resist preignition but i personally wouldn't rely on that.
Fill your car with what the manual recommends.

Fun fact: if your car is designed to run 87RON vs 93RON running the higher octane fuel is actually making your vehicle less efficient.

Side note: 87RON is different than 87AKI. Make sure you understand what you're putting in your car. Different countries follow different fuel standards.

It's for engines designed for high pressure, this usually but not always involves a turbo. The higher octane allows greater compression for greater torque at higher RPMs.

My car's engine requires it. It is a turbocharged engine. Gets great gas mileage and runs great!

When I owned an Infiniti G37x, the owners manual said to use the higher octane fuel, so I did just that.

Higher octane resists detonation (knocking). It's required for engines with high compression ratios. Turbocharged engines are susceptible to detonation, so they'll often require higher octane fuel.

My car has a 4-cylinder turbocharged engine. The manual says I can use standard, mid, or premium. The engine has a "knock sensor" which reduces power (basically by turning the turbo-charger down/off) if it senses detonation. If I use premium gas, the engine will use the turbo as much as possible, increasing gas mileage (according to the manual). The increased cost isn't worth it to me.

I use it in my 50cc motor scooter since it's got a barely 1 gallon tank that lasts me ~2 weeks a fill, so the price difference is negligible

I figured the higher octane would make a noticeable difference since it's such a small engine

But reading this thread I'm now thinking maybe I shouldn't do this?

I buy it for classics.

I will start my reply saying I don't understand much about cars.

My answer to you, OP, is that various mechanics recommended me I use at least 95 and try to avoid anything lower. Why, I'm not exactly sure but they said it's better for the engine and that it will help my fuel sensor, which is a bit faulty, not light up prematurely or erroneously. I sure feel the difference on the motorway, lots of hills here.

I live on a mountain with a heavier vehicle. The higher octane does actually make a difference for the uphill journey.

It's only old sports cars where you really should use the high octane stuff all the time but some cars do get better miles per gallon on it as well, but rarely enough to offset the cost increase.