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.
- 1A B-tree changes 4 kB pages in place. A random write reads a page, then writes the whole page back.
- 2An LSM tree only appends: log, memtable, sorted files. Compaction later rewrites each byte several times.
- 3Choose by the amplification you can afford: write, read or space. An engine lowers one by paying in another.
- 4Bloom filters make an LSM point read cost about one block. A range scan still reads every sorted run.
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.
| cost | B-tree | leveled | tiered |
|---|---|---|---|
| Write: bytes on disk per byte put, random keys | 25.82× | 9.13× | 4.98× |
| Write: sequential keys | 2.62× | 2.29× | 4.98× |
| Read: pages or runs checked per lookup | 3 | 4.86 | 7.29 |
| Read: disk reads per lookup | 0.89 | 0.86 | 0.87 |
| Space: disk per live byte, after updates | 1.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.
| database | engine | layout |
|---|---|---|
| Postgres | B-tree | Rows in a heap of 8 kB pages. Indexes are B-trees that point into the heap. |
| MySQL InnoDB | B-tree | Rows live inside a B-tree ordered by the primary key (a clustered index). |
| MongoDB | B-tree | The WiredTiger engine keeps collections and indexes in B-trees. |
| RocksDB | LSM | Leveled compaction by default. Universal (tiered) and FIFO on request. |
| Cassandra | LSM | Size-tiered by default. Leveled and time-window per table. |
| ScyllaDB | LSM | Size-tiered by default, like Cassandra, with the same file format. |
| CockroachDB | LSM | Pebble, an LSM engine modelled on RocksDB. |
Postgres and InnoDB are B-trees. RocksDB, Cassandra and ScyllaDB are LSM trees.
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.
| workload | B-tree Postgres | LSM, leveled RocksDB | LSM, tiered Cassandra |
|---|---|---|---|
| Writes with random keys, data bigger than memory | Page per write | Approved | Fewest rewrites |
| Writes with sequential or time-ordered keys | Approved | Approved | Approved |
| Point reads by key | Approved | With filters | More runs to check |
| Range scans | Approved | One read per run | Many runs |
| Frequent updates of the same keys | Hot pages cached | Versions until compaction | Space grows |
| Little free disk space | 70% full pages | Approved | 2x during a merge |
| Many deletes, queue-like tables | Needs VACUUM | Tombstones | Tombstones |
| Transactions across many rows | Approved | Engine-dependent | Per 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.
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.
| tool | capability | what it gives this design | also used for |
|---|---|---|---|
| Postgres | Write-ahead log, group commit | A commit waits for one sequential log flush, not for page writes. | Replication, point-in-time recovery |
| Postgres | Shared buffers and checkpoints | Dirty pages are written later, in batches. Many changes to a hot page cost one write. | Every query |
| Postgres | Full-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 |
| Postgres | B-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 |
| Postgres | HOT updates | An update that changes no indexed column, and fits on its page, writes no index entry. | Counters, status columns |
| Postgres | VACUUM and autovacuum | Old row versions are freed for reuse. The file rarely shrinks. | Index-only scans (sheet D2) |
| RocksDB | Memtable and write-ahead log | A write is an append to the log plus an insert in memory. No read. | Under CockroachDB (Pebble) and TiKV |
| RocksDB | Bloom filter per file | A point read skips most files in memory. 10 bits a key gives about 1% false positives. | Joins, caches (sheet B4) |
| RocksDB | Block cache | Hot data blocks, filters and indexes stay in memory. | Every read |
| RocksDB | Leveled compaction (the default) | One file per level per read and little dead space, for more rewrites. | Read-heavy tables |
| RocksDB | Universal compaction (tiered) | Fewer rewrites, for more runs to read and more space during a merge. | Write-heavy ingest |
| RocksDB | Write stalls (L0 file triggers) | Limit Writes slow at 20 L0 files and stop at 36, so reads stay bounded. | |
| Cassandra | Time-window compaction | Time series compact once per window, then expire as whole files. | Metrics, logs |
| Cassandra | Tombstones, 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.
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- 1Write-ahead: the log record is durable before the page changes. The page itself can reach disk much later.
- 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.
- 3Same page, same slot. No second copy of the key exists anywhere.
- 4The rule Postgres uses. Sequential keys leave pages 90% full instead of half full.
- 5Splits climb toward the root. When the root splits, the tree grows one level.
- 6A 116-byte change still costs a whole page write. This is the B-tree write amplification.
Tested source Go: put and split
// 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 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.
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.
- 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.
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- 1The write path touches no data file. This is why LSM trees take random writes well.
- 2The memtable is written once, in key order, as an immutable file (an SSTable).
- 3A newer run holds a newer version, so the first version found wins.
- 4A Bloom filter is never wrong about "no". At 10 bits a key it says "maybe" for about 1% of absent keys.
- 5A delete writes a marker. It hides older versions until compaction drops them all.
Tested source Go: put and flush · Go: get, with filters
// 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()
}
// 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.
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- 1L0 files overlap, because each is one memtable. Merging them into L1 restores one sorted run.
- 2Each compaction takes the next key range, so the whole level is rewritten evenly.
- 3A trivial move. Sequential keys never overlap, so they reach the bottom level with no rewrite.
- 4Only at the bottom of the key range. Above it, the tombstone must still hide older versions.
- 5Cassandra groups runs within half and one and a half times the bucket average.
Tested source Go: leveled compaction
// 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.
Leveled costs about the level ratio in rewrites per level. Tiered rewrites each byte once per tier but keeps more runs and old versions.
| primary key | rows a second | log bytes a row | page images per 1,000 rows | index size | leaf pages | leaves full |
|---|---|---|---|---|---|---|
| bigint, sequential | 593,243 | 133 | 0 | 43 MB | 5,465 | 90% |
| bigint, random | 309,312 | 174 | 5.4 | 58 MB | 7,443 | 66% |
| uuid, random (v4) | 240,273 | 207 | 7.5 | 75 MB | 9,559 | 72% |
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;- 1A scattered key that costs as little to compute as g. The run measures the index, not the key function.
- 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.
| event | result | what limits it | saved by |
|---|---|---|---|
| A write burst outruns compaction | L0 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 runs | The 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 engine | Each 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 Postgres | Leaves 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 table | Dead 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 write | An 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 inserts | Each 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 |
| step | add | it handles | move up when you see |
|---|---|---|---|
| 1 | One 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. |
| 2 | Time-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. |
| 3 | An 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. |
| 4 | Shards: more nodes, spread by partition key (sheet X2). | Throughput grows with the number of nodes. | Different tables need different compaction. |
| 5 | Compaction 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.
0 of 9 known
Random inserts: why does the B-tree write 25.82 times the user data?
Sequential keys bring the B-tree down to 2.62×. Why?
Why does the leveled LSM tree write 0 compaction bytes for sequential keys?
A lookup for a key that does not exist: what does each engine read from disk?
Why do Bloom filters not help a range scan?
An event log is written all day and read rarely. A user table is read 100 times per write. Which compaction for each?
You delete 1 million rows from an LSM table. Why does disk use go up first?
Postgres: why is the random-key index 58 MB and the sequential one 43 MB, for the same rows?
Why does an LSM put never read from disk, and why does that matter?
- 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.