04/04/2026, 12:57 SQL vs NoSQL — Part 1: The Big Picture & SQL Deep Dive

SQL V S NOSQL — PA RT 1

The Big Picture & SQL Deep Dive

Why database choice matters, what SQL databases are, how they work internally, ACID,
JOINs, normalisation, and when SQL is the right choice

Section 01 — The Big Picture: Why Database Choice

Matters
1
What is a database, what is a DBMS, and the three pillars that decide SQL vs NoSQL

What Is a Database?

DEFINITION

A database is an organised collection of structured data stored and managed electronically. Think of
it as a giant, highly organised digital filing cabinet where data can be stored, retrieved, updated, and
deleted in a controlled, efficient manner.

Every app you use — Instagram, WhatsApp, your banking app — relies on a database to store its
data.

2
What Is a DBMS?

DEFINITION

A Database Management System (DBMS) is the software layer that manages the database. It is the
middle-man between your application code and the raw data. It handles security, querying, indexing,
backups, and ensures data integrity.

Example: MySQL (the DBMS) manages a MySQL database. The two main families of DBMS are
Relational (SQL) and Non-Relational (NoSQL).
 [Link] 1/11
04/04/2026, 12:57 SQL vs NoSQL — Part 1: The Big Picture & SQL Deep Dive

LIBRARY ANALOGY

A database is like a library — it holds all the books (data).

The DBMS is the librarian — they know where everything is, let authorised people access
books, keep the catalog updated, and ensure books are returned in the right order.

HOW YOUR APP TALKS TO DATA

YOUR APPLICATION ([Link], Python, Java) Sends SQL or queriesDBMS (MySQL, PostgreSQL, MongoDB) reads/writes DATABASE (Actual data on disk)
API queries Security · Querying · Indexing Tables, Documents, Files
Backups · Integrity · Concurrency

3
The Core Question Every Interview Asks

THE QUESTION

"Would you use SQL or NoSQL here?" — This is one of the most common system design
interview questions. Choosing wrong means you'll either struggle to scale past a few million users
(wrong NoSQL choice) or spend months debugging data inconsistencies that corrupt your
business logic (wrong SQL choice).

The answer always depends on three pillars:

THREE PILLARS OF DATABASE CHOICE

SQL or NoSQL?

① CONSISTENCY MODEL ② SCALABILITY NEED ③ DATA STRUCTURE

Does every read need to see the latest write 10,000 users or 10 million? 10 GB or 10 TB of Well-structured with clear relationships? →
immediately? Or can it tolerate slightly stale data for a data? Vertical vs horizontal scaling? SQL Flexible, unstructured, or rapidly
few seconds? evolving? → NoSQL

Section 02 — SQL Databases: Full Deep Dive

[Link] 2/11
04/04/2026, 12:57 SQL vs NoSQL — Part 1: The Big Picture & SQL Deep Dive What SQL is, how relational databases work internally,
ACID, JOINs, normalisation, and when to choose SQL
 1 What Is SQL?

DEFINITION

SQL stands for Structured Query Language. It is both the language used to interact with
relational databases and the informal name for relational databases themselves.

SQL databases organise data into tables (like spreadsheets) with rows and columns. Each table
represents one type of entity (Users, Orders, Products), and relationships between tables are defined
using primary and foreign keys.

EXCEL ANALOGY

A SQL database is like a well-organised Excel workbook with multiple sheets. Each sheet has
predefined column headers. Every row must follow the same structure. You can cross reference
data between sheets using a shared ID column.

SQL DATABASE — TABLES, ROWS, COLUMNS & RELATIONSHIPS

USERS TABLE Each row = one user. Each column = one field. ORDERS TABLE

id (PK) name email city 1 Amit amit@.. Mumbai 2 Priya priya@.. id (PK) user_id (FK) total status 101 1 5000 done 102 3 2500
Delhi 3 Sara sara@.. BLR FOREIGN KEY pending 103 1 8000 done
orders.user_id → [Link]

HOW RELATIONSHIPS WORK

• PRIMARY KEY (PK): Unique identifier for each row. [Link] = 1, 2, 3 — no duplicates.
• FOREIGN KEY (FK): Links to PK in another table. orders.user_id references [Link].
• This lets you JOIN: "Show me all orders for Amit" → match user_id=1 across both tables.

2 Core RDBMS Concepts You Must Know

Tables, Primary Keys & Foreign Keys

[Link] 3/11
04/04/2026, 12:57 SQL vs NoSQL — Part 1: The Big Picture & SQL Deep Dive CORE CONCEPTS — TABLES, ROWS,

COLUMNS

TABLE = The fundamental building block.

Has rows (records) and columns (fields).
Example: 'Users' table with columns: id, name, email, created_at

PRIMARY KEY (PK) = Unique identifier for every row.

 No two rows can share the same PK. Guarantees uniqueness.
Example: user_id = 1, 2, 3...

FOREIGN KEY (FK) = A column that references the PK of another table.

This is how RELATIONSHIPS are enforced.
Example: orders.user_id references [Link]

INDEX = Data structure that speeds up retrieval (B+ Tree).

Without it: scan every row. With it: jump directly.
(You already mastered this in the Indexing chapters!)

Normalisation — Eliminating Redundancy

WHAT IS NORMALISATION?

The process of organising tables to eliminate data redundancy and ensure data integrity. You break
one big table into multiple smaller, related tables so that each piece of data is stored exactly once.

WHY NORMALISE? — BEFORE AND AFTER

BEFORE NORMALISATION (one big denormalised table):

┌────┬───────┬────────────┬───────────────┬───────────┐
│ id │ name │ email │ order_total │ product │
├────┼───────┼────────────┼───────────────┼───────────┤
│ 1 │ Amit │ amit@.. │ 5000 │ Laptop │
│ 1 │ Amit │ amit@.. │ 8000 │ Phone │ ← Amit's data
│ 2 │ Priya │ priya@.. │ 2500 │ Headphone │ DUPLICATED!
└────┴───────┴────────────┴───────────────┴───────────┘
Problem: Amit's name and email are stored TWICE. If email changes,
you must update EVERY row — miss one and data is inconsistent.

AFTER NORMALISATION (split into related tables):

Users: Orders:

[Link] 4/11
04/04/2026, 12:57 SQL vs NoSQL — Part 1: The Big Picture & SQL Deep Dive ┌────┬───────┬─────────┐

┌─────┬─────────┬───────┬───────────┐
│ id │ name │ email │ │ id │ user_id │ total │ product │
├────┼───────┼─────────┤ ├─────┼─────────┼───────┼───────────┤
│ 1 │ Amit │ amit@.. │ │ 101 │ 1 │ 5000 │ Laptop │
│ 2 │ Priya │ priya@..│ │ 102 │ 2 │ 2500 │ Headphone │
└────┴───────┴─────────┘ │ 103 │ 1 │ 8000 │ Phone │
└─────┴─────────┴───────┴───────────┘
Amit's data stored ONCE. Orders reference via FK (user_id=1).
Change email? Update ONE row. Always consistent.
 JOINs — SQL's Superpower

A JOIN is an SQL operation that combines rows from two or more tables based on a related
column. This is what makes relational databases powerful — you split data across tables
(normalisation) and then recombine it on the fly with JOINs.

THE FOUR TYPES OF JOINS

SELECT [Link], [Link], [Link]

FROM users u
JOIN orders o ON [Link] = o.user_id;
- Result: Amit|5000|Laptop, Amit|8000|Phone, Priya|2500|Headphone

INNER JOIN: Only rows that have a match in BOTH tables.

Users with orders + their orders. Users without orders excluded.

LEFT JOIN: ALL rows from left table + matching rows from right.
ALL users + their orders (if any). Users without orders show NULL.

RIGHT JOIN: ALL rows from right table + matching rows from left.
ALL orders + their user info. Orphan orders show NULL user.

FULL JOIN: ALL rows from BOTH tables. Non-matching show NULL.
Every user and every order, matched where possible.

FOUR TYPES OF JOINS — VISUAL

AB

INNER JOIN Only matching rows LEFT JOIN RIGHT JOIN FULL JOIN
All left + matching right All right + matching left All from both tables

[Link] 5/11
3
04/04/2026, 12:57 SQL vs NoSQL — Part 1: The Big Picture & SQL Deep Dive ACID — The Guarantee

SQL Databases Provide

WHAT IS ACID?
 ACID is a set of four properties that guarantee database transactions are processed reliably. This
is the cornerstone of SQL databases and the reason banks, payment systems, and anything
involving money use SQL.

ACID — THE FOUR GUARANTEES

A = ATOMICITY
"All or nothing." A transaction either completes fully or not at all. If transferring ₹5000 from Account
A to B:
Step 1: Debit A by 5000
Step 2: Credit B by 5000
If Step 2 fails, Step 1 is ROLLED BACK. Money doesn't vanish.

C = CONSISTENCY
"Always valid state." The database moves from one valid state to another. Constraints (foreign keys,
unique, NOT NULL) are always enforced. You can never have an order pointing to a user_id that doesn't
exist.

I = ISOLATION
"Transactions don't interfere." Multiple transactions running at
the same time don't see each other's partial changes.
User A transferring money and User B checking balance simultaneously — B sees either the state
BEFORE or AFTER, never a half-done state.

D = DURABILITY
"Once committed, it's permanent." After a transaction commits,
the data survives even if the server crashes or loses power.
The database writes to disk (write-ahead log) before confirming.

ACID — WHY BANKS USE SQL

A C I D
Atomicity Consistency Isolation Durability
All or nothing. Always valid state. Constraints always No interference. Once committed,
Debit+Credit both happen, or enforced. No bad data. Concurrent txns don't see partial it's permanent. Survives crashes &
neither. changes. power loss.

[Link] 6/11
04/04/2026, 12:57 SQL vs NoSQL — Part 1: The Big Picture & SQL Deep Dive

BANK TRANSFER ANALOGY

You transfer ₹5000 from your account to a friend. Atomicity ensures the money is both debited from
you AND credited to your friend — or neither happens. Consistency ensures total money in the
system doesn't change. Isolation ensures if someone checks your balance mid transfer, they see the
old or new balance, not a weird in-between. Durability ensures once the transfer is confirmed, it stays
even if the bank's server crashes.
 4
The SQL Query Language — What You Can Do

SQL is divided into four sub-languages, each for a different type of operation: TYPE

FULL NAME COMMANDS PURPOSE

DDL Data Definition Language (tables, columns)

CREATE , ALTER , DROP Define and modify database structure

DML Data Manipulation Language SELECT , INSERT , UPDATE , DELETE Read and write actual data

DCL Data Control Language GRANT , REVOKE Manage permissions and access control

TCL Transaction Control Language COMMIT , ROLLBACK , SAVEPOINT Manage transactions (ACID guarantees)

QUICK EXAMPLES OF EACH SUB-LANGUAGE

DDL — Define structure:

CREATE TABLE users (id INT PRIMARY KEY, name VARCHAR(100));
ALTER TABLE users ADD COLUMN email VARCHAR(255);
DROP TABLE users;

DML — Read/write data:

SELECT * FROM users WHERE city = 'Mumbai';
INSERT INTO users (name, email) VALUES ('Amit', 'amit@[Link]');
UPDATE users SET email = 'new@[Link]' WHERE id = 1;
DELETE FROM users WHERE id = 5;

DCL — Permissions:
[Link] 7/11
04/04/2026, 12:57 SQL vs NoSQL — Part 1: The Big Picture & SQL Deep Dive GRANT SELECT ON users TO

readonly_user;
REVOKE DELETE ON users FROM junior_dev;

TCL — Transactions:
BEGIN;
UPDATE accounts SET balance = balance - 5000 WHERE id = 1; UPDATE accounts
SET balance = balance + 5000 WHERE id = 2; COMMIT; - both succeed together
(atomicity!)
- or ROLLBACK; if something goes wrong

5 Popular SQL Databases

 DATABASE LICENCE BEST FOR WHO USES IT

PostgreSQL Open Source

MySQL Open Source

Complex queries, extensibility,
JSON support

Web apps, LAMP stack, e

commerce
Instagram, Apple, Twitch

Facebook (early), WordPress, Airbnb

SQL Server Commercial Enterprise Windows systems, BI Stack Overflow, Dell Oracle DB Commercial Mission-

critical enterprise systems Banks, telecom, government

SQLite Open Source Firefox

Embedded, mobile, local storage Android apps, browsers,

6 SQL Scaling — Vertical vs Horizontal

THE SCALING CHALLENGE

SQL databases traditionally scale vertically — you make the single server bigger (more RAM, faster
CPU, bigger disk). This works until you hit hardware limits. Horizontal scaling (adding more servers)
is possible but complex for SQL because JOINs across servers are hard and maintaining ACID
across distributed nodes is expensive.

[Link] 8/11
04/04/2026, 12:57 SQL vs NoSQL — Part 1: The Big Picture & SQL Deep Dive VERTICAL VS HORIZONTAL SCALING

VERTICAL SCALING ("Scale Up"):

One server → make it BIGGER
4 GB RAM → 32 GB RAM → 256 GB RAM → 1 TB RAM
✓ Simple — no code changes
✓ ACID stays simple (one machine)
 ✗ Has a ceiling — can't buy infinite hardware
✗ Single point of failure
✗ Expensive (doubling RAM = more than double cost)

HORIZONTAL SCALING ("Scale Out"):

One server → add MORE servers
1 server → 5 servers → 50 servers → 500 servers
✓ No ceiling — keep adding machines
✓ Redundancy (one fails, others handle it)
✗ Complex for SQL — JOINs across servers?
✗ ACID across distributed nodes = very hard
✗ Need sharding, replication, consensus protocols

SQL databases CAN scale horizontally (sharding, read replicas), but it's much harder than with
NoSQL databases that were BUILT for it.

VERTICAL SCALING vs HORIZONTAL SCALING

↑
BIGGER Server
More RAM, CPU, Disk Srv 1 Srv 2 Srv 3 Srv 4 Srv 5 Srv 6 →keep adding!
VERTICAL (Scale Up) Server HORIZONTAL (Scale Out)

7 SQL Advantages & Disadvantages

✅ SQL ADVANTAGES ❌ SQL DISADVANTAGES

→ Strong ACID transaction guarantees → Vertical scaling hits hardware limits

→ Rich JOINs & aggregation query power entry Horizontal sharding is complex to

→ Enforced schema prevents bad data → implement

[Link] 9/11
04/04/2026, 12:57 SQL vs NoSQL — Part 1: The Big Picture & SQL Deep Dive

→ documentation →
40+ years of tools, ecosystem, Not ideal for unstructured data (logs,
→ Schema changes need careful → Easy to find skilled developers
JSON)
migrations

→ Excellent for complex reporting & BI → Can bottleneck at very high write loads

→→ integrity → JOINs across huge tables are slow →

Well-understood backup & recovery Fixed schema slows rapid prototyping →

Foreign key constraints ensure referential strategies Poor fit for hierarchical or graph data
 8 When to Choose SQL — Decision Framework

CHOOSE SQL WHEN

1. Your data has clear relationships (users have orders, orders have items)
2. You need ACID transactions (money transfers, inventory, bookings)
3. Your schema is well-defined and stable (not changing every week)
4. You need complex queries — JOINs, aggregations, GROUP BY, subqueries
5. Data integrity is more important than raw write speed
6. You need reporting and analytics on structured data

CLASSIC SQL USE CASES

Banking & Financial Systems → ACID is non-negotiable. Money can't vanish. E-Commerce →
Products, orders, payments — clear relationships. Healthcare → Patient records need strict
integrity.
ERP / CRM Systems → Complex business rules, many entity relationships. Inventory Management →
Stock counts must be exact (no overselling). Booking Systems (Hotels/Flights) → Double-booking =
disaster. Need isolation. Reporting & BI Dashboards → Complex JOINs and aggregations across
tables.

�� SQL Deep Dive — Quick Revision

[Link] 10/11
04/04/2026, 12:57 SQL vs NoSQL — Part 1: The Big Picture & SQL Deep Dive WHAT IS SQL?

