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.
This is very educational and I appreciate the answer, but for the uninitiated- what's 87RON and 93RON? 87 AKI? I've never seen such a thing for sale at a station, all I see is unleaded 92, 95, 98, Diesel and E-10
The numbers are the rating; RON and AKI are units. Different places use different units. The small print next to the number will tell you which unit your station serves. Different stations may have slightly different numbers—just choose the one that is as close as you can to what is recommended in your vehicle's manual.
Unleaded is because old fuels used to have lead in them but leaded fuels are bad for modern engines and, turns out, breathing an atmosphere with lead in it is not great.
Diesel is just a different fuel entirely and has entirely different properties. Diesel and gasoline are not interchangable.
E-10 is gasoline and ethanol mixed at 10% ethanol/90% gasoline. Some places may have E-20 or E-85. Here in the States it's standard to have a low percentage of ethanol mixed in. Small percentages can still be ran by a normal engine whereas the engine will need to be tuned to run higher percentages such as E-85. "Flex Fuel" cars can run pretty much any mix percentage because they have ethanol content sensors and will adjust the tune as necessary.
Fun facts:
Ethanol is a type of alcohol and is a biofuel commonly made from corn.
Ethanol is popular in the performance tuning world because it has a higher energy content but it requires significantly more fuel to be burned.
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.
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 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.
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.