Skip to content

Store families: document, key-value, wide-column, graph, search, vector

Pick the store by the read it makes cheap, and know which reads it makes expensive.

Predict first

Before you read: predict

A chat app stores each message as one JSON document in a Postgres jsonb column, messages.doc. Until release 12 the app wrote the sender as "from": "u7"; from release 12 it writes "sender": {"id": "u7"}. Nobody rewrote the older documents. The table holds 40 messages; u7 wrote 3 of them before release 12 and 2 after it. The moderation page counts u7's messages with:

SELECT count(*) FROM messages WHERE doc->>'from' = 'u7';

What does the query return?

Pick one

The 30-second version

Each store family is built for one kind of read: by key, a whole document, one partition in sort order, a walk along edges, by words, or by nearest numbers. List your reads first. A document store keeps any shape, so your code owns the schema. Postgres covers most of these reads; move one out when a measurement says so.

Pick the store by the read it makes cheap, and know which reads it makes expensive.

How it works

Step 1 of 13

Reads first. Each family is an index shape built for one kind of read. Write the reads down, then pick.

Analogy: A seismological network's data centre

At the data centre In the stores
a station's details card, fetched by its station code alone key-value (step 2)
one file per earthquake, holding its location, magnitude and every station's readings, taken out and filed back whole a document (step 3)
older quake files say depth, newer ones depth_km; each analyst's script checks for both the schema in the code (step 4)
one recording drum per station, its trace in time order; station K7 from 02:00 to 02:10 is one drum partition and sort key (step 5)
the fault map: which fault branches from which, followed as far as the branches go a graph (step 10)
the bulletin index: each place name lists the reports that mention it an inverted index (step 11)
quakes whose waveform looks like this one, waveforms compared as numbers vectors (step 12)

In real terms: choose the store by the read you must make cheap.

Breaks down when: an analyst can always unroll every drum to find one quake; a store asked for a read it was not keyed for can only scan everything.

Contrasting case: a small observatory's single ledger with every reading in it: one store for every read, which is enough while the ledger is short.

Examples, run for real

4.1 ExamHall EXH-4670 → MR !4970: the proctoring event log

The proctor console shows one candidate's events in one exam, in time order. EXH-4670: the event log was keyed by event_id alone, like a relational table, so every timeline was a Scan. MR !4970 keys it by the read: partition key exam_id#candidate_id, sort key at (steps 5–6).

4.1 model: wide-column table (lib/widecol.mjs) · 12,000 proctoring events · 400 candidates in 3 exams · one timeline holds 30 events
4.1 EXH-4670, partition key event_id: the timeline is a Scan · items read 12,000 · events shown 30
4.1 MR !4970, partition key exam_id#candidate_id, sort key at: one Query · items read 30 · events shown 30

4.2 ExamHall: the question bank stays in Postgres

An editor looks for economics questions about inflation that resemble question q3. One SQL statement filters a jsonb field, matches words and orders by embedding distance (step 13).

4.2 model: 8 questions on PostgreSQL 18.3 (PGlite) with pgvector 0.8.1 · meta jsonb, search tsvector ('english'), embedding vector(3)
4.2 meta->>'subject' = 'econ': 6 questions · and search @@ 'inflation': 3 · ordered by embedding <-> q3's: q3, q6, q1

4.3 The board for "Which store, and why?" (worked, not executed)

Typical question Steps that answer it Mechanism owner
"When would you choose MongoDB or DynamoDB over PostgreSQL? Give a concrete trigger." 1, 6, 9, 13 this lesson; scaling Postgres: db-partitioning-sharding
"Why design DynamoDB or Cassandra tables around queries instead of entities?" 5, 6, 7, 8 this lesson
"How would you add full-text or semantic search to an existing Postgres app?" 11, 12, 13 this lesson; configurations and ranking: sc-faceted-search

Try it

Answer first, then check. Nothing is saved.

Question 1 of 6

