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
| Optimistic locking | Pessimistic locking | |
|---|---|---|
| Mechanism | Version column; write with WHERE version = ?; retry on 0 rows | SELECT … FOR UPDATE; others wait |
| Waiting | None | Blocks conflicting transactions |
| Deadlocks | Impossible | Possible; needs consistent lock order |
| Cost under contention | Wasted work from retries | Throughput lost to waiting |
| Best when | Conflicts are rare | Conflicts are likely and retries are expensive |
| Choose this when | Editing a profile or a document — simultaneous writers are rare and you do not want to hold locks across think time. | Money movement, inventory decrements, seat booking — conflicts are likely and a lost race is costly. |