-- Step 1: Identify the slowest queries
SELECT query, calls, mean_exec_time, total_exec_time
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 20;

-- Step 2: Check for missing indexes (sequential scans on large tables)
SELECT relname, seq_scan, idx_scan,
       round(seq_scan::numeric / (seq_scan + idx_scan + 1) * 100, 1) AS seq_pct
FROM pg_stat_user_tables
WHERE seq_scan > 0
ORDER BY seq_scan DESC;

-- ✅ Relational: index seek on primary key — O(log n)
SELECT * FROM orders WHERE order_id = 'ord_8f3k2';

-- ✅ Redis: O(1) hash get — sub-millisecond
GET session:user:42

-- ✅ DynamoDB: partition key hash — single-digit millisecond at any scale
GetItem({ TableName: 'orders', Key: { orderId: 'ord_8f3k2' } })

-- ✅ Relational: B-tree range scan — efficient with index
SELECT * FROM events
WHERE user_id = 42 AND created_at BETWEEN '2026-01-01' AND '2026-03-31';

-- ❌ DynamoDB: requires a GSI on (user_id, created_at) upfront — retrofitting is costly
-- ❌ MongoDB: works but requires a compound index; aggregation pipeline for reporting adds overhead

-- ✅ Neo4j Cypher: 3-hop traversal in milliseconds — native graph storage
MATCH (u:User)-[:PURCHASED]->(p:Product)<-[:PURCHASED]-(similar:User)
-[:PURCHASED]->(rec:Product)
WHERE u.id = 42 AND NOT (u)-[:PURCHASED]->(rec)
RETURN rec, COUNT(similar) AS strength
ORDER BY strength DESC LIMIT 10;

-- ❌ SQL recursive CTE: same 3-hop traversal — seconds at scale
WITH RECURSIVE recs AS (
  SELECT p2.product_id, 1 AS depth FROM orders o1
  JOIN orders o2 ON o1.product_id = o2.product_id AND o1.user_id != o2.user_id
  JOIN orders p2 ON o2.user_id = p2.user_id AND p2.product_id != o1.product_id
  WHERE o1.user_id = 42
)
SELECT product_id, COUNT(*) FROM recs GROUP BY product_id ORDER BY 2 DESC LIMIT 10;

-- ✅ Relational: window functions and GROUP BY — optimized for ad-hoc
SELECT user_id,
       SUM(amount) AS total_spend,
       RANK() OVER (ORDER BY SUM(amount) DESC) AS spend_rank
FROM orders
WHERE created_at >= NOW() - INTERVAL '30 days'
GROUP BY user_id;

// ❌ Broken pattern: assuming eventual consistency is safe for inventory
async function reserveSeat(seatId: string, userId: string) {
  const seat = await dynamoDB.get({ Key: { seatId } }) // eventual read!
  if (seat.available) {
    await dynamoDB.put({ Item: { seatId, userId, available: false } })
    // Two concurrent users can both read available=true → double booking
  }
}

// ✅ Correct: use conditional write (optimistic concurrency) for strong consistency
async function reserveSeat(seatId: string, userId: string) {
  await dynamoDB.put({
    Item: { seatId, userId, available: false },
    ConditionExpression: 'attribute_not_exists(userId)',
    // Fails with ConditionalCheckFailedException if already booked
  })
}

Operational overhead per database:
  + Backup configuration and monitoring
  + Separate connection pool management
  + Dedicated dashboards (latency, error rate, disk usage)
  + Failover procedures and runbooks
  + On-call expertise (team must be able to debug it at 3 AM)
  + Security patching and upgrade cycles
  + Schema migration tooling

What is your workload class?
│
├── OLTP (transactions, point reads/writes)
│     ├── Need complex queries / reporting → PostgreSQL
│     ├── Need extreme write scale (millions/s) → DynamoDB or Cassandra
│     └── Need horizontal SQL + multi-region HA → CockroachDB
│
├── Search (full-text, faceted, fuzzy)
│     └── Elasticsearch / OpenSearch
│
├── Graph (3+ hop relationship traversal)
│     └── Neo4j
│
├── Cache / Session (sub-millisecond, loss-tolerant)
│     └── Redis
│
├── Vector / Similarity (ANN search on embeddings)
│     ├── < 10M vectors → pgvector (inside Postgres)
│     └── > 10M vectors → Pinecone / Qdrant / Milvus
│
└── Time-series / Append-only (sensor, metrics, logs)
      ├── Moderate scale → PostgreSQL + BRIN index or TimescaleDB
      └── High write volume + distributed → Cassandra (TWCS compaction)