NoSql solves a different problem set. Modern SQL servers definitely have a lot limitations at huge scale. You can only write-scale so far before needing to resort to the variety of fragile SQL scaling solutions. (Read scaling has more options).
That's not to say they lose usefulness at scale or should be avoided because they'll be a "bottleneck" at some point (if a SQL database is ever a bottleneck, you're super lucky!) but there are definitely cases where a non-SQL database is the right choice.
In some cases, it's not a scaling but just a use-case difference (Elasticsearch)
