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

Design: Database Replication (Master-Slave)

Design focuses on the replication architecture, data flow, and failover handling. Out of scope are specific database engine internals and network infrastructure details.

Functional Requirements

FR1: Maintain one master database that handles all write operations.

FR2: Replicate data from the master to one or more slave databases for read operations.

FR3: Ensure slaves are eventually consistent with the master.

FR4: Support read scaling by distributing read queries to slaves.

FR5: Handle failover scenarios where a slave can be promoted to master if needed.

FR6: Provide monitoring for replication lag and health.

Non-Functional Requirements

NFR1: Replication lag should be minimal, ideally under 1 second.

NFR2: System should support up to 10,000 read queries per second.

NFR3: Write latency on master should remain under 100ms.

NFR4: Availability target: 99.9% uptime (about 8.77 hours downtime per year).

NFR5: Data consistency model: eventual consistency between master and slaves.

Think Before You Design

Questions to Ask

❓ Question 1

❓ Question 2

❓ Question 3

❓ Question 4

❓ Question 5

❓ Question 6

Key Components

Master database server

Slave database servers

Replication mechanism (e.g., binary log shipping, streaming replication)

Load balancer or proxy for read queries

Monitoring and alerting system

Failover management component

Design Patterns

Master-slave replication pattern

Read-write splitting

Failover and leader election

Eventual consistency

Monitoring and alerting

Reference Architecture

          +-------------------+
          |    Application    |
          +---------+---------+
                    |
          +---------v---------+
          |  Load Balancer /   |
          |  Read Proxy       |
          +----+----------+---+
               |          |
       +-------v--+   +---v-------+
       |  Slave 1  |   |  Slave 2  |
       +----------+   +-----------+
               ^          ^
               |          |
          +----+----------+---+
          |     Master DB      |
          +--------------------+

Components

Master Database

Relational DBMS with replication support (e.g., PostgreSQL, MySQL)

Handles all write operations and generates replication logs.

Slave Databases

Same DBMS as master

Receive and apply replication logs from master; serve read queries.

Replication Mechanism

Streaming replication or binary log shipping

Transfers data changes from master to slaves asynchronously.

Load Balancer / Read Proxy

Proxy server or middleware (e.g., HAProxy, ProxySQL)

Distributes read queries among slaves to scale reads.

Monitoring System

Monitoring tools (e.g., Prometheus, Grafana)

Tracks replication lag, server health, and alerts on issues.

Failover Manager

Automated failover tool (e.g., Patroni, Orchestrator)

Detects master failure and promotes a slave to master.

Request Flow

1. 1. Application sends write queries to the master database.

2. 2. Master executes writes and records changes in a replication log.

3. 3. Replication mechanism streams or ships logs to slave databases asynchronously.

4. 4. Slaves apply changes from the replication log to update their data.

5. 5. Application sends read queries to the load balancer or read proxy.

6. 6. Load balancer routes read queries to one of the slave databases.

7. 7. Monitoring system continuously checks replication lag and server health.

8. 8. If master fails, failover manager promotes a slave to master and updates routing.

Database Schema

Entities remain the same as the application database schema. Replication is at the database engine level, so no schema changes are required. Relationships and tables are replicated as-is from master to slaves.

Scaling Discussion

Bottlenecks

Master database write throughput limits.

Replication lag increases with network or load.

Load balancer becomes a single point of failure.

Failover delay impacts availability.

Monitoring system overload with many replicas.

Solutions

Scale master vertically or use sharding for write scaling.

Optimize replication by compressing logs and using faster network links.

Use multiple load balancers with health checks and failover.

Automate failover with fast detection and promotion tools.

Distribute monitoring load and aggregate metrics efficiently.

Interview Tips

Time: Spend 10 minutes understanding requirements and clarifying constraints, 20 minutes designing the architecture and data flow, 10 minutes discussing scaling and failover, and 5 minutes summarizing.

Explain the master-slave roles clearly and why writes go to master only.

Discuss eventual consistency and replication lag trade-offs.

Describe how read scaling is achieved by distributing reads to slaves.

Highlight failover strategy and monitoring importance.

Mention bottlenecks and realistic scaling solutions.

Practice

1. What is the primary role of the master in a master-slave database replication setup?

easy

