Data systems: which trade-off is this?

Most data-system questions are one of about twenty trades wearing a costume: a stale read, a lost write, a slow write bought for a fast read, a wait bought for a correct count. The trap is answering with a product ("use Kafka") before naming the trade, because the product is the last decision, not the first. Name the idea, say the detail in the question that gave it away, and then say what change would turn it into a different question. That last sentence is what separates recognising an idea from remembering a page of notes.

The tells, on one screen

Each line is a detail you hear in a question and the idea it usually points to. Every one has a variation that breaks it, and the questions below end on that variation.

"I saved it, refreshed, and it was gone": Replication lag: the read went to a copy that had not caught up. Read-your-writes is the fix.
"Two people changed it at once and one change vanished": A lost update. Isolation level, an atomic operation, or a compare-and-set.
"Writes are fast, but reads got slower as the data grew": Log-structured storage: reads check several files until compaction catches up.
"One machine is on fire, the rest are idle": A hot partition: the key spreads the data evenly but not the load.
"The old app crashes on the new message": Schema evolution: forward compatibility was not kept.
"Both sides think they are the leader": Split brain. A fencing token, or a majority vote that only one side can win.
"The count changed after we published it": Event time against arrival time, and how long a window waits for stragglers.
"It got charged twice": At-least-once delivery without an idempotency key.
"The nightly job is too slow, but the data fits on a laptop": Maybe nothing distributed at all: one machine is often faster.

Questions

Answer each one yourself before you open it.

