Our orders are fine, but the activity feed has two billion rows

System Design · Foundations
20 min read, about 1 h with practice

Concept
Asked often: high
Reviewed 2026-11

See the problem

Your shop keeps everything in one PostgreSQL database. Orders and payments are a few GB and work well. The activity feed (“Sam liked your review”) gets 50 million new rows a day and is now 2 billion rows. Backups take all night, and adding a column locks the table for an hour.

A teammate says: “SQL doesn’t scale. Let’s move everything to a NoSQL store.” Is that right?

Define the task

What you have: several kinds of data. Orders and payments are small and must be exactly right. The feed is huge and simple: read one user’s latest events.
What you want: a store for each kind of data that is fast, safe and easy to grow.
The hard part: each store makes some queries cheap and others impossible. Picking one store for everything gives up something you need.

Pause and think

Hint 1 of 3

A payment takes money from one account and adds it to another. What if only half happens?

Hint 2 of 3

The feed is always read as “this user’s latest 20”, never joined with anything.

Hint 3 of 3

A cart is read and written by one key: the user’s id.

Understand the idea

Think of a library and a warehouse. A library (SQL) has a catalogue: you can ask any question, like “every book by authors born in 1950, borrowed last month”. A warehouse (many NoSQL stores) has shelves by label: give it the label and you get the box at once, but “every box with a red item” means opening every box.

SQL (relational): tables with a fixed shape, and joins across tables. It has transactions (several changes all happen or none do) and constraints (rules the database enforces, like “balance can’t go below 0”). Examples: PostgreSQL, MySQL.
Key-value: one value per key, very fast. Examples: Redis, DynamoDB used simply. Good for sessions, carts and caches.
Document: each record is a JSON-like document with its own fields. Examples: MongoDB. Good for records read whole, with fields that vary.
Wide-column: rows grouped by a partition key across many machines, sorted inside each partition. Examples: Cassandra, ScyllaDB. Good for huge amounts of writes, read by key, like feeds and sensor data.
Search index and graph stores answer text search and relationship questions.

Watch the shop try one store, then another, then the right mix. Change the feed’s daily events below the player.

One store, another store, then the right mix

v1: one SQL database

Step 1 of 5. A shop keeps everything in one SQL database: customers, orders, payments, carts and the activity feed (“Sam liked your review”).

Speed Ask me first Show as text

The tests run these small models: a SQL transfer with a crash, the same transfer as two separate writes, and a feed stored by user:

"""SQL and NoSQL stores for one shop, as small runnable models.

- SQL (sqlite3 here): tables, joins and transactions: money moves all or nothing.
- Key-value, by hand with a dict: one lookup by key, nothing else.
- A feed stored by partition (like Cassandra or DynamoDB): all of one user's
  events together, newest first, so reading a feed touches one partition.
"""

import sqlite3


def sql_shop() -> sqlite3.Connection:
    db = sqlite3.connect(":memory:")
    db.executescript(
        """
        CREATE TABLE accounts (
            id INTEGER PRIMARY KEY,
            balance INTEGER NOT NULL CHECK (balance >= 0)
        );
        INSERT INTO accounts VALUES (1, 100), (2, 0);
        """
    )
    return db


def sql_transfer(
    db: sqlite3.Connection, src: int, dst: int, amount: int, crash: bool = False
) -> None:
    """Both updates happen, or neither does."""
    with db:  # One transaction: commit at the end, roll back on any error.
        db.execute("UPDATE accounts SET balance = balance + ? WHERE id = ?", (amount, dst))
        if crash:
            raise RuntimeError("server crashed between the two writes")
        db.execute("UPDATE accounts SET balance = balance - ? WHERE id = ?", (amount, src))


def kv_transfer(kv: dict[str, int], src: str, dst: str, amount: int, crash: bool = False) -> None:
    """Two separate writes to a store with no transactions across keys."""
    kv[dst] += amount
    if crash:
        raise RuntimeError("server crashed between the two writes")
    kv[src] -= amount


class Feed:
    """Events stored by user (the partition key), newest first within a user."""

    def __init__(self) -> None:
        self.partitions: dict[int, list[tuple[int, str]]] = {}
        self.read = 0  # Events looked at by queries.

    def add(self, user: int, ts: int, text: str) -> None:
        events = self.partitions.setdefault(user, [])
        events.append((ts, text))
        events.sort(reverse=True)

    def latest(self, user: int, n: int) -> list[str]:
        events = self.partitions.get(user, [])[:n]
        self.read += len(events)
        return [text for _, text in events]

    def mentioning(self, word: str) -> list[str]:
        """Not by the partition key: every event of every user must be read."""
        out = []
        for events in self.partitions.values():
            self.read += len(events)
            out += [text for _, text in events if word in text]
        return out

Climb the ladder

Each rung stores the same shop: orders, payments, carts and a feed growing by 50 million events a day.

The idea: Every table in PostgreSQL: orders, payments, carts and the feed.

This is the right start for almost every product. One database, one backup, and every question can be asked in SQL.

Gives: any question, exact money
Weak spot: the feed outgrows it
Why: Transactions and joins for everything; but one machine holds a table growing by about 3.7 TB a year.

