System Design Interview: Complete Preparation Guide

SQL databases (PostgreSQL, MySQL) are best for: • Structured data with complex relationships • Transactions requiring ACID guarantees (financial systems) • Complex queries with joins and aggregations • Moderate scale (millions of rows, not billions) NoSQL databases come in several types: • Document stores (MongoDB) — flexible schemas and hierarchical data • Key-value stores (Redis, DynamoDB) — high-throughput simple lookups • Wide-column stores (Cassandra, HBase) — time-series data and write-heavy workloads at massive scale • Graph databases (Neo4j) — relationship-heavy data (social networks, recommendation engines) Decision matrix: • If you need joins → SQL • If you need horizontal scaling beyond a single machine → NoSQL • If you need flexible schemas that evolve frequently → document store • If you need sub-millisecond latency on simple lookups → key-value store

The three guarantees: • Consistency — every read receives the most recent write • Availability — every request receives a response • Partition Tolerance — the system continues operating despite network partitions Since network partitions are inevitable in distributed systems, the real choice is between CP and AP. CP systems (HBase, MongoDB with strong consistency): • Return errors or timeouts during partitions rather than stale data • Use for: financial transactions, inventory systems AP systems (Cassandra, DynamoDB in eventual consistency mode): • Always respond but may return stale data during partitions • Use for: social media feeds, analytics, caching In practice: Most systems mix strategies — a payment service might be CP while the product recommendation service is AP.

1. Cache-Aside (Lazy Loading): • Application checks cache first; on miss, reads from DB and populates cache • Most common pattern — good for read-heavy workloads • Risk: cache stampede (many concurrent misses for the same key) 2. Write-Through: • Application writes to cache and DB simultaneously • Ensures cache is always up-to-date — higher write latency but eliminates stale reads • Good for write-moderate workloads where consistency matters 3. Write-Behind (Write-Back): • Application writes to cache only; cache asynchronously writes to DB in batches • Lowest write latency but risks data loss if cache fails before flushing • Good for write-heavy workloads where some data loss is acceptable (analytics, logging) 4. Read-Through: • Cache automatically loads data from DB on miss (cache-as-a-service) • Simplifies application code but couples cache to DB Eviction policies: • LRU (least recently used) — best for most workloads • LFU (least frequently used) — best for workloads with hot keys • TTL-based — best for data with known freshness requirements