Database Engines and Write Amplification
Database performance follows physical access paths and storage-engine behavior, not the database label.
Database Engines and Write Amplification
Database performance follows physical access paths and storage-engine behavior, not the database label. A B-tree maintains ordered pages for equality and range access. An LSM-style engine buffers writes in memory and flushes sorted runs, then compacts them. WAL supports recovery before data pages are flushed. MVCC keeps row versions so readers can observe a consistent snapshot.
Mechanism and worked example
For a tenant inbox ordered newest first, an index on (tenant_id, created_at DESC, id DESC) supports tenant equality and cursor range. A created_at-only index may scan other tenants. A write-heavy event store may favor LSM throughput, while read-heavy range queries may favor B-tree locality.
| Mechanism | Strength | Cost |
|---|---|---|
| B-tree | Ordered lookup and range scans | Page splits and index maintenance |
| LSM tree | Buffered writes and sequential flush | Compaction and read amplification |
| WAL | Crash recovery ordering | Log bandwidth and retention |
| MVCC | Consistent snapshots | Version cleanup and bloat |
Failure modes and operations
Page splits, dead tuples, vacuum lag, compaction debt, tombstones, WAL growth, replica lag, lock contention and connection exhaustion can dominate. An index can be unused when predicates do not match leading columns. A sequential scan may be optimal for a query returning much of the table.
Trade-offs and design boundary
B-trees offer ordered range access with page maintenance. LSM designs trade write amplification and read amplification differently and need compaction capacity. Covering indexes reduce some reads but increase storage and write cost.
Interview transfer exercise
Choose an engine for a high-write time-series workload. Explain retention, late writes, query windows, compaction, partitioning, recovery and the expected hot-key distribution.
Read the diagram
Commit is recorded in WAL → B-tree updates ordered pages → LSM flushes memtable into sorted runs → Compaction merges runs and obsolete versions