System Design
D1

Storage engines: B-trees and LSM trees

A storage engine decides how rows reach the disk. A B-tree keeps one sorted copy and changes it in place. An LSM tree writes new sorted files and merges them later. This sheet measures what each one costs.

Not startedSaved in this browser only.
  1. 1A B-tree changes 4 kB pages in place. A random write reads a page, then writes the whole page back.
  2. 2An LSM tree only appends: log, memtable, sorted files. Compaction later rewrites each byte several times.
  3. 3Choose by the amplification you can afford: write, read or space. An engine lowers one by paying in another.
  4. 4Bloom filters make an LSM point read cost about one block. A range scan still reads every sorted run.
D1
    A

    One small write

    what reaches the disk
    B-treeput 116 Bone entrylog record140 B, appendedread the leaf0.63 reads per putwrite the page4,096 B, in placeA random key lands on any leaf. Each put dirties a whole page,and most pages leave the cache before a second put reaches them.LSM treeput 116 Bone entrylog record140 B, appendedmemtablesorted, in memoryflush, compactrewritten per levelA put never reads. Each byte goes to the log, is flushed once,then is copied again by each compaction that reaches it.bytes written per 116 B putrandom inserts, recordedB-tree2,995 B, 25.82×LSM, leveled1,059 B, 9.13×LSM, tiered578 B, 4.98×

    Recorded from the simulation in panel I: 200,000 random inserts of 116 bytes, 4 kB pages, a 4 MB cache.

    A disk writes whole pages. A B-tree pays a page for each scattered small write; an LSM tree pays later, in compaction.

    B

    Three costs

    amplification, recorded
    costB-treeleveledtiered
    Write: bytes on disk per byte put, random keys25.82×9.13×4.98×
    Write: sequential keys2.62×2.29×4.98×
    Read: pages or runs checked per lookup34.867.29
    Read: disk reads per lookup0.890.860.87
    Space: disk per live byte, after updates1.54×1.52×1.95×

    The B-tree's space cost is its 70% full pages. The LSM trees' is old versions that wait for compaction.

    I compare engines by write, read and space amplification. Each engine lowers one by paying in another.

    C

    Who uses which

    documented defaults
    databaseenginelayout
    PostgresB-treeRows in a heap of 8 kB pages. Indexes are B-trees that point into the heap.
    MySQL InnoDBB-treeRows live inside a B-tree ordered by the primary key (a clustered index).
    MongoDBB-treeThe WiredTiger engine keeps collections and indexes in B-trees.
    RocksDBLSMLeveled compaction by default. Universal (tiered) and FIFO on request.
    CassandraLSMSize-tiered by default. Leveled and time-window per table.
    ScyllaDBLSMSize-tiered by default, like Cassandra, with the same file format.
    CockroachDBLSMPebble, an LSM engine modelled on RocksDB.

    Postgres and InnoDB are B-trees. RocksDB, Cassandra and ScyllaDB are LSM trees.

    D

    Two structures

    pages in place, or sorted files
    B-treepages of 4 kB; a leaf holds 33, an inner page 145 childrenrootinnerinnerinnerfull: next insert splits itA lookup reads one page per level: 3 levels for 200,000 keys.A write changes its leaf in place. Leaves link left to right,so a range scan walks along them.LSM treesorted files (SSTables), never changed in placememorymemtable, 256 kBfilters and block indexes10 bits a key, one per filediskL0flushed memtables; ranges overlapL11 MB targetL210× L1L3most of the data, 10× L2A write goes to the log and the memtable. A full memtable becomesan L0 file. Compaction merges files down, level by level.

    A B-tree has one copy of each key, in a page it changes in place. An LSM tree can hold several versions of a key, in files it never changes.

    E

    Which engine for which workload

    from the recorded runs
    workloadB-tree PostgresLSM, leveled RocksDBLSM, tiered Cassandra
    Writes with random keys, data bigger than memoryPage per writeApprovedFewest rewrites
    Writes with sequential or time-ordered keysApprovedApprovedApproved
    Point reads by keyApprovedWith filtersMore runs to check
    Range scansApprovedOne read per runMany runs
    Frequent updates of the same keysHot pages cachedVersions until compactionSpace grows
    Little free disk space70% full pagesApproved2x during a merge
    Many deletes, queue-like tablesNeeds VACUUMTombstonesTombstones
    Transactions across many rowsApprovedEngine-dependentPer partition only

    Transactions belong to the database, not the engine. Cassandra offers lightweight transactions on one partition. CockroachDB and TiKV run full transactions over LSM engines.

    I pick the B-tree by default. I move a table to an LSM engine when random writes are the bottleneck and reads are mostly by key.

    F

    Write and read paths

    click a step; its path lights up
    Applicationputs and getsPostgres logappend onlyShared bufferspages in memoryData filespages, in placeRocksDB logappend onlyMemtablesorted, in memoryL0 filesone per flushL1 to Lneach 10x larger

    Step 1: B-tree write

    • Append the log record. The commit waits for this flush only.
    • Find the leaf through the buffer pool. Read it from disk if it is not there.
    • Change the page in memory and mark it dirty.

    If it fails

    A crash before the page reaches disk: replay the log from the last checkpoint.

    Both engines log first. The B-tree then changes a page in memory; the LSM tree adds to a memtable and merges files later.

    G

    Capabilities used

    what each engine gives you
    toolcapabilitywhat it gives this designalso used for
    PostgresWrite-ahead log, group commitA commit waits for one sequential log flush, not for page writes.Replication, point-in-time recovery
    PostgresShared buffers and checkpointsDirty pages are written later, in batches. Many changes to a hot page cost one write.Every query
    PostgresFull-page writes (full_page_writes = on)After a checkpoint, the first change to a page logs the whole 8 kB page. Recovery repairs a torn page from it.Base backups
    PostgresB-tree fill factor (90 for leaves)Leaves free space for later inserts. A split of the rightmost page leaves it 90% full.Tables, for HOT updates
    PostgresHOT updatesAn update that changes no indexed column, and fits on its page, writes no index entry.Counters, status columns
    PostgresVACUUM and autovacuumOld row versions are freed for reuse. The file rarely shrinks.Index-only scans (sheet D2)
    RocksDBMemtable and write-ahead logA write is an append to the log plus an insert in memory. No read.Under CockroachDB (Pebble) and TiKV
    RocksDBBloom filter per fileA point read skips most files in memory. 10 bits a key gives about 1% false positives.Joins, caches (sheet B4)
    RocksDBBlock cacheHot data blocks, filters and indexes stay in memory.Every read
    RocksDBLeveled compaction (the default)One file per level per read and little dead space, for more rewrites.Read-heavy tables
    RocksDBUniversal compaction (tiered)Fewer rewrites, for more runs to read and more space during a merge.Write-heavy ingest
    RocksDBWrite stalls (L0 file triggers)Limit Writes slow at 20 L0 files and stop at 36, so reads stay bounded.
    CassandraTime-window compactionTime series compact once per window, then expire as whole files.Metrics, logs
    CassandraTombstones, gc_grace_seconds (10 days)Limit A delete is a write. Space returns only after the grace period and a compaction.

    Defaults from the RocksDB tuning guide and the Cassandra documentation. RocksDB: 64 MB memtables, 4 L0 files start a compaction, L1 is 256 MB, each level is 10 times the last.

    Postgres gives me a log, a buffer pool and B-tree indexes. RocksDB gives me cheap writes, filters and a choice of compaction.

    H

    B-tree insert and split

    pseudo code
    put, with a page splitpseudo code
    put(key):
      log(key, value)               // append first1
      path = pages, root to leaf    // a miss is a disk read2
      leaf = last page of path; mark it dirty
      IF key is in leaf:
        overwrite the value; RETURN // an update, in place3
      insert key in sorted order
      IF leaf holds more than 33 entries:
        IF key went to the end of the rightmost leaf:
          keep 90% on the left4      // sequential keys
        ELSE: keep half on the left
        move the rest to a new page; log it
        add the new page to the parent  // it may split too5
    
    evict(page):
      IF page is dirty: write all 4,096 bytes6
    1. 1Write-ahead: the log record is durable before the page changes. The page itself can reach disk much later.
    2. 2A page that is not in the buffer pool is read from disk. With random keys that happens whenever the tree is bigger than memory.
    3. 3Same page, same slot. No second copy of the key exists anywhere.
    4. 4The rule Postgres uses. Sequential keys leave pages 90% full instead of half full.
    5. 5Splits climb toward the root. When the root splits, the tree grows one level.
    6. 6A 116-byte change still costs a whole page write. This is the B-tree write amplification.
    Tested source Go: put and split
    Go: put and splitgo
    // Put inserts key, or overwrites its value in place.
    func (t *BTree) Put(key uint64) {
      t.c.UserBytes += EntrySize
      t.c.WALBytes += WALHeader + EntrySize // the log record comes first
      path := t.descend(key, true)          // read the leaf; it is now dirty
      leaf := path[len(path)-1]
      i, found := slices.BinarySearch(leaf.keys, key)
      if found {
        return // an update: same page, same place
      }
      leaf.keys = slices.Insert(leaf.keys, i, key)
      t.live++
      if len(leaf.keys) > LeafCap {
        t.split(path, i == len(leaf.keys)-1)
      }
    }
    
    // split divides the full page at the bottom of path, and any parent that overflows in turn.
    func (t *BTree) split(path []*page, atEnd bool) {
      for d := len(path) - 1; d >= 0; d-- {
        p := path[d]
        rightmost := atEnd
        for j := d; j > 0 && rightmost; j-- {
          parent := path[j-1]
          rightmost = parent.kids[len(parent.kids)-1] == path[j]
        }
        var sep uint64
        var right *page
        if p.leaf() {
          if len(p.keys) <= LeafCap {
            return
          }
          cut := len(p.keys) / 2
          if rightmost {
            cut = LeafCap * rightLeafFill / 100 // sequential keys: keep the left page 90% full
          }
          right = t.alloc(true)
          right.keys = slices.Clone(p.keys[cut:])
          p.keys = p.keys[:cut]
          right.next, p.next = p.next, right
          sep = right.keys[0]
          t.c.WALBytes += WALHeader + int64(len(right.keys))*EntrySize // the moved half is logged
        } else {
          if len(p.kids) <= innerCap {
            return
          }
          cut := len(p.kids) / 2
          if rightmost {
            cut = innerCap * rightInnerFill / 100
          }
          right = t.alloc(false)
          sep = p.keys[cut-1]
          right.kids = slices.Clone(p.kids[cut:])
          right.keys = slices.Clone(p.keys[cut:])
          p.kids, p.keys = p.kids[:cut], p.keys[:cut-1]
          t.c.WALBytes += WALHeader + int64(len(right.kids))*(KeySize+8)
        }
        if d == 0 { // the root split: the tree grows one level
          root := t.alloc(false)
          root.keys, root.kids = []uint64{sep}, []*page{p, right}
          t.root = root
          t.height++
          return
        }
        parent := path[d-1]
        t.use(parent, true, false)
        at := slices.Index(parent.kids, p)
        parent.keys = slices.Insert(parent.keys, at, sep)
        parent.kids = slices.Insert(parent.kids, at+1, right)
      }
    }
    
    a full leaf: 33 entries, one more arrives33 of 33, 100%a random key: split in the middle17 of 33, 52%17 of 33, 52%the largest key on the rightmost leaf: keep 90%29 of 33, 88%5 of 33, 15%the new entryleaf pages full at the end, measuredsimulation, sequential keys88%simulation, random keys70%Postgres, bigint sequential90%Postgres, bigint random66%Postgres, uuid random72%

    A B-tree insert reads its leaf, changes it in place, and splits a full page. A dirty page costs a full page write when it leaves memory.

    I

    Try it: six workloads, three engines

    recorded from a seeded simulation

    Random inserts, 200,000 operations. The B-tree writes 25.82× the data and reads 0.63 pages per insert. The LSM trees never read to write: 9.13× leveled, 4.98× tiered.

    write amplificationB-tree25.82×LSM, leveled9.13×LSM, tiered4.98×
    disk reads per operationB-tree0.63LSM, leveled0LSM, tiered0
    space amplificationB-tree1.54×LSM, leveled1.08×LSM, tiered1.08×
    MB written for 22.13 MB of user dataB-tree: log 43.29 MB, pages or flushes 527.9 MB, compaction 0 MBB-tree571 MBLSM, leveled: log 26.7 MB, pages or flushes 23.89 MB, compaction 151.33 MBLSM, leveled202 MBLSM, tiered: log 26.7 MB, pages or flushes 23.89 MB, compaction 59.62 MBLSM, tiered110 MBlogpages, or flushescompaction
    B-tree
    height 3, 8727 pages, leaves 70% full
    LSM, leveled
    L0 3, L1 3, L2 39, L3 50 files
    LSM, tiered
    8 runs

    200,000 keys of 116 bytes, 4096-byte pages and blocks, a 4 MB cache for each engine, 256 kB memtables, seed 1. The cache holds about 15% of the data. A larger cache lowers the B-tree's write cost.

    Random writes favour the LSM tree; lookups of absent keys favour its filters; range scans and space favour the B-tree.

    J

    LSM put, flush and get

    pseudo code
    put and getpseudo code
    put(key):
      log(key, value)                     // append; never read1
      memtable[key] = (value, seq + 1)    // sorted, in memory
      IF memtable >= 256 kB:
        write it as one sorted file in L0 // a flush2
        compact while a trigger fires
    
    get(key):
      IF key in memtable: RETURN it
      FOR EACH run, newest first3:         // L0 files, then L1, L2 ...
        IF key is outside the run's range: skip
        IF the filter says no4: skip       // in memory, no disk read
        read one block
        IF key found: RETURN it           // a tombstone means deleted5
      RETURN not found
    1. 1The write path touches no data file. This is why LSM trees take random writes well.
    2. 2The memtable is written once, in key order, as an immutable file (an SSTable).
    3. 3A newer run holds a newer version, so the first version found wins.
    4. 4A Bloom filter is never wrong about "no". At 10 bits a key it says "maybe" for about 1% of absent keys.
    5. 5A delete writes a marker. It hides older versions until compaction drops them all.
    Tested source Go: put and flush · Go: get, with filters
    Go: put and flushgo
    // Put appends to the log and to the memtable. It never reads from disk.
    func (l *LSM) Put(key uint64) {
      l.c.UserBytes += EntrySize
      l.write(rec{key: key})
    }
    
    // Delete writes a tombstone. The old value stays on disk until compaction drops both.
    func (l *LSM) Delete(key uint64) {
      l.c.UserBytes += KeySize
      l.write(rec{key: key, tomb: true})
    }
    
    func (l *LSM) write(r rec) {
      l.seq++
      r.seq = l.seq
      l.c.WALBytes += WALHeader + r.size() - 8
      if old, ok := l.mem[r.key]; ok {
        l.memBytes -= old.size() // a newer version replaces it in memory
      }
      l.mem[r.key] = r
      l.memBytes += r.size()
      if l.memBytes >= l.cfg.MemtableBytes {
        l.flush()
      }
    }
    
    // flush writes the memtable as one sorted SSTable, then compacts if a trigger fires.
    func (l *LSM) flush() {
      if len(l.mem) == 0 {
        return
      }
      recs := slices.SortedFunc(maps.Values(l.mem), func(a, b rec) int { return cmp.Compare(a.key, b.key) })
      clear(l.mem)
      l.memBytes = 0
      t := l.newTable(recs)
      l.c.DataBytes += t.bytes
      if l.cfg.Compaction == Tiered {
        l.runs = slices.Insert(l.runs, 0, t)
        l.compactTiered()
        return
      }
      l.l0 = append(l.l0, t)
      l.compactLeveled()
    }
    
    Go: get, with filtersgo
    // probe looks for key in one SSTable. The filter is in memory; only a "maybe" costs a block read.
    func (l *LSM) probe(t *table, key uint64) (rec, bool) {
      if key < t.min || key > t.max {
        return rec{}, false // the key range, kept in memory, rules the table out
      }
      l.c.Probes++
      if t.filter != nil && !t.filter.mayContain(key) {
        return rec{}, false
      }
      i, found := slices.BinarySearchFunc(t.recs, key, func(r rec, k uint64) int { return cmp.Compare(r.key, k) })
      l.readBlock(t, t.block(min(i, len(t.recs)-1)))
      if !found {
        return rec{}, false // a false positive: one block read for nothing
      }
      return t.recs[i], true
    }
    
    // Get checks the memtable, then each sorted run from newest to oldest, and stops at the first
    // version it finds. A tombstone means the key was deleted.
    func (l *LSM) Get(key uint64) bool {
      if r, ok := l.mem[key]; ok {
        return !r.tomb
      }
      if l.cfg.Compaction == Tiered {
        return l.getTiered(key)
      }
      for i := len(l.l0) - 1; i >= 0; i-- {
        if r, ok := l.probe(l.l0[i], key); ok {
          return !r.tomb
        }
      }
      for lv := 1; lv < len(l.levels); lv++ {
        files := l.levels[lv]
        j := sort.Search(len(files), func(j int) bool { return files[j].max >= key })
        if j < len(files) {
          if r, ok := l.probe(files[j], key); ok {
            return !r.tomb
          }
        }
      }
      return false
    }
    

    An LSM put appends to the log and the memtable. A get checks the memtable, then each run, newest first, and the filters skip most runs.

    K

    Compaction

    pseudo code
    leveled and tieredpseudo code
    leveled:
      IF L0 has 4 files:
        merge all of L01 with the L1 files they overlap
      ELSE IF a level is over its target:  // L1 1 MB, then 10x each
        pick the next file of that level, round robin by key2
        merge it with the files it overlaps one level down
      IF no file overlaps: move it down, rewrite nothing3
    
    merge(files):
      keep the newest version of each key
      drop a tombstone4 IF no lower level can hold the key
      cut the output into files of 256 kB
    
    tiered:
      IF 4 runs have a similar size5: merge them into one run
    1. 1L0 files overlap, because each is one memtable. Merging them into L1 restores one sorted run.
    2. 2Each compaction takes the next key range, so the whole level is rewritten evenly.
    3. 3A trivial move. Sequential keys never overlap, so they reach the bottom level with no rewrite.
    4. 4Only at the bottom of the key range. Above it, the tombstone must still hide older versions.
    5. 5Cassandra groups runs within half and one and a half times the bucket average.
    Tested source Go: leveled compaction
    Go: leveled compactiongo
    // compactLeveled runs until no trigger fires: L0 has too many files, or a level is over its target.
    func (l *LSM) compactLeveled() {
      for {
        if len(l.l0) >= l.cfg.L0Trigger {
          l.compactL0()
          continue
        }
        lv := 0
        for i := 1; i < len(l.levels); i++ {
          if levelBytes(l.levels[i]) > l.target(i) {
            lv = i
            break
          }
        }
        if lv == 0 {
          return
        }
        l.compactLevel(lv)
      }
    }
    
    // compactL0 merges every L0 file with the L1 files whose key ranges they meet.
    func (l *LSM) compactL0() {
      in := l.l0
      l.l0 = nil
      lo, hi := in[0].min, in[0].max
      for _, f := range in {
        lo, hi = min(lo, f.min), max(hi, f.max)
      }
      over, rest := overlap(l.levels[1], lo, hi)
      if len(over) == 0 && disjoint(in) {
        l.levels[1] = byMin(append(rest, in...)) // a trivial move: no byte is rewritten
        return
      }
      l.levels[1] = byMin(append(rest, l.merge(append(over, in...), 1)...))
    }
    
    // compactLevel pushes one file of level lv into lv+1, taking files in key order, round robin.
    func (l *LSM) compactLevel(lv int) {
      l.ensure(lv + 1)
      files := l.levels[lv]
      i := sort.Search(len(files), func(i int) bool { return files[i].min > l.ptr[lv] })
      if i == len(files) {
        i = 0
      }
      f := files[i]
      l.ptr[lv] = f.max
      l.levels[lv] = slices.Delete(slices.Clone(files), i, i+1)
      over, rest := overlap(l.levels[lv+1], f.min, f.max)
      if len(over) == 0 {
        l.levels[lv+1] = byMin(append(rest, f))
        return
      }
      l.levels[lv+1] = byMin(append(rest, l.merge(append(over, f), lv+1)...))
    }
    

    Leveled compaction keeps one sorted run per level and rewrites more. Tiered compaction rewrites less and leaves more runs to read.

    L

    Leveled or tiered

    what a compaction rewrites
    Leveledone sorted run per level; each level 10× the lastL1pick 1 fileL2Its key range meets about 10 files in L2.The merge rewrites all 11 to move 1 file down.Each level adds up to 10× of writes.recorded, random inserts and updateswrite amplification 9.13×space after updates 1.52×runs checked per read 4.86Size-tieredmerge 4 runs of a similar size into 1256 kB runs1 MB runs4 MB runA byte is rewritten once per tier, so fewer times.A read checks every run, and old versions stayuntil their runs merge.write amplification 4.98×space after updates 1.95×runs checked per read 7.29

    Leveled costs about the level ratio in rewrites per level. Tiered rewrites each byte once per tier but keeps more runs and old versions.

    M

    Key order in Postgres

    measured, 2 million rows
    primary keyrows a secondlog bytes a rowpage images per 1,000 rowsindex sizeleaf pagesleaves full
    bigint, sequential593,243133043 MB5,46590%
    bigint, random309,3121745.458 MB7,44366%
    uuid, random (v4)240,2732077.575 MB9,55972%
    the three insertssql
    INSERT INTO ins_seq SELECT g, g FROM generate_series($1::bigint, $2::bigint) g;
    
    -- A 32-bit hash of g in the high half and g in the low half: unique, in scattered order, and as
    -- cheap to compute as g itself.
    INSERT INTO ins_rand
    SELECT (hashint8(g)::bigint << 32) | g1, g FROM generate_series($1::bigint, $2::bigint) g;
    
    INSERT INTO ins_uuid SELECT gen_random_uuid()2, g FROM generate_series($1::bigint, $2::bigint) g;
    1. 1A scattered key that costs as little to compute as g. The run measures the index, not the key function.
    2. 2A version 4 UUID: 122 random bits, and 16 bytes against 8 for a bigint.
    • A sequential key appends to the rightmost leaf. That page stays in memory, and the split leaves it 90% full.
    • A random key lands on any leaf. After each checkpoint, the first change to a page logs the whole page.
    • Use a time-ordered key, such as a bigint identity or UUID version 7, when the table takes many inserts.

    Postgres 16.14, shared_buffers 128MB, 10,000 rows per transaction, a checkpoint every 500,000 rows. Best of 3 rounds on a shared 8-core laptop; read the rates as orders of magnitude.

    Random primary keys make Postgres split leaves in the middle. The index grows, the log grows, and inserts run about half as fast.

    N

    Failure cases

    what breaks, and what limits it
    eventresultwhat limits itsaved by
    A write burst outruns compactionL0 files pile up, and each read checks more of them.RocksDB slows writes at 20 L0 files and stops them at 36. Rate-limit the producer.Write stall
    Size-tiered merges its largest runsThe merge needs free space equal to its inputs.Keep about half the disk free, or use leveled compaction.Free space
    A queue table on an LSM engineEach read skips past thousands of tombstones.Cassandra warns at 1,000 tombstones in one read and fails it at 100,000. Do not build queues on it.Tombstone limits
    Random primary keys in PostgresLeaves 66% full against 90%; inserts about half as fast.Use time-ordered keys: bigint identity, or UUID version 7.Key choice
    Many updates on a Postgres tableDead row versions bloat the table and its indexes.Autovacuum frees them. A lower fill factor lets updates stay on their page (HOT).VACUUM
    A crash in the middle of a page writeAn 8 kB page is half old, half new.Recovery copies the full-page image from the log over it.Full-page writes
    A checkpoint, then random insertsEach page's first change logs 8 kB: 5.4 images per 1,000 random rows, 0 for sequential.Spread checkpoints out (checkpoint_completion_target 0.9) and raise max_wal_size.Checkpoint tuning
    O

    Scale ladder

    start simple; climb only on a signal
    Each step adds one component1Postgres2+ ordered keys3+ LSM store4+ shards5+ compaction per tablemore load →
    Capacity against demand1k10k100k1MDemand, average: 10,000 rows per secondDemand, average10,000Demand, 10× peak: 100,000 rows per secondDemand, 10× peak100,000Postgres, uuid keys: 240,273 rows per secondPostgres, uuid keys240,273Postgres, random bigint: 309,312 rows per secondPostgres, random bigint309,312Postgres, sequential: 593,243 rows per secondPostgres, sequential593,243rows per second, log scale
    stepaddit handlesmove up when you see
    1One Postgres, B-tree indexes, the default settings.About 309,000 batched inserts a second with random keys on a laptop. Most applications never need more.Inserts slow down as the indexes outgrow memory.
    2Time-ordered keys and time partitions. Drop old partitions instead of DELETE.About 593,000 a second with sequential keys. Each insert touches the newest pages only.The disk's write bandwidth is full, or random writes cannot be avoided.
    3An LSM store for the write-heavy table: RocksDB inside a service, or Cassandra or ScyllaDB.At 500 MB/s of disk writes: about 472,000 random puts a second leveled, against 167,000 for the B-tree.One node's disk or CPU is full.
    4Shards: more nodes, spread by partition key (sheet X2).Throughput grows with the number of nodes.Different tables need different compaction.
    5Compaction per table: time-window for time series, leveled for read-heavy, tiered for write-heavy.Each table pays only the amplification it can afford.Top of the ladder.

    Demand example: 100,000 devices send one reading every 10 seconds, 10,000 writes a second. The 500 MB/s budget divides by write amplification × 116 bytes: 500,000,000 / (9.13 × 116). The disk rate is an assumption; the amplifications are recorded.

    I start with Postgres and time-ordered keys. I move one write-heavy table to an LSM store only when the disk's write bandwidth is the limit.

    P

    Drill

    predict, then reveal

    0 of 9 known

    1. Random inserts: why does the B-tree write 25.82 times the user data?

    2. Sequential keys bring the B-tree down to 2.62×. Why?

    3. Why does the leveled LSM tree write 0 compaction bytes for sequential keys?

    4. A lookup for a key that does not exist: what does each engine read from disk?

    5. Why do Bloom filters not help a range scan?

    6. An event log is written all day and read rarely. A user table is read 100 times per write. Which compaction for each?

    7. You delete 1 million rows from an LSM table. Why does disk use go up first?

    8. Postgres: why is the random-key index 58 MB and the sequential one 43 MB, for the same rows?

    9. Why does an LSM put never read from disk, and why does that matter?

    Q

    Numbers to say

    measured, recorded or cited
    write amp
    Random inserts: B-tree 25.82×, leveled 9.13×, tiered 4.98×. Recorded.
    page
    A 116-byte entry in a 4,096-byte page: one page write per put is 35×.
    fanout
    145 children per 4 kB inner page, so each level multiplies capacity by up to 145. 3 levels hold up to 693,825 entries.
    filter
    10 bits a key: 0.82% false positives in theory, 0.81% recorded.
    Postgres keys
    Sequential 593,243, random 309,312, UUID 240,273 rows a second. Measured.
    leaf fill
    90% with sequential keys, 66% with random ones. Measured.
    RocksDB
    64 MB memtable, 4 L0 files to compact, stall at 20, stop at 36, 10× per level. Documented defaults.
    Cassandra
    Tombstones: warning at 1,000 per read, failure at 100,000. Grace period 10 days. Documented defaults.

    Simulation: seed 1, deterministic. Postgres 16 on an 8-core laptop shared with other jobs. Use these as orders of magnitude.