Part 8: Databases in Depth.

Basketly, Part 7's grocery-delivery app, is a year older. Its API is clean now. Its database isn't. (Basketly is illustrative: the company, times and numbers are made up. Each failure is a classic one, and the arithmetic matches this lesson's.)

All five happen on one server, from not knowing what it does inside. Part 6 gave you the vocabulary (ACID, isolation levels, WAL and LSM in a paragraph each). This part goes underneath it: the single-node knowledge interviewers probe when you write "PostgreSQL" and they ask "why?". Spreading data across machines is Week 1's job.

01Chapter 1: What Happens to a Write#

1.1 The Simple Definition#

A storage engine puts rows on disk and finds them again. Three of its parts explain most of what follows:

• Pages. Disk is read and written in fixed blocks: 8 kB in PostgreSQL, 16 KB by default in InnoDB, MySQL's engine (PostgreSQL docs and MySQL 8.4 Reference Manual). Rows live on pages, and an index is a tree of pages.
• The buffer pool. Pages cached in RAM. A hit costs microseconds; a miss is a disk read.
• The write-ahead log (WAL). Every change is appended to a sequential log first. A commit is durable once its log record is on disk, even if the changed pages are only in memory.

1.2 The Buffer Pool and the Working Set#

For a dedicated server, PostgreSQL's docs suggest shared_buffers at 25% of RAM, and say more than 40% rarely helps "because PostgreSQL also relies on the operating system cache" (the page cache from Part 5). MySQL's docs say "on dedicated servers, up to 80% of physical memory is often assigned to the buffer pool": InnoDB does its own caching, inserting new pages at the midpoint of its LRU list so one big scan can't push out the hot pages.

The number that matters in a design is the working set, the pages your hot queries touch. While it fits in memory, the database is fast. When it stops fitting, latency jumps. Discord gave exactly this as its reason for moving messages off MongoDB: "the data and the index could no longer fit in RAM and latencies started to become unpredictable" (Discord Engineering, 2017).

1.3 The WAL: A Commit Is One Sequential Write#

PostgreSQL's rule: changes to data files "must be written only after those changes have been logged". Since the log can redo everything after a crash, "we do not need to flush data pages to disk on every transaction commit".

%% title: One commit in PostgreSQL: the log is flushed, the pages are not
sequenceDiagram
  autonumber
  participant C as Checkout service
  participant BE as Backend process
  participant BP as Shared buffers
  participant W as WAL on disk
  participant D as Data files
  C->>BE: UPDATE stock, INSERT order, COMMIT
  BE->>BP: change pages in memory, now dirty
  BE->>W: append WAL records, then fsync
  W-->>BE: flushed
  BE-->>C: COMMIT OK
  Note over BP,D: later, background writer and checkpoints
  BP->>D: write dirty pages
  Note over W,D: after a crash, replay WAL from the last checkpoint

• Group commit. "When the server is processing many small concurrent transactions, one fsync of the WAL file may suffice to commit many transactions" (PostgreSQL docs).
• Torn pages. With full_page_writes on (the default), the first change to a page after a checkpoint logs the whole page, so a half-written page can be rebuilt.
• Replicas apply this same log, which Week 1, Day 2 builds on.

02Chapter 2: B-Trees#

2.1 How a B-Tree Finds a Row#

A B-tree is a sorted tree of pages: internal pages hold keys and child pointers, linked leaves hold every key in order. Hundreds of keys per page make it wide and shallow: a lookup reads one page per level, a range scan seeks once and walks the leaves.

Writes update pages in place. A full leaf splits into two half-full pages. Insert order decides how full pages stay: InnoDB's docs say sequential inserts leave pages "about 15/16 full", random ones "from 1/2 to 15/16 full".

Tables are organised differently, and Chapter 4 depends on it:

• InnoDB: the table is a B-tree. The clustered index on the primary key stores whole rows in its leaves. Secondary indexes store the primary key.
• PostgreSQL: the table is a heap. Rows go wherever there's room, and every index, the primary key included, points at a physical location (ctid: page and slot).

2.2 The Worked Estimate: One Billion Orders#

03Chapter 3: LSM-Trees#

3.1 The Idea#

O'Neil, Cheng, Gawlick and O'Neil designed the log-structured merge-tree in 1996 for tables taking constant inserts, where a B-tree index "will effectively double the I/O cost of the transaction". Their algorithm "defers and batches index changes, cascading the changes from a memory-based component through one or more disk components in an efficient manner reminiscent of merge sort" (Acta Informatica, 1996). RocksDB, Cassandra and ScyllaDB descend from it. It never updates in place: it writes new sorted files and merges them later.

%% title: An LSM-tree (RocksDB style): sequential writes, merges in the background
flowchart TD
  W["Put or Delete"]:::client --> WAL[("WAL<br/>sequential append")]:::store
  W --> MT["Memtable<br/>sorted, in RAM"]:::cache
  MT -->|"full: flush"| L0[("L0 SST files<br/>key ranges overlap")]:::store
  L0 -->|"compaction"| L1[("L1<br/>one sorted run")]:::store
  L1 -->|"compaction · next level 10× bigger"| L2[("L2 ... Ln")]:::store
  R["Get key"]:::client -.->|"1 newest first"| MT
  R -.->|"2 Bloom filter, then file"| L0
  R -.->|"3 at most one file per level"| L1

• Write: into the memtable (a skiplist by default) and optionally the WAL. A full memtable is flushed to a sorted SST file in L0 and its WAL can be deleted (RocksDB wiki).
• Compaction: in leveled compaction each level below L0 is one sorted run, 10× the one above by default. An overfull level merges a file into the next level, dropping overwritten values. A delete is a tombstone until compaction reaches it.
• Read: memtable, then L0's files, then at most one file per level. A per-file Bloom filter says "definitely not here": 10 bits per key gives about a 1% false-positive rate (RocksDB wiki).

3.2 Amplification: The Three Costs#

33 doesn't make LSM worse at writes: its writes are large, sequential and in the background; a B-tree's are small, random and on the commit path.

04Chapter 4: What an Index Buys, and What It Costs#

4.1 Primary and Secondary#

What a secondary index entry points to differs:

• InnoDB: the primary key, so a lookup searches two B-trees. The docs: "If the primary key is long, the secondary indexes use more space, so it is advantageous to have a short primary key."
• PostgreSQL: a ctid. One index search plus one heap read, but when an update writes the row at a new location, every index needs a new entry.

Uber gave that as a main reason for moving from PostgreSQL to MySQL in 2016: in PostgreSQL, even indexes on columns an update didn't change "still must be updated with the creation of a new row tuple", while "InnoDB secondary index records hold a pointer to the primary key value" (Uber Engineering, 2016). PostgreSQL's answer is HOT (heap-only tuple) updates: if no indexed column changes and the new version fits on the same page, no index entries are added. A lower fillfactor leaves room for them (PostgreSQL docs).

4.2 Composite Indexes: Equality First, Then Range#

Part 6 gave the leftmost-prefix rule. The ordering rule underneath: equality columns first, then the one range or sort column. (user_id, created_at) stores each user's orders together, sorted by time, so WHERE user_id = ? ORDER BY created_at DESC LIMIT 20 reads 20 adjacent entries. For Part 7's cursor, add the tie-breaker: (user_id, created_at, id).

4.3 Covering Indexes, Selectivity and the Write Tax#

An index covers a query when it holds every column the query needs. In PostgreSQL that's an index-only scan, and INCLUDE adds payload columns: CREATE INDEX ... ON orders (user_id, created_at, id) INCLUDE (status, total). The MVCC catch: the index can't tell whether an entry is visible to your snapshot, so PostgreSQL checks the visibility map, and only pages marked all-visible (recently vacuumed) skip the heap. InnoDB likewise skips the shortcut when a secondary record is delete-marked or its page "is updated by a newer transaction".

Selectivity is the fraction of rows a condition matches. Small is good. When it's large, jumping from index to scattered rows is slower than reading the table straight through. PostgreSQL's EXPLAIN examples show the planner moving from an index scan (unique1 = 42) to a bitmap scan (< 100) to a sequential scan (< 7000 of 10,000 rows).

Every index is another tree to update and log on each write, and more pages competing for memory. Add one when a real query needs it and EXPLAIN shows it used.

4.4 Reading EXPLAIN: One Query, Three Plans#

Part 7's history page for the café owner:

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, created_at, status, total
FROM orders
WHERE user_id = 4812
  AND (created_at, id) < ('2026-03-02 18:04', 'ord_77')
ORDER BY created_at DESC, id DESC
LIMIT 21;

ANALYZE runs the query (wrap writes in BEGIN … ROLLBACK, as the docs advise) and adds actual rows and times. BUFFERS (on by default with ANALYZE since PostgreSQL 18) shows pages found in cache (hit) and read from disk (read). The outputs are illustrative and trimmed: PostgreSQL 18's format and node names, made-up numbers.

Plan 1: only an index on created_at.

Limit  (cost=0.57..504738.75 rows=21 width=48) (actual time=8412.317..8412.906 rows=21.00 loops=1)
  ->  Index Scan Backward using orders_created_at_idx on orders  (cost=0.57..98544120.00 rows=4100 width=48) (actual time=8412.315..8412.901 rows=21.00 loops=1)
        Filter: ((user_id = 4812) AND (ROW(created_at, id) < ROW(...)))
        Rows Removed by Filter: 3186420
        Buffers: shared hit=402113 read=1290874

The index only supplies the order. PostgreSQL walks orders backwards in time and discards 3.2 million to find 21, reading 1.3 million pages from disk. The giveaway: Rows Removed by Filter far above rows returned.

Plan 2: CREATE INDEX orders_user_created_idx ON orders (user_id, created_at DESC, id DESC);

Limit  (cost=0.70..85.30 rows=21 width=48) (actual time=0.912..1.384 rows=21.00 loops=1)
  ->  Index Scan using orders_user_created_idx on orders  (cost=0.70..85.30 rows=21 width=48) (actual time=0.910..1.379 rows=21.00 loops=1)
        Index Cond: ((user_id = 4812) AND (ROW(created_at, id) < ROW(...)))
        Buffers: shared hit=6 read=19

Now it's an Index Cond: one seek, 21 adjacent entries. The reads are mostly heap pages, one per order, for status and total.

Plan 3: the same keys plus INCLUDE (status, total).

Limit  (cost=0.70..4.90 rows=21 width=48) (actual time=0.048..0.081 rows=21.00 loops=1)
  ->  Index Only Scan using orders_user_created_cov on orders  (cost=0.70..4.90 rows=21 width=48) (actual time=0.047..0.077 rows=21.00 loops=1)
        Index Cond: ((user_id = 4812) AND (ROW(created_at, id) < ROW(...)))
        Heap Fetches: 0
        Buffers: shared hit=5

Heap Fetches: 0: the visibility map let it skip the table. Part 7 counted index entries; here's the cost in pages: five, all cached. If Heap Fetches climbs, vacuum is behind (Chapter 6). Also compare estimated and actual rows: a big gap means stale statistics.

05Chapter 5: The Anomalies, and What Each Level Prevents#

5.1 Five Anomalies#

Part 6 tabled the three ANSI phenomena. Berenson et al. showed those definitions "fail to characterize several popular isolation levels" and introduced new phenomena, among them lost update and write skew ("A Critique of ANSI SQL Isolation Levels", SIGMOD 1995), the two behind the Preface's ghee and coupon bugs. On Basketly:

Write skew is the hard one: no row is written twice, so row-level conflict checks never see it.

5.2 What PostgreSQL and InnoDB Actually Do#

Both use snapshots (Chapter 6), but they treat writes differently:

Read-then-write in application code loses updates on both defaults. Atomic statements avoid it: UPDATE products SET stock = stock - 1 WHERE id = 7 AND stock >= 1 is safe even at Read Committed. In PostgreSQL the second updater waits for the first to commit, then "the search condition of the command (the WHERE clause) is re-evaluated" against the updated row, so it updates 0 rows.

For the coupon, the fix is a constraint: UNIQUE (customer_id, coupon_code) on the uses table makes the second insert fail at any level, because the rule now lives in one row the database can check. Where no constraint fits, use Serializable and retry on 40001. Week 5, Day 1 connects isolation to distributed consistency.

06Chapter 6: MVCC: How Snapshots Are Built#

Multi-version concurrency control keeps old row versions so each transaction reads a consistent snapshot while others write: "reading never blocks writing and writing never blocks reading" (PostgreSQL docs). The two engines keep old versions in opposite places.

%% title: Where old versions live: PostgreSQL's heap versus InnoDB's undo log
flowchart TD
  subgraph PG["PostgreSQL: new version in the heap"]
    direction TB
    IX1["Index entries<br/>point at a ctid"]:::service --> V1["Tuple v1 · stock=1<br/>xmin 900 · xmax 905"]:::bad
    IX1 --> V2["Tuple v2 · stock=0<br/>xmin 905 · xmax 0"]:::ok
    V1 -->|"t_ctid"| V2
    VAC["VACUUM removes v1<br/>once no snapshot needs it"]:::core -.-> V1
  end
  subgraph INNO["InnoDB: update in place, old version in undo"]
    direction TB
    CI["Clustered index row · stock=0<br/>DB_TRX_ID 905"]:::ok -->|"DB_ROLL_PTR"| U1["Undo record<br/>stock=1 · trx 900"]:::bad
    U1 --> U2["Older undo records"]:::bad
    PUR["Purge discards undo<br/>once no read view needs it"]:::core -.-> U1
  end

6.1 PostgreSQL: Versions in the Table#

Each tuple header has t_xmin (the inserting transaction), t_xmax (the deleting one) and t_ctid (the newer version). An UPDATE writes a new tuple and sets xmax on the old one; a snapshot compares those IDs with what had committed when it was taken.

The old tuple stays until VACUUM: it "must not be deleted while it is still potentially visible to other transactions". VACUUM marks the space for reuse; VACUUM FULL shrinks the file but locks the table exclusively.

VACUUM also freezes old rows. Transaction IDs are 32 bits, so after about 4 billion transactions the counter wraps, and "transactions that were in the past appear to be in the future": their rows vanish. Autovacuum forces a VACUUM on any table whose oldest unfrozen XID is 200 million transactions old (the autovacuum_freeze_max_age default). Before sharding, Notion saw "the Postgres VACUUM process began to stall consistently", with "the prospect of transaction ID (TXID) wraparound" looming, when Postgres "would stop processing all writes" (Notion Engineering, 2021).

6.2 InnoDB: Versions in the Undo Log#

Each row carries a hidden 6-byte DB_TRX_ID (the last writer) and a 7-byte DB_ROLL_PTR to an undo log record that rebuilds the previous version. Rows are updated in place; an older snapshot follows the undo chain back.

Purge discards update undo once no snapshot could need it. The docs' advice applies to both engines: "commit transactions regularly, including transactions that issue only consistent reads", or "the rollback segment may grow too big".

07Chapter 7: Locking: Pessimistic and Optimistic#

7.1 SELECT … FOR UPDATE#

A locking read makes other writers wait for the row:

BEGIN;
SELECT stock FROM products WHERE id = 7 FOR UPDATE;   -- the second checkout waits here
-- check stock >= 1, compute the price, build the order
UPDATE products SET stock = stock - 1 WHERE id = 7;
INSERT INTO orders (...) VALUES (...);
COMMIT;                                                -- lock released

• NOWAIT errors instead of waiting. SKIP LOCKED skips locked rows: "an inconsistent view of the data", wrong for general work but right for "multiple consumers accessing a queue-like table" (PostgreSQL docs).
• InnoDB locks ranges. At Repeatable Read, WHERE slot BETWEEN 10 AND 20 FOR UPDATE takes next-key locks that block inserts into the range.
• Keep it short, with no network calls inside, and lock rows in ID order to avoid deadlocks.

7.2 Optimistic Concurrency With a Version Column#

Locks live inside one transaction. A manager who opens "edit product", thinks for two minutes and saves is outside any. Optimistic concurrency detects the conflict instead: each row has a version, and an update only succeeds if it hasn't changed. It's the database half of Part 7's ETag and If-Match.

class Conflict(Exception):
    """The row changed after we read it: reload and ask the user."""

def save_price(db, product_id: int, new_price: int, version_seen: int) -> int:
    cur = db.execute(
        """UPDATE products
              SET price = %s, version = version + 1
            WHERE id = %s AND version = %s""",
        (new_price, product_id, version_seen),
    )
    if cur.rowcount == 0:                 # version moved on, or the row is gone
        raise Conflict(product_id)        # API layer → 412 Precondition Failed
    db.commit()
    return version_seen + 1               # the new ETag

%% title: Two managers edit one price: the version column catches the second save
sequenceDiagram
  autonumber
  participant A as Manager A
  participant B as Manager B
  participant DB as products row
  A->>DB: read price 45, version 17
  B->>DB: read price 45, version 17
  A->>DB: UPDATE price 48 WHERE version = 17
  DB-->>A: 1 row, now version 18
  B->>DB: UPDATE price 42 WHERE version = 17
  DB-->>B: 0 rows, conflict
  Note over B: reload, see 48, decide again

08Chapter 8: A Decision Framework#

8.1 Start From the Access Patterns#

Choose from the queries you must serve: by key, by range within a key, ad hoc, by text or by time? Which invariants span rows (stock ≥ 0, one coupon each) and need transactions in one place? Updates or appends? Does one node with replicas suffice, or is partitioning needed from day one (Week 1, Day 1)?

%% title: Picking the store behind an endpoint
flowchart TD
  Q{"Invariants across rows,<br/>or ad hoc queries?"} -->|"yes"| PG@{ icon: "tech:postgresql", label: "Relational<br/>PostgreSQL · MySQL" }
  Q -->|"no"| K{"Every read by a<br/>known key?"}
  K -->|"yes, small values"| KV@{ icon: "tech:dynamodb", label: "Key-value<br/>DynamoDB · Redis" }
  K -->|"key + range, huge writes"| WC@{ icon: "tech:cassandra", label: "Wide-column<br/>Cassandra · ScyllaDB" }
  K -->|"no"| T{"Text relevance,<br/>or time rollups?"}
  T -->|"text"| ES@{ icon: "tech:elasticsearch", label: "Search index<br/>fed from the primary store" }
  T -->|"time"| TS@{ icon: "tech:clickhouse", label: "Time-series or columnar<br/>ClickHouse · TimescaleDB" }
  T -->|"neither: whole aggregates"| DOC@{ icon: "tech:mongodb", label: "Document<br/>MongoDB" }
  class PG,KV,WC,DOC store
  class ES,TS service

The default is relational. Leave it for an access pattern you can name. A search index or cache is a copy, not the record.

8.2 What Real Companies Chose#

• Notion stayed on PostgreSQL and sharded it by workspace ID into 480 logical shards on 32 physical databases, since "each block belongs to exactly one workspace". It judged switching to DynamoDB "too risky for our use case" (Notion Engineering, 2021).
• Discord moved messages from MongoDB to Cassandra. Reads "were extremely random and our read/write ratio was about 50/50"; the key was ((channel_id, bucket), message_id) with Snowflake message IDs and ~10-day buckets (Discord Engineering, 2017). By 2022 that was 177 Cassandra nodes fighting hot partitions, GC pauses and compaction backlogs; on 72 ScyllaDB nodes, p99 historical reads fell from 40–125 ms to 15 ms (Discord Engineering, 2023).
• Uber moved from PostgreSQL to MySQL in 2016, citing write amplification from index updates among other reasons (Uber Engineering, 2016).
• Key-value at scale: on Prime Day 2025, DynamoDB traffic from Amazon's own sites and systems (Alexa, Amazon.com, fulfilment centres) got "single-digit millisecond responses" while "peaking at 151 million requests per second" (AWS, August 2025).
• Search lags the source: Elasticsearch documents are searchable "within 1 second" by default (Elastic docs).

09Chapter 9: Generating IDs#

9.1 Auto-Increment, and Where It Stops#

BIGSERIAL or AUTO_INCREMENT is 8 bytes, ordered and appends at the index's right edge. Its limits: one counter means one writer, so shards and regions need coordination. Values have gaps: in InnoDB, values used by rolled-back transactions are "lost", and "such lost values are not reused", and the default lock mode (2, "interleaved") doesn't promise consecutive values. IDs leak volume (order 1,000,412 tells a competitor your count), which is why Part 7 made them opaque. And the ID exists only after the insert.

9.2 The Alternatives#

import os, time, uuid

def uuid7() -> uuid.UUID:
    """RFC 9562: unix_ts_ms(48) | ver(4) | rand_a(12) | var(2) | rand_b(62)."""
    ms = time.time_ns() // 1_000_000
    rand = int.from_bytes(os.urandom(10), "big")
    value = (ms & ((1 << 48) - 1)) << 80        # bits 127..80: ms since 1970
    value |= 0x7 << 76                          # version 7
    value |= ((rand >> 62) & 0xFFF) << 64       # rand_a
    value |= 0b10 << 62                         # variant
    value |= rand & ((1 << 62) - 1)             # rand_b
    return uuid.UUID(int=value)
# Same-millisecond IDs aren't ordered here; RFC 9562 §6.2 allows a counter in rand_a for that.

Snowflake (Twitter, 2010) fits in 64 bits. Twitter built it moving from MySQL to Cassandra, for "minimum 10k ids per second per process" without coordination (Twitter's Snowflake README):

63 bits, so the sign bit stays 0 and IDs fit a signed BIGINT. The hard parts are unique machine IDs and clocks: the README says "you should use NTP", and if the clock moves back Snowflake "will refuse to generate ids" until it passes the last one used. The Unique ID Generator problem builds one end to end.

ULID (a community spec): 48-bit milliseconds plus 80 random bits as 26 Crockford base32 characters, sortable as text.

Ticket servers (Flickr, 2010): a one-row MySQL table bumped with REPLACE INTO Tickets64 (stub) VALUES ('a'); SELECT LAST_INSERT_ID();. Two servers with auto_increment_increment = 2 and offsets 1 and 2 hand out odd and even IDs. Flickr rejected GUIDs because "GUIDs are big, and they index badly in MySQL"; the scheme had run "since Friday the 13th, January 2006" (Flickr Engineering, 2010).

10Chapter 10: Common Mistakes#

1. "Add an index" for every slow query. Each one taxes every write. Read EXPLAIN first: the fix may be column order, a partial index or a covering one.
2. Trusting the default isolation level with read-then-write code. Neither default prevents lost updates. Use guarded updates, FOR UPDATE or versions, and constraints for cross-row rules.
3. UUIDv4 primary keys on big write-heavy tables, especially InnoDB. Use UUIDv7 or Snowflake-style IDs.
4. Long transactions on the primary. They block vacuum and purge. Alert on transaction age; run reports on a replica.
5. NoSQL "for scale" without naming the access pattern. Notion sharded PostgreSQL instead.

11Chapter 11: Interview Tips#

11.1 When to Bring It Up#

When you pick the database (one sentence of why), when you sketch the data model (primary key, indexes for the top queries), and whenever money, stock or uniqueness appears (how concurrent writers can't break the rule).

11.3 Common Follow-Up Questions#

12Chapter 12: Practice Problems#

Further Reading#

Documentation#

• Blog

  PostgreSQL — Transaction Isolation: https://www.postgresql.org/docs/current/transaction-iso.html

• Blog

  PostgreSQL — Routine Vacuuming: https://www.postgresql.org/docs/current/routine-vacuuming.html

• Blog

  PostgreSQL — WAL Introduction: https://www.postgresql.org/docs/current/wal-intro.html

• Blog

  PostgreSQL — Index-Only Scans and Covering Indexes: https://www.postgresql.org/docs/current/indexes-index-only-scans.html

• Blog

  PostgreSQL — Using EXPLAIN: https://www.postgresql.org/docs/current/using-explain.html

• Docs

  MySQL 8.4 — InnoDB Transaction Isolation Levels: https://dev.mysql.com/doc/refman/8.4/en/innodb-transaction-isolation-levels.html

• Docs

  MySQL 8.4 — InnoDB Multi-Versioning: https://dev.mysql.com/doc/refman/8.4/en/innodb-multi-versioning.html

• Docs

  MySQL 8.4 — InnoDB Locking: https://dev.mysql.com/doc/refman/8.4/en/innodb-locking.html

• Wiki

  RocksDB — Tuning Guide: https://github.com/facebook/rocksdb/wiki/RocksDB-Tuning-Guide

Standards and Papers#

• Docs

  RFC 9562 — Universally Unique IDentifiers (May 2024): https://www.rfc-editor.org/rfc/rfc9562.html

• Paper

  O'Neil et al. — The Log-Structured Merge-Tree, Acta Informatica, 1996: https://www.cs.umb.edu/~poneil/lsmtree.pdf

• Read

  Berenson et al. — A Critique of ANSI SQL Isolation Levels, SIGMOD 1995: https://www.microsoft.com/en-us/research/publication/a-critique-of-ansi-sql-isolation-levels/

Engineering Posts#

• Read

  Twitter — Snowflake README (2010): https://github.com/twitter-archive/snowflake/tree/snowflake-2010

• Read

  Flickr — Ticket Servers (2010): https://code.flickr.net/2010/02/08/ticket-servers-distributed-unique-primary-keys-on-the-cheap/

• Blog

  Discord — Billions of Messages (2017) and Trillions of Messages (2023): https://discord.com/blog/how-discord-stores-billions-of-messages · https://discord.com/blog/how-discord-stores-trillions-of-messages

• Blog

  Uber — Why Uber Switched from Postgres to MySQL (2016): https://www.uber.com/blog/postgres-to-mysql-migration/

• Blog

  Notion — Sharding Postgres at Notion (2021): https://www.notion.com/blog/sharding-postgres-at-notion

Book#

• Read

  Martin Kleppmann — Designing Data-Intensive Applications, chapters 3 and 7

Week 0 Complete! You now have a solid foundation in:

• Read

  System design methodology (Part 1)

• Read

  Infrastructure building blocks (Part 2)

• Read

  Back-of-envelope estimation (Part 3)

• Read

  Networking fundamentals (Part 4)

• Read

  Operating system concepts (Part 5)

• Read

  Theory and terminology (Part 6)

• Docs

  API design (Part 7)

• Read

  Databases in depth (Part 8)

Next: Week 1 — Data at Scale (the Week 1 preview). Everything here ran on one server. Week 1 spreads Basketly's billion orders across many: Day 1 partitions them (the shard key is often the column that leads your composite indexes), and Day 2 replicates them by shipping the WAL from Chapter 1.