Yes!

Distributed systems introduce severe restrictions on what can be reasonably done at scale. Having a single connection string is one thing, being able to do a massive JOIN is another (and should only be ever done in analytical databases).

The question is not "when sharding becomes counter-productive" but "when it starts making sense".

With sharding something somewhere has to know how to route queries to subsets of data. So it is a complexity price paid for being able to scale. If one can avoid paying this cost then he should.

And USUALLY cross-shard queries are not just expensive but simply impossible in operational clusters. Like, if you do COUNT on a table, you only count within a single db shard table.

Parent commenter was not asking about when sharding becomes counterproductive, it was asking about when hiding sharding from the application becomes counterproductive.

The point is that it’s a leaky abstraction. It should not be at the database proxy level.

Instead, the application should be responsible to route queries to the correct shard. That way it can’t make cross-shard queries or cross-shard transactions accidentally, it must do so explicitly.