Database Types

"We'll use a database" is not an architecture decision — it's punting. Every system design interview eventually asks: Postgres or Cassandra? Redis or DynamoDB? Mongo or Elasticsearch? The answer is never "it depends" alone — it's "this workload needs X because Y."

This page is the decision matrix. Learn the shape of each, the failure modes of each, and which real workloads fit each.

1. Do you need ACID transactions across multiple rows? → Postgres/MySQL. Nothing else does this well.
2. Do you need single-key lookups in < 1ms? → Redis (in-memory) or DynamoDB (managed).
3. Do you need > 100k writes/sec on one logical dataset? → Cassandra or DynamoDB. Avoid SQL sharding unless the team has scar tissue.
4. Do you need full-text search with relevance scoring? → Elasticsearch. Alongside, never instead of, a source-of-truth DB.
5. Is your data graph-shaped (traversals > joins)? → Neo4j or a graph extension.
6. Is your data timestamped metrics? → Prometheus (metrics) or TimescaleDB (analytics).
7. Default for greenfield apps? → Postgres. Can handle most workloads up to mid-scale and the migration path is well-trodden.

# LSM = Log-Structured Merge tree. Writes: append to memtable + WAL.
# Reads: check memtable → bloom filters → SSTables from newest to oldest.

class LSM:
    def __init__(self, memtable_limit=10**6):
        self.memtable = {}  # sorted map (in-mem B-tree in real engines)
        self.wal = WAL("lsm.wal")
        self.sstables = []  # list of on-disk immutable files, newest first

    def put(self, k, v):
        self.wal.append((k, v))
        self.memtable[k] = v
        if len(self.memtable) >= 10**6:
            self._flush()

    def _flush(self):
        sst = SSTable.from_dict(self.memtable)
        self.sstables.insert(0, sst)
        self.memtable = {}
        self.wal.rotate()

    def get(self, k):
        if k in self.memtable: return self.memtable[k]
        for sst in self.sstables:
            if k in sst.bloom:  # cheap probabilistic check
                v = sst.get(k)
                if v is not None: return v
        return None

# Compaction merges older SSTables; Cassandra, RocksDB, LevelDB all do this.

At scale, you don't pick one DB. You pick many. A realistic stack for a consumer social app:

• Postgres — users, payments, account settings (relational + ACID)
• Cassandra — feed posts, activity events (write volume + eventual OK)
• Redis — session tokens, feed caches, rate limiters (sub-ms reads)
• Elasticsearch — search (posts, users, hashtags)
• S3 — media (images, video) with CDN in front
• Kafka — event bus between all of the above

Each has one job it does 10× better than the alternatives. Trying to force Postgres to do search, or Elasticsearch to hold the ledger, loses ~90% of the specialized DB's value.

Every problem in this portfolio picks 2–5 DBs. The decision table in §4 is the framework; the specific choices are justified on each problem page.