It is my understanding that VCs only stay afloat because the payoff in case of success is huge enough to offset many failures. And I guess many VCs also don't stay afloat forever.

This is correct. For small and medium funds, a single successful portfolio company can return their entire fund with some multiple on top of this. The reason you invest in 30-40 companies instead of one is because you don't know which one it'll be.

I've seen this from the inside, I liked the company a lot but I'd never suspect it'd become the most obvious success for us.

It is my understanding that most VCs expect only one in ten businesses to succeed, but that one is enough to offset the loss and ensure a profit.

The "fairytale" cases (IPO) are quite rare, but acquisition is a little less rare, and help support the portfolio.