Bird
Raised Fist0

How to Choose a Database - Decision Framework for Interviews

HLDmediumInterview freq: common
Choose your preparation mode4 modes available
ArchitectureTraceChallenge
🎯
How to Choose a Database - Decision Framework for Interviews
45 min · senior
senior

You are designing a decision framework tool used by engineers at a 50M DAU startup to select the optimal database technology for various product features. The system must help engineers reason about trade-offs such as consistency, latency, scalability, and security under real-world constraints.

Constraints
  • Data encryption at rest and in transit is mandatory
  • P99 read and write latency must be under 200ms at peak load
  • Support offline data delivery and sync for mobile clients
  • System must handle 50M DAU with 10K QPS writes and 100K QPS reads
  • Data model complexity varies: from simple key-value to complex relational queries
📋
Guided Steps
1/5
Step 1
Define workload and data model characteristics
What key workload and data model parameters must the system capture to recommend a database?
💡 Hint
Consider read/write ratio, query complexity, data size, and consistency needs.
Key Decision
Use a structured schema with JSON Schema and Protobuf to capture workload parameters like read/write QPS, data size, query types, and consistency requirements for precise reasoning.
Interviewer Expectation
Mid: Lists workload parameters. Senior: Explains why capturing consistency and query complexity explicitly is critical for database choice.
⚠️Common Wrong Answer
Ignoring consistency and query complexity leads to poor recommendations that fail under real workloads.
Step 2
Select candidate database technologies to evaluate
Which real database technologies should the framework consider and why?
💡 Hint
Think about NoSQL, NewSQL, and traditional RDBMS with their strengths and weaknesses at scale.
Key Decision
Include Cassandra for high write throughput and linear scalability, PostgreSQL for complex relational queries, MongoDB for flexible schema, and DynamoDB for managed scalability and encryption compliance.
Interviewer Expectation
Mid: Names popular databases. Senior: Explains trade-offs like Cassandra’s LSM-tree for writes vs PostgreSQL’s ACID for complex joins.
⚠️Common Wrong Answer
Choosing only one database type ignores workload diversity and leads to suboptimal recommendations.
Step 3
Design the recommendation engine logic
How will the system map workload parameters and constraints to database recommendations?
💡 Hint
Consider rule-based scoring, constraint filtering, and trade-off analysis.
Key Decision
Implement a rule-based engine using Drools or a custom rules engine that scores candidates by matching workload profiles against database capabilities and constraints like latency and encryption.
Interviewer Expectation
Mid: Suggests simple filtering. Senior: Details scoring with weighted rules and fallback strategies for conflicting constraints.
⚠️Common Wrong Answer
Hardcoding recommendations without scoring leads to brittle and inaccurate results at scale.
Step 4
Ensure security and compliance in recommendations
How does the system enforce encryption and compliance constraints in database selection?
💡 Hint
Focus on encryption at rest/in transit and compliance certifications.
Key Decision
Integrate a compliance metadata store referencing databases’ encryption support and certifications (e.g., HIPAA, GDPR) to filter out non-compliant options automatically.
Interviewer Expectation
Mid: Mentions encryption. Senior: Explains metadata-driven filtering and impact on recommendation accuracy.
⚠️Common Wrong Answer
Ignoring compliance risks recommending insecure databases, causing legal and operational failures.
Step 5
Design APIs for client interaction
What are the 3 core API endpoints for submitting workloads, querying recommendations, and updating constraints?
💡 Hint
Think REST or gRPC endpoints with clear request/response models.
Key Decision
POST /workloads to submit workload profiles, GET /recommendations/{workload_id} to fetch database suggestions, PATCH /workloads/{workload_id}/constraints to update constraints dynamically.
Interviewer Expectation
Mid: Defines basic endpoints. Senior: Explains idempotency, versioning, and validation for robust client interaction.
⚠️Common Wrong Answer
Using only synchronous blocking calls or missing update endpoints limits usability and scalability.
🔌
API Design Challenge
Sketch 3 core API endpoints for the decision framework service that engineers will use to evaluate and select databases.
Hints
  • How does the client submit a new database use case or workload profile?
  • How does the client query recommendations or decision results?
  • How does the client provide feedback or update constraints to refine recommendations?
