SQL vs NoSQL

This lesson on SQL vs NoSQL is hands-on and example-driven. You will be able to evaluate system requirements to select between SQL and NoSQL database architectures during system design interviews. You will justify database choices based on schema flexibility, transactional ACID guarantees, and vertical versus horizontal scaling tradeoffs.

What You'll Be Able To Do

• Evaluate system data requirements to select between relational and non-relational database models.
• Identify transactions requiring ACID guarantees versus operations tolerant of eventual consistency.
• Architect horizontally scalable data stores using NoSQL sharding and peer-to-peer replication.
• Assess hardware and cost limitations of vertical scaling in write-heavy relational workloads.

Detailed Concept Walkthrough

1. Relational Databases and ACID Compliance

Relational databases organize structured data into predefined tables with strict relationships, guaranteeing ACID properties for transactional safety.

• Mechanism: Relational engines enforce rigid schemas and table relationships using primary and foreign keys. This guarantees structural integrity and prevents invalid data insertion across related entities.
• Under the Hood: ACID compliance guarantees that database transactions execute reliably as all-or-nothing operations (Atomicity) while maintaining strict invariants (Consistency), isolated views (Isolation), and committed persistence (Durability).
• Best Practice: Choose SQL for domain models requiring multi-table transactions, financial data fidelity, and strict schema validation where write integrity outweighs horizontal scale limits.

-- Strict relational schema with ACID transactional guarantee
BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 500 WHERE account_id = 'A123';
UPDATE accounts SET balance = balance + 500 WHERE account_id = 'B456';
COMMIT;

Key Takeaway: SQL databases enforce strict schema validation and full ACID guarantees at the expense of seamless horizontal write scalability.

2. Relational Limitations and Vertical Scaling

Relational databases traditionally scale vertically by adding hardware resources, creating hard throughput ceilings and steep cost curves for write-heavy workloads.

• Mechanism: Scaling a SQL database typically involves provisioning larger instances with faster CPUs, increased RAM, and high-performance storage. This vertical approach avoids the overhead of distributed transactional consensus.
• Under the Hood: Cross-table relational joins, constraints, and ACID transactions require centralized coordination, making distributed horizontal write operations complex and resource-intensive across multiple nodes.
• Best Practice: Mitigate vertical limits by evaluating whether data access patterns genuinely require relational joins before scaling single-node hardware to cost-prohibitive tiers.

-- Complex join across normalized relational tables
SELECT o.order_id, u.username, p.product_name, o.total_amount
FROM orders o
JOIN users u ON o.user_id = u.id
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.id
WHERE o.status = 'COMPLETED';

Key Takeaway: Vertical scaling provides simple transactional guarantees but eventually hits hardware ceilings and cost inefficiencies under heavy write traffic.

3. NoSQL Models and Horizontal Sharding

NoSQL databases accommodate unstructured or rapidly evolving data models by partitioning and distributing documents, key-values, or graphs across clusters of commodity hardware.

• Mechanism: NoSQL systems store non-relational entities without rigid predefined schemas, allowing rapid schema evolution as application requirements shift. Data is partitioned across nodes using sharding algorithms.
• Under the Hood: Data records are hashed by shard key and allocated across storage nodes, allowing the cluster to increase read and write capacity simply by provisioning additional commodity servers.
• Best Practice: Select appropriate partition and shard keys to prevent data skew and avoid single-node hotspots during high-volume distributed writes.

// Flexible unstructured document representation in NoSQL
{
  "_id": "order_98745",
  "user_id": "u_102",
  "metadata": { "channel": "mobile", "retry_count": 0 },
  "items": [
    { "sku": "WIDGET-1", "qty": 2, "price": 19.99 }
  ]
}

Key Takeaway: NoSQL systems achieve horizontal write scaling and rapid iteration through dynamic schemas and distributed sharding.

4. Replication and Eventual Consistency

