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
45 min · senior
senior

You are designing the core transactional system for a 50 million daily active user (DAU) fintech startup. The system must handle high throughput financial transactions with strict consistency, support complex multi-row ACID transactions, and ensure data integrity under concurrent access. The business requires end-to-end encryption of sensitive data at rest and in transit, offline transaction delivery for mobile users, and a P99 latency under 200ms for transaction commit confirmation.

Constraints
  • End-to-end encryption of sensitive data at rest and in transit
  • Support for multi-row ACID transactions with strict consistency
  • Offline transaction delivery and sync for mobile clients
  • P99 transaction commit latency under 200ms
  • High throughput: ~100K transactions per second peak
  • Data integrity and auditability for regulatory compliance
📋
Guided Steps
1/5
Step 1
Choosing the Database Technology
Which database technology would you choose to support strict ACID transactions at scale and why?
💡 Hint
Consider the need for multi-row transactions, strong consistency, and complex queries.
Key Decision
Use PostgreSQL with distributed extensions like Citus for horizontal scaling while preserving ACID semantics.
Interviewer Expectation
Mid: Names PostgreSQL for ACID support. Senior: Explains why NoSQL like Cassandra fails due to lack of multi-row ACID and why NewSQL solutions like Citus enable scale with relational guarantees.
⚠️Common Wrong Answer
Choosing Cassandra or DynamoDB for transactions - they lack multi-row ACID, leading to data anomalies and integrity issues.
Step 2
Ensuring Low Latency Transaction Commits
How would you architect the system to achieve P99 commit latency under 200ms at 100K TPS?
💡 Hint
Think about transaction coordination, replication, and network overhead.
Key Decision
Use synchronous replication with PostgreSQL's built-in streaming replication combined with partitioning to localize transactions and reduce coordination overhead.
Interviewer Expectation
Mid: Mentions synchronous replication for durability. Senior: Details partitioning by customer_id to localize transactions, reducing cross-node coordination and latency.
⚠️Common Wrong Answer
Using asynchronous replication only - risks data loss and inconsistent reads violating transactional guarantees.
Step 3
Handling Offline Transaction Delivery and Sync
How would you design offline transaction delivery and reconciliation for mobile clients?
💡 Hint
Consider conflict detection, idempotency, and ordering guarantees.
Key Decision
Implement client-side transaction queue with unique IDs and vector clocks; server uses PostgreSQL to detect conflicts and apply transactions in order during sync.
Interviewer Expectation
Mid: Describes client queue and server reconciliation. Senior: Explains vector clocks for conflict detection and why eventual consistency models fail for financial data.
⚠️Common Wrong Answer
Allowing clients to blindly replay transactions without conflict detection - leads to double spending or inconsistent balances.
Step 4
Encrypting Data at Rest and In Transit
How do you ensure end-to-end encryption of sensitive data while maintaining queryability and performance?
💡 Hint
Balance encryption with the need for transactional queries and indexing.
Key Decision
Use PostgreSQL Transparent Data Encryption (TDE) for at-rest encryption and TLS 1.3 for in-transit; sensitive fields encrypted at application layer with deterministic encryption for indexed columns.
Interviewer Expectation
Mid: Mentions TLS and disk encryption. Senior: Explains deterministic encryption trade-offs for indexed fields and how it preserves queryability without leaking data patterns.
⚠️Common Wrong Answer
Encrypting all data blindly at application layer - breaks indexing and query performance, causing latency spikes.
Step 5
Designing APIs for Transactional Operations
Sketch 3 core API endpoints to support transaction submission, status querying, and offline sync reconciliation.
💡 Hint
Consider idempotency, request validation, and efficient status retrieval.
Key Decision
POST /transactions with idempotency key for submission; GET /transactions/{id}/status for status; POST /transactions/sync for batch offline reconciliation with conflict resolution.
Interviewer Expectation
Mid: Names endpoints and basic methods. Senior: Explains idempotency keys to prevent duplicates, efficient status caching, and batch reconciliation with conflict detection.
⚠️Common Wrong Answer
Using simple POST without idempotency - causes duplicate transactions under retries, violating financial correctness.
🔌
API Design Challenge
Sketch 3 core API endpoints for the transactional system that support transaction submission, status querying, and offline sync reconciliation.
Hints
  • How does the client submit a new transaction ensuring atomicity?
  • How does the client query transaction status with low latency?
  • How does the client reconcile offline transactions upon reconnect?
