Bird
Raised Fist0

Database Replication - Primary-Replica, Multi-Primary Patterns

HLDmediumInterview freq: common
Choose your preparation mode4 modes available
ArchitectureTraceChallenge
🎯
Database Replication - Primary-Replica and Multi-Primary Patterns
45 min · senior
senior

You are designing a database replication system for a 50 million daily active user (DAU) startup that requires high availability and low latency data access globally. The system must support both primary-replica replication for read scalability and multi-primary replication for geo-distributed writes. Business constraints include end-to-end encryption of data in transit and at rest, offline data delivery for mobile clients, and a P99 read latency under 200ms globally.

Constraints
  • End-to-end encryption of data both in transit and at rest
  • Support offline data delivery and sync for mobile clients
  • P99 read latency under 200ms globally
  • Support multi-primary writes with conflict resolution
  • Ensure strong consistency for primary-replica reads
  • Handle network partitions gracefully without data loss
📋
Guided Steps
1/5
Step 1
Choosing Replication Architecture
Which replication architecture will you choose to support both primary-replica and multi-primary patterns at 50M DAU scale?
💡 Hint
Consider architectures that separate write and read paths and support geo-distributed writes with conflict resolution.
Key Decision
Use Apache Cassandra with multi-datacenter replication for multi-primary writes and Apache Kafka for change data capture to primary-replica read replicas.
Interviewer Expectation
Mid: Names Cassandra for multi-primary and Kafka for replication. Senior: Explains Cassandra's tunable consistency and Kafka's log-based replication enabling ordered, durable change streams.
⚠️Common Wrong Answer
Using MySQL primary-replica with asynchronous replication only - leads to high replication lag and inconsistent reads at scale.
Step 2
Ensuring Data Consistency and Conflict Resolution
How will you ensure strong consistency for primary-replica reads and resolve conflicts in multi-primary writes?
💡 Hint
Think about quorum reads/writes and conflict-free replicated data types (CRDTs) or vector clocks.
Key Decision
Implement Cassandra's QUORUM consistency level for strong consistency on reads and writes, and use last-write-wins (LWW) with vector clocks for conflict resolution in multi-primary writes.
Interviewer Expectation
Mid: Mentions quorum reads/writes. Senior: Details vector clocks for causal ordering and explains trade-offs of LWW vs CRDTs in this context.
⚠️Common Wrong Answer
Relying on eventual consistency without conflict resolution - causes data anomalies and stale reads.
Step 3
Designing Offline Data Delivery and Sync
How will you design offline data delivery and synchronization for mobile clients with intermittent connectivity?
💡 Hint
Consider local caching, delta sync, and conflict detection mechanisms.
Key Decision
Use Apache Cassandra's timestamped writes combined with client-side SQLite for local caching and implement a sync protocol using Kafka change logs with cursor-based incremental updates.
Interviewer Expectation
Mid: Suggests local caching and periodic sync. Senior: Explains cursor-based incremental sync using Kafka offsets and conflict detection on client-side with vector clocks.
⚠️Common Wrong Answer
Full data sync on every reconnect - leads to excessive bandwidth and high latency.
Step 4
Meeting Latency and Encryption Constraints
How will you ensure P99 read latency under 200ms globally while enforcing end-to-end encryption?
💡 Hint
Think about geo-distributed replicas, TLS, and encryption at rest with hardware security modules (HSMs).
Key Decision
Deploy geo-distributed Cassandra clusters with local read replicas, enforce TLS 1.3 for in-transit encryption, and use AWS KMS with envelope encryption for data at rest.
Interviewer Expectation
Mid: Mentions TLS and geo-replication. Senior: Details TLS 1.3 benefits, envelope encryption with KMS, and latency impact of cross-region reads avoided by local replicas.
⚠️Common Wrong Answer
Encrypting only in transit but not at rest - violates compliance and security requirements.
Step 5
API Design for Replication System
Sketch 3 core API endpoints: write, read, and conflict resolution/sync. What are their inputs, outputs, and guarantees?
💡 Hint
Consider idempotency for writes, consistency levels for reads, and conflict metadata for sync.
Key Decision
Write API: idempotent POST with vector clock metadata; Read API: GET with consistency level param; Sync API: GET with cursor token returning delta changes and conflict info.
Interviewer Expectation
Mid: Defines basic CRUD endpoints. Senior: Explains idempotency keys, consistency tuning per read, and cursor-based incremental sync with conflict metadata.
⚠️Common Wrong Answer
Stateless writes without idempotency - causes duplicate writes under retries.
🔌
API Design Challenge
Sketch 3 core API endpoints for the replication system: one for write operations, one for read operations, and one for conflict resolution or sync.
Hints
  • How does the client send writes to the multi-primary system?
  • How does the client read from replicas while ensuring consistency?
  • How does the system expose conflict resolution or sync status to clients?
