Design Distributed Database

Problem and Scope

Design a distributed database system that can scale beyond the capacity of a single machine. You have a SQL database with a single table that is growing beyond what one machine can handle, and you need to design a solution to distribute this data across multiple machines.

Start by confirming the core user journey, exclusions, success criteria, and the constraints that materially affect the architecture.

Requirements to Clarify

A strong answer should establish scope before choosing components.

Functional requirements:

• Support storing and querying data that exceeds the capacity of a single machine
• Allow data access and retrieval from any part of the distributed system
• Maintain the ability to perform both transactional operations and reporting queries
• Support both single-record access and aggregated queries across the entire dataset

Non-functional requirements:

• Minimize query latency, especially for frequently accessed data
• Balance load across machines to avoid any single point of bottleneck
• Maintain reasonable performance for both transaction processing and reporting queries
• Ensure scalability as data volume continues to grow

Scale and Capacity

Use the workload to justify storage, partitioning, caching, and reliability decisions. Clarify or challenge these assumptions rather than treating them as unquestionable facts:

• The database currently has more rows than can fit on a single machine
• The system should be able to scale horizontally by adding more machines
• No specific guidance on exact data size, but assume the scale is large enough to require distribution
• Assume both transactional and analytical workloads need to be supported

Architecture Discussion

Walk through the important read and write paths, identify ownership boundaries, and explain how the design behaves when dependencies fail. Cover these areas explicitly:

• Partitioning/sharding strategy for distributing data
• Load balancing mechanism to direct queries to appropriate machines
• Query routing and optimization for distributed queries
• Potential read/write separation for different workloads
• Consistency management across distributed data
• Determining an appropriate partitioning strategy
• Handling queries that span multiple partitions
• Managing load distribution across nodes
• Balancing competing requirements (e.g., transactional vs. reporting workloads)
• Maintaining reasonable performance as the system scales

Follow-up Questions

Expect the interviewer to test the consequences of your choices. Practise answering these questions with a concrete decision, its benefit, and its cost:

• What factors would guide your choice of partitioning strategy?
  • Focus: Assess the candidate's answer to "What factors would guide your choice of partitioning strategy" for explicit assumptions, a workable mechanism, failure behavior, and consequential trade-offs.
• How would your system handle queries that need data from multiple partitions?
  • Focus: Assess the candidate's answer to "How would your system handle queries that need data from multiple partitions" for explicit assumptions, a workable mechanism, failure behavior, and consequential trade-offs.
• What trade-offs exist between different approaches to load balancing?
  • Focus: Assess the candidate's answer to "What trade-offs exist between different approaches to load balancing" for explicit assumptions, a workable mechanism, failure behavior, and consequential trade-offs.
• How would your design change if the workload was heavily skewed toward a particular type of data?
  • Focus: Assess the candidate's answer to "How would your design change if the workload was heavily skewed toward a particular type of data" for explicit assumptions, a workable mechanism, failure behavior, and consequential trade-offs.
• What happens when you need to add additional machines to your system?
  • Focus: Assess the candidate's answer to "What happens when you need to add additional machines to your system" for explicit assumptions, a workable mechanism, failure behavior, and consequential trade-offs.
• How would your system handle the failure of one of the database nodes?
  • Focus: Assess the candidate's answer to "How would your system handle the failure of one of the database nodes" for explicit assumptions, a workable mechanism, failure behavior, and consequential trade-offs.

Evaluation Rubric

MockMe evaluates the answer across the following dimensions. A complete answer should connect claims to requirements and explain consequential trade-offs.

• Requirements and scope (15%): Clarifies and prioritizes the required behavior for Support storing and querying data that exceeds the capacity of a single machine; Allow data access and retrieval from any part of the distributed system; Maintain the ability to perform both transactional operations and reporting queries. Establishes the constraints that materially affect Design Distributed Database, including Minimize query latency, especially for frequently accessed data; Balance load across machines to avoid any single point of bottleneck. Strong evidence includes Separates the critical path from secondary features and resolves ambiguous requirements before choosing components.
• Architecture and interfaces (20%): Presents coherent ownership boundaries and end-to-end flows covering Partitioning/sharding strategy for distributing data; Load balancing mechanism to direct queries to appropriate machines; Query routing and optimization for distributed queries; Potential read/write separation for different workloads. Strong evidence includes Defines interfaces and traces important success, retry, and failure paths across the proposed components.
• Data and scaling (25%): Uses workload assumptions such as The database currently has more rows than can fit on a single machine; The system should be able to scale horizontally by adding more machines to justify capacity and partitioning decisions. Explains the data, state, or model strategy for Partitioning/sharding strategy for distributing data; Consistency management across distributed data; Determining an appropriate partitioning strategy. Strong evidence includes Quantifies a dominant workload, identifies the first bottleneck, and explains how the design evolves as that workload grows.
• Reliability, correctness, and safety (20%): Explains concrete failure behavior, recovery, and operational safeguards for Consistency management across distributed data. Strong evidence includes States the required correctness or consistency boundary and covers retries, partial failure, observability, and safe degradation.
• Communication and trade-offs (20%): Drives a structured discussion and compares consequential alternatives for Managing load distribution across nodes; Balancing competing requirements (e.g., transactional vs. reporting workloads); Maintaining reasonable performance as the system scales. Strong evidence includes Makes assumptions explicit, answers the question asked, and explains both the benefit and cost of major decisions.