Bird
Raised Fist0

Why Relational Databases Dominate Transactional Systems

HLDmediumInterview freq: common
Choose your preparation mode4 modes available
ArchitectureTraceChallenge
▶
Request Trace
Step 1/5
Client App

Sends transactional write request

Client issues a SQL INSERT to create a new record

Protocol:HTTP/2
BeforeClient has new data to persist but not yet sent
AfterWrite request is in-flight to Load Balancer
Load Balancer

Routes write request to Primary DB

HAProxy forwards SQL write to primary PostgreSQL instance

Protocol:PostgreSQL Protocol
BeforeLoad Balancer has incoming client request
AfterPrimary DB receives write request for processing
Primary DB (PostgreSQL)

Processes write and appends to WAL

Primary DB executes transaction and logs changes in WAL

Protocol:Internal DB operation
BeforePrimary DB has pending write request
AfterWrite durably recorded in WAL; transaction committed
Replication Manager

Streams WAL entries to replicas

Replication Manager ships WAL segments to read replicas

Protocol:WAL Streaming
BeforeWAL contains new committed transaction entries
AfterWAL data sent to replicas for synchronization
Read Replica (PostgreSQL Streaming Replication)

Applies WAL entries to update replica state

Replica replays WAL to reflect latest committed data

Protocol:Streaming Replication
BeforeReplica is slightly behind primary state
AfterReplica state updated; ready to serve consistent reads
🏗️
Architecture Diagram
Architecture Diagram
┌─────────────┐
│ Client App  │
└──────┬──────┘
       │ HTTP/2
       ▼
┌─────────────┐
│ Load Balancer│
│   (HAProxy) │
└──────┬──────┘
       │ PostgreSQL Protocol
       ▼
┌───────────────────────────────┐
│ Primary DB (PostgreSQL)       │
│ - Accepts writes              │
│ - Maintains Write-Ahead Log  │
└──────┬────────────────────────┘
       │ WAL Streaming
       ▼
┌───────────────────────────────┐
│ Replication Manager           │
│ - Manages WAL shipping        │
│ - Coordinates replicas        │
└──────┬────────────────────────┘
       │ Streaming Replication
       ▼
┌───────────────────────────────┐
│ Read Replica (PostgreSQL Replication)      │
│ Streaming Replication)        │
│ - Serves read-only queries    │
└───────────────────────────────┘
Design Rationale

This architecture highlights why relational databases dominate transactional systems. The Client App sends a transactional write routed by HAProxy to the Primary PostgreSQL DB, which processes the write and durably logs it in the Write-Ahead Log (WAL). The Replication Manager streams these WAL entries to Read Replicas using PostgreSQL's streaming replication protocol. This design ensures strong durability and consistency guarantees for writes while enabling read scalability through replicas. The WAL is critical for crash recovery and replication, making relational databases reliable for transactional workloads. The Load Balancer ensures all writes go to the primary, preserving consistency, while replicas serve read queries without impacting write performance.

Primary Focus

✓

Practice

(1/5)
1. A stock exchange system must handle millions of incoming orders per second and ensure strict ordering per stock symbol for matching. Which component is best suited to guarantee ordering and durability of these orders before matching?
easy
A. Use a partitioned Kafka topic keyed by stock symbol to persist and order incoming orders
B. Use a relational database with a global sequence number to store all orders
C. Use a distributed in-memory cache like Redis to store incoming orders temporarily
D. Use a simple message queue like RabbitMQ without partitioning to enqueue orders

Solution

  1. Step 1: Identify Kafka's role in ordering and durability

    Kafka partitions keyed by stock symbol ensure strict ordering per symbol and durable storage of orders before matching.

  2. Step 2: Eliminate other options

    Redis lacks durability guarantees; relational DB with global sequence is a bottleneck at scale; RabbitMQ without partitioning cannot guarantee ordering per symbol.

  3. Final Answer:

    Option A -> Option A

  4. Quick Check:

    Kafka partition keyed by symbol -> ordering + durability guaranteed [check]
Hint: Partition by key for ordering and durability [check]
Common Mistakes:
2. A distributed API rate limiter must enforce limits with minimal latency at the network edge. Which component is best suited to perform this enforcement?
easy
A. Centralized database that aggregates all request counts for global enforcement
B. Edge cache with local counters and periodic sync to central store
C. Batch job that processes logs every minute to update usage stats
D. Client-side SDK that tracks usage and blocks requests locally

Solution

  1. Step 1: Identify edge enforcement needs

    Enforcement must be low-latency and close to the client to avoid network round trips.

  2. Step 2: Evaluate options for latency and consistency

    Centralized DB (A) adds latency; batch jobs (C) are too delayed; client SDK (D) is untrustworthy. Edge cache with local counters (B) balances latency and eventual consistency.

  3. Final Answer:

    Option B -> Option B

  4. Quick Check:

    Edge cache enforces limits locally with periodic sync [check]
Hint: Enforce limits close to client with local counters [check]
Common Mistakes:
3. In a system where Step 4 is the Delivery Worker checking message delivery status, what happens if the Delivery Worker finds the recipient's presence TTL expired?
easy
A. The Delivery Worker queues the message for offline delivery and updates status to 'sent but not delivered'
B. The Delivery Worker immediately marks the message as delivered without retry
C. The Delivery Worker deletes the message assuming recipient is unreachable
D. The Delivery Worker retries sending indefinitely until TTL is refreshed

Solution

  1. Step 1: Trace Delivery Worker decision at Step 4 -- Bob's presence TTL

    If TTL expired, recipient is offline; message can't be delivered immediately.

  2. Step 2: Understand offline delivery handling

    Delivery Worker queues message for later delivery and marks status as 'sent but not delivered' to reflect state.

  3. Final Answer:

    Option A -> Option A

  4. Quick Check:

    Expired TTL -> queue message, status 'sent but not delivered' [check]
Hint: Expired TTL means offline, queue message for later delivery [check]
Common Mistakes:
4. In a token bucket rate limiter, at step 4 the Request Handler checks the token count before processing a request. What happens if the bucket is empty at this step?
easy
A. The token count is reset to maximum and the request proceeds
B. The request is immediately processed and the token count goes negative
C. The request is rejected or delayed until tokens are refilled
D. The request is queued indefinitely without token consumption

Solution

  1. Step 1: Trace Request Handler token check at Step 4

    If the token bucket is empty, no tokens are available to allow immediate processing.

  2. Step 2: Determine request fate

    The request must be rejected or delayed until tokens refill; processing without tokens violates rate limits.

  3. Final Answer:

    Option C -> Option C

  4. Quick Check:

    Empty bucket -> reject or delay request [check]
Hint: No tokens? Delay or reject request [OK]
Common Mistakes:
5. At 10x scale, which component is most likely to fail first and why?
hard
A. The event store (Kafka) because partition count limits throughput and rebalancing causes downtime
B. The read model database because it cannot handle the increased query load
C. The command handler because synchronous validation blocks under high load
D. The Delivery Worker because it cannot keep up with event consumption rate

Solution

  1. Step 1: Analyze event store scaling constraints

    Kafka partitions limit throughput; increasing partitions requires rebalancing, causing temporary unavailability.

  2. Step 2: Compare other components

    Read model DB can be scaled horizontally; command handler is async; Delivery Worker scales with consumers.

  3. Final Answer:

    Option A -> Option A

  4. Quick Check:

    Kafka partition limits and rebalancing -> first bottleneck at 10x scale [check]
Hint: Kafka partition limits -> first bottleneck at scale [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