Database Selection Guide: How to Choose the Right DB for Your System

# DATABASE CATEGORY DECISION TREE
# Use this mental model before writing a single line of schema

function selectDatabaseCategory(requirements):

    # STEP 1: Is the data primarily time-stamped metrics or events?
    if requirements.isTimeSeriesData:
        # e.g. CPU usage every 5s, stock prices, IoT sensor readings
        return "Time-Series DB (InfluxDB, TimescaleDB)"
        # WHY: These DBs compress sequential numeric data up to 90%
        # and run time-window aggregations (avg over last 1hr) natively

    # STEP 2: Are RELATIONSHIPS between entities the core query?
    if requirements.queriesAreRelationshipTraversals:
        # e.g. 'find all users connected within 3 hops who like jazz'
        return "Graph DB (Neo4j, Amazon Neptune)"
        # WHY: SQL JOINs across many-to-many relationships become
        # exponentially slower. Graph traversal stays O(depth), not O(rows)

    # STEP 3: Is sub-millisecond lookup by a single key the priority?
    if requirements.primaryAccessPattern == "KEY_LOOKUP" and
       requirements.latencyBudgetMs < 5:
        # e.g. session tokens, rate limit counters, feature flags
        return "Key-Value Store (Redis, DynamoDB)"
        # WHY: Hash-map style storage eliminates query planning entirely.
        # Redis keeps data in RAM — it's literally as fast as memory access

    # STEP 4: Does the schema change frequently or vary per record?
    if requirements.schemaIsFlexible or requirements.nestedObjectsAreCommon:
        # e.g. product catalog where 'laptop' and 'shirt' have totally
        # different attributes, or a CMS where articles have custom fields
        return "Document DB (MongoDB, Firestore, CouchDB)"
        # WHY: Embedding related data in one document avoids JOINs
        # entirely. One read = complete object. Perfect for read-heavy,
        # object-oriented access patterns

    # STEP 5: Default — do you need ACID transactions and complex queries?
    if requirements.requiresACIDTransactions or
       requirements.complexMultiTableQueries:
        # e.g. banking, e-commerce orders, healthcare records
        return "Relational DB (PostgreSQL, MySQL)"
        # WHY: Decades of optimization, rock-solid consistency guarantees,
        # powerful query planner, and JOIN performance that beats
        # application-level joins for complex analytical queries

    # If nothing is extreme, default to relational — it's the safest bet
    return "Relational DB (PostgreSQL) — until a specific pain point emerges"

-- ============================================================
-- SCENARIO: E-commerce order system
-- We'll model the same data in SQL and discuss the trade-offs
-- ============================================================

-- ── SQL APPROACH (PostgreSQL) ────────────────────────────────
-- Three normalized tables. Every order_item links back to a product.
-- Changing a product name updates ONE row and every order reflects it.

