How do you choose databases in system design?

Question

Accepted Answer

Choosing the right **database** is a key system-design decision — between **SQL (relational)** and **NoSQL** types (document, key-value, etc.), based on the data, access patterns, scale, and consistency needs. Different databases suit different requirements.

## SQL vs NoSQL (the main choice)

```text
SQL (relational: PostgreSQL, MySQL) → structured data, tables, relationships, ACID:
  ✓ strong consistency, complex queries/joins, transactions, well-defined schema
  ✓ for: relational data, transactions, complex queries (most traditional apps)
NOSQL → various non-relational models, flexible schema, often easier horizontal scaling:
  → DOCUMENT (MongoDB) → flexible JSON-like docs
  → KEY-VALUE (Redis, DynamoDB) → simple, fast lookups, caching
  → WIDE-COLUMN (Cassandra) → massive scale, high write throughput
  → GRAPH (Neo4j) → relationships/networks
  ✓ for: flexible schema, huge scale, specific access patterns, high throughput
```

## How to choose

```text
Consider:
  → DATA structure → relational/structured (SQL) vs flexible/varied (NoSQL document)
  → ACCESS PATTERNS → complex queries/joins (SQL) vs simple key lookups (key-value)
  → SCALE → massive horizontal scale / huge write volume (some NoSQL) vs moderate (SQL fine)
  → CONSISTENCY → strong consistency/transactions (SQL) vs eventual OK (some NoSQL)
  → choose the database that FITS the requirements (not just what's trendy)
```

## Polyglot persistence

```text
→ Use DIFFERENT databases for different needs in one system ("polyglot persistence"):
  e.g. PostgreSQL for core data + Redis for caching + Elasticsearch for search
→ no single database is best for everything → use the right tool for each job
```

## Why it matters

Understanding how to choose databases is foundational because **database choice is a key, consequential system-design decision** that significantly affects a system's capabilities, so it's essential knowledge.

The database is central to most systems, and choosing the right one — between **SQL (relational)** and the various **NoSQL** types — based on requirements is an important architectural decision.

Understanding the **SQL vs NoSQL** distinction is fundamental: SQL databases offer strong consistency, complex queries and joins, transactions, and structured schemas (suiting relational data and traditional applications), while NoSQL databases offer flexible schemas and often easier horizontal scaling, with different types (document, key-value, wide-column, graph) suiting different needs (flexible data, huge scale, high throughput, specific access patterns).

Understanding **how to choose** — based on data structure (relational vs flexible), access patterns (complex queries vs simple lookups), scale (massive horizontal scale vs moderate), and consistency needs (strong vs eventual) — is the key skill, with the important principle of choosing the database that **fits the requirements** rather than what's trendy.

Understanding **polyglot persistence** — using different databases for different needs in one system (e.g. PostgreSQL for core data, Redis for caching, Elasticsearch for search) — reflects the mature recognition that no single database is best for everything, so using the right tool for each job is often optimal.

Since the database is central to most systems and choosing the right one (SQL vs NoSQL types, based on data, access patterns, scale, and consistency) significantly affects the system's capabilities and is a key design decision, and since understanding how to choose and the polyglot-persistence approach is fundamental, understanding how to choose databases is essential, foundational system-design knowledge — a key, consequential decision in system design, requiring understanding of the database options and how to match them to requirements, and central to designing systems with appropriate data storage.