Structured Query Language. Data in tables (rows + columns). Relationships via primary/foreign keys.
Schema enforced — every row must match the structure.

CORE CONCEPTS

Table = rows + columns. PK = unique row ID. FK = link to another table's PK. Index = fast lookup (B+
Tree). Normalisation = eliminate redundancy.

JOINS — SQL'S SUPERPOWER

INNER = matching rows only. LEFT = all left + matching right. RIGHT = all right + matching left. FULL
= everything from both.

ACID GUARANTEES

Atomicity (all or nothing) · Consistency (always valid) · Isolation (no interference) · Durability
(permanent after commit)
 SQL SUB-LANGUAGES

DDL = CREATE/ALTER/DROP (structure). DML = SELECT/INSERT/UPDATE/DELETE (data). DCL =

GRANT/REVOKE (permissions). TCL = COMMIT/ROLLBACK (transactions).

SCALING

Vertical (scale up) = bigger server. Simple but hits ceiling.

Horizontal (scale out) = more servers. Possible but complex for SQL (JOINs across nodes,
distributed ACID).

WHEN TO CHOOSE SQL

Clear relationships · ACID needed · Stable schema · Complex queries/JOINs · Data integrity
critical · Reporting/BI

POPULAR DATABASES

PostgreSQL (complex queries, JSON). MySQL (web apps, LAMP). SQL Server (enterprise/BI).
Oracle (mission-critical). SQLite (embedded/mobile).

[Link] 11/11
04/04/2026, 12:57 SQL vs NoSQL — Part 2: NoSQL Deep Dive & ACID vs BASE

SQL V S NOSQL — PA RT 2

NoSQL Deep Dive & ACID vs BASE

All 6 types of NoSQL databases explained, when to use each, eventual consistency, CAP
theorem, and the ACID vs BASE battle

Section 03 — NoSQL Databases: Full Deep Dive

 1
6 types of NoSQL, what each stores, how they work internally, and when to pick which

What Is NoSQL?

DEFINITION

NoSQL stands for "Not Only SQL". It is a broad family of databases that don't use the relational
table model. Instead of one single design (tables + SQL), NoSQL offers multiple data models,
each optimised for a specific type of data or workload.

The common thread: they sacrifice some consistency guarantees in exchange for horizontal
scalability, schema flexibility, and high availability.

STORAGE TOOLS ANALOGY

If SQL is one type of organised filing cabinet (rigid but reliable), NoSQL is a collection of very
different storage tools — a backpack, a whiteboard, a spreadsheet, a corkboard — each perfect for a
specific purpose. You pick the tool that fits your data, not force your data into one shape.

2 The 6 Types of NoSQL Databases

INTERVIEW CRITICAL

[Link] 1/15
04/04/2026, 12:57 SQL vs NoSQL — Part 2: NoSQL Deep Dive & ACID vs BASE You must know all 6 types, what they store, and when
to use them. Each type is designed for a fundamentally different kind of data. Interviewers will ask:
"What type of NoSQL would you use for X?" — and you need to pick the right one with reasoning.

THE 6 TYPES OF NoSQL DATABASES

① KEY-VALUE ③ WIDE-COLUMN NoSQL 6 Types

Giant hash map. GET/SET. Flexible cols per row. Massive writes. Cassandra, HBase ④ GRAPH
Redis, DynamoDB Nodes + edges. Relationships. Neo4j, Neptune

② DOCUMENT ⑤ IN-MEMORY
JSON docs, flexible schema. RAM-first. Microsecond latency. Redis, Memcached
MongoDB, Firestore

TYPE 1
⑥ TIME-SERIES Timestamped data. Metrics. InfluxDB, Prometheus

Key-Value Store — The Simplest NoSQL (Giant Hash Table)

GET key, SET key value, DELETE key — nothing more, nothing less

WHAT IS IT?

Stores data as simple key → value pairs. The key is a unique identifier. The value can be
anything: a string, number, JSON blob, or binary data. The database doesn't care what's
inside the value — it just stores and retrieves it by key. Operations are extremely fast: GET ,
SET , DELETE .

KEY-VALUE STORE — HOW IT LOOKS

KEY VALUE
─────────────────────────────────────────────────
"user:1001:session" → "abc123xyz789"
"user:1001:cart" → {"items": [{"id": 42, "qty": 2}]}
"rate_limit:ip:1.2.3" → "47"
"cache:product:555" → "{name: 'Laptop', price: 75000}"
"leaderboard:game1" → [{"user": "amit", "score": 9500}, ...]

Operations:

[Link] 2/15
04/04/2026, 12:57 SQL vs NoSQL — Part 2: NoSQL Deep Dive & ACID vs BASE SET "user:1001:session"

"abc123xyz789" → store
GET "user:1001:session" → retrieve → "abc123xyz789"
DELETE "user:1001:session" → remove
TTL "user:1001:session" 3600 → auto-expire in 1 hour

Use Cases: Session storage, user preferences, shopping carts, rate limiting, real-time
leaderboards, caching results of expensive queries.

Examples: Redis (most popular), Amazon DynamoDB, Azure Cosmos DB, Memcached.

WHY IT'S SO FAST

It's essentially a giant hash table. hash(key) → memory location → value. O(1)
lookups. Redis stores everything in RAM — reads take microseconds, not
milliseconds. This is why it's used as a caching layer in front of slower databases.
 TYPE 2

Document Store — JSON Documents, Flexible & Powerful

Self-contained documents with nested data — no schema required

WHAT IS IT?

Stores data as self-contained documents — usually JSON or BSON format. Each

document can have different fields (schema-less). Documents can be nested (e.g., an Order
document containing an array of Product sub-documents). Ideal when your data is naturally
hierarchical.

DOCUMENT STORE — A USER PROFILE DOCUMENT (MONGODB STYLE)

/ In SQL: this would need 3 tables (users, addresses, preferences)

/ In MongoDB: ONE document holds everything

{
"_id": "user_1001",
"name": "Amit Sharma",
"email": "amit@[Link]",
"age": 28,
"address": { ← nested object
"city": "Mumbai",
"state": "Maharashtra",
"pin": "400001"

[Link] 3/15
04/04/2026, 12:57 SQL vs NoSQL — Part 2: NoSQL Deep Dive & ACID vs BASE },

"orders": [ ← nested array

{"id": "ord_501", "total": 5000, "status": "delivered"},
{"id": "ord_502", "total": 2500, "status": "pending"}
],
"preferences": { ← flexible fields
"theme": "dark",
"notifications": true
},
"tags": ["premium", "early_adopter"] ← arrays
}

No JOIN needed — everything for this user is in ONE document.

Different users can have DIFFERENT fields (schema-less).

Use Cases: User profiles, product catalogs, blog posts with comments, content
management systems, e-commerce catalogs, configuration storage.

Examples: MongoDB (most popular), CouchDB, Amazon DocumentDB, Firestore

(Firebase).
 SQL VS DOCUMENT — THE TRADE-OFF

SQL: User data split across users, addresses, orders tables → needs JOINs →
data always consistent → but slower for "get everything about this user."

Document: Everything for a user in ONE document → no JOINs → blazing fast

reads → but if you update the address format, you must update each document
individually.

TYPE 3

Wide-Column Store — Tables but with Flexible Columns per Row

Massive writes, time-series, billions of events — built for distribution

WHAT IS IT?

Similar to SQL tables but each row can have completely different columns. Data is stored in
column families rather than rows. Optimised for writing massive amounts of data and for
time-series queries ("give me all records between time A and time B"). Built for distribution
across many nodes.

WIDE-COLUMN VS SQL TABLE

[Link] 4/15
04/04/2026, 12:57 SQL vs NoSQL — Part 2: NoSQL Deep Dive & ACID vs BASE

SQL TABLE (every row has SAME columns):

┌─────────┬───────┬────────┬───────┬────────┐
│ user_id │ name │ email │ phone │ city │
├─────────┼───────┼────────┼───────┼────────┤
│ 1 │ Amit │ a@.. │ 9876 │ Mumbai │
│ 2 │ Priya │ p@.. │ 8765 │ Delhi │ ← every row, same columns
│ 3 │ Sara │ s@.. │ 7654 │ BLR │
└─────────┴───────┴────────┴───────┴────────┘

WIDE-COLUMN (each row can have DIFFERENT columns):

Row Key │ Columns (vary per row!)
──────────┼──────────────────────────────────────────
user:1 │ name="Amit" email="a@.." phone="9876" city="Mumbai"
user:2 │ name="Priya" email="p@.." age="25" ← no phone, has age user:3 │
name="Sara" twitter="@sara" ← only 2 columns!

Each row is identified by a ROW KEY.

Columns are grouped into COLUMN FAMILIES.
Rows don't need to share the same columns.
 Use Cases: Time-series data, IoT sensor logs, activity feeds, messaging platforms,
telemetry, analytics, write-heavy workloads with billions of events.

Examples: Apache Cassandra (most popular), HBase, Google Bigtable (the original), Azure
Table Storage.

WHY CASSANDRA HANDLES MASSIVE WRITES

Cassandra uses a Log-Structured Merge Tree (LSM Tree) — it writes sequentially to an in-
memory table, then flushes to disk. No random I/O on writes. This lets it handle millions of
writes per second across distributed nodes. Instagram uses Cassandra to store billions of
user activity events.

TYPE 4

Graph Database — Nodes, Edges, and Relationships

When relationships ARE the data — social networks, fraud detection, recommendations

WHAT IS IT?

Data is stored as nodes (entities) and edges (relationships between entities). Ideal
when the relationships between data are just as important as the data itself.

[Link] 5/15
04/04/2026, 12:57 SQL vs NoSQL — Part 2: NoSQL Deep Dive & ACID vs BASE Traversing millions of connections is extremely
fast — impossible in SQL without many expensive JOIN operations.

GRAPH DATABASE — SOCIAL NETWORK EXAMPLE

[Amit] ──FOLLOWS──→ [Priya]

