Day 8 — SQL vs NoSQL — When to Pick Which

Time budget: ~60 min · Level: Foundational for every case study from here on.

This is the single most-asked trade-off in FAANG system design. Most candidates treat it as a one-dimensional choice ("SQL good, NoSQL scalable") — which is wrong and immediately signals a junior mindset. The reality is a multi-dimensional decision driven by data shape, access patterns, consistency needs, scale, and operational cost.

Kill the myths first

Before we go anywhere, disarm these:

The right question is never "SQL or NoSQL?" — it's "what data model + consistency + scale + query pattern does this service need?"

The 7 dimensions of choosing a database

You should walk into every DB question with this checklist:

1. Data shape — flat records? nested documents? graph? time-series?
2. Query patterns — single-key lookup? range scans? joins? full-text search? ad-hoc analytics?
3. Consistency requirements — strong? read-your-writes? eventual? Snapshot isolation?
4. Scale — rows, QPS, storage, growth rate
5. Availability & geographic distribution — single region OK? multi-region active-active?
6. Write/read ratio — 99% read? balanced? write-heavy?
7. Team & operational cost — do you have DBAs? is managed OK? cost per TB?

Answer these 7, and the "which DB" answer usually falls out.

Database taxonomy — know the map

Row-oriented SQL (relational)

Examples: PostgreSQL, MySQL, SQL Server, Oracle, SQLite

Data model: rows of typed columns. Enforced schema.

Strengths: - ACID transactions with real isolation guarantees - Powerful joins across normalized tables - Ad-hoc queries with WHERE, GROUP BY, ORDER BY - Rich indexing: B-tree, hash, GIN, GiST, partial, expression, covering - Mature ecosystem (ORMs, migrations, monitoring, backup tools)

Weaknesses: - Vertical scaling ceiling (~64 cores, ~2 TB RAM per node) before you need sharding - Sharding is possible but adds significant complexity (Vitess, Citus, PgBouncer patterns) - Fixed schema requires migrations for changes - JSON support exists but isn't as efficient as document DBs for deeply nested data

Use when: transactional workload, complex queries, joined data, moderate scale (up to ~50k QPS on a single node with replicas).

Common at scale: Github, Stripe, Instagram all run on Postgres/MySQL at massive scale.

Wide-column stores

Examples: Cassandra, ScyllaDB, HBase, Bigtable

Data model: rows keyed by a partition key + sort key, columns can vary per row. Think "distributed sorted map of maps."

Strengths: - Enormous write throughput — LSM-tree storage optimized for sequential writes - Linear horizontal scaling — add nodes, capacity grows - Multi-datacenter replication built in - Tunable consistency per query (QUORUM, ONE, ALL) - Great for time-series and append-heavy workloads

Weaknesses: - No joins — you denormalize in the data model - Limited transaction support (only single-partition atomic writes) - Query patterns must be known up front — you design tables around queries, not vice versa - Bad for ad-hoc analytics (use Spark on top for that)

Use when: massive write scale, time-series / event logging, known access patterns, geo-distribution needed.

Real users: Netflix (Cassandra), Discord (Cassandra), Apple (Cassandra), Instagram (Cassandra for feed).

Key-value stores

Examples: Redis, DynamoDB (also document), Riak, etcd, Memcached

Data model: just key -> value. Value may be blob, string, JSON, or type-specific (Redis has lists, sets, sorted sets, hashes, streams).

Strengths: - Blazing fast — often sub-millisecond - Trivially horizontal — hash the key, done - Simple mental model - Redis specifically has rich data structures + atomic ops (INCR, LPUSH, ZADD)

Weaknesses: - No queries beyond "get by key" (Redis has some indexes on sorted sets) - Redis limited by single-threaded model per shard (~100k QPS/shard) - No relationships / joins

Use when: caching, session storage, rate limiting counters (Redis + INCR), leaderboards (Redis sorted sets), pub/sub, distributed locks.

Document databases

Examples: MongoDB, DynamoDB (also KV), DocumentDB, Firestore, CouchDB

