Choosing a Data Model: Access Patterns First, Then the Store

Relational, document, key-value, wide-column, graph, time-series and search — chosen by the questions you ask, not the shape of your entities. One dataset, loaded three ways into PostgreSQL 18, with five access patterns measured in pages touched.

The question is never “which database is best”. It is “which questions will this data be asked, how often, and by how many people at once”. Answer that and the model mostly picks itself; skip it and you get a beautifully normalised schema that cannot serve its own home page, or a document store that cannot answer a question nobody thought of on launch day.

This part loads one dataset into PostgreSQL 18 three ways — normalised, as documents, and as opaque key-value blobs — and measures what five different questions cost each of them. Holding the engine constant is the point: what changes is the model, not the software.

Try this first

You have orders. Each order has a customer and a few lines. You need two things: show one order on a page, and total revenue by product across all orders.

Which shape is better, three tables or one document per order? Write down your answer, and a guess at how much better.

The measurement, and why it’s in pages

Every number here is buffers: the 8 kB pages a query read or found in cache, from EXPLAIN (ANALYZE, BUFFERS). Pages are far steadier than milliseconds — steady enough to quote — where a laptop’s timings move with whatever else the machine is doing. They are not a constant of nature either: a page count moves with shared_buffers, with bloat, with the planner’s choice of parallel workers. Every figure below is the median of 5 runs on a warm cache.

Two limits, stated up front, because they both bite later.

Pages count visits, not work. A page of jsonb that has to be parsed and unnested costs far more than a page of narrow rows, and the page count is identical. Where that matters below, the milliseconds are quoted too.

Pages don’t count writes. Buffers exclude the write-ahead log, which is most of what an update costs. So the update row is also measured with EXPLAIN (ANALYZE, WAL), and that is where the real number turns out to be.

The dataset is small on purpose: 5,000 customers, 50,000 orders, 4 lines each. Small enough to keep the lab’s runtime sane. The shape of the results is what carries over, not the absolute numbers.

Question	Normalised	Document	Key-value	What the key-value store had to do
One order, whole	13	3	3	Index Scan
One customer’s orders	12	14	3,334	Seq Scan
Orders containing one product	404	404	3,334	Seq Scan
Revenue by product, everything	1,479	3,350	3,350	Seq Scan, Function Scan
One customer moves city	9	101	3,356	Seq Scan

One thing to declare about that third column. The key-value model here is the document table with its index taken away, so “key-value” means “reachable only by primary key”. A real key-value store would not run four of these five queries slowly — it could not express them at all, and you would have maintained a second key, like customer:56:orders, to answer them. Read the column as what it costs when a question does not match the key you chose.

Read across each row rather than down, because each row is a different question and the winner changes:

One order, whole. The document wins, 3 pages against 13. This is the case document stores are named for: the thing you display is the thing you stored, and a nested loop over three tables is doing work the document did once, at write time.

One customer’s orders. The normalised model and the document are within two pages of each other. In milliseconds the normalised model is still ahead — 0.14 against 0.2 — which is the “pages count visits, not work” caveat showing up for the first time. The key-value store scans the entire table, because the only way in is the key, and “customer 56” is not the key.

Orders containing a product. Normalised and document are level at 404 pages each. The GIN index on the document does the same job as the B-tree on order_lines.product. So “document stores can’t query inside documents” is false, and has been for years.

Revenue by product, across everything. Normalised wins, 1,479 pages against 3,350, and by more than that in time. The document has to be unnested before it can be aggregated, and every byte of every order — the customer, the status, the timestamps — is dragged through memory to get at the line items.

A customer moves city. This is the one people forget, and it is the row where pages understate the damage. That customer has 10 orders. Normalised, the change is one row: 9 pages and 75 bytes of write-ahead log. As documents, the same change rewrites ten whole documents and their index entries: 101 pages and 10,400 bytes of log, about 139 times as much.

That last row is the whole trade in miniature, and it scales with how much you embedded: ten orders here, and a customer with a thousand orders would pay a hundred times that. Embedding buys the read and sells the write.

There is a second bill, in disk. The three normalised tables come to 25.1 MB with their indexes; the document table is 35.0 MB, about 39% more, because every order carries its own copy of the customer.

The rule the vendors actually state