Where it breaks: the feed. At 50 million events of about 200 bytes a day, it adds about 3.7 TB a year. Backups, index builds and schema changes on a table that size take hours.

The idea: Move all data to a NoSQL store that splits it across machines.

The waste in Okay: one machine carried a huge, simple table that never needed joins.

Gives: the feed scales out
Weak spot: money can go wrong; reports get hard
Why: The feed is spread by user, but a payment is now two writes to two keys, with no transaction across them.

Where it breaks: in the tests, a crash between “add 30 to Bo” and “take 30 from Asha” leaves 30 that came from nowhere. In SQL, the same crash rolls back both. Reports that joined orders with customers now need code.

The idea: Orders and payments in SQL; the feed in a wide-column store split by user; carts and sessions in a key-value store; product search in a search index.

The waste in Better: it gave up transactions for data that needed them. Now each store gets the data whose reads it makes cheap.

Gives: each store does what it is good at
Weak spot: several stores to run and keep in step
Why: Reading a user’s latest 20 events touches one partition: 20 events read, not every event of every user.

When Best isn’t best: every new store is another thing to back up, watch and learn. A small team should keep one PostgreSQL until a real number forces a move. PostgreSQL also stores JSON documents well, which covers many “we need NoSQL” cases.

Draw it yourself

Step 1 keeps orders in SQL. Step 2 adds a separate store for the feed, and a search index.

Step 1: Orders need transactions

A shop takes orders and payments.
Money must move all or nothing, even if a server crashes.

Solid arrow: sends requests toDashed arrow: copies data to

Watch the traffic

Send 10 requests through your board and see where each one goes. Then make a part fail and send them again.

SendReadsWritesBackground jobs

Make a part fail

How requests move

Requests start at the Client and follow your arrows. Reads try a cache first (4 in 5 are found there), then a read replica, then the database. Writes go to the database. Background jobs go on a queue, and a worker saves them. A load balancer skips a part that is down; nothing else can.

Compare

Store	Strong at	Weak at	Use it for
SQL	transactions, joins, constraints, any question	spreading writes over many machines	orders, payments, users, most data
Key-value	one very fast lookup by key	any other question	sessions, carts, caches, counters
Document	records read whole, varying fields	joins, changes across documents	product catalogues, profiles, content
Wide-column	huge writes, reads by partition key	queries by anything else	feeds, messages, sensor and event data
Search index	finding records by words	being the main copy of the data	product search, logs

Common mistakes and edge cases

“NoSQL scales, SQL doesn’t.” SQL scales a long way with indexes, replicas and partitioning. Size and access pattern decide, not the label.
One hot partition. If one key gets most of the traffic (a celebrity’s feed), one machine does most of the work.
No schema at all. A document store accepts anything, so bad data gets in. Check fields in the app, or use the store’s validation.
Losing transactions by accident. Some NoSQL stores offer transactions only inside one partition, or at extra cost. Know what yours gives you.
Two stores, no plan to keep them in step. If the search index is built from SQL, decide how changes reach it, and what happens when that lags.

Say it in the interview

Questions to ask first

What are the main queries? By key, by range, or one-off?
What must change together, all or nothing?
How big does each kind of data get, and how fast does it grow?

A 60-second walkthrough

“I’d start with PostgreSQL for orders, payments and users: they need transactions, joins and constraints, and they’re small. The activity feed is different: 50 million events a day, always read as one user’s latest. So I’d put it in a wide-column store like Cassandra, with the user id as the partition key and time as the sort key. Reading a feed touches one partition. Sessions and carts go in Redis, by key. Product search goes in a search index fed from SQL. Each store does one job, and SQL stays the place where money is right.”

Likely follow-ups

What is ACID? Atomic (all or nothing), Consistent (rules hold), Isolated (transactions don’t see each other’s half-done work), Durable (saved once committed).
What is a partition key? The field that decides which machine holds a record. Queries by it are cheap; queries by anything else read many machines.
Can PostgreSQL store documents? Yes: the jsonb type stores and indexes JSON.

Red flags interviewers notice

Picking a database before saying the queries.
Payments in a store with no transactions, and no plan for half-done writes.
“MongoDB because it is web scale.”

Practice

1. Pick a store (easy)

Pick a store for each: a user’s login session; a bank transfer; a product catalogue where shirts have sizes and laptops have CPUs; temperature readings from 1 million sensors every minute.

Hint 1 of 3

A session is read by one key on every request.

Hint 2 of 3

A transfer changes two accounts at once.

Hint 3 of 3

Sensor readings are written constantly and read by sensor and time.

Show the answers

The idea: Four tables in one PostgreSQL database.

1 million readings a minute is about 17,000 writes a second, all day.

Gives: correct, simple
Weak spot: sensors write 1 million rows a minute into it
Why: It works, but the sensor data dwarfs everything else.

The idea: Sessions in key-value; transfers in SQL; catalogue and sensors still in SQL.

The catalogue can live in SQL with a JSON column for the varying fields.

Gives: fast sessions, safe money
Weak spot: sensors still crowd the database
Why: The two clearest cases are matched.

