# SQL vs NoSQL: How to Choose the Right Database

Source: https://www.techinterview.org/post/3233459967/sql-vs-nosql/
Updated: 2026-04-16 · techinterview.org

"Which database would you use?" is one of the most common follow-up questions in system design interviews. Many candidates default to "Postgres" or "MongoDB" without justification. The right answer depends on your data model, query patterns, consistency requirements, and scale — and showing that reasoning is what earns points.

## Strategy

Don't lead with a specific database. Lead with the question: *what does this data look like and how will it be queried?* The database follows from the answer. There are five broad categories: relational (SQL), document, column-family, key-value, and graph. Know one real example of each and when you'd pick it.

## Relational Databases (SQL)

Store data in tables with rows and columns. Relationships between entities are expressed via foreign keys and JOINs. ACID transactions guarantee correctness across multiple tables.

**Examples:** PostgreSQL, MySQL, SQLite, Oracle, SQL Server, Aurora.


```
-- Classic relational model
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INT REFERENCES users(id),
    total DECIMAL(10,2),
    status VARCHAR(50)
);

-- JOIN across tables
SELECT u.email, o.total
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE o.status = 'pending';
```


**When SQL wins:**

- Data has clear relationships that benefit from JOINs (users → orders → items).

- You need ACID transactions — financial systems, inventory, reservations (no overselling).

- Schema is known and stable — you won't be adding columns constantly.

- Complex queries with aggregations, filtering, sorting across multiple tables.

- Regulatory requirements for data integrity (banking, healthcare).

