system-design · beginner

SQL vs NoSQL — Choosing a Data Store

The Central Question

Consider two distinct data persistence challenges in a modern e-commerce platform:

• Challenge A: A financial ledger service processing customer payment transactions. The service requires strict multi-row balance constraints (account_A_balance - $50 >= 0), strict foreign key relationships, and guaranteed transactional integrity across multiple tables.
• Challenge B: A real-time clickstream analytics engine tracking 500,000 user activity events per second. The payload structures vary continuously based on user device type, and the engine requires ultra-high write throughput without complex multi-table joins.

At the persistence layer, systems select between two primary database paradigms: Relational Databases (SQL) and Non-Relational Databases (NoSQL).

This lesson answers one central question: How do Relational (SQL) and Non-Relational (NoSQL) database paradigms differ in schema structure, transaction guarantees, and scaling characteristics, and how do engineers choose the correct data store for financial ledgers vs high-throughput document stores?

Data Model Modeling: Normalized Tables vs. Embedded Documents

The fundamental distinction between SQL and NoSQL databases lies in how data is structured on disk and queried by applications:

flowchart TB
  subgraph Relational Model (SQL - Normalized Tables)
    Users["Users Table (id, name, email)"]
    Orders["Orders Table (id, user_id, total)"]
    Items["Order_Items Table (id, order_id, product_name, price)"]
    
    Users -->|1-to-Many Foreign Key| Orders
    Orders -->|1-to-Many Foreign Key| Items
    Note1["Requires SQL JOINs to assemble full order object."]
  end
  subgraph Document Model (NoSQL - Embedded JSON)
    Doc["Orders Collection (Single JSON Document)<br/>{<br/>  orderId: 'ord_901',<br/>  user: { id: 42, name: 'Alice' },<br/>  items: [{ product: 'Laptop', price: 1200 }]<br/>}"]
    Note2["Single disk read fetches complete order hierarchy."]
  end

Figure 1: Comparison of normalized relational tables against an embedded NoSQL JSON document.

1. Relational Data Modeling (Normalized Tables)

-- Relational Query Requiring Multi-Table Joins
SELECT u.name, o.order_id, i.product_name, i.price
FROM users u
JOIN orders o ON u.id = o.user_id
JOIN order_items i ON o.order_id = i.order_id
WHERE u.id = 42;

2. Document Data Modeling (Denormalized JSON)

Data Store Taxonomy: Four Types of NoSQL

"NoSQL" is not a single database engine. It represents four distinct non-relational database families optimized for different query access patterns:

flowchart TD
  NoSQL[NoSQL Database Family] --> KV[1. Key-Value Stores<br/>Redis / DynamoDB]
  NoSQL --> Doc[2. Document Stores<br/>MongoDB / Couchbase]
  NoSQL --> Col[3. Wide-Column Stores<br/>Cassandra / ScyllaDB]
  NoSQL --> Graph[4. Graph Databases<br/>Neo4j / Amazon Neptune]
  
  KV -->|Access Pattern| KVDesc["Fast O(1) primary key lookups & session caches."]
  Doc -->|Access Pattern| DocDesc["Flexible JSON document queries & product catalogs."]
  Col -->|Access Pattern| ColDesc["High-throughput append-only time-series telemetry."]
  Graph -->|Access Pattern| GraphDesc["Traversing complex relationships in social networks."]

Figure 2: Taxonomy of the four primary NoSQL database categories and their access patterns.

Architectural Comparison of Data Stores

Consistency & Guarantees: ACID vs. BASE

The trade-off between SQL and NoSQL maps directly to the underlying transaction guarantees provided by the database engine:

flowchart LR
  subgraph SQL: ACID Guarantees
    ACID["Atomicity, Consistency, Isolation, Durability<br/>• Strict Multi-Row Integrity<br/>• Immediate Consistency"]
  end
  subgraph NoSQL: BASE Guarantees
    BASE["Basically Available, Soft-state, Eventual consistency<br/>• High Availability Focus<br/>• Eventual Consistency Window"]
  end

Figure 3: Contrasting relational ACID transactional guarantees against NoSQL BASE availability models.

1. ACID Guarantees (Relational)

• Atomicity: All operations in a transaction succeed, or all roll back.
• Consistency: Database transitions from one valid schema state to another, enforcing foreign keys and check constraints.
• Isolation: Concurrent transactions execute without inter-leaving corruption.
• Durability: Committed data survives system crashes.

2. BASE Guarantees (NoSQL)

• Basically Available: The database prioritizes remaining operational for reads and writes even during network partitions.
• Soft-state: Data state may change over time without explicit user interaction due to background replica synchronization.
• Eventual Consistency: Replicas will eventually converge to identical states, but temporary stale reads may occur during write propagation windows.

Scaling Paradigms: Vertical vs. Horizontal Partitioning

How SQL and NoSQL scale hardware resources defines their operational boundaries:

flowchart TB
  subgraph Vertical Scaling: Scale-Up (SQL Standard)
    VM1[Single Large Server<br/>128 Cores / 1 TB RAM] -->|Hardware Upgrade| VM2[Bigger Server<br/>256 Cores / 2 TB RAM]
    Note1["Ceiling limited by maximum single-machine hardware size."]
  end
  subgraph Horizontal Scaling: Scale-Out (NoSQL Standard)
    Cluster[Distributed Cluster] --> Node1[Server Node 1]
    Cluster --> Node2[Server Node 2]
    Cluster --> Node3[Server Node 3]
    Note2["Scales near-infinitely by adding inexpensive commodity nodes."]
  end

Figure 4: Structural differences between vertical scaling (scaling up) and horizontal partition scaling (scaling out).