The “model by access patterns” line is not a blog-post slogan. It is what the vendors’ own documentation says, and they say it most clearly where they are describing what they are not.

AWS, on DynamoDB, draws the contrast explicitly: “For an RDBMS, you can go ahead and create a normalized data model without thinking about access patterns. You can then extend it later when new questions and query requirements arise.” And then the other side: “By contrast, you shouldn’t start designing your schema for DynamoDB until you know the questions it will need to answer. Understanding the business problems and the application use cases up front is essential.”

That is the trade stated by the vendor selling the second thing. Design order is reversed, and the flexibility is what you pay with.

MongoDB puts the same rule in one sentence: “A core principle of data modeling in MongoDB is that data that’s accessed together should be stored together.” Cassandra’s documentation makes it the rule of the whole exercise, and is equally blunt about the cost: there are no joins, and denormalisation is not a failure but the plan.

Now three corrections, because this is an area where what “everyone knows” has drifted from what the documentation says.

“Single-table design” is not AWS’s term, and AWS’s own example does not do it. The phrase appears on neither of the AWS pages we checked. And the page the whole idea is cited from — AWS’s worked example of modelling relational data in DynamoDB — opens by saying the opposite: “This design uses multiple specialized tables rather than a single adjacency list, providing clear operational boundaries while leveraging strategic GSIs to serve all access patterns efficiently.” What AWS actually writes elsewhere is hedged and always has been: “As a general rule, you should maintain as few tables as possible in a DynamoDB application. Exceptions are cases where high-volume time series data are involved, or datasets that have very different access patterns.” As few as possible, with named exceptions, is not one.

The same page states the principle better than any slogan does: the approach means “grouping data based on access patterns rather than rigid entity boundaries”. That sentence is this entire part, in eleven words.

Cassandra’s “100 MB partition limit” is a guideline, and it is not the limit. The documented hard limit is different in kind: “Cassandra’s hard limit is 2 billion cells per partition, but you’ll likely run into performance issues before reaching that limit.” The famous number comes from a different page and is explicitly soft: “Though requirements for these measures may vary based on the application a general guideline is to keep number of values per partition to below 100,000 and disk space per partition to below 100MB.” Both numbers are real and both are Apache’s. One is a wall; the other is advice. Note the unit, too — Cassandra counts cells, not rows and not bytes.

“Index-free adjacency” is a marketing term, not a documented one. It appears on none of Neo4j’s graph-database or graph-concepts documentation pages; it turns up on a Neo4j Labs page, alongside a complexity table with no benchmark attached. What the product docs do claim is plainer and more useful: “Graph databases do not use JOINs. Rather, relationships are stored natively alongside the data elements (nodes) in a more flexible format …” That is a real architectural difference. Treat the big-O tables you’ll find quoting it as advertising.

Following an edge, and what actually explodes

Joins are cheap. Joins of joins of joins are not — and it is worth being precise about why, because the usual explanation is wrong.

Our lab builds a follow graph — 5,000 people, each following 20, 100,000 edges in one table — and asks “who can I reach within N hops?” as a recursive query.

Hops	People reached	Recursive CTE	Visited-set BFS
1	20	6	538
2	413	46	2,598
3	4,721	846	30,213
4	4,999	10,286	46,541

The cost does not grow with the number of hops. It grows with the size of the frontier, and the frontier grows as the branching factor raised to the hop count. Each of those 4,999 people reached has to be expanded against the table, and that is where the pages go: roughly two page visits per person in the frontier. On a graph of five million people the same four hops would cost a thousand times more, at exactly the same hop count.

That distinction matters because it decides what a different database can save you. A graph store does not make the frontier smaller. Traversing this graph, it would visit the same 100,000 edges. What it changes is the cost of one hop: a stored pointer to a neighbour instead of an index descent, which here is about two pages a row. That is a constant factor, and a real one — but this lab did not measure a graph database, so treat the constant as unmeasured and the explosion as the thing that is demonstrated.

The other half of the lesson is in the second column. By four hops the answer is 4,999 of 5,000 people — everybody. In a well-connected graph, “friends of friends of friends” is quickly just “everyone”, and an answer of “everyone” is rarely what anyone wanted.

The optimisation that lost