📊
Scoring Rubric
▼ Show
Must Have - 60%
  • Choosing a relational database with ACID support (PostgreSQL) over NoSQL for transactional integrity
  • Partitioning strategy to localize transactions and reduce latency
  • Use of synchronous replication for durability and consistency
  • Designing offline sync with conflict detection mechanisms
  • API endpoints supporting idempotency and offline reconciliation
Good to Have - 30%
  • Application-layer deterministic encryption for indexed sensitive data
  • Use of vector clocks or similar for conflict detection
  • Explanation of trade-offs between encryption and query performance
Bonus - 10%
  • Discussion of audit logging and regulatory compliance integration
  • Consideration of multi-region deployment impacts on latency and consistency
Anti-patterns to Avoid
Using NoSQL databases like Cassandra or DynamoDB for multi-row ACID transactions - leads to data anomalies and broken financial guarantees, risking incorrect balances and audit failures.
Relying on asynchronous replication only - risks data loss and inconsistent reads violating transactional guarantees, causing potential financial discrepancies.
Blindly encrypting all data at application layer without considering indexing - causes severe query performance degradation and latency spikes, impacting user experience.
Ignoring idempotency in transaction submission APIs - results in duplicate transactions under retries, violating financial correctness and causing double spending.
Using eventual consistency models for financial transactions - leads to incorrect balances and audit failures due to stale or conflicting data.
Polling-based offline sync without conflict detection - causes data corruption and user confusion due to conflicting transaction states.
HTTP polling at 50M DAU: 50 million reconnects per minute just to check for messages - collapses under load and wastes resources.
✅
Model Answer
▼ Reveal

The system uses PostgreSQL with Citus extension to achieve distributed ACID-compliant transactions at scale, partitioned by customer_id to localize transactions and reduce latency. Synchronous streaming replication ensures durability and low-latency commit confirmation. Offline transaction delivery is handled via client-side queues with unique IDs and vector clocks for conflict detection, reconciled on the server. Data is encrypted at rest using PostgreSQL TDE and in transit with TLS 1.3, with deterministic encryption applied at the application layer for indexed sensitive fields to balance security and query performance. The API design includes idempotency keys on transaction submission to prevent duplicates and batch reconciliation endpoints for offline sync with conflict resolution.

Key Decisions
  • Choosing PostgreSQL with Citus to maintain ACID guarantees while scaling horizontally
  • Partitioning data by customer_id to minimize cross-node transaction coordination and reduce latency
  • Using synchronous replication to ensure durability and consistency at commit time
  • Implementing client-side queues with vector clocks to detect and resolve conflicts during offline sync
  • Applying PostgreSQL Transparent Data Encryption for at-rest security and TLS 1.3 for in-transit encryption
  • Encrypting sensitive indexed fields deterministically at the application layer to preserve queryability
  • Designing APIs with idempotency keys on POST /transactions to avoid duplicate processing
  • Providing batch reconciliation endpoint POST /transactions/sync to handle offline transaction sync with conflict resolution
  • PostgreSQL with Citus extension for distributed ACID-compliant transactions at scale
  • Partitioning by customer_id to localize transactions and reduce cross-node coordination
  • Synchronous streaming replication for durability and low-latency commit confirmation
  • Client-side transaction queue with unique IDs and vector clocks for offline sync conflict detection
  • PostgreSQL Transparent Data Encryption (TDE) combined with TLS 1.3 for secure data at rest and in transit
  • Deterministic encryption at application layer for indexed sensitive fields to preserve queryability
  • API design with idempotency keys on POST /transactions to prevent duplicate processing
  • Batch reconciliation endpoint POST /transactions/sync supporting conflict resolution and ordering guarantees

Practice

(1/5)
1. A globally distributed key-value store must serve low-latency reads even during network partitions. Which architectural choice best supports this requirement?
easy
A. Use asynchronous replication with local reads allowed during partitions
B. Use synchronous replication across all data centers to ensure consistency
C. Use a single primary data center with read replicas in others
D. Use a quorum-based consensus protocol for all reads and writes

Solution

  1. Step 1: Identify PACELC trade-off

    During partitions (P), latency (L) matters; asynchronous replication allows local reads with low latency.

  2. Step 2: Evaluate options for latency under partition

    Synchronous replication (A) and quorum (D) increase latency; single primary (C) risks unavailability if primary is unreachable.

  3. Final Answer:

    Option A -> Option A

  4. Quick Check:

    Asynchronous replication -> low latency reads during partitions [check]