📊
Scoring Rubric
▼ Show
Must Have - 60%
  • Captures workload parameters including consistency and query complexity
  • Considers multiple real database technologies with explicit trade-offs
  • Implements a rule-based scoring engine for recommendations
  • Enforces encryption and compliance constraints in selection
  • Defines clear API endpoints for workload submission, querying, and updates
Good to Have - 30%
  • Uses Protobuf for schema validation and efficient serialization
  • Includes metadata store for compliance and encryption capabilities
  • Supports offline delivery and sync for mobile clients
  • Implements API versioning and idempotency for robustness
Bonus - 10%
  • Integrates feedback loop to improve recommendations over time
  • Provides UI or CLI tooling for interactive decision making
Anti-patterns to Avoid
Ignoring consistency and query complexity leads to poor database matches and system failures, causing incorrect database choices that degrade performance and reliability.
Choosing a single database technology ignores workload diversity and causes scalability bottlenecks, resulting in system failures under varied use cases.
Hardcoding recommendations without scoring or filtering results in brittle and inaccurate advice, which cannot adapt to changing workloads or constraints.
Neglecting encryption and compliance risks legal penalties and data breaches, exposing the company to regulatory fines and loss of user trust.
Designing APIs without update or feedback endpoints limits system flexibility and client usability, preventing iterative improvement and dynamic constraint handling.
Using synchronous blocking calls for all APIs causes poor scalability and client experience, leading to high latency and system resource exhaustion at scale.
✅
Model Answer
▼ Reveal

A decision framework capturing detailed workload parameters and constraints, evaluating multiple real databases via a rule-based engine, enforcing encryption and compliance, and exposing RESTful APIs for client interaction.

Key Decisions
  • Use JSON Schema and Protobuf to ensure precise and efficient workload profile serialization and validation
  • Select Cassandra for workloads requiring high write throughput and linear scalability due to its LSM-tree storage and partitioning
  • Choose PostgreSQL for workloads needing complex ACID transactions and relational joins
  • Leverage DynamoDB for managed scalability, encryption compliance, and low-latency key-value access
  • Implement a Drools-based rule engine to score candidate databases against workload parameters and constraints, enabling weighted trade-off analysis
  • Maintain a compliance metadata store to automatically filter out databases lacking required encryption or certifications
  • Design RESTful APIs with idempotency and versioning to support robust client interactions and dynamic constraint updates
  • Use TLS and encrypted storage to meet security requirements
  • Enable offline delivery by allowing clients to cache recommendations and sync constraints asynchronously
  • Use JSON Schema and Protobuf to capture detailed workload and data model parameters for precise reasoning
  • Include Cassandra for write-heavy, scalable workloads due to its LSM-tree storage and partitioning
  • Use PostgreSQL for complex relational queries requiring ACID transactions and joins
  • Leverage DynamoDB for managed scalability, encryption compliance, and low-latency key-value access
  • Implement a rule-based recommendation engine with Drools to score and filter databases based on constraints and workload
  • Maintain a compliance metadata store to enforce encryption and certification requirements automatically
  • Design RESTful APIs: POST /workloads, GET /recommendations/{id}, PATCH /workloads/{id}/constraints for flexible client interaction
  • Use TLS for all API communication to ensure encryption in transit
  • Store sensitive data encrypted at rest using AWS KMS or HashiCorp Vault integration
  • Support offline delivery by allowing clients to cache recommendations and sync constraints asynchronously

Practice

(1/5)
1. In a PACELC-based system, at step 4 the Write Coordinator checks the network partition status before committing a write. What happens if the network is partitioned but latency is prioritized?
easy
A. The Write Coordinator rejects the write to maintain consistency
B. The Write Coordinator waits for all replicas to acknowledge before committing
C. The Write Coordinator commits locally and replicates asynchronously later
D. The Write Coordinator redirects the write to a different data center

Solution

  1. Step 1: Trace Write Coordinator behavior at Step 4

    When partitioned and latency prioritized, writes commit locally without waiting for replicas.

  2. Step 2: Understand asynchronous replication impact

    Local commit allows low latency; replication happens later, risking temporary inconsistency.

  3. Final Answer:

    Option C -> Option C

  4. Quick Check:

    Local commit + async replication -> low latency under partition [check]
