Step 2 of 18 · 1 min

What a data model is

A data model is the shape a database expects your data to have, and therefore the kinds of questions it can answer easily. It's the most important decision about a database, more important than which product you pick, because it shapes how you write every feature afterwards.

There are a handful of main families. Each one makes some things easy by making other things hard.

Step 3 of 18 · 1 min

Relational

This is Postgres, MySQL and SQL Server: what Chirp already had, and what Part 1 used throughout.

Data lives in tables with fixed columns. Each fact is stored in one place, and tables refer to each other by ID:

users   (id, display_name, city, created_at)
posts   (id, author_id → users.id, text, created_at)
follows (follower_id → users.id, followee_id → users.id, created_at)

What it's good at:

• Joining. "Posts by people I follow," "users who liked this post and live in Pune." You can combine tables in ways nobody planned when the schema was designed, and the database figures out how to run it.
• Rules the database enforces. A post can't point to a user who doesn't exist. Usernames are unique. These are checked on every write, so bugs can't quietly break them.
• Transactions across many rows and tables (lesson 1.4).
• Answering new questions. Ad hoc queries, reports, analytics. Product managers love this.

What it's less good at:

• Changing a huge table's structure can be slow. Adding a column with a default is instant in modern Postgres, but some changes rewrite the whole table.
• Objects in your code don't map perfectly onto tables. A user profile with settings, a list of links and a few badges is one object in your code and several tables in the database. Libraries (ORMs) hide this, sometimes too well (remember the N+1 problem in lesson 1.2).
• One machine for writes, by default. Replicas spread reads (lesson 2.5). Spreading writes across machines takes real work (Part 5).

Step 4 of 18 · 2 min

Document

MongoDB, Couchbase, and Firestore. Data is stored as documents, usually JSON-like, grouped into collections. A document can contain nested objects and lists.

Arjun's sketch of a Chirp user:

{
  "_id": 4821,
  "display_name": "Meera Kapoor",
  "city": "Pune",
  "settings": { "theme": "dark", "notify_likes": true },
  "links": ["meera.dev", "github.com/meerak"],
  "recent_posts": [
    { "id": 9001, "text": "Shipping day!", "likes": 41 },
    { "id": 8993, "text": "Chai > coffee", "likes": 12 }
  ]
}

What it's good at:

• Reading a whole object at once. Everything about a user is in one place, so the profile page is a single read. Lesson 1.2 showed why fewer round trips matter. This is called locality.
• Data that's naturally a tree. Settings, an order with its line items, a product with its variants: things that are always loaded and saved together.
• Varied shapes. If different documents need different fields, that's easy to store.
• Spreading across machines. Most document databases are designed to split a collection across many servers.

What it's less good at:

• Relationships between documents. As soon as data refers to other data, like posts referring to users or follows connecting users to users, you either copy data into many documents or look things up across documents. Lookups across documents are weaker than SQL joins in most document databases, and copying data has a cost this lesson and lesson 3.3 come back to.
• Unbounded lists. The recent_posts list works because it's small. A list of all a user's followers wouldn't be: some users have millions. MongoDB, for example, limits a single document to 16 MB, and long before that limit, rewriting a huge document on every change gets slow.

"Schemaless" is often given as an advantage. It's worth being precise about what it means. Your data still has a shape, because your code expects certain fields. The difference is when that shape is checked. A relational database checks it when you write (schema-on-write). A document database lets you write anything, and your code has to cope with whatever it finds when it reads (schema-on-read). That's genuinely useful when shapes vary a lot. It also means that after three years, your code has to handle every shape that was ever written, because nothing stopped old shapes from staying around.

Modern document databases have also closed some gaps: MongoDB, for instance, has supported transactions across multiple documents since version 4.0. The trade-offs above are about what each model makes natural, not what's strictly possible.

Step 5 of 18 · 1 min

Wide-column

Cassandra, ScyllaDB, HBase, and Google's Bigtable. The name is confusing; it's easier to think of these as "sorted rows, grouped by a partition key."

You design each table around one specific query. For a notifications inbox:

CREATE TABLE notifications_by_user (
    user_id     bigint,
    created_at  timestamp,
    notif_id    uuid,
    kind        text,
    actor_id    bigint,
    post_id     bigint,
    PRIMARY KEY ((user_id), created_at, notif_id)
) WITH CLUSTERING ORDER BY (created_at DESC);

The partition key, user_id, decides which machine stores the row. All of one user's notifications live together on the same machines. Inside a partition, rows are kept sorted by the clustering columns, created_at newest first. So "the 50 newest notifications for user 4821" means going to one partition and reading the first 50 rows in order, which is about as fast as storage can be.

What it's good at:

