Welcome to the Tech Forum!
I’d say the answer is “it depends”. If you could give an example or two of which specific parts to which you are referring, that might help. However, the specifics of your design, the nature of your application, and the expected environmental conditions would likely have more significant effects on how reliable the resulting solution is.
As a general statement, I would think longer pins would improve reliability, but other factors such as contact materials and platings, as well as diameter of pins, number and nature of mating socket “fingers”, and possibly other factors may play as great, or even greater role in overall reliability. If your stand-offs are dimensioned properly, and positioned in such locations as to minimize the likelihood of board-to-board separation and/or translation, then length of pins may not be the most critical consideration.