I'm the author of Redis so I should be biased the other way around, but the Redis and KV experience taught me that to be exposed to the KV parading was for me a similar experience as learning the Scheme language, I started to write code in imperative languages in a new way. In the same way once you start thinking at scalability of your data in a new way, from the point of view of partitioning the data, organizing this data in away that is easy to access for your usage pattern, make judicious use of serialization, SQL databases can be a good pick. Simply you need to abandon the paradigm of let's design our nice tables and run our multiple joins and group by against.

But of course designing DBs in this new way makes most of the SQL features not needed in most scenarios... and you still have the overhead. And there are a number of other limits now that complex designs are not a good idea, for instance there is no way to get back the data in the natural ordering (the order you pushed this data, or the reverse)...

This is where KV stores start to be interesting as real world alternative, not just in order to learn the paradigm of scalability. But again, if you don't trust KV stores it is truly possible to use an SQL database in a more conservative and scalable way.