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
| Read Committed | Serializable | |
|---|---|---|
| Snapshot | Fresh per statement | Per transaction + dependency tracking |
| Anomalies allowed | Non-repeatable, phantom, lost update, write skew | None |
| Throughput | Highest | Lower; aborts (40001) under contention |
| Retries | Not needed | Required — 40001 is normal operation |
| When | Ordinary requests with self-contained statements | Correctness depends on a condition you read but did not write |
| Choose this when | The default. Most application code, with atomic statements (SET x = x + 1, INSERT … ON CONFLICT, FOR UPDATE). | Invariants no single constraint can express — and only with a retry loop; serialise hot rows with a lock instead. |