Bird
Raised Fist0

Database Replication - Primary-Replica, Multi-Primary Patterns

HLDmediumInterview freq: common
Choose your preparation mode4 modes available
ArchitectureTraceChallenge
🏗️
Database Replication - Primary-Replica, Multi-Primary Patterns
Designing scalable, consistent, and highly available database replication using primary-replica and multi-primary patterns
mediumDatabases - SQL and TransactionsFreq: common
⚙️
Functional Requirements
  • Support primary-replica replication with asynchronous replication from primary to replicas
  • Support multi-primary (multi-master) replication with conflict detection and resolution
  • Ensure strong consistency guarantees for primary-replica mode and eventual consistency for multi-primary mode
  • Provide automatic failover for primary node in primary-replica pattern
  • Allow read scaling by serving reads from replicas
  • Support transactional writes with ACID properties in primary-replica mode
📊
Non-Functional Requirements
  • Handle up to 2 billion daily active users (DAU)
  • Support peak write throughput of 925K writes per second (derived from 2B × 40 msgs/user/day)
  • Achieve 99.99% availability for writes and reads
  • Ensure 100ms P99 latency for read queries from replicas
  • Replication lag under 500ms in primary-replica mode
❓
Clarifying Questions to Ask
  • What consistency guarantees are required for multi-primary replication?
  • Is replication synchronous or asynchronous in primary-replica mode?
  • What is the expected replication lag tolerance?
  • Are conflict resolution policies predefined or customizable in multi-primary?
  • What is the expected read/write ratio?
  • Is cross-region replication required or only within a single region?
Out of Scope
✗ Designing the underlying storage engine or query optimizer
✗ Handling cross-datacenter geo-distribution beyond replication
✗ Designing client-side SDKs or ORMs
✗ Detailed schema design for application-specific data models
📌
Assumptions
  • 2 billion daily active users (DAU)
  • Each user generates 40 write messages per day
  • Average message size is 100 bytes
  • Read to write ratio is 5:1
⚡
QPS Calculation
write qps2,000,000,000 × 40 / 86,400 ≈ 925,926 writes/sec
peak write qpsAssuming 2× peak factor → 1,851,852 writes/sec
read qps5 × write_qps = 4,629,630 reads/sec
💾
Storage Estimate
daily storage2,000,000,000 × 40 × 100 bytes = 8,000,000,000,000 bytes = 8 TB/day
retention period30 days
total storage8 TB × 30 = 240 TB
with replicationAssuming 3 replicas → 240 TB × 3 = 720 TB total
🖥️
Server Estimate
write nodes38
read replicas47
🏗️
Architecture Diagram
Architecture Diagram
          +----------------+
          |   Client App   |
          +-------+--------+
                  |
                  v
          +-------+--------+
          |  Load Balancer |
          +-------+--------+
                  |
        +---------+----------+
        |                    |
        v                    v
 +------+-------+    +-------+-------+
 | Primary Node |    | Replica Nodes |
 +------+-------+    +-------+-------+
        |                    |
        |                    |
        |  Async Replication |
        +--------------------+
        |
        v
 +------+-------+
 | Conflict    |
 | Resolution  |
 | Service     |
 +------+-------+
        |
        v
 +--------------+
 | Multi-Primary|
 | Coordination|
 | Service     |
 +--------------+
🧩
Components
Primary Node
Handles all writes and synchronous transactions in primary-replica mode
Why thisPostgreSQL with built-in synchronous replication support ensures ACID compliance and strong consistency
Why notMySQL with asynchronous replication was rejected due to potential data loss and weaker consistency guarantees at 925K writes/sec
Replica Nodes
Serve read queries and asynchronously replicate data from primary
Why thisPostgreSQL replicas provide read scalability with replication lag under 500ms
Why notCassandra was rejected for replicas due to eventual consistency and lack of strong transactional support
Load Balancer
Distributes client requests to primary or replicas based on read/write nature
Why thisEnvoy proxy supports advanced routing and health checks for high availability
Why notDNS-based load balancing was rejected due to slow failover and lack of fine-grained routing
Conflict Resolution Service
Detects and resolves write conflicts in multi-primary replication using vector clocks
Why thisCustom service using vector clocks enables deterministic conflict resolution for multi-primary writes
Why notLast-write-wins was rejected due to data loss risk and non-deterministic outcomes
Multi-Primary Coordination Service
Coordinates writes across multiple primaries using consensus protocol
Why thisEtcd provides Raft-based consensus ensuring linearizability and fault tolerance for multi-primary coordination
Why notZookeeper was rejected due to higher operational complexity and weaker linearizability guarantees
🔄
Data Flow
1
Client App → Load Balancer
Send read or write request
Load balancer routes writes to primary, reads to replicas
2
Load Balancer → Primary Node
Forward write requests
Primary executes transaction synchronously
3
Primary Node → Replica Nodes
Asynchronously replicate committed writes
Replication lag kept under 500ms
4
Replica Nodes → Load Balancer
Serve read queries
Read scaling achieved by offloading reads
5
Multi-Primary Nodes → Multi-Primary Coordination Service
Coordinate concurrent writes using Raft consensus
Ensures linearizable writes across primaries
6
Multi-Primary Coordination Service → Conflict Resolution Service
Detect and resolve conflicts using vector clocks
Ensures deterministic conflict resolution
7
Conflict Resolution Service → Multi-Primary Nodes
Apply resolved writes
Updates propagate to all primaries
🗄️
UserMessages
PK: user_id
user_idmessage_idmessage_contentmessage_timestampstatus
🗄️
ConflictLogs
PK: conflict_id
conflict_idmessage_idconflict_typeresolution_dataresolution_timestamp
🔌
/writeMessage
Request
{
  "user_id": "string",
  "message_content": "string"
}
Response
{
  "status": "success|failure",
  "message_id": "string"
}
API to write a new message, routed to primary node
🔌
/readMessages
Request
{
  "user_id": "string",
  "limit": "int"
}
Response
{
  "messages": "array of message objects"
}
API to read messages, served from replicas for scalability
🔌
/resolveConflict
Request
{
  "conflict_id": "string",
  "resolution_data": "object"
}
Response
{
  "status": "success|failure"
}
API to manually resolve conflicts if automatic resolution fails

