Database replication (master-slave) in HLD - System Design Guide

Problem Statement

When a single database server handles all reads and writes, it becomes a bottleneck causing slow response times under heavy load. If this server fails, the entire system loses data availability, leading to downtime and potential data loss.

Solution

Database replication creates copies of the primary database (master) on one or more secondary servers (slaves). The master handles all writes and propagates changes to slaves, which serve read requests. This spreads read load and provides failover options if the master fails.

Architecture

Client

→Master

↓

→Slave2

This diagram shows clients sending writes to the master database, which replicates data asynchronously or synchronously to multiple slave databases that handle read requests.

Trade-offs

✓ Pros

→

Improves read scalability by distributing read traffic across multiple slaves.

→

Provides high availability through failover to slave if master fails.

→

Reduces load on master, improving write performance stability.

→

Enables backup and analytics on slaves without impacting master.

✗ Cons

→

Replication lag can cause slaves to serve stale data temporarily.

→

Write operations still bottleneck on the single master node.

→

Complexity in handling failover and ensuring data consistency.

→

Increased infrastructure and maintenance overhead.

Use when read traffic significantly exceeds write traffic (e.g., read-to-write ratio > 5:1) and high availability is required for read operations.

Avoid when write traffic is very high and requires horizontal scaling, or when strict real-time consistency between reads and writes is mandatory.

Real World Examples

Twitter

Uses master-slave replication to scale read-heavy timelines while writes go to the master, balancing load and improving availability.

GitHub

Employs master-slave replication to separate read queries from write operations, ensuring fast repository browsing without impacting commit performance.

Shopify

Uses replication to offload reporting and analytics queries to slaves, preventing impact on transactional master database.

Alternatives

Multi-master replication

Allows multiple nodes to accept writes and replicate changes to each other, unlike master-slave where only one node accepts writes.

Use when: Choose when write availability and horizontal write scaling are critical, and conflict resolution mechanisms are in place.

Sharding

Splits data horizontally across multiple databases, each responsible for a subset of data, rather than replicating the same data across nodes.

Use when: Choose when dataset size or write throughput exceeds single database capacity.

Summary

Master-slave replication prevents a single database server from becoming a read bottleneck by distributing reads to replicas.

It improves availability by allowing failover to slaves if the master fails, but writes remain centralized on the master.

Replication lag and write bottlenecks are key trade-offs to consider when using this pattern.