Choosing Databases Like a Senior Engineer

---

---

Section 1: The System Design DB Selection Framework

1. Access Patterns — Read-heavy (99:1) vs Write-heavy (80:20)? Key-Value lookups vs Complex Joins vs Aggregations?
2. Data Structure — Rigid, structured schemas with foreign keys vs Unstructured / Evolving JSON documents?
3. Scalability Model — Vertical Scaling (Scale Up) vs Horizontal Scaling (Scale Out / Sharding)?
4. Consistency & ACID — Strict Serializability (Banking) vs Eventual Consistency (Social Media Likes / Feed)?
5. Query Complexity — Simple Primary Key lookups vs Text Search / Range Queries vs Multi-hop Graph Traversal?

The CAP & PACELC Theorem Decision Rule

• CAP Theorem: In the presence of a Network Partition (P), you must choose between Consistency (C) or Availability (A).
• PACELC Theorem: If Partition (P): choose between Availability (A) and Consistency (C); Else (E): choose between Latency (L) and Consistency (C).

---

Section 2: Database Paradigms Deep-Dive

2.1 Relational Databases (PostgreSQL, MySQL)

• Underlying Engine: B+ Trees for indexing. Disk-oriented storage with Write-Ahead Logging (WAL).
• Key Strengths: Full ACID (Atomicity, Consistency, Isolation, Durability) guarantees, complex SQL JOINs, Foreign Key integrity constraints, rich indexing (B-Tree, Hash, GIN, GiST).
• Weaknesses: Horizontal sharding is complex and requires application-level logic or proxy tools (Vitess, Citus). High write-amplification under massive append rates.
• Best Used For: Financial transactions, user accounts, order management, billing systems.

---

2.2 NoSQL Document Stores (MongoDB, Couchbase)

• Underlying Engine: B-Trees or WiredTiger storage engine (supports compressed B-Trees & In-Memory caching).
• Key Strengths: Schema-flexible JSON/BSON storage. Embedded documents eliminate complex JOIN overhead for single-entity aggregates. Native auto-sharding and replica sets.
• Weaknesses: No native multi-table constraints. Poor performance for deep many-to-many relationships (requires $lookup which is slow).
• Best Used For: User profiles, product catalogs, content management, dynamic forms.

---

2.3 NoSQL Key-Value Stores (Redis, Memcached, DynamoDB)

• Underlying Engine: In-memory Hash Tables & SkipLists (Redis) or Distributed Hash Ring / LSM-Tree (DynamoDB).
• Key Strengths: Sub-millisecond latency (~1ms). High throughput (100k+ QPS per node). Advanced data structures (Sorted Sets, Hashes, HyperLogLogs, Bitmaps).
• Weaknesses: Limited query capability (lookup strictly by Key). Memory is expensive compared to SSDs (for Redis).
• Best Used For: Caching, session storage, distributed locks, pub-sub messaging, real-time leaderboards.

---

2.4 NoSQL Wide-Column Stores (Cassandra, ScyllaDB)

• Underlying Engine: LSM-Trees (Log-Structured Merge-Trees). Sequential disk writes to Commit Log + Memtable, flushed to immutable SSTables.
• Key Strengths: Blazing fast Write Throughput (O(1) append writes). Masterless architecture (no single point of failure). Tunable Consistency (e.g. LOCAL_QUORUM).
• Weaknesses: No JOINs, no foreign keys, no secondary indexing (poor query flexibility). Anti-pattern to run range scans across partitions.
• Best Used For: Time-series logs, chat message history, IoT telemetry, clickstream analytics.

---

2.5 Search Engines & Vector DBs (Elasticsearch, Pinecone)

• Underlying Engine: Inverted Index (Lucene) for text search; HNSW (Hierarchical Navigable Small World) graph indexing for Vector Embeddings.
• Key Strengths: Full-text search, fuzzy matching, TF-IDF / BM25 ranking, spatial/geo queries, AI vector similarity search (Cosine, Euclidean distance).
• Weaknesses: High memory footprint, eventual consistency, not suitable as a primary transactional source-of-truth database.
• Best Used For: E-commerce search bars, log aggregation (ELK Stack), AI RAG (Retrieval-Augmented Generation) search.

---

2.6 Graph Databases (Neo4j, Amazon Neptune)

• Underlying Engine: Index-Free Adjacency (Nodes store direct physical pointers to adjacent neighbor nodes in memory).
• Key Strengths: Constant time O(1) pointer-hopping traversal regardless of total graph size. Eliminates recursive O(N^k) SQL JOIN bottlenecks.
• Weaknesses: Hard to shard horizontally across multiple nodes without cross-network hop latency.
• Best Used For: Social network friend connections, fraud detection networks, recommendation graphs, knowledge graphs.

---

2.7 Time-Series Databases (TimescaleDB, InfluxDB)