The recursive query above is the naive one. Its UNION deduplicates on person and depth, so somebody reached at hop two is expanded again at hop three, and again at hop four. The obvious fix is to keep a set of people already seen and expand only the new frontier — which is what PostgreSQL’s manual suggests for cycles, and what any breadth-first search does.

A recursive CTE cannot see its own accumulated output, so that has to be done as one statement per hop against a table you maintain yourself. We measured that too: it is the fourth column, and it is 5 to 90 times more expensive.

The bookkeeping costs more than the repeated work it saves. Maintaining the visited set means writing it, indexing it, and rebuilding the frontier every hop, and at this size that is dearer than re-walking edges the planner is already reading sequentially. It would stop being true on a graph where re-expansion dominates — a deeper walk, a higher branching factor, or edges expensive to fetch. The honest summary is that the naive recursive CTE was the right answer here, and the only way to know was to measure both.

Where every model runs out: text

Six searches over 50,000 short notes. The lab marks every index on the table invalid except the one named, so each row is that index on its own. (That is a direct write to a system catalogue, not a supported API. It is used because it leaves the statistics and the physical layout untouched, which is what makes the rows comparable, and it is wrapped in a finally so a failure cannot leave the database with silently unusable indexes.)

Query	Index available	Pages	What the planner chose	Rows matched	Pages holding a match
LIKE 'order 123%'	a plain B-tree	439	Seq Scan	111	—
LIKE 'order 123%'	a text_pattern_ops B-tree	5	Index Only Scan	111	—
LIKE '%held at customs%'	a plain B-tree	439	Seq Scan	10,000	439
LIKE '%held at customs%'	a trigram GIN index	529	Bitmap Heap Scan, Bitmap Index Scan	10,000	439
full text, a common word	a full-text GIN index	446	Bitmap Heap Scan, Bitmap Index Scan	20,000	439
full text, a rare word	a full-text GIN index	4	Bitmap Heap Scan, Bitmap Index Scan	1	1

Three things worth taking from that table.

An index only helps the query shape it fits. LIKE 'order 123%' with an ordinary B-tree got a sequential scan: 439 pages. The same query with a text_pattern_ops index got 5. PostgreSQL’s manual states the rule exactly: a B-tree can serve LIKE when “the pattern is a constant and is anchored to the beginning of the string”, but “if your database does not use the C locale you will need to create the index with a special operator class”. Our database does not use the C locale, so the plain B-tree could not answer a query it looks perfectly suited to.

What decides whether an index wins is how many pages hold a match, not how many rows. The trigram index, which can answer a substring query, touched more pages than the scan it replaced — 529 against 439. The reason is the last column: the 10,000 matching rows are spread over all 439 pages of the table, so the bitmap heap scan visits every page anyway and pays for the index on top. The same 10,000 rows clustered into a tenth of the pages would have made that index a clear win. The full-text search for a common word loses the same way, for the same reason: 446 pages against a 439-page scan. Only the rare term, whose one match sits on one page, pays off — 4 pages.

And the stemmer is doing more than you asked. That common word matches 20,000 rows, though the phrase it came from appears in only 10,000. to_tsvector('english', 'customs customer') returns 'custom':1,2 — both words reduce to the same lexeme, so a search for one finds the other. That is exactly the behaviour PostgreSQL’s manual advertises when it complains that its own LIKE operators have “no linguistic support”: a search for satisfy ought to find satisfies. It is a feature. It is also why a full-text match count can surprise you.

The manual’s own indictment of LIKE is worth reading in full, because it is a database arguing against itself: no linguistic support, “no ordering (ranking) of search results, which makes them ineffective when thousands of matching documents are found”, and a tendency to be slow “because there is no index support”.

Ranking, stemming and fuzzy matching are a different problem from “find the rows”, and a dedicated search engine does them far better. On whether such an engine should be your source of truth, be careful what you attribute: the sentence everybody quotes is not on any of the Elastic documentation pages we checked. What is there is mechanical, and if anything stronger. Reindexing — routine, and required to change a mapping or move to a new major version — reads the original document back, which is why the manual warns “Do not disable the _source field, unless absolutely necessary”. And on durability: “Taking a snapshot is the only reliable and supported way to back up a cluster.” Draw your own conclusion about where the authoritative copy belongs; just don’t sign Elastic’s name to it.