Practice

(1/5)
1. In a rate limiting system, at Step 3 the Service Layer checks the user's request count against its local cache. What happens if the cache TTL expires before the database updates the count?
easy
A. The service rejects all requests until the cache refreshes
B. The service allows requests assuming zero usage, risking over-limit breaches
C. The service blocks requests permanently due to stale data
D. The service queries the database synchronously for every request

Solution

  1. Step 1: Understand cache TTL impact

    When the cache expires, the service assumes no prior usage until refreshed, allowing requests that may exceed limits.

  2. Step 2: Evaluate other options

    Rejecting all requests or blocking permanently is too harsh; synchronous DB queries per request cause latency and overload.

  3. Final Answer:

    Option B -> Option B

  4. Quick Check:

    Expired cache -> assume zero usage -> risk over-limit [check]
Hint: Expired cache means zero usage assumed, risking breaches [check]
Common Mistakes:
2. In the search ranking system request flow, at step 3 the Ranking Component calculates BM25 scores for candidate documents. What happens if the document frequency (DF) data is stale at this step?
easy
A. The query is rejected until fresh DF data is available
B. BM25 scores are recalculated later with fresh DF, so initial ranking is unaffected
C. The system falls back to TF-IDF scoring automatically to avoid stale data impact
D. Ranking Component uses stale DF, causing inaccurate relevance scores and suboptimal ranking

Solution

  1. Step 1: Understand BM25 reliance on DF

    BM25 scoring depends on accurate document frequency to weigh term rarity; stale DF skews scores.

  2. Step 2: Trace impact of stale DF at Ranking Component

    Ranking uses stale DF directly, leading to inaccurate relevance scores and suboptimal document ordering; no automatic fallback or query rejection occurs.

  3. Final Answer:

    Option D -> Option D

  4. Quick Check:

    Stale DF causes inaccurate BM25 scores and ranking errors [check]
Hint: BM25 depends on fresh DF; stale DF skews scores [check]
Common Mistakes:
3. In a distributed lock system using ZooKeeper, at step 4 the client attempts to acquire the lock by creating an ephemeral sequential node. What happens if the client crashes immediately after creating this node but before confirming lock ownership?
easy
A. ZooKeeper automatically deletes the ephemeral node, releasing the lock
B. The ephemeral node remains until manually deleted, causing a deadlock
C. The client retains the lock until a timeout expires
D. The lock is transferred to the next waiting client immediately

Solution

  1. Step 1: Understand ephemeral node semantics

    Ephemeral nodes in ZooKeeper are automatically deleted when the client session ends (e.g., crash), preventing deadlocks (A) and stale locks.

  2. Step 2: Trace lock ownership after crash

    Since the ephemeral node is deleted, the next client waiting for the lock will be notified, but the lock is not immediately transferred (D) until they acquire it explicitly.

  3. Final Answer:

    Option A -> Option A

  4. Quick Check:

    Ephemeral node deletion on client crash -> lock released [check]
Hint: Ephemeral nodes vanish on client crash, freeing locks [check]
Common Mistakes:
4. At 10x scale, which component in the job portal system is most likely to become the first bottleneck, and why?
hard
A. WebSocket servers maintaining live candidate-job chat sessions, due to stateful connections limiting horizontal scaling
B. Relational database storing user profiles, due to complex joins and ACID constraints
C. Search index cluster, due to increased indexing latency from frequent job postings
D. Message queue cluster, due to partition limits causing uneven load distribution

Solution

  1. Step 1: Identify stateful components limiting scale

    WebSocket servers hold persistent connections bound to server memory, limiting resharding.

  2. Step 2: Compare other components

    Relational DB (A) can be scaled with sharding; search cluster (C) scales horizontally; message queues (D) can add partitions. Stateful WebSocket servers (B) face connection stickiness and memory limits first.

  3. Final Answer:

    Option A -> Option A

  4. Quick Check:

    Stateful WebSocket connections -> scaling bottleneck at 10x load [check]
Hint: Stateful WebSocket servers limit horizontal scaling first [check]
Common Mistakes:
5. At 10x scale, which component is most likely to fail first and why in Facebook Messenger's group chat system?
hard
A. Chat Servers because WebSocket connections are stateful and cannot be resharded easily
B. The database because it cannot handle 10x write throughput
C. Kafka brokers because partition count cannot be increased dynamically
D. Presence service because Redis TTL expiration causes message loss

Solution

  1. Step 1: Analyze stateful components at 10x scale

    Chat Servers maintain WebSocket connections bound to server memory, limiting horizontal scaling.

  2. Step 2: Compare other components

    Databases and Kafka can be sharded or scaled; Redis TTL issues cause transient problems but not first failure.

  3. Final Answer:

    Option A -> Option A

  4. Quick Check:

    Stateful WebSocket connections -> Chat Servers bottleneck at scale [check]
Hint: Stateful WebSocket binds connections -> Chat Servers bottleneck [OK]
Common Mistakes:
Recommended

Ready to prove it?

Pass the test and this pattern moves unknown strong while earning +1 Grip toward your certificate.

10 questions~5 mineasy hard