Distributed SQL

An online game publisher stores every player's inventory: 40 million players, 2 billion items, and a marketplace where players trade items with each other. A trade must move a sword from one inventory and coins from another, both or neither. On one PostgreSQL primary that's an ordinary transaction, until write volume outgrows the one machine that accepts writes.

Sharding by hand spreads the writes but breaks the trade: the two players may live on different shards, and a transaction across them becomes the application's problem (Sharding & partitioning, Distributed transactions: 2PC & sagas). Distributed SQL moves that work into the database. It speaks SQL (CockroachDB uses PostgreSQL's wire protocol, so existing drivers work), splits tables automatically, and runs transactions across the pieces:

Underneath the tables, CockroachDB keeps all data as one sorted map of keys to values. A row's key is its table, its index and its primary key values; every secondary index is another set of keys, with the indexed columns in the key:

Sorted keys keep one player's items next to each other, so "show my inventory" is one short scan. The map is cut into contiguous ranges: CockroachDB splits a range when it passes 512 MiB, merges neighbors when they shrink below 128 MiB, and keeps a two-level index of which range holds which keys, cached on every node. Spanner calls the pieces splits and YugabyteDB calls them tablets; all three add split points automatically, and Spanner also splits by load, so a few very busy rows can each get their own server.

One consequence surprises people: a row and its index entries are different keys, and they can live in different ranges on different machines. Every secondary index turns a one-row insert into a multi-range write.

Each range is copied to three replicas by default, and the replicas form their own Raft group (Consensus & leader election). One replica holds the lease: it is the only one that serves reads or proposes writes for that range. A write commits once a majority of replicas have it:

The arithmetic is the usual one for majorities: n replicas survive (n − 1) ÷ 2 failures, so three survive one and five survive two. With thousands of ranges, every node is leaseholder for some and follower for others, so load spreads across the cluster. When a node joins or leaves, replicas are moved to rebalance; adding capacity is adding nodes.

A transaction that touches one range can commit in a single step: CockroachDB calls it the one-phase commit fast path. A trade between players in different ranges needs more. Each write is first laid down as a write intent, a provisional value that also acts as a lock, replicated like any write. A transaction record, stored in the range of the first write, says whether those intents are pending, committed or aborted:

Ranges that a transaction touches are replicated groups, so this is two-phase commit where every participant is fault-tolerant: one machine failing doesn't leave the transaction stuck, as it can in classic 2PC. The cost doesn't: each extra range adds work and each consensus round adds latency. Keeping transactions short and local, with related rows near each other, keeps them fast. Spanner offers interleaved tables for this, storing each child row right after its parent.

CockroachDB runs every transaction at SERIALIZABLE, the strongest isolation level, by default: the result is as if transactions ran one at a time (Transactions & isolation levels). When two transactions conflict, the database first tries to retry on its own; when it can't, it returns an error with SQLSTATE 40001 and the client must rerun the whole transaction.

A launch event shows the problem. 50,000 players try to buy from a limited stock of 1,000 dragon eggs at the same minute, and every purchase updates the same stock row. Each conflicting transaction is retried, and the retries conflict again. The fixes are the ones from Hot partitions & skew: split the counter into several rows, take reservations through a queue, or move the contested decision out of a long transaction:

• Always handle 40001. Wrap every transaction in a retry loop with backoff.
• Keep transactions short. No user think-time or network calls to other services inside one.
• Find hot rows early. Load-test launch traffic against the real schema.

Because ranges are slices of sorted keys, a key that always grows sends every insert to the same place. With sequential keys, all incoming writes go to the last range, and load-based splitting can't help because there's no split point that divides the traffic. Spanner has the same problem with monotonically increasing integers. The trade log, keyed by a timestamp, would put the whole marketplace's writes on one machine:

The usual choices:

• Random keys, such as UUIDs, spread inserts evenly.
• Hash sharding keeps a meaningful key but scatters it. YugabyteDB offers hash or range sharding per primary key; hash spreads load evenly and makes range queries inefficient.
• Prefix with the owner. Keying items by player ID first keeps each player's items together and spreads players across ranges, which suits the game's most common queries.

If one primary with read replicas can take the writes, it remains the simpler choice (Database replication). Distributed SQL earns its cost when writes or data outgrow one machine, when you need to survive a zone or region without data loss, or when data must be placed by region (Multi-region replication covers home regions and global tables).