There are a number of reasons why it is harder to horizontally distribute an RDBMS than a NoSQL system. Let me just start with tables and JOINs.
In a NoSQL database you may have very large denormalized tables, but they exist without JOINs between them. So you can partition and shard them fairly evenly across the nodes in a cluster. Sure, data will be duplicated, but each row can be dealt with atomically and locally. I look it up on node X, Y and Z where the replicas exist.
Now imagine that with a SQL JOIN. You have to look up the customer on X, Y and Z, but then their orders are elsewhere. So I have to look them up on A, B, and C.
Now consider more complex JOINs. The more JOINs you need to do means you are going to be hitting more and more of a cluster. Each JOIN is a performance hit and a data marshaling challenge before you return results back to the client.
Then there is the issue of automatic vs. manual sharding. While some SQL systems support automatic sharding, many require manual sharding to this day — which is often perilous. Many databases were never designed to be distributed, and such partitioning is an unnatural act imposed on them.
There are distributed SQL or "NewSQL" databases such as PostgreSQL, Spanner or CockroachDB that handle sharding and distribution better, but they don't obviate the issues of strong consistency/ACID transactions, which, when put up against speed-of-light propagation delays across datacenters, means you are going to be limited in terms of throughputs and latencies.
Which is why many NoSQL databases instead implement eventual consistency or tunable consistency, so you can have QUORUM or even LOCAL QUORUM consistency. This allows maintaining those higher throughputs and lower latencies. Which means you won't have as many timeouts or locking and blocking on operations.
There's also the issue of active-active vs. primary/replica. And this applies across SQL and NoSQL alike. Some systems just aren't designed for all read-write nodes. Instead they have a primary, and other nodes are read-only replicas. This can be problematic when it comes to mixed read-write or write-heavy workloads. Bottlenecking can occur. Plus it's a single point of failure, and you have to design for quick failover.
There are more systems in the NoSQL space that are designed for active-active leaderless topologies. Which means no single point of failure. And less bottlenecking on write-heavy workloads.
Active-active leaderless design also means being more homogenous in node operations. You're trying to avoid that one "more equal" peer, and especially trying to avoid that one "hot node" that everything has to be passing through.
Is it possible to do a good horizontal design with an RDBMS? Yes. There are good examples with PostgreSQL and CockroachDB. But because of strong consistency requirements, they will never be as good in terms of performance as a NoSQL system designed for fast eventually-consistent operations.
More details and considerations here:
Disclosure: I work for ScyllaDB; we make a NoSQL database.
