CockroachDB, I heard the dev experience of spinning up nodes can be dumbed down to 1 or 2 docker commands, and it's Postgres compatible, continues to have global transactions, although you wont get the same transaction performance as something GPS/clock hardware synced like Spanner, it's still a good self hosted solution.

If you're interested, CRDB writes a lot about how they do their transactions on their blog, it's good to know what guarantees you actually get with your DB's transactions.

For what reason do you need to scale it beyond a single instance?

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.

Right now I (well, we, my team) uses Hbase and in a previous project I used Cassandra. These are very high scale systems where we need replicated writes (for durability and availability), we need horizontal scalability and performance (write heavy or mixed read/write workloads) trumps transactionality. There aren't a lot of writeups about performance benchmarks between NoSQL and SQL but as far as I know it's not even close to the same ballpark: https://www.scylladb.com/2021/01/21/cockroachdb-vs-scylla-be...

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 ;)

I agree! I've seen Cassandra in production, and learned why it was put there. With it, you still get some of the SQL goodness, but most guarantees are only within one shard.

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.

Seeing most people go for the cloud what percentage of projects will outgrow say Aurora? and beyond Aurora there is a ton of options like CockroachDB, TiDB, Yugobyte etc.

If the data couldn’t be fully sharded (I.e. independent db’s and replicas), I’d personally choose Cockroach DB for this, I admittedly haven’t had the pleasure of using Cockroach, but I’ve seen alternative approaches and knowing what I wouldn’t do again…