The verdict
Default to a relational database like PostgreSQL — it gives you transactions, ad-hoc queries, and decades of operational maturity. Choose NoSQL only when a concrete requirement forces it: a document store for flexible, self-contained documents read by key; a wide-column or key-value store for very high write throughput and predictable single-key lookups at scale. The decision is per-workload, not per-company, and real systems often use both (polyglot persistence).
How to decide
- 1Start from access patterns and consistency needs, not from the shape of the data.
- 2Estimate scale honestly: reads/writes per second, total data size, and growth over 2–3 years.
- 3Do you need multi-row transactions, joins, or ad-hoc queries you can't predict? → relational.
- 4Is the data document-shaped, self-contained, and read mostly by a known key? → document.
- 5Do you need linear write scale and predictable single-key access above what one relational primary can serve? → wide-column / key-value.
- 6Default to relational until a requirement forces otherwise. Using more than one store for different workloads (polyglot persistence) is a sign of maturity, not indecision.
The options
Relational (Postgres / MySQL)
Tables, rows, and SQL with ACID transactions.
Best for
- Data with relationships you'll query in many ways
- Anything needing multi-row/multi-table transactions
- When you can't yet predict every access pattern
Strengths
- ACID transactions and strong consistency by default
- Ad-hoc queries and joins without pre-planning access paths
- Mature tooling, indexing, and operational knowledge
- Postgres scales vertically and read-horizontally far further than most teams assume
Weaknesses
- Horizontal write scaling requires sharding, which you build and operate
- Rigid schema (though migrations are routine)
- A single primary is a write bottleneck at extreme scale
Document (MongoDB / DynamoDB docs)
Self-contained JSON-like documents, usually read by key.
Best for
- Aggregates that are loaded and saved as a whole (a product, a user profile)
- Flexible or rapidly evolving schema
- Read-heavy workloads keyed by a known id
Strengths
- Flexible schema — no migration to add a field
- Fast reads/writes of a whole document by key
- Horizontal scale is a first-class feature
Weaknesses
- Cross-document consistency and joins are your problem, not the database's
- Easy to model yourself into duplication and update anomalies
- Ad-hoc analytical queries are weaker than SQL
Wide-Column / Key-Value (Cassandra / DynamoDB / Redis)
Massive, horizontally-scaled storage for known access patterns.
Best for
- Very high write throughput (event logs, time series, feeds)
- Predictable single-key or single-partition access
- Global scale with tunable consistency
Strengths
- Linear horizontal write scale
- Predictable low latency at huge volume
- Tunable consistency (Cassandra) or single-digit-ms managed (DynamoDB)
Weaknesses
- You must design the schema around queries up front — reshaping access patterns later is painful
- Usually eventual consistency; no rich transactions
- Hot partitions if the partition key is chosen poorly
Trade-offs at a glance
| Dimension | Relational (Postgres / MySQL) | Document (MongoDB / DynamoDB docs) | Wide-Column / Key-Value (Cassandra / DynamoDB / Redis) |
|---|---|---|---|
| Data model | Tables + relations | Documents (aggregates) | Rows keyed by partition |
| Query flexibility | High (SQL, joins, ad-hoc) | Medium (by key; limited joins) | Low (designed per query) |
| Transactions | Strong, multi-row ACID | Usually single-document | Limited / none |
| Horizontal write scale | Manual sharding | Built-in | Built-in, linear |
| Schema flexibility | Rigid + migrations | Flexible | Fixed per access pattern |
| Consistency default | Strong | Tunable / eventual | Eventual (tunable) |
| Best when | Relationships + unknown queries | Aggregates by key | Extreme write scale |
In the interview
In the datastore-selection moment of a system-design round. The interviewer wants to see you tie the choice to requirements, not reach for a database because it's trendy or familiar.
What a Staff answer includes
A Staff answer starts from access patterns, consistency, and scale — then names the specific failure mode of the option it rejects (e.g. 'a wide-column store would force me to fix every query up front, and this workload's access patterns aren't stable yet'). It's comfortable with polyglot persistence and can explain what consistency its NoSQL choice actually provides.
Follow-ups you should expect
- →How would you shard the relational database when a single primary can't keep up?
- →Your NoSQL choice is 'eventually consistent' — what does that mean for this feature, concretely?
- →How do you handle a transaction that must span two services with different databases?
- →What's your partition key, and how do you avoid hot partitions?
Common mistakes
- ✕'NoSQL because it scales' — with no stated access pattern or scale number to justify it.
- ✕Assuming relational databases can't scale; modern Postgres handles far more than most systems ever need.
- ✕Treating 'NoSQL' as one thing — document, key-value, wide-column, and graph stores solve different problems.
- ✕Forgetting that eventual consistency changes application logic (read-your-writes, conflict resolution).
Further reading
Related decisions
Strong vs Eventual Consistency
Strong consistency when correctness needs every reader to see the latest write (money, inventory, uniqueness); eventual when availability and scale matter more and brief staleness is tolerable (feeds, counts, caches).
Replication vs Sharding
Scale reads first with read replicas; split by feature (functional partitioning) when parts of the schema have different load; shard (horizontal partitioning) only when a single primary can't handle the writes or dataset. Exhaust caching and replicas before sharding.