📊
Scoring Rubric
▼ Show
Must Have - 60%
  • Use of a distributed database supporting multi-primary writes with tunable consistency (e.g., Cassandra)
  • Reliable replication mechanism with ordered change streams (e.g., Kafka)
  • Conflict resolution strategy for multi-primary writes (e.g., vector clocks)
  • Offline sync design with incremental updates and conflict detection
  • Encryption in transit and at rest meeting compliance
Good to Have - 30%
  • Geo-distributed replicas to meet global latency SLAs
  • Idempotent and consistent API design for writes and reads
  • Use of hardware security modules or cloud KMS for key management
Bonus - 10%
  • Detailed explanation of failure modes during network partitions
  • Trade-offs between last-write-wins and CRDTs for conflict resolution
Anti-patterns to Avoid
Using asynchronous MySQL replication only - causes stale reads and high lag at 50M DAU
Ignoring conflict resolution in multi-primary writes - leads to data inconsistency and user confusion
Full data sync on every reconnect for offline clients - causes bandwidth spikes and poor UX
Encrypting only in transit but not at rest - violates security compliance and risks data leaks
Stateless write APIs without idempotency - causes duplicate writes under retries
Cross-region synchronous reads without local replicas - causes P99 latency spikes beyond 200ms
✅
Model Answer
▼ Reveal

The system uses Apache Cassandra for multi-primary replication with tunable consistency and Apache Kafka for change data capture enabling reliable primary-replica replication. Conflict resolution leverages vector clocks combined with last-write-wins to handle concurrent writes. Offline data delivery is supported via client-side SQLite caching and cursor-based incremental sync using Kafka offsets. Security is ensured with TLS 1.3 for in-transit encryption and AWS KMS envelope encryption for data at rest. Geo-distributed Cassandra read replicas ensure P99 read latency under 200ms globally. The APIs are designed for idempotent writes with vector clock metadata, consistent reads with tunable consistency levels, and incremental sync with conflict metadata.

Key Decisions
  • Use Cassandra's QUORUM consistency level for strong consistency on reads and writes
  • Implement vector clocks with last-write-wins for conflict resolution in multi-primary writes
  • Leverage Kafka offsets for cursor-based incremental sync to minimize bandwidth and latency
  • Deploy geo-distributed Cassandra read replicas to meet global latency SLAs
  • Enforce TLS 1.3 for encryption in transit and AWS KMS envelope encryption for data at rest
  • Design write API as idempotent POST with vector clock metadata to prevent duplicate writes
  • Design read API with tunable consistency level parameter to balance latency and consistency
  • Design sync API with cursor tokens returning delta changes and conflict metadata
  • Apache Cassandra multi-datacenter replication for scalable multi-primary writes with tunable consistency
  • Apache Kafka for change data capture and reliable primary-replica replication streams
  • Vector clocks combined with last-write-wins for conflict detection and resolution in multi-primary writes
  • Client-side local caching using SQLite with cursor-based incremental sync leveraging Kafka offsets
  • TLS 1.3 for in-transit encryption and AWS KMS envelope encryption for data at rest
  • Geo-distributed Cassandra read replicas to meet P99 latency under 200ms globally
  • Idempotent write API design with vector clock metadata to prevent duplicate writes
  • Cursor-based read and sync APIs supporting consistency level parameters and conflict metadata

Practice

(1/5)
1. For the fraud detection system's rule engine, which architecture best supports rapid rule updates without downtime while maintaining low latency scoring?
medium
A. Embed rules directly in the scoring service code and redeploy on changes
B. Use an external rule engine service with hot-reloadable rule definitions
C. Store rules in a database and query them synchronously during scoring
D. Compile rules into scoring service binaries and load at startup only

