When does sharding a database actually become a performance bottleneck?

Everyone talks about sharding for scaling databases when they get big. It sounds like a great fix for huge amounts of data and traffic. But, I'm starting to wonder, when does sharding actually cause more trouble than it's worth? Like, cross-shard queries can be a total pain to deal with and make fast. And what about just handling the shards themselves, like moving data around or dealing with crashes across a bunch of different servers? It feels like there's a good point where sharding helps, but if you go too far, you're just piling on a lot of extra work and creating new problems. Has anyone had sharding actually make things worse for their system instead of better? What were the main signs you sharded too much, or in a way that messed up performance?