When does sharding a database become a performance bottleneck?

We're planning for growth and sharding has come up as a way to scale our main Postgres database. The idea is to distribute the load across multiple servers. But, I'm curious about the downsides. At what point does the overhead of managing shards, cross-shard queries, and distributed transactions start to eat into the performance gains? Are there specific traffic patterns or data access needs where sharding might actually hurt performance compared to a very large single instance?