Solution

  1. Step 1: Evaluate embedding rules in code (A) and binaries (D)

    Both require redeployment or restart on rule changes, causing downtime or latency spikes.

  2. Step 2: Assess querying rules synchronously (C)

    Querying rules synchronously adds latency and risk of inconsistent rule versions during scoring.

  3. Step 3: Consider external rule engine with hot-reload (B)

    This allows dynamic rule updates without downtime and low latency via caching and efficient evaluation.

  4. Final Answer:

    Option B -> Option B

  5. Quick Check:

    External hot-reload rule engine -> dynamic updates, low latency [check]
Hint: Hot-reload rule engine enables live updates without downtime [check]
Common Mistakes:
2. For a mobile app with limited bandwidth and unpredictable network latency, which API design is better to minimize data usage and why?
medium
A. Use REST with multiple endpoints to fetch only needed data per screen
B. Use REST with a single endpoint returning all data to avoid multiple round-trips
C. Use GraphQL subscriptions to push all data updates continuously
D. Use GraphQL to request exactly the fields needed in one query, reducing over-fetching

Solution

  1. Step 1: Analyze bandwidth and latency constraints

    Mobile apps benefit from minimizing data transfer and round-trips due to limited bandwidth and latency.

  2. Step 2: Compare REST and GraphQL trade-offs

    REST multiple endpoints cause multiple round-trips; single endpoint REST over-fetches; GraphQL queries exactly needed fields in one request.

  3. Final Answer:

    Option D -> Option D

  4. Quick Check:

    GraphQL reduces over-fetching and round-trips for mobile [check]
Hint: GraphQL single query cuts data and round-trips [check]
Common Mistakes:
3. For a global typeahead autocomplete service that must support fuzzy matching and ranking by popularity, which architecture is best suited?
medium
A. Use Elasticsearch with fuzzy queries and custom scoring to handle ranking and typo tolerance
B. Use a prefix hash map with precomputed fuzzy variants for each prefix
C. Use an in-memory Trie for all prefixes with fuzzy matching logic implemented on top
D. Use a relational database with LIKE queries and additional application-side ranking

Solution

  1. Step 1: Evaluate fuzzy matching and ranking requirements

    Fuzzy matching and popularity ranking require complex scoring and typo tolerance, which Elasticsearch supports natively with fuzzy queries and custom scoring functions.

  2. Step 2: Assess other options

    Trie and prefix hash map lack built-in fuzzy matching and ranking; relational DB LIKE queries are inefficient and don't scale well for fuzzy search.

  3. Final Answer:

    Option A -> Option A

  4. Quick Check:

    Elasticsearch supports fuzzy search and ranking out-of-the-box [check]
Hint: Elasticsearch = fuzzy + ranking built-in [check]
Common Mistakes:
4. At 10x scale, which component in a synchronous quorum replication system is most likely to become the bottleneck and why?
hard
A. Quorum coordinator because it must wait for majority acknowledgments synchronously
B. Storage nodes because disk I/O saturates under high write volume
C. Network links because replication traffic grows linearly with replicas
D. Client-side libraries because they block waiting for acknowledgments

Solution

  1. Step 1: Identify synchronous quorum coordinator role

    Coordinator waits synchronously for majority acks, causing latency and throughput limits.

  2. Step 2: Compare other components' scaling challenges

    Storage nodes and network scale better; client-side blocking is per client, less centralized.

  3. Final Answer:

    Option A -> Option A

  4. Quick Check:

    Coordinator sync wait limits throughput at scale [check]
Hint: Coordinator sync wait blocks throughput first at scale [check]
Common Mistakes:
5. At 10x scale, which component in a Blue-Green deployment system is most likely to fail first and why?
hard
A. Deployment pipeline because build servers cannot handle 10x concurrent builds, delaying releases
B. Green environment servers because maintaining two full production environments doubles resource usage and costs
C. Load balancer because it must instantly switch traffic for millions of users, causing connection storms
D. Feature flag service because it cannot handle 10x toggle evaluations per request

Solution

  1. Step 1: Identify scaling bottlenecks

    Blue-Green requires two full environments; load balancer scales horizontally; feature flags scale independently.

  2. Step 2: Analyze deployment pipeline

    Build servers face 10x concurrent builds, causing queueing and delays, blocking releases.

  3. Step 3: Compare other components

    Load balancer and feature flags can be sharded; doubling environment cost is expensive but not a failure.

  4. Final Answer:

    Option A -> Option A

  5. Quick Check:

    Build servers bottleneck at 10x concurrent builds, delaying deployments [check]
Hint: Build servers choke first at 10x concurrent builds, blocking deploys [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