A chat app keeps its messages in a DynamoDB-style wide-column table (run on this lesson's labelled model) with partition key conversation_id, sort key sent_at. It holds 1,200 messages in 40 conversations. User u7 belongs to 6 conversations and wrote 18 messages in them. A new page must list every message u7 wrote, from one request that reads only those 18 messages.

Which design meets that?

Pick one

Question 2 of 6

A chat app stores replies as messages(id, reply_to). m1 starts a thread. m2 and m3 reply to m1; m4 replies to m2; m5 replies to m3; m6 replies to m4; m7 replies to m6. m20 starts a thread. m21 replies to m20. A moderator removes m1's thread: m1 and every reply under it, at any depth, and nothing else.

-- Query X
WITH RECURSIVE thread(id) AS (
  SELECT id FROM messages WHERE id = 'm1'
  UNION
  SELECT m.id FROM messages m JOIN thread t ON m.reply_to = t.id
)
SELECT id FROM thread;
-- Query Y
SELECT id FROM messages
WHERE id = 'm1' OR reply_to = 'm1'
   OR reply_to IN (SELECT id FROM messages WHERE reply_to = 'm1');
-- Query Z
WITH RECURSIVE thread(id, reply_to) AS (
  SELECT id, reply_to FROM messages WHERE id = 'm1'
  UNION
  SELECT m.id, m.reply_to FROM messages m JOIN thread t ON m.id = t.reply_to
)
SELECT id FROM thread;

Which query lists exactly those messages?

Pick one

Question 3 of 6

A chat app flags spam by similarity. Each message has an embedding from the app's model (3 numbers here, to keep it readable):

message embedding
Buy cheap followers now [0.7, 0.3, 0.1]
Free fans, click here [0.85, 0.05, 0.3]
Cheap tickets for the final [0.5, 0.6, 0.2]
WIN WIN WIN, DM me [2.0, 1.5, 1.0]
See you at practice [0.6, 0.9, 1.5]

The reported message, "Cheap followers, best price", has the embedding [0.9, 0.1, 0.2]. The app runs:

SELECT body FROM messages ORDER BY embedding <-> '[0.9,0.1,0.2]' LIMIT 2;

Which two messages does it return, nearest first?

Pick one

Question 4 of 6

A chat app keeps messages in a wide-column table (run on this lesson's labelled model) with partition key conversation_id, sort key sent_at. Conversation c1 holds 100 messages. Its page sends one request: Query conversation_id = c1, newest first, Limit 20, filter expression deleted = false. 6 of c1's 20 newest messages are deleted, and 9 of its 100 are.

How many messages does the request return?

Pick one

Question 5 of 6

A chat app's moderators search six messages: "Art club tonight", "Party at 8", "Start the call now", "art supplies list", "Smart reply is on", "The ARTS fair". They try two queries:

SELECT count(*) FROM messages WHERE body ILIKE '%art%';
SELECT count(*) FROM messages WHERE to_tsvector('simple', body) @@ to_tsquery('simple', 'art');

How many messages does each query return?

Pick one

Question 6 of 6

A chat company keeps its messages in DynamoDB, keyed for the app's reads, and already runs Postgres for billing. Its analytics team adds new reports every month (messages by country, by device, by hour, by feature flag), each filtering on fields nobody has listed yet.

Where should the reports run?

Pick one

Go deeper

Pro tip
  1. Answer which database? with a table of reads first: the read, how often, the key it needs, then the store. It shows the interviewer the store follows from the reads. When: the interviewer asks whether you would use MongoDB or DynamoDB here. Status: RAN on the model.
Source

Kleppmann's Designing Data-Intensive Applications (the row's first source) is a book and keys nothing. Behaviour of the named products is valid as of 2026-10-17.

  • AWS DynamoDB docs, Best practices: NoSQL design for DynamoDB (the curriculum row's second source; the best-practices index and its query-and-scan, secondary-index and relational-modelling pages also saved): https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/bp-general-nosql-design.html (aws-bp-nosql-design.txt). "you shouldn't start designing your schema for DynamoDB until you know the questions it will need to answer." (steps 1, 6). Query-and-scan page (aws-bp-query-scan.txt): "A Scan operation always scans the entire table or secondary index.".
  • AWS DynamoDB docs, Query, filter expressions, the Query API and secondary indexes (aws-query.txt, aws-query-filter.txt, aws-api-query.txt, aws-lsi.txt, aws-bp-gsi.txt): the rules the wide-column model in lib/widecol.mjs follows, each quoted in that file's header (steps 5, 7, 8).
  • PostgreSQL 18 docs, 12.1 Introduction to Full Text Search (the curriculum row's third source; also from the 17 docs, CL-069 (1)): https://www.postgresql.org/docs/18/textsearch-intro.html. LIKE and ILIKE "provide no ordering (ranking) of search results" and "tend to be slow because there is no index support" (step 11).
  • PostgreSQL 18 docs, 8.14 JSON Types and 7.8 WITH Queries (both also from the 17 docs): "The structure is typically unenforced" (step 4); "Recursive queries are typically used to deal with hierarchical or tree-structured data." (steps 10, 13).
  • pgvector 0.8.1 README: https://github.com/pgvector/pgvector/blob/v0.8.1/README.md (pgvector-readme.txt). "By default, pgvector performs exact nearest neighbor search, which provides perfect recall." and an approximate index "trades some recall for speed" (step 12).

Related lessons

Checked against the tools and docs as of 2026-10-17.