Distributed SQL

SQL was invented in the 1970s for databases running on a single computer. Every assumption in the SQL standard, transactions, JOINs, foreign keys, aggregations, assumes the data is in one place, accessible through one engine, with one consistent view.

Distributing SQL across multiple machines breaks all of these assumptions.

A JOIN between two tables is trivial when both tables are on the same disk. But what if the orders table is sharded across 10 nodes and the products table is sharded across 5 nodes? The database must figure out which nodes have the relevant data, ship partial results between nodes, merge them, and return a consistent answer, all while maintaining the illusion that the user is querying a single database.

This is the distributed SQL problem. The previous lesson covered NewSQL databases that solve it. This lesson digs into the mechanics: how data is distributed, how queries are routed, how distributed transactions work, and where the performance pitfalls hide.

Range-Based Sharding

Split data into ranges based on the primary key. If the key is an integer:

Node 1: keys 1 - 1,000,000
Node 2: keys 1,000,001 - 2,000,000
Node 3: keys 2,000,001 - 3,000,000

CockroachDB uses this approach, it calls each range a "range" (64 MB chunks by default). Ranges are automatically split when they grow too large and rebalanced across nodes.

Advantage: Range scans are efficient, contiguous key ranges likely live on the same node. Risk: Hot spots. If all new data has incrementally increasing keys (auto-increment IDs, timestamps), all writes go to the last node. CockroachDB mitigates this with UUID-based default primary keys.

Hash-Based Sharding

Hash the primary key and assign the hash to a node. node = hash(key) % num_nodes.

Advantage: Uniform distribution, no hot spots. Risk: Range scans are terrible, contiguous keys are scattered across all nodes. A query like WHERE id BETWEEN 100 AND 200 must hit every node.

Geo-Partitioning

Assign data to specific regions based on a column value. CockroachDB allows:

ALTER TABLE users PARTITION BY LIST (region) (
 PARTITION us_east VALUES IN ('us-east'),
 PARTITION eu_west VALUES IN ('eu-west')
);
ALTER PARTITION us_east OF TABLE users CONFIGURE ZONE USING
 constraints = '[+region=us-east]';

Users in the US are stored on US nodes. Users in the EU are stored on EU nodes. Reads are fast (local), writes are fast (local consensus), and you get data residency compliance for free.