Query Optimization

WRITE PATH (optimize for correctness):
  User creates order → Normalize → Store in tables
  orders(id, user_id, total)
  order_items(order_id, product_id, qty)
  → Clean, no redundancy, easy to update

READ PATH (optimize for speed):
  User views dashboard → Precomputed view → Instant response
  user_dashboard(user_id, order_count, total_spent, last_order_date)
  → Redundant, but no JOINs needed, 1ms lookup

The pattern:
  Write → Normalized tables (source of truth)
       → Trigger/event → Update denormalized views, indexes, caches
  Read  → Denormalized view / materialized view / search index
       → Fast response (no JOINs, no aggregations)

-- NORMALIZED (correct, slow reads):
-- To display "Alice ordered Laptop":
SELECT u.name, p.name, o.total
FROM orders o
JOIN users u ON u.id = o.user_id
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON p.id = oi.product_id
WHERE o.id = 101;
-- 3 JOINs, touches 4 tables, ~20ms

-- DENORMALIZED (redundant, fast reads):
-- Store user_name and product_name directly in orders
SELECT user_name, product_name, total
FROM orders_denormalized
WHERE id = 101;
-- 0 JOINs, 1 table, ~1ms

-- The trade-off: when Alice changes her name,
-- you must update it in the users table AND in every order row.
-- Write complexity increases, but reads are 20x faster.

Base Tables:
  orders (200M rows)
  order_items (1B rows)
  products (5M rows)

Expensive query (runs on every dashboard load):
  SELECT category, SUM(total) as revenue, COUNT(*) as order_count
  FROM orders o
  JOIN order_items oi ON oi.order_id = o.id
  JOIN products p ON p.id = oi.product_id
  GROUP BY category;
  → Scans 1.2B rows, takes 30 seconds

Materialized view:
  CREATE MATERIALIZED VIEW category_revenue AS
  SELECT category, SUM(total) as revenue, COUNT(*) as order_count
  FROM orders o
  JOIN order_items oi ON oi.order_id = o.id
  JOIN products p ON p.id = oi.product_id
  GROUP BY category;

Dashboard query:
  SELECT * FROM category_revenue;
  → Reads precomputed table, takes 2ms

Refresh:
  REFRESH MATERIALIZED VIEW category_revenue;
  → Re-runs the expensive query, updates the stored result
  → Schedule: every 5 minutes, or trigger on write

                    WRITE PATH                    READ PATH
                    ─────────                    ─────────
  User places order                    User views order history
        │                                       │
        ▼                                       ▼
  ┌──────────────┐                    ┌──────────────────┐
  │  Write DB    │                    │   Read DB        │
  │ (normalized) │                    │ (denormalized)   │
  │              │                    │                  │
  │ orders       │  ──── Event ────→  │ order_history    │
  │ order_items  │     Stream         │ (user_name,      │
  │ products     │  (Kafka/SQS)       │  product_name,   │
  │ users        │                    │  total, status)  │
  └──────────────┘                    └──────────────────┘

Write: INSERT into normalized tables → publish event
Event consumer: reads event → updates denormalized read model
Read: SELECT from denormalized read model → instant response

Write DB: PostgreSQL (ACID, normalized, source of truth)
Read DB: MongoDB / Elasticsearch / Redis (optimized for queries)

Documents:
  doc1: "comfortable running shoes for men"
  doc2: "lightweight running sneakers"
  doc3: "comfortable leather shoes"

Inverted Index (built during indexing):
  comfortable → [doc1, doc3]
  running     → [doc1, doc2]
  shoes       → [doc1, doc3]
  men         → [doc1]
  lightweight → [doc2]
  sneakers    → [doc2]
  leather     → [doc3]

Search: "comfortable running shoes"
  comfortable → [doc1, doc3]
  running     → [doc1, doc2]
  shoes       → [doc1, doc3]
  Intersection: doc1 (matches all 3 terms) → highest relevance

SQL equivalent:
  WHERE name LIKE '%comfortable%' AND name LIKE '%running%'
  → Full table scan on 5M rows, no index used, 10+ seconds

Elasticsearch:
  → Inverted index lookup, 3 set intersections, 5ms

Architecture:
  PostgreSQL (source of truth) → Change Data Capture → Elasticsearch

Write path:
  1. Product created/updated in PostgreSQL
  2. CDC (Debezium) captures the change
  3. Change event sent to Kafka
  4. Consumer indexes the product in Elasticsearch

Read path (search):
  1. User searches "comfortable running shoes"
  2. App queries Elasticsearch (not PostgreSQL)
  3. Elasticsearch returns matching product IDs + relevance scores
  4. App fetches full product details from PostgreSQL/cache

Key insight:
  PostgreSQL = source of truth (writes, transactions, consistency)
  Elasticsearch = search index (reads, full-text, relevance)
  They serve different purposes. Don't replace one with the other.

WRITE PATH:
  Order placed → PostgreSQL (normalized, ACID)
               → Kafka event → Update denormalized views
               → Kafka event → Update Elasticsearch index
               → Kafka event → Update MongoDB read model

READ PATH:
  Product listing  → Denormalized table (PostgreSQL) → Redis cache
  Product search   → Elasticsearch (inverted index)
  Admin dashboard  → Materialized views (PostgreSQL)
  Order history    → MongoDB read model (CQRS)
  Product detail   → Redis cache → PostgreSQL fallback

Result:
  Write DB (PostgreSQL): handles 1K writes/sec (orders, updates)
  Read layer: handles 100K reads/sec across specialized stores
  Each read path is optimized for its specific query pattern