Cruxly › System design › Databases: Choosing One

Data models: what each family is built for, and where it fails

every family is a data structure plus an access path; learn which queries it makes cheap

Each database family is a data structure with one access path that is fast and many that are slow. Relational is built around sorted indexes and joins, document around whole aggregates, key-value around one key, wide-column around a partition key and a sort order, time-series around time partitions, search around an inverted index, vector around a neighbour graph, and graph around stored edges. Knowing the structure tells you which query is cheap, which is a scan, what the family is paired with, and where it stops.

Every family is a structure plus an access path

A family is not a brand or a syntax. It is a data structure and the access path that structure makes fast. Ask two questions of any store: what is the one lookup it does cheaply, and what does it do when you ask something else? The answer to the second is almost always "scan".

The recurring lesson: relational databases keep absorbing the others. Stonebraker and Pavlo's 2024 survey notes that NoSQL systems added SQL and ACID, relational systems added JSON, and they expect vector and graph support to follow.

:::key Say the structure, then the query "It is an inverted index, so word search is cheap and a range by price is not what it is for" is a complete, defensible sentence. "It is good for search" is not. :::

Relational and document: where the join happens

Normalisation stores each fact once and links rows by key. Denormalisation copies facts so a read needs fewer lookups. Neither is right in general. They move work between read time and write time.

What a join really costs. For an indexed join, the database walks one side and does an index lookup on the other for each row. Assume a user has 20 orders with 3 items each: that is one range read for the orders, 20 index lookups for the items, and 60 for the products, so on the order of 80 index lookups, each a few page reads. That is cheap in one database. It gets expensive in three situations:

• the join column has no index, so every outer row triggers a scan
• the result is huge and unselective, so you are joining millions of rows
• the tables live on different shards, so lookups become network calls and coordination

OpenAI's 2026 write-up of its Postgres setup describes one extremely costly query that joined 12 tables, whose spikes were behind past high-severity incidents; it advises avoiding complex multi-table joins on the hot path. Joins are not slow. Unbounded, unindexed or cross-shard joins are.

Document vs relational is mostly where the join happens. A document store does the join when you write: the order, its lines and the product names go into one document, and a read is one lookup. A relational store does the join when you read. Stonebraker and Pavlo note the old problems with prejoining: duplicated data when the relationship is not one-to-many, no guarantee it is faster than the join, and no data independence. The duplicated data is the copy problem in the video.

MongoDB's modelling guide gives the rule: embed when data is read together or has a contains relationship, reference when it grows without bound or is shared. A product catalogue is shared, so reference it. Order lines are bounded and read with the order, so embed them. Copy on purpose only what must stay frozen, such as the price paid, because the price paid is a fact about that order that must not follow the catalogue.

{ "_id": "o-1001", "user": 7, "placed": "2026-10-02",
  "lines": [ { "sku": "S-9", "name": "Red shoe", "qty": 1, "price_paid": 49.00 } ] }

MongoDB does have multi-document transactions (60 second default limit), but embedding means you rarely need one.

:::key Do not say "document stores avoid joins" Say "a document store does the join at write time and stores the result, which makes reads cheap and rewrites expensive". :::

Schema on write versus schema on read

Schema on write means the database enforces a declared structure when data goes in: column types, NOT NULL, foreign keys, CHECK constraints. Schema on read means the store accepts any shape and the code that reads it decides what the shape means. Either way a schema exists. The question is where it is enforced and who finds out when it is violated.

Two corrections interviewers like. First, "NoSQL means no schema" is wrong: CQL tables in Cassandra declare their columns, and a document store's schema simply lives in your application code where nothing enforces it. Second, flexibility is not free: a field added in version two means every reader needs a branch for documents without it, forever, or a backfill job.

You can choose per column rather than per database. In Postgres, jsonb gives schema-on-read for one column inside a table whose other columns are enforced. Per the Postgres docs, jsonb does not preserve key order or duplicate keys. Keep the fields you filter on, join on or constrain as real columns, and put the long tail of varying attributes in the jsonb column.

:::key The schema never disappears Ask "where is it enforced, and who finds out first?" For money and identity you want the answer to be "the database, at write time". :::

Key-value and wide-column: the key is the design

Key-value is the simplest family: one key, one value. Stonebraker and Pavlo note that a plain key-value store has no secondary indexes and leaves joins to the application, which suits caches and sessions and is risky for multi-field records; they add that some systems, DynamoDB among them, grew richer record models. DynamoDB is the durable, partitioned example, with its own hard numbers: items up to 400 KB, a partition key value up to 2,048 bytes, and a per-partition ceiling of 3,000 read units and 1,000 write units a second, as documented by AWS.

Wide-column descends from Bigtable: a sorted map from row, column and timestamp to a value, with transactions only inside one row, per the Bigtable paper. In Cassandra the primary key has two jobs. The partition key picks the node, and the clustering key sorts rows inside the partition. You design one table per query and denormalise, because there are no joins to lean on.

