Compare
Side-by-side on the decisions that recur: index vs scan, normalize vs denormalize, optimistic vs pessimistic, partition vs shard, and more — with when to choose each.
Index Scan vs Sequential ScanNormalize vs DenormalizeOptimistic locking vs Pessimistic lockingRead Committed vs SerializablePartitioning vs ShardingRead replica vs ShardRelational vs DocumentPostgres + pgvector vs Dedicated vector DBCache-aside vs Write-throughB+ tree storage engine vs LSM tree storage engineHeap table + secondary indexes (PostgreSQL-style) vs Clustered primary index (InnoDB-style)LRU vs Clock (second chance)Synchronous replication vs Asynchronous replication
| B+ tree storage engine | LSM tree storage engine | |
|---|---|---|
| Point read | height + 1 page reads; one structure | memtable, then each level; bloom filters skip most files |
| Range scan | leaf chain, sequential | k-way merge across memtable and SSTables |
| Write path | find leaf, modify in place, maybe split; random page writes | append to WAL + memtable; sequential flushes |
| Write amplification | page-granular: a 100 B update rewrites 8 KB (+WAL) | compaction rewrites data several times (leveled ~10× per level) |
| Read amplification | low and predictable | grows with number of levels / files; needs bloom filters |
| Space | pages partially full after splits/deletes | stale versions until compaction; tombstones |
| Background work | vacuum / merge, checkpoint flushing | compaction — competes for I/O, can stall writes |
| Caching | buffer pool of pages; upper levels stay hot | block cache + OS cache; immutable files are easy to cache |
| Examples | PostgreSQL, InnoDB, SQLite, WiredTiger | RocksDB, LevelDB, Cassandra, HBase, ScyllaDB |
| Choose this when | Read-heavy or mixed OLTP, point lookups and range scans with predictable latency; the dataset and indexes are updated in place. | Write-heavy ingest (events, time series, logs), sequential-friendly storage, tolerance for compaction and slightly higher read cost. |