The models, and the question each one answers

Model	The question it is shaped for	What it makes hard
Relational	anything, including questions you haven’t thought of yet	one entity spread over many tables; very deep traversals
Document	“give me this whole thing”	aggregating across everything; updating data you embedded in many places
Key-value	“give me the value at this key”	every question that isn’t the key
Wide-column	“give me this partition, in this order”	anything the partition key doesn’t lead with; joins
Graph	“how is this connected to that, several hops out”	scans and aggregates over everything
Time-series	“this range of time, aggregated”	updates and deletes of individual points
Search	“which documents are about this, ranked”	being the source of truth

The row that matters most is the first one. A relational database answers questions you didn’t design for, and that is not a small feature: most of what a system is asked to do in year three was not on the list in year one.

Explain it like I’m ten

Think about how you keep things in your room.

• A drawer with a label on it. You know exactly which drawer, you open it, you have the thing. That’s a key-value store. Fast, and useless if you’ve forgotten which drawer.
• A box with a whole costume in it — hat, cloak, wand, all together. Getting dressed is one trip. That’s a document. But if you own three costumes with the same hat and the hat changes colour, you have to fix it three times.
• Everything sorted into separate boxes: hats in one, cloaks in another. Getting dressed takes four trips, but “how many hats do I own” takes one, and you only ever fix a hat once. That’s relational.
• A string between everything that goes together, so you can follow it. That’s a graph.
• A diary where you only ever add today’s page and mostly ask “what happened last week”. That’s time-series.

Nobody’s room is only one of these. The question is which trip you make twenty times a day.

The precise version

• Making one trip instead of four is locality: putting together what is read together.
• Fixing the hat three times is denormalisation, and the bill it comes with is update anomalies — the copies can disagree.
• “Which drawer” is the key, and a question that doesn’t name one turns into a scan. (In a wide-column store the drawer is chosen by a partition key, and picking it badly is how a partition grows without bound.)
• Following the string is a traversal. Its cost grows with the frontier — how many things you’ve reached — and the frontier grows as the branching factor raised to the number of hops.
• Where the analogy breaks: a real system usually keeps several of these at once, over the same facts, and something has to keep them agreeing.

Trade-offs

• Embedding trades write cost for read speed, and our lab prices both: the document answered “one order, whole” in 3 pages against 13, and wrote 10,400 bytes of log against 75 when the embedded customer changed.
• A key-value model is only as good as its key. Every question the key doesn’t answer is a scan, and adding a second access pattern later means adding an index, a second copy, or a different store.
• Query-first design buys speed and sells flexibility. Cassandra and DynamoDB both tell you to model from your queries, and AWS says plainly that you shouldn’t start designing until you know the questions. That works exactly as long as the query list doesn’t change.
• Relational is the default for a reason. It is the model whose cost for an unanticipated question is most often “write the query” rather than “migrate the data” — though a document store with a good index answered an unanticipated question here just as cheaply.
• Specialised stores earn their keep at the extremes, not in the middle: deep traversals, ranked text, high-rate time-series ingest. Below the extreme, the general store with the right index usually wins on total cost.
• Every extra store is an operational cost forever: backups, upgrades, monitoring, on-call knowledge, and the consistency problem between it and the store of record.

Common mistakes

• Choosing the model from the entities instead of the queries. The entity diagram tells you what exists; it doesn’t tell you what is asked.
• Believing document stores can’t query inside documents. Ours answered a nested-attribute query in the same 404 pages as the normalised model.
• Embedding something that changes independently. If the embedded thing has its own life cycle, you have signed up to update it everywhere.
• Expecting an index to fix a query shape it doesn’t fit. Our prefix query scanned the whole table with a valid, analysed B-tree beside it — the wrong operator class for that shape, exactly as the manual says.
• Adding an index for a query whose matches are spread over every page. Measured: the trigram index cost 529 pages against a 439-page scan, because all 439 pages held a match.
• Reaching for a graph database at depth two. Measure your actual hop count and your branching factor first: ours cost 6 and 46 pages at one and two hops. And remember a graph store shrinks the cost per hop, not the frontier.
• Making the search engine the only copy. Mapping changes and major-version upgrades are served by reindexing, and reindexing reads the original document.
• Adding a second store to fix a query you could have indexed. The second store is permanent; the index is one line.

