The tone makes a big difference. Repeated huge stakes with a lighter, more pulpy tone is doable. Repeated huge stakes with a tone of "this is serious and everything is INTENSE" is unsatisfying because there are no highs and lows. There's a reason only Hulk gets to write in all caps.
Even the biggest threats of super high stakes can be fine when couched in something more personal. There are huge stakes in TCOTEOF, but what matters is that it hinges on one life, one decision. Same with Yesterday's Enterprise. This is why the Pike moment worked better than many others this season.
If the focus had been more on any one relationship - saving Spock, saving Burnham Sr - and the battles and tech took a backseat to that, it could have been the biggest stakes ever and no one would bat an eye. A battle could happen and be huge, but it would have been so much more satisfying if the climax was letting her mother go or something like that - those things can even be intercut. (The climax of LOTR has a battle, but the big battle isn't the climax.)
Also, huge stakes are better when felt than stated. So many terrible things could happen if the Dominion won the war, and some characters may voice specific what-ifs, but those maybes are so much more intense than hearing "the end of all life in all the multiverse" which comes across not as intense but silly.