Do you really think so? Consider the scenario of a social networking site having one table for users and another table for the users posts. If the developers don't know better than to query all my 200 shards at once this will be a pretty major performance killer.

I can instead use my userid as my shard-key and just query the shard holding the data for that user which will be way faster.

Scaling is everyone's responsibility.

The question is can middleware really solve the problem of having to re-engineer when you reach the limits of your current stack?

Not so much anymore. Scaling strategies are quickly becoming part of the required skill set for developers. And companies are expecting as much. Simplifying scalability through elegant abstractions, allowing developers to structure their data models and applications holistically, can be a great thing.

To be fair I think those are hints that improve performance rather than required syntax. The question I guess is whether these are "optional" in the sense that you get horrendous performance without them or truly optional and you get ok performance without them.