• Enormous write volume, spread across many machines. Lesson 3.2 explains why its storage engine handles writes so cheaply.
• Growing by adding machines. Partitioning is built in. Need more capacity? Add nodes.
• Staying up. Data is copied to several machines, and there's no single leader to fail over.

What it's less good at:

• Any query you didn't design a table for. There are no joins and only limited support for filtering by other columns. "Notifications for user 4821 from Meera" needs another table, or filtering in your code.
• So you copy data. The usual approach is one table per query, with the same data written to several tables. Writes are cheap, so this is the expected way to work, but keeping the copies consistent is your job.
• Transactions are limited. Cassandra has lightweight transactions for conditional updates on a single partition, but they're much slower than normal writes, and there's nothing like a multi-table SQL transaction.
• Partition sizes need watching. If one partition grows huge (a celebrity's follower list, say), that partition becomes slow and its machines become hot spots.

Step 6 of 18 · 1 min

Key-value, graph and search

Three more families, briefly:

• Key-value stores (Redis, DynamoDB used simply, etcd): you store a value under a key and fetch it by that key. Extremely fast and simple. Anything more than "get by key" is up to you. Chirp's cache from lesson 2.4 is one.
• Graph databases (Neo4j, Amazon Neptune): data is nodes (users, posts) and edges (follows, likes). They're built for questions that walk along relationships, like "friends of friends," "shortest path between two people," or "who is connected to this account through three hops." In SQL those become many self-joins. In a graph query language, they're one line.
• Search engines (Elasticsearch, OpenSearch, Meilisearch): built for finding documents by words in them, with typos and ranking. Lesson 3.4 is all about these.

Step 7 of 18 · 1 min

Write down the access patterns first

Here's what Meera wanted, and it's the method this lesson is really about. Before choosing a model, list every access pattern: each distinct way the app reads or writes data. For each, write down:

• What it asks, in plain words.
• How often, at peak.
• Its shape: fetch by ID? A range ("newest 50")? Combining two sets? Matching words? Counting?
• How fast it needs to be.
• How correct it needs to be: is a few seconds out of date acceptable?

Here's the table Chirp ended up with, for the four new features:

Once it was written down, the argument mostly settled itself. Each shape points towards a different kind of store, and the same database isn't the best answer for all of them.

Step 8 of 18 · 4 min

Feature by feature

Mutual friends: combining two sets

"Which of the people I follow also follow this profile?" means taking two sets (the people I follow, and this profile's followers) and finding the overlap.

Here's how that question looks in each model:

Relational. This is exactly what SQL is for:

SELECT f.follower_id
FROM follows f
WHERE f.followee_id = :profile
  AND f.follower_id IN (SELECT followee_id FROM follows WHERE follower_id = :me)
LIMIT 3;

The detail that makes it fast is the index. The follows table already had its primary key on (follower_id, followee_id), which answers "who do I follow" quickly. Chirp added a second index on (followee_id, follower_id), which answers "does X follow this profile" with a single index lookup (lesson 1.4).

With both indexes, the database can start from the smaller set. A typical user follows around 200 people, so the database checks 200 entries against the profile's follower index, even if the profile has 10 million followers. That's a few hundred index lookups, mostly from memory. A few milliseconds.

Document. You can't store a celebrity's 10 million followers as a list inside their user document, because it's far too big. So follows go in their own collection, one document per follow. That collection is basically the relational follows table. Now the overlap has to be computed somewhere: either with the database's cross-collection lookup features, or by the app fetching my 200 follows and then checking each one. It works, with more round trips or more code, and it's a sign that this data is relational in nature.

Wide-column. You'd keep two tables, following_by_user and followers_by_user. To find the overlap, the app reads my 200 follows from one, then checks each against the other: 200 point reads, which you'd batch into a few requests. Doable, but the app is doing the join itself.

Graph. The question is the graph:

MATCH (me:User {id: $me})-[:FOLLOWS]->(x)-[:FOLLOWS]->(p:User {id: $profile})
RETURN x LIMIT 3

This is where graph databases shine, and if Chirp later wanted "friends of friends you might know," three hops out, a graph store would become very tempting. For one hop, Postgres with the right index was already fast.

Decision: stay relational, add the reverse index.

People search: matching words

"Users whose name is like meera k, in Pune" sounds simple, but look at what it really needs: matching part of a word (meer), forgiving typos (mera), ignoring case and accents, ranking the best matches first, and, for Chirp's Indian users, matching names typed in Hindi script or spelled in Latin letters.

The first attempt was WHERE display_name ILIKE '%meera%', which can't use a normal index (lesson 1.4) because of the % at the start, so it scanned all 2 million users. That took over a second.

None of the general-purpose models are built for this: not relational, not document, not wide-column. It's what search engines exist for, and lesson 3.4 explains how they work. Postgres has some useful text-search features that go a fair way, and the lesson covers when they're enough.

Decision: a search index, which is lesson 3.4.

Notifications: lots of appends, read by time, then deleted

This was the feature that made Arjun's case, though not quite in the way he'd argued it.

Every like, reply and follow creates a notification for someone. At Chirp's evening peak, that's around 2,500 new rows a second. Each user reads only their own newest few. And notifications older than 30 days are deleted, which in Postgres means a constant stream of DELETEs on a table receiving thousands of inserts a second.

That's the textbook shape for wide-column storage: one partition per user, sorted by time, writes that are always appends, and old data expired automatically with a time-to-live (TTL) on each row. Cassandra was built for workloads like this.

But "built for" doesn't mean "needed." At 2,500 writes a second, Postgres was still well within its limits, if the table was designed for this pattern. Lesson 3.2 shows the trick that made it work: splitting the table by week, so deleting old notifications becomes dropping a whole week's table, which is instant. It also explains what an LSM-tree storage engine does differently, and at what point Chirp would move notifications to one.

Decision: stay in Postgres for now, with a table designed for appends and bulk expiry. Revisit at roughly ten times the volume.

Name changes: where does the name live?

The fourth feature was a single-row update. It became the most interesting, because of Arjun's document sketch.

In that sketch, each post embedded a copy of its author's details, so loading a feed didn't need to look anything up. That's the document model's big advantage: locality. Now think about what happens when someone changes their name.

In the relational model, the name lives in one row of users. One update, and every page that displays it shows the new name on the next read.

In the document sketch, the name was copied into every post the user had written, and into every comment, and into notifications. A user with 4,000 posts means 4,000 documents to rewrite. Anything that's missed shows the old name. And if the job fails halfway through, half of their posts say one thing and half say another.

This is the cost of copying data to make reads faster, which is called denormalization. Sometimes it's absolutely worth it. Lesson 3.3 is entirely about when, and how to keep the copies correct.

Decision: keep the name in one place, and look it up (from cache, lesson 2.4) when displaying it.

Step 9 of 18 · 1 min

About "it doesn't scale"

Arjun's strongest argument was scale: Postgres runs writes on one machine; NoSQL databases spread across many. That's true, and it's the main reason teams choose Cassandra or a distributed document store.

But it's worth doing the napkin math (lesson 1.3) before acting on it. Chirp's whole database was about 400 GB. Its peak write rate was a few thousand rows a second. A single well-sized Postgres server, with replicas for reads and a cache in front, handles that comfortably, and has room to grow several times over. Moving to a distributed database would have meant giving up joins, foreign keys and easy transactions now, to solve a problem Chirp might have in two years.

The better rule: choose for the access patterns you have, and know which pattern would force a change. For Chirp, that's written down: when notification writes pass about 25,000 a second, or the database's write load stops fitting on one large machine, revisit. Part 5 covers what happens then.

Step 10 of 18 · 1 min

Using more than one kind of database

The end result of the meeting was that Chirp would use Postgres for most things, plus a search engine for search, and maybe a wide-column store for notifications later. Using different databases for different access patterns has a name: polyglot persistence.

It's the right idea, with a real cost that's easy to underestimate. Each additional database is:

• Another system to run: backups, upgrades, monitoring, failover, on-call knowledge.
• Another copy of data to keep in sync. The search index has to learn about every new user and every name change. If syncing breaks, search silently goes stale. Lesson 3.4 is mostly about doing this reliably.
• Another thing to learn. Each database has its own failure modes. A team that knows Postgres deeply is better off than one that knows four databases shallowly.

So the rule Chirp adopted: add a new kind of store only when an access pattern clearly needs it, and the existing store can't reasonably do it. Search met that bar. Notifications didn't, yet.

Step 11 of 18 · 1 min

A rough guide

Step 12 of 18 · 1 min

From the real world: how Discord keyed its messages

Discord's engineering team wrote one of the best public examples of designing a key from an access pattern, in 2017.

Discord started out storing messages in MongoDB, in one replica set. By November 2015 it had 100 million messages, and the data and its index no longer fit in memory, so latency became unpredictable. The team listed how messages were actually read and written:

• Reads are almost always "the most recent messages in one channel," newest first, a page at a time.
• Writes are overwhelmingly new messages, appended at the end.
• Reads are random across channels (any channel anyone opens), and some channels are vastly busier than others.

That's the "sorted rows, grouped by a partition key" shape from the wide-column section, so they chose Cassandra. The interesting decision was the key.

The obvious key is the channel ID, with messages sorted by message ID inside it. Every read of a channel's history becomes one partition read: exactly what you want. The problem is that partitions never stop growing. Cassandra works best when partitions stay under roughly 100 MB, and a busy channel would pass that within months and keep going.

So the partition key became (channel_id, bucket), where a bucket is a fixed window of time, 10 days in Discord's design. Message IDs were Snowflake-style (lesson 4.3), so the bucket can be computed from the ID itself. To load history, Discord reads the current bucket; if that doesn't return enough messages, it reads the bucket before, and so on. Busy channels fill a page from the current bucket. Quiet channels touch a few empty or small buckets, which is cheap.

The general method is exactly this lesson's: write down the queries first, pick a key that makes each common query hit one partition, then check that no single partition can grow without limit. Time-bucketing is the standard fix for anything that grows forever under one key: messages, events, sensor readings, activity logs.

Step 13 of 18 · 1 min

Going deeper: sizing a partition before it bites

"Keep partitions small" needs a number, and you can work it out on a napkin (lesson 1.3) before the first row is written.

Take the busiest key you expect, not the average one. For a chat channel: suppose Chirp's busiest group chat gets 20,000 messages a day at about 160 bytes each, including overhead. That's about 3.2 MB a day. Without buckets, its partition passes 100 MB in a month and 1 GB in about a year.

Now choose the bucket length so the busiest key stays under your limit:

bucket length ≤ size limit ÷ busiest key's growth per day
             = 100 MB ÷ 3.2 MB per day ≈ 31 days

A 30-day bucket fits, barely. Anything twice as busy would overflow, so you'd pick 10 days for headroom, as Discord did. Then check the other side of the trade-off: how many buckets does a typical read touch? For a channel that gets a message a week, a page of 50 messages might span a year: 37 ten-day buckets, most of them empty. That's acceptable if empty buckets are cheap to check, and if not, you can store "the last bucket that has messages" alongside the channel and skip straight to it.

Two more things to plan for:

• Some keys outgrow any fixed bucket. For those, the bucket can be a function of the key's own rate (big channels get 1-day buckets), recorded in the channel's metadata.
• Hot is different from big. A small partition read by 100,000 people at once is a hot partition, and bucketing doesn't help. That needs caching or request coalescing, which is what Discord ran into five years later (lesson 3.2).

Step 14 of 18 · 1 min

What Chirp decided

• Stayed on Postgres for users, posts, follows and likes.
• Added a reverse index on follows (followee_id, follower_id) for mutual friends.
• Kept notifications in Postgres, with a table designed for appends and bulk expiry (lesson 3.2), and a written-down threshold for moving them.
• Added a search engine for people and post search, kept in sync from the database (lesson 3.4).
• Kept display names in one place. Pages look up names at read time, through the cache (lesson 3.3).

Arjun got his new database after all: the search engine. He was also, by general agreement, the person who'd read the most about all of this, so he led the search project.

Step 15 of 18 · 1 min

In the simulation

Decision D1 in Stage 0 asked you to choose Chirp's main database before you knew any of this, which is how it usually goes in real life. Stage 2 is where that choice is tested: the product manager arrives with these same four features. If you chose a document store, the name change and mutual friends cost more. If you chose a wide-column store, mutual friends and search need extra tables. If you chose relational, Stage 2 goes fairly smoothly, but notifications and Stage 4's write volume have their own surprises.

Open the simulation →

Step 16 of 18 · 1 min

If you're here for an interview

"SQL or NoSQL?" comes up in nearly every design interview. The weak answer picks one by reputation ("NoSQL scales better"). The strong answer:

• Lists the access patterns first, briefly, with rough rates.
• Matches shapes to models: joins and rules point to relational; huge append-only writes by key and time point to wide-column; text search points to a search engine.
• Mentions the cost of copying data when it chooses a model that needs it.
• Is happy to use two stores where the patterns clearly differ, and says how they'll be kept in sync.

"I'd start relational, because we need joins for X and transactions for Y, and I'd move Z to a wide-column store if writes pass roughly this rate" is about as strong an answer as there is.

Step 17 of 18 · 2 min

Try these yourself

Step 18 of 18 · 1 min

Further reading

• Martin Kleppmann, Designing Data-Intensive Applications, Chapter 2, "Data Models and Query Languages." The best single overview of this topic.
• Cassandra's documentation on data modeling, especially "query-first" design and partition sizing.
• MongoDB's documentation on data modeling: embedding versus referencing.
• E. F. Codd, "A Relational Model of Data for Large Shared Data Banks," Communications of the ACM, 1970. Short, and still worth reading to see why the relational model was invented.
• Stanislav Vishnevskiy, "How Discord Stores Billions of Messages," Discord blog, 2017.
• Apache Cassandra documentation on data modelling and partition size; DataStax's guidance on keeping partitions under about 100 MB.