Every storage engine has to put a new version of a record somewhere, and there are only two honest places: overwrite the old one in place, or append the new one and clean up later. B-trees overwrite. LSM-trees append. That single decision sets the write amplification, the read amplification, the space amplification and the tail latency of everything above it, and it is why two databases with identical SQL surfaces behave nothing alike under a write-heavy load.
A B-tree turns one logical update into two to four random page writes, while an LSM-tree turns it into one sequential append plus ten to thirty bytes of compaction rewrite per byte stored — the bill arrives on different hardware counters.
Part One: What is being compared
A B-tree keeps one sorted copy of the data. A write descends the tree, latches the leaf page, changes a few bytes, writes the page back, and records the change in a write-ahead log so a crash can be repaired. Pages are fixed size, usually 4 to 16 kilobytes, and the tree stays balanced by splitting pages when they fill.
An LSM-tree keeps many sorted copies. A write lands in a memory table and in a log, and when the table fills it is flushed as an immutable sorted run. Background compaction merges runs so the number of copies does not grow without bound. Reads consult the memory table and then the runs, newest to oldest, using Bloom filters to skip the ones that cannot contain the key.
Both are mature. InnoDB, PostgreSQL and SQL Server are B-tree engines at heart. RocksDB, LevelDB, Cassandra, ScyllaDB and the storage layer under many time-series systems are LSM. Neither is a research prototype, so the comparison is about workload shape rather than maturity.
Part Two: The arithmetic
Take a mainstream NVMe drive: about 3.5 gigabytes per second of sequential write, roughly 80,000 random 4-kilobyte write IOPS, and a few hundred microseconds of latency per random operation. Those two numbers differ by an order of magnitude, and the entire debate lives in that gap.
A B-tree update dirties a leaf page, and the write-back plus the log record plus the eventual checkpoint means roughly two to four physical page writes per logical update. At 80,000 random IOPS and four pages each, the ceiling lands near 20,000 updates per second, and each one costs a seek-equivalent stall. A page split doubles the work for that insert and briefly holds a latch that other writers queue behind.
An LSM-tree writes the record sequentially into the log and the memory table, then flushes a sorted file. Sequential bandwidth looks generous until compaction is counted: with a levelled policy and a size ratio of ten, each byte is rewritten roughly ten to twenty times over its life. Divide 3.5 gigabytes per second by fifteen and the sustainable user write rate is about 230 megabytes per second, which is roughly 58,000 four-kilobyte updates per second. That is two to three times the B-tree ceiling on the same device, and the writes arrive as large sequential batches rather than scattered pages.
Reads invert the picture. A B-tree lookup with the upper levels cached touches one or two pages, so a point read is close to one device access and a handful of microseconds of comparison work. An LSM lookup must check every level: five to seven Bloom filters at roughly 100 nanoseconds of hashing each, then usually one real read. The filters keep the average near 1.05 device reads, but the CPU cost is paid on every single lookup, and a filter with a 1 percent false-positive rate still sends one in a hundred reads to disk for nothing.
Part Three: Where the model breaks
The clean crossover assumes uniform keys and a device that behaves like its datasheet. Both assumptions fail in production.
B-trees fragment. Random inserts into a tree with 16-kilobyte pages leave pages half full, so space amplification drifts toward 1.5 to 2 times the logical data, and the working set that must stay cached grows with it. A tree that fit in 64 gigabytes of RAM at load time may need 100 gigabytes a year later, which is the quiet way a database stops being memory-resident.
LSM-trees stall. Compaction competes with foreground writes for the same device, and when incoming writes outrun compaction the engine applies back pressure: write stalls, throttling, or a hard slowdown at a level-size threshold. The average throughput looks excellent while the ninety-ninth percentile jumps by an order of magnitude during a compaction burst. Tombstones make it worse — a delete is a marker that must survive until every older version is compacted away, so a workload of insert-then-delete can hold disk space far above the live data size.
The crossover also depends on concurrency. A B-tree with a good buffer pool and group commit can push far past the naive IOPS estimate because many updates hit pages already in memory; the random writes only happen on eviction. An LSM-tree with a large memory table and a fast device can hide compaction entirely if the write rate stays under the compaction budget. The formula tells you which failure mode to expect, not the exact number.
Part Four: The later cost
The operational bill differs in kind. A B-tree is easy to reason about: one copy, one place to look, backups and point-in-time recovery map directly onto pages and logs. Its failure mode is contention — hot pages, latch waits, deadlocks between concurrent updates — and the fix is usually schema or access-pattern work that the application team owns.
An LSM-tree moves the complexity into tuning. Level count, size ratio, compaction strategy, Bloom filter bits per key, block size and compression all interact, and a change that improves write throughput usually worsens read latency or space. The failure mode is a background process, which means the person debugging it is reading compaction stats at three in the morning rather than reading a query plan.
There is also a hardware asymmetry that has grown sharper. Consumer and datacentre SSDs have much higher sequential than random write throughput, which favours LSM. But drives with power-loss protection and large DRAM caches, plus persistent memory and CXL-attached memory, narrow the gap by making random writes cheaper. On a device where random 4-kilobyte writes cost almost the same as sequential ones, the LSM advantage largely evaporates and its read and space costs remain.
Part Five: Outside the team
For the person using the product, the difference is whether the dashboard loads in 40 milliseconds or 400, and whether last month's data is still queryable after a bulk import. Write amplification becomes an infrastructure bill: an LSM engine that rewrites every byte fifteen times wears out drives faster and burns more write bandwidth, which on a metered cloud volume shows up as a line item.
For the team, the choice decides who is on call. B-tree problems surface as slow queries and lock waits that a database administrator can see in a familiar plan. LSM problems surface as compaction debt and read stalls that require understanding an internal scheduler. Neither is better; they are different jobs attached to the same feature.
A fair comparison fixes the device, the key distribution, the read-write ratio and the latency target, then measures bytes written per user byte and ninety-ninth percentile read latency. Comparing average throughput across two different drives is the most common way this decision gets made badly.
Market sizing check
If managed database spend keeps growing at low double digits and storage engines remain the core of that spend, the segment tied to engine choice — managed Postgres, wide-column stores, time-series platforms and the observability backends built on them — is plausibly $30B to $60B a year by 2030. The swing factor is how much of the workload shifts to append-only telemetry, which favours LSM, versus transactional records, which favour B-trees.
These figures are a synthesis of published market ranges and a stated model, not a forecast of any specific vendor.
The comparison that matters
The comparison that matters is between random overwrite and sequential append, priced on the device you actually run. B-trees win when the working set fits in memory, reads dominate, and space predictability matters. LSM-trees win when writes arrive faster than a tree can absorb them, when the data is append-heavy, and when sequential bandwidth is the cheap resource.
The ranking assumes the read-write ratio holds. Shift toward read-heavy traffic and the B-tree's single-copy lookup becomes decisive; shift toward ingest and the LSM's sequential path does.
Pick the engine by which amplification your workload can afford — random write IOPS for B-trees, compaction bandwidth and read CPU for LSM-trees — then measure the ninety-ninth percentile, because averages hide both failure modes.
SOL: HCqPmPJM3NVtS6YrmVaTZYnuP8XZ2PFgQZKT94VK1LkS
If this saved you time, a tip is appreciated.
