SQL vs NoSQL: The Database Guide

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: Single-node, replicas, sharding, or distributed SQL depending on product
Transactions: Isolation and durability depend on engine and configuration

Philosophy: Flexibility first, structure optional
Data model: Varies (documents, key-value, graphs, columns)
Schema: Product-dependent; flexible does not mean unvalidated
Relationships: Embedded or denormalized
Query language: Varies by database
Scaling: Primarily horizontal
Consistency: Product- and operation-specific, from eventual to strongly consistent

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(oi.quantity * oi.unit_price) 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);

-- Locking and rewrite behavior depend on the database, version, and default.
-- Plan and test the migration; adding a nullable column need not rewrite every row.

One node has finite compute, memory, storage, and I/O capacity.
The relevant limit depends on the product, hardware, and workload.
Replicas, partitioning, middleware, or distributed SQL may extend capacity.

-- 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"

// Bound partition size and hotspot risk; a theoretical limit is not a target.

// 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

- Required queries meet measured latency targets
- Transaction and durability settings meet business invariants
- Growth and recovery plans are operationally manageable

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)

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. Different transaction boundaries → Verify supported atomic operations
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 durable change capture or an outbox with reconciliation 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