“SQL is consistent; NoSQL scales” is not a decision framework#
Relational and non-relational systems span many transaction, replication, consistency, and scaling designs. The useful question is which data model and operating trade-offs fit this workload.
| Pressure | Relational may fit | Non-relational model may fit |
|---|---|---|
| Relationships and evolving queries | Joins, constraints, flexible SQL | Explicit precomputed access paths |
| Correctness boundary | Multi-row transactions and constraints | Key/document-scoped operations may be sufficient |
| Dominant access | Several related query shapes | Predictable key, document, or partition-range access |
| Distribution | Can scale, with system-specific trade-offs | Some models make partitioning central |
| Shape | Structured relationships | Nested documents or wide sparse partitions |
A compact decision flow#
Need relational constraints or multi-row transactions?
→ Consider relational storage first
Mostly key lookup at very high scale?
→ Consider key-value or wide-column
Need bounded nested aggregates read together?
→ Consider a document model
Need full-text relevance?
→ Add a search index; do not force one primary store to do everythingMultiple stores can be reasonable, but every additional system adds synchronization, failure, and operational work. Start with the simplest store that satisfies the important invariants.
Four workloads, four defensible starting points#
- Payments: relational constraints, transactions, and auditability are often attractive.
- Product catalog: relational tables or documents can both fit; attribute variability and query requirements decide.
- Sessions: key-value lookup with expiry is natural.
- High-volume events: a wide-column or time-series-oriented model may fit known time/partition queries.
Related practice problems
Use the manifest-backed prompts below to apply this lesson in a complete design.
- PostgreSQL data definitionPostgreSQL Documentation
- DynamoDB data-modeling best practicesAWS Documentation
Reach this point to mark the lesson as read.