Data model: collections of JSON-like documents. Fields can vary per document.

Strengths: - Natural for hierarchical data (a user with nested address, orders, preferences) - Flexible schema — good for rapidly evolving domains - Horizontal sharding built in - Rich query language (MongoDB has aggregations, indexes on nested fields)

Weaknesses: - No joins across collections in most (MongoDB has $lookup but it's slow) - Schema drift over time can cause bugs if not disciplined - Transactions historically weak (MongoDB now supports them but with caveats) - Denormalization causes update anomalies (change one thing, update it in 10 places)

Use when: content management, product catalogs (variable attributes), user profiles with nested data, semi-structured data.

Graph databases

Examples: Neo4j, Amazon Neptune, JanusGraph, TigerGraph

Data model: nodes + edges (relationships), both with properties.

Strengths: - Traversals ("friends of friends", "shortest path", "6 degrees") are O(edges) not O(rows) - Natural fit for social networks, fraud detection, recommendation, knowledge graphs

Weaknesses: - Not great for anything else — bulk analytics, single-key lookups better done elsewhere - Operationally harder to scale than KV/document - Smaller ecosystem

Use when: graph is genuinely the domain (LinkedIn connections, fraud rings, dependency analysis).

Time-series databases

Examples: InfluxDB, TimescaleDB (Postgres extension), Prometheus TSDB, ClickHouse, OpenTSDB

Data model: timestamped points, tagged with dimensions (host, region, metric).

Strengths: - Compression is 10-100× better than general-purpose DBs for time-series - Automatic downsampling (raw at 1s → 1min → 1hr → 1day retention tiers) - Time-range queries are the primary access pattern and are extremely fast - Purpose-built retention policies

Weaknesses: - Not for OLTP - Query languages are non-standard (Flux, PromQL)

Use when: metrics, IoT sensor data, application observability, financial ticks.

Search engines

Examples: Elasticsearch, OpenSearch, Solr, Meilisearch, Typesense

Data model: inverted index on documents + fields.

Strengths: - Full-text search with tokenization, stemming, fuzzy matching - Faceted search, autocomplete, geo-search - Highly parallel query execution

Weaknesses: - Not a system of record — data can be lost on cluster corruption - Expensive to keep in sync with a source-of-truth DB (typically via CDC) - Not for transactional workloads

Use when: search UI, log analytics, filtered/faceted browsing.

NewSQL / distributed SQL

Examples: Google Spanner, CockroachDB, TiDB, YugabyteDB, Amazon Aurora

Data model: relational + SQL, but distributed horizontally.

Strengths: - Familiar SQL + ACID transactions across shards (via Paxos/Raft) - Horizontal scaling - Multi-region strong consistency (Spanner's TrueTime)

Weaknesses: - More expensive than either Postgres or Cassandra - Cross-shard transactions are slower than single-node ACID - Operational complexity higher than managed Postgres

Use when: you genuinely need SQL semantics + horizontal scale + geographic distribution. Financial ledgers, global inventory.

Blob / object storage

Examples: S3, GCS, Azure Blob, MinIO

Data model: file → bytes, keyed by path.

Strengths: - Effectively unlimited capacity - Very cheap per GB - Highly durable (11 nines) - CDN-friendly

Weaknesses: - No queries — GET/PUT/DELETE only - Higher per-operation latency (~100ms) than KV stores - Not for small hot objects

Use when: user uploads, videos, images, backups, ML datasets, static assets.

Polyglot persistence — most real systems

A real FAANG system uses many databases. Example — Twitter/X circa 2020:

The lesson: don't design "one DB for everything." Match each data domain to the best-fit DB. This is called polyglot persistence.

Choosing under pressure — decision heuristics

Some interview shortcuts:

• Write-heavy time-series or event data? → Cassandra, ScyllaDB, or TimescaleDB.
• User profiles + auth? → Postgres or MySQL.
• Session storage / rate-limit counters? → Redis.
• Product catalog with faceted search? → Postgres or MongoDB as source of truth, Elasticsearch for search.
• Social graph traversals? → Neo4j / Neptune (if graph is central) OR wide-column with adjacency lists (if scale demands).
• Financial ledger with multi-region strong consistency? → Spanner / CockroachDB.
• User uploads (video, images)? → S3 for bytes, Postgres for metadata.
• Chat messages? → Cassandra (append-heavy, partition by conversation_id).
• Full-text search over documents? → Elasticsearch downstream of the source-of-truth DB.
• Real-time leaderboard? → Redis sorted set.

The scale conversation — when does SQL "not scale"?

This is where capacity math earns its keep. Don't assert "we need NoSQL because scale" without numbers.

Rough Postgres/MySQL single-node limits (2026 hardware):

Read scaling with replicas: you can 10× reads by adding replicas. Writes stay bounded by the leader.

When to actually shard SQL: - Data > 10 TB per node → yes - Writes > 20k QPS sustained → yes - Otherwise: probably not yet. Vertical scale + read replicas usually suffices longer than junior engineers assume.

Real-world calibration: - Instagram ran on a single Postgres node for years (2010-2012) before sharding. - Github still primarily on MySQL (sharded via Vitess) as of 2024. - Discord moved from MongoDB → Cassandra → ScyllaDB as scale grew — MongoDB wasn't fast enough at their write rate.

So — before answering "NoSQL," ask: what's the actual QPS and data size? Can Postgres do it?

Common interview traps

1. Picking DynamoDB for a joined query. DynamoDB is amazing for single-key lookups. If you say "user 42's orders" and need to fetch orders joined with product info joined with shipping status — you're in for pain. You'll denormalize until you cry.

2. Picking MongoDB "for flexibility" without knowing access patterns. You'll end up scanning collections because you didn't index the right fields.

3. Ignoring transactions. If your business requires "debit account A, credit account B — both or neither," ACID is not optional. NoSQL alternatives (sagas, outbox) exist but are more complex.

4. Confusing OLTP and OLAP. Postgres is fine for transactions but bad for "SUM(revenue) grouped by day for the last 5 years." That's a data warehouse job (Snowflake, BigQuery, Redshift, ClickHouse).

5. Not thinking about cost. DynamoDB at 100k QPS with 1KB records is thousands per month. Postgres on RDS at the same load is a fraction. Managed vs self-hosted also differs 10×.

6. "Just use Cassandra" without knowing partition keys. A wrong partition key gives you hot partitions and 90% of your requests hit 10% of your cluster.

Interview angle — expected structure

"Design the storage for a chat application (100M users, 500M messages/day)."

Bad answer: "Use MongoDB / Cassandra because scale."

Good answer: 1. Capacity math first: 500M msg/day ÷ 86400 = 5.8k avg writes/s, peak 5-10× = ~50k writes/s. Storage: 500M × 500B avg = 250 GB/day = ~90 TB/year. 2. Access patterns: - Write a message (partition key: conversation_id, sort key: timestamp) - Fetch last N messages in a conversation (range scan) - Notify recipients (out of scope for DB, handled by push/queue) 3. Choice: Cassandra / ScyllaDB. Reasons: 50k writes/s exceeds a single Postgres node's comfort; access pattern is single-partition range scan (Cassandra's sweet spot); geo-distribution needed; time-series nature. 4. Alternative considered: Postgres sharded on user_id. Rejected because the natural query key is conversation_id, not user_id. 5. Related storage: - User accounts → Postgres - Message search → Elasticsearch fed via CDC - Media attachments → S3 + CDN - Presence / online-status → Redis 6. Cost estimate: ~$X/month vs $Y for alternative.

Interviewers grade on: numbers, access-pattern analysis, and honest trade-off discussion.

Spaced-repetition callbacks in today's quiz

• Diagnostic Q9 (choosing DB for read-heavy analytics) reappears in a scenario in Q8. Your original answer got the right instinct (NoSQL for scale) but didn't ground it in access patterns. Today's quiz forces you to show the reasoning chain.
• Capacity math (Days 3, 6, 7 Q8 all missed the numbers) — Q8 today explicitly requires capacity math and it's part of the grade.