SQL vs NoSQL: The Storage Engine War

In the world of System Design, choosing the right database is often the first and most critical decision. It dictates how you structure your data, how you scale, and ultimately, how fast your application feels. It’s not just about “Relational vs Non-Relational”. It’s about Access Patterns, Storage Engines, and Trade-offs.

1. The Core Philosophy

SQL (Relational)

Think of SQL as a Digital Filing Cabinet with strict rules.

  • Structure: Data is stored in Tables with fixed Columns (Schema).
  • Relationships: Tables are linked via Foreign Keys. You “Join” tables to reconstruct data.
  • ACID: Guarantees data integrity. (See ACID Transactions).
  • Scale: Traditionally scales Vertically (buy a bigger server).
  • Examples: PostgreSQL, MySQL, Oracle.

NoSQL (Non-Relational)

Think of NoSQL as a Warehouse of Flexible Containers.

  • Structure: Data is stored as Documents, Key-Values, or Graphs.
  • Schema-less: You can add fields on the fly. Record A can have “Age”, Record B can have “Hobbies”.
  • Scale: Designed to scale Horizontally (add more cheap servers). (See Data Scaling & Sharding).
  • Examples: MongoDB, Cassandra, Redis, Neo4j.

2. The Decision Framework: When to Use What?

[!IMPORTANT] Interview Pro-Tip: Never say “NoSQL is always better” or “SQL is always better”. The correct answer is “It depends on your access patterns”.

Quick Decision Table

Factor Choose SQL Choose NoSQL
Data Structure Structured, relational (Users → Orders → Items) Unstructured, nested (JSON documents, logs)
Schema Changes Rare, well-defined upfront Frequent, evolving rapidly
Transactions ACID required (Banking, E-commerce checkout) Eventual consistency acceptable (Social feeds, analytics)
Query Patterns Complex JOINs, aggregations, analytics Simple key-value lookups, full document retrieval
Scale Vertical scaling first (Single powerful server) Horizontal scaling first (Distributed across nodes)
Consistency Strong consistency (Read-after-write guaranteed) Eventual consistency (Replicas may lag)
Use Cases Banking, ERP, Inventory, Reporting Social media, IoT sensors, Logs, Caching

Decision Tree

START: What is your primary requirement?

├─ Strong ACID Transactions? 
│  └─ YES → SQL (Postgres, MySQL)
│
├─ Massive Write Throughput (>100K writes/sec)?
│  └─ YES → NoSQL (Cassandra, ScyllaDB)
│
├─ Complex Queries with JOINs?
│  └─ YES → SQL (Analytical DB like Redshift)
│
├─ Flexible Schema (Frequent changes)?
│  └─ YES → NoSQL (MongoDB, DynamoDB)
│
├─ Global Distributed Reads?
│  └─ YES → NoSQL (DynamoDB Global Tables)
│
└─ Default for CRUD apps with <1M users?
   └─ SQL (Postgres) - Simple, proven, cheap

3. Deep Dive: Storage Engines (The Hidden Engine)

Why is SQL faster for reads but NoSQL often faster for writes? It’s usually down to the data structure on disk.

A. B-Trees (The SQL Standard)

Relational databases (Postgres, MySQL) typically use B+ Trees.

  • Concept: A balanced tree structure where data is stored in leaf nodes.
  • Read Performance: O(log N). Very fast. The tree height is usually low (3-4 levels).
  • Write Performance: Poor for Random Writes. Inserting a random row requires the disk head to physically move (“Seek”) to the correct leaf node. If the node is full, the tree must Split and rebalance, causing multiple I/O operations.
  • Write Amplification: High. Changing 1 byte might require rewriting an entire 4KB or 16KB page.

B. LSM Trees (The NoSQL Speedster)

Many NoSQL databases (Cassandra, RocksDB, BigTable) use Log-Structured Merge (LSM) Trees.

  • Concept: All writes are appended sequentially to an in-memory buffer called a MemTable.
  • Write Performance: Excellent (O(1)). No disk seeking. Just appending to a log. When the MemTable is full, it is flushed to disk as an immutable SSTable (Sorted String Table).
  • Read Performance: Slower. To find a record, the DB must check the MemTable, then SSTable 1, SSTable 2, etc. This is why Bloom Filters are critical for LSM trees (to quickly skip SSTables that don’t have the key).
  • Compaction: In the background, the DB merges old SSTables into new, larger ones to clean up deleted data and reduce read costs.

