Full disclosure: I work for Get London Transfer, a private-hire airport transfer firm, so bear that in mind. We get asked about this a lot, so here's what we tell passengers.
When the Piccadilly line is shut at weekends, the realistic options are:
Elizabeth line - the best alternative for most people. Direct to T2/3, T4 and T5, and much cheaper than Heathrow Express on pay-as-you-go.
Heathrow Express from Paddington - the quickest, but only worth it if you're near Paddington or book early for the cheaper advance fares.
Night buses and rail replacement - the cheapest, but slow and hard work with luggage. The N9 covers the night Tube route.
Coach from Victoria - handy if you're south of the river.
Pre-booked car or cab - this is our line of work. It makes most sense for families, groups splitting the fare, lots of luggage, or early flights before the trains start. For a solo traveller near an Elizabeth line station, the train is honestly better value.
We've put a fuller breakdown on our Heathrow page if it's useful to anyone:
getlondontransfer.co.uk/heathrow-airport-transfers.html
On your wider point, I agree that relying on one century-old line is a weakness. The Elizabeth line has taken a lot of pressure off, but places like Hammersmith, Earl's Court and Acton still have no good backup when the Piccadilly is down.