Every system starts with: "How do we store data?"

Wrong choice:
  - Slow queries
  - Expensive rewrites
  - Scalability bottlenecks
  - Engineering frustration

Right choice:
  - Natural data model
  - Fast queries
  - Easy scaling
  - Happy team

Philosophy: Structure first, flexibility later
Data model: Tables with rows/columns
Schema: Strict (defined upfront)
Relationships: Foreign keys + JOINs
Query language: SQL
Scaling: Primarily vertical
Consistency: ACID guarantees

Philosophy: Flexibility first, structure optional
Data model: Varies (documents, key-value, graphs, columns)
Schema: Dynamic or schema-less
Relationships: Embedded or denormalized
Query language: Varies by database
Scaling: Primarily horizontal
Consistency: Eventually consistent (BASE)

BEGIN TRANSACTION;
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
  UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

-- Guaranteed: Both happen or neither happens

-- Find top customers who bought products in multiple categories
SELECT 
    c.name,
    COUNT(DISTINCT p.category) AS categories_purchased,
    SUM(o.total) AS total_spent
FROM customers c
JOIN orders o ON c.id = o.customer_id
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id
GROUP BY c.id
HAVING COUNT(DISTINCT p.category) >= 3
ORDER BY total_spent DESC
LIMIT 10;

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    age INTEGER CHECK (age >= 18),
    created_at TIMESTAMP DEFAULT NOW()
);

-- Database enforces:
-- - Unique emails
-- - Non-null emails
-- - Age >= 18
-- - Auto-generated IDs

-- Adding a column requires migration
ALTER TABLE users ADD COLUMN phone VARCHAR(20);

-- Affects entire table (millions of rows!)
-- Requires downtime or careful migration

Single server limitations:
  - Max CPU: 128 cores
  - Max RAM: 4TB
  - Max storage: ~100TB
  - Cost: $$$$$

Beyond this: Must shard manually (complex!)

-- 5-way JOIN on large tables = slow
SELECT *
FROM table1
JOIN table2 ON ...
JOIN table3 ON ...
JOIN table4 ON ...
JOIN table5 ON ...
WHERE ...;

-- Time: can be seconds on large datasets

// User document
{
  "_id": "user123",
  "name": "Alice",
  "email": "alice@example.com",
  "address": {
    "street": "123 Main St",
    "city": "NYC"
  },
  "orders": [
    { "id": "order1", "total": 99.99, "date": "2024-01-01" },
    { "id": "order2", "total": 149.99, "date": "2024-01-15" }
  ]
}

// Query
db.users.find({ "address.city": "NYC", "orders.total": { $gt: 100 } })

# Redis
redis.set("user:123:name", "Alice")
redis.set("user:123:email", "alice@example.com")

# Retrieve
name = redis.get("user:123:name")

# Atomic operations
redis.incr("page:views")
redis.expire("session:abc", 3600)  # TTL

Column Family: user_events
Row Key: user_123

Column: event_2024-01-01-10:00:00 → "login"
Column: event_2024-01-01-10:05:23 → "click_button"
Column: event_2024-01-01-10:10:15 → "purchase"
...
Column: event_2024-01-15-09:30:00 → "logout"

// Can have billions of columns per row!

// Social network
(Alice:Person)-[:FRIEND]->(Bob:Person)-[:FRIEND]->(Carol:Person)
(Alice)-[:BOUGHT]->(Product:Laptop)
(Bob)-[:BOUGHT]->(Product)

// Query: Friends who bought same product
MATCH (me:Person)-[:FRIEND]-(friend)-[:BOUGHT]->(product)
WHERE me.name = 'Alice'
  AND (me)-[:BOUGHT]-> (product)
RETURN friend.name, product.name

Atomicity:   All or nothing
Consistency: Valid state always
Isolation:   Transactions don't interfere
Durability:  Committed = persisted

Example:
  Bank transfer: $100 from A to B
  Either both happen or neither
  Database never shows inconsistent state

Basically Available:   System responds even if some nodes down
Soft state:            State may change over time
Eventually consistent: Reads may be stale, but will converge

Example:
  Twitter follower count
  Might show 1,000 on one server, 1,001 on another
  Eventually both show 1,001
  Acceptable trade-off for speed

E-commerce:
  - Users, Orders, Products, Inventory
  - Clear foreign keys
  - Need JOINs (order → order_items → products)