A. Handle all write operations and propagate changes to slaves

B. Serve read requests only

C. Backup data from slaves

D. Monitor network traffic between nodes

Solution

1. Step 1: Understand master responsibilities

   The master database is responsible for handling all write operations to ensure data consistency.

2. Step 2: Understand slave responsibilities

   Slaves replicate data from the master and typically handle read requests to reduce load on the master.

3. Final Answer:

   Handle all write operations and propagate changes to slaves -> Option A

4. Quick Check:

   Master = writes + replication [OK]

Hint: Master handles writes; slaves handle reads [OK]

Common Mistakes:

• Thinking slaves handle writes
• Confusing backup role with master
• Assuming master only monitors

2. Which of the following is the correct sequence for setting up master-slave replication?

easy

A. Configure slaves only, master is optional

B. Configure slaves, then configure master, then start replication

C. Start replication, then configure master and slaves

D. Configure master, then configure slaves, then start replication

Solution

1. Step 1: Configure master first

   The master must be configured first to accept writes and log changes for replication.

2. Step 2: Configure slaves after master

   Slaves are then configured to connect to the master and replicate its data.

3. Final Answer:

   Configure master, then configure slaves, then start replication -> Option D

4. Quick Check:

   Master setup before slaves [OK]

Hint: Master setup precedes slaves setup [OK]

Common Mistakes:

• Configuring slaves before master
• Starting replication before configuration
• Ignoring master configuration

3. Consider a master-slave setup where the master receives 100 writes per second and each slave replicates with a 2-second lag. If a read request is sent to a slave immediately after a write, what is the likely result?

medium

A. The slave returns the latest data including the write

B. The slave crashes due to replication lag

C. The slave returns stale data missing the latest write

D. The slave rejects the read request

Solution

1. Step 1: Understand replication lag effect

   Replication lag means slaves are behind the master by 2 seconds, so recent writes are not yet applied.

2. Step 2: Analyze read request timing

   A read immediately after a write will hit the slave before replication applies the change, so stale data is returned.

3. Final Answer:

   The slave returns stale data missing the latest write -> Option C

4. Quick Check:

   Replication lag causes stale reads [OK]

Hint: Replication lag means slaves have delayed data [OK]

Common Mistakes:

• Assuming slaves always have latest data
• Thinking slaves reject reads during lag
• Believing slaves crash due to lag

4. A system uses master-slave replication but experiences inconsistent reads from slaves. Which change can fix this issue?

medium

A. Allow writes on slaves to sync data faster

B. Increase monitoring and reduce replication lag

C. Disable replication and use only master for reads

D. Add more slaves without changing replication

Solution

1. Step 1: Identify cause of inconsistent reads

   Inconsistent reads usually happen due to replication lag causing slaves to have outdated data.

2. Step 2: Fix by reducing replication lag

   Improving monitoring and optimizing replication reduces lag, making slave reads more consistent.

3. Final Answer:

   Increase monitoring and reduce replication lag -> Option B

4. Quick Check:

   Reduce lag to fix inconsistent reads [OK]

Hint: Reduce lag to fix stale slave reads [OK]

Common Mistakes:

• Allowing writes on slaves breaks consistency
• Disabling replication loses read scaling
• Adding slaves without lag fix doesn't help

5. You are designing a high-availability system using master-slave replication. Which approach best ensures minimal downtime and data loss during master failure?

hard

A. Automatically promote a slave to master and redirect writes

B. Stop all writes until the master is manually restored

C. Allow writes on slaves during master downtime

D. Use only one master without slaves to avoid complexity

Solution

1. Step 1: Understand master failure impact

   If the master fails, writes cannot proceed unless a new master is available.

2. Step 2: Use automatic failover

   Promoting a slave to master automatically and redirecting writes ensures availability and minimal data loss.

3. Step 3: Evaluate other options

   Stopping writes causes downtime; writing on slaves breaks consistency; single master lacks redundancy.

4. Final Answer:

   Automatically promote a slave to master and redirect writes -> Option A

5. Quick Check:

   Automatic failover ensures availability [OK]

Hint: Auto-promote slave for failover [OK]

Common Mistakes:

• Stopping writes causes downtime
• Writing on slaves breaks data integrity
• Ignoring failover leads to outages