• Underlying Engine: Time-bucketed partitioning (Hypertables) + automatic data compression (Gorilla XOR compression).
• Key Strengths: High-frequency time-stamped write ingestion, automatic data retention policies, fast time-bucket aggregations (e.g. 5-minute averages over millions of rows).
• Weaknesses: Poor performance for frequent random updates to old historical records.
• Best Used For: Server CPU/RAM monitoring (Prometheus), stock market tick data, IoT sensor streams.

---

2.8 Distributed SQL / NewSQL (CockroachDB, Spanner)

• Underlying Engine: Distributed Consensus (Raft or Paxos) + Multi-Version Concurrency Control (MVCC) + Range Partitioning (RocksDB engine).
• Key Strengths: Best of both worlds: Full ACID compliance + horizontal scaling out-of-the-box. Global serializability without single-master bottleneck.
• Weaknesses: Higher write latency than raw Key-Value or Cassandra due to Raft consensus rounds.
• Best Used For: Global banking systems, multi-region ledger apps, global reservation systems.

---

Section 3: Real-World Architecture System Case Studies

3.1 Banking / Payment Processing System

• Database Pick: PostgreSQL (Single-Region) or CockroachDB (Multi-Region Distributed SQL).
• Why?
  • Strong ACID Isolation (REPEATABLE READ or SERIALIZABLE).
  • Money transfers require atomic two-table balance updates: UPDATE accounts SET balance = balance - 100 WHERE id = 1 AND UPDATE accounts SET balance = balance + 100 WHERE id = 2.
  • Foreign key constraints prevent orphaned transaction records.

---

3.2 WhatsApp / Discord Chat Message Storage

• Database Pick: Apache Cassandra / ScyllaDB.
• Why?
  • Write-Heavy Workload: Hundreds of thousands of messages sent per second.
  • Partition Key Strategy: Partition Key = channel_id or conversation_id, Clustering Key = message_id (TimeUUID).
  • LSM-Tree Engine: Writes hit memory (Memtable) and append-only disk (CommitLog) instantly without random disk seeks.

---

3.3 Uber Real-Time Driver Location & Dispatch

• Database Pick: Redis (Geospatial HSET / H3 Index).
• Why?
  • Drivers send GPS updates every 4 seconds (1M+ driver updates/sec).
  • Requires sub-millisecond range queries: GEOSEARCH drivers RADIUS 5 km FROM rider_location.
  • In-memory execution avoids storage disk I/O bottlenecks.

---

3.4 E-Commerce Search & Product Catalog

• Database Pick: MongoDB (Product Metadata Catalog) + Elasticsearch (Search Engine).
• Why?
  • MongoDB for Catalog: Different product types (Laptops, Shoes, Books) have completely different attributes (RAM vs Shoe Size). Schema-less JSON allows varied attributes.
  • Elasticsearch for Search Bar: Fast inverted index search across millions of products with typo tolerance ("iphne" -> "iPhone").

---

3.5 Social Network Recommendation & Fraud Graph

• Database Pick: Neo4j / Amazon Neptune.
• Why?
  • Finds mutual friends, 2nd-degree connections, and fraudulent user rings.
  • Query in Cypher: MATCH (user:User {id: '123'})-[:FRIEND]->()-[:FRIEND]->(fof) RETURN fof.
  • Memory pointer-hopping executes in milliseconds instead of minute-long SQL recursive JOINs.

---

Section 4: Database Trade-offs

PostgreSQL vs MongoDB

• Choose PostgreSQL for relationships, joins, constraints, and strong transactions.
• Choose MongoDB for flexible document-shaped data and rapidly changing schemas.
• Trade-off: PostgreSQL gives stronger relational guarantees; MongoDB makes document-based scaling and schema evolution simpler.

PostgreSQL vs Cassandra

• Choose PostgreSQL for flexible queries, joins, and transactional correctness.
• Choose Cassandra for predictable, write-heavy workloads that need high availability.
• Trade-off: PostgreSQL is easier to query; Cassandra scales distributed writes better but requires careful partition-key design.

Redis vs a Primary Database

• Choose Redis for caching, sessions, rate limits, locks, and short-lived counters.
• Choose a primary database for durable business data and source-of-truth records.
• Trade-off: Redis is much faster, but its data can expire, be evicted, or become unavailable. Always define the database fallback.

Elasticsearch vs a Primary Database

• Choose Elasticsearch for full-text search, ranking, filtering, and aggregations.
• Choose a primary database for transactions and canonical records.
• Trade-off: Elasticsearch provides better search performance, but indexing can lag and the index must be rebuildable from the source of truth.

Kafka vs Database Writes

• Choose Kafka for event streaming, buffering, replay, and multiple asynchronous consumers.
• Choose database writes for immediate transactional state changes.
• Trade-off: Kafka decouples services and absorbs traffic spikes, but consumers need idempotency and events are commonly delivered at least once.

Section 5: Database Selection Flowchart