When your application grows to millions of users, a single SQL database (like PostgreSQL or MySQL) will eventually hit a physical hardware limit. You can scale vertically (buying a server with more RAM and CPU), but eventually, you run out of money or reach the limits of modern hardware. At this point, you must scale horizontally—spreading your data across multiple servers. This is the domain of Replication and Sharding.
Module 1: Read-Heavy Architectures (Replication)
Before attempting sharding, analyze your traffic. Most web applications (like Twitter or Medium) have a Read-to-Write ratio of 99:1 (100 people read a tweet for every 1 person who posts a tweet). If reads are your bottleneck, implement Master-Slave Replication.
The Replication Topology
- Primary (Master) Node: The only database server allowed to process WRITE operations (INSERT, UPDATE).
- Replica (Slave) Nodes: Multiple servers that contain a complete copy of the Master's data.
- The Flow: A user writes data to the Primary. The Primary records this in the Write-Ahead Log (WAL) and asynchronously streams the changes to the Replicas. The application routes all SELECT queries to the Replicas, instantly multiplying your read capacity.
Module 2: Write-Heavy Architectures (Sharding)
If you are building an IoT platform receiving 10,000 sensor updates per second, a single Primary node cannot handle the WRITE load. You must implement Sharding (Horizontal Partitioning).
Sharding splits your massive users table across multiple separate database servers. Instead of one server holding 100 million users, 4 servers hold 25 million users each.
-- Shard 1 (Database Server A)
SELECT * FROM users WHERE id BETWEEN 1 AND 25000000;
-- Shard 2 (Database Server B)
SELECT * FROM users WHERE id BETWEEN 25000001 AND 50000000;Module 3: Choosing a Sharding Key
The routing logic in your backend needs to know WHICH server to query. This requires selecting a Shard Key. Choosing the wrong key will destroy your database.
Sharding Strategies
- Range-Based Sharding: Partitioning by continuous values (e.g., users A-M on Shard 1, N-Z on Shard 2). DANGER: Can lead to 'Hotspots'. If you shard by date, and today is March 5th, 100% of the write traffic will hit the March shard, leaving the others completely idle.
- Hash-Based Sharding: You take an ID, run it through a Hash function (e.g.,
hash(user_id) % 4), and use the result to pick one of 4 shards. This guarantees a perfectly even distribution of data. DANGER: Adding a 5th server ruins the mathematical modulo, forcing you to move terabytes of data across the network to rebalance.
Module 4: Consistent Hashing
To solve the rebalancing nightmare of Hash-Based sharding, massive distributed databases (like Cassandra and DynamoDB) use Consistent Hashing.
Instead of mapping data directly to 4 physical servers, we map data to a virtual 'Hash Ring' containing millions of slots. The servers are then placed on this ring. If Server 1 crashes or Server 5 is added, only the data belonging to the immediate neighboring slots on the ring needs to be moved, dramatically minimizing network transfer during cluster scaling.
Module 5: The Death of the JOIN
The fatal drawback of sharding is the loss of relational capabilities.
SELECT u.name, o.total
FROM users u
JOIN orders o ON u.id = o.user_id;If users is sharded by UserID, and orders is sharded by OrderID, the database cannot perform a JOIN because the data lives on two entirely different physical machines. To solve this, you must either denormalize your database (duplicating data intentionally) or perform the JOIN in the application code (fetching users, fetching orders, and merging the arrays in memory).