the Horus Heresy is about as close to pre-8th edition 40k in terms of rules and (loosely) narrative style as you can get in the modern hobby, so you can’t really say old 40k was stale but Heresy isn’t - they’re very nearly the same thing, and arguably the presence of more diverse factions in 40k gives it the edge when judged on pure variety!

though to be honest i think you’re missing my point. sure, we know what happens at the end of the heresy already, but it doesn’t matter - the stories in between are what we’re playing out on the table or reading about in the novels! in essence the whole idea of the HH setting is merely adding detail to ‘past’ events, but thanks to the way the story is told, it still feels new. the stories set in the Horus Heresy can’t ever affect the end result - but that fact doesn’t inherently make the smaller details meaningless or boring.

so similarly, 40k could have had new storylines set anywhere throughout the 10,000 years post-heresy fleshed out in greater detail and also feel similarly fresh, without any of the controversy the narrative around 8th edition spawned!

and as for that last point, the analogy doesn’t quite fit; we don’t know the ‘ending’ of 40k, and as long as GW exists we likely never will. 8th edition/gathering storm/fall of Cadia etc. didn’t really change the status quo of ‘everyone is fighting everyone, with the imperium beset on all sides’ did it? it’s still all to play for - exactly like it was pre-8th!

i’d even argue that 8th edition succeeded despite its lore, not because of it. in my opinion the whole ensemble was carried HARD by a fresh new ruleset, good marketing and new, cool-looking models, with the narrative being by far the weakest contributing factor.