With something like Cassandra you get read after write guarantees that I think you don't get with read replicas and SQL databases but I'm not too familiar with the latest there. Also once you start breaking up your SQL databases into shards you lose all SQL goodness across shards (like joins and other properties).
So no free lunches basically. I'd love for there to be something fast, scalable, queryable, transactional etc. etc. Honestly I haven't looked in a while but every open source SQL solution I've seen in the past that claimed to be durable/reliable/scalable doesn't actually do that under failure modes like NoSQL does and pretty much loses either performance or other guarantees or both. Ofcourse NoSQL doesn't solve some of these problems, but at least you know what you're getting into ;)
With that, you let go of the global ordering, because global ordering / single-point serialization just becomes technically infeasible. At that point you usually don't exactly need them, though. You only need local order within a transaction.
If all you need is to alleviate the high read load (many selects), nearly every SQL database (even SQLite!) has a free and supported way to create read replicas, usually out of the box.
If you need distributed transactional updates, MySQL / MariaDB has Galera (GPL), and Postgres has Citus (proprietary with a few AGPL parts).
And, of course, there is CockroachDB that prioritizes reliability over speed, but is truly distributed out of the box.