┌─────────────────────────────────────────────────────────────┐
│  Layer 1: Storage Layout  — the physics                     │
│  How bytes sit on disk. Cannot be escaped by software.      │
│  Row store / Document / Column-family / Columnar            │
└───────────────────────┬─────────────────────────────────────┘
                        │ constrains
┌───────────────────────▼─────────────────────────────────────┐
│  Layer 2: Data Model  — the contract                        │
│  How you express data. Embed vs reference.                  │
│  Schema vs schema-less. The irreversible decision.          │
└───────────────────────┬─────────────────────────────────────┘
                        │ constrains
┌───────────────────────▼─────────────────────────────────────┐
│  Layer 3: Query Pattern  — what you ask                     │
│  Point lookup vs scan vs aggregation.                       │
│  Indexes help here — but cannot exceed Layer 1's ceiling.   │
└─────────────────────────────────────────────────────────────┘

Page 1 (8KB):
┌─────────────────────────────────────────────────────────┐
│ Page Header (24 bytes)                                  │
│ Item pointers → [ptr1][ptr2][ptr3]...                   │
│                                                         │
│ Row (tuple): id=1 | name="Shreya" | age=25 | city="BLR"│
│ Row (tuple): id=2 | name="Rahul"  | age=28 | city="MUM"│
│ Row (tuple): id=3 | name="Priya"  | age=22 | city="DEL"│
└─────────────────────────────────────────────────────────┘

Page 2 (8KB):
┌─────────────────────────────────────────────────────────┐
│ Row (tuple): id=4 | name="Arjun" | age=31 | city="BLR" │
│ ...                                                     │
└─────────────────────────────────────────────────────────┘

B-tree on age:
         [25]
        /    \
    [21,24]  [28,31]
       |         |
  page ptrs   page ptrs

-- Functional index: index a specific path inside JSON
CREATE INDEX ON users ((profile -> 'address' ->> 'city'));

-- GIN index: index all keys inside the JSON blob
CREATE INDEX ON users USING GIN (profile jsonb_path_ops);

Before UPDATE:
  [Row v1: id=1, age=25]  ← live

After UPDATE (age=26):
  [Row v1: id=1, age=25]  ← dead (marked, not deleted yet)
  [Row v2: id=1, age=26]  ← live

After VACUUM:
  [Row v1: id=1, age=25]  ← reclaimed
  [Row v2: id=1, age=26]  ← live

SELECT * FROM users WHERE id = 'u123';
-- B-tree index → O(log n) → fetch one page → done
-- Excellent. This is the sweet spot.

SELECT * FROM orders
WHERE user_id = 'u123'
AND status = 'pending'
AND created_at > '2024-01-01';
-- Compound index covers all three columns
-- Postgres query planner picks the optimal strategy

SELECT u.name, o.amount, p.name AS product
FROM users u
JOIN orders o ON u.id = o.user_id
JOIN products p ON o.product_id = p.id
WHERE u.city = 'Bangalore';
-- Postgres executes nested loop, hash join, or merge join
-- depending on table sizes and available indexes

SELECT city, COUNT(*), AVG(age)
FROM users
GROUP BY city;
-- Fine up to tens of millions of rows
-- Hits ceiling at hundreds of millions without partitioning

BEGIN;
  UPDATE accounts SET balance = balance - 500 WHERE id = 'a1';
  UPDATE accounts SET balance = balance + 500 WHERE id = 'a2';
  INSERT INTO audit_log (action, amount) VALUES ('transfer', 500);
COMMIT;
-- Either all three happen or none do
-- Postgres's strongest unique capability

-- Slow: reading all rows to extract one column
SELECT AVG(revenue) FROM orders;  -- 2 billion rows
-- Must read every page on disk
-- Even with index, a full scan touches everything

-- Millions of inserts per second into one table
-- MVCC dead tuple accumulation, VACUUM can't keep up
-- WAL (write-ahead log) becomes a bottleneck

-- 500 million rows, no partition key in query
-- Full table scan → minutes, not milliseconds

CREATE TABLE events PARTITION BY RANGE (created_at);
-- Query with created_at filter → touches only relevant partition
-- Still row-oriented, but much smaller scan surface

JSON (text):  { "name": "Shreya", "age": 25 }  ← 26 bytes text
BSON (binary): \x1a\x00\x00\x00\x02name...      ← more compact, pre-parsed