CREATE TABLE messages (
  chat_id int, day date, ts timeuuid, body text,
  PRIMARY KEY ((chat_id, day), ts)
) WITH CLUSTERING ORDER BY (ts DESC);
-- latest 50 in a chat today:
SELECT * FROM messages WHERE chat_id = 7 AND day = '2026-10-11' LIMIT 50;

The key decides three things:

For the hot key, take an assumed 2,400 writes a second on one DynamoDB key with items under 1 KB. One partition takes 1,000 write units a second, so it throttles, and more table capacity does not help. Three suffix shards carry 800 each, and a read asks all three. You pay with scatter-gather reads.

Honest limits: Cassandra deletes write tombstones that compaction removes later, so a table used as a queue degrades; its linearizable writes (lightweight transactions) use Paxos and cost accordingly.

:::key Name the partition key and the clustering key In an interview, the first line of a wide-column design is the primary key. If you cannot say how large one partition gets, the design is not finished. :::

Time-series: partition by time, watch the labels

Time-series data is append-mostly, written in time order, queried by recent window or by aggregate over a range, and aged out by policy. The structure that fits is partitioning by time, often with compression of older partitions and rollups of old detail into coarser aggregates.

What makes it cheap:

• Recent windows: the last hour usually touches only the newest partition.
• Retention: the Postgres docs say that dropping or detaching a partition is far faster than a bulk delete and avoids the vacuum work a bulk delete leaves. TimescaleDB, a Postgres extension, calls its time partitions chunks and documents dropping them.
• Aggregates: a rollup table or continuous aggregate answers "per-hour average" without rescanning raw points.

What makes it expensive:

Cardinality is the failure interviewers probe. In Prometheus a metric name plus its label set identifies a series, and the Prometheus docs warn against labels with unbounded values such as user ids or email addresses. Assume 3 hosts and 5 routes: 15 series. Add a user_id label with 1 million users and you have 15 million. Put per-user detail in logs or an event store instead.

http_requests_total{host="web-3", route="/cart", status="200"}  12894  @ 2026-10-11T10:00:00Z

Typical pairing: an ingest buffer in front and a dashboard and alerting layer on top. There is no trustworthy universal threshold for needing a dedicated time-series database. The honest signal is a partitioned Postgres table that can no longer absorb the ingest rate or query history cheaply.

:::key Retention is a design input Ask "how long do we keep raw points, and at what resolution afterwards?" before choosing anything. It decides partitioning, rollups and cost. :::

Search: an inverted index that lags the truth

A search engine is built around an inverted index, as Lucene-based systems such as Elasticsearch and OpenSearch are. At index time, text is split into terms; the index maps each term to the list of documents containing it. A query for "red shoes" looks up both terms and intersects the lists, then ranks the survivors by relevance, for instance with term frequency scoring.

{ "query": { "match": { "title": "red shoes" } } }

Cheap: relevance-ranked text queries, prefix and fuzzy matching, filters and facet-style counts over the same documents. Expensive or wrong: exact transactional reads, joins, being the only copy of the data.

Eventual consistency. In Elasticsearch a new document becomes searchable after a refresh. The documented default index.refresh_interval is 1 second in Elastic Stack (5 seconds on Serverless, which is also its minimum). If the setting is not set explicitly, a shard with no search traffic for 30 seconds skips background refreshes until the next search arrives, and that search triggers the refresh. So: the product is written to the database, a pipeline copies it to the index, and a search in the gap does not find it. That is fine for search results and wrong for checkout, which must read price and stock from the database.

It is a derived store. The pattern from earlier in this topic: one system of record, other stores derived and rebuildable. Write to the database, then feed the index from a change stream or outbox, not by a second write in application code, which can fail between the two. If the index is lost, create a new one and bulk load from the source, then swap an alias. A search index that cannot be rebuilt has quietly become your source of truth.

You may not need a separate engine yet. Postgres full text search stores a tsvector and uses a GIN index, which the docs describe as the preferred text search index; GIN holds an index entry per word with a list of locations, the same inverted idea.

Use Postgres full text for modest data and simple matching, where seeing data in the same transaction matters. Add a search engine when relevance tuning or search traffic needs its own scaling and should not compete with orders.

:::key The test for a derived store "If this node disappears, can I rebuild it from the system of record?" If the answer is no, say so and fix it. :::

Graph and vector: traversal depth and recall

Graph. A property graph stores nodes and edges, each with properties. Neo4j's documentation says relationships are stored natively alongside the data rather than computed by joins. Treat that as a design claim to reason from, not proof of speed: the benefit is following links on long chains. The cheap query is a traversal of variable depth: everyone within three hops of Asha.

MATCH (a:Person {name: 'Asha'})-[:FRIEND*1..3]->(f) RETURN DISTINCT f

