Katkat
Back

Choose DynamoDB vs MySQL for a backend

SalesforceSoftware Engineer · Technical Screen
MediumSoftware EngineerTechnical ScreenSoftware Engineering Fundamentals

Last updated

This question tests your ability to evaluate database technologies based on specific application requirements, trade-offs in consistency models, and operational scalability.

The Scenario

You are tasked with designing the backend data store for a new microservice at Salesforce. The service manages 'User Activity Logs' for enterprise accounts. The requirements are as follows:

  • The system must handle 50,000 writes per second during peak hours.
  • Queries primarily involve fetching a single user's last 50 activities or counting activities for a user within a specific timeframe.
  • Data volume is expected to grow to several petabytes over three years.
  • The service requires strong consistency for write operations to ensure logs are never lost.
  • Budget is a concern, but uptime and predictable latency are critical.

The Task

Compare and contrast using Amazon DynamoDB (a managed NoSQL key-value store) versus Amazon RDS MySQL (a relational database) for this specific use case.

  1. Explain how you would model the data in both systems.
  2. Discuss the trade-offs regarding scalability, consistency models, and maintenance overhead for this specific workload.
  3. Based on the requirements, provide a recommendation on which database system to choose and justify your decision.

Example Considerations

  • How would you handle the 'top N' query pattern in each system?
  • How does the locking mechanism in MySQL impact high-concurrency writes compared to DynamoDB's partitioned approach?
  • What happens to the database performance as the table size grows to 100TB+?

Hints

Hint 1

Consider the difference between horizontal scaling (sharding) in MySQL versus the native partitioned scaling of DynamoDB.

Hint 2

Think about how secondary indexes or global secondary indexes (GSIs) are maintained in DynamoDB compared to B-tree indexes in MySQL.

Hint 3

Analyze whether the need for strong consistency impacts the performance overhead or cost differently between these two platforms.

Follow-up questions

  • How would your choice change if the product team suddenly requested complex JOINs between User Activity Logs and a separate 'User Profile' table?
  • If you chose DynamoDB, how would you handle a sudden, unexpected spike in traffic that exceeds your provisioned throughput?
  • What are the implications of migration if the requirements evolve to include multi-table ACID transactions later?