Hint: Async replication favors latency over consistency under partitions [check]
Common Mistakes:
2. Without the DAU Estimator component, what exactly breaks in the system's capacity planning process?
medium
A. Bandwidth estimation fails because DAU directly affects total data transfer calculations
B. The system cannot estimate peak QPS, causing under-provisioning of servers
C. Storage estimation becomes inaccurate because DAU is a key multiplier for daily data volume
D. User authentication fails because DAU tracks active sessions

Solution

  1. Step 1: Identify DAU Estimator role

    DAU Estimator provides daily active user count critical for bandwidth calculations as bandwidth depends on total active users.

  2. Step 2: Eliminate other options

    The system cannot estimate peak QPS, causing under-provisioning of servers is incorrect because peak QPS depends on instantaneous requests, not just DAU; Storage estimation becomes inaccurate because DAU is a key multiplier for daily data volume is partially true but storage depends more on total objects, not DAU alone; User authentication fails because DAU tracks active sessions is unrelated to authentication.

  3. Final Answer:

    Option A -> Option A

  4. Quick Check:

    Bandwidth depends directly on DAU for total data transfer [check]
Hint: Bandwidth depends directly on DAU count [check]
Common Mistakes:
3. You must implement a distributed lock for a system with high contention and strict latency requirements. Which approach is best and why?
medium
A. Use Redis SETNX with a fixed TTL to minimize latency
B. Use a consensus-based lock service like etcd to ensure correctness despite latency
C. Use a database row lock with retries to handle contention
D. Use a local in-memory lock combined with periodic heartbeat checks

Solution

  1. Step 1: Evaluate Redis SETNX under high contention

    Redis SETNX with TTL (A) is fast but can cause split-brain and stale locks under network partitions, violating correctness.

  2. Step 2: Consider consensus-based lock service

    Consensus-based services like etcd (B) provide strong consistency and fault tolerance, ensuring correctness even if latency is higher.

  3. Step 3: Assess database row locks and local locks

    Database locks (C) have high latency and risk deadlocks; local locks (D) do not coordinate across nodes, failing correctness.

  4. Final Answer:

    Option B -> Option B

  5. Quick Check:

    Consensus ensures correctness under contention despite latency [check]
Hint: Consensus beats speed for correctness under contention [check]
Common Mistakes:
4. At 10x scale, which component in a push-based newsfeed system is most likely to fail first and why?
hard
A. The fan-out service, because pushing to millions of followers causes write amplification bottleneck
B. The database, because it cannot handle 10x writes per second
C. The cache layer, because it runs out of memory storing all user feeds
D. The web servers, because they cannot handle 10x concurrent HTTP connections

Solution

  1. Step 1: Analyze database scaling

    Databases can be sharded and scaled horizontally; not usually the first bottleneck.

  2. Step 2: Analyze fan-out service scaling

    Push-based fan-out causes write amplification proportional to follower count; at 10x scale, this bottleneck grows superlinearly and saturates first.

  3. Step 3: Analyze cache and web servers

    Caches can be scaled with eviction and sharding; web servers scale with load balancers and stateless design.

  4. Final Answer:

    Option A -> Option A

  5. Quick Check:

    Fan-out write amplification causes first bottleneck at 10x scale [check]
Hint: Fan-out write amplification hits first at 10x scale [check]
Common Mistakes:
5. At 10x scale in user requests and live traffic updates, which component is most likely to fail first and why?
hard
A. The live traffic ingestion pipeline, as message queues saturate and delay update propagation
B. The Road Graph database, due to inability to handle increased write volume for live updates
C. The Routing Engine servers, because shortest path computations become CPU-bound and latency spikes
D. The client-side map rendering, due to increased data volume overwhelming browser memory

Solution

  1. Step 1: Analyze scaling bottlenecks

    Road Graph DB (A) can be sharded; ingestion pipeline (C) can scale horizontally; client rendering (D) is local and less critical.

  2. Step 2: Identify Routing Engine CPU bottleneck

    Routing Engine performs complex shortest path computations per request; 10x load causes CPU saturation and latency spikes, failing first.

  3. Final Answer:

    Option C -> Option C

  4. Quick Check:

    Routing Engine CPU-bound under 10x load causes first failure [check]
Hint: Routing Engine CPU saturates first under 10x load [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