In SQL the same question is a self join per hop, or a recursive query. For two or three hops on a modest graph, an index on the edge columns makes that fine. Stonebraker and Pavlo predict that OLTP graph applications will be largely served by relational systems, and SQL:2023 added property-graph queries (SQL/PGQ). They grant that Neo4j's pointer-style edges suit long chains, and say the open question is whether enough long-chain workloads exist to justify a separate system. Reach for a graph store when deep, variable-length traversals are the core query on a very large graph. Its limits: aggregations and bulk scans are not its strength, and it is one more system to run, usually beside a relational system of record.

Vector. An embedding turns text or an image into a list of numbers; similar items sit near each other. The query is "nearest K to this vector". Exact search compares against every row. Approximate indexes avoid that:

Both are approximate, so a vector search has recall: the share of true nearest neighbours it returns. You measure it against exact search. Numbers from the pgvector README: a vector takes 4 bytes per dimension plus 8, so a 1,536-dimension embedding (an assumed, common size) is 6,152 bytes and 1 million rows is about 6 GB before the index. Vectors can be stored up to 16,000 dimensions but indexed only up to 2,000 with the vector type.

SELECT id FROM items WHERE in_stock AND price < 50
ORDER BY embedding <-> '[0.12, -0.4, ...]' LIMIT 10;

The filter trap. Approximate indexes apply the filter after the index scan. In pgvector with the default hnsw.ef_search of 40, a filter matching 10 percent of rows leaves about 4 of the 40 candidates on average. You asked for 10 and got 4, with no error. Since pgvector 0.8.0 iterative index scans keep scanning until enough rows pass. In an interview, ask any vector engine: does it filter before, after or during the graph search?

:::key Graph and vector are query shapes, not default stores Say "variable depth on a very large graph" or "nearest neighbours with a recall target". Without that sentence, a relational table with a join or an extension is usually the answer. :::

How far Postgres goes, and where it stops

Postgres covers the shape of several families at once, in the same transactions, with one backup and one set of skills.

Everything on the right-hand side shares one primary's CPU, memory and disk with your orders. That is the real limit. A big vector index build, a large reindex or a heavy search query is competing with the writes you care about most.

OpenAI's 2026 write-up shows the same limit: a single primary with nearly 50 read replicas served very large read volume, and shardable write-heavy workloads were moved to sharded systems such as Azure CosmosDB.

Move a family out when:

1. Measured contention: its load hurts the primary's latency for core queries, and read replicas or a separate Postgres instance for it do not fix that.
2. A missing capability: embeddings wider than even half-precision or binary indexing can cover, relevance features the full text search does not offer, ingest beyond one primary, or graph depth that recursive queries cannot handle.
3. Different lifecycle: the data is derived and rebuildable and should not share failover with your orders.

A step short of moving out: run the family on its own Postgres instance, keeping SQL and tooling.

:::key Start in Postgres, split on evidence "I would start with Postgres using jsonb, full text and pgvector, measure, and move the first workload that hurts orders or hits a hard limit" is a senior answer. :::

How to say all of this in an interview

Slogans lose points. A mechanism and one number win them. Use these corrections:

Five parts, for any family:

1. The structure and the query shape it makes cheap.
2. The query shape it makes expensive.
3. One concrete record or query.
4. What it is paired with in production.
5. Where it stops being enough, with a number if you have one.

Wide-column in one breath: "Rows sit under a partition key and are sorted by a clustering key, so the latest fifty messages are one read. Sender queries are not in the key and need a second table or a search index. In production it sits beside a search index and a relational store. It stops being enough when you need ad hoc queries or multi-row transactions."

:::key Every answer names its cost A sentence that names a structure, a cheap query, an expensive query and a limit cannot be a buzzword. Practise it for all eight families. :::

How to say it in the interview

"I pick the family from the query shape. Relational for joins and constraints, document when the aggregate is read as one unit, key-value for lookups by key, wide-column for time-ordered data under a key, search and vector as derived indexes. Postgres already covers several of them, so I would start there and move one out only when measured load or a missing capability forces it."

Checklist

• For each of the eight families, I can name its core structure, one query it makes cheap and one it makes expensive.
• I can explain what an indexed join costs and name the three cases where joins get expensive.
• I can say that document vs relational is mostly where the join happens, and name the update anomaly it causes.
• I can design a wide-column primary key that bounds partition size and orders the data, and fix a hot key by sharding it.
• I know a search index and a vector index are derived, approximate or delayed, and say how I would rebuild each.
• I can list what Postgres covers (jsonb, full text, pgvector, partitioning) and the signals that make me move a family out.
• I can correct at least five common wrong statements with a mechanism and a number.

Related lessons

• How to choose a database
• ACID, precisely
• Isolation levels, in depth
• Storage engines: B-trees, LSM-trees, row vs column
• NoSQL modelling in practice: DynamoDB, Cassandra, MongoDB
• Distributed SQL: Spanner, CockroachDB, Aurora and friends