SQL vs NoSQL¶

Prerequisites: Database Sharding, Consistent Hashing

← Consistent Hashing | Next: Indexing & Storage →

Why This Exists¶

"SQL vs NoSQL" is a bad framing. It suggests a single axis with relational databases on one end and everything else lumped together on the other. In practice there are at least six distinct data models, each optimized for a different access pattern, and the question a Staff engineer actually answers is not "which side am I on" but "what does this workload need to do fast, and what can it afford to get eventually."

The stub version of this debate — "SQL is old and rigid, NoSQL is new and scalable" — is wrong on both counts. Modern relational databases shard, replicate, and scale writes into the millions per second (see Sharding). Modern NoSQL databases support secondary indexes, transactions, and joins. The real differences are in the data model's native shape and what it optimizes away.

Mental Model¶

Think of each database family as answering a different question about how you'll touch the data:

Relational   → "How do these entities relate to each other?"
Document     → "What does one aggregate look like as a whole?"
Key-Value    → "Give me the value for this exact key, fast."
Wide-Column  → "Give me a time-ordered slice of this partition."
Graph        → "How are these nodes connected, and how deep?"
Time-Series  → "How has this metric moved, and what's the trend/aggregate over a window?"

The model you pick shapes how painful every future query, migration, and scale-out will be. Picking wrong doesn't fail immediately — it fails two years later when a "just add a join" ticket turns into a rewrite.

Abstraction Levels¶

The Six Families¶

1. Relational (SQL) — Postgres, MySQL, Aurora¶

Optimized for: multi-entity consistency and ad-hoc queries across relationships.

• Schema: fixed, enforced at write time
• Joins: first-class, query planner optimizes them
• Transactions: full ACID, multi-row, multi-table
• Example use case: an order management system where an order references a customer, a shipping address, line items, and payment records, and you need "all unpaid orders over $500 placed by customers in California" as a single query.

SELECT o.id, o.total, c.name
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'unpaid' AND o.total > 500 AND c.state = 'CA';

2. Document — MongoDB, DynamoDB (document mode), Firestore¶

Optimized for: reading and writing one self-contained aggregate as a unit.

• Schema: flexible per-document, versioned in application code
• Joins: discouraged; you embed instead of join
• Transactions: strong within a document, weaker across documents
• Example use case: a product catalog where each product has a variable set of attributes (a shirt has size/color, a laptop has RAM/storage) and you almost always fetch one product at a time.

{
  "_id": "sku_9182",
  "name": "Trail Runner Jacket",
  "attributes": { "size": ["S", "M", "L"], "color": "orange", "waterproof": true },
  "price_cents": 12900
}

3. Key-Value — Redis, Memcached, DynamoDB (KV mode), Riak¶

Optimized for: O(1) lookup by a single exact key, nothing else.

• Schema: none — the value is an opaque blob
• Joins: not supported
• Transactions: single-key atomic operations only
• Example use case: session storage (session:abc123 → user_id, expiry), a feature-flag cache, a rate-limit counter.

4. Wide-Column — Cassandra, Bigtable, HBase, ScyllaDB¶

Optimized for: high-throughput writes and range scans within a partition, across a massive number of partitions.

• Schema: column families defined loosely; rows in a partition can have different columns
• Joins: not supported — you design the table around the query, not the entity
• Transactions: per-partition (lightweight transactions); no cross-partition ACID
• Example use case: time-series telemetry — partition_key = device_id, clustering_key = timestamp — writing millions of sensor readings per second and reading "the last 24 hours for device X" as a contiguous scan.

Partition: device_id=42
  2026-08-13T00:00:00 → {temp: 21.3, humidity: 40}
  2026-08-13T00:00:05 → {temp: 21.4, humidity: 40}
  2026-08-13T00:00:10 → {temp: 21.4, humidity: 41}

5. Graph — Neo4j, Amazon Neptune, JanusGraph¶

Optimized for: traversing relationships of arbitrary, unknown depth.

• Schema: nodes and edges, both with properties
• Joins: replaced by graph traversal — no join-explosion as depth increases
• Transactions: ACID, but the win is traversal speed, not consistency
• Example use case: "friends of friends who are not already friends" for a social network, or fraud rings — "accounts that share a device fingerprint within 2 hops of a known bad actor." A relational equivalent needs a self-join per hop and gets slower with every hop; a graph traversal stays roughly constant per hop.

6. Time-Series — InfluxDB, TimescaleDB, OpenTSDB, Prometheus¶

Optimized for: extremely high-rate appends of timestamped points, and range/aggregate queries over a time window.

