Choosing a Data Model You Can Live With

Choosing a Data Model You Can Live With is the decision that comes after the guarantee and before the boundary, and it is settled by a list of questions rather than by a preference about stores. The model that survives contact with production is the one whose cheap operations match the operations your product actually performs most often.

Key takeaways

• Write the questions first. AWS states in its DynamoDB design guidance that you should not begin designing a schema until you know the questions it will need to answer.
• An access pattern is four facts: the question, how selective it is, how often it runs, and its latency budget. A pattern without those four is a wish.
• The index type is the constraint. PostgreSQL 18 documents B-tree, Hash, GiST, SP-GiST, GIN and BRIN, plus the bloom extension, each serving its own operator set.
• Read what a store refuses to promise. The Dynamo paper states it provides no isolation guarantees and permits only single-key updates, which is a hard boundary on what you can build over it.
• Normalisation is a cost paid per read and denormalisation is a cost paid per write. Neither is a virtue, and the traffic mix decides which one you can afford.

Read this after Chapter 3, whose vocabulary you need to interpret the guarantees a store's documentation offers, and before Chapter 5, which decides where the transaction boundary goes once the model exists. This chapter deliberately says nothing about replication, which was Chapter 2's decision, so that the two remain separable in your design document rather than collapsing into a single argument about which database is best.

A schema arrives in review with fourteen tables and every relation normalised. It is correct. It models the domain accurately.

Somebody asks what the home screen loads. The answer is nine joins across six of those tables, on every page view, for the most common request in the product.

Nothing about the schema is wrong. It was designed against the domain instead of against the traffic.

Write the questions before the schema

The instruction exists in first-party documentation, which is useful when arguing with somebody who thinks this is a stylistic preference.

AWS's NoSQL design guidance for DynamoDB states that you should not start designing your schema until you know the questions it will need to answer, and that the first step is to identify the query patterns the system must satisfy. It also asks you to understand data size, data shape and data velocity before beginning, and to keep as few tables as possible.

That advice is written for a key-value store and it applies to every store. A relational schema designed without the query list produces the nine-join home screen. A document schema designed without it produces the aggregate that has to be assembled in application code across four collections.

So the artifact that comes first is a list, not a diagram. Every question the product asks of its data, written as a sentence a product manager would recognise.

An access pattern is four facts

A pattern written as give me the user is not usable. It needs enough detail to be costed.

The four facts are the question, its selectivity, its frequency and its latency budget. Selectivity is how much of the data the question touches: one row, a hundred rows, or a scan. Frequency comes straight out of Chapter 1's load model, per class of work. The latency budget is the percentile target for the request this query sits inside.

Written properly, a pattern reads like this: fetch the twenty most recent messages in one conversation, ordered by time, at 900 per second peak, inside a request budgeted at p99 under 200 milliseconds. Now it can be designed against.

Twelve of those sentences will fit on one page and that page is the real design document. The schema is a rendering of it.

The index type decides which queries are cheap

Once the questions exist, the next question is mechanical. What makes each one cheap.

PostgreSQL's index types documentation lists B-tree, Hash, GiST, SP-GiST, GIN and BRIN, with bloom available as an extension, and describes the operator set each one can serve. That list is the point. An index is not a general speed-up; it is a structure that answers a particular family of comparisons quickly and is useless for the others.

B-tree serves ordered comparison and prefix matching, which covers most ordinary lookups and range scans. GIN serves containment queries over composite values, which is what makes full-text and array membership tractable. BRIN serves very large tables whose physical order correlates with the queried column, at a fraction of the size.

The design consequence is direct. A pattern with no matching index type is a pattern the store will answer by scanning. Tuning does not change the class of the operation. That is where a store choice honestly gets made.

Size, shape and velocity change the answer

The same pattern list can point at different models depending on three numbers AWS asks for by name.

Size is how much data there is now and in a year. Shape is how uniform the records are: whether every row has the same fields, or whether a third of them carry an arbitrary bag of customer-defined attributes. Velocity is how fast rows arrive and how fast they stop being interesting.

Those three change conclusions that the pattern list alone leaves open. A hundred thousand records with a stable shape can be modelled almost any way and it will work. A hundred million records arriving at a steady rate, mostly queried within a day of arrival and never again, is a time-partitioned model whether you planned one or not.

Velocity is the one teams underestimate. A table nobody deletes from is a table whose cheapest query gets slower every month, and the index that made it fast is the thing that eventually stops fitting in memory.

Read what a store refuses to promise

Every store's documentation contains a paragraph describing what it will not do. That paragraph is more informative than the feature list.

The Dynamo paper, published at SOSP in October 2007, states in its description of design considerations that the system provides no isolation guarantees and permits only single-key updates. Amazon accepted that deliberately, because a significant portion of its services needed only primary-key access. Read that sentence with Chapter 3's vocabulary and it says something precise: every anomaly on the list is permitted, and any invariant spanning two keys is yours to maintain in application code.

Abadi's PACELC labels give the same information in compact form. A store at PA/EL has chosen availability under partition and latency in normal operation, which means a reader can see a stale value on an ordinary day, not only during an incident.

So the reading exercise is short and worth doing before any prototype. Find the sentence in the official documentation that says what is not guaranteed. Go and read it. If you cannot find that sentence, you do not yet know the store well enough to choose it.

Normalisation is a cost, not a virtue

Normalisation is taught as correctness, which makes it hard to discuss as a trade.

The honest framing is that normalisation moves cost from writes to reads and denormalisation moves it back. Neither one is a virtue. A fully normalised schema writes each fact once and pays for assembly on every read. A denormalised one assembles at write time and pays for consistency maintenance across copies, which is work your code has to do because the store will not.

Which one you can afford depends on the ratio in Chapter 1's load model. A product with a hundred reads per write can afford to denormalise aggressively. A product with a write-heavy ledger cannot, because every extra copy multiplies the write amplification and the number of places a bug can leave an inconsistency.

State the ratio before arguing about the schema. It converts a taste argument into an arithmetic one.

What makes each pattern cheap or expensive

This table is the artifact to lift. It is written by pattern shape rather than by product, so it stays usable across stores.

The last row is the one that quietly becomes a capacity problem. An aggregate computed live is fine at ten thousand rows and is a design fault at ten million. Nothing warns you during the transition.

A worked example, composited and labelled

The details here are composited from ordinary production shapes rather than one system, and the shape is exact.

A field-service product has five patterns. Fetch one job by identifier, at 1,200 per second peak. List a technician's jobs for today, ordered by start time, at 400 per second. Search job notes for a phrase, at 5 per second. Report completed jobs per region per month, run twice a day by six people. And update a job's status, at 90 per second.

Four of the five are ordered lookups on known keys, which an ordinary relational store serves with two composite indexes and no cleverness. The phrase search needs an inverted index, which the same store can provide, and its frequency is low enough that it does not justify a separate system yet. The monthly report is the odd one out: it is a scan over history, run rarely, by few people, with a latency budget of seconds rather than milliseconds.

The decision that falls out is to keep one store for the four hot patterns and to serve the report from a periodically refreshed aggregate rather than from raw rows. The constraint recorded alongside it is that the report may be up to one hour stale, which the six people who read it have agreed to in writing. The reversal signal is phrase-search frequency above 100 per second, or report readers needing figures fresher than an hour.

The objection: we will add an index later

Adding an index later is genuinely easy, which is why this objection sounds strong. It is answering a different question than the one that matters.

An index changes the cost of a query. It does not change the shape of the model. If the model stores a fact in a place that cannot be reached by the question you need to ask, no index rescues it, and the fix is a data migration rather than a schema tweak. The objection collapses that distinction.

There is also a cost that shows up late. Each index makes writes more expensive and consumes memory that the working set wanted, so a table with nine indexes is a table whose write path has been quietly taxed to cover for a model that did not match its questions. Chapter 11 is where that bill arrives.

The cheap move is the pattern list. It costs a page and it is the only thing that reliably prevents a migration of a running system eighteen months later.

Chapter summary

The data model is settled by a written list of the questions the store must answer, and AWS states this outright in its DynamoDB design guidance: you should not start designing a schema until you know the questions it will need to answer, and the first step is to identify the query patterns the system must satisfy, along with data size, shape and velocity. A usable access pattern carries four facts, being the question, its selectivity, its frequency from Chapter 1's load model, and the latency budget of the request it sits inside, and a page of twelve such sentences is the real design document with the schema as a rendering of it. The index type then decides which questions are cheap, because PostgreSQL documents B-tree, Hash, GiST, SP-GiST, GIN and BRIN plus the bloom extension, each serving its own operator set, so a pattern with no matching index type is a pattern answered by scanning. Reading what a store refuses to promise is the fastest way to disqualify it: the Dynamo paper states it provides no isolation guarantees and permits only single-key updates, which in Chapter 3's vocabulary means every anomaly is permitted and cross-key invariants belong to your code. Normalisation moves cost to reads and denormalisation moves it to writes, so the read-to-write ratio decides which is affordable, and the chapter's decision is the pattern list with a staleness constraint and a frequency threshold recorded per derived artifact.

The model says which questions are cheap and where each fact lives. The next decision is where to draw a line through that model, because a service boundary is a transaction boundary before it is anything else. Chapter 5 is Where to Put the Boundary Between Services.