SQL past one server
Keeping tables, joins and transactions when the data or the write load no longer fits on one primary server.
What it asks of a system
A single PostgreSQL or MySQL primary can be scaled up and given read replicas, but every write still goes to one machine. Past that point there are two families. Sharding layers, such as Vitess for MySQL or Citus for PostgreSQL, split tables by a sharding key across many ordinary database servers and route each query to the shards it needs; queries and transactions that stay on one shard are cheap, and the rest cost more or are limited. Distributed SQL databases, such as Spanner, CockroachDB and TiDB, store data as ranges of an ordered key space, keep several synchronous replicas of each range, and split and move ranges between nodes on their own, so any transaction can span any rows.
The questions that separate them: does adding a node move data automatically, or does someone plan a reshard? Are transactions that touch several shards fully atomic and isolated, and at what level? Can a query join tables sharded on different keys? Do schema changes run online? And how compatible is the SQL with the PostgreSQL or MySQL that the application was written for, including the drivers, ORMs and extensions it uses?
Good at it · 7
Compare side by sideRelational databases
Alibaba Cloud PolarDBCommercialPolarDB-X shards tables on the primary key across Paxos-replicated data nodes, with two-phase commit and global timestamps.Alibaba Cloud’s cloud-native relational databases: MySQL- and PostgreSQL-compatible clusters whose compute nodes share one copy of the data on distributed storage, plus PolarDB-X, a distributed MySQL-compatible edition.
CockroachDBCommercialData is cut into ranges of up to 512 MiB that split, merge and rebalance on their own as nodes are added.A distributed SQL database that speaks the PostgreSQL wire protocol, replicates every slice of data with Raft and keeps transactions serializable across nodes and regions.
PlanetScaleCommercialVitess shards MySQL tables across many instances by a vindex, while the app sees one database behind VTGate.Managed MySQL on Vitess and managed Postgres, with schema changes shipped through branches and deploy requests.
CitusOpen sourceShards tables across ordinary Postgres workers by a distribution column, sends single-tenant queries to one node and fans the rest out.A PostgreSQL extension that shards tables across a cluster of ordinary Postgres servers, sending each query to the one shard it needs or to all shards in parallel.
TiDBOpen sourceRegions of up to 256 MiB split and move between TiKV nodes on their own, and stateless SQL nodes scale behind a load balancer.A distributed SQL database that speaks the MySQL protocol, stores rows in TiKV, a Raft-replicated key-value store, and can keep a columnar copy in TiFlash for analytics on live data.
VitessOpen sourceVTGate routes each query to the shards that hold its rows, and `Reshard` moves data to new shards with seconds of read-only time.A proxy layer, born at YouTube, that shards MySQL across many servers while the app still sees one database.
YugabyteDBOpen sourceTables are cut into tablets that split and rebalance on their own, each committing through its own Raft group, behind PostgreSQL’s query layer.A distributed SQL database that runs PostgreSQL’s own query layer on top of DocDB, a sharded, Raft-replicated storage engine built on RocksDB.Sources
- Vitess docs: Sharding
- Citus docs: Concepts (distributed tables, shards, co-location)
- TiDB docs: Architecture
- CockroachDB docs: Architecture overview
- Corbett et al., Spanner: Google’s Globally-Distributed Database (OSDI 2012)
Why each technology is listed comes from the sources on its own page.