Interview questions

Try to answer each one before opening the model answer.

1. How would you choose between a relational and a document model?

Show a strong answer

• Start from the access patterns, not the entities: what is read, how often, by what key, and what is written.
• Document wins when the aggregate is the unit — read whole, written whole, with a natural boundary. Our lab: 3 pages against 13 to read one order.
• Relational wins when the same facts are asked different questions, especially aggregates: 1,479 pages against 3,350 for revenue by product.
• The deciding question is usually the write: does anything you’d embed change on its own schedule? A customer’s city changed one row in the normalised model, and every order that embedded it in the document one.
• In PostgreSQL you don’t have to choose globally. jsonb with a GIN index gives you the document where it helps, in the same transaction as the relational part.

Likely follow-up: “When would you embed anyway, knowing the update cost?” When the copy is deliberately a snapshot — an invoice should keep the address it was sent to, not follow the customer’s current one.

2. What does “model your data by your access patterns” actually mean?

Show a strong answer

• List the queries first, with their frequency and their latency budget, before any table or collection exists.
• Then build the model so that each hot query is a single, direct lookup, which in a wide-column or key-value store means the partition key is the thing the query has in hand.
• Accept the consequence: a query that wasn’t on the list may not be servable at all without a new copy of the data.
• That is a real trade, not a best practice. It buys predictable latency at scale and sells the ability to ask something new cheaply.
• In a relational store you can do this too — it’s called an index — and you can undo it.

Likely follow-up: “What if a new access pattern arrives?” In a query-first store: a new table with the same data in a different shape, backfilled, and kept in sync. That’s the cost you agreed to.

3. When is a graph database actually the right answer?

Show a strong answer

• When traversal depth is variable or unbounded: shortest path, reachability, cycle detection, recommendations several hops out.
• Not at depth one or two. Our lab followed a follow-graph in plain SQL: one hop cost 6 pages, two cost 46. That is not a reason to add a database.
• The cost curve is the argument, but be precise about it: three hops cost 846 pages, four cost 10,286, and that is the frontier growing, not the hop count. A graph store would visit the same edges.
• A graph store stores relationships next to the nodes, so a hop doesn’t re-find its neighbours. Neo4j’s docs put it as “relationships are stored natively alongside the data elements (nodes)”. (“Index-free adjacency” is a marketing term, not a documented one — don’t cite it as though it came from the product docs.)
• Watch for saturation: four hops reached 4,999 of 5,000 people. An answer of “everyone” usually means the question was wrong.

Likely follow-up: “How would you do it without a graph database?” A recursive CTE with a depth cap — and measure before hand-rolling a visited set, because ours cost more than the repeated work it saved.

4. Your search feature is slow. Walk me through it.

Show a strong answer

• First find out what the planner did, with EXPLAIN (ANALYZE, BUFFERS). “It’s slow” plus a guess is how the wrong index gets created.
• Check the query shape against the index: a prefix LIKE needs a text_pattern_ops index unless the database is in the C locale; a substring needs a trigram index; word search needs a full-text index. Our prefix query scanned 439 pages with an ordinary B-tree and 5 with the right one.
• Then check how the matches are spread. If they touch every page, no index helps: our trigram index cost 529 pages against a 439-page scan, because all 439 pages held a match.
• Then ask what “search” means here. Ranking, stemming and typo tolerance are a different problem, and that’s when a search engine earns its place.
• If you add one, treat it as a derived index, fed from the store of record and rebuildable — Elastic’s own docs note that a snapshot is the only supported backup, and that reindexing needs the original document.

Likely follow-up: “How do you keep it in sync?” Change data capture or an outbox, with a full rebuild as the backstop — and the acceptance that it is eventually consistent.

5. What is polyglot persistence, and what does it cost?

Show a strong answer

• The idea: use a different store for each job, chosen by the job’s access pattern.
• The cost, which is the part usually left out: every store is backups, upgrades, monitoring, an on-call runbook and someone who knows it at 3 a.m.
• And the hard part: consistency between them. Two stores holding the same fact will disagree at some point, so one has to be the source of truth and the rest derived.
• The cheap version first: most “we need another database” problems are an index, a materialised view, or a cache.
• When it’s genuinely right: a workload at an extreme the general store is bad at, with enough volume to pay for the operational weight.