WiredTiger Page (~16KB):
┌──────────────────────────────────────────────────────────────────┐
│ Doc u123: { name:"Shreya", age:25, address:{city:"BLR"},        │
│             languages:["Python","Go"], open_to_work:true }       │
│                                                                  │
│ Doc u124: { name:"Rahul", specialty:"neurology",                │
│             license:"MH-2891" }  ← different shape, same page   │
│                                                                  │
│ Doc u125: { name:"Priya", age:22, tags:["ml","python"] }        │
└──────────────────────────────────────────────────────────────────┘

Collection B-tree (keyed by _id):
  _id: "u101" → BSON blob of doc u101
  _id: "u123" → BSON blob of doc u123
  _id: "u200" → BSON blob of doc u200

Index B-tree (keyed by age):
  22 → _id: "u125"
  25 → _id: "u123"   ← Step 1: find _id
  28 → _id: "u089"
            ↓
         Step 2: look up _id in collection B-tree
            ↓
         Fetch BSON blob → full document

Document: { _id: "u123", languages: ["Python", "Go", "Rust"] }

Index entries created:
  "Go"     → _id: "u123"
  "Python" → _id: "u123"
  "Rust"   → _id: "u123"

10,000 documents × 1,000 array elements = 10,000,000 index entries

Impact:
  - Index may not fit in RAM → spills to disk → slow queries
  - Every array modification = multiple index updates
  - Write throughput degrades with array size

