Every relational database you've ever used made a bet at the storage-engine level, before you wrote a single query: reads matter more than writes, or writes matter more than reads. That bet shows up as a choice between two data structures, a B-tree or an LSM-tree. Most engineers never think about it. It's still deciding how their database behaves under load.
The actual tradeoff
A B-tree updates data in place. It walks down to the exact leaf page a key belongs on and rewrites it right there, so the tree stays fully sorted at all times. A read never does more than follow one path from root to leaf. There's no ambiguity about where a key lives, because there's only ever one copy of it.
An LSM-tree (log-structured merge-tree) refuses to do that. It never touches old data on a write. Instead it buffers writes in memory, flushes them to new immutable files on disk, and merges those files together later in the background. A write is a sequential append, not a random rewrite, which is why LSM-trees dominate anywhere ingest volume is the bottleneck: Cassandra, RocksDB, LevelDB, HBase. B-trees stay the default for read-heavy, point-lookup workloads: Postgres, MySQL's InnoDB, basically every traditional RDBMS.
What's worth understanding is what each side gives up, not which one "wins."
B-trees pay at write time. Once your dataset outgrows memory, a page update that used to be a cheap in-memory write becomes a real disk seek. Under heavy write load the tree also spends real time rebalancing, splitting pages as they fill so every leaf stays at the same depth.
LSM-trees pay at read time instead. A single key can exist simultaneously in the in-memory memtable, the most recently flushed file, and several older files. The newest write wins, but a lookup has to check each location until it finds a match. Bloom filters make this bearable in practice (a probabilistic "this file definitely doesn't have your key" check that skips most files without opening them), but a worst-case LSM-tree lookup is still checking more places than a B-tree ever has to.
There's also a cost LSM-trees defer rather than eliminate: compaction, the background process merging small files into larger ones and dropping superseded key versions. This is the part people underestimate. It's not free maintenance sitting quietly in the background, it's competing for the same disk I/O and CPU your live traffic needs. A compaction backlog is the classic LSM-tree failure mode, and it's an ugly one: writes outpace merging, read latency climbs as lookups check more uncompacted files, and the whole thing spirals if nothing throttles incoming writes to let compaction catch up. If you've operated Cassandra under sustained write pressure, you've probably met this one at 3am.
Where this actually bites you
This decision almost never gets made consciously by application code. It gets made once, implicitly, by whichever database a team picked years ago. So the first time most engineers actually think about it is after a write-heavy workload starts struggling on a B-tree-backed system that was never built for it. Time-series data, event logging, anything ingesting far more than it reads back in the same window: that's LSM-tree territory. Building it on Postgres because "that's what we use" is how you end up fighting the storage engine instead of your actual problem.
Some engines hedge instead of picking a side outright. MySQL's MyRocks storage engine swaps InnoDB's B-tree for an LSM-tree underneath the same relational interface, specifically for workloads write-bound enough to justify the read-side cost.
I wrote up the fuller comparison, including read and write amplification and where each structure shows up in real systems, here, if you want the longer version of this post.
Seeing it instead of reading about it
The part that's genuinely hard to build intuition for from a blog post is why a B-tree stays shallow even at billions of rows. So I built an interactive B-tree visualizer. Insert keys and watch a node split once it fills, the middle key pushing up into the parent every time. Search afterward and it highlights exactly which nodes the lookup touches. Entirely client-side, no backend. Took me about four minutes of playing with it to have the mechanism actually click in a way the text version never quite managed.
Next time someone asks why adding an index didn't help their write-heavy table, or why their time-series database "just feels different" from Postgres under load, this is the mechanism underneath both answers.
Top comments (0)