Hint: Local commit first, replicate later under partition and latency [check]
Common Mistakes:
2. A distributed messaging system must handle 1 million writes per second and guarantee message durability despite node failures. Which component best handles this requirement?
easy
A. Use a distributed log system like Apache Kafka with replication and partitioning
B. Use an in-memory cache like Redis without persistence to speed up writes
C. Use a relational database with synchronous writes for durability
D. Use a simple message queue with no replication to reduce latency

Solution

  1. Step 1: Identify durability and high write throughput needs

    Redis without persistence (A) cannot guarantee durability on failure. Relational DB (C) cannot handle 1M writes/sec synchronously without bottlenecks. Simple queue without replication (D) risks message loss.

  2. Step 2: Recognize Kafka's replication and partitioning

    Kafka provides durable, replicated logs with partitioning for high throughput and fault tolerance.

  3. Final Answer:

    Option A -> Option A

  4. Quick Check:

    Kafka replication + partitioning -> durability and scale [check]
Hint: Kafka replicates logs for durability and scales with partitions [check]
Common Mistakes:
3. A company needs to store user data with the highest read/write speed for active sessions, but cost must be balanced for less frequently accessed data. Which storage tier is best suited for the active session data?
easy
A. Cold storage using object storage with infrequent access pricing
B. Hot storage using in-memory databases like Redis or Memcached
C. Archive storage using tape or deep cold storage
D. Warm storage using HDD-based block storage with moderate latency

Solution

  1. Step 1: Identify storage tier characteristics

    Hot storage provides the lowest latency and highest throughput, suitable for active session data requiring fast reads/writes.

  2. Step 2: Eliminate other tiers based on latency and cost

    Cold and archive tiers have high latency and are optimized for cost, not speed. Warm storage has moderate latency but is slower than hot storage.

  3. Final Answer:

    Option B -> Option B

  4. Quick Check:

    Hot storage = fastest access, ideal for active sessions [check]
Hint: Hot tier = fastest access for active data [check]
Common Mistakes:
4. For a large-scale code repository system supporting millions of users, which branching strategy best balances performance and user flexibility?
medium
A. Use a centralized locking mechanism to serialize all branch creation and merges
B. Use lightweight branch pointers referencing immutable commits with eventual consistency
C. Use full branch copies stored separately for each user to avoid conflicts
D. Use synchronous replication of branch metadata across all servers before allowing merges

Solution

  1. Step 1: Analyze branching strategies for scale

    Lightweight branch pointers referencing immutable commits allow fast branch creation and merges without locking, supporting high concurrency.

  2. Step 2: Evaluate other options

    Centralized locking serializes operations, hurting performance; full branch copies waste storage and complicate merges; synchronous replication adds latency and reduces availability.

  3. Final Answer:

    Option B -> Option B

  4. Quick Check:

    Immutable commits + lightweight pointers -> scalable branching [check]
Hint: Immutable commits enable lightweight, scalable branches [check]
Common Mistakes:
5. At 10x traffic scale, which component in a Layer 7 load balancing architecture is most likely to become the bottleneck first, and why?
hard
A. Layer 7 load balancer CPU, due to expensive HTTP header parsing and SSL termination
B. Network switches, because increased packet forwarding saturates bandwidth
C. Backend servers, because they cannot handle increased HTTP request volume
D. DNS servers, because client-side resolution delays increase exponentially

Solution

  1. Step 1: Identify Layer 7 load balancer workload at scale

    Layer 7 load balancers perform CPU-intensive tasks like HTTP header parsing and SSL termination, which scale poorly with traffic.

  2. Step 2: Compare with other components

    Backend servers can be scaled horizontally; network switches handle packet forwarding efficiently; DNS servers are less impacted by traffic volume.

  3. Final Answer:

    Option A -> Option A

  4. Quick Check:

    Layer 7 LB CPU bottleneck due to HTTP parsing and SSL termination [check]
Hint: Layer 7 LB CPU chokes first at high traffic 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