I have read lots of speculation out there, but to me they are all wrong. They all seem to rotate around how Picard led a rescue flotilla that tried to save Romulans and then that failed and he quitted. I think it HAS to be deeper than that. I think the Federation tried to annex parts of the Empire to "protect" Romulans against Klingons who were trying to invade their space. That hypocrisy led Picard out. I think ST Online had some good material on that, which will hopefully be used for the series. A new confrontation between Klingons and Federation seems inevitable given how differently the two powers will look at the disaster. Will see, but if writers play it well, this could be very nice.
Because a failed rescue mission, if with good intentions, is no reason to quit Starfleet. Picard had plenty of failures before, like everyone has
The Federation tried to move a group of people from their planet against their will. Oh, wait. This has to be something that touched Picard personally in a big way. Maybe his official Starfleet duties directly led to the loss of a lover, or a dear friend, or family that he had started in the last couple decades since we last saw him. Kor
I think the destruction of Romulus does affect him quite deeply for the following reasons: The failure of the armada to save countless lives is too much for him, both Romulan and Federation (the armada is probably mostly destroyed). The loss of Spock has deep significance to him, due to their connection (and also Picard's to Sarek). He lives on Romulus with his family, and they are killed. He couldn't save them. It has to be something fairly straightforward. No deep political intrigue like the OP suggests.
Unless said failed rescue mission involves the destruction of the Enterprise and/or the death of someone from the TNG crew. I could see that having an impact on him. Add on top of that if this failed rescue mission is indeed the destruction of Romulus, then there's the loss of an entire planet and empire on his watch, along with the presumed loss of a Federation legend (Spock). Yeah, this could well lead to him deciding to leave Starfleet.
I would say, the reason he'd left, is that he'd lost his wife during the rescue mission. and figure that he couldn't rise their kids and serve in Starfleet at the same time.
The voice-over clearly said that "we let you down". That's more than an accident to me, that's something been done intentionally
In his grief over the loss of his Romulan wife and family, Picard marked his face in the traditional Romulan manner of mourning (he didn't even have to shave his head), and he fully intended to go join Nero's crusade. But Nero disappeared into the black hole before Picard could make the rendezvous, leaving Picard feeling hollow and unfulfilled for years to come. Kor
He left Starfleet because the writers didn't give him anything interesting to do. He missed being an explorer but the writers refused to write about that. So he quit. Picard was the Captain of the flagship and there was a whole galaxy for him to explore. The Picard of TNG wouldn't abandon that.
My own theory is that Picard led a rescue mission to Romulus but at the last minute, Starfleet told him and the armada to stand down and he was forced to just sit and watch possibly billions die. It may even have been a case that some high-ranking admiral may have suddenly decided that a devastated Romulan Empire was actually good for the Federation, and Picard may have felt he could no longer stay in Starfleet after that...
More likely, it would've been because of slow bureaucracy needing to do endless scientific studies on the star in-question (like in the TNG episode "Force of Nature") which creates delays that otherwise could've saved millions. It's also doubtful that the Romulans themselves would be excited about the idea of allowing a Starfleet armada into the heart of its space unless all parties were completely convinced and completely trusting of one another.
As others have stated it depends on the nature of failure. If he failed in saving lives, or lost crew in the name of the rescue mission then I could see that weighing on him significantly. Not everyone processes through things the same way, so context is going to be a huge part of this failure for Picard and his retirement.
So basically, Picard and the rescue armada he organized were chomping at the bit to go, but bureaucracy and maybe a big helping of self-interest by the Federation/Starfleet delayed it enough that they weren't even close to being on time and that made him bitter and quit? That might be interesting, especially if Picard feels that certain parts of command might not have been entirely unhappy with the situation from a threat assessment standpoint.
I said this before, but he left because he doesn't like the new Federation president who wants to build a wall around Federation and block any Romulan refugees from coming in. Kurtzman has said the Picard will feel more "real world" (compared to Discovery).
If it is the Hobus event that made him leave Starfleet, I'm unsure of the year in which STP will be set. If Hobus happened 15 years ago (2387), then STP starts off in 2402. Didn't we hear it would be set either 2389 or 2399? Maybe I'm missing something.