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
| Normalize | Denormalize | |
|---|---|---|
| Optimises for | Write correctness — each fact stored once | Read speed — a value pre-computed or duplicated |
| Anomalies | Removes update/insert/delete anomalies | Reintroduces them; needs active maintenance |
| Reads | Joins to assemble data | Fewer joins, sometimes none |
| Cost | Join cost on hot read paths | A write path, a drift check, and a repair per copy |
| Rule | The default for the system of record | Add a copy for a specific slow read — never un-normalise the source |
| Choose this when | The source of truth for anything that gets updated. Start here. | A specific read far more frequent than its invalidating writes, once a well-indexed query is still too slow — and you can maintain the copy. |