01 · SQL vs NoSQL — Choose the Right Data Model¶
Foundations · Topic 1 of 4
Why This Matters¶
Choosing the wrong data model is the most expensive early architectural decision. SQL and NoSQL are not competitors — they are tools optimized for different access patterns.
Core Concepts¶
Relational (SQL)¶
- Data stored in tables with fixed schema
- Relationships via foreign keys and joins
- Strong ACID guarantees
- Query via SQL (declarative)
Non-Relational (NoSQL)¶
- Schema-flexible: documents, key-value, wide-column, graph
- Optimized for horizontal scale and specific access patterns
- Transaction and consistency guarantees depend on the operation, scope, and configuration; MongoDB and DynamoDB both offer multi-item transactions
- SQL is a query language, not a scaling architecture: distributed SQL systems also shard data
Data Model Types¶
| Type | Example DBs | Best For |
|---|---|---|
| Relational | PostgreSQL, Spanner | Structured data, complex queries, joins |
| Document | MongoDB | Semi-structured, nested objects |
| Key-Value | DynamoDB, Redis | Simple lookups, caching, sessions |
| Wide-Column | Cassandra, Bigtable | Write-heavy, time-series, IoT |
| Analytical | BigQuery | OLAP, large scans, aggregations |
| Graph | Neo4j | Relationship-heavy traversals |
Decision Framework¶
graph TD
A[Define invariants and transaction scope] --> B[Compare guarantees and operational cost]
B --> C[What's your dominant access pattern?]
C -->|Simple key lookups| D[Key-Value: DynamoDB / Redis]
C -->|Flexible nested docs| E[Document: MongoDB]
C -->|High write throughput| F[Wide-Column: Cassandra]
C -->|Large analytical scans| G[BigQuery]
Cloud Implementations¶
- Self-managed or Cloud SQL (GCP), RDS (AWS), Azure Database
- Full SQL, ACID, extensions (PostGIS, pg_trgm)
- Best for: transactional workloads with complex queries
- Globally distributed relational database
- SQL + strong external consistency at planetary scale
- Uses TrueTime API for global ordering
- Best for: global applications needing SQL + horizontal scale
- AWS managed key-value + document store
- Single-digit millisecond latency at any scale
- Partition key determines data distribution
- Best for: high-throughput, predictable access patterns
- Document model with flexible BSON schema
- Atlas cloud offering (multi-cloud)
- Best for: catalogs, content, user profiles
- Serverless columnar analytical engine
- Columnar execution, partition pruning and clustering; specialized search and vector indexes are available
- Best for: analytics, reporting, ML features
- Wide-column, masterless, peer-to-peer
- Write path: commit log → memtable → SSTable
- Best for: write-heavy IoT, time-series, event logs
Key Tradeoffs¶
| Dimension | SQL | NoSQL |
|---|---|---|
| Schema | Explicit, evolvable; JSON support is common | Flexible representation; validation still matters |
| Joins | Native relational joins | Product-specific; embedding or application joins are common |
| Transactions | Common; scope and isolation vary | Available in many systems; scope and cost vary |
| Scale | Vertical, replicas, or distributed SQL/sharding | Often partition-oriented; hot keys still limit scale |
| Query flexibility | Usually broad ad hoc querying | Often optimized around predefined access patterns |
Common Pitfalls¶
Don't pick NoSQL just for scale
Many teams adopt NoSQL prematurely. PostgreSQL handles millions of rows and thousands of QPS with proper indexing.
Don't embed everything in a document
MongoDB's document model tempts over-embedding. Deep nesting makes updates and queries painful.
Work Backwards from a Workload¶
For an order service, list the invariants first: one payment per idempotency key, no negative inventory, and an order linked to an existing customer. Then list queries: lookup by order ID, latest orders per customer, and daily revenue. The first two favor selective transactional access; revenue may belong in a separate analytical projection fed by CDC. A database category does not answer all three requirements by itself.
Check Your Understanding¶
See the worked foundations lab and verified implementation references.