││
FOLLOWS FOLLOWS
↓↓
[Sara] ──FOLLOWS──→ [Rahul]
│
LIKES
↓
[Post #42]

NODES = Amit, Priya, Sara, Rahul, Post #42 (entities)

EDGES = FOLLOWS, LIKES (relationships)

Query: "Find all friends-of-friends of Amit"

SQL: Multiple self-JOINs on a huge table → SLOW
Graph: Traverse 2 edges from Amit → INSTANT
 Query: "Find everyone who follows someone that Amit follows"
SQL: Complex subquery with JOINs → gets exponentially slower
Graph: Traverse edges → stays fast regardless of data size

Use Cases: Social networks (friend-of-friend), fraud detection (suspicious patterns),

recommendation engines, knowledge graphs, network topology, access control systems.

Examples: Neo4j (most popular), Amazon Neptune, ArangoDB, TigerGraph.

WHY NOT JUST USE SQL FOR RELATIONSHIPS?

SQL handles relationships via JOINs. For "friends of friends," you need a self-JOIN. For
"friends of friends of friends," you need another JOIN. Each level of depth adds
another JOIN — and SQL JOINs get exponentially slower. Graph databases store
relationships as first-class citizens — traversing 5 levels deep is just as fast as
traversing 1 level. That's the fundamental difference.

[Link] 6/15
04/04/2026, 12:57 SQL vs NoSQL — Part 2: NoSQL Deep Dive & ACID vs BASE TYPE 5

In-Memory Database — RAM-First, Microsecond Latency

Eliminates disk I/O entirely — orders of magnitude faster

WHAT IS IT?

Data is stored primarily in RAM (memory) rather than on disk. This eliminates disk I/O, making reads and writes
orders of magnitude faster. Trade-off: data is volatile (lost on power failure) unless persistence mechanisms are
enabled. Used as a speed layer in front of other databases.

IN-MEMORY AS A CACHING LAYER

Without cache:
App → Database (disk) → 5-50ms per read

With in-memory cache (Redis):

App → Redis (RAM) → 0.1ms per read ← 50-500x FASTER
↓ (cache miss)
Database (disk) → fill cache → next time: from RAM

TYPICAL PATTERN:
1. App checks Redis first (cache hit? → return instantly)
2. Cache miss → query actual database
3. Store result in Redis with TTL (e.g., expire in 5 minutes)
4. Next request for same data → served from RAM
This is called CACHE-ASIDE or READ-THROUGH pattern.

Use Cases: Caching (most common), session management, real-time leaderboards, pub/sub messaging, rate
limiting, temporary data that changes rapidly.

Examples: Redis (also a key-value store), Memcached, Apache Ignite, Hazelcast, SAP HANA.

PERSISTENCE IN REDIS

Redis supports two persistence modes so data isn't lost on restart:

RDB (snapshotting): Periodic point-in-time snapshots to disk.
AOF (Append-Only File): Logs every write command to disk. More durable but slightly

[Link] 7/15

04/04/2026, 12:57 SQL vs NoSQL — Part 2: NoSQL Deep Dive & ACID vs BASE slower.

Most production Redis setups use both for safety.

TYPE 6

Time-Series Database — Optimised for Timestamped Data

Millions of data points per second, chronological queries, automatic downsampling

WHAT IS IT?

Specialised database for storing data points indexed by time. Data is naturally ordered
chronologically. Highly optimised for ingesting millions of events per second, and for queries
like "average CPU over the last 5 minutes" or "all readings between 9am and 10am."

TIME-SERIES DATA — WHAT IT LOOKS LIKE

METRIC: server_cpu_usage
┌─────────────────────┬───────────┬──────────┐
│ timestamp │ server_id │ cpu_% │
├─────────────────────┼───────────┼──────────┤
│ 2024-03-15 09:00:01 │ srv-01 │ 45.2 │
│ 2024-03-15 09:00:01 │ srv-02 │ 72.8 │
│ 2024-03-15 09:00:02 │ srv-01 │ 47.1 │
│ 2024-03-15 09:00:02 │ srv-02 │ 71.5 │
│ 2024-03-15 09:00:03 │ srv-01 │ 52.3 │
│ ...millions more... │ │ │
└─────────────────────┴───────────┴──────────┘
 SPECIAL FEATURES:
• Automatic downsampling: keep per-second data for 1 day,
per-minute data for 1 month, per-hour data for 1 year
• Built-in aggregation: AVG, MIN, MAX, PERCENTILE over time windows
• Compression: timestamps compress extremely well (sequential)
• Retention policies: auto-delete data older than X days

Use Cases: DevOps monitoring (CPU, memory metrics), IoT sensor data, financial tick data,
application performance monitoring (APM), smart grid data, weather data.

[Link] 8/15
04/04/2026, 12:57 SQL vs NoSQL — Part 2: NoSQL Deep Dive & ACID vs BASE Examples: InfluxDB, Prometheus (+
Grafana), TimescaleDB, Amazon Timestream, Graphite.

3
All 6 NoSQL Types — Quick Comparison

TYPE DATA MODEL BEST FORTOP EXAMPLE

Key-Value key → value (any blob) Caching, sessions, rate limiting Redis Document JSON/BSON

documents User profiles, catalogs, CMS MongoDB

Wide Column IoT, time-series, massive writes Cassandra

Row key + flexible columns

Graph Nodes + edges Social networks, fraud, recommendations Neo4j

In-Memory RAM-first (any model) Caching layer, real-time data Redis Time-Series Timestamped data points

Monitoring, IoT metrics, APM InfluxDB

4
NoSQL Advantages & Disadvantages
 ✅ NOSQL ADVANTAGES ❌ NOSQL DISADVANTAGES

→ Horizontal scaling — add nodes easily tolerance → Each type optimised for its Eventual consistency — stale reads

→ Schema-less — iterate without use case possible

migrations → Built for massive write →→→ No native JOINs — complex queries are
hard
throughput → High availability and fault
Limited multi-document transaction support
→ Multi-region distribution is built-in → No enforced schema = risky data quality

[Link] 9/15
04/04/2026, 12:57 SQL vs NoSQL — Part 2: NoSQL Deep Dive & ACID vs BASE

→ data → Less mature tooling and BI integration

Great for unstructured/semi-structured
→ Each type needs specialised knowledge

→ Often faster for simple key-based lookups distributed issues is complex

→ Higher risk of data inconsistency bugs → Debugging

Section 04 — ACID vs BASE: The Consistency Battle

Two philosophies: correctness vs availability. Understanding both is essential for database
interviews.

1
ACID — The SQL Standard (Recap with Depth)

ACID is a set of four properties that guarantee database transactions are processed reliably and that
data is never left in a corrupted state, even during system failures. Every major SQL database is
ACID-compliant by default.

ACID — THE FOUR GUARANTEES (IN DEPTH)

A = ATOMICITY — "All or Nothing"

Every operation in a transaction completes fully or not at all.
Bank transfer: debit Account A AND credit Account B — both succeed
or both are rolled back. Money NEVER vanishes between accounts.

C = CONSISTENCY — "Always Valid"

Transaction must bring DB from one valid state to another.
Rule: balance = 0. A transaction making balance negative is REJECTED.
Foreign key: can't have order pointing to non-existent user.
 I = ISOLATION — "Don't Interfere"
Concurrent transactions execute as if sequential.
Two users buying the LAST concert ticket simultaneously:
Only one succeeds. The other sees "already sold" — not a partial state.

D = DURABILITY — "Never Forgotten"

Once committed, data survives crashes and power failures.

[Link] 10/15
04/04/2026, 12:57 SQL vs NoSQL — Part 2: NoSQL Deep Dive & ACID vs BASE Written to disk (WAL — Write-Ahead

Log) before confirming.

Server crashes 1ms after commit? Data is NOT lost.

2
BASE — The NoSQL Philosophy

WHAT IS BASE?

BASE is the alternative philosophy adopted by most distributed NoSQL databases. It relaxes
ACID's strict consistency guarantees to achieve higher availability and easier horizontal scaling.

BASE stands for: Basically Available, Soft State, Eventually Consistent.

BASE — THE THREE PROPERTIES EXPLAINED

BA = BASICALLY AVAILABLE
The system guarantees SOME response to every request,
even during partial failures. The response might be stale
or from a replica, but the system never goes completely down.

Example: During Amazon's Black Friday sale, the site stays UP

and accepts orders — even if some product counts are slightly off.

S = SOFT STATE
The system's state may change over time even WITHOUT new inputs,
as data propagates between nodes asynchronously.

Example: A social media post you just liked shows 100 likes to you,
but still shows 99 to someone in another region — the update
hasn't propagated to their node yet.

E = EVENTUALLY CONSISTENT
Given enough time with no new updates, ALL nodes will converge
to the same data. Consistency is guaranteed EVENTUALLY — not
 immediately.

Example: After editing your Twitter profile, it appears updated

to you instantly, but a friend in another country might see the
OLD bio for a few seconds until the change propagates.

[Link] 11/15
04/04/2026, 12:57 SQL vs NoSQL — Part 2: NoSQL Deep Dive & ACID vs BASE EVENTUAL CONSISTENCY — HOW IT

WORKS

T = 0s
Write arrivesNode A
✓ UpdatedNode B
✓ Updated
✗ StaleNode C

✗ Stale

T = 2s
PropagatingNode A
✓ UpdatedNode B

⟳ SyncingNode C

3 ACID vs BASE — Head-to-Head

Reads from B or C return STALE data B is catching up, C still behind
✗ Stale

T = 5s ALL nodes now have same data ✓ This is "Eventually Consistent"

Converged!Node A
✓ UpdatedNode B

✓ UpdatedNode C

DIMENSION ACID (SQL) BASE (NOSQL) Consistency Strong — immediate, always Eventual — converges over

time Availability May sacrifice for consistency Prioritised above consistency Transactions Multi-row, multi-

table ACID Single-document or limited Failure Handling Roll back entire transaction Resolve conflicts after the

fact Scalability Harder to scale horizontally Built for horizontal scale Data Integrity Enforced by the database

Enforced by application code Latency Higher (coordination overhead) Lower (no sync coordination) Use

When Financial, inventory, medical Social, analytics, IoT, sessions Example DBs MySQL, PostgreSQL,

Oracle Cassandra, DynamoDB, MongoDB

ACID vs BASE — THE SPECTRUM

ACID Vertical scaling Banks, payments BASE

Most systems use a MIX of both!
Strong consistency Lower availability Eventual consistency High availability
Horizontal scaling Social, IoT, analytics
 KEY INTERVIEW INSIGHT — AMAZON'S TWO SYSTEMS

[Link] 12/15
04/04/2026, 12:57 SQL vs NoSQL — Part 2: NoSQL Deep Dive & ACID vs BASE Amazon's shopping cart uses BASE/eventual
consistency intentionally. They would rather show a slightly stale cart than fail to load the page.

But Amazon's payment processing uses ACID/strong consistency — you can NEVER have
eventual consistency for money. The payment must either go through completely or not at all.

Real systems use BOTH. The same company picks ACID for critical paths and BASE for
everything else. This is the nuanced answer interviewers want.

4
CAP Theorem — The Distributed Systems Trade-off

CAP THEOREM (BREWER'S THEOREM)

In a distributed system, you can only guarantee two out of three properties at the same time:

C = Consistency — every read gets the most recent write

A = Availability — every request gets a response (not an error)
P = Partition Tolerance — the system works even if network between nodes breaks

Since network partitions always happen in distributed systems, you really choose between CP
(consistency + partition tolerance) or AP (availability + partition tolerance).

CAP THEOREM — PICK TWO (but P is mandatory)

C
Consistency

AP Systems CP Systems
Cassandra, DynamoDB, CouchDB MongoDB, HBase, Redis
Available but may serve stale data Consistent but may reject reads

A P
Availability Partition Tol.

CA = only works if no network issues (single node / not distributed)

CAP IN PRACTICE — WHICH DATABASES CHOOSE WHAT?

CP (Consistency + Partition Tolerance):

[Link] 13/15
04/04/2026, 12:57 SQL vs NoSQL — Part 2: NoSQL Deep Dive & ACID vs BASE → System may become
 UNAVAILABLE during network partition
→ But data is always CONSISTENT when you can read it
→ Examples: MongoDB (default), HBase, Redis Cluster, Zookeeper

AP (Availability + Partition Tolerance):

→ System ALWAYS responds, even during network issues
→ But response might contain STALE data
→ Examples: Cassandra, DynamoDB, CouchDB, Riak

CA (Consistency + Availability):
→ Only possible on SINGLE node (no partitions)
→ Traditional single-server SQL databases (PostgreSQL, MySQL)
→ Not truly distributed — if that node fails, everything fails

REALITY: Most modern databases let you TUNE the trade-off.

Cassandra: adjustable consistency (ONE, QUORUM, ALL)
MongoDB: configurable read/write concern
DynamoDB: strongly consistent reads available (at higher cost)

�� NoSQL & ACID vs BASE — Quick Revision

WHAT IS NOSQL?

"Not Only SQL." Multiple data models. Sacrifices consistency for scalability, flexibility,
availability.

6 NOSQL TYPES

1. Key-Value: hash map. GET/SET. Redis, DynamoDB. → sessions, cache, rate

limiting 2. Document: JSON docs. MongoDB, Firestore. → user profiles, catalogs,
CMS

3. Wide-Column: flexible cols/row. Cassandra, HBase. → IoT, time-series, massive

writes 4. Graph: nodes + edges. Neo4j, Neptune. → social networks, fraud,
recommendations 5. In-Memory: RAM-first. Redis, Memcached. → caching layer, real-
time, leaderboards 6. Time-Series: timestamped. InfluxDB, Prometheus. → monitoring,
IoT metrics, APM

ACID (SQL)

Atomicity (all or nothing) · Consistency (always valid) · Isolation (no interference) · Durability
(permanent). Used for: money, inventory, medical.

BASE (NOSQL)

Basically Available (always responds) · Soft state (may change without input) · Eventually
consistent (converges over time). Used for: social, analytics, IoT.
 [Link] 14/15
04/04/2026, 12:57 SQL vs NoSQL — Part 2: NoSQL Deep Dive & ACID vs BASE CAP THEOREM

Pick 2 of 3: Consistency, Availability, Partition tolerance. P is mandatory in distributed systems.

Choose CP (MongoDB, HBase) or AP (Cassandra, DynamoDB).

AMAZON'S APPROACH (INTERVIEW GOLD)

Shopping cart = BASE (show stale cart, never fail to load page).
Payment processing = ACID (money must never be inconsistent).
Real systems use BOTH. Pick per use case.

[Link] 15/15
04/04/2026, 12:57 SQL vs NoSQL — Part 3: Comparison, Sharding, Replication & Advanced Topics
 SQL V S NOSQL — PA RT 3

Comparison, Sharding, Replication &

Advanced Topics
Complete side-by-side comparison, the golden decision framework, normalisation vs
denormalisation, sharding strategies, federation, replication vs mirroring

Section 06 — SQL vs NoSQL: Complete Side-by-Side

The master comparison table, the golden decision framework, and the correct interview
answer

1
The Complete Comparison Table

DIMENSION SQL NOSQL INTERVIEW TIP

Data Model Tables, rows, columns Documents, KV, Graph, Column Structured → SQL; Flexible → NoSQL

Schema Fixed, enforced upfront NoSQL

Dynamic, schema-less Stable domain → SQL; Evolving →

Scalability Vertical (scale up) Horizontal (scale out) <10TB → SQL; >100TB → NoSQL

Consistency Strong (ACID) Eventual (BASE) Financial → SQL; Social → NoSQL

Transactions Full multi-table ACID Single-doc / limited Multi-step ops → SQL

Query Power Rich JOINs, aggregations Relationships Foreign keys, JOINs Embedded / denormalised
Simple key lookups Ad-hoc analytics → SQL Complex relations → SQL

[Link] 1/12
04/04/2026, 12:57 SQL vs NoSQL — Part 3: Comparison, Sharding, Replication & Advanced Topics DIMENSION SQL NOSQL INTERVIEW

TIP

Write Speed Moderate (ACID overhead) High (no coordination) High write volume → NoSQL

Read Speed Fast with indexes + JOINs NoSQL Tolerance

Fast for key-based reads
Complex reads → SQL; Simple → Fault Schema Change
Single point of failure Distributed, fault Migration needed Just start writing new iteration → NoSQL
tolerant fields
High availability → NoSQL Rapid

Best Use Cases Banking, e commerce, ERP Match to consistency need

Social, IoT, analytics, cache

2 The Golden Decision Framework

CHOOSE SQL WHEN... CHOOSE NOSQL WHEN...

→→→→

You need ACID transactions

across multiple records

Your data has complex

relationships requiring JOINs

Dataset fits under ~10TB and

write load < 100K writes/sec

You need ad-hoc analytics and

complex reporting
→→→→

You need to scale beyond

100TB or 1M+ writes/second

Can tolerate eventual

consistency for your domain

Schema evolves rapidly or

varies per record

Primary queries are simple key-

based lookups
→ stable →
Domain is well-defined and schema is Data is naturally document-shaped or
→ High availability across regions is critical → Team has strong SQL expertise
graph-shaped

→ requirements mandate strong → sensor readings

Compliance or audit Append-only data: logs, events,
consistency

[Link] 2/12
04/04/2026, 12:57 SQL vs NoSQL — Part 3: Comparison, Sharding, Replication & Advanced Topics SQL OR NoSQL? — DECISION

FLOWCHART

→ SQL → SQL → SQL Need ACID NO YES

→ NoSQL
transactions?

Scale > 100TB or

1M+ writes/sec?

YES YES NO NO

Complex JOINs Most large systems use BOTH!

& relationships? SQL for transactions + NoSQL for
caching/analytics/scale

THE CORRECT INTERVIEW ANSWER

"Which is better — SQL or NoSQL?"

The answer is ALWAYS: "It depends on the consistency requirements, expected scale, team
expertise, and nature of the data." Show nuance. Most large systems use BOTH — SQL for
transactional paths, NoSQL for scale, caching, and analytics.

Section 07 — Advanced Topics

Normalisation vs Denormalisation, Sharding strategies, Database Federation

1
Normalisation vs Denormalisation
 Two opposite database design strategies. Understanding when to use each is a classic
interview question.

Normalisation — Eliminate Redundancy

WHAT IS IT?
[Link] 3/12
04/04/2026, 12:57 SQL vs NoSQL — Part 3: Comparison, Sharding, Replication & Advanced Topics Normalisation organises tables to reduce
data duplication. The same piece of information is stored only once, and other tables reference it.
There are formal levels called Normal Forms.

NORMAL
FORMRULE EXAMPLE

1NF Atomic values only. No repeating groups or arrays. Denormalisation — Optimise for Read Speed WHAT

2NF Meets 1NF + no partial dependency on composite key IS IT?

3NF Meets 2NF + no transitive dependency (non key → Split "phone1, phone2" into separate rows

non-key)
Customer name depends on customer_id alone

City shouldn't depend on zip_code in Orders table

Denormalisation intentionally adds redundancy by combining tables or pre-computing JOINs. This

speeds up reads (no expensive JOINs at query time) at the cost of slower writes and potential data
inconsistency if updates are missed.

NORMALISED (SQL-STYLE) DENORMALISED (NOSQL-STYLE)

→ Data stored once, referenced by FK → Writes are → Data duplicated for fast access → Writes slower

fast (update one place) → Reads are slower (JOIN (update many copies) → Reads are lightning fast (no

needed) → Best for OLTP: banking, inventory → JOIN) → Best for OLAP / high read workloads →

Less storage space used More storage space used

→ Data always consistent → Risk of inconsistency if updates missed

NORMALISED VS DENORMALISED — VISUAL EXAMPLE

 NORMALISED (3 tables, no duplication):
Users: {id:1, name:"Amit", email:"amit@.."}
Orders: {id:101, user_id:1, total:5000} ← references Users via FK
Products: {id:50, name:"Laptop", price:5000}

[Link] 4/12
04/04/2026, 12:57 SQL vs NoSQL — Part 3: Comparison, Sharding, Replication & Advanced Topics To show "Amit bought Laptop
for 5000" → JOIN all 3 tables. Change Amit's name? Update ONE row in Users. Done.

DENORMALISED (1 document, data duplicated):

{
order_id: 101,
user_name: "Amit", ← duplicated from Users
user_email: "amit@..", ← duplicated from Users
product_name: "Laptop", ← duplicated from Products
total: 5000
}

To show "Amit bought Laptop for 5000" → read ONE document. No JOIN! Change
Amit's name? Must update EVERY document that has his name.

NORMALISED vs DENORMALISED — THE TRADE-OFF

NORMALISED 2
Users Orders Products
Sharding — Horizontal Scaling for SQL
Data stored ONCE
JOINs needed to combine
✓ Consistent ✗ Slower reads WHAT IS SHARDING?

DENORMALISED
One Big Document / Table

Data DUPLICATED
No JOINs — read one doc
✓ Fast reads ✗ Risk of inconsistency

Sharding splits a large table across multiple database servers. Each server holds a subset (shard)
of the data. This is how SQL databases can scale horizontally despite not being built for it natively.

SHARDING — SPLITTING DATA ACROSS SERVERS

 BEFORE SHARDING (single server, hitting limits):
┌────────────────────────────────────┐
│ Server 1: ALL 100 million users │ ← too much for one server
└────────────────────────────────────┘

AFTER SHARDING (split across 4 servers):

[Link] 5/12
04/04/2026, 12:57 SQL vs NoSQL — Part 3: Comparison, Sharding, Replication & Advanced Topics ┌──────────────────┐

┌──────────────────┐
│ Shard 1: Users │ │ Shard 2: Users │
│ A-F (25M users) │ │ G-M (25M users) │
└──────────────────┘ └──────────────────┘
┌──────────────────┐ ┌──────────────────┐
│ Shard 3: Users │ │ Shard 4: Users │
│ N-S (25M users) │ │ T-Z (25M users) │
└──────────────────┘ └──────────────────┘

Each shard is a separate database server.

Application needs to know which shard to query.

Three Sharding Strategies

STRATEGY HOW IT WORKS PROS CONS

Range Shard by value ranges. Simple. Range uneven (more users start with
Based Users A-M → Server 1; N-Z → queries stay on one shard. "S" than "X")
Server 2
Even distribution. No hot Range queries span ALL
Hash hash(shard_key) % shards. shards. Adding shards =
Based num_shards → determines reshuffling data.
shard
Most flexible. Can move Lookup table itself can be

Directory Based A lookup table maps each individual keys. bottleneck + single point of
key → specific shard Hot shards if distribution is failure.

RANGE-BASED THE DOWNSIDE OF SHARDING ✗ Range queries = all shards

✗ Adding shards = reshuffle
A-M → Shard 1 N-Z → Shard 2
THREE SHARDING STRATEGIES

✓ Simple logic
✓ Range queries local HASH-BASED
DIRECTORY-BASED lookup_table[key] → shard
✗ Hot shards risk
hash(key) % N → shard
✗ Uneven distribution
✓ Most flexible
✓ Can move individual keys ✗ Lookup table = bottleneck ✗
✓ Even distribution
✓ No hot shards Single point of failure

Cross-shard JOINs are very slow or impossible — you can't easily JOIN data that lives on
different servers.
 ACID transactions across shards require expensive two-phase commit (2PC) protocol — all shards
must agree before committing.

[Link] 6/12
04/04/2026, 12:57 SQL vs NoSQL — Part 3: Comparison, Sharding, Replication & Advanced Topics This is why sharding is a last resort for
SQL databases — try vertical scaling, read replicas, and caching first.

3
Database Federation

WHAT IS FEDERATION?

Federation (also called functional partitioning) splits different databases by function — a Users DB,
an Orders DB, a Products DB — each on separate servers. A federation layer sits on top and
provides a unified query interface.

DATABASE FEDERATION — HOW IT WORKS

┌─────────────────────┐
│ FEDERATION LAYER │ ← routes queries to correct DB │ (unified endpoint) │
└──────┬──────┬───────┘
│││
┌────────────┤ │ ├────────────┐
↓↓↓↓↓
┌──────────────┐ ┌──────────────┐ ┌──────────────┐
│ Users DB │ │ Orders DB │ │ Products DB │
│ (PostgreSQL) │ │ (MySQL) │ │ (MongoDB) │
└──────────────┘ └──────────────┘ └──────────────┘

BENEFITS:
• Transparency: App queries one endpoint, federation routes to correct DB • Heterogeneity: Each DB can
be a different type!
• Autonomy: Each database is self-contained, updated independently
• Natural fit for MICROSERVICES (each service has its own DB)

TRADE-OFFS:
• Cross-database JOINs are slow
• Global transactions across federated DBs are complex
• Added complexity in the federation layer
 [Link] 7/12
04/04/2026, 12:57 SQL vs NoSQL — Part 3: Comparison, Sharding, Replication & Advanced Topics

Section 08 — Data Replication vs Data Mirroring

Copies of data for safety, availability, and performance — knowing the difference is a strong
interview signal

1
Data Replication

WHAT IS REPLICATION?

Copying data from one location (the primary/source) to one or more other locations
(replicas/secondaries). Replication can be synchronous or asynchronous, and creates
multiple copies across different servers or geographic locations.

MASS EMAIL ANALOGY

Replication is like sending a mass email. You write one email and it gets delivered to 10 people.
Each recipient gets their own copy. The delivery can be instant (sync) or delayed by a few seconds
(async). You control who gets updates and when.

Three Types of Replication

TYPE HOW IT WORKS TRADE-OFF EXAMPLE

Synchronous Primary waits for ALL replicas to confirm ACK to client

Zero data loss Google Spanner, PG synchronous standby
before

Asynchronous Primary confirms background MySQL read replicas, MongoDB replica

Fast but risk of data loss if primary crashes
immediately, replicates in sets

Semi Primary waits for at least ONE Middle ground — reduced risk, MySQL semi-sync plugin (used
Synchronous replica to confirm reasonable speed at Facebook)

[Link] 8/12
04/04/2026, 12:57 SQL vs NoSQL — Part 3: Comparison, Sharding, Replication & Advanced Topics THREE REPLICATION MODES
SYNCHRONOUS Replica 1 ⟳ Replica 2 ⟳
Use Cases for Replication 5 KEY
Primary Confirms IMMEDIATELY
✓ Fast writes
✗ Risk of data loss
USE CASES FOR REPLICATION MySQL, MongoDB
Replica 1 ✓ Replica 2 ✓ SEMI-SYNCHRONOUS Primary

Waits for ALL to ACK

✓ Zero data loss
Replica 1 ✓ Replica 2 ⟳
✗ Slower writes
Google Spanner
1. READ SCALING Waits for at least ONE
ASYNCHRONOUS ✓ Reduced risk
~ Moderate speed
Primary MySQL (Facebook)

Add read replicas to distribute read traffic.

Primary handles ALL writes; replicas handle reads.

2. GEOGRAPHIC DISTRIBUTION
Replicate to data centres in Asia, Europe, US
for low-latency local reads.

3. DISASTER RECOVERY
If primary data centre fails, promote a replica to primary.

4. REPORTING & ANALYTICS

Run expensive analytics queries on a replica
without impacting production.

5. ZERO-DOWNTIME MIGRATIONS
Replicate to new server, then switch over without downtime.

2 Data Mirroring

WHAT IS MIRRORING?

Creating an exact, real-time replica of a database. Mirroring is almost always synchronous. Every
write to the primary is simultaneously written to the mirror. The mirror is an exact,

[Link] 9/12
04/04/2026, 12:57 SQL vs NoSQL — Part 3: Comparison, Sharding, Replication & Advanced Topics current duplicate — not a slightly-
behind copy. Used specifically for high availability and instant failover.
 SHADOW ANALOGY

Mirroring is like a live shadow. When a dancer moves, the shadow moves at exactly the same
instant. There is never a gap between the two. If the dancer "fails," the shadow can immediately
take over without any missed frames.

HOW DATABASE MIRRORING WORKS

ARCHITECTURE:
┌──────────────┐ ┌──────────────┐
│ PRINCIPAL │ ──────→ │ MIRROR │
│ (Primary) │ sync │ (Standby) │
│ Server │ write │ Server │
└──────────────┘ └──────────────┘
↑↑
│ ┌──────────────┘
││
┌─────────────────┐
│ WITNESS │ ← optional: automates failover decisions
│ (monitors both) │ if principal fails, witness + mirror
└─────────────────┘ vote to promote mirror to principal

FLOW:
1. Client sends write to Principal
2. Principal writes AND sends to Mirror simultaneously
3. Mirror applies transaction, sends ACK
4. ONLY after both have the data → client gets confirmation
5. If Principal fails → Witness detects → Mirror promoted → zero downtime

Use Cases for Mirroring

WHEN TO USE MIRRORING

• HIGH AVAILABILITY (HA): Instant automatic failover. Zero data loss.

• DISASTER RECOVERY: Mirror in separate building/city. Primary burns? Mirror takes over • FINANCIAL TRADING:
Every millisecond matters. Transactions cannot be lost. • HEALTHCARE RECORDS: Patient data must NEVER be lost.
Live duplicate always exists.

[Link] 10/12
04/04/2026, 12:57 SQL vs NoSQL — Part 3: Comparison, Sharding, Replication & Advanced Topics

3
Replication vs Mirroring — Head-to-Head
 DIMENSION REPLICATION MIRRORING

Synchronisation Sync OR Async (flexible) Always synchronous (real-time) Number of Copies Multiple (1 to

many) Usually one mirror (1-to-1) Primary Purpose Availability, load balancing, analytics High availability,

instant failover Data Lag Async: seconds of lag possible No lag — always current

Performance Impact mirror ACK)

Async: minimal; Sync: write latency Higher write latency (wait for

Flexibility Very flexible — many configurations More rigid — exact replication only Failover Manual promotion

usually Automatic failover (with witness)

Use Cases Read scaling, reporting, geo distribution Consistency Async replicas may serve stale reads
HA systems, financial, critical data Mirror always has same data

Cost Lower per replica (async = cheap) Higher (synchronous = double write cost)

INTERVIEW SUMMARY

Replication = copies for scale + backup (can be async). Flexible, many copies, may have lag.
Mirroring = always-on exact copy for zero-downtime failover (always sync). Rigid, 1-to-1, zero lag.

Most enterprise systems use BOTH: mirroring for HA failover + async replication for read
scaling.

[Link] 11/12
04/04/2026, 12:57 SQL vs NoSQL — Part 3: Comparison, Sharding, Replication & Advanced Topics �� SQL vs NoSQL

Part 3 — Quick Revision

SQL VS NOSQL DECISION

ACID needed? → SQL. Complex JOINs? → SQL. >100TB or 1M+ writes/sec? → NoSQL.
Schema evolving? → NoSQL. Most systems use BOTH.

NORMALISATION VS DENORMALISATION
 Normalised: no duplication, FK references, JOINs needed, consistent. Best for OLTP (banking).
Denormalised: data duplicated, no JOINs, fast reads, risk of inconsistency. Best for OLAP / reads.

SHARDING (HORIZONTAL SCALING FOR SQL)

Range: shard by value range (simple but hot shards). Hash: even distribution (no range queries).
Directory: lookup table (flexible but bottleneck).

Cross-shard JOINs are slow. Distributed ACID needs 2PC. Sharding = last resort — try vertical
scaling, replicas, caching first.

FEDERATION

Split by function: Users DB, Orders DB, Products DB on separate servers. Federation layer
routes queries. Natural fit for microservices. Cross-DB JOINs are complex.

REPLICATION

Sync: wait for all replicas → zero data loss, slower. Async: confirm immediately → fast, risk
of loss. Semi-sync: wait for ONE → middle ground.

Use cases: read scaling, geo-distribution, disaster recovery, analytics offloading.

MIRRORING

Always synchronous, 1-to-1, zero lag. Principal + Mirror + Witness. Instant automatic failover. Used
for HA, financial, healthcare.

REPLICATION VS MIRRORING

Replication = flexible, many copies, can be async. For scale + backup.

Mirroring = rigid, exact copy, always sync. For zero-downtime failover.
Enterprise systems use both: mirroring for HA + async replication for read scaling.

[Link] 12/12
04/04/2026, 13:01 SQL vs NoSQL — Part 4: Real-World Case Studies

SQL V S NOSQL — PA RT 4

Real-World Case Studies

Instagram, Discord, Twitter, Uber — how real companies made database decisions under
pressure, and what you can learn for interviews
 WHY CASE STUDIES MATTER IN INTERVIEWS

Interviewers don't just want to know SQL vs NoSQL theory — they want to see that you
understand how these decisions play out in real systems at real scale. Referencing actual
company decisions shows depth and credibility. These four case studies cover the four most
common architectural patterns.

CASE STUDY 1

Instagram — PostgreSQL + Cassandra

The gold-standard "polyglot persistence" example — using BOTH SQL and NoSQL, each for what it does
best

The Problem

Instagram launched with PostgreSQL and scaled to 300 million daily active users. Two very different
data needs emerged:

DATA NEEDING STRONG CONSISTENCY DATA NEEDING MASSIVE THROUGHPUT

→ User profiles and account data → → Activity feeds (your home feed) → Likes (millions

Follower/following relationships per second globally)

→
Photo metadata (which photo belongs to
whom)
→ Notifications

→ Eventual consistency is fine here

→ in the wrong account → seconds? No problem
You can NEVER show a photo Seeing 99 likes vs 100 for 2
→ Follower counts must be accurate → Needs millions of writes/second

[Link] 1/11
04/04/2026, 13:01 SQL vs NoSQL — Part 4: Real-World Case Studies

→ Needs ACID transactions + complex queries

What They Did — The Architecture

INSTAGRAM'S DATABASE ARCHITECTURE

Instagram Application Layer

ACID required Scale required

PostgreSQL (SQL)
Why This Design Works — Deep Analysis
Apache Cassandra (NoSQL)
What it stores:
• User profiles & account data
• Follower/following relationships
What it stores:
• Photo metadata • Activity feeds (home feed)
• Likes (millions per second)
Why PostgreSQL: • Notifications
• ACID transactions (critical data)
• Complex JOINs for relationships
Why Cassandra:
• Sharded by user_id for locality • Massive write throughput
Strong consistency guaranteed • Horizontal scaling (100s of nodes)
• Simple key-based lookups
Eventual consistency acceptable

INSTAGRAM'S DATA SPLIT — WHY EACH DATABASE WAS CHOSEN

PostgreSQL for Users/Relationships/Photos:

─────────────────────────────────────────────
1. ACID: "Show Amit's photo on Priya's profile" — MUST be accurate.
Wrong photo on wrong account = critical bug.
2. JOINs: "Get all followers of user X who also follow user Y"
→ requires multi-table JOIN on follower relationships.
3. Sharding by user_id: Each user's data stays on ONE shard.
All of Amit's data (profile + photos + followers) on same server.
ACID works because all related data is local to one shard.

Cassandra for Feeds/Likes/Notifications:

─────────────────────────────────────────────
1. WRITE VOLUME: When a celebrity posts, millions of followers'
feeds need updating. 1 post → millions of feed writes.
PostgreSQL can't handle this write volume.
2. EVENTUAL CONSISTENCY: If your feed shows a post 2 seconds
late, nobody notices. If a like count shows 999 instead of
1000 for 3 seconds — no problem.
3. SIMPLE ACCESS: "Get feed for user X" = single key lookup.
 No JOINs needed. Cassandra excels at this.

[Link] 2/11
04/04/2026, 13:01 SQL vs NoSQL — Part 4: Real-World Case Studies 4. HORIZONTAL SCALE: Add Cassandra

nodes as users grow.

No resharding complexity like PostgreSQL.

INTERVIEW TAKEAWAY

Instagram is the gold-standard polyglot persistence example. The lesson: don't force one database to
do everything. Use SQL where consistency matters and NoSQL where scale matters. When
interviewers ask "Would you use SQL or NoSQL?", the best answer is often: "I'd use BOTH — here's
how I'd split the data."

CASE STUDY 2

Discord — MongoDB → Cassandra

A NoSQL-to-NoSQL migration — proving that even within NoSQL, choosing the wrong TYPE is costly

The Problem

Discord initially chose MongoDB for message storage — attracted by its flexible JSON schema and
easy development setup. But as they scaled past 100 million messages per channel, serious problems
emerged:

WHY MONGODB FAILED FOR MESSAGES WHAT MESSAGES ACTUALLY NEED

→ Read latency degraded badly at scale → Append-only (never updated after posting)

→ wasn't optimised for time-series → ("messages in channel X

MongoDB's storage engine Always queried by time range
data between Y and Z")

→
Messages are always queried by TIME
range, not by flexible fields
→ Predictable low latency at massive scale

→ Simple key-based access pattern

→ Sharding became operationally nightmarish → Hot partitions What They Did — Migration to Cassandra
on popular channels → Write-optimised for high throughput

[Link] 3/11
04/04/2026, 13:01 SQL vs NoSQL — Part 4: Real-World Case Studies DISCORD'S DATABASE MIGRATION

BEFORE: MongoDB Flexible JSON docs migrated to massive scale Partition: (channel_id, bucket) ✅ Right NoSQL
NOT optimised for time-series Read latency grew with scale AFTER: Apache Cassandra Write-optimised LSM Tree type!
engine Built-in time-series capabilities Predictable latency at
Sharding was a nightmare ❌ Wrong NoSQL type!

DISCORD'S CASSANDRA DATA MODEL FOR MESSAGES

PARTITION KEY: (channel_id, bucket)

→ All messages in a channel land in the same partition
→ "bucket" splits very active channels into time-based chunks
→ Prevents any single partition from growing too large

CLUSTERING KEY: message_id (contains timestamp)

→ Messages within a partition are sorted chronologically
→ "Get messages from 9am to 10am" = sequential read within partition
→ No scanning, no filtering — data is PRE-SORTED

QUERY PATTERN:
SELECT * FROM messages
WHERE channel_id = 'abc123'
AND bucket = '2024-03-15'
AND message_id > [timestamp_for_9am]
AND message_id < [timestamp_for_10am];
→ Reads EXACTLY the messages needed, nothing more.

WHY CASSANDRA FITS:

• Messages are APPEND-ONLY (never updated after posting)
• Queries are ALWAYS time-range based
• Cassandra's LSM Tree = sequential writes to disk (fast!)
• No JOINs needed — just "give me messages for this channel"

THE KEY LESSON

NoSQL is not one thing. MongoDB's document flexibility was NOT the right NoSQL type for time-
 series messages. Cassandra's write-optimised wide-column model was. The lesson: don't just
choose "NoSQL" — choose the right TYPE of NoSQL for your specific access pattern.

[Link] 4/11
04/04/2026, 13:01 SQL vs NoSQL — Part 4: Real-World Case Studies CASE STUDY 3

Twitter (X) — MySQL → Manhattan (Custom NoSQL)

Building a custom distributed key-value store because no existing solution fit the scale

The Problem

Twitter started with Ruby on Rails and MySQL for all data, including tweets. As Twitter grew from
thousands to hundreds of millions of users, MySQL couldn't handle the volume:

WHY MYSQL COULDN'T HANDLE TWEETS AT SCALE

TWEET CHARACTERISTICS:
• 300K+ tweets posted per hour
• Tweets are IMMUTABLE (never edited after posting — at that time)
• Accessed primarily by tweet_id or user_id
• Need enormous write throughput + read throughput
• Timeline generation requires reading tweets from 100s of followed users

THE "FAIL WHALE":

Twitter's infamous downtime error page was largely caused by
DATABASE BOTTLENECKS. MySQL on a single server simply couldn't:
→ Ingest 300K+ tweets/hour
→ Generate timelines for millions of users simultaneously
→ Handle the read amplification of "fan-out on read"

WHY MySQL STRUGGLED:

1. VERTICAL SCALING LIMIT — single server maxed out
2. WRITE CONTENTION — all tweets go to one table, one server
3. TIMELINE QUERY — "get latest tweets from 500 people I follow"
= 500 separate queries or a massive JOIN on a huge table
4. SHARDING COMPLEXITY — sharding MySQL breaks JOINs

What They Did — The Architecture

 [Link] 5/11
04/04/2026, 13:01 SQL vs NoSQL — Part 4: Real-World Case Studies TWITTER'S SPLIT ARCHITECTURE

Twitter Application

MySQL (kept for ACID data) Manhattan (Custom NoSQL KV Store)

What stays in MySQL: What moved to Manhattan:

• User accounts & authentication • Tweets (immutable, key-based access)
• User settings & preferences • Timelines (pre-computed feeds)
• Follow relationships • Social graph data
Why MySQL stays: Why custom-built:
• ACID for authentication (critical) • Distributed key-value store
• Username uniqueness must be enforced Strong consistency non- • 300K+ tweets/hour throughput
negotiable Eventual consistency acceptable

TWITTER'S TECHNOLOGY STACK (ADDITIONAL COMPONENTS)

ADDITIONAL SYSTEMS TWITTER BUILT:

Apache Kafka:
→ Event streaming between services
→ When a tweet is posted, Kafka distributes the event to:
- Timeline service (add to followers' feeds)
- Notification service (notify mentioned users)
- Search index service (make tweet searchable)
- Analytics pipeline (count impressions)

Redis:
→ Caching layer for timelines
→ "Get timeline for user X" → check Redis first (O(1))
→ Cache miss → query Manhattan → store in Redis

TIMELINE GENERATION (Fan-out):

Celebrity posts a tweet
→ Celebrity has 50 million followers
→ Fan-out-on-write: pre-compute and push to each follower's timeline
→ For celebrities: fan-out-on-read instead (query at read time)
→ Hybrid approach balances write cost vs read latency

INTERVIEW TAKEAWAY

Twitter kept ACID where it mattered (accounts, authentication — you can NEVER have
two users with the same username) and adopted NoSQL for high-volume, read-
optimised data. The fan-out problem (1 tweet → millions of timeline updates) is a
classic system design question. Know the trade-off between fan-out-on-write vs fan-
out-on-read.

[Link] 6/11
04/04/2026, 13:01 SQL vs NoSQL — Part 4: Real-World Case Studies CASE STUDY 4
 Uber — MySQL + Schemaless (Custom NoSQL on SQL) The
most creative solution — getting NoSQL benefits WITHOUT leaving MySQL

The Problem

Uber had a unique constraint that the other companies didn't face:

UBER'S CONFLICTING REQUIREMENTS

THEY NEEDED NoSQL BECAUSE:

→ Billions of trips with EVOLVING schema
(new fields added constantly: surge pricing, pool trips, Uber Eats)
→ Schema changes on billions of rows in SQL = migration nightmare
→ Massive scale (millions of trips/day)

BUT THEY ALSO NEEDED SQL BECAUSE:

→ MySQL's PROVEN operational reliability
→ ACID transactions for trip data (billing, payments)
→ Existing backup tooling and monitoring
→ Team expertise was in MySQL
→ Running a SEPARATE NoSQL cluster adds:
- Operational complexity
- New expertise requirements
- Additional infrastructure cost

THE DILEMMA: How do you get NoSQL's flexibility

WITHOUT leaving MySQL's reliability?

What They Did — Schemaless: NoSQL on Top of MySQL

[Link] 7/11
04/04/2026, 13:01 SQL vs NoSQL — Part 4: Real-World Case Studies

UBER'S "SCHEMALESS" — NoSQL LAYER ON TOP OF MySQL

Uber Application
 "Schemaless" — NoSQL Abstraction Layer
Accepts JSON documents · Key-based lookups · Schema-free writes

stores as blob

MySQL (underneath — actual storage)

JSON docs stored in MySQL's BLOB columns · Sharded by entity_id
Retains: ACID transactions · Backup tools · Operational maturity

HOW SCHEMALESS WORKS — DEEP DIVE

THE CLEVER TRICK:

1. APPLICATION writes JSON documents to "Schemaless" API:

{
"trip_id": "trip_abc123",
"rider": "user_456",
"driver": "driver_789",
"fare": 350,
"surge_multiplier": 1.5, ← new field! no migration needed
"pool_riders": [...] ← another new field! just add it
}

2. SCHEMALESS LAYER receives the JSON and stores it in MySQL:

INSERT INTO trips (entity_id, blob_data, created_at)
VALUES ('trip_abc123', '{"rider":"user_456",...}', NOW());

The JSON goes into a BLOB column — MySQL doesn't care about
the schema of the JSON. You can add any field anytime.

3. ACCESS PATTERN is key-based:

SELECT blob_data FROM trips WHERE entity_id = 'trip_abc123';
→ Returns the full JSON document.
→ No JOINs needed (all trip data in one document).

4. SHARDING by entity_id:
Each trip's data stays on ONE MySQL shard.
ACID transactions work because data is local to one shard.

WHAT YOU GET:

✓ NoSQL schema flexibility (add fields without migration)
✓ NoSQL key-based access pattern (fast lookups)
✓ MySQL ACID guarantees (billing/payments are safe)

[Link] 8/11
04/04/2026, 13:01 SQL vs NoSQL — Part 4: Real-World Case Studies ✓ MySQL operational tooling (backups,

monitoring, team expertise)

✓ No need to learn/operate a separate NoSQL system

WHY THIS IS BRILLIANT

Uber realised their access pattern was always key-based ("get trip by trip_id"). They never
 needed SQL's JOINs for trip data. So the lack of JOIN power wasn't a constraint. By storing
JSON in MySQL blob columns, they got NoSQL's flexibility while keeping MySQL's battle tested
reliability. This shows that you can achieve NoSQL benefits on SQL if your access patterns allow
it.

INTERVIEW TAKEAWAY

Uber's approach teaches a critical lesson: always match the solution to the actual query patterns,
not the hype. They didn't need a fancy NoSQL cluster — they needed schema flexibility + key
lookups + ACID. MySQL with a JSON blob layer gave them all three. This is the kind of nuanced
thinking interviewers love.

5
All 4 Case Studies — Comparison Table

COMPANY PATTERN SQL FOR NOSQL FOR KEY LESSON

Instagram SQL + NoSQL side- Users, relationships, photos Feeds, likes, Use BOTH — each for what it
by-side (ACID) notifications (scale) does best

Discord NoSQL → different NoSQL messages

— MongoDB → Cassandra for Right TYPE of NoSQL matters hugely

Twitter SQL + custom NoSQL SQL Tweets, timelines (Manhattan ACID where it
Accounts, auth (ACID)
built KV) matters, NoSQL for volume

Match solution to access

MySQL underneath for ACID +
Uber NoSQL layer ON TOP of Schemaless JSON layer on top patterns, not hype
ops

[Link] 9/11
04/04/2026, 13:01 SQL vs NoSQL — Part 4: Real-World Case Studies FOUR ARCHITECTURAL PATTERNS FOR

SQL + NoSQL

① POLYGLOT ② MIGRATION Type selection matters! ④ NoSQL ON SQL (Uber)

(Instagram) (Discord) ③ CUSTOM BUILD (Twitter)
NoSQL API layer

NoSQL A → NoSQL B
SQL NoSQL SQL + Custom KV
MySQL underneath

Side by side Build your own when

Wrong type → right type Same family,
Different data to different nothing off-the-shelf NoSQL flexibility
different databases data model fits the scale + SQL reliability
Best of both worlds
Most common pattern Only at massive scale
 �� Real-World Case Studies — Quick Revision

INSTAGRAM (POLYGLOT PERSISTENCE)

PostgreSQL for users, relationships, photos (ACID needed). Cassandra for feeds, likes,
notifications (scale needed). PG sharded by user_id. Gold-standard example of using BOTH.

DISCORD (NOSQL TYPE MIGRATION)

MongoDB → Cassandra for messages. MongoDB's document model was wrong for time-series
messages. Cassandra's LSM Tree + wide-column model = perfect fit. Partition key:
(channel_id, bucket) . Lesson: right TYPE of NoSQL matters.

TWITTER (SQL + CUSTOM NOSQL)

MySQL kept for accounts/auth (ACID). Manhattan (custom KV store) built for tweets/timelines. Plus
Kafka for event streaming, Redis for caching. Lesson: ACID where it matters, NoSQL for volume.

UBER (NOSQL LAYER ON SQL)

Schemaless = NoSQL API layer on top of MySQL. JSON in blob columns. Schema flexibility + ACID
+ MySQL operational maturity. Lesson: match solution to access patterns, not hype.

FOUR PATTERNS TO KNOW

① Polyglot (SQL + NoSQL side by side) — most common

② Migration (wrong NoSQL → right NoSQL) — type matters

③ Custom Build (build your own at extreme scale)

④ NoSQL on SQL (abstraction layer over MySQL) — creative hybrid

UNIVERSAL LESSON

[Link] 10/11
04/04/2026, 13:01 SQL vs NoSQL — Part 4: Real-World Case Studies

No company uses just one database at scale. They all use SQL for critical data + NoSQL for scale.
The interview answer is always: "It depends — and real systems use both."
 [Link] 11/11
04/04/2026, 13:14 MongoDB — Complete Deep Dive from Zero to Expert

NOSQL D EEP D I V E — DOC UMENT STORE

MongoDB — Complete Deep Dive

From zero to expert: what it is, how it works internally, querying, scaling, trade-offs, and real-world examples with
architecture decisions

1 What Is MongoDB? (Starting From Zero)

DEFINITION

MongoDB is a document-oriented NoSQL database. Instead of storing data in tables with rows and columns (like
SQL), MongoDB stores data as JSON-like documents (technically BSON — Binary JSON). Each document is a self-
contained unit that can have any structure — no two documents need to look the same.

THE FILING CABINET ANALOGY

Imagine SQL as a spreadsheet — every row has the same columns, strictly enforced. Now imagine MongoDB as a
folder of papers. Each paper (document) can have different information on it. One paper has name + email + phone.
Another has name + address + age. No one forces them to match. You just put whatever you need on each paper
and file it in a folder (collection).

SQL vs MongoDB — Terminology Mapping

SQL CONCEPT MONGODB EQUIVALENT EXAMPLE

Database Database myapp_db

Table Collection users collection

Row Document One user's data (JSON object) Column Field name , email , age

Primary Key _id field Auto-generated ObjectId [Link] 1/20

04/04/2026, 13:14 MongoDB — Complete Deep Dive from Zero to Expert SQL CONCEPT MONGODB EQUIVALENT

EXAMPLE

JOIN Embedding or $lookup Nest related data inside document Schema (enforced) Schema (optional) Can

validate, but not required

 SQL TABLE vs MONGODB COLLECTION

Document 1:
SQL TABLE — Fixed Schema { name:"Amit", email:"amit@..", age:28,
address:{city:"Mumbai"}, phone:"9876" }
id name email age 1 Amit amit@.. 28 2 Priya priya@.. 25 3 Sara sara@.. 30

Every row MUST have same columns Document 2:

Can't add "phone" to just one row { name:"Priya", email:"priya@..", age:25,
tags:["premium"], preferences:{dark:true} }
Schema change = ALTER TABLE migration Related data → separate
table + JOIN Document 3:
{ name:"Sara", age:30, bio:"Developer" }
Addresses, orders = separate tables
MONGODB COLLECTION — Flexible Each document can have DIFFERENT fields! Related data EMBEDDED
inside document

2 How MongoDB Stores Data — Documents & BSON

What Is a Document?

A document is a JSON-like object that holds all the data for one entity. Unlike SQL where you split
data across multiple tables and JOIN them, MongoDB encourages you to embed related data inside
one document.

A COMPLETE MONGODB DOCUMENT — USER PROFILE

{
"_id": ObjectId("507f1f77bcf86cd799439011"), / auto-generated unique ID "name": "Amit Sharma",
"email": "amit@[Link]",
"age": 28,
"created_at": ISODate("2024-03-15T10:30:00Z"),

/ NESTED OBJECT — in SQL this would be a separate "addresses" table

"address": {
"street": "MG Road",
"city": "Mumbai",

[Link] 2/20
04/04/2026, 13:14 MongoDB — Complete Deep Dive from Zero to Expert

"state": "Maharashtra",
"pin": "400001"
},

/ ARRAY OF OBJECTS — in SQL this would be a separate "orders" table

"orders": [
{ "order_id": "ORD-001", "total": 5000, "status": "delivered" },
{ "order_id": "ORD-002", "total": 2500, "status": "pending" }
],

/ FLEXIBLE FIELDS — other documents may not have these

"preferences": { "theme": "dark", "notifications": true },
"tags": ["premium", "early_adopter"],
 "referral_code": "AMT2024"
}

WHY EMBEDDING IS POWERFUL

In SQL, to show "Amit's profile with his address and orders," you'd need to JOIN 3 tables (users
+ addresses + orders). That's 3 separate disk lookups and a computation to merge them.

In MongoDB, ALL of Amit's data is in ONE document. One read from disk → you have
everything. This is why MongoDB is often faster for "get everything about this entity"
queries.

What Is BSON?

BSON = BINARY JSON

MongoDB doesn't actually store raw JSON text. It converts JSON to BSON (Binary JSON) — a
binary-encoded format that's more efficient to store and faster to parse. BSON also supports
additional data types that JSON doesn't have: Date , ObjectId , Decimal128 , Binary , RegExp .

You write JSON in your application code, but MongoDB stores BSON on disk. This is
transparent — you never need to think about the conversion.

The _id Field — MongoDB's Primary Key

OBJECTID — HOW MONGODB AUTO-GENERATES UNIQUE IDS

[Link] 3/20
04/04/2026, 13:14 MongoDB — Complete Deep Dive from Zero to Expert

Every document MUST have an "_id" field. If you don't provide one,
MongoDB auto-generates an ObjectId:

ObjectId("507f1f77bcf86cd799439011")
├────┤├──┤├──┤├────────┤
││││
│ │ │ └─ Counter (3 bytes) — unique within same second │ │ └────── Process ID (2 bytes, since
v3.4: random)
│ └─────────── Machine ID (3 bytes, since v3.4: random)
└───────────────── Timestamp (4 bytes) — seconds since epoch

WHY ObjectId is clever:

• Contains a TIMESTAMP — you can extract when a document was created
• Globally unique — no coordination needed between servers
 • Roughly SORTED by creation time — helps with index performance
• 12 bytes — smaller than a UUID (16 bytes)

3 Internal Architecture — How MongoDB Works Under the Hood

WiredTiger Storage Engine

WHAT IS A STORAGE ENGINE?

The storage engine is the component that manages how data is stored on disk and read into
memory. It's the lowest layer — everything else (queries, indexes, replication) sits on top of it. Since
MongoDB 3.2, the default storage engine is WiredTiger.

WIREDTIGER — KEY FEATURES

1. DOCUMENT-LEVEL LOCKING
In older MongoDB (MMAPv1 engine): collection-level lock.
One writer blocks ALL other writers on the same collection.

WiredTiger: DOCUMENT-LEVEL concurrency control.

Two users can update two DIFFERENT documents simultaneously.
Much higher write throughput for concurrent workloads.

2. COMPRESSION
WiredTiger compresses data on disk using:

[Link] 4/20
04/04/2026, 13:14 MongoDB — Complete Deep Dive from Zero to Expert

• Snappy (default): fast compression, moderate ratio

• Zlib: better ratio, slower
• Zstd: best balance (MongoDB 4.2+)
Typically 50-70% space savings vs uncompressed.

3. WRITE-AHEAD LOG (JOURNAL)

Every write is first logged to a JOURNAL (write-ahead log).
If the server crashes, the journal replays to recover data.
This gives DURABILITY (the "D" in ACID).

4. IN-MEMORY CACHE
WiredTiger maintains a cache in RAM (default: 50% of RAM).
Hot data stays in memory → reads don't hit disk.
Eviction policies manage what stays and what gets flushed.

5. B-TREE INDEXES
 Indexes are stored as B-Tree structures (similar to B+ Tree).
Default index on _id is created automatically.
Additional indexes must be created manually.

MONGODB INTERNAL ARCHITECTURE

Your Application ([Link], Python, Java)

MongoDB Driver (converts queries to wire protocol)

MongoDB Query Engine

Query Parser → Optimizer → Execution Plan → Cursor

WiredTiger Storage Engine

Cache (RAM) | B-Tree Indexes | Compression | Journal (WAL) | Concurrency Control

Disk — BSON data files + Index files + Journal logs

How a Write Works — Step by Step

WRITE PATH — WHAT HAPPENS WHEN YOU INSERT A DOCUMENT

[Link]({ name: "Amit", email: "amit@[Link]", age: 28 })

Step 1: DRIVER converts the document to BSON format

Step 2: BSON sent to MongoDB server over wire protocol
Step 3: MongoDB VALIDATES the document (optional schema validation)
Step 4: Write goes to WIREDTIGER IN-MEMORY CACHE

[Link] 5/20
04/04/2026, 13:14 MongoDB — Complete Deep Dive from Zero to Expert
Step 5: Write is logged to JOURNAL (write-ahead log on disk)
→ At this point, write is DURABLE (survives crash)
Step 6: Periodically, WiredTiger CHECKPOINTS — flushes dirty
pages from cache to the data files on disk
Step 7: If the document has INDEXED fields, the index B-Trees
are also updated (in memory, then flushed)

WRITE CONCERN controls how much durability you want:

{ w: 0 } → "Fire and forget" — don't even wait for ACK
{ w: 1 } → Wait for PRIMARY to confirm (default)
{ w: "majority" }→ Wait for MAJORITY of replica set to confirm
(strongest guarantee, highest latency)

How a Read Works — Step by Step

READ PATH — WHAT HAPPENS WHEN YOU QUERY

[Link]({ email: "amit@[Link]" })

Step 1: Query engine PARSES the query

Step 2: OPTIMIZER checks available indexes
→ Index on email exists? Use it (index scan)
→ No index? Full COLLECTION SCAN (read every document)
Step 3: If using index: traverse B-Tree → find matching _id(s)
→ Fetch full document from cache (or disk if not cached)
Step 4: If collection scan: read every document, filter in memory
Step 5: Return matching documents as a CURSOR to the driver
Step 6: Driver converts BSON back to JSON/native objects

READ PREFERENCE controls WHERE you read from:

primary → Read from primary only (strongest consistency)
primaryPreferred → Primary if available, else secondary
secondary → Read from secondary only (may be stale)
secondaryPreferred → Secondary if available, else primary
nearest → Whichever node has lowest network latency

4 Querying MongoDB — CRUD Operations

MongoDB uses its own query language (not SQL). Here are the core operations:

[Link] 6/20

04/04/2026, 13:14 MongoDB — Complete Deep Dive from Zero to Expert

CREATE — Inserting Documents

INSERT OPERATIONS

/ Insert ONE document

[Link]({
name: "Amit",
email: "amit@[Link]",
age: 28,
address: { city: "Mumbai", state: "Maharashtra" }
})

/ Insert MANY documents at once (batch insert — much faster)

[Link]([
{ name: "Priya", email: "priya@[Link]", age: 25 },
{ name: "Sara", email: "sara@[Link]", age: 30, bio: "Developer" },
{ name: "Rahul", email: "rahul@[Link]", age: 22, tags: ["student"] } ])
/ Note: Sara has "bio", Rahul has "tags" — different fields! No problem.

READ — Finding Documents

 READ OPERATIONS — FROM SIMPLE TO COMPLEX

/ Find ALL documents in a collection

[Link]()

/ Find with a FILTER (like SQL WHERE)

[Link]({ city: "Mumbai" }) / WHERE city = 'Mumbai'
[Link]({ age: { $gt: 25 } }) / WHERE age > 25
[Link]({ age: { $gte: 20, $lte: 30 } }) / WHERE age BETWEEN 20 AND 30 [Link]({ name: { $in: ["Amit",
"Sara"] } }) / WHERE name IN ('Amit','Sara')

/ Find with AND (multiple conditions)

[Link]({ city: "Mumbai", age: { $gt: 25 } }) / city='Mumbai' AND age>25

/ Find with OR
[Link]({ $or: [{ city: "Mumbai" }, { age: { $gt: 30 } }] })

/ Find ONE document (returns first match)

[Link]({ email: "amit@[Link]" })

/ PROJECTION — select specific fields (like SQL SELECT name, email)

[Link] 7/20
04/04/2026, 13:14 MongoDB — Complete Deep Dive from Zero to Expert

[Link]({ city: "Mumbai" }, { name: 1, email: 1, _id: 0 })

/ SORT + LIMIT + SKIP (pagination)

[Link]().sort({ age: -1 }).limit(10).skip(20) / Page 3, 10 per page

/ COUNT
[Link]({ city: "Mumbai" })

/ QUERY NESTED FIELDS (dot notation)

[Link]({ "[Link]": "Mumbai" }) / query inside nested object

/ QUERY ARRAY ELEMENTS

[Link]({ tags: "premium" }) / any doc where tags contains "premium" [Link]({ "[Link]": "pending" }) /
nested array object field

UPDATE — Modifying Documents

UPDATE OPERATIONS

/ Update ONE document

[Link](
{ email: "amit@[Link]" }, / filter (which document?)
{ $set: { age: 29, "[Link]": "Pune" } } / update (what to change?) )
 / Update MANY documents at once
[Link](
{ city: "Mumbai" }, / all Mumbai users
{ $set: { region: "West" } } / add a new field to all of them )

/ INCREMENT a number
[Link](
{ _id: ObjectId("507f1f77bcf86cd799439011") },
{ $inc: { age: 1 } } / age = age + 1
)

/ PUSH to an array
[Link](
{ email: "amit@[Link]" },
{ $push: { tags: "verified" } } / add "verified" to tags array )

/ UPSERT — update if exists, insert if not

[Link](

[Link] 8/20
04/04/2026, 13:14 MongoDB — Complete Deep Dive from Zero to Expert

{ email: "new@[Link]" },
{ $set: { name: "New User", age: 20 } },
{ upsert: true } / creates document if not found )

DELETE — Removing Documents

DELETE OPERATIONS

/ Delete ONE document

[Link]({ email: "amit@[Link]" })

/ Delete MANY documents

[Link]({ age: { $lt: 18 } }) / delete all users under 18

/ Delete ALL documents in a collection (but keep the collection)

[Link]({})

/ Drop entire collection (deletes collection + all indexes)

[Link]()

Aggregation Pipeline — MongoDB's Power Feature

WHAT IS THE AGGREGATION PIPELINE?

MongoDB's equivalent of SQL's GROUP BY , HAVING , JOIN , and subqueries — all in one. Data flows
through a series of stages, each transforming the data. Like a factory assembly line where each
 station does one operation.

AGGREGATION PIPELINE — SQL EQUIVALENT EXAMPLES

/ SQL: SELECT city, COUNT(*) as count FROM users GROUP BY city ORDER BY count DESC
[Link]([
{ $group: { _id: "$city", count: { $sum: 1 } } },
{ $sort: { count: -1 } }
])

/ SQL: SELECT city, AVG(age) FROM users WHERE age > 20 GROUP BY city HAVING COUNT(*) > [Link]([
{ $match: { age: { $gt: 20 } } }, / Stage 1: WHERE age > 20
{ $group: { / Stage 2: GROUP BY city [Link] 9/20

04/04/2026, 13:14 MongoDB — Complete Deep Dive from Zero to Expert

_id: "$city",
avgAge: { $avg: "$age" },
count: { $sum: 1 }
}},
{ $match: { count: { $gt: 5 } } }, / Stage 3: HAVING count > 5 { $sort: { avgAge: -1 } } / Stage 4: ORDER BY
avgAge DESC ])

/ $lookup = MongoDB's LEFT JOIN

[Link]([
{ $lookup: {
from: "users", / join with users collection localField: "user_id", / orders.user_id
foreignField: "_id", / matches users._id
as: "user_info" / output field name
}}
])

PIPELINE STAGES (most common):

$match → Filter documents (WHERE)
$group → Group and aggregate (GROUP BY)
$sort → Sort results (ORDER BY)
$project → Select/rename fields (SELECT)
$limit → Limit results (LIMIT)
$skip → Skip results (OFFSET)
$unwind → Flatten arrays (one doc per array element)
$lookup → Join with another collection (LEFT JOIN)
$addFields → Add computed fields

5
Indexing in MongoDB

Just like SQL databases, MongoDB uses B-Tree indexes to speed up queries. Without indexes,
 MongoDB must scan every document in a collection (collection scan). With indexes, it jumps directly to
matching documents.

MONGODB INDEX TYPES AND HOW TO CREATE THEM

/ DEFAULT: _id index (created automatically on every collection)

/ Every collection always has an index on _id.

/ SINGLE FIELD INDEX

[Link] 10/20
04/04/2026, 13:14 MongoDB — Complete Deep Dive from Zero to Expert
[Link]({ email: 1 }) / 1 = ascending, -1 = descending

/ COMPOUND INDEX (composite — same left-prefix rule as SQL!)

[Link]({ city: 1, age: -1 }) / city ascending, age descending

/ UNIQUE INDEX (prevents duplicates)

[Link]({ email: 1 }, { unique: true })

/ TEXT INDEX (for full-text search)

[Link]({ title: "text", body: "text" })
[Link]({ $text: { $search: "mongodb tutorial" } })

/ TTL INDEX (auto-delete documents after a time period)

[Link]({ createdAt: 1 }, { expireAfterSeconds: 3600 })
/ Documents auto-deleted 1 hour after createdAt — perfect for sessions!

/ PARTIAL INDEX (index only a subset of documents)

[Link](
{ created_at: 1 },
{ partialFilterExpression: { status: "pending" } }
)

/ MULTIKEY INDEX (index arrays — MongoDB-specific!)

[Link]({ tags: 1 })
/ Automatically indexes EACH element of the tags array
/ [Link]({ tags: "premium" }) → uses this index

/ CHECK WHAT INDEXES EXIST

[Link]()

/ EXPLAIN — see if query uses index

[Link]({ email: "amit@[Link]" }).explain("executionStats")

MONGODB INDEXING GOTCHAS

1. Same left-prefix rule as SQL: compound index {a:1, b:1, c:1} works for queries on a , a+b , a+b+c — NOT b alone.
2. Each index slows down writes (same trade-off as SQL).
3. MongoDB has a 64 index limit per collection.
4. Multikey indexes on arrays can get very large — index each array element.
5. TTL indexes are amazing for session data, cache entries, and logs — auto-cleanup!
[Link] 11/20

6
04/04/2026, 13:14 MongoDB — Complete Deep Dive from Zero to Expert Scaling MongoDB —
Replica Sets & Sharding

Replica Sets — High Availability & Read Scaling

WHAT IS A REPLICA SET?

A replica set is a group of MongoDB servers that maintain the same data. One server is the primary
(handles all writes), and the others are secondaries (receive copies of the data). If the primary fails, a
secondary is automatically elected as the new primary — automatic failover.

MONGODB REPLICA SET — HIGH AVAILABILITY

Application

PRIMARY
All WRITES go here

SECONDARY 1 AUTOMATIC FAILOVER:

Async replication from Primary
Can serve reads (read preference) 1. Primary goes down (crash, network issue)
SECONDARY 2
Async replication from Primary Automatic failover if Primary dies

REPLICA SET — KEY BEHAVIORS

2. Secondaries detect primary is unresponsive (heartbeat timeout)

3. Secondaries hold an ELECTION
4. One secondary is PROMOTED to new primary
5. Application driver automatically reconnects to new primary
6. Total failover time: typically 10-30 seconds

READ SCALING:
Route read queries to secondaries to spread the load.
But reads from secondaries may return SLIGHTLY STALE data
 (async replication lag — usually milliseconds to seconds).

MINIMUM SETUP: 3 members recommended

→ 1 Primary + 2 Secondaries (can survive 1 failure)
→ Or: 1 Primary + 1 Secondary + 1 Arbiter (votes but holds no data)

Sharding — Horizontal Scaling for Massive Data

[Link] 12/20
04/04/2026, 13:14 MongoDB — Complete Deep Dive from Zero to Expert

WHAT IS SHARDING IN MONGODB?

When your data is too large for one server (or write throughput exceeds one server's capacity),
you shard — split data across multiple servers. MongoDB has built-in sharding, unlike SQL
databases where sharding is an afterthought.

MONGODB SHARDED CLUSTER ARCHITECTURE

Application

Shard 1 (Replica Set) Shard 2 (Replica Set) Shard 3 (Replica Set)

Primary Sec 1 Sec 2 Primary Sec 1 Sec 2 Primary Sec 1 Sec 2

Users A-H Users I-P Users Q-Z

(33M documents) (33M documents) (34M documents)
Each shard IS a replica set! Config Servers
mongos (Query Router) Routes queries to Metadata: which shard has what
correct shard(s)

SHARDING COMPONENTS AND SHARD KEY SELECTION

THREE COMPONENTS OF A SHARDED CLUSTER:

1. mongos (Router)
→ Your app connects to mongos (not directly to shards)
→ mongos knows which shard has which data
→ Routes queries to the correct shard(s)
→ Multiple mongos instances for load balancing

2. Config Servers (Replica Set)

→ Store metadata: which chunks live on which shard
→ Store the shard key ranges
→ Must be a 3-member replica set for durability

3. Shards (each is a Replica Set)

→ Each shard holds a SUBSET of the data
 → Each shard is itself a replica set (for HA)
→ Data is split into CHUNKS based on shard key

SHARD KEY SELECTION — THE MOST CRITICAL DECISION:

The shard key determines HOW data is distributed across shards.

[Link] 13/20
04/04/2026, 13:14 MongoDB — Complete Deep Dive from Zero to Expert Bad shard key = hot spots, uneven distribution,

terrible performance.

GOOD shard keys:

• High cardinality (many distinct values) → even distribution
• Write distribution (writes spread across shards, not all to one) • Query isolation (most queries
target one shard, not all)
• Example: user_id → each user's data on one shard, queries by user = one shard

BAD shard keys:

• Low cardinality (e.g., status: "active"/"inactive") → 2 chunks max • Monotonically
increasing (e.g., _id, timestamp) → all writes go to ONE shard (the one with the
highest range). Creates HOT SHARD.
• Example: created_at → all new data goes to latest shard

STRATEGIES:
• Hashed shard key: hash(user_id) → even write distribution
But range queries on user_id now span all shards.
• Range shard key: user_id ranges → range queries are efficient
But risk of hot shards if distribution is uneven.
• Compound shard key: { region: 1, user_id: 1 }
→ Queries for one region go to one shard group

7 Read-Heavy vs Write-Heavy — How MongoDB Handles Both READ-

HEAVY WORKLOAD WRITE-HEAVY WORKLOAD

→ Strategy: Add read replicas (secondaries) → Strategy: Shard to distribute writes

→ read preference → write distribution

Route reads to secondaries via Use hashed shard key for even

→ Create proper indexes for hot queries → Use covering → Minimize indexes (each slows writes) → Use bulk

indexes (all fields in index) → WiredTiger cache keeps inserts (insertMany) for batching → Lower write concern

hot data in RAM for non-critical data

→ → WiredTiger journal handles burst writes → Example: Product catalog, user profiles
Consider embedding related data (avoid →
→ Example: IoT data, event logs, analytics
$lookup) Real-world: CERN uses MongoDB for
→ product catalog reads particle physics data
Real-world: eBay uses MongoDB for

[Link] 14/20
04/04/2026, 13:14 MongoDB — Complete Deep Dive from Zero to Expert READ-HEAVY VS WRITE-HEAVY —

ARCHITECTURE PATTERNS

READ-HEAVY PATTERN (e-commerce product pages):

┌──────────┐ reads ┌───────────┐
│ App │ ──────────────→│ Secondary │ (read replica)
│ │ reads ├───────────┤
│ │ ──────────────→│ Secondary │ (read replica)
│ │ writes ├───────────┤
│ │ ──────────────→│ Primary │ (all writes here) └──────────┘
└───────────┘
Reads spread across 3 servers. Writes go to 1.
Ratio: 90% reads / 10% writes → this works great.

WRITE-HEAVY PATTERN (IoT sensor data):

┌──────────┐ write ┌──────────────────┐
│ App │ ──────────→│ mongos (router) │
└──────────┘ └─────┬──────┬──────┘
│││
┌────┘ │ └────┐
↓↓↓
┌──────────┐ ┌──────────┐ ┌──────────┐ │ Shard 1 │ │ Shard 2
│ │ Shard 3 │ │ writes A │ │ writes B │ │ writes C │ └──────────┘
└──────────┘ └──────────┘ Writes distributed across 3 shards.
Each shard handles 1/3 of write volume.
Hashed shard key ensures even distribution.

8 Challenges & Trade-offs

✅ MONGODB STRENGTHS ❌ MONGODB WEAKNESSES

→ → No native JOINs — $lookup is slow at scale

Schema flexibility — add fields without

→ Embedded documents — one →→ Data duplication — embedded

migration
read gets everything data can go stale
Limited transactions — multi- doc ACID
→ Built-in sharding — horizontal scale native since 4.0 but slower than SQL

[Link] 15/20
04/04/2026, 13:14 MongoDB — Complete Deep Dive from Zero to Expert

→ Replica sets — automatic failover → No enforced schema by default — bad

→ aggregation pipeline → Memory hungry — WiredTiger

Rich query language — data can sneak in wants lots

→ Multikey indexes — index array documents →

of RAM
elements → TTL indexes — auto-expire
Shard key is immutable — can't change

→→→ Change streams — real-time →→ Not ideal for highly relational

event notifications data — graphs, complex JOINs

Geo-spatial queries — location- Developer experience — JSON after creation Write amplification — indexes +
based search = natural
journal + replication

for web devs THE 16MB DOCUMENT LIMIT

→ Eventual consistency on secondary reads → Size limit —

16MB per document

A single MongoDB document cannot exceed 16 MB. This means you can't embed unlimited data
inside one document. If an array (like comments on a post) can grow unboundedly, eventually the
document hits 16MB and writes fail. For unbounded arrays, use a separate collection with a
reference — don't embed.

EMBEDDING VS REFERENCING — THE CORE DESIGN DECISION

EMBED when: data is always accessed together, data is bounded (won't grow forever), one-to few
relationship (user has 1-3 addresses).

REFERENCE (separate collection) when: data is accessed independently, data can

grow unboundedly (user has millions of comments), many-to-many relationship
(students ↔ courses).

9 When to Use MongoDB — And When NOT To

✅ USE MONGODB WHEN ❌ DON'T USE MONGODB WHEN → You need complex JOINs across many
Data is document-shaped (user profiles, catalogs) entities
→

[Link] 16/20
04/04/2026, 13:14 MongoDB — Complete Deep Dive from Zero to Expert

→ (startups, prototypes) → across multiple docs

Schema evolves rapidly ACID transactions are critical

→ You need horizontal scaling built-in → Data is highly relational (many-to-many Real-time analytics with aggregation
everywhere) pipeline
Primary access = key-based lookups →
read
Data has nested structures (JSON-like) You need strong consistency on every
→ Financial/banking data (use SQL +
→→ → ACID)

→ Content management systems → Graph traversals (use Neo4j)

→ Mobile/web apps with JSON APIs → IoT data with varying sensor schemas Time-series metrics at extreme scale (use

→ Cassandra/InfluxDB)

→ Team has zero NoSQL experience

10 Real-World Companies Using MongoDB — And Why

COMPANYWHAT THEY USE MONGODB

FORWHY MONGODB

eBay Product catalog, search suggestions Handles 100B+ events/day. Schema-less events with varying
properties.
Forbes Content management system
MongoDB's geospatial indexes for finding nearby drivers.
Adobe User data platform, analytics Location data changes constantly.

Uber Geospatial data, trip matching Rapidly evolving data models as new cryptocurrencies and
features are added.
Coinbase Cryptocurrency portfolio data
Flexible product schemas (electronics vs clothing have different EA
fields). Billions of listings. Games
Player profiles, game state Each game has different data
structures. Player state varies widely between games.
Articles have varying structures (text, video, galleries). Schema
flexibility is critical.

Toyota Connected vehicle data IoT sensor data from vehicles with varying sensor configurations per
model.

[Link] 17/20
04/04/2026, 13:14 MongoDB — Complete Deep Dive from Zero to Expert

THE PATTERN ACROSS ALL THESE COMPANIES

Notice the common thread: every company chose MongoDB because their data was naturally
document-shaped with varying fields. A product listing for a laptop has "RAM, CPU, GPU" fields. A
product listing for a shirt has "size, color, material" fields. In SQL, you'd need either a massive table
with hundreds of nullable columns or complex EAV (Entity-Attribute-Value) patterns. In MongoDB,
each document simply has the fields it needs.

11
Interview Questions & Model Answers

Q1 : "WHEN WOULD YOU CHOOSE MONGODB OVER POSTGRESQL?"

When my data is naturally document-shaped with nested structures (user profiles, product catalogs),
when the schema evolves frequently (startup, rapid iteration), when I need built-in horizontal sharding
for scale, and when my primary access pattern is key-based lookups rather than complex multi-table
JOINs. I'd choose PostgreSQL when I need ACID transactions across multiple entities, complex
relational queries, or the data has many-to-many relationships.

Q2: "HOW DOES MONGODB ACHIEVE HIGH AVAILABILITY?"

Through replica sets — a group of MongoDB instances maintaining the same data. One
primary handles all writes, secondaries replicate asynchronously. If the primary fails, an
automatic election promotes a secondary within 10-30 seconds. The driver automatically
reconnects. For read scaling, you can route reads to secondaries using read preferences.

Q3: "HOW WOULD YOU HANDLE A USE CASE WHERE A DOCUMENT KEEPS GROWING (E.G., COMMENTS ON A
POST)?"

I would NOT embed comments inside the post document because it can grow unboundedly and hit
the 16MB document limit. Instead, I'd use a separate comments collection with a reference: each
comment has a post_id field. I'd create an index on { post_id: 1, created_at: -1 } for efficient retrieval.
This is the "reference" pattern vs the "embed" pattern — use embedding for bounded, always-
accessed-together data, and referencing for unbounded or independently-accessed data.

Q4: "WHAT MAKES A GOOD SHARD KEY IN MONGODB?"

A good shard key has high cardinality (many distinct values for even distribution), even write distribution (writes don't
all go to one shard), and query isolation (most queries can be routed
[Link] 18/20
04/04/2026, 13:14 MongoDB — Complete Deep Dive from Zero to Expert
 to a single shard). Example: user_id is often a good shard key. A bad shard key is something
monotonically increasing like created_at because all new writes go to the latest shard, creating a hot
spot.

Q5: "DOES MONGODB SUPPORT TRANSACTIONS?"

Yes, since MongoDB 4.0 (2018), it supports multi-document ACID transactions across replica sets,
and since 4.2, across sharded clusters. However, transactions are slower than SQL transactions and
should be used sparingly. MongoDB's philosophy is to design your data model so that most
operations affect a single document (which is always atomic) and only use multi-document
transactions when absolutely necessary.

Q6: "EMBEDDING VS REFERENCING — HOW DO YOU DECIDE?"

Embed when: the data is always read together, the relationship is 1-to-few (user has 2-3
addresses), the embedded data is bounded and won't grow forever.
Reference when: the data is accessed independently, the relationship is 1-to-many or many to-
many, the array could grow unboundedly (comments, orders), or the referenced data changes
frequently and you don't want to update it everywhere.

�� MongoDB — Complete Quick Revision

WHAT IS MONGODB?

Document-oriented NoSQL. Stores JSON-like documents (BSON) in collections. Schema-less —

each document can have different fields. No tables, no rows, no JOINs by default.

CORE CONCEPTS

Database → Collection (table) → Document (row) → Field (column). _id = auto-generated

ObjectId (primary key). Embed related data inside documents.

STORAGE ENGINE

WiredTiger: document-level locking, compression (Snappy/Zstd), journal (WAL for durability), in-
memory cache (50% RAM), B-Tree indexes.

QUERIES

find() , insertOne/Many() , updateOne/Many() , deleteOne/Many() . Aggregation pipeline: $match → $group →

$sort → $project → $lookup (JOIN).

[Link] 19/20
04/04/2026, 13:14 MongoDB — Complete Deep Dive from Zero to Expert

INDEXES
 B-Tree indexes. Single, compound (left-prefix rule!), unique, text, TTL (auto-expire), partial,
multikey (arrays). 64 index limit per collection.

SCALING

Replica Sets: 1 primary + N secondaries. Automatic failover. Read scaling via secondaries. Async
replication.

Sharding: Built-in. mongos router → config servers → shards (each a replica set). Shard key
= most critical decision. Hashed (even writes) vs ranged (efficient range queries).

READ VS WRITE HEAVY

Read-heavy: add secondaries, proper indexes, embedding, caching. Write-heavy: shard with hashed
key, fewer indexes, bulk inserts, lower write concern.

EMBED VS REFERENCE

Embed: always accessed together, bounded, 1-to-few. Reference: independent access,

unbounded growth, many-to-many. 16MB doc limit!

WHEN TO USE

Use: document-shaped data, evolving schema, horizontal scale, key-based lookups, nested data,
CMS, catalogs, mobile apps.

Don't use: complex JOINs, multi-doc ACID critical, highly relational, financial/banking, graph
traversals.

COMPANIES

eBay (catalogs), Forbes (CMS), Adobe (analytics), Uber (geospatial), Coinbase (crypto), EA
Games (player state), Toyota (IoT).

[Link] 20/20
04/04/2026, 13:39 Apache Cassandra — Complete Deep Dive from Zero to Expert
 NOSQL D EEP D I V E — WI D E- COL UMN STORE

Apache Cassandra — Complete Deep Dive

From zero to expert: the write-optimised distributed database — LSM Trees, SSTables,
partitioning, tunable consistency, and why the biggest companies use it for billions of events

1 What Is Apache Cassandra? (Starting From Zero)

DEFINITION

Apache Cassandra is a distributed, wide-column NoSQL database designed for massive write
throughput, high availability, and linear horizontal scaling. It was originally built at Facebook to power
their inbox search feature and later open-sourced. It's now used by Apple, Netflix, Instagram,
Discord, and Uber to handle billions of events per day.

THE POSTAL SYSTEM ANALOGY

Imagine a postal system with hundreds of post offices across a country. When you send a letter
(write), it goes to the nearest post office — no single central office handles everything. Each post
office stores letters for its local area. If one post office burns down, the others still work. Letters are
eventually delivered to all relevant offices (eventual consistency). No single
post office is the "boss" — they're all equal (peer-to-peer). That's Cassandra — no single
master, every node is equal, writes go to the nearest available node.

Why Cassandra Exists — The Problem It Solves

THE PROBLEM TRADITIONAL DATABASES CAN'T SOLVE

SCENARIO: Instagram Activity Feed

→ 2 billion users
→ When a celebrity posts, millions of followers' feeds need updating
→ 1 post = millions of write operations
→ Writes must be fast (users expect instant feed updates)
→ System must NEVER go down (even during server failures)
→ Data spans multiple continents (US, Europe, Asia)

[Link] 1/19
04/04/2026, 13:39 Apache Cassandra — Complete Deep Dive from Zero to Expert WHY SQL CAN'T HANDLE THIS:

→ Single master = bottleneck for writes

→ Vertical scaling has a ceiling
→ Multi-region ACID = extremely expensive and slow
→ One master fails = entire system down until failover
 WHY MONGODB CAN'T HANDLE THIS WELL:
→ Primary handles ALL writes = single-node write bottleneck → Sharding
helps but primary per shard is still a bottleneck → Not optimised for extreme
write throughput

WHY CASSANDRA WAS BUILT:

→ NO single master — every node accepts writes (peer-to-peer) → Writes are
the CHEAPEST operation (LSM Tree architecture) → Linear scaling — add
nodes, get proportional throughput → Multi-datacenter replication built into
the core
→ Designed to NEVER go down

Cassandra vs MongoDB vs SQL — Quick Positioning

ASPECT SQL (POSTGRESQL) MONGODB CASSANDRA

Architecture Single master Primary + secondaries Peer-to-peer (no master!)

Write Speed Moderate Fast Extremely fast (LSM Tree) Read Speed Fast (B+ Tree) Fast (B-Tree) Moderate

(multi-level reads)

Consistency Strong (ACID) Configurable Tunable (ONE → QUORUM → ALL)

Query Power Rich JOINs, SQL Aggregation pipeline Limited (no JOINs, restricted WHERE)

Horizontal Scale Complex (sharding) Built-in sharding Native, linear, effortless

Availability Single point of failure Automatic failover Always available (no master)

Best For Complex queries, ACID Flexible docs, catalogs Massive writes, time-series, IoT

[Link] 2/19
2
04/04/2026, 13:39 Apache Cassandra — Complete Deep Dive from Zero to Expert Cassandra's Data Model —
Partitions, Rows & Columns

THE MOST IMPORTANT CONCEPT

Cassandra's data model looks like SQL tables on the surface, but underneath it works completely
differently. The partition key determines which node stores the data. The clustering key determines
the sort order within a partition. Getting this right is the single most critical design decision.
 CASSANDRA TERMINOLOGY MAPPED TO SQL

SQL Concept Cassandra Equivalent Key Difference

──────────────────────────────────────────────────────────────────
Database Keyspace Also defines replication strategy Table Table Looks similar but storage is different Row Partition (+
rows inside) Grouped by partition key
Primary Key Partition Key + Clustering Key Determines data placement + sort Column Column Each row can have
different columns Index Secondary Index / SAI Limited compared to SQL indexes JOIN DOES NOT EXIST Denormalise
instead!

Partition Key & Clustering Key — The Heart of Cassandra

UNDERSTANDING PARTITION KEY AND CLUSTERING KEY

CREATE TABLE messages (

channel_id UUID, - PARTITION KEY: determines which node
message_id TIMEUUID, - CLUSTERING KEY: sort order within partition author TEXT,
content TEXT,
created_at TIMESTAMP,
PRIMARY KEY (channel_id, message_id)
);
-↑↑
- partition key clustering key

WHAT THE PARTITION KEY DOES:

→ hash(channel_id) = determines which NODE stores this data
→ All messages for channel "abc" land on the SAME node
→ This is why reads for one channel are fast — all data is local

[Link] 3/19
04/04/2026, 13:39 Apache Cassandra — Complete Deep Dive from Zero to Expert

WHAT THE CLUSTERING KEY DOES:

→ Within a partition, rows are SORTED by message_id
→ message_id is a TIMEUUID (contains timestamp)
→ So messages within a channel are automatically sorted by time
→ Range queries within a partition are extremely efficient:
"Get messages from channel X between 9am and 10am"

RESULT:
Partition: channel_id = "gaming-chat"
┌──────────────────────────────────────────────────────┐
│ message_id (sorted!) │ author │ content │
├──────────────────────┼─────────┼─────────────────────┤
│ 2024-03-15T09:00:01 │ Amit │ "Hello everyone!" │
│ 2024-03-15T09:00:05 │ Sara │ "Hey Amit!" │
│ 2024-03-15T09:01:12 │ Rahul │ "Good morning" │
│ 2024-03-15T09:03:45 │ Amit │ "How's it going?" │
 │ .millions more . │ │ │
└──────────────────────┴─────────┴─────────────────────┘
All sorted by time within this partition → range reads are FAST

CRITICAL RULE: PARTITION SIZE

All data with the same partition key lives on the same node. If a partition grows too large (100MB+),
it becomes a hot spot — one node handles disproportionate traffic while others are idle. This is why
Discord uses (channel_id, bucket) — the bucket splits very active channels into time-based chunks so
no single partition gets too big.

Compound Partition Keys & Composite Keys

DIFFERENT KEY DESIGNS FOR DIFFERENT USE CASES

- SIMPLE PARTITION KEY (most common)

PRIMARY KEY (user_id)
→ All data for one user on one node.
→ Good when queries always filter by user_id.

- COMPOUND PARTITION KEY (split large partitions)

PRIMARY KEY ((channel_id, bucket), message_id)
→ Data split by channel AND bucket (e.g., daily bucket)
→ Prevents single channel from creating a mega-partition
→ Discord uses this exact pattern!

- COMPOSITE KEY (partition + multiple clustering)

[Link] 4/19
04/04/2026, 13:39 Apache Cassandra — Complete Deep Dive from Zero to Expert PRIMARY KEY (user_id, year, month)

→ Partition by user_id, sorted by year then month

→ "Get all records for user X in 2024" = efficient range query

3
Internal Architecture — Why Writes Are So Fast

THE CORE INNOVATION: LSM TREE

Cassandra uses a Log-Structured Merge Tree (LSM Tree) instead of a B+ Tree. This is the
fundamental reason Cassandra writes are so fast. In a B+ Tree (SQL/MongoDB), writes require
finding the right position on disk and updating in-place — this means random disk I/O. In an LSM
Tree, writes go to an in-memory table first, then get flushed sequentially to disk. Sequential writes
are 100x faster than random writes.
 The Write Path — Step by Step

CASSANDRA WRITE PATH — LSM TREE ARCHITECTURE

Client Write Request

+ ② MEMTABLE (in RAM)
① COMMIT LOG (on disk) In-memory sorted data structure. Fastest possible write.
Append-only sequential write — FAST. Durability guarantee.

WHY THIS IS FAST: when full ③ SSTABLE (on disk)

Write ACK sent to client here! ✅ No disk seek needed — that's why
writes are fast
1. Commit log = sequential append (no seek)
2. Memtable = RAM write (microseconds)
(Append-Only, On Disk)
3. Client gets ACK after step 1+2
4. SSTable flush happens in background
5. No random disk I/O at write time!
Result: millions of writes/second
on commodity hardware
Sorted String Table — immutable, sequential write to disk.

④ COMPACTION (background process)

Merges multiple SSTables into fewer, larger ones.
Removes deleted data (tombstones) and duplicates. Reclaims space.
WRITE PATH — DETAILED STEP BY STEP Step 1: COMMIT LOG

→ Write is appended to the end of the commit log file

→ This is a SEQUENTIAL write (fast — no disk seeking)
→ Purpose: DURABILITY. If server crashes, replay the log to recover.
→ Think of it like a journal — log every operation for safety.

[Link] 5/19
04/04/2026, 13:39 Apache Cassandra — Complete Deep Dive from Zero to Expert

Step 2: MEMTABLE (In-Memory Sorted Structure)

→ Simultaneously, the write goes into the Memtable (in RAM)
→ Memtable is a sorted data structure (like a Red-Black Tree)
→ Writes to RAM = microseconds (no disk I/O!)
→ ACK is sent to the client RIGHT HERE.
The client doesn't wait for the data to reach disk files.
This is WHY Cassandra writes are so fast.

Step 3: SSTABLE FLUSH (Memtable → Disk)

→ When Memtable reaches a size threshold (e.g., 64MB),
it is FLUSHED to disk as an SSTable (Sorted String Table)
→ SSTable is IMMUTABLE — once written, never modified
→ Write is sequential (dump entire sorted table at once)
→ Old commit log entries for this data can now be deleted

Step 4: COMPACTION (Background Merge)

→ Over time, many SSTables accumulate on disk
→ Compaction merges multiple SSTables into fewer, larger ones
→ Removes tombstones (deleted data markers)
→ Removes duplicate/overwritten values (keeps latest)
 → Reclaims disk space
→ Strategies: Size-Tiered (default), Leveled, Time-Window

The Read Path — Why Reads Are Slower

READ PATH — STEP BY STEP

Query: SELECT * FROM messages WHERE channel_id = 'abc' AND message_id = 'xyz';

Step 1: Check MEMTABLE (RAM)

→ Is the data in the current in-memory table?
→ If YES → return immediately (fastest possible read)

Step 2: Check BLOOM FILTERS (probabilistic check)

→ For each SSTable on disk, Cassandra has a Bloom Filter
→ Bloom Filter says: "This SSTable DEFINITELY doesn't have the key"
or "This SSTable MIGHT have the key"
→ Skips SSTables that definitely don't have the data
→ Avoids reading unnecessary files (huge optimisation)

Step 3: Check KEY CACHE (RAM)

→ Caches the byte offset of partition keys in SSTables
→ If key is cached: jump directly to the right position on disk
→ Avoids scanning the SSTable index

[Link] 6/19
04/04/2026, 13:39 Apache Cassandra — Complete Deep Dive from Zero to Expert

Step 4: Check PARTITION INDEX → PARTITION SUMMARY

→ If not in key cache, use the on-disk index to find the partition
→ Partition Summary (in RAM) narrows down the search range
→ Partition Index (on disk) gives exact byte offset

Step 5: Read from SSTABLE on disk

→ Use the byte offset to read the actual data
→ May need to read from MULTIPLE SSTables
(data could be spread across several — before compaction)

Step 6: MERGE results from all sources

→ Combine data from Memtable + multiple SSTables
→ Use timestamps to keep the LATEST version of each column
→ Return the final, merged result to the client

WHY READS ARE SLOWER THAN WRITES:

→ Writes: 2 steps (commit log + memtable) → done
→ Reads: up to 5+ steps, may hit multiple SSTables on disk
→ Compaction reduces the number of SSTables (improves reads)
→ Bloom Filters prevent unnecessary SSTable reads
 → Key cache and row cache further accelerate hot data

B+ TREE VS LSM TREE — THE FUNDAMENTAL DIFFERENCE

B+ Tree (SQL/MongoDB): Reads are fast (sorted tree, one lookup). Writes are slower (find
position, update in-place, random I/O). Read-optimised.

LSM Tree (Cassandra): Writes are fast (append to log + RAM, sequential I/O). Reads are
slower (check memtable + multiple SSTables). Write-optimised.

This is the fundamental trade-off. Cassandra chose to optimise for writes because its target use
cases (IoT, feeds, messaging) are write-heavy.

4
Distributed Architecture — No Master, Everyone Is Equal

PEER-TO-PEER ARCHITECTURE

Unlike SQL (single master) or MongoDB (primary + secondaries), Cassandra uses a peer-to peer
architecture. Every node is equal — there is no master, no primary, no leader. Any node

[Link] 7/19
04/04/2026, 13:39 Apache Cassandra — Complete Deep Dive from Zero to Expert can accept reads and writes. If one node goes
down, the others continue serving traffic seamlessly. No failover election needed because
there's nothing to fail over FROM.

CASSANDRA'S RING ARCHITECTURE — CONSISTENT HASHING

Token: 0-24

Node A
Node F is DOWN
Other nodes handle its
data via replication → no downtime! Node F Node B Write: user_id = "amit" hash("amit") = 37
DOWN! ✗
→ Token range 25-49
Token: 25-49
→ Goes to Node B

Node E Node D
Token: 100-124
Token: 75-99
Node CToken: 50-74

CONSISTENT HASHING — HOW DATA IS DISTRIBUTED

THE TOKEN RING:

→ Cassandra uses CONSISTENT HASHING to distribute data
→ The entire key space is arranged in a RING (0 to 2^63)
→ Each node is assigned a TOKEN RANGE on the ring
→ When you write data, Cassandra hashes the partition key:
hash(partition_key) → token number → which node's range?
 EXAMPLE:
Node A: tokens 0-24 Node D: tokens 75-99
Node B: tokens 25-49 Node E: tokens 100-124
Node C: tokens 50-74 Node F: tokens 125-149

Write user "amit": hash("amit") = 37 → falls in 25-49 → Node B

Write user "priya": hash("priya") = 88 → falls in 75-99 → Node D

ADDING A NEW NODE:

Add Node G with tokens 30-39
→ Node B gives up tokens 30-39 to Node G
→ Only data in token range 30-39 needs to move
→ Other nodes are NOT affected (minimal data movement)
→ This is WHY scaling is LINEAR and easy

Replication — How Data Is Copied for Safety

[Link] 8/19
04/04/2026, 13:39 Apache Cassandra — Complete Deep Dive from Zero to Expert REPLICATION FACTOR AND HOW COPIES

ARE DISTRIBUTED

REPLICATION FACTOR (RF) = number of copies of each piece of data

RF = 3 (most common production setting):

→ Each piece of data is stored on 3 DIFFERENT nodes
→ If one node dies, 2 copies still exist
→ If two nodes die, 1 copy still exists

HOW REPLICAS ARE PLACED:

Write: hash("amit") = 37 → primary = Node B (tokens 25-49)
→ Replica 1: Node B (primary)
→ Replica 2: Node C (next node clockwise on ring)
→ Replica 3: Node D (next node after C)

3 copies on 3 different nodes:

┌──────────────────────────────────┐
│ Node B: amit's data (primary) │
│ Node C: amit's data (replica) │
│ Node D: amit's data (replica) │
└──────────────────────────────────┘

MULTI-DATACENTER REPLICATION:
CREATE KEYSPACE myapp WITH replication = {
'class': 'NetworkTopologyStrategy',
'us-east': 3, - 3 copies in US East
'eu-west': 3, - 3 copies in EU West
'ap-south': 3 - 3 copies in Asia
};
→ Data automatically replicated across 3 continents
→ Local reads are fast (nearby datacenter)
 → If entire US datacenter goes down, EU and Asia still work

5 Tunable Consistency — Cassandra's Superpower

WHAT IS TUNABLE CONSISTENCY?

Unlike SQL (always strong) or most NoSQL (always eventual), Cassandra lets you choose per
query how consistent you want the response to be. You control the trade-off between consistency
and latency/availability for each individual read and write operation.

[Link] 9/19
04/04/2026, 13:39 Apache Cassandra — Complete Deep Dive from Zero to Expert CONSISTENCY LEVELS — FROM WEAKEST

TO STRONGEST

With Replication Factor = 3 (3 copies on 3 nodes):

WRITE Consistency Levels:

ONE → Write to 1 node, ACK immediately.
Fastest. Risk: if that node dies before replicating, data lost. QUORUM → Write to majority (2
of 3 nodes), then ACK.
Good balance. Survives 1 node failure.
ALL → Write to ALL 3 nodes, then ACK.
Slowest. Strongest guarantee. If any node is down, write FAILS.

READ Consistency Levels:

ONE → Read from 1 node (nearest). Fastest. May return stale data. QUORUM → Read from
majority (2 of 3). Compare responses, return latest. Good balance. Higher latency than ONE.
ALL → Read from ALL 3 nodes. Compare all. Strongest but slowest.

THE MAGIC FORMULA for strong consistency:

W + R > RF → STRONG CONSISTENCY

Where: W = write consistency, R = read consistency, RF = replication factor

Examples (RF = 3):

W=QUORUM(2) + R=QUORUM(2) = 4 > 3 → STRONG ✓ (most common!)
W=ALL(3) + R=ONE(1) = 4 > 3 → STRONG ✓
W=ONE(1) + R=ONE(1) = 2 < 3 → EVENTUAL ✗ (fastest but may read stale) TUNABLE

CONSISTENCY — PER QUERY CONTROL (RF=3)

CL = ONE Fastest ⚡ ✓ ACK ✓ ACK async

1 node confirms
May read stale data Use for: analytics, logs
Balanced ⚖️
✓ ACK wait wait CL = QUORUM Majority (2 of 3) confirms Strong if W+R > RF
 Use for: most production ✓ ACK ✓ ACK ✓ ACK Strongest but Slowest �� ALL 3 nodes must
CL = ALL confirm 1 node down = operation FAILS Use for:
rarely (sacrifices HA)

6
Querying Cassandra — CQL (Cassandra Query Language)

[Link] 10/19

04/04/2026, 13:39 Apache Cassandra — Complete Deep Dive from Zero to Expert CQL looks like SQL but has strict constraints on
what you can query. This is by design — Cassandra trades query flexibility for performance.

CQL CRUD OPERATIONS

- CREATE KEYSPACE (like a database)

CREATE KEYSPACE myapp
WITH replication = {'class': 'SimpleStrategy', 'replication_factor': 3};

USE myapp;

- CREATE TABLE
CREATE TABLE messages (
channel_id UUID,
message_id TIMEUUID,
author TEXT,
content TEXT,
created_at TIMESTAMP,
PRIMARY KEY (channel_id, message_id)
) WITH CLUSTERING ORDER BY (message_id DESC); - newest first

- INSERT
INSERT INTO messages (channel_id, message_id, author, content, created_at) VALUES (uuid(), now(),
'Amit', 'Hello world!', toTimestamp(now()));

- READ (must include partition key!)

SELECT * FROM messages WHERE channel_id = some-uuid;

- READ with clustering key range (time-range query)

SELECT * FROM messages
WHERE channel_id = some-uuid
AND message_id > minTimeuuid('2024-03-15 09:00:00')
AND message_id < minTimeuuid('2024-03-15 10:00:00');

- UPDATE (actually an UPSERT — insert if not exists)

UPDATE messages SET content = 'Edited message'
WHERE channel_id = some-uuid AND message_id = some-timeuuid;
- DELETE
DELETE FROM messages
WHERE channel_id = some-uuid AND message_id = some-timeuuid;

- DELETE with TTL (auto-delete after 7 days)

INSERT INTO messages (channel_id, message_id, author, content)
VALUES (uuid(), now(), 'Amit', 'Temporary message')
USING TTL 604800; - 604800 seconds = 7 days

[Link] 11/19