[!TIP] Write Amplification Formula: WA = (Total Bytes Written to Disk) / (Data Payload Size)

  • B-Tree: High WA (Random updates rewrite pages).
  • LSM Tree: Low WA (Sequential writes), but Compaction adds background I/O load.

4. Interactive Demo: Storage Engine Racer

Visualize the fundamental difference in I/O Patterns.

  • B-Tree Mode: Watch the “Disk Head” (Yellow) jump around to find the correct spot for every insert. This simulates Random I/O.
  • LSM Mode: Watch writes stack up in the MemTable (Green) and flush sequentially to SSTables (Blue). This simulates Sequential I/O.
B+ Tree Structure (Random Seek)
[ 50 ]
10
20
60
80
Seek
Waiting for Writes...

5. Row-Oriented vs Column-Oriented (OLTP vs OLAP)

Another critical distinction is how data is physically stored.

Row-Oriented (Postgres, MySQL)

Stores data like a CSV. Good for Transactional (OLTP) workloads where you fetch “User 123’s Profile” (all columns).

  • Physical Layout: [ID:1, Name:Alice, Age:30] [ID:2, Name:Bob, Age:40]
  • Pros: Fast to write/read a single entity.
  • Cons: Slow to calculate “Average Age” (Must read Name and ID unnecessarily).

Column-Oriented (Redshift, Cassandra, ClickHouse)

Stores each column in a separate file. Good for Analytical (OLAP) workloads.

  • Physical Layout: [ID:1, ID:2], [Name:Alice, Name:Bob], [Age:30, Age:40]
  • Pros: Ultra-fast aggregations (“Select AVG(Age)”). The DB reads only the Age file.
  • Cons: Slow to write a new record (must open 3 files).

6. Case Study: System Design of a URL Shortener

Let’s apply this to a real interview question: “Design a URL Shortener (TinyURL).”

Requirements

  1. Functional: Users paste a long URL, get a short URL.
  2. Scale: 1 Billion new URLs per month. 100:1 Read/Write ratio.
  3. Performance: Redirects must be ultra-fast (<10ms).

The Decision: SQL vs NoSQL?

Option A: SQL (Postgres)

  • Pros: Easy to query metadata (“Show me all links by User X”). ACID guarantees.
  • Cons: Storing billions of rows in a single table requires Sharding (manual partitioning). Horizontal scaling is painful.
  • Verdict: Good for Metadata (User accounts), bad for the core Mapping table.

Option B: NoSQL (DynamoDB / Cassandra)

  • Pros:
    • Mapping: A Key-Value store is perfect. GET /abc1234 is a simple O(1) lookup.
    • Scale: Handles billions of rows effortlessly by auto-sharding (Consistent Hashing).
  • Cons: Hard to query relations (“Show all links by User X” requires a secondary index).
  • Verdict: Perfect for the Core Mapping Table.

Polyglot Persistence Architecture

The best designs use Polyglot Persistence (using multiple DBs).

  1. URL Mappings Table: Use NoSQL (DynamoDB).
    • Key: ShortID
    • Value: LongURL
    • Why? Massive Read/Write scale, simple K-V access.
  2. User Data: Use SQL (Postgres).
    • Table: Users, Billing
    • Why? Complex relationships, ACID for payments.

7. Summary Table

Feature SQL (B-Tree) NoSQL (LSM Tree) NewSQL
Primary Goal General Purpose, ACID Scale, Flexibility Scale + ACID
Write Cost High (Random Seek) Low (Sequential Append) Medium (Consensus Overhead)
Read Cost Low (O(log N)) Medium (Check SSTables) Low
Scaling Vertical (Hard to Shard) Horizontal (Easy Shard) Horizontal (Auto Shard)
Use Case Financials, CRM IoT, Social Feeds, Big Data Global Ledgers

8. Conclusion

  • Use SQL by default. It is reliable and flexible.
  • Use NoSQL (LSM) when you hit massive Write Scale limits.
  • Use Polyglot Persistence to get the best of both worlds.
  • Don’t forget Write Amplification: B-Trees hurt your SSDs on random writes; LSM Trees save your disk but cost CPU for compaction.