System Design Questions for Database Systems

In any system design interview, the question of which database to use often emerges early and carries enormous weight. This isn't a superficial technology choice—it's a foundational architectural decision that influences every subsequent aspect of your design: data models, query patterns, consistency guarantees, scaling strategies, operational complexity, and ultimately, whether the system succeeds or fails under load.

The interviewer isn't looking for you to name-drop technologies. They want to see systematic reasoning—a structured approach that weighs requirements against capabilities, understands trade-offs, and arrives at a justified recommendation. This is what separates junior candidates who pick databases based on familiarity from senior engineers who select them based on principled analysis.

Effective database selection requires a structured methodology. The DARTS Framework provides a systematic approach that interviewers respect because it demonstrates both technical depth and engineering judgment:

• Data Model Analysis
• Access Pattern Evaluation
• Requirements Mapping
• Trade-off Assessment
• Selection Justification

Each phase of this framework builds upon the previous, creating a logical chain of reasoning that leads to a defensible database choice. Let's examine each phase in detail.

Phase 1: Data Model Analysis

Before selecting a database, you must deeply understand the shape and nature of your data:

• Entities and Relationships: What are the core entities? How are they related? Are relationships simple (1:N) or complex (M:N with attributes)?
• Schema Flexibility: Is the schema rigidly defined upfront, or will it evolve frequently? Do different records of the same 'type' have varying attributes?
• Data Size Estimates: How large is each record? How many records total? What's the expected growth rate?
• Temporal Characteristics: Is data time-series in nature? Does it have a natural time dimension for partitioning?
• Hierarchical/Graph Structures: Does data exhibit deep hierarchies or interconnected graph-like relationships?

Phase 2: Access Pattern Evaluation

The way your application accesses data is often more important than the data model itself. Different databases optimize for radically different access patterns:

• Query Types: Are queries simple lookups by primary key? Range scans? Complex aggregations? Full-text searches? Graph traversals?
• Read vs. Write Ratio: Is the workload read-heavy (100:1), balanced, or write-heavy?
• Latency Requirements: What's the acceptable p50/p95/p99 latency? Are there hard real-time constraints?
• Throughput Needs: How many operations per second at peak? What's the burst capacity requirement?
• Consistency Needs per Operation: Do some operations require strong consistency while others can tolerate eventual?

A principal-level understanding requires going beyond surface-level categorization into the architectural principles that give each database type its characteristics. Let's examine the major categories through this lens.

Real-world systems rarely use a single database. Polyglot persistence—using different databases for different use cases within the same system—is the norm at scale. Understanding when and how to combine databases demonstrates senior-level architectural thinking.

The Command Query Responsibility Segregation (CQRS) Pattern

CQRS separates read and write models, often backed by different databases. This acknowledges that read and write workloads have different optimization requirements:

┌─────────────┐     ┌──────────────────┐     ┌─────────────────┐
│   Writes    │────►│  Event Store /   │────►│  Read Database  │
│  (Commands) │     │  Write Database  │     │  (Denormalized) │
└─────────────┘     └──────────────────┘     └─────────────────┘
                                                      │
                                                      ▼
                                               ┌─────────────┐
                                               │    Reads    │
                                               │  (Queries)  │
                                               └─────────────┘

Write-Optimized Store (e.g., PostgreSQL, Event Store):

• Normalized for update efficiency
• ACID transactions for write consistency
• Captures intent as events/facts

Read-Optimized Store (e.g., Elasticsearch, Redis, MongoDB):

• Denormalized for query performance
• Multiple projections for different query patterns
• Eventually consistent with write store

When presenting database selection in an interview, structure your reasoning as a decision tree that progressively narrows options based on requirements.

Requirement Prioritization Matrix

Not all requirements are equal. Prioritize them and map to databases:

Start by identifying P0 requirements—these are non-negotiable filters. Then evaluate remaining candidates against P1 and P2 requirements to differentiate.

Beyond matching data models and access patterns, several operational and strategic factors influence database selection. Senior engineers consider these carefully.

Scaling Characteristics Comparison

Understanding how different databases scale is crucial for system design:

Let's apply the DARTS framework to a realistic interview scenario.

Step 1: Data Model Analysis

Core entities:

• Users: Profile information, credentials, preferences (fixed schema, ~5KB per user)
• Posts: Text + media references, timestamps, author (semi-structured, variable size)
• Follows: User A follows User B (relationship data, billions of edges)
• Feed: Materialized view of posts from followed users (denormalized for read performance)

Relationship complexity:

• User-Post: 1:N (simple)
• User-User (follows): M:N (high volume, traversal-heavy for feed generation)
• Post-Interactions: 1:N (likes, comments)

Step 2: Access Pattern Evaluation

• Post Creation: Write-heavy, needs durability, moderate latency tolerance
• Feed Retrieval: Read-heavy, latency-critical (p99 < 200ms), massive fanout
• Profile Lookup: Read-heavy, point lookups by user ID
• Follow Operations: Write moderate, needs consistency (prevent duplicate follows)
• Search: Full-text search on posts, real-time or near-real-time

Step 3: Requirements Mapping

Step 4: Trade-off Assessment

Given the requirements, we need:

1. High write throughput for posts → rules out single-node RDBMS
2. Graph-like access for follows → but graph DB won't scale to billions of edges with low latency
3. Pre-computed feeds for latency → requires denormalization and caching
4. ACID for critical operations → user accounts, financial (ads) transactions

Step 5: Selection Justification

I recommend a polyglot architecture:

• PostgreSQL for user accounts, authentication, and ad billing

  • ACID transactions for sensitive operations
  • Proven at scale with read replicas

• Cassandra for posts and feeds

  • Horizontal scaling for write throughput
  • Time-series-like access (recent posts)
  • Partition by user_id for feed retrieval

• Redis for hot data caching

  • Pre-computed feeds for active users
  • Session storage
  • Real-time counters (likes, views)

• Elasticsearch for search

  • Full-text indexing of posts
  • Eventually consistent is acceptable

This combination balances consistency, availability, and partition tolerance according to the specific needs of each data type.

Even experienced engineers make database selection mistakes. Awareness of common pitfalls helps you avoid them in interviews and production.

Database selection is both science and art. Let's consolidate the key takeaways:

What's Next:

With database selection mastered, the next page dives into Schema Design—how to structure your data within the chosen database to optimize for performance, maintainability, and evolution. You'll learn to design schemas that support both current needs and future growth.