**Limitations:** Horizontal scaling is hard. Read replicas are easy; write scaling requires [sharding](https://www.techinterview.org/post/3233459955/database-sharding/), which adds significant complexity. Schema migrations on large tables are painful (adding a column to a 500M-row table can lock the table for hours without online DDL tools).

## Document Databases

Store data as JSON-like documents. Each document is self-contained — it holds all related data together rather than spreading it across tables.

**Examples:** MongoDB, CouchDB, Firestore, DynamoDB (also key-value).


```
// Document model — entire user profile in one document
{
  "_id": "user_123",
  "email": "alice@example.com",
  "profile": {
    "name": "Alice",
    "avatar": "https://...",
    "bio": "Engineer"
  },
  "settings": {
    "notifications": true,
    "theme": "dark"
  },
  "tags": ["premium", "early_adopter"]
}
```


**When document DBs win:**

- Data is naturally hierarchical or nested (a blog post with embedded comments).

- Schema evolves frequently — you add new fields to documents without a migration.

- You read and write a single entity at a time — no JOINs needed.

- Horizontal scaling is a first-class feature (MongoDB sharding, Firestore's global distribution).

- Catalog systems, CMS, user profiles, product details with variable attributes.

**Limitations:** No JOINs (you either embed or do application-level joins). Weaker consistency guarantees by default. No ACID transactions across multiple documents (though MongoDB added multi-document transactions in 4.0). Flexible schema means no enforcement — bad data can sneak in.

## Column-Family Databases

Store data in tables, but rows can have different columns, and columns are grouped into column families. Optimized for writing huge volumes of data and reading specific columns across many rows.

**Examples:** Apache Cassandra, HBase, Google Bigtable.


```
-- Cassandra table design
-- Primary key = (partition key, clustering key)
CREATE TABLE user_events (
    user_id UUID,
    event_time TIMESTAMP,
    event_type TEXT,
    payload TEXT,
    PRIMARY KEY (user_id, event_time)
) WITH CLUSTERING ORDER BY (event_time DESC);
```


Data for a given `user_id` is stored contiguously on disk, ordered by `event_time`. Fetching all events for a user is a single sequential disk read.

**When column-family DBs win:**

- Time-series data — IoT sensor readings, event logs, metrics, activity feeds.

- Write-heavy workloads at massive scale (Cassandra handles millions of writes/sec).

- You know your query patterns in advance and can design the primary key around them.

- Global distribution is required (Cassandra's multi-datacenter replication).

**Limitations:** You design the table around queries — no flexible querying after the fact. No JOINs. Schema changes are limited. Requires understanding of partition keys to avoid hotspots (see [consistent hashing](https://www.techinterview.org/post/3233459953/consistent-hashing/)). Data modeling requires expertise.

## Key-Value Stores

The simplest model: a dictionary. Set a key, get a value. No query language, no schema, no relationships.

**Examples:** Redis, Memcached, DynamoDB (also document), etcd, Riak.


```
SET user:123:session "eyJhbGciOiJIUzI1NiJ9..."  EX 3600
GET user:123:session

SET rate_limit:user:123 42  EX 60
INCR rate_limit:user:123
```


**When key-value stores win:**

- [Caching](https://www.techinterview.org/post/3233459956/caching-strategies/) — by far the most common use case (Redis in front of a primary DB).

- Session storage — look up a session by token, get the user object.

- Rate limiting — atomic increment counters per user per window.

- Leaderboards — Redis sorted sets keep a ranked list with O(log n) insert/query.

- Distributed locks — Redis `SET NX EX` implements a lightweight lock.

- Feature flags, configuration — fast lookup by key.

**Limitations:** No querying beyond the key. No relationships. Values are opaque blobs (though Redis supports rich types: strings, hashes, lists, sets, sorted sets, streams).

## Graph Databases

Nodes (entities) and edges (relationships) are first-class citizens. Optimized for traversing relationships — queries that in SQL would require many recursive JOINs.

**Examples:** Neo4j, Amazon Neptune, JanusGraph.


```
// Cypher query (Neo4j)
// Find friends of friends who like the same movies
MATCH (me:User {id: "123"})-[:FRIEND]->(friend)-[:FRIEND]->(fof)
WHERE NOT (me)-[:FRIEND]->(fof)
AND (fof)-[:LIKES]->(:Movie)<-[:LIKES]-(me)
RETURN fof.name
```


**When graph DBs win:**

- Social networks — friend recommendations, mutual connections.

- Fraud detection — finding suspicious transaction patterns across accounts.

- Knowledge graphs — semantic relationships between concepts.

- Recommendation engines — "people who bought X also bought Y."

- Network topology — mapping infrastructure dependencies.

**Limitations:** Niche use case. Most systems don't need graph traversal. Smaller ecosystem and community than SQL/document DBs. Scaling graph databases horizontally is hard — graphs are inherently difficult to partition.

## The Decision Framework


```
Is data relational with complex queries and transactions?
  → SQL (Postgres, MySQL)

Is data hierarchical/nested, schema flexible, horizontal scale needed?
  → Document (MongoDB, Firestore)

Is it time-series, event log, or write-heavy at extreme scale?
  → Column-family (Cassandra, HBase)

Is it a cache, session store, leaderboard, or rate limiter?
  → Key-value (Redis)

Are relationships between entities the primary query target?
  → Graph (Neo4j, Neptune)
```


## Real-World Examples

- **Uber:** Postgres for trip/payment data (ACID). Cassandra for location history (time-series, massive write volume). Redis for caching and surge pricing state.

- **Instagram:** Postgres for user/media metadata. Cassandra for activity feeds. Redis for session storage and feed caching.

- **LinkedIn:** MySQL for profiles and connections (small-medium). Espresso (document DB, internal) for scalable member data. Kafka for the activity feed. Voldemort (KV store) for recommendations.

- **Twitter/X:** MySQL for tweets (sharded). Redis for timeline caching. Manhattan (internal KV) for high-scale storage.

Most large systems use *multiple* databases, each serving the workload it's best at. In an interview, it's reasonable to say "Postgres for user accounts and transactions, Cassandra for the event log, Redis for the cache."

## NewSQL: The Middle Ground

NewSQL databases offer the scalability of NoSQL with the ACID guarantees of SQL:

- **Google Spanner / CockroachDB:** Globally distributed, strongly consistent, SQL interface. Used when you need ACID transactions at planetary scale. Expensive and complex.

- **PlanetScale (Vitess):** MySQL-compatible, horizontally sharded. Used by Slack, GitHub, Square.

Worth mentioning in interviews when the requirements are "global, strongly consistent, and SQL."

## Summary

SQL for structured data with relationships and transactions. Document for flexible, nested data at scale. Column-family for time-series and write-heavy workloads. Key-value for caching and simple lookups. Graph for relationship-traversal queries. Most production systems combine two or three of these. In a system design interview, state which database you'd use, why it fits the data model and query patterns, and acknowledge the trade-offs — especially around consistency ([CAP theorem](https://www.techinterview.org/post/3233459954/cap-theorem/)) and scale ([sharding](https://www.techinterview.org/post/3233459955/database-sharding/)).

## Related System Design Topics
- [Database Sharding](https://www.techinterview.org/post/3233459955/database-sharding/) — how you scale whichever database you choose beyond a single node.
- [CAP Theorem](https://www.techinterview.org/post/3233459954/cap-theorem/) — the theoretical basis for why NoSQL databases trade consistency for availability and scale.
- [Consistent Hashing](https://www.techinterview.org/post/3233459953/consistent-hashing/) — the algorithm NoSQL databases use internally to distribute data across nodes.
- [Caching Strategies](https://www.techinterview.org/post/3233459956/caching-strategies/) — Redis (key-value) in front of your primary DB is the most common multi-database pattern.

**See also:** [Design a Payment System](https://www.techinterview.org/post/3233463538/system-design-payment-system/) — the canonical example of why ACID transactions and relational databases are non-negotiable for financial data.