Skip to content

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

#

Question 1

Does flexible JSON mean there is no schema?

#

Question 2

Should every service immediately choose a different database?

See the worked foundations lab and verified implementation references.