If I recall my chemistry studies from many years ago, most metals are pretty soft in their pure form, they way to make them stiff is to alloy them with small quantities of other metals like vanadium and chromium. I believe this can be fine tuned to get the properties you want. I would imagine that the stiffness of a frame depends on the composition of the metal as well as the geometry, tube dimensions etc.
I expect someone who knows more about it will be along soon.
BTW wasn't there a magnesium alloy framed MTB some years back, horrendously ugly thing, the frame was basically a girder and they kept snapping. Another project that seemed like a good idea at the time. Scrap ones must have burnt nicely though 😆