SQL vs NoSQL

Metadata

1. Overview

What problem does it solve?

Data comes in different shapes, sizes, and velocity.

• A banking ledger requires strict consistency, relationships, and guarantees that no money is ever lost. (SQL)
• A social media feed requires storing unstructured JSON posts, scaling to 100,000 writes per second, and prioritizing speed over absolute consistency. (NoSQL)

Choosing the right database paradigm (SQL vs NoSQL) is the most consequential architectural decision in any system design interview.

What breaks without it?

• Using SQL for everything: Your database crashes under massive write-loads because scaling a relational database horizontally (across multiple servers) is notoriously difficult.
• Using NoSQL for everything: You end up writing complex "joins" in your application code, and your financial data becomes corrupted because NoSQL often sacrifices strict ACID guarantees.

2. Motivation

The History of the Split

For 30 years (1980s - 2000s), Relational Databases (SQL) like Oracle, MySQL, and PostgreSQL ruled the world. They were designed for an era when storage was incredibly expensive. Their goal was to eliminate data duplication (Normalization) and ensure data integrity (ACID).

In the mid-2000s, companies like Google and Amazon hit the limits of SQL. They were generating petabytes of data (web indexes, shopping carts). Relational databases couldn't scale horizontally.

• Google invented Bigtable (Column-Family).
• Amazon invented Dynamo (Key-Value).

This spawned the NoSQL (Not Only SQL) movement, prioritizing horizontal scalability, flexible schemas, and high write throughput over strict relational constraints.

3. Real-World Usage

4. Prerequisites

5. Visual Explanation

Data Modeling: SQL vs NoSQL

Scenario: A User and their Orders.

SQL (Normalized): Data is split across tables to prevent duplication. Reads require JOINs.

