Choosing the right database for your system design - SQL vs NoSQL and when to use each.
Finished this lesson?
Sign in to save your progress across devices.
Tip: use ← and → to move between lessons.
Was this lesson helpful?
Rehearse this with a Staff+ engineer
45-minute mock with a Staff+ engineer who interviews at your target company. Premium members save $20.
Databases are the foundation of almost every system you'll design. Understanding when to use different types of databases and their trade-offs is essential knowledge for any system design interview.
The choice between SQL and NoSQL databases is one of the most fundamental decisions in system design.
Relational databases like PostgreSQL, MySQL, and SQL Server excel at:
Non-relational databases like MongoDB, Cassandra, and DynamoDB excel at:
In interviews, you'll often use both types in the same system. User profiles might go in SQL while activity feeds go in NoSQL.
Users table:
- user_id (primary key)
- email
- name
Orders table:
- order_id (primary key)
- user_id (foreign key)
- total_amount
- status
Order_Items table:
- item_id (primary key)
- order_id (foreign key)
- product_id (foreign key)
- quantity
{
"feed_id": "user_123_feed",
"posts": [
{
"post_id": "p_456",
"author": "user_789",
"content": "Hello world!",
"timestamp": 1642345678,
"likes": 42
}
]
}
Examples: Redis, DynamoDB, Memcached
Best for:
Examples: MongoDB, CouchDB
Best for:
Examples: Cassandra, HBase
Best for:
Examples: Neo4j, Amazon Neptune
Best for:
Create copies of your database for:
Split your data across multiple databases:
Sharding adds significant complexity. Only shard when you actually need it, and choose your shard key carefully - it's hard to change later.
User profiles, authentication, and account settings typically go in SQL for ACID guarantees.
Feeds, logs, and analytics typically go in NoSQL for scale.
Frequently accessed data gets cached in Redis for sub-millisecond latency.
| SQL vs NoSQL Trade-offs | |
|---|---|
| Name | Description |
| Schema | SQL: Rigid, defined upfront. NoSQL: Flexible, can evolve. |
| Scaling | SQL: Primarily vertical. NoSQL: Built for horizontal. |
| Transactions | SQL: Full ACID guarantees. NoSQL: Usually eventual consistency. |
| Query complexity | SQL: Complex JOINs, aggregations. NoSQL: Simple key-based lookups. |
| Best for | SQL: Relationships, consistency. NoSQL: Scale, flexibility. |
Now that you understand databases, let's look at how caching can dramatically improve your system's performance and reduce database load.