Distributed SQL and Sharding

When a single database server is no longer enough, you must distribute data across multiple machines. This is where sharding and distributed SQL come into play.

Sharding

Sharding splits a large database into smaller, independent databases called “shards”. Each shard holds a subset of the data and runs on its own server (or set of servers).

-- Shard key determines which shard stores a row
-- Example: Shard by user_id
-- Shard 1: user_id 1-10000
-- Shard 2: user_id 10001-20000
-- Shard 3: user_id 20001-30000

Sharding Strategies

Strategy	How It Works	Best For
Range-Based	Partition by ID range (1-1000, 1001-2000)	Sequential data, simple to implement
Hash-Based	Hash the shard key, mod by shard count	Even distribution, no hot spots
Directory-Based	Lookup table maps keys to shards	Flexible, dynamic rebalancing
Geographic	Partition by region	Multi-region applications

Sharding Challenges

• Cross-shard queries: Queries that span multiple shards are slow and complex.
• Rebalancing: Adding or removing shards requires redistributing data.
• Transactions: Distributed transactions across shards are expensive (2PC).
• Joins: Joining data across shards must happen in the application layer.
• Auto-increment IDs: Global unique IDs are harder to generate (use UUIDs or Snowflake IDs).

Replication

Replication copies data from one server to another for redundancy and read scaling.

Type	Description
Master-Slave	One primary (writes), multiple replicas (reads)
Multi-Master	Multiple nodes accept writes (conflict resolution needed)
Synchronous	Write is committed on all replicas before ack (slower)
Asynchronous	Write is ack’d immediately, replicated later (faster, possible data loss)

Distributed SQL

Modern distributed SQL databases (like CockroachDB, YugabyteDB, Google Spanner) aim to provide the SQL interface and ACID guarantees of traditional databases while scaling horizontally like NoSQL.

Key features:

• Automatic sharding and rebalancing.
• Distributed ACID transactions across shards.
• SQL interface (joins, aggregations, constraints).
• Multi-region deployment with low-latency reads.

Distributed SQL vs NoSQL Sharding

Aspect	Distributed SQL	NoSQL Sharding
Consistency	Strong ACID	Eventual (BASE)
Query Capability	Full SQL (joins, aggregates)	Limited (key-based lookups)
Transactions	Distributed transactions	Single-document transactions
Operational Complexity	Automated (rebalancing, repair)	Manual (shard management)

Read/Write Splitting (Application-Level Sharding)

For applications that need both consistency and scale, a common pattern is:

Writes   → Master PostgreSQL instance
Reads    → Read replicas (asynchronously replicated)
Analytics → Dedicated columnar store

This allows the main database to handle writes without being slowed down by heavy reporting queries.