Distributed NoSQL architectures prioritize availability and horizontal write performance over immediate consistency via peer-to-peer replication.

• Mechanism: In distributed peer-to-peer clusters, write operations are accepted by local nodes and asynchronously propagated across replica nodes throughout the cluster.
• Under the Hood: Because write propagation takes time across network partitions, different nodes temporarily return different versions of the same record until updates fully settle across the replica set.
• Best Practice: Utilize eventual consistency for high-throughput, latency-sensitive features where temporary read discrepancies do not compromise system correctness.

# Conceptual distributed write scenario
# Node A accepts write:
curl -X POST http://node-a:8080/records -d '{"id": "doc1", "val": 42}'
# Immediate read from Node B (may return stale value until replication settles):
curl -X GET http://node-b:8080/records/doc1

Key Takeaway: Distributed NoSQL systems trade immediate consistency for high availability and horizontally scalable write performance through eventual consistency.

Topics Covered in SQL vs NoSQL

1. Foundational Database Definitions (0:00 - 0:36) — Introduces relational table models and vertical scaling alongside non-relational document, key-value, and graph horizontal architectures.
2. Strengths of Relational Databases (0:36 - 1:14) — Details how relational schema design and ACID transaction guarantees protect structured data integrity.
3. Limitations of Relational Databases (1:14 - 1:50) — Analyzes the high setup overhead, schema rigidity, and costly horizontal scaling barriers of SQL databases.
4. Advantages of NoSQL Databases (1:50 - 2:27) — Examines schema flexibility, rapid prototyping, and horizontal sharding across cost-effective commodity servers.
5. Consistency Tradeoffs in Distributed NoSQL (2:27 - 3:28) — Explains how peer-to-peer replication achieves write scale while introducing eventual consistency across cluster nodes.

System Design Fundamentals Cheat Sheet

• CREATE TABLE — Defines rigid relational table structure and constraints

  CREATE TABLE users (id INT PRIMARY KEY, name TEXT);
  

• BEGIN TRANSACTION — Executes atomic updates across relational tables

  BEGIN TRANSACTION; UPDATE u SET val=1; COMMIT;
  

• Document Insert — Stores flexible unstructured document in collection

  db.collection.insertOne({user: 'alice', age: 30});
  

• Sharding Key — Partitions distributed data across cluster nodes

  sh.shardCollection('db.orders', {order_id: 'hashed'});
  

• Secondary Read — Reads potentially stale data from replica

  rs.secondaryOk(); db.orders.find({id: 101});
  

Comparison Table

Common Pitfalls

• Mistake: Assuming NoSQL guarantees immediate data consistency across all replica nodes after a write. Avoid: Design client workflows to tolerate eventual consistency or select relational databases for strict read-after-write needs.
• Mistake: Scaling a relational database horizontally for complex write-heavy joins without considering architectural complexity. Avoid: Scale SQL vertically first or transition unstructured write-heavy models to distributed NoSQL stores.
• Mistake: Using rigid relational schemas for rapidly changing unstructured domain models. Avoid: Select dynamic NoSQL document stores to iterate rapidly without requiring frequent database migration scripts.
• Mistake: Selecting NoSQL for critical financial transactions without evaluating ACID transaction requirements. Avoid: Use ACID-compliant relational databases whenever multi-table consistency and strict transaction integrity are mandatory.

FAQs

• Why is horizontal scaling difficult for relational databases? Relational joins, constraints, and ACID transactions require synchronized cross-node coordination, introducing significant latency and distributed locking overhead.
• What does eventual consistency mean in practice? When data is updated on one node, replicas temporarily serve stale data until the update propagates across the distributed cluster.
• Can NoSQL databases store structured data? Yes, NoSQL handles structured, semi-structured, and unstructured data, but it does not enforce relational integrity constraints out of the box.
• When should I choose SQL over NoSQL in an interview? Choose SQL when you need ACID guarantees, multi-entity relationships, strict schemas, or when vertical scaling meets the system's write volume.