Embed when:                          Reference when:
─────────────────────────────────    ────────────────────────────────
Always accessed together             Accessed independently
Bounded size (won't grow forever)    Unbounded (can grow forever)
No independent existence             Has its own lifecycle
"Address" inside "User"              "Orders" for a "User"

// Good embed:                       // Bad embed (use reference):
{                                    { _id: "u123",
  _id: "u123",                         orders: [...1000 orders...] }
  address: {                         
    city: "Bangalore"                // Good reference:
  }                                  // orders collection:
}                                    { _id: "o1", user_id: "u123" }

db.users.findOne({ _id: "u123" })
// One B-tree lookup → one page read → complete document
// Fastest possible access pattern for document stores

db.users.find({ "address.city": "Bangalore" })
// Dot notation is native — no operator tricks
// Works with index: db.users.createIndex({ "address.city": 1 })

db.users.find({ languages: "Python" })
// Multikey index makes this O(log n)
// No JOIN to a separate table needed

// Doctors, musicians, developers — all in users collection
// Each has different fields — no NULLs, no separate tables
db.users.find({ profile_type: "doctor", specialty: "neurology" })

db.orders.aggregate([
  { $match: { status: "completed" } },
  { $group: { _id: "$user_id", total: { $sum: "$amount" } } },
  { $sort: { total: -1 } },
  { $limit: 10 }
])
// Flexible, but slower than SQL for complex multi-stage aggregations

// No native join — must use $lookup (slow) or application-side join
// If you find yourself doing this often, wrong database
db.orders.aggregate([
  { $lookup: { from: "users", localField: "user_id", foreignField: "_id", as: "user" } }
])

// Only need name and email from 10 million users
db.users.find({}, { name: 1, email: 1 })
// Still reads entire document BSON blob per match
// Projections filter after fetch — storage is still read in full

// MongoDB shards horizontally — but sharding adds complexity
// Single shard writes limited by one WiredTiger instance
// At Cassandra-level write volumes, MongoDB sharding is harder to operate

Partition: user_id = "u123"   (lives on Node A)
┌─────────────────────────────────────────────────────┐
│  sent_at: 2024-01-15  │ message_id │ content        │
│  sent_at: 2024-01-14  │ message_id │ content        │
│  sent_at: 2024-01-13  │ message_id │ content        │
│  ... sorted by clustering key (sent_at) ...         │
└─────────────────────────────────────────────────────┘

Partition: user_id = "u124"   (lives on Node B)
┌─────────────────────────────────────────────────────┐
│  sent_at: 2024-01-15  │ message_id │ content        │
│  ...                                                │
└─────────────────────────────────────────────────────┘

New message arrives → written to in-memory MemTable instantly
Write is confirmed to client immediately
No disk I/O on the write path

When MemTable fills up → flushed to disk as an SSTable file
SSTable = Sorted String Table = immutable, sorted, sequential file
Sequential writes are 10-100x faster than random writes

Multiple SSTables accumulate → merged and compacted in background
Compaction removes deleted data and merges updates
Read performance maintained without blocking writes

Write path:
  Client write → MemTable (RAM) → confirmed ✓
                      ↓ (async, when full)
                  SSTable on disk (sequential)
                      ↓ (background)
                  Compaction → merged SSTable

Compare to B-tree (Postgres):
  Client write → find correct B-tree position → random disk write → confirmed ✓
  (random writes are slow at scale)

         Node A (tokens 0–25%)
        /                     \
Node D (75–100%)          Node B (25–50%)
        \                     /
         Node C (50–75%)

Write flow:
  1. Hash(user_id) → determines coordinator node
  2. Coordinator writes to owner node + 2 replica nodes
  3. Once quorum (2 of 3) confirms → acknowledged to client
  
If one node is down:
  Write still succeeds (2 remaining nodes confirm quorum)
  Downed node catches up when it rejoins (hinted handoff)

-- Design for the query: "give me user X's messages, newest first"
CREATE TABLE inbox (
  user_id    UUID,
  sent_at    TIMESTAMP,
  message_id UUID,
  content    TEXT,
  sender_id  UUID,
  PRIMARY KEY (user_id, sent_at)
) WITH CLUSTERING ORDER BY (sent_at DESC);

-- This query is instant — hits one partition, sequential read
SELECT * FROM inbox
WHERE user_id = 'u123'
AND sent_at > '2024-01-14'
LIMIT 50;

-- This query is catastrophic — no partition key, full cluster scan
SELECT * FROM inbox WHERE content LIKE '%urgent%';
-- Cassandra asks EVERY node to scan EVERY partition
-- Do not do this in production

SELECT * FROM inbox
WHERE user_id = 'u123'
AND sent_at > toTimestamp(now()) - 86400s
LIMIT 100;
-- One partition → one node → sequential disk read → milliseconds

5 million writes/second:
  - Each write goes to the node that owns that partition
  - No single write bottleneck
  - MemTable absorbs writes in RAM
  - Sequential flush to SSTables on disk
  - Adding nodes linearly increases write capacity

Node A goes down:
  Reads and writes route to replica nodes
  No downtime, no manual failover
  System degrades gracefully

-- Netflix: user's viewing history
CREATE TABLE viewing_history (
  user_id   UUID,
  watched_at TIMESTAMP,
  show_id   UUID,
  progress  INT,
  PRIMARY KEY (user_id, watched_at)
) WITH CLUSTERING ORDER BY (watched_at DESC);

-- Uber: driver location pings
CREATE TABLE driver_locations (
  driver_id UUID,
  recorded_at TIMESTAMP,
  lat FLOAT,
  lng FLOAT,
  PRIMARY KEY (driver_id, recorded_at)
) WITH CLUSTERING ORDER BY (recorded_at DESC);

-- These are full cluster scans — catastrophically slow
SELECT * FROM inbox WHERE sender_id = 'u456';
SELECT * FROM inbox WHERE content = 'hello';
SELECT COUNT(*) FROM inbox;  -- never do this

Cassandra has no JOIN.
Cross-entity queries must be done in application code.
Design your data model so you never need to join.

-- "What % of users sent messages in the last 24 hours?"
-- Requires scanning all partitions across all nodes
-- Cassandra is the wrong tool for this query

Cassandra supports lightweight transactions (LWT) within one partition.
Cross-partition transactions do not exist.
If your use case needs them: wrong database.

hypertable: sensor_readings
  ├── chunk: 2024-01-01 to 2024-01-07  (one physical table)
  ├── chunk: 2024-01-08 to 2024-01-14  (one physical table)
  ├── chunk: 2024-01-15 to 2024-01-21  (one physical table, "hot")
  └── chunk: 2024-01-22 to now         (current write target)

ALTER TABLE sensor_readings SET (
  timescaledb.compress,
  timescaledb.compress_orderby = 'time DESC',
  timescaledb.compress_segmentby = 'sensor_id'
);
SELECT add_compression_policy('sensor_readings', INTERVAL '7 days');
-- Chunks older than 7 days → columnar compressed automatically

In-memory WAL (Write-Ahead Log):
  sensor=temp01, time=1705312200, value=72.4
  sensor=temp01, time=1705312205, value=72.6
  sensor=cpu01,  time=1705312205, value=0.84
  ↓ (flushed periodically)

TSM File (on disk, sorted by series key + time):
  series: sensor=cpu01
    time=1705312200 → 0.82
    time=1705312205 → 0.84
    time=1705312210 → 0.81
  series: sensor=temp01
    time=1705312200 → 72.4
    time=1705312205 → 72.6

-- TimescaleDB
SELECT
  time_bucket('5 minutes', time) AS bucket,
  sensor_id,
  AVG(value) AS avg_temp,
  MAX(value) AS max_temp
FROM sensor_readings
WHERE sensor_id = 'temp01'
  AND time > NOW() - INTERVAL '24 hours'
GROUP BY bucket, sensor_id
ORDER BY bucket DESC;

-- This is the native query shape. Extremely fast.

-- "Average CPU across all servers, last hour, per 1-minute window"
SELECT
  time_bucket('1 minute', time) AS bucket,
  AVG(cpu_usage) AS avg_cpu
FROM metrics
WHERE time > NOW() - INTERVAL '1 hour'
GROUP BY bucket
ORDER BY bucket;

-- Time-series DB scans the relevant time range only
-- Cassandra would require scanning all partitions — catastrophic

-- TimescaleDB: pre-aggregate automatically
CREATE MATERIALIZED VIEW hourly_avg
WITH (timescaledb.continuous) AS
SELECT
  time_bucket('1 hour', time) AS bucket,
  sensor_id,
  AVG(value) AS avg_val
FROM sensor_readings
GROUP BY bucket, sensor_id;

-- Dashboard queries hit the view, not raw data
-- Near-instant response for charts

-- Auto-delete data older than 90 days
SELECT add_retention_policy('sensor_readings', INTERVAL '90 days');
-- Drops entire old chunks — O(1) operation, no row-by-row delete

-- "Get all readings for sensor temp01 on Jan 15th" — fine
-- "Get all sensors whose readings exceeded 100 at any point" — slow
-- Time is the primary access key; non-time filters are secondary

Time-series DBs don't model relationships.
"Which user triggered this metric?" → wrong question for this tool.
Use your OLTP database for entity relationships.

Every record must have a timestamp as the organizing axis.
If your data isn't measurements over time, wrong tool.

START: What does your data actually look like?
│
├── Uniform rows, same shape per record, relationships between entities
│   └── Do you need complex joins and ACID transactions?
│       YES → PostgreSQL
│       NO  → continue
│
├── Objects with different shapes per record
│   └── Do you almost always need the full object?
│       YES → MongoDB
│       NO (need partial fields + SQL) → PostgreSQL + JSONB column
│
├── High-volume writes (millions/min), same query shape always
│   └── Is your query always "give me entity X's data for time range Y"?
│       YES → Cassandra
│       NO (need flexible queries too) → wrong tool; consider Postgres first
│
├── Measurements over time (metrics, sensors, prices)
│   └── Do you need time-aware aggregations (avg, rate, downsample)?
│       YES → TimescaleDB (if you want SQL) or InfluxDB (purpose-built)
│       └── Do you also need joins and transactions alongside this?
│           YES → TimescaleDB (it's still Postgres underneath)
│           NO  → InfluxDB (higher write throughput, simpler)
│
└── Historical analysis, BI reports, ad-hoc questions across all data
    └── OLAP (BigQuery, Redshift, ClickHouse)
        Does data freshness need to be real-time (seconds)?
        YES → ClickHouse (handles higher-frequency ingestion)
        NO  → BigQuery / Redshift (batch ETL, minutes to hours lag)

Before reaching for a specialized database, ask:

1. Can Postgres handle my write volume?
   Postgres handles ~10,000 writes/second on modest hardware.
   Most systems never exceed this. If yours doesn't → use Postgres.

2. Can Postgres handle my data with JSONB?
   Variable structure? Use a JSONB column for the flexible part.
   Structured envelope + flexible payload → Postgres + JSONB.

3. Can table partitioning solve my scale problem?
   Partition by time or by a high-cardinality field.
   Dramatically reduces scan surface. Extends Postgres life.

Only after exhausting these options → reach for a specialized database.

PostgreSQL bet:   "Rows with relationships are the universal data shape.
                   Flexibility and correctness matter most."

MongoDB bet:      "Objects are the universal data shape.
                   Flexibility and locality matter most."

Cassandra bet:    "Write volume and availability are the hard constraints.
                   We will sacrifice query flexibility to solve them."

Time-series bet:  "Time is the universal access axis for measurements.
                   Time-aware queries should be trivially simple."

OLAP bet:         "Analytics scan everything.
                   Column access is the only thing that matters."