system-design

Part 3 — Data & Storage Deep Dive

How databases actually work underneath, how to model data for them, and how to change things without breaking production.

Time for this part: Weeks 5–6 on the Standard track, plus follow-up. Part 2 taught you which storage components exist; this part is the depth an interviewer probes when they ask “why?”

Chapters

How storage actually works

#	Chapter	Time	The problem it solves
1	Storage Engines: B-Trees vs LSM-Trees	24 min	Why Postgres and Cassandra behave so differently
2	Transactions and ACID	22 min	“All or nothing” while a thousand things run concurrently
3	Isolation Levels & Anomalies	24 min	Your transactions are not serializable, and here’s what that allows

Modelling data

#	Chapter	Time	The problem it solves
4	Data Modeling: Relational	24 min	Getting the schema right is cheaper than any later optimization
5	Data Modeling: NoSQL	26 min	Design from the queries, because there’s no join to save you
6	Choosing a Database	20 min	A decision guide, and permission to pick Postgres

Specialized stores

#	Chapter	Time	The problem it solves
7	Time-Series Databases	18 min	100,000 writes/second of data that ages out
8	Graph Databases	18 min	When the relationships are the query
9	Warehouses, Lakes, Lakehouses	22 min	Analytics that don’t take down production

Moving data around

#	Chapter	Time	The problem it solves
10	Batch vs Stream Processing	24 min	Process at 2 a.m., or as it happens — and what that costs
11	Change Data Capture	22 min	Keeping five derived systems in sync without dual writes
12	Zero-Downtime Migrations	24 min	Changing a schema with a million users on it

The through-line

Disk is fast sequentially and slow randomly (ch 1)
        ↓
So engines choose: update in place (B-tree) or append (LSM)
        ↓
Either way, concurrent access needs rules (ch 2, 3)
        ↓
And those rules are weaker by default than you assume
        ↓
So the schema must encode correctness itself (ch 4, 5)
        ↓
Different workloads want different engines entirely (ch 6–9)
        ↓
Which means data must flow between them (ch 10, 11)
        ↓
And all of it must change while running (ch 12)

The six things to remember

B-trees optimize reads by paying for random writes; LSM-trees optimize writes by paying for complex reads and compaction. Everything else about those databases follows.
The write-ahead log is the most important mechanism in databases — it’s why commits are fast, and it’s what replication and CDC are built on.
Your transactions are not serializable by default, and write skew is the anomaly that catches experienced engineers.
Money is integers, timestamps are TIMESTAMPTZ, and constraints beat application validation — because constraints apply to every writer.
NoSQL modeling starts from the queries. There is no join to rescue an unforeseen access pattern.
CDC eliminates the dual-write problem structurally. If the transaction committed, the change is in the log.

Before moving on

You should be able to answer these without notes:

Explain why Cassandra sustains far higher write throughput than Postgres.
Explain what a WAL does beyond crash recovery.
Describe write skew with a concrete example, and say which isolation level prevents it.
Say what your database’s default isolation level is and what it permits.
Explain why a star schema is deliberately denormalized.
Design a Cassandra table for a given access pattern, and explain why the partition is bounded.
Explain the dual-write problem and two ways to solve it.
Walk through renaming a column with zero downtime, step by step.
Explain the Postgres lock queue problem and the one-line mitigation.

Next: Part 4 — Distributed Systems — what happens when the machines have to agree with each other.

This site is open source. Improve this page.