01A team wants to split their app across forty machines from day one. It has a few thousand users and one database of 20 GB. What is the first question to ask?
Scalability against simplicity. The tell: nothing in the numbers needs more than one machine, so every extra machine buys failure modes and no capacity.
A distributed system adds network faults, partial failures and clock disagreement in exchange for capacity or availability beyond one box. 20 GB fits in the memory of one server. Ask what load they expect, in numbers, and which part of it one machine cannot carry.
Follow-up Now the app must keep working when an entire data centre goes offline.
Now there is a reason to have more than one machine: availability, not capacity. Replicas in a second site, and a decision about what happens to writes during the switch.
Learn more What a data system promises
02The median page loads in 80 ms, and the team says performance is fine. Users on the biggest accounts keep complaining. What is being missed?
Tail latency. The tell: the complaint comes from a specific group, and the median hides exactly the slowest requests, which often belong to the users with the most data.
Report the 99th and 99.9th percentile, not the median or the mean. When one page fans out to many backend calls, the page is as slow as its slowest call, so a rare slow call becomes a common slow page.
Follow-up The page calls twenty services in parallel, each slow 1% of the time. How often is the page slow?
About 18% of the time: 1 minus 0.99 to the power 20. Tail latency amplifies with fan-out.
Learn more What a data system promises
03A résumé has many jobs, each job many skills, and the whole thing is always loaded and shown at once. Rows across five tables, or one document?
Document model. The tell: a tree that is always read whole and never joined to anything else, so one document is one read with no joins.
The relational model wins when data has many-to-many relationships and is queried from many directions. A self-contained tree read as a unit is the document model's case. The cost appears when something inside it, like a company name, needs to be shared and updated in one place.
Follow-up Now each job should link to the company's own page, and companies rename themselves.
That is a many-to-one relationship. Store a company id, not the name, and join. The document model starts needing joins, which is where the relational model is stronger.
Learn more Data models and query languages
04You need to answer "who are the friends of friends of this person, within three hops, who live in the same city?" What shape of model fits?
Graph model. The tell: the number of joins is not known in advance, because it depends on the path length.
In SQL a variable-length path needs a recursive query. A graph model makes vertices and edges first class and the query language describes paths directly.
Follow-up Now the question is only "list this person's direct friends".
A plain join table answers that in one join. A graph database earns its place on traversals, not on one hop.
Learn more Data models and query languages
05Support asks why an account's balance is what it is, and the database only stores the current balance. What design would have let you answer?
Event sourcing. The tell: the question is about history, and a table that is overwritten in place keeps only the latest state.
Store every change as an immutable event and derive the current state by replaying them. The audit trail comes free, and a new view can be built later from the same log. The cost is that reads need a derived view kept up to date, which is the read side of CQRS.
Follow-up A customer asks for their personal data to be deleted, and the log is immutable.
Encrypt each person's events with their own key and delete the key, so the events stay but are unreadable. Crypto-shredding.
Learn more Data models and query languages
06A sensor platform takes 200,000 writes a second and is rarely read. Which storage engine shape suits it, and what does it cost?
Log-structured (an LSM tree). The tell: writes dominate, and appending sequentially is the cheapest thing a disk does.
Writes go to an in-memory table and are flushed as sorted files; background compaction merges them. The costs are reads that may check several files, and compaction competing with writes for disk bandwidth.
Follow-up Now the same data is read far more than it is written, mostly single-key lookups.
A B-tree: one place per key, read in a few page reads, at the cost of rewriting a page per write.
Learn more Storage engines · LSM vs B-tree lab
07The disk is busy writing far more bytes than the application sends. Nobody changed the code. What is happening?
Write amplification. The tell: the extra bytes come from the storage engine itself, rewriting data during compaction or page updates.
An LSM tree rewrites each record every time it is merged into a lower level; leveled compaction rewrites more and keeps reads cheap, size-tiered rewrites less and uses more space. A B-tree writes a whole page, plus the write-ahead log, for a small change.
Follow-up Disk space is now the bottleneck, not write bandwidth.
That is space amplification, which size-tiered compaction makes worse. Leveled compaction trades more rewriting for less space.
Learn more Storage engines
08A dashboard sums one column across 2 billion rows, and each row has 80 columns. Reads take minutes. What layout change helps most?
Column-oriented storage. The tell: the query reads one or two columns of every row, and a row store has to read all 80.
Storing each column separately means the scan reads only the columns it needs, and values of one column compress very well. The cost is that writing one row touches every column file, so analytic stores batch writes.
Follow-up Now the query is "fetch everything about order 42".
A row store, with an index on the order id. One row, all columns, one place.
Learn more Storage engines
09Users search product descriptions for words, and a sorted index on the description column does not help. Why not, and what does?
An inverted index. The tell: the query is "which rows contain this word", and a sorted index only finds values that start the same way.
An inverted index maps each word to the list of documents containing it, so a search is a lookup and an intersection of lists. Ranking then weights rare words above common ones. Postgres offers this as a GIN index over a tsvector.
Follow-up Now users want "products similar to this one", not ones sharing a word.
Vector search: embed each product, and find the nearest vectors with an approximate index such as HNSW, trading a little recall for speed.
Learn more Storage extras
10You deploy a new service version that adds a field to a message. During the rollout, old servers crash on messages from new ones. What property was not kept?
Forward compatibility. The tell: old code reading data written by new code, which every rolling deploy causes.
Backward compatibility is new code reading old data, and it is the easier half. Forward compatibility needs old readers to skip fields they do not know. Protobuf and Avro do that when new fields are optional and tags or names are never reused.
Follow-up An old server reads a record with the new field, edits it, and writes it back.
The unknown field must be kept on the way through, or the old server silently deletes the new data.
Learn more Encoding and schema change
11A user edits their profile, the page reloads, and the old name is back. Ten seconds later the new name appears. What happened?
Replication lag on an asynchronous follower. The tell: the write succeeded, and a read moments later went to a copy that had not caught up.
The guarantee that fixes it is read-your-writes: route the user's own reads to the leader for a short time after they write, or read from a follower only once it has caught up to the user's last write position.
Follow-up Now the user sees a comment, refreshes, and it disappears.
Monotonic reads: the two reads went to followers at different lag. Pin each user to one replica.
Learn more Replication · The copy clerks lab
12Two offices both accept edits to the same document while the link between them is down. When the link comes back, both versions exist. What kind of replication is this, and what must you decide?
Multi-leader replication. The tell: more than one place accepts writes, so conflicts are not an error case but a normal one.
You must choose how to resolve a conflict: last writer wins (simple, silently loses a write), merge the values (works for sets and counters), or keep both and ask a person. Conflict-free replicated data types make merging automatic for some data shapes.
Follow-up Now the two offices only ever edit different documents.
Route each document's writes to one home office. No conflicts, but a failed home office blocks its documents.
Learn more Replication
13In a leaderless store, one replica was down for an hour and missed writes to keys nobody has read since. How does it ever catch up?
Anti-entropy. The tell: read repair only fixes keys that are read, so keys that are never read need a background process.
Replicas compare Merkle trees, hashes of ranges of keys, top down. Matching hashes skip a whole range, so only the ranges that differ are walked and copied.
Follow-up Now the stale keys are read often.
Read repair handles them: a quorum read notices the stale copy and writes the newer value back to it.
Learn more Replication
14Data is spread across 16 machines by hashing the user id, and one machine is at 100% while the others idle. What is going on?
A hot key. The tell: hashing spreads keys evenly, but one key, such as a celebrity's account, carries a huge share of the load.
Hashing balances the number of keys, not the traffic per key. Fixes: split the hot key by adding a small random suffix and fan reads out across the parts, or cache it in front of the store.
Follow-up Now the problem is range queries, like "all orders from last Tuesday", that touch every machine.
Partition by a key range instead of a hash, at the risk of today's range becoming the hot one.
Learn more Partitioning · Splitting the ledger lab
15You add a 17th machine, and almost every key has to move to a new machine. Why, and how should it have been set up?
Hash mod N. The tell: changing N changes almost every key's home.
Create many more partitions than machines from the start, say 1,000, and move whole partitions when machines are added. Consistent hashing gets the same effect: only the keys next to the new machine move.
Follow-up Now queries filter by email, but data is partitioned by user id.
A secondary index. Either each partition indexes its own rows (every query asks every partition), or the index is partitioned by email (one partition answers, but writes update two places).
Learn more Partitioning · The ring lab
16Two requests read a stock count of 1, both see it is positive, and both sell the item. Which property was missing?
Isolation, specifically against a lost update or a write skew. The tell: two transactions read the same value and each decided based on it.
Under read committed or snapshot isolation both transactions see 1. Fixes: an atomic decrement with a condition, a row lock with SELECT FOR UPDATE, or serializable isolation, which aborts one of them.
Follow-up Now each request checks a different row, such as two doctors each checking that someone else is on call.
Write skew. Row locks on the row you write do not help; you need serializable isolation or to lock the rows you read.
Learn more Transactions · Isolation levels lab
17A long report runs for an hour against a busy database and must see one consistent moment. How does it avoid blocking every writer?
Snapshot isolation with multi-version concurrency control. The tell: a long read that must be consistent alongside writes that must not wait.
The database keeps old versions of rows, and each transaction reads the versions that existed when it began. Writers create new versions instead of overwriting. The cost is that old versions pile up until a vacuum or garbage collector can remove them.
Follow-up The table keeps growing even though the row count is flat.
Old versions are not being cleaned, often because one long transaction is still holding the oldest snapshot open.
Learn more Transactions · The vacuum lag lab
18A server paused for 30 seconds during garbage collection, woke up, and kept writing as the leader, although another server had already taken over. What prevents the damage?
A fencing token. The tell: a process that believes it still holds a lease, after the rest of the system has moved on.
Each new leader or lock holder gets a higher number, and the storage rejects writes carrying an older one. The paused process cannot know it was paused, so the protection must live in the thing it writes to.
Follow-up Now the question is how the group agrees on who the new leader is.
Consensus: a majority vote such as Raft, so two leaders cannot both win a majority in the same term.
Learn more When machines fail · Who holds the pen lab
19Two events on different servers carry timestamps 3 ms apart, and you need to know which happened first. Can you trust the clocks?
No: clock skew. The tell: wall clocks on different machines, compared at millisecond precision.
Clocks drift and are corrected by NTP, sometimes jumping backward. For order without clocks, use a logical clock: a Lamport timestamp gives a total order consistent with causality; a vector clock can also say when two events were concurrent.
Follow-up Now you only need unique, roughly time-ordered ids, not true order.
A Snowflake-style id or UUIDv7: a timestamp in the top bits plus machine and sequence bits. Sortable, unique, and only roughly ordered.
Learn more Consistency and consensus
20A unique username must be enforced across three data centres, even while they cannot reach each other. What do you give up?
Availability during a partition. The tell: a constraint every replica must agree on, under a network split.
Uniqueness needs linearisability: one copy's worth of truth. During a split, the side without a majority must refuse sign-ups. The alternative is to accept both and resolve the clash afterwards, which means telling a user their name was taken after all.
Follow-up Now it is a like counter, not a username.
Both sides can keep counting and add the counts when the link returns. Eventual consistency is enough.
Learn more Consistency and consensus · The cut phone line lab
21A nightly job joins a year of clicks with user records and takes six hours. A bug is found in it. What property makes fixing it cheap?
Batch jobs over immutable input. The tell: the input is not changed by the job, so you fix the code and rerun it, and the bad output is simply replaced.
A batch job reads files and writes new files, so a rerun is safe and repeatable. That is why derived data built in batch can always be rebuilt, and why the output usually goes to a fresh location rather than over the old one.
Follow-up Now the results are needed within seconds of the click, not the next morning.
Stream processing: the same join, done on events as they arrive, at the cost of deciding how long to wait for late events.
Learn more Batch processing
22A dashboard counts taps per minute. A phone in a tunnel uploads taps two minutes late, and the minute they belong to has already been shown. What is the decision here?
Event time against arrival time, and how long a window waits for stragglers. The tell: the event carries when it happened, and that differs from when it arrived.
The processor tracks a watermark, the newest event time it has seen minus an allowed lateness, and closes a window once the watermark passes its end. Waiting longer loses fewer late events but shows every number later. Late events can be dropped, or the window can be corrected and re-sent.
Follow-up Now the count is used for a monthly bill, not a live screen.
Correctness beats speed. Wait much longer, or recompute in a batch job once all events are in.
Learn more Stream processing
23A consumer processes a payment message, crashes before recording that it did, and processes the same message again after restart. What is missing?
Idempotence. The tell: at-least-once delivery, which means a crash between doing the work and acknowledging it repeats the work.
Exactly-once delivery is not available across a crash. Exactly-once effect is: give each message an id, record processed ids in the same transaction as the effect, and skip any id already seen.
Follow-up Now the effect is an email sent through an outside provider, with no shared transaction.
Pass the message id to the provider as an idempotency key if it accepts one. If not, a duplicate is possible and the design must say so.
Learn more Stream processing · Charged twice lab
24The database, a search index and a cache must all reflect the same writes. The app writes to all three, and sometimes one write fails. How should it be built?
Change data capture into a log. The tell: dual writes from the application, which leave the stores disagreeing whenever one of them fails.
Write once to the database, read its replication log as a stream, and have each other store apply the changes in the same order. Every store ends up consistent with the database, just a little behind it.
Follow-up Now a new store is added a year later and needs all the old data.
Replay the log from a snapshot. Derived stores can be rebuilt because the log is the source of truth.
Learn more Stream processing · The ledger everyone reads lab
25A model ranks job applicants using past hiring decisions, and the people it ranks low are never hired, so the model never sees evidence they would have done well. What is the problem?
A feedback loop. The tell: the system's output decides which data it is trained on next.
Past decisions carry past bias, and a model trained on them repeats it and then reinforces it, because the outcomes it would need to correct itself are never observed. Audit what the system cannot see, not only what it gets right.
Follow-up Now the question is what to keep about each applicant at all.
Data minimisation: collect only what the decision needs, and set a deletion date when it is collected.
Learn more Where data systems are heading

← Many hands on few rows Networking: from URL to packet →