• Schema: a metric name + tags/labels + timestamp + value; the shape is fixed but the label set per metric is not
• Joins: not supported in the relational sense — you correlate series by shared tags, not foreign keys
• Transactions: varies by product, not a family-wide property — purpose-built time-series engines (InfluxDB, OpenTSDB, Prometheus) treat points as append-only and immutable once written, where "correctness" means never losing or misordering a point within a series rather than multi-row atomicity. TimescaleDB is the explicit exception: it's a PostgreSQL extension, not a separate engine, so it inherits full ACID transactions, joins, and the entire relational feature set — you get time-series-optimized storage (hypertables, automatic partitioning by time) with none of the transactional trade-offs the rest of this family makes. That's the concrete reason "time-series = no transactions" isn't a safe generalization to make in an interview.
• Example use case: IoT sensor telemetry or infrastructure metrics — cpu_usage{host="web-1", region="us-east"} written every 10 seconds — and a dashboard query like "p99 latency for this service, 5-minute buckets, last 24 hours."

metric: cpu_usage  tags: {host: web-1, region: us-east}
2026-08-13T00:00:00Z → 21.3
2026-08-13T00:00:10Z → 21.4
2026-08-13T00:00:20Z → 21.4

Time-series stores are easy to mistake for wide-column stores (Cassandra's partition_key=device_id, clustering_key=timestamp pattern from §4 looks almost identical), and the two do share a storage shape — sequential, time-ordered writes within a partition. The real difference is that a dedicated time-series engine bakes in the operations you'd otherwise build by hand on top of wide-column: automatic downsampling/rollups, retention-by-age, and delta/Gorilla-style compression tuned specifically for slowly-changing numeric values (see Metrics & Monitoring for the full ingestion/storage/cardinality design). Reach for a purpose-built time-series database once you need those built in; Cassandra-style wide-column is still the right call for time-ordered data that isn't purely numeric-metric shaped (event logs, telemetry blobs).

Architecture: Where Each Fits in a Request Path¶

graph TD
    Client[Client Request] --> API[API Layer]
    API --> KV[(Key-Value: session, rate limit)]
    API --> Doc[(Document: product catalog)]
    API --> SQL[(Relational: orders, payments)]
    API --> WC[(Wide-Column: event stream, telemetry)]
    API --> Graph[(Graph: recommendations, fraud graph)]
    API --> TS[(Time-Series: metrics, IoT sensor data)]
    WC --> Warehouse[(Analytics Warehouse)]
    SQL --> Warehouse
    TS --> Dashboards[Monitoring Dashboards / Alerting]

Trade-off Axes¶

These are the axes that actually matter — not "SQL vs NoSQL" but where each family lands on each axis.

How It Works: The Underlying Trade¶

Every one of these systems is trading the same currency: how much work happens at write time vs. read time, and how much the schema constrains you vs. the query engine does.

• Relational pushes structure to write time (schema, constraints, indexes) so reads can be arbitrary and still fast — the query planner does the work.
• Wide-column pushes structure to write time even harder — you design the table for the one query pattern you'll run, so writes are cheap and reads are a sequential scan.
• Document defers structure to the application — flexible writes, but "what does this field mean" lives in code, not the database.
• Key-value defers almost everything — the fastest possible read, the least the database can help you with.
• Graph inverts the relational trade-off: instead of computing joins at query time across normalized tables, it stores the relationship as a first-class, traversable edge, so multi-hop reads don't degrade with depth the way SQL self-joins do.

Worked Example: Choosing for a Social Feed¶

A social app needs: user profiles, posts, a "who follows whom" graph, a materialized feed, and session auth.

User profiles         → Relational or Document (structured, low write volume, needs strong consistency for username uniqueness)
Follow graph           → Graph DB or adjacency-list table (traversal: "who does X follow", "mutual follows")
Post content            → Document (variable attachments, embeds, per-post schema drift over time)
Feed generation (fan-out)→ Wide-Column (partition per user, clustering by time — exactly the Cassandra sweet spot)
Session / auth tokens   → Key-Value (Redis — O(1) lookup, TTL expiry built in)

This is one product, five data stores. That's normal at this scale, not over-engineering — each store is doing the one thing it's fastest at.

Failure Modes¶

Forcing Joins on a Document Store¶

Modeling a document database like a relational one — normalizing into many small collections and joining in application code — gets you the worst of both: no query planner to optimize the join, and N+1 round trips.

Detection: application code doing loops of queries per parent document Fix: embed related data that's read together; reference (and denormalize) data that's independently updated

Wide-Column Table Designed Around the Entity, Not the Query¶

Cassandra tables designed like relational tables (users, posts, joined at read time) force scatter-gather reads across partitions — the opposite of what wide-column stores are for.

Detection: read queries hitting multiple partitions or requiring ALLOW FILTERING Fix: one table per query pattern, denormalize aggressively, accept write amplification

Eventual Consistency Surprising the Product¶

A key-value or wide-column store returns stale data right after a write (read-your-own-write violation) — a user posts a comment and doesn't see it on refresh.

Detection: support tickets like "my update disappeared" that resolve themselves on retry Fix: read-your-writes via session affinity to the same replica, or route the immediate post-write read to the primary

Graph Database for Simple Lookups¶

Using a graph database for data that's mostly point lookups (not traversal) adds operational and query-language overhead with no payoff.

Detection: most queries are single-hop equality lookups, not multi-hop traversal Fix: graph DBs earn their cost on 2+ hop traversal; for 0–1 hop, a relational or document store is simpler and cheaper

Production Debugging¶

Symptom: Feature works in staging, times out in production at scale.

1. Is the query doing a join/traversal the store isn't built for?
   → EXPLAIN (relational), query profiler (Mongo), trace (graph)
2. Is the access pattern actually point-lookup but modeled as relational?
   → check for single-row SELECTs behind a full schema + joins overhead
3. Is a wide-column table being scanned across partitions?
   → check for ALLOW FILTERING or missing partition key in WHERE
4. Is eventual consistency causing retried/duplicate writes downstream?
   → check idempotency keys, write timestamps vs. read timestamps
5. Is one store doing double duty as both OLTP and analytics?
   → move reporting to a warehouse; don't run aggregate queries on the primary

Metrics: query_latency_p99{store}, cross_partition_scans, join_row_estimate_vs_actual, stale_read_rate, replica_lag.

Scaling Limits¶

• Relational scales reads easily (replicas) but writes need sharding — plan the shard key before you need it, not after.
• Document stores scale writes well but joins across collections get worse, not better, as data grows — denormalize early.
• Key-value stores scale almost linearly but offer no query flexibility — if you find yourself scanning keys by pattern, it's the wrong store.
• Wide-column stores scale to petabytes and huge write volume but every new query pattern may need a new table (denormalized copy) — schema evolution has an operational cost.
• Graph traversal performance degrades with unconstrained hop count on dense graphs — bound the traversal depth or pre-materialize common paths.
• Time-series stores scale write throughput almost linearly, but unbounded label cardinality (see the cardinality trap above) is the failure mode that actually takes them down — not raw point volume.

Decision Table¶

flowchart TD
    A[What's the access pattern?] -->|Ad-hoc joins across entities| B[Relational]
    A -->|Whole aggregate, one call| C[Document]
    A -->|Exact key, O(1)| D[Key-Value]
    A -->|Time/partition range scan, huge write volume| E[Wide-Column]
    A -->|Multi-hop traversal| F[Graph]
    A -->|Numeric metric append + downsample/retain| G[Time-Series]

Polyglot Persistence¶

Most systems past a certain scale don't pick one database family — they pick several, each for the workload it's best at. This is polyglot persistence, and it's the normal end state, not a smell.

Example architecture — an e-commerce platform:

Relational (Postgres)   → orders, payments, inventory counts (needs ACID transactions)
Document (MongoDB)      → product catalog (variable attributes per category)
Key-Value (Redis)       → cart sessions, rate limiting, feature flags
Wide-Column (Cassandra) → clickstream / view events at massive write volume
Graph (Neo4j)           → "customers who bought X also bought Y" recommendations
Time-Series (InfluxDB)  → infra metrics / request latency for the platform's own observability
Warehouse (Snowflake)   → nightly ETL from all of the above for BI/reporting

The cost of polyglot persistence is operational: more systems to run, monitor, back up, and staff for. The Staff-level judgment call is when the access-pattern mismatch is expensive enough to justify that operational cost — a startup with one Postgres instance handling everything is often correct; a platform at 100M users with five specialized stores is also often correct. The mistake is picking the second architecture on day one, or refusing to leave the first one once a single access pattern is clearly the bottleneck.

Trade-offs¶

Interview Questions¶

Reasoning Exercises¶

1. You're building a URL shortener (short_code → long_url, billions of entries, read-heavy, no relationships). Which family fits best, and why would relational be overkill?
2. A ride-sharing app needs: driver locations (updated every few seconds), trip history (needs joins with payments and users), and "drivers near this rider" queries. Sketch which store handles each and why a single database wouldn't fit all three.
3. Your team stores user profiles in MongoDB and just added a requirement: "generate a report of all users who made a purchase in the last 30 days, joined with their support ticket history (in a separate relational database)." What's the right place to do this join, and why not in the application layer at request time?
4. A wide-column table is designed as partition_key = event_type, clustering_key = timestamp. After six months, the login event type partition is 500x larger than any other and is timing out. What's the actual design mistake, and how would you fix the partition key?
5. Your infra metrics currently live in a hand-rolled Cassandra table (partition_key = host_id, clustering_key = timestamp). It works, but every dashboard query re-implements downsampling in application code, and a recent incident happened because nobody noticed retention had silently grown to 3 years of raw data. What does moving to a dedicated time-series store actually buy you here, given that the storage shape barely changes?

Key Takeaways¶