Units 03–05 taught you how to reason about storage. This is the catalog: the databases you'll actually name in interviews, each with its read/write profile, consistency, the query patterns it supports, what it's best at, and the gotcha that bites people.
⏱ 30 min · referencePrereq: Units 03–06Use as: a lookup you revisit, not a one-time read
How to use this unit
Skim all the cards once to build a mental map of the landscape.
For each, lock in one sentence: "I'd use it when ___, but watch out for ___."
Remember the meta-rule: name a database and the access pattern that justifies it.
The lens for every database
Ask five things: (1) read vs write profile, (2) consistency — ACID or eventual, (3) query patterns it makes cheap (point lookup? range? join? full-text? aggregation?), (4) how it scales (up vs out), (5) the gotcha. Every card below is organized around these.
Relational (OLTP)
PostgreSQL
The versatile default. A true Swiss-army knife that has quietly absorbed half of NoSQL.
R/W: balanced; scales up easily, out via read replicas / Citus.
Consistency: full ACID, MVCC, serializable available.
Consistency: single-primary strong per key; async replicas; persistence optional (RDB/AOF).
Queries: O(1) by key + data structures — sorted sets (leaderboards/feeds), sets, hashes, streams, TTLs, pub/sub, Lua.
Best for: cache, sessions, rate limiters, leaderboards, queues, ephemeral counters.
Gotcha: RAM-limited & pricey per GB; hot keys; persistence is a real durability tradeoff — treat as cache, not source of truth by default.
DynamoDB
AWS's managed, serverless key–value / wide-column. Scales to anything, you never see a server.
R/W: both, single-digit-ms at any scale; pay per capacity/request.
Consistency: tunable — eventual (default) or strong reads; transactions supported.
Queries: partition key (+ optional sort key); GSIs for other access paths. No joins, no ad-hoc queries.
Best for: known access patterns at huge scale, serverless stacks, predictable latency.
Gotcha: you must model around your queries up front (single-table design); hot partitions; scans are slow/costly; GSIs are eventually consistent.
Wide-column
Cassandra · ScyllaDB
Masterless rings built for relentless writes and always-on availability.
R/W:write-optimized (LSM-tree), linear horizontal scale; multi-datacenter.
Consistency: tunable per query (AP-leaning); no multi-row transactions.
Queries: CQL; query-first modeling — partition key + clustering columns give fast per-partition range scans. No joins, weak secondary indexes.
Best for: time-series, event/IoT logs, messaging, write-heavy at scale, no single point of failure.
Gotcha: no ad-hoc queries — model the table per query; deletes create tombstones that hurt reads; large partitions are dangerous. (ScyllaDB = C*-compatible, C++ rewrite, faster.)
Bigtable · HBase
Google-lineage wide-column for petabyte operational + analytical workloads.
R/W: very high both; sorted by row key → excellent range scans.
Consistency: strong per row; no cross-row transactions.
Queries: single row-key lookups & range scans only; no secondary indexes or joins.
Best for: massive time-series, personalization/feature stores, analytics serving.
Best for: variable/nested objects read as a unit (catalogs, profiles, content), fast iteration.
Gotcha: schema-flexibility becomes schema-drift; keep the working set in RAM or reads fall off a cliff; don't reach for it just to "avoid SQL" — model the access pattern either way.
Search
Elasticsearch · OpenSearch
An inverted index for full-text search and log analytics — not a system of record.
R/W: read-heavy search + high-ingest logs; near-real-time (slight index delay).
Consistency: eventually consistent; not your source of truth — index from your primary DB.
Queries: full-text relevance, fuzzy, faceting/aggregations, geo. Poor at joins/transactions.
Best for: search boxes, autocomplete, the "E" in ELK log analytics, observability.
Gotcha: RAM-hungry and operationally heavy; mapping changes mean reindexing; treat it as a derived view you can rebuild.
Analytics (OLAP) & warehouses
ClickHouse · Apache Druid
Columnar OLAP engines that aggregate billions of rows in milliseconds.
R/W: huge append-only ingest + blazing analytical reads; poor at point updates/deletes.
Consistency: eventual; not transactional.
Queries: massive GROUP BY / scans / rollups over columns; real-time dashboards.
Best for: product analytics, real-time metrics, event exploration at scale.
Gotcha: not OLTP — no per-row edits; wrong tool for user-facing transactional writes.
Snowflake · BigQuery · Redshift
Cloud data warehouses: separate storage & compute, near-infinite analytical scale.
R/W: batch/bulk load + heavy ad-hoc analytical queries; not low-latency serving.
Queries: full SQL over enormous datasets; BI, ETL/ELT, joins across fact/dim tables.
Best for: BI dashboards, data science, company-wide analytics (the OLAP side of OLTP↔OLAP).
Gotcha: seconds-to-minutes latency and per-query/compute cost — never put one on a user's hot path.
Specialized: time-series & graph
TimescaleDB · InfluxDB · Prometheus
Purpose-built for timestamped metrics and events.
R/W: write-heavy ingest; time-ranged reads; auto downsampling/retention.
Queries: time-bucketed aggregations, rollups. (Timescale = Postgres extension, so you keep SQL.)
Best for: monitoring, IoT, financial ticks, anything indexed by time.
Gotcha:high cardinality (too many unique label combos) is the classic killer, especially Prometheus.
Neo4j · Amazon Neptune
Nodes and edges, built for traversing relationships.
R/W: read-heavy traversals; moderate scale.
Consistency: ACID (Neo4j).
Queries: multi-hop traversals in Cypher/Gremlin ("friends of friends who liked X") that would be brutal joins in SQL.
Best for: social graphs, fraud rings, recommendations, knowledge graphs.
Gotcha: sharding a graph is genuinely hard; niche — don't force normal relational data into it.
Not a database, but it'll come upKafka is a durable, replayable log, not a database — great for streaming/event backbones (Unit 08), wrong for random-access queries. Don't answer "where do I store it?" with "Kafka." SQLite is the opposite end: an embedded single-file DB (in-process, one writer) — perfect for local/edge/mobile and surprisingly capable, but not a concurrent multi-writer server.
Pick-by-need cheat sheet
If you need…
Reach for
Because
Transactions / correctness (money, orders)
PostgreSQL / MySQL
ACID, joins, mature.
Relational + horizontal scale + global
CockroachDB / Spanner
Distributed SQL keeps ACID.
Cache / sessions / leaderboards / rate limit
Redis
In-memory data structures, sub-ms.
Huge scale, known key-based access, serverless
DynamoDB
Predictable latency, no ops.
Write-heavy, time-series/events, always-on
Cassandra / ScyllaDB
LSM writes, masterless, linear scale.
Flexible/nested documents, fast iteration
MongoDB
Schema-flex + rich queries.
Full-text search / log analytics
Elasticsearch
Inverted index, relevance, facets.
Real-time analytics over billions of rows
ClickHouse / Druid
Columnar aggregation.
BI / company-wide ad-hoc analytics
Snowflake / BigQuery
Elastic warehouse, storage≠compute.
Metrics / monitoring / IoT
Timescale / Influx / Prometheus
Time-bucketed, retention, downsampling.
Relationship traversals
Neo4j / Neptune
Multi-hop beats recursive joins.
Big blobs (images/video)
Object store (S3), not a DB
URL in the DB, bytes in the store (Unit 11).
Polyglot persistence is normal Real systems use several of these together — e.g., Postgres for accounts, Redis for cache, S3 for media, Elasticsearch for search, ClickHouse for analytics. Interviewers love "the right store per access pattern," but justify each addition; every new datastore is another thing to run, sync, and keep consistent.
Cross-cutting gotchas interviewers probe
"Just use NoSQL, it scales" — the trap
NoSQL scales a specific access pattern, not magic. You trade joins, ad-hoc queries, and often transactions for it. If you can't state the exact query the table serves, you're not ready to pick NoSQL. Many "we need NoSQL for scale" cases are solved by Postgres + cache + read replicas first.
OLTP vs OLAP — don't mix them
Transactional stores (Postgres, Dynamo) serve many tiny reads/writes with low latency. Analytical stores (ClickHouse, Snowflake) scan huge ranges. Running big analytics on your OLTP primary competes with user traffic and is slow (row storage). Pipe events to a warehouse instead (Unit 16).
Secondary indexes aren't free (especially in NoSQL)
In relational DBs indexes speed reads but slow writes and use space. In Dynamo/Cassandra, "secondary indexes" are limited, can be eventually consistent, or are really separate denormalized tables you maintain yourself. Plan the access paths up front.
Eventual consistency shows up as real bugs
Read-your-writes failures, GSI lag, replica lag, search-index delay. Decide per feature what staleness is acceptable (Unit 05) and route reads accordingly.
Quick check
You need to power a "friends-of-friends who liked this" query across a large social graph. Best fit?
Graph database. Multi-hop relationship traversals explode into expensive recursive joins in relational stores; graph engines make them a first-class, index-free-adjacency operation. (At extreme scale many still precompute with a wide-column store, but graph is the on-point answer.)
Quick check
A team wants sub-10ms key lookups at massive scale on AWS with zero servers to manage, and their access patterns are fixed. Best fit?
DynamoDB. Fixed access patterns + huge scale + serverless + predictable single-digit-ms latency is exactly its sweet spot. Snowflake is an analytical warehouse (seconds, not ms); Neo4j is for traversals. The catch you'd name: you must design the table (and GSIs) around those queries up front.
Practice before you move on
For your photo-sharing capstone, assign a concrete database to each and justify with the access pattern + one gotcha: (a) user accounts & auth, (b) the home-feed cache, (c) uploaded photos, (d) full-text caption search, (e) product analytics on views/likes, (f) the social follow-graph for "people you may know." You should reach for 4–5 different stores — that's polyglot persistence done right.