The idea: Sessions: key-value. Transfers: SQL. Catalogue: document store (or SQL with JSON). Sensors: wide-column or a time-series database, keyed by sensor and time.

Keep old sensor data as summaries (per hour, per day) and delete the raw rows after a while.

Gives: each store fits its reads
Weak spot: four stores to run
Why: Sensor readings are written constantly and read by sensor and time range: exactly a partition key plus a sort key.

2. Product attributes that vary (medium)

Shirts have size and colour; laptops have CPU, RAM and screen; books have author and pages. New kinds of product arrive every month. How do you store them?

Hint 1 of 3

A column for every possible attribute means most cells are empty.

Hint 2 of 3

A table of (product, attribute, value) rows is flexible but hard to query.

Hint 3 of 3

Can one column hold a small document?

Show the answers

The idea: Add size, colour, cpu, ram, author… as columns.

Fine for three kinds of product, painful for three hundred.

Gives: simple queries
Weak spot: hundreds of mostly empty columns; a change per new kind
Why: Every new kind of product needs a schema change.

The idea: A table of (product_id, name, value) rows.

This pattern is often called entity-attribute-value, and it gets slow and messy.

Gives: any attribute, no schema change
Weak spot: every value is text; queries need many joins
Why: Finding “laptops with 16 GB and a 14-inch screen” joins the table to itself twice.

The idea: Common fields (name, price, stock) as columns; the varying ones in a JSON column (jsonb), with an index on the ones people filter by.

A document store is the other good answer, if products don’t need transactions with orders.

Gives: flexible and queryable
Weak spot: the app must check the JSON’s shape
Why: Prices and stock keep their transactions; attributes can differ per kind, and filters stay fast with an index.

3. Chat messages (hard)

A chat app stores 2 billion messages a day. People open a conversation and read the latest 50 messages, then scroll back.

Hint 1 of 3

Which key do almost all reads use?

Hint 2 of 3

Inside one conversation, messages should be in time order.

Hint 3 of 3

A huge group chat could make one partition enormous.

Show the answers

The idea: messages(id, conversation_id, sender, text, sent_at) with an index on (conversation_id, sent_at).

About 23,000 writes a second on average, more at peak.

Gives: fast reads at first
Weak spot: 2 billion rows a day on one machine
Why: The index makes reads fast, but writes and size grow too big for one database within weeks.

The idea: Partition key: conversation_id. Sort key: the time (or a time-ordered id), newest first.

Scrolling back reads the next 50 from the same partition.

Gives: latest 50 is one partition read
Weak spot: huge group chats make huge partitions
Why: All of a conversation’s messages sit together, already sorted.

The idea: Partition key: (conversation_id, week). Recent weeks are read first; old weeks only when scrolling far back.

Search across all messages needs a separate search index; the chat store can’t answer it.

Gives: any chat size stays bounded
Weak spot: reads that cross weeks ask two partitions
Why: No partition grows forever, so a busy group can’t overload one machine.

Check yourself

Pick the patternMoving money between two accounts, all or nothing, even through a crash. Which store?
A key-value storeA SQL database with a transactionA search indexA CDN
Multiple choiceA feed store uses user_id as its partition key. Which query is cheap?
Every post that mentions “tea”User 42’s latest 20 eventsHow many events were posted yesterdayEvery user who posted in France
Multiple choiceWhat does a SQL constraint like CHECK (balance >= 0) give you?
Faster readsThe database refuses any change that breaks the rule, whatever code sent itAutomatic shardingA backup
Spot the bugThis transfer runs on a key-value store with no transactions. What can go wrong?
```
def transfer(kv, src, dst, amount):
    kv[dst] += amount
    kv[src] -= amount
```
NothingA crash between the two lines adds money that was never takenKey-value stores can’t add numbersIt is too slow

Cheat sheet

SQL: transactions, joins, constraints. The default, and the home of money.
Key-value for lookups by one key; document for records read whole; wide-column for huge data read by a partition key; search index for words.
The partition key decides which queries are cheap. Write the queries first.
Move one kind of data out of SQL when a number forces it, not before.

Go deeper

Can you do these now?

If you’re not sure of any of these, scroll back to that part before moving on.

Name the main kinds of NoSQL store and the access pattern each one fits
Explain what transactions, joins and constraints give you in SQL
Choose a store for a piece of data from how it is read and written
Explain why a partition key decides which queries are cheap

Status: Not started

Our orders are fine, but the activity feed has two billion rows

See the problem

Define the task

Pause and think

Understand the idea

Climb the ladder

Everything in one SQL database

Everything in one NoSQL store

The right store for each kind of data

Draw it yourself

Step 1: Orders need transactions

Watch the traffic

Compare

Common mistakes and edge cases

Say it in the interview

Practice

1. Pick a store (easy)

SQL for everything

Match the obvious ones

Each to its pattern

2. Product attributes that vary (medium)

A column for every attribute

Attribute rows

Fixed columns plus a document

3. Chat messages (hard)

One SQL table

Wide-column, keyed by conversation

Keyed by conversation and time bucket

Check yourself

Cheat sheet

Go deeper

Can you do these now?