Database Scaling & Distributed Consistency
As systems scale from thousands to millions of users, a single database instance inevitably becomes the primary bottleneck for CPU, memory, and I/O capacity.
1. Database Replication (Primary-Replica)
Replication distributes copies of database data across multiple server instances.
Primary-Replica (Master-Slave) Architecture:
flowchart TB
Apps["📱 Applications"] -->|"Writes (INSERT / UPDATE / DELETE)"| Primary["🗄️ Primary DB"]
Primary -->|"Async / Sync Replication"| R1["📗 Replica 1"]
Primary -->|"Async / Sync Replication"| R2["📗 Replica 2"]
Primary -->|"Async / Sync Replication"| R3["📗 Replica 3"]
Apps -->|"Reads (SELECT)"| R1
Apps -->|"Reads (SELECT)"| R2
Apps -->|"Reads (SELECT)"| R3
| Trait | Single DB Instance | Primary-Replica Architecture |
|---|---|---|
| Read Capacity | Bound to 1 machine | Multiplied by number of read replicas |
| Write Capacity | Bound to 1 machine | Still bound to single primary node |
| Fault Tolerance | Single Point of Failure (SPOF) | Automatic failover promotes a replica if primary dies |
| Consistency | Immediate consistency | Potential replication lag (eventual consistency on replicas) |
2. Sharding (Horizontal Partitioning)
Sharding splits huge datasets across multiple independent database nodes according to a Shard Key.
Common Sharding Strategies:
| Strategy | Mechanism | Pros | Cons |
|---|---|---|---|
| Hash-Based | hash(user_id) % num_shards |
Uniform distribution; prevents hotspots | Adding new shards requires reshuffling data (unless using Consistent Hashing) |
| Range-Based | Split by alphabet (A-M in Shard 1, N-Z in Shard 2) or dates |
Easy range scans (WHERE date BETWEEN...) |
Severe hotspotting if traffic skews toward recent dates |
| Geographic | Route by user region (US, EU, APAC) |
Low latency; compliance with data sovereignty laws (GDPR) | Unbalanced load across geographic timezones |
| Directory-Based | Central lookup service maps keys to shard locations | Dynamic and flexible | Lookup service is a SPOF and extra latency hop |
Sharding Trade-offs & Challenges:
- Cross-Shard JOINs: Extremely slow and complex across separate database networks.
- Cross-Shard Transactions: Requires distributed 2-Phase Commit (2PC), reducing throughput.
- Resharding & Rebalancing: Splitting an overloaded shard requires live data migration.
3. CAP Theorem
The CAP theorem (Brewer's Theorem) states that a distributed data store can guarantee at most two out of three properties in the event of a network failure.
| Property | Core Guarantee |
|---|---|
| Consistency (C) | Every read returns the most recent write or errors out. |
| Availability (A) | Every non-failing node returns a non-error response for every request. |
| Partition Tolerance (P) | The system continues operating despite lost or delayed network messages. |
Crucial Reality: Network partitions (P) are unavoidable in distributed physical hardware. Therefore, the actual trade-off during a partition is between Consistency (CP) and Availability (AP):
| Trade-off Mode | Behavior During Partition | Systems Example |
|---|---|---|
| CP (Consistency + Partition Tolerance) | Refuses/blocks writes and inconsistent reads until network heals. | MongoDB, HBase, Redis Sentinel, etcd |
| AP (Availability + Partition Tolerance) | Accepts reads and writes immediately; synchronizes data eventually. | Apache Cassandra, AWS DynamoDB, CouchDB |
4. PACELC Theorem
PACELC extends CAP to describe system behavior even when there is no network partition:
- If Partition (P): Choose between Availability (A) and Consistency (C).
- Else (E): Choose between Latency (L) and Consistency (C).
flowchart LR
P{"Network\nPartition?"}
P -->|"Yes (P)"| PA["Choose:\nAvailability (A)\nor Consistency (C)"]
P -->|"No (E)"| EL["Choose:\nLatency (L)\nor Consistency (C)"]
PA --> DYN["DynamoDB, Cassandra → PA"]
PA --> PG1["PostgreSQL, MySQL → PC"]
EL --> DYN2["DynamoDB, Cassandra → EL"]
EL --> PG2["PostgreSQL, MySQL → EC"]
5. Consistency Models
| Consistency Level | Definition | Best Use Case |
|---|---|---|
| Strong Consistency | Reads are guaranteed to see the latest write across all nodes. | Banking, stock exchange, inventory reservation |
| Eventual Consistency | Given no new updates, all replicas eventually converge to the same value. | Social media feeds, product reviews, DNS records |
| Read-Your-Writes | A user will always see their own recent updates immediately. | User profile edits, comment posting |
| Monotonic Reads | Once a user reads a particular value, they will never observe an older value. | Timeline feeds, status updates |