System Design Databases: SQL vs NoSQL and Scaling

Scenario context

SQL gives you ACID guarantees and powerful relational queries; NoSQL gives you horizontal scale and flexible schemas. The real decision is not "which is better" — it is "which trade-offs align with your workload." Understanding replication, sharding, and the CAP theorem helps you pick correctly every time.

graph TD
    subgraph Master
        A[Write Request] --> B[Write-Ahead Log]
        B --> C[Apply to MemTable]
        C --> D[Flush to SSTable / Commit]
    end
    subgraph Replicas
        D --> E[Replication Stream]
        E --> F[Apply to Replica Log]
        F --> G[Update Replica State]
    end
    G --> H[Read Request from Replica]

Observable outcome: Replication lag is the time difference between master and replica: $$\Delta = t ext{master} - t ext{replica}$$ A small $\Delta$ is acceptable for social feeds; it is catastrophic for financial ledgers.

Stage 3 — Deep Dive: How Write-Ahead Logs and LSM-Trees Protect Data

• Both SQL and NoSQL engines guard against data loss using append-only logs before modifying data files.
• SQL engines use a Write-Ahead Log (WAL) : every change is written sequentially to a log before being applied to B-Tree pages, enabling crash recovery.
• NoSQL LSM-tree engines write first to an in-memory MemTable , flush to immutable SSTables on disk, and periodically compact them.

Observable outcome: NoSQL LSM-tree engines write first to an in-memory MemTable , flush to immutable SSTables on disk, and periodically compact them.

Stage 4 — Decision Flow: Selecting the Right Database

• Use this flowchart as a first-pass guide when choosing a database for a new service.
• Most production systems combine multiple databases — for example, PostgreSQL for transactional data alongside Redis for caching.
• Start here and refine with your specific read/write ratio and consistency requirements.
• 📊 Read-Your-Writes Consistency in Replicated DB This sequence diagram illustrates the read-your-writes consistency hazard in a replicated database.

flowchart TD
    A[Define your workload] --> B{Need ACID transactions?}
    B -->|Yes| C[SQL: PostgreSQL / MySQL]
    B -->|No| D{Write-heavy at massive scale?}
    D -->|Yes| E[Wide-column NoSQL: Cassandra]
    D -->|No| F{Schema changes frequently?}
    F -->|Yes| G[Document NoSQL: MongoDB]
    F -->|No| H{Ultra-fast key lookups needed?}
    H -->|Yes| I[Key-Value Store: Redis / DynamoDB]
    H -->|No| C

Observable outcome: 📊 Read-Your-Writes Consistency in Replicated DB This sequence diagram illustrates the read-your-writes consistency hazard in a replicated database.

Stage 5 — Choosing Your Database: A Worked Example

• This example models an orders entity in an e-commerce system side-by-side in PostgreSQL and MongoDB to make the SQL-vs-NoSQL trade-off concrete and visible in real code.
• The orders entity is the canonical choice because it sits at the boundary: it demands multi-table joins and ACID guarantees for accurate reporting (SQL's strengths) yet benefits from document embedding when every read n…
• As you read, focus on how the SQL version normalises items into a separate table — enabling flexible queries at the cost of joins — while the MongoDB version embeds items directly in the order document, enabling a singl…
• SQL representation (PostgreSQL): In the normalized SQL model, customer names and order totals live in separate tables linked by a foreign key on user id .

Observable outcome: SQL representation (PostgreSQL): In the normalized SQL model, customer names and order totals live in separate tables linked by a foreign key on user id .

Stage 6 — Real-World Applications by Workload Type

Observable outcome: You can explain how “Real-World Applications by Workload Type” changes the architecture or implementation path.

End-to-end outcome

SQL internals SQL engines parse → plan → optimize → execute.

Alternatives considered

The Library vs. the Drop Box: SQL and NoSQL Intuition

By 2013, they had 10 billion pins, 100 million users, and a 4-second page load time .

Decision Guide: SQL vs. NoSQL at a Glance

Decision Guide: SQL vs. NoSQL at a Glance

Schema Evolution: SQL vs. NoSQL in Practice

One of the most tangible day-to-day differences between SQL and NoSQL is how they handle schema changes as product requirements evolve.

Failure and degraded modes

Trade-offs & Failure Modes: Trade-offs and Failure Modes

• Detection: Split-brain scenario (master-master setup): 1.
• User or operator impact: Split-brain scenario (master-master setup): 1.