What is Distributed SQL and How Does It Work?

Definition
Distributed SQL is a database architecture that provides full relational capabilities (ACID transactions, SQL queries, joins, foreign keys) across a horizontally scalable cluster of nodes. Unlike single-server databases limited to one machine, distributed SQL splits data into ranges (contiguous key spans) replicated across multiple nodes, enabling both higher capacity and automatic failover without sacrificing consistency guarantees.
How Data Distribution Works
Distributed SQL databases partition tables into ranges (sometimes called shards), where each range contains a contiguous span of primary key values. A table with 10 billion rows might split into 10,000 ranges of 1 million rows each. Each range is independently replicated across multiple nodes using consensus protocols. Raft (a leader-based protocol where the leader coordinates writes, and followers replicate them) or Paxos (a more complex protocol for agreeing on values) ensure that a write is committed only when a majority of replicas acknowledge it.
This replication provides both durability (data survives node failures) and availability (queries route to surviving replicas). The database tracks which node holds the leader replica for each range, routing writes to leaders and reads to any replica. When a leader fails, consensus elects a new leader from the remaining replicas within seconds, making failover automatic and transparent to applications.
Standard SQL Without Manual Sharding
Applications use standard SQL without awareness of data distribution. A query like SELECT * FROM orders WHERE customer_id = 12345 executes identically whether data spans one node or a thousand. The query planner identifies which ranges contain the relevant keys, routes sub-queries to those nodes, gathers partial results, and merges them into the final response. Joins, aggregations, and transactions work across ranges transparently.
This eliminates the complexity of manual sharding, where application code must route queries to correct shards, coordinate cross-shard transactions with application-level logic, and handle shard rebalancing when data or traffic patterns change. Distributed SQL hides all this complexity, letting developers focus on business logic rather than data distribution.
Performance and Scale Characteristics
The fundamental trade off is latency for scale. Single-node databases commit writes in under 1 millisecond because they need only flush to local disk. Distributed SQL adds consensus latency: within a single datacenter region, writes complete in 2 to 5 milliseconds for the network round-trips required by Raft or Paxos. Cross-region writes, where replicas span continents, take 50 to 150 milliseconds due to speed-of-light latency between datacenters.
In exchange, distributed SQL scales horizontally. Production deployments handle tens of thousands of nodes, petabytes of data, and millions of operations per second while surviving complete datacenter failures. The system automatically rebalances ranges across nodes as data grows or nodes join the cluster, maintaining even distribution without manual intervention.

💡 Key Takeaways

✓Distributed SQL provides ACID transactions and full SQL across horizontally scalable clusters using consensus replication (Raft or Paxos) for each data range

✓Ranges (contiguous key spans) are independently replicated; writes commit when a majority of replicas acknowledge, ensuring durability across node failures

✓Applications use standard SQL without manual shard routing; the database automatically handles data placement, query routing, and cross-range transactions

✓Write latency increases from under 1ms (single node) to 2 to 5ms (single region) or 50 to 150ms (cross region) due to consensus coordination

✓Automatic failover elects new leaders from surviving replicas within seconds when nodes fail, transparent to applications

✓Production deployments scale to tens of thousands of nodes, petabytes of data, and millions of operations per second

📌 Interview Tips

1Explain the trade off: distributed SQL sacrifices sub-millisecond latency for horizontal scale and automatic failover that single-node databases cannot provide

2Discuss consensus overhead: every write requires majority acknowledgment from replicas, adding network round-trips that single-node databases avoid

3Mention transparent routing: applications write standard SQL, the database handles data distribution, eliminating application-level sharding logic

← Back to Distributed SQL (CockroachDB, Spanner) Overview