B-Tree (PostgreSQL, MySQL InnoDB):
- In-place updates
- Consistent read performance
- Write amplification from page splits
- Better for read-heavy workloads
LSM Tree (Cassandra, RocksDB):
- Append-only writes
- Sequential I/O (SSD-friendly)
- Read amplification (multiple files)
- Better for write-heavy workloads
Decision factors:
- Read/write ratio
- Latency requirements
- Storage efficiency needs
- Workload pattern (random vs sequential)
Modern databases often offer both: MySQL has MyRocks (LSM), Postgres has extensions.