Does he do well only at smaller clubs? Not really, IMO - he's done well everywhere he's been, apart from his second spell at Chelsea (which was distinctly below par in terms of performances in European football). I think managers of the class of Mourinho and Fergie would've done well wherever they'd been - be it at a mega club, or a smaller one (the latter did and has tangible proof on his side, but Mourinho is still quite young in managerial terms, too - and the United job could be a great barometer in that sense because of the upper management's relatively less intrusive approach). Actually winning the Champions League requires a bit of random luck, so while he didn't lead Madrid to La Decima, his time there is slightly underrated in retrospective terms, though some Madrid fans will undoubtedly argue otherwise. Games at a certain stage are decided by extremely fine details - and we could easily be looking a realistic alternate history - where United's goal vs Porto stands, or Barcelona score a late goal at Camp Nou vs Internazionale to go through on away goals, or Garcia's goal is disallowed.
eg. at Madrid, first season: Finished 4 points behind Barcelona in the league, won the Copa del Rey - facing Barcelona in the final, the Champions League semi first leg was kinda decided in no small part by the genius of Messi at the Bernabéu.
Second season: Wins the league with record points and goals scored, faces Barcelona in the Copa de Rey - again, and this time the Catalans prevail, faces Heyneckes' Bayern in the Champions League semis, and somehow his best players forget how to take penalties:
Third season: This was a bit disappointing to be fair, especially with all the background drama. Dismantled in the first leg of the semi-finals vs Dortmund in the Champions League, distant second in the league, lose to Simeone's Atlético in the Copa de Rey final.
Mind, this was a Madrid team that hadn't reached the Champions League semi-final since 2002/03 - with 6 consecutive Round of 16 exits from 2004/05. And for added context, they were facing one of the best club sides of all time at their peak - with players that had developed chemistry and a common style over years (heightened by Guardiola's coaching), and he was facing an exceptional league manager. Plus, the little guy who could create something out of nothing, and if you take Pep's team out of the equation - he could have realistically won league titles on the bounce, a European Cup, and the might not have been sacked because the antagonism he created (that arguably led to his decline) wouldn't have been necessary. Ifs and buts, but again, the margins are very fine.