Why NoSQL Scales Horizontally More Easily

NoSQL document and key-value stores enforce a strict Partition Key. Because all data for a specific partition key resides on a single node, NoSQL databases distribute read/write traffic evenly across hundreds of commodity nodes without complex cross-node transactions.

Decision Matrix: When to Choose SQL vs. NoSQL

Engineers select database engines based on query access patterns and transaction requirements rather than personal preference:

flowchart TD
  Q1{Do you require multi-row ACID transactions & relational joins?} -->|Yes| SQL[Choose Relational SQL<br/>PostgreSQL / MySQL]
  Q1 -->|No| Q2{Do you require flexible schemas & high write throughput?}
  
  Q2 -->|Yes| Q3{What is your primary query access pattern?}
  Q2 -->|No| SQL
  
  Q3 -->|Simple Key Lookup| KV[Choose Key-Value Store<br/>Redis / DynamoDB]
  Q3 -->|Rich Nested Documents| Doc[Choose Document Store<br/>MongoDB]
  Q3 -->|Time-Series / Sensor Logs| Col[Choose Wide-Column Store<br/>Cassandra]

Figure 5: Decision tree for selecting between SQL and NoSQL database paradigms.

Complete Worked Example: CheckoutLab Multi-Database Architecture

Modern enterprise platforms rarely rely on a single database. Instead, they deploy a Polyglot Persistence architecture, using the optimal database for each specific service domain in the CheckoutLab platform (checkoutlab.com).

sequenceDiagram
    autonumber
    actor Client as Mobile Client
    participant GW as API Gateway
    participant OrderSvc as Order Service
    participant CatalogSvc as Catalog Service
    participant SessionSvc as Session Service
    
    participant PsqlDB as PostgreSQL (SQL)
    participant MongoDB as MongoDB (Document)
    participant RedisDB as Redis (Key-Value)
    
    Note over Client,RedisDB: 1. Validate Session (Key-Value Access)
    Client->>GW: POST /v1/checkout (Session-Token: abc)
    GW->>SessionSvc: GET session_abc
    SessionSvc->>RedisDB: GET session_abc
    RedisDB-->>SessionSvc: Return User 42 JSON
    
    Note over Client,PsqlDB: 2. Process Order Payment (ACID Transaction)
    GW->>OrderSvc: Create Order ($150)
    OrderSvc->>PsqlDB: BEGIN; UPDATE balance... INSERT order... COMMIT;
    PsqlDB-->>OrderSvc: Transaction Committed (HTTP 201)
    
    Note over Client,MongoDB: 3. Log Analytics Event (High-Throughput Write)
    OrderSvc->>CatalogSvc: Log Event (Order Created)
    CatalogSvc->>MongoDB: db.events.insertOne({ event: 'CHECKOUT', userId: 42 })
    MongoDB-->>CatalogSvc: Write Acknowledged

Figure 6: Polyglot persistence sequence diagram illustrating specialized SQL, Key-Value, and Document stores in one workflow.

Failure Modes and Engineering Mitigations

What You Should Remember

1. SQL uses structured tables; NoSQL uses flexible schemas: SQL enforces rigid schemas, foreign keys, and normalization. NoSQL uses denormalized documents, key-value maps, or column families.
2. ACID vs. BASE: Relational databases prioritize strict multi-row ACID transactions and immediate consistency. NoSQL databases prioritize BASE availability, horizontal scale, and eventual consistency.
3. Choose based on access patterns: Use SQL for financial ledgers, transactional ordering, and complex multi-table joins. Use NoSQL for session caching, high-frequency telemetry, and flexible document stores.
4. Vertical vs. Horizontal scaling: SQL typically scales vertically on a single large server. NoSQL scales horizontally across distributed commodity nodes using partition keys.
5. Embrace Polyglot Persistence: Modern architectures combine SQL (for core transactions), Redis (for session caching), and NoSQL (for analytics) within the same platform.

Operational Governance of Polyglot Persistence

Glossary of Terms

Practice Scenario and Self-Assessment

Architecture Scenario

The system requires:

1. User Accounts & Payment Billing (Strict financial integrity)
2. Real-time Driver GPS Location Tracking (100,000 updates/sec)
3. User Session Caching (Sub-millisecond lookup)

1. Recommend the optimal database paradigm (SQL, Document NoSQL, Key-Value, or Wide-Column) for each of the three requirements.
2. Explain the architectural failure that occurs if you attempt to store 100,000 driver GPS coordinates per second inside a single un-sharded PostgreSQL relational table.

Interactive Self-Assessment

It provides strict multi-row ACID transactions and schema constraints, guaranteeing atomic all-or-nothing financial updates.

It automatically scales horizontally across hundreds of commodity nodes without configuration.

It eliminates the need for schema definitions and data types.

It stores all financial data inside un-structured JSON text files.

Vertical scaling hits single-machine hardware ceilings and exponential costs, whereas horizontal scaling adds commodity nodes continuously.

Vertical scaling eliminates foreign key constraints automatically.

Horizontal scaling disables TLS network encryption across nodes.

Vertical scaling speeds up client-side browser HTML rendering.

What to Learn Next

• ACID Transactions — All-or-Nothing Database Work: Deep dive into Atomicity, Isolation levels, and Write-Ahead Logging.
• Database Indexes — Find Rows Without Scanning Everything: Learn how B-Trees accelerate relational and NoSQL queries.
• Data Replication — Keeping Copies in Sync: Master primary-replica log shipping and read scaling.

Track: Data, Storage and Messaging

Previous: Search System Design

Next: Types of Databases — Matching Engines to Access Patterns

By Shubham Jain

All articles · Study paths

Shubham Jain · Learning Lab