Module 07 · Databases · Lesson 1 of 13
Common database types
The database families you will actually choose between, what each is shaped for, and the one question that picks among them: what does the access pattern look like? A map of the module before the deep dives.
Why it matters
"Which database?" is the most common decision in a design interview and the most commonly fumbled, because candidates name a product before naming the access pattern. Every database family is a bet on a shape of data and a shape of query. Once you can say what shape your data and queries have, the choice mostly makes itself, and the rest of this module is about understanding each bet well enough to defend it.
The idea
A database is a storage engine (how bytes hit disk and get found again) plus a data model (how you think about the data) plus a query interface. The families below differ on all three, but the useful way to tell them apart is by what they make cheap.
| Family | Data shape | Makes cheap | Makes expensive | Examples |
|---|---|---|---|---|
| Relational | Tables with rows and typed columns, relations via keys | Ad-hoc queries, joins, transactions across rows, integrity constraints | Horizontal write scaling, schema changes at huge scale | Postgres, MySQL |
| Document | Self-contained JSON-like documents | Reading or writing a whole entity at once, flexible per-record shape | Joins across documents, multi-document transactions | MongoDB, DynamoDB (document mode) |
| Key-value | Opaque value behind a key | Get and put by key at very high throughput and low latency | Anything that is not a key lookup | Redis, DynamoDB, Riak |
| Wide column | Rows with a partition key and sorted clustering columns, sparse columns | Writes at massive scale, range scans within a partition | Ad-hoc queries, joins, secondary access paths | Cassandra, HBase, Bigtable |
| Graph | Nodes and edges with properties | Traversals: friends of friends, paths, neighbourhoods | Aggregates over the whole graph, bulk analytics | Neo4j, Neptune |
| Time series | Timestamped measurements per series | Append-heavy ingest, time-window aggregates, retention and downsampling | Updates, point lookups by non-time attributes | InfluxDB, TimescaleDB, Prometheus |
| Search | Inverted index over text and fields | Full-text relevance, faceting, fuzzy matching | Being the source of truth, transactions | Elasticsearch, OpenSearch, Meilisearch |
| Vector | High-dimensional embeddings | Nearest-neighbour similarity at scale | Exact results, frequent updates of large indexes | pgvector, Pinecone, Milvus |
The questions that pick
- 1
What is the unit you read and write?
A whole entity at a time suggests document or key-value. Pieces of many entities joined together suggests relational. - 2
What is the ratio and scale of reads to writes?
Hundreds of thousands of writes per second with simple reads is wide-column territory. Complex reads with modest writes is relational. - 3
What guarantees do writes need?
Money and inventory want transactions and constraints. Logs and events tolerate eventual consistency. - 4
What queries will you not predict?
If analysts will ask questions you cannot foresee, keep a relational or warehouse copy; schemaless stores punish unforeseen queries. - 5
What is the special shape?
Text relevance, time windows, graph hops and similarity each have a store built for exactly that, usually fed from the core rather than replacing it.
Polyglot persistence
Real systems run several databases, each for the access pattern it serves best, synchronised through events or change data capture. The discipline is to have one source of truth per entity and treat the specialists as derived, rebuildable views. Three stores you can rebuild are manageable; three stores that each own some of the truth is a consistency problem you will debug for years.
Trade-offs
Relational databases are the safest default and scale further than their reputation suggests, at the cost of harder horizontal write scaling. Specialist stores win on their one shape and lose everywhere else, and each one adds operational surface. The common mistake is choosing a NoSQL store for scale the system will never reach, and paying for missing joins and transactions every day until then.
In an interview
Name the pattern, then the store: "Orders need transactions and are queried in many ways, so Postgres is the source of truth. Product search needs relevance and facets, so a search index is fed from Postgres by change data capture. Session data is key lookups at high rate, so Redis." Then the default defence: "Start relational unless a concrete pattern proves it cannot cope."
Check yourself
A startup picks a wide-column store for its first product 'so it can scale later'. Six months in, every feature request involves a query the data model cannot serve without a full scan. What went wrong?