erDiagram
    USERS ||--o{ ORDERS : places
    USERS {
        int id PK
        string name
    }
    ORDERS {
        int id PK
        int user_id FK
        float total
    }

NoSQL Document (Denormalized): Data is grouped together. Reads are fast (O(1)), but updating a username might require updating thousands of orders if not careful.

{
  "_id": 1,
  "name": "Sourav",
  "orders": [
    { "order_id": 101, "total": 50.0 },
    { "order_id": 102, "total": 20.0 }
  ]
}

6. Internal Working

6.1 ACID Properties (The hallmark of SQL)

When you transfer $100 from Alice to Bob, you deduct $100 from Alice, and add $100 to Bob. If the server crashes in between, the money vanishes. SQL solves this with Transactions that guarantee ACID properties:

1. Atomicity: All or nothing. If part of the transaction fails, the entire thing rolls back.
2. Consistency: Data must obey all constraints (e.g., balance cannot be negative).
3. Isolation: Concurrent transactions don't interfere with each other.
4. Durability: Once committed, the data is saved to disk and survives a power loss.

(Note: Modern NoSQL databases like DynamoDB and MongoDB now support transactions, but they are slower and not the primary design goal).

6.2 Scaling SQL vs NoSQL

• SQL scales Vertically: Buy a bigger server with more RAM and CPU. Scaling SQL horizontally (Sharding) is extremely difficult because JOINs and Transactions do not work well across different physical servers.
• NoSQL scales Horizontally: Designed from day 1 to be distributed across cheap commodity servers. Data is partitioned (Block 017) using consistent hashing. If you need more capacity, you just add another server to the ring.

7. Implementation

Why Go? Go is excellent for building adapters that interface with different database paradigms. We will build a simple mock comparing how you construct queries in a Relational mapping vs a Document mapping.

/*
014 - SQL vs NoSQL Data Modeling
Demonstrates the difference between querying Normalized Relational data
and Denormalized Document data.
Run: `go run databases.go`
*/
package main

import (
	"encoding/json"
	"fmt"
)

// ── 1. SQL (Relational) Mock ──

type SQLUser struct {
	ID   int
	Name string
}

type SQLOrder struct {
	ID     int
	UserID int
	Total  float64
}

type SQLDatabase struct {
	Users  []SQLUser
	Orders []SQLOrder
}

func (db *SQLDatabase) QueryUserWithOrders(userID int) {
	fmt.Println("--- SQL: Relational JOIN ---")
	// 1. Find the user
	var user *SQLUser
	for _, u := range db.Users {
		if u.ID == userID {
			user = &u
			break
		}
	}

	// 2. Find all orders belonging to the user (The JOIN)
	var orders []SQLOrder
	for _, o := range db.Orders {
		if o.UserID == userID {
			orders = append(orders, o)
		}
	}

	fmt.Printf("User: %s\n", user.Name)
	for _, o := range orders {
		fmt.Printf(" - Order #%d: $%.2f\n", o.ID, o.Total)
	}
	fmt.Println()
}


// ── 2. NoSQL (Document) Mock ──

// The Document contains the User AND their Orders in one struct (Denormalized)
type NoSQLDocument struct {
	ID     string  `json:"_id"`
	Name   string  `json:"name"`
	Orders []Order `json:"orders"`
}

type Order struct {
	OrderID int     `json:"order_id"`
	Total   float64 `json:"total"`
}

type NoSQLDatabase struct {
	Collection map[string]NoSQLDocument
}

func (db *NoSQLDatabase) QueryDocument(docID string) {
	fmt.Println("--- NoSQL: Document Fetch ---")
	// 1. Fetch the single document (No JOINs required!)
	doc, exists := db.Collection[docID]
	if !exists {
		fmt.Println("Document not found")
		return
	}

	// Pretty print the JSON document
	bytes, _ := json.MarshalIndent(doc, "", "  ")
	fmt.Println(string(bytes))
	fmt.Println()
}


// ── 3. Simulation ──

func main() {
	// Seed SQL Database
	sqlDB := &SQLDatabase{
		Users: []SQLUser{{ID: 1, Name: "Sourav"}},
		Orders: []SQLOrder{
			{ID: 101, UserID: 1, Total: 50.0},
			{ID: 102, UserID: 1, Total: 20.0},
		},
	}

	// Seed NoSQL Database
	noSQLDB := &NoSQLDatabase{
		Collection: map[string]NoSQLDocument{
			"user_1": {
				ID:   "user_1",
				Name: "Sourav",
				Orders: []Order{
					{OrderID: 101, Total: 50.0},
					{OrderID: 102, Total: 20.0},
				},
			},
		},
	}

	// Execute Queries
	sqlDB.QueryUserWithOrders(1)
	noSQLDB.QueryDocument("user_1")
}

Sample Output

$ go run databases.go
--- SQL: Relational JOIN ---
User: Sourav
 - Order #101: $50.00
 - Order #102: $20.00

--- NoSQL: Document Fetch ---
{
  "_id": "user_1",
  "name": "Sourav",
  "orders": [
    {
      "order_id": 101,
      "total": 50
    },
    {
      "order_id": 102,
      "total": 20
    }
  ]
}

8. Complexity

9. Trade-offs

The "Impedance Mismatch"

• SQL suffers from the Object-Relational Impedance Mismatch. Object-oriented code (like Java or Go classes) doesn't map perfectly to flat 2D SQL tables, requiring bulky ORMs (Hibernate/GORM).
• NoSQL Document maps perfectly to code. A JSON document is exactly the same as a Python Dictionary or a Go Struct.

The Cost of Denormalization

In NoSQL, you duplicate data for read speed. If you store the "Author Name" inside every single "Book" document, what happens if the author changes their name? You have to find and update 50 Book documents. In SQL, you update the name in one place (the Authors table).

10. Production Evolution

11. Common Bugs

12. Interview Questions

1. You are building an e-commerce checkout system. Do you choose SQL or NoSQL? Hint: SQL. Checkout involves inventory deduction, payment processing, and order creation. You need strict ACID transactions to ensure money is never lost.

2. You are building the telemetry system for an IoT fleet of 1 million cars sending GPS data every second. SQL or NoSQL? Hint: NoSQL (Column-Family like Cassandra). Relational DBs cannot handle 1 million writes per second. Cassandra's LSM trees are built for massive append-only write loads.

3. What is Denormalization? Hint: Deliberately duplicating data to avoid JOINs and speed up read queries, at the expense of slower, more complex updates.

4. Why is it hard to scale a relational database horizontally? Hint: ACID transactions and JOINs require data to be located together. If half your tables are on Server A and half on Server B, a JOIN requires massive network transfer, and a transaction requires complex Two-Phase Commits (Block 019).

13. Used By (Downstream Blocks)

• 017 Data Partitioning (Sharding) — How NoSQL databases scale horizontally.
• 018 Database Replication — How databases copy data for High Availability.
• 022 CAP Theorem — The theoretical framework that dictates why NoSQL trades Consistency for Availability.

14. Used In (Case Studies)

15. Related Blocks

16. Try It Yourself

Exercise 1: The Bounded Array Problem

In the Go NoSQL implementation, what happens if the user makes 1,000,000 orders? The NoSQLDocument struct will use gigabytes of RAM when queried. Modify the NoSQL struct to hold an array of OrderID strings (references) instead of the full Order structs, demonstrating how to handle unbounded data in Document databases.

Website Metadata