Likely follow-up: “How do you decide?” Write down the query that forced it, measure it on what you already run, and only then price the alternative.

6. How do you handle a many-to-many relationship in a document store?

Show a strong answer

• Reference rather than embed on at least one side, because embedding both ways duplicates every update.
• Or embed the small, stable side and reference the large or changing one — the asymmetry is usually obvious once you ask what changes.
• Denormalise deliberately, with a job that fixes the copies, not accidentally.
• Consider whether this is the signal that the relationship, not the entity, is the thing your system is about. That’s the argument for a relational join table or a graph.
• Measure it: changing one embedded customer cost our document model 10,400 bytes of write-ahead log against the relational model’s 75 — and that was with only 10 orders carrying the copy.

Likely follow-up: “What if the referenced document is needed on every read?” Then you’re doing a join in application code, which is the trade you took the document store to avoid — reconsider the boundary.

7. What would make you pick a wide-column store like Cassandra?

Show a strong answer

• A known, small set of queries, all of which lead with a partition key, and a write rate that a single-leader relational store can’t take.
• You design a table per query, accepting duplicated data, because the store cannot join and cannot filter freely.
• Partition size is a real constraint, and worth knowing precisely: the documented hard limit is 2 billion cells per partition, while the famous 100,000 values / 100 MB is a general guideline on a different page. Neither is a tested breaking point, and an unbounded partition is the thing to design against.
• You are also buying a replication and availability model, which is often the actual reason, more than the data model.
• You are selling ad-hoc querying entirely. If someone will want to ask a new question next quarter, say so now.

Likely follow-up: “How do you pick the partition key?” From the query, and then check the cardinality and the growth: it must spread writes and keep each partition bounded.

8. Can you do all of this in PostgreSQL?

Show a strong answer

• More than people assume. jsonb with GIN gives you documents; recursive CTEs give you traversals; full-text search and trigrams give you search; partitioning, and BRIN where the time column tracks physical row order, give you time-series.
• Our lab did exactly that for the three models it measured — normalised, document and key-value were one PostgreSQL 18 instance with only the schema changed. Wide-column and time-series it did not measure at all.
• The honest limits are at the extremes: very high ingest rates, very deep traversals, ranked relevance search, and horizontal write scaling.
• The advantage is one set of transactions, one backup, one thing to operate, and one place where the facts agree.
• The rule of thumb: start here, measure, and let a specialised store be something you’re pushed into rather than something you choose up front.

Likely follow-up: “When did you last outgrow it?” A good answer names a specific measured limit, not a feeling.

Sources

• Lab: system-design/checks/part17_data_model/postgres.py — one dataset loaded three ways into PostgreSQL 18 in a container, with five access patterns, a recursive traversal at four depths, and six text searches, each measured in pages touched via EXPLAIN (ANALYZE, BUFFERS)
• E. F. Codd, A Relational Model of Data for Large Shared Data Banks, Communications of the ACM, 1970
• PostgreSQL 18: index types, full-text search introduction, full-text indexes, jsonb types and indexing, recursive queries
• Amazon DynamoDB: NoSQL design for DynamoDB, modelling relational data, partition key design, service constraints
• MongoDB: data modelling, referencing
• Apache Cassandra: data modelling introduction, refining the model
• Neo4j: graph databases, graph database concepts
• Elasticsearch: the _source field, snapshot and restore
• Redis: Redis data types — which is where “data structure server” comes from; M. Fowler, PolyglotPersistence

What to remember

• Choose the model from the questions, not from the entity diagram.
• Embedding buys the read and sells the write, in log bytes and in disk. Price both before deciding.
• A key-value model is exactly as useful as its key, and no more.
• Query-first design buys predictable latency and sells the question nobody has asked yet.
• Traversal cost grows with the frontier, not the hop count — and a graph store shrinks the cost per hop, not the frontier.
• An index only helps the query shape it fits, and only when the matches sit on few pages.
• Every additional store is permanent operational weight. An index is one line.

The data model is a bet about which questions you will be asked. Write the questions down first, and the bet gets a lot easier to win.