This post shares my perspective on a topic we've frequently discussed (Community Open Hours, Episode 15: Colocated and Interleaved Tables) when comparing Distributed SQL (like YugabyteDB) and Sharded databases (Like Citus, Aurora Limitless): Should we co-locate rows together on a pre-determined one-to-many relationship to optimize local joins?

My response is: No, it's impossible to establish a single privileged hierarchy without seriously limiting the advantages of an SQL database. The key benefit of using relational databases is that they offer logical-physical independence, allowing applications to evolve independently of data physical organization. Determining a privileged hierarchy, like "customers-orders" for an e-commerce application, is easy when starting on a single use-case. But once a customer has passed an order, you need to query "products-orders" to check the stock, and your dreams of local joins vanish. I'm describing a Distributed database. In a multi-tenant database that uses sharding to isolate the tenants, the situation is different.

Sharded databases like Citus or Aurora Limitless require tables to be partitioned on the same key to favor local joins and local indexes. However, they are SQL databases only for single-shard transactions. Queries involving multiple shards have limitations that remind the NoSQL trade-offs, lacking foreign keys, unique indexes, and certain ACID properties. This can be verified easily using pgbench: Citus is not ACID but Eventually Consistent.

YugabyteDB is different. Instead of a sharding layer on top of PostgreSQL, it uses PostgreSQL on top of a sharding key-value storage, so that all SQL features are available globally.

There are many extensions and forks around PostgreSQL and all have their pros and cons. It is good to hear different points of views. I'm writing this while reading Marco Slot's slides, especially this one, which presents the agility of Distributed SQL architecture as a trade-off:

https://www.postgresql.eu/events/pgconfeu2023/sessions/session/4826/slides/416/Distributed%20PostgreSQL%202023.pdf

After reading the pros and cons of this slide, I wanted to give you more context about YugabyteDB, which has solved many of those problems.

• Good read and write availability (shard-level failover)

"Good availability" usually applies to the primary/standbys configuration with synchronous replication and automated failover. However, Distributed SQL databases offer better: resilience, fault tolerance on failure. In the event of a node, zone, or region failure, there is no need to perform a failover as the database will continue to function seamlessly, thanks to Raft leader leases.

• Single table, single key operations scale well

YugabyteDB scales all operations, including complex queries. The PostgreSQL layer is stateless. The Storage layer is sharded with each shard being an independent Raft group. The response time may increase when you add secondary indexes and join more tables. However, the scalability of the system remains intact. This is because you can add new nodes to the database, and the shards will be automatically split and re-balanced. As a result, the response time will remain consistent even when dealing with larger tables and more concurrent transactions. Of course, as I explained previously, scalability can be affected if the queries are poorly written or the configuration is incorrect.

• No additional data modeling steps or snapshot isolation concessions

YugabyteDB supports all PostgreSQL isolation levels. When modeling the data, it's important to consider the primary key and indexes to ensure proper distribution, and duplicate covering indexes for reference tables, but that's much simpler than manual sharding.

• Many internal operations incur high latency

Network latency can become a factor in reading and writing data in a distributed system. However, it's important to understand what is considered "high" latency. In the case of AWS Availability Zones, the latency is only one millisecond. Additionally, internal operations are batched and asynchronous, meaning that thousands of rows can be read or written within that one-millisecond operation. Here is a join between million-rows tables taking 10 milliseconds on YugabyteDB.

• No local joins in current implementations

I talked about this point earlier. YugabyteDB optimizes joins by batching outer rows and pushing down the join predicate to a loose index scan, not by constraining the physical organization of tables.

• Less mature and optimized than PostgreSQL

It is important to note that Distributed SQL is a newer technology compared to PostgreSQL or Oracle. YugabyteDB is a completely open-source database. This means that the more people who test new applications and provide feedback, the better the database becomes. Designing a new database also brings its optimizations. You don't need to worry about vacuuming, bloat, or transaction ID wraparound when using YugabyteDB (more here).

• General guideline: Just use PostgreSQL

That's why YugabyteDB offers more features than other distributed SQL databases. It achieves this by using PostgreSQL for the query layer instead of developing a new SQL parser and executor. This approach enables it to be compatible with numerous existing applications, frameworks, tools, and extensions.

Although some of the above points may also apply to other Distributed SQL databases, I wanted to be precise, with facts, about a specific database, YugabyteDB, instead of guessing based on the overall architecture. I'll be happy to read any comments and other points of view.