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.