B-Trees, LSM-Trees & the RUM Triangle

• Balanced binary tree over 10⁹ keys: height ≈ 30.
• Every pointer chase is a potential device read.
• Spinning disk, ~10 ms/seek: 300 ms per lookup.
• NVMe, ~80 µs: still ~2.4 ms.
• Each 4 KB page read used a 16-byte node — 0.4% useful.

• Interior node = pure navigation: separator key + child pointer.
• 8-byte key + 8-byte pointer = 16 bytes/entry.
• 4 KB page → 4096 / 16 = 256 entries.
• Call it ~250-way fanout after the header.
• Height: log₂₅₀ 10⁹ ≈ 20.7 / 5.5 ≈ 3.8.

levels instead of thirty — and levels 1–3 total 1 + 250 + 62,500 ≈ 63 k pages ≈ 250 MB, pinned in memory. A point lookup costs one device read: the leaf.

• External record identity = (page #, slot #).
• Engine shuffles cells freely within the page.
• No secondary-index entry is ever invalidated.
• Deletion marks a slot dead; page compacts lazily.
• Every size-changing update survives on this two-level indirection.

• Full leaf splits: half the entries move to a new page.
• Separator pushed into the parent — which may split too.
• The tree grows only at the root.
• Balance is a side effect of the growth rule.
• No rotations, no rebalancing pass, ever.

steady-state page occupancy under random inserts (ln 2) — a permanent ~1.44× space tax for being update-friendly. Monotonic keys + split-at-insertion-point cheat to ~100%-full pages.

• Textbooks merge pages below half full.
• Production engines mostly don’t — merging under concurrency is misery.
• Pages drift sparse; cleanup is offline: VACUUM, OPTIMIZE TABLE.
• Grew to 100 GB, shrank to 10 GB live? Still 100 GB on disk.

• Random UUIDv4 primary keys on InnoDB, write-heavy service.
• Every insert dirtied a cold leaf: buffer pool thrashed.
• Throughput collapsed to a few thousand rows/s.
• Switch to UUIDv7/ULID: inserts confined to the right edge.
• Throughput tripled — nothing in the schema changed.

• Steal: evict dirty pages of uncommitted transactions? Yes.
• No-force: flush all dirty pages at commit? No.
• Price: disk holds uncommitted changes, misses committed ones.
• WAL rule 1: log before dirty page reaches disk (undo).
• WAL rule 2: commit record durable before return (redo).

• LSNs stamp pages → redo is idempotent.
• Analysis: reconstruct live transactions from a fuzzy checkpoint.
• Redo: repeat history exactly — including the losers.
• Undo: roll losers back, writing CLRs.
• All this because B-trees mutate in place. Hold that thought.

• Workload: ingest a firehose, occasionally look things up.
• In-place writes become pure liability: random I/O, eviction fights.
• O’Neil, Cheng, Gawlick & O’Neil, 1996: never update in place.
• Buffer in memory, dump immutable sorted runs, merge in background.
• Under RocksDB, Cassandra, HBase — and most agent-memory stores.

• Append to WAL (sequential, cheap), insert into memtable (skiplist).
• At ~64 MB (RocksDB): freeze, write out as an SSTable.
• SSTable: immutable, sorted, block index, min/max keys, Bloom filter.
• Updates are newer versions; deletes are tombstones.
• Tombstones survive until every older version is provably buried.

• Check memtable, then SSTables newest to oldest.
• Newest version wins.
• Untreated: one block read per run the key might be in.
• Compaction, Bloom filters, RUM — all about managing this bill.

• WA = bytes physically written ÷ bytes logically written.
• RA = device reads per query ÷ reads logically needed.
• SA = bytes occupied ÷ bytes of live, unique data.
• Vendors won’t define them. You must.

write amplification ≈ 1 (WAL) + 1 (flush) + 10 × 4 (descents) — the folklore “~10× per level, 40–50× overall” is just this sum. Size-tiered, 3 tiers: WA ≈ 5.

B-tree WA for a 128-byte update: 4 KB leaf dirtied + full-page write in WAL = 8 KB for 128 bytes. The LSM often amplifies less, and sequentially — its sin is that the cost is deferred, bursty, and eats your p99.

** Compaction Stats [default] **
Level   Files  Size(GB)  Read(GB)  Write(GB)  W-Amp
  L0     4/0      0.25       0.0       62.1    1.0
  L1    10/1      0.62     601.7      598.9    9.6
  L2    98/3      6.21     580.4      577.8    9.3
  L3   940/8     62.05     551.2      549.0    8.9
 Sum                      1733.3     1787.8   28.8

Friday’s lab: predict the W-Amp column before you run it.

• m bits, k hashes; false positives only, never false negatives.
• FPR ≈ (1 − e^(−kn/m))^k, minimized at k = (m/n)·ln 2.
• Standard 10 bits/key: k = 7, FPR ≈ 0.8%.
• Expected reads ≈ 1 + 0.008 × (runs − 1).
• Twelve-run nightmare collapses to ~1.1 reads. Best deal in systems.

• A Bloom filter answers “is key X in this file?”
• It cannot answer “does anything in [a, b) live here?”
• Range scans get no help — every overlapping run consulted.
• “Memories similar to this embedding” isn’t even a range.
• There is no Bloom filter for meaning.

• Ingest: millions of small appends/hour, never updated in place.
• Recall: semantic top-k over all history — Bloom filters are spectators.
• Space: agents almost never delete; memory is the product.
• Most “AI memory” = LSM + vector index: chose U, pays R at query time.
• Week 6 adds recall quality — triangle becomes a tetrahedron.

• Redo the fanout math for 16 KB InnoDB pages with UUIDv4 keys — why does your DBA mutter about surrogate keys?
• 200 GB/day of agent memories, leveled T = 10, four levels: does a 7.68 TB SSD at 3 DWPD survive?
• Recall scans the newest 30 days: which compaction policy wins, and why don’t Bloom filters appear in your answer?

• Database Internals, ch. 2–4 & 7 — Petrov, O’Reilly 2019. Redo the fanout math for 16 KB pages.
• The RUM Conjecture — Athanassoulis et al., EDBT 2016. Which corner did your last system silently choose?
• The LSM-Tree — O’Neil et al., Acta Informatica 1996, §1–3. Which HDD-era assumptions did flash break?