CREATE TABLE customers (
    customer_id   UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email         TEXT UNIQUE NOT NULL,
    created_at    TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE orders (
    order_id      UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    customer_id   UUID REFERENCES customers(customer_id),  -- foreign key enforced by DB
    total_cents   INTEGER NOT NULL,                        -- store money as cents, never float
    status        TEXT CHECK (status IN ('pending','paid','shipped','cancelled')),
    placed_at     TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE order_items (
    item_id       UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    order_id      UUID REFERENCES orders(order_id) ON DELETE CASCADE,
    product_name  TEXT NOT NULL,
    quantity      INTEGER NOT NULL CHECK (quantity > 0),
    unit_cents    INTEGER NOT NULL
);

-- Fetch a complete order with one JOIN query
-- PostgreSQL's query planner will use indexes automatically
SELECT
    o.order_id,
    c.email            AS customer_email,
    oi.product_name,
    oi.quantity,
    (oi.unit_cents * oi.quantity) / 100.0 AS line_total_dollars
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
JOIN order_items oi ON oi.order_id = o.order_id
WHERE o.status = 'paid'
  AND o.placed_at > NOW() - INTERVAL '7 days';

-- WHY SQL WINS HERE:
-- 1. The CHECK constraint on status means invalid states are impossible
-- 2. The FOREIGN KEY means you can never have an orphaned order_item
-- 3. One JOIN query answers complex business questions ad-hoc
-- 4. Changing a product's name is one UPDATE, not finding every document

-- ── WHAT THE SAME DATA LOOKS LIKE IN A DOCUMENT DB ──────────
-- (MongoDB-style JSON document — shown here as a SQL comment for comparison)

/*
{
  "_id": "ord_7f3a9c",
  "customer": {
    "email": "alice@example.com"      // DENORMALIZED — copied at order time
  },
  "items": [
    { "product_name": "Mechanical Keyboard", "quantity": 1, "unit_cents": 12999 },
    { "product_name": "USB-C Cable",         "quantity": 2, "unit_cents": 1499 }
  ],
  "status": "paid",
  "placed_at": "2024-06-01T14:23:00Z"
}

  WHY DOCUMENT WINS HERE:
  - One read fetches the entire order. Zero JOINs. Faster for this specific query.
  - If your UI always shows 'a single order', this shape matches perfectly.
  - Schema can vary: a digital order might have 'download_url', a physical one
    might have 'shipping_address' — no ALTER TABLE needed.

  WHY DOCUMENT LOSES HERE:
  - 'Show me all orders containing Mechanical Keyboards placed in June'
    requires scanning every document unless you build a specific index.
  - The customer's email is copied into every order. If they change it,
    you can't easily update historical orders — is that right or wrong?
    In SQL, that question doesn't arise.
*/

# ============================================================
# SCENARIO: Social media platform — showing how one feature
# (loading a user's home feed) touches THREE different databases
# ============================================================

import redis          # Key-value store — for cached feed
import psycopg2       # PostgreSQL — for user auth and post metadata
# from neo4j import GraphDatabase  # Graph DB — for follow graph

# In a real app these would come from environment variables / secrets manager
REDIS_HOST      = "redis.internal"
POSTGRES_DSN    = "postgresql://app_user:secret@pg.internal/social_db"
NEO4J_URI       = "bolt://neo4j.internal:7687"

FEED_CACHE_TTL_SECONDS = 300   # Cache each user's feed for 5 minutes
FEED_MAX_POSTS         = 20    # Return at most 20 posts per feed load


def get_home_feed(requesting_user_id: str) -> list[dict]:
    """
    Load the home feed for a user.

    Access pattern breakdown:
    1. Redis (Key-Value)   — Check if we already computed this feed recently.
                            If yes, return it instantly. (~0.1ms)
    2. Neo4j (Graph)       — Find which users this person follows.
                            Traversal is native, not a JOIN. (~5ms)
    3. PostgreSQL (SQL)    — Fetch the actual post content + metadata
                            for posts from those followed users. (~20ms)
    4. Redis again         — Cache the assembled feed for next time.
    """

    # ── STEP 1: Check the feed cache first ────────────────────
    cache_client = redis.Redis(host=REDIS_HOST, decode_responses=True)
    cache_key    = f"feed:v2:{requesting_user_id}"   # versioned key lets us bust cache on schema change

    cached_feed = cache_client.get(cache_key)
    if cached_feed:
        print(f"[Cache HIT]  Feed for {requesting_user_id} served from Redis")
        return eval(cached_feed)   # In production: use json.loads()

    print(f"[Cache MISS] Building feed for {requesting_user_id} from scratch")

    # ── STEP 2: Get followed user IDs from the graph database ─
    # In Neo4j, this is a single traversal — no JOIN across a 500M row follows table
    # Cypher query would look like:
    #   MATCH (me:User {id: $userId})-[:FOLLOWS]->(friend:User)
    #   RETURN friend.id LIMIT 500
    #
    # For this example, we simulate the result:
    followed_user_ids = _get_followed_users_from_graph(requesting_user_id)
    print(f"[Graph DB]   {requesting_user_id} follows {len(followed_user_ids)} users")

    if not followed_user_ids:
        return []   # New user with no follows — return empty feed

    # ── STEP 3: Fetch post content from PostgreSQL ────────────
    # SQL is great here: we need sorting, filtering by time, and joining
    # post content with author display names — exactly what SQL is built for
    pg_connection = psycopg2.connect(POSTGRES_DSN)
    cursor        = pg_connection.cursor()

    # %s placeholders are psycopg2's safe parameterization — never use f-strings for SQL
    placeholders = ','.join(['%s'] * len(followed_user_ids))
    cursor.execute(f"""
        SELECT
            posts.post_id,
            posts.author_id,
            users.display_name,
            posts.content_text,
            posts.created_at,
            posts.like_count
        FROM posts
        JOIN users ON users.user_id = posts.author_id
        WHERE posts.author_id IN ({placeholders})
          AND posts.created_at > NOW() - INTERVAL '48 hours'   -- recency window
        ORDER BY posts.created_at DESC
        LIMIT %s
    """, (*followed_user_ids, FEED_MAX_POSTS))

    raw_rows = cursor.fetchall()
    cursor.close()
    pg_connection.close()

    # Convert raw tuples to readable dicts
    feed_posts = [
        {
            "post_id":      row[0],
            "author_id":    row[1],
            "author_name":  row[2],
            "text":         row[3],
            "posted_at":    str(row[4]),
            "likes":        row[5]
        }
        for row in raw_rows
    ]
    print(f"[PostgreSQL] Fetched {len(feed_posts)} posts from followed users")

    # ── STEP 4: Cache the assembled feed in Redis ─────────────
    # Next request for this user returns instantly without touching PG or Neo4j
    cache_client.setex(cache_key, FEE_CACHE_TTL_SECONDS, str(feed_posts))
    print(f"[Cache SET]  Feed cached for {FEE_CACHE_TTL_SECONDS}s")

    return feed_posts


def _get_followed_users_from_graph(user_id: str) -> list[str]:
    """Simulates a Neo4j graph traversal result for this example."""
    # In production this would run a Cypher query via the neo4j-driver
    simulated_follow_graph = {
        "user_alice": ["user_bob", "user_carol", "user_dave"],
        "user_bob":   ["user_alice", "user_eve"],
    }
    return simulated_follow_graph.get(user_id, [])


# ── DEMO RUN ──────────────────────────────────────────────────
if __name__ == "__main__":
    # First call — cache miss, full pipeline
    feed = get_home_feed("user_alice")
    print(f"\nFeed contains {len(feed)} posts\n")

    # Second call — cache hit, instant response
    feed_cached = get_home_feed("user_alice")

# ============================================================
# CAP THEOREM — Practical database mapping
# This is a system design reference, not runnable code.
# Use this when justifying database choices in design interviews
# or architecture review meetings.
# ============================================================

cap_theorem_database_guide:

  # ── CP: Consistency + Partition Tolerance ─────────────────
  # Behavior during network partition: REJECT requests rather than serve stale data
  # Real-world use: Any domain where stale reads cause real harm
  cp_databases:
    - name: PostgreSQL (with synchronous replication)
      use_when:
        - Financial transactions — double-spend is catastrophic
        - Inventory management — overselling is a business problem
        - Healthcare records — an outdated allergy list could hurt someone
      trade_off: >
        During a network split, your app will get errors.
        Your ops team needs alerting. Users see error pages.
        That is the CORRECT behavior when data integrity > availability.

    - name: HBase
      use_when:
        - Large-scale consistent writes (Hadoop ecosystem)
        - Time-series that cannot tolerate duplicate or out-of-order writes
      trade_off: Operationally complex. Only worth it at massive write scale.

  # ── AP: Availability + Partition Tolerance ─────────────────
  # Behavior during network partition: SERVE potentially stale data
  # Real-world use: Any domain where uptime > perfect accuracy
  ap_databases:
    - name: Cassandra
      use_when:
        - Shopping carts — stale cart is fine, empty cart is not
        - User activity logs — a missed click event is acceptable
        - Messaging (last-seen timestamps) — approximate is good enough
      trade_off: >
        You WILL get eventual consistency, not immediate.
        Your application code must handle 'read your own writes' edge cases.
        Cassandra makes this explicit — you choose consistency level per query.
      cassandra_consistency_levels:
        ONE:    "Fastest. Read from 1 replica. May be stale. Use for analytics."
        QUORUM: "Read from majority of replicas. Balance of speed + safety."
        ALL:    "Read from all replicas. Slowest. Use for critical reads."

    - name: DynamoDB (default mode)
      use_when:
        - Session state — slightly stale session data is usually fine
        - Product catalog reads — showing yesterday's price for a split second is ok
        - Gaming leaderboards — approximate rankings are acceptable
      strongly_consistent_read_flag: >
        DynamoDB lets you opt into consistency PER OPERATION with
        ConsistentRead=True. This doubles read cost but guarantees
        the latest data. Powerful escape hatch.

    - name: CouchDB
      use_when:
        - Offline-first mobile apps — device syncs when back online
        - Distributed edge deployments where partition is the default state

  # ── The 'CA' Myth ──────────────────────────────────────────
  # CA (Consistent + Available, no partition tolerance) only exists
  # on a single-node system. The moment you add a second machine,
  # you MUST handle partition tolerance. CA is a theoretical category
  # for single-node databases like SQLite.
  ca_note: >
    Single-node PostgreSQL is CA — consistent and available, but if
    the single machine goes down, you have neither. This is fine for
    development and small applications. Just don't call it 'distributed'.

  # ── Decision Rule ──────────────────────────────────────────
  decision_rule: >
    Ask yourself: 'During a 30-second network outage between my database
    nodes, is it better to (A) return an error, or (B) return potentially
    stale data?' If A: choose CP. If B: choose AP.
    The answer reveals more about your system than any benchmark.