Banking, Healthcare, Payroll:
  - Cannot lose transactions
  - Must maintain constraints
  - ACID guarantees required

Analytics, Reporting:
  - Multi-table aggregations
  - Ad-hoc queries
  - Business intelligence

 < 10M active rows
- Single datacenter
- Vertical scaling acceptable

Content platforms, catalogs:
  - Product attributes vary
  - User profiles with custom fields
  - Rapid feature development

Social media, IoT, logs:
  - Billions of records
  - Petabytes of data
  - Horizontal scaling needed

Time-series, events, metrics:
  - 100k+ writes/second
  - Append-only workload
  - Eventual consistency OK

Global apps:
  - Low-latency worldwide
  - Multi-region replication
  - Partition tolerance critical

Key-value: Cache, sessions (Redis)
Graph: Social networks (Neo4j)
Document: CMS, catalogs (MongoDB)
Wide-column: Time-series (Cassandra)

Phase 1 (Early): PostgreSQL
  - Simple schema (users, photos, likes)
  - ACID transactions
  - <1M users

Phase 2 (Growth): Still PostgreSQL
  - Vertical scaling to largest machines
  - Read replicas
  - ~10M users

Phase 3 (Scale): Cassandra
  - Billions of photos
  - Petabytes of data
  - Global distribution
  - Horizontal scaling
  - Eventual consistency acceptable

MySQL:
  - Driver/rider accounts
  - Trip transactions
  - Payment processing
  (ACID critical)

Redis:
  - Driver location cache
  - Surge pricing calculations
  - Real-time matching
  (Speed critical)

Cassandra:
  - Trip history
  - Location logs
  - Analytics events
  (Scale + writes critical)

MySQL:
  - User accounts
  - Billing
  - Subscription management

Cassandra:
  - Viewing history (billions of events)
  - Recommendations pre-computations
  - A/B test results

EVCache (Memcached):
  - Movie metadata
  - User preferences
  - API response caching

Elasticsearch:
  - Search and discovery
  - Content recommendations

graph TB
    App[Application]
    
    App -->|User Auth| PG[PostgreSQL<br/>ACID transactions]
    App -->|Caching| Redis[Redis<br/>Sub-ms reads]
    App -->|Events| Kafka[Kafka<br/>Stream processing]
    App -->|Analytics| Cassandra[Cassandra<br/>Time-series]
    App -->|Search| ES[Elasticsearch<br/>Full-text search]
    App -->|Recommendations| Neo4j[Neo4j<br/>Graph queries]

PostgreSQL:
  - Orders, payments, inventory
  - ACID for financial accuracy

Redis:
  - Shopping cart (session data)
  - Product catalog cache
  - Real-time inventory count

Elasticsearch:
  - Product search
  - Faceted navigation
  - Autocomplete

Cassandra:
  - User click streams
  - Product view history
  - Recommendation training data

Neo4j:
  - Product recommendations
  - "Customers also bought..."
  - Fraud detection rules

1. No JOINs → Denormalize data
2. No transactions → Application-level consistency
3. Different query patterns → Rethink access patterns
4. Data migration → Dual-write period

1. Identify bounded contexts
2. Migrate one service/table at a time
3. Use dual-write during transition
4. Validate data consistency
5. Gradual traffic shift

Phase 1: MongoDB (rapid prototyping)
Phase 2: PostgreSQL (after PMF, stable schema)

Reason: Complex reporting needs emerged

Scenario: 1M user profiles

SQL (PostgreSQL):
  - Read: ~1ms (indexed)
  - Write: ~2ms (ACID overhead)
  - JOIN (3 tables): ~10-50ms
  - Analytics query: ~100ms-1s
  - Max throughput: ~10k writes/sec (single node)

NoSQL (MongoDB):
  - Read: ~0.5ms (embedded docs, no JOINs)
  - Write: ~0.8ms (eventual consistency)
  - JOIN equivalent: N/A (denormalized)
  - Analytics query: ~1-10s (MapReduce)
  - Max throughput: ~100k writes/sec (sharded)

Key-Value (Redis):
  - Read: ~0.1ms (in-memory)
  - Write: ~0.1ms (in-memory)
  - JOIN equivalent: N/A
  - Analytics query: N/A (not designed for this)
  - Max throughput: ~1M ops/sec