Bird
Raised Fist0

Why Relational Databases Dominate Transactional Systems

HLDmediumInterview freq: common
Choose your preparation mode4 modes available
ArchitectureTraceChallenge
🏗️
Why Relational Databases Dominate Transactional Systems
Exploring why relational databases remain the backbone of transactional systems at scale
mediumDatabases - SQL and TransactionsFreq: common
⚙️
Functional Requirements
  • Support ACID-compliant transactions ensuring atomicity, consistency, isolation, and durability
  • Provide strong consistency guarantees for concurrent transactional workloads
  • Support complex relational queries with joins and secondary indexes
  • Enable multi-row and multi-table transactions with rollback capabilities
  • Offer durability with crash recovery and write-ahead logging
  • Support horizontal scaling strategies for read and write workloads
📊
Non-Functional Requirements
  • Handle up to 2 billion daily active users generating 40 transactions per user per day
  • Achieve 99.99% availability with failover and replication
  • Maintain 100ms P99 latency for transactional queries under peak load
  • Support peak write throughput of approximately 925K writes per second
  • Ensure data durability with replication factor of 3 and crash recovery
❓
Clarifying Questions to Ask
  • What is the expected transaction size and complexity (single row vs multi-row)?
  • Are cross-shard or distributed transactions required?
  • What is the acceptable latency for commit operations?
  • Is strong consistency mandatory or can eventual consistency be tolerated?
  • What is the expected read-to-write ratio?
  • Are there specific compliance or durability requirements (e.g., audit logs)?
Out of Scope
✗ Designing NoSQL or eventual consistency systems
✗ Handling analytical or OLAP workloads
✗ Designing schema-less or document-based storage
✗ Real-time streaming or event sourcing architectures
📌
Assumptions
  • 2 billion daily active users (DAU)
  • Each user generates 40 transactional writes per day
  • Average transaction size is 100 bytes
  • Replication factor of 3 for durability
⚡
QPS Calculation
write qps2B × 40 / 86400 ≈ 925,926 writes/sec
peak write qpsAssuming 3× peak factor: 925,926 × 3 ≈ 2.78M writes/sec
read qpsAssuming 10 reads per write: 925,926 × 10 ≈ 9.26M reads/sec
💾
Storage Estimate
daily storage2B × 40 × 100 bytes = 8 TB/day
retention period30 days
total storage8 TB × 30 = 240 TB
with replication240 TB × 3 = 720 TB
🖥️
Server Estimate
write db nodesAssuming each node handles 50K writes/sec, need 2.78M / 50K ≈ 56 nodes
read replicasAssuming each replica handles 200K reads/sec, need 9.26M / 200K ≈ 47 replicas
🏗️
Architecture Diagram
Architecture Diagram
          +----------------------+
          |   Application Layer   |
          +----------+-----------+
                     |
          +----------v-----------+
          |  Load Balancer (HAProxy) |
          +----------+-----------+
                     |
        +------------+-------------+
        |                          |
+-------v-------+          +-------v-------+
|  Read Replica |          |  Primary DB   |
|  (PostgreSQL) |          |  (PostgreSQL) |
+-------+-------+          +-------+-------+
        |                          |
        |                          |
        |            +-------------v-------------+
        |            |  Write-Ahead Log (WAL)    |
        |            |  (PostgreSQL WAL Segment) |
        |            +-------------+-------------+
        |                          |
        |            +-------------v-------------+
        |            |  Replication Manager      |
        |            |  (Streaming Replication)  |
        |            +---------------------------+
🧩
Components
Primary DB (PostgreSQL)
Handles all write transactions ensuring ACID compliance with MVCC and WAL
Why thisPostgreSQL provides mature MVCC for concurrency, synchronous WAL for durability, and supports complex SQL queries with joins
Why notMySQL InnoDB lacks advanced MVCC and has less efficient write scaling at 2.78M writes/sec
Read Replica (PostgreSQL Streaming Replication)
Serves read-only queries to offload primary and improve read scalability
Why thisStreaming replication offers near real-time consistency with minimal lag and supports read scaling to 9.26M reads/sec
Why notAsynchronous replicas like in MySQL can lag causing stale reads, violating strong consistency
Load Balancer (HAProxy)
Distributes incoming requests to primary or read replicas based on query type
Why thisHAProxy supports TCP-level routing with health checks and low latency, critical for 100ms P99 SLA
Why notDNS-based load balancing lacks fine-grained control and health awareness
Write-Ahead Log (WAL)
Ensures durability by logging all changes before commit, enabling crash recovery
Why thisPostgreSQL WAL provides sequential disk writes optimized for durability and replication
Why notNoSQL systems like Cassandra use commit logs but sacrifice strong consistency guarantees
Replication Manager
Manages synchronous streaming replication to maintain consistency across replicas
Why thisEnsures replicas are consistent with primary, enabling failover and read scaling without stale data
Why notEventual consistency replication (e.g., DynamoDB) cannot guarantee transactional consistency
🔄
Data Flow
1
Application Layer → Load Balancer
Send transactional write or read query
Writes routed to primary, reads routed to replicas
2
Load Balancer → Primary DB
Forward write transaction
Primary executes transaction with MVCC and WAL logging
3
Primary DB → Write-Ahead Log
Append transaction changes to WAL before commit
Ensures durability and crash recovery
4
Write-Ahead Log → Replication Manager
Stream WAL segments to replicas
Synchronous replication maintains consistency
5
Replication Manager → Read Replica
Apply WAL changes to replica
Replica stays consistent and serves read queries
6
Load Balancer → Read Replica
Forward read queries
Read replicas handle high QPS reads with low latency
7
Read Replica → Application Layer
Return query results
Ensures 100ms P99 latency for reads

Practice

(1/5)
1. A messaging platform must handle 1 million messages per second with guaranteed ordering per conversation and durability. Which component best decouples message ingestion from fan-out delivery while preserving ordering?
easy
A. Use a Kafka topic partitioned by conversation ID
B. Use a relational database with row-level locks for ordering
C. Use a Redis pub/sub channel for message delivery
D. Use a simple in-memory queue per chat server

Solution

  1. Step 1: Identify Kafka's role in decoupling ingestion and delivery

    Kafka partitions by conversation ID ensure ordering per conversation and durability via replication.

  2. Step 2: Eliminate other options

    Redis pub/sub lacks durability and ordering guarantees; relational DBs can't handle 1M writes/sec with locking overhead; in-memory queues lack durability and don't scale across servers.

  3. Final Answer:

    Option A -> Option A

  4. Quick Check:

    Kafka with conversation_id partition -> ordering within conversation guaranteed [check]
Hint: Kafka partitions preserve ordering and durability [check]
Common Mistakes:
2. In a system where messages are produced to Kafka and consumed by a delivery worker, at step 4 the Delivery Worker reads the message offset from Kafka. What state does the Delivery Worker mark if the downstream consumer acknowledges receipt?
easy
A. Marks the message as sent but does not commit the offset yet
B. Deletes the message from Kafka immediately
C. Marks the message as delivered and commits the offset to Kafka
D. Retries sending the message regardless of acknowledgment

Solution

  1. Step 1: Understand Kafka offset commit semantics

    Kafka consumers commit offsets only after processing messages successfully to avoid reprocessing.

  2. Step 2: Delivery Worker behavior on acknowledgment

    Upon downstream acknowledgment, the worker marks the message as delivered and commits the offset, signaling safe processing.

  3. Final Answer:

    Option C -> Option C

  4. Quick Check:

    Offset commit after acknowledgment ensures exactly-once processing [check]
Hint: Commit Kafka offset only after downstream ack to avoid duplicates [check]
Common Mistakes:
3. For a full-text search system requiring low-latency queries and frequent index updates, which indexing architecture is most appropriate?
medium
A. Use a batch-only indexing system that rebuilds the entire index nightly
B. Use a single monolithic inverted index updated synchronously on every document change
C. Use a distributed inverted index sharded by document ID with eventual consistency
D. Use a multi-level index with a small in-memory index for recent updates and a large on-disk index for older data

Solution

  1. Step 1: Evaluate synchronous monolithic index (A)

    Updating a single large index synchronously causes high write latency and blocks queries.

  2. Step 2: Consider multi-level index (B)

    Multi-level indexing with a small in-memory index for recent changes enables low-latency queries and fast incremental updates, while the large on-disk index handles bulk data efficiently.

  3. Step 3: Analyze distributed sharding (C) and batch rebuild (D)

    Sharding by document ID with eventual consistency can cause stale query results; batch rebuilds cause high query latency during rebuild.

  4. Final Answer:

    Option D -> Option D

  5. Quick Check:

    Multi-level index balances update speed and query latency [check]
Hint: Multi-level index: fast recent updates, slow bulk storage [check]
Common Mistakes:
4. At 10x scale, which component of the distributed task scheduler is most likely to become the bottleneck first, and why?
hard
A. The network load balancer, as it cannot handle the increased connection count
B. The cron scheduler, because it cannot generate enough scheduled events concurrently
C. The worker nodes, because CPU resources are exhausted processing tasks
D. The distributed priority queue, due to contention on priority ordering and lease renewals

Solution

  1. Step 1: Analyze cron scheduler scalability

    Cron schedulers are typically lightweight and can be sharded or distributed; unlikely to bottleneck first (A).

  2. Step 2: Analyze worker node resource limits

    Workers scale horizontally; CPU exhaustion (C) is mitigated by adding nodes.

  3. Step 3: Analyze network load balancer capacity

    Load balancers scale with standard techniques; connection count (D) is manageable.

  4. Step 4: Analyze distributed priority queue bottlenecks

    Priority queue faces contention on ordering, lease renewals, and coordination overhead, which grows non-linearly with scale, causing it to bottleneck first (B).

  5. Final Answer:

    Option D -> Option D

  6. Quick Check:

    Priority queue contention and lease overhead limit scale first [check]
Hint: Priority queue contention limits scale before others [check]
Common Mistakes:
5. At 10x scale, which observability component is most likely to fail first and why?
hard
A. Centralized logging storage, because write throughput and storage costs grow linearly and cause bottlenecks
B. Alerting system, because it cannot process all incoming alerts in real-time at high scale
C. Distributed tracing backend, because the volume of spans grows exponentially with request fan-out, overwhelming storage and query systems
D. Metrics aggregation system, because it cannot shard time-series data effectively across many nodes

Solution

  1. Step 1: Analyze scaling characteristics of observability components

    Tracing volume grows exponentially with request fan-out, causing storage and query bottlenecks.

  2. Step 2: Compare with other components

    Logging scales linearly; metrics systems shard well; alerting scales with metrics, so less likely first failure.

  3. Final Answer:

    Option C -> Option C

  4. Quick Check:

    Tracing volume explosion -> backend overwhelmed first [check]
Hint: Tracing volume explodes with fan-out, stressing backend first [check]
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