The explanation about stability is space is excellent. If you are familiar with a gyro, then just consider the bullet a long skinny gyro.
But there is more. The RPM required to keep a given bullet stable is also a function of the medium it is in. Air density changes from day to day with both air pressure and air temperature. A load that is just barely stable in the summer will work great, but tumble in the winter.
Also, the faster a bullet goes, the more dense the air around it. The good news about this, is that if the bullet is not tumbling at 100 yards, you don’t have to worry about it remaining stable at long range. As the bullet slows down the effective air density drops, and the bullet gains stability.
But, if you have shot at long range (over 900 yards) then you know some funny things happen. This relates to that little gyro holding a fixed orientation in space. As the bullets goes down range, the trajectory arcs over and starts to come down at a fairly steep angle. But the gyro (bullet) is still point slightly up, the same as when it left the barrel. So, relative to air flow, it is coming down more and more sideways. This was mentioned in a post above.
As the bullet path arcs over, there is pressure on the bullet to arc over, too. And it will, if it is not too stable. Stability is noted as “s” meaning the rpm needed to make a bullet barely stable as it starts down range. A real high quality bullet can be shot at a stability factor of, say, 1.2 times the minimum. An average bullet can be shot at 1.3 to 1.5. Those of you shooting light bullets at high speed can have S factors over 2, unless you use a special slow twist barrel. (For example, a 147 gr military 7.62 bullet only needs a twist of 1:16 to be stable, so you can imagine how much extra RPM it has in a 1:12 or 1:10 barrel).
At long range, the S factor matters. Over 1.3, the bullet will stay pointed at the sky as the trajectory arcs over. Phrased another way, at a RPM over 1.3S, the trajectory will arc over, but the bullet won’t. Under 1.3, the air pressures will force the bullet to stay lined up with the trajectory, and it will come down, nose first. For long range accuracy, this is better.
But, the gyro will prerecess. It will turn 90 degrees to the force applied to it. So as it is forced to tip over, it will also turn to the right. So, at long range, it still has some sideways aspect to the airflow.
Long range shooters have long noted that funny things happen between 900 and 1000 yards. Loads that work well at 900 sometimes crap out at 1000. This is often ascribed to variables shooters can relate to, such as twist or going sub sonic. It is related to twist only in that more twist makes it worse. It is related to going subsonic only in that the longer the bullet stays super-sonic, the flatter the trajectory and the less the bullet turns sideways to the airflow.
So what does all this mean? It means buy quality bullets, develop an accurate load at 500 yards and then test it to see if it works at 1000 (or more). The last step is the more difficult, but critical.
Makers of nose-fused munitions are very concerned about these issues, because they need their toys to work at long range.