How data is laid out decides what every read and write costs. A relational database lets you lay it out once, in tidy tables, and ask almost anything later. A key-value store asks you to know your questions first and shape the data for each one. Interviewers ask "SQL or NoSQL?" to hear whether you know what each layout costs, so this chapter counts it.

Start from the access patterns

An access pattern is one way the application reads or writes the data, with how often it happens. List them before drawing a single table: the layout exists to make the common ones cheap.

Reads outnumber writes by about ten to one, and one read, the post page, is most of the traffic. That's the one to make cheap. The rename is rare, which matters later: a rare write can afford to be expensive.

Relational: each fact once

A normalised schema stores each fact in one place and points to it by key: a writers table, a posts table with the writer's id, a comments table with the post's id and the commenter's id. A writer's name is in one row, so a rename is one write, and no two copies can ever disagree.

Pages are rebuilt from the pieces with joins, and an index on each foreign key makes every piece a quick lookup. The strength is flexibility: a question nobody planned for, such as every comment a writer made on posts from last March, is one more query and maybe one more index. Transactions across rows are built in, and relationships that run every way, as in a social graph or an order system, stay manageable. The cost is that a page made of many pieces reads many rows.

Shaped for the query

A key-value or wide-column store, such as DynamoDB or Cassandra, has no joins. Each read fetches by key, so you lay data out so that each common access pattern reads one item, or one partition: the items that share a partition key, kept in order by a sort key.

Reading a partition in order is one request, however many items it returns. In exchange, you have to know the access patterns up front: a new one may need a new table or a secondary index, and a question that cuts across partitions, like every comment by one writer, means a scan or another copy of the data. The payoff is performance that stays flat as the data grows, because every request touches one partition, however many there are.

Copying and embedding

Without joins, the way to make a read cheap is to put what it needs where it reads. Denormalising means copying data next to where it's read: the writer's name onto every post and comment, so the page doesn't look each one up. Embedding means storing child items inside their parent: every comment inside its post's item, so one read returns both.

Both have costs. A copied fact has to be rewritten everywhere it lives when it changes, and until that finishes, the copies disagree. An embedded list makes its parent grow with it, and every new child rewrites the whole parent.

Embed only what is bounded

The fix for the comments is to embed a bounded slice: keep every comment in its own table, partitioned by post, and copy only the newest 20 into the post's item. The page reads one item, the item never grows past 20 comments, and older comments come from the table when someone scrolls. The cost is two writes per comment: the comment itself, and the post's list of newest ones.

The same move works for the profile: keep the writer's 20 newest post titles in the writer's item. Publishing writes the post and updates the list, a cost paid 50 times a second to save 20 reads on each of 1 000 profile views.

Each copy is a promise to keep it in step. When a writer renames, a background job rewrites their posts and comments in batches, and for a few seconds some pages show the old name. Say that out loud in an interview: it's a choice, and most products can live with it. A balance or an inventory count usually can't, which is one reason those stay normalised.

Choosing

Pick by access patterns and consistency, not by brand:

"NoSQL scales and SQL doesn't" is the weak answer. Relational databases scale reads with replicas and can be partitioned, as Vitess, Citus and Spanner do. What a key-value store buys is predictable per-key performance at any size, in exchange for designing every query ahead of time and doing the joins yourself. Many systems use both: a relational database as the source of truth, and copies shaped for the hot reads.

In the wild

The ideas in this chapter, and where you'll meet them in real systems and interviews:

Your practice

Model Inkwell for its access patterns: the same writers, posts and comments and the same traffic, with two more ways to shape them. Find the layout that serves every access pattern cheaply and has no item that can grow without limit.