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
| Partitioning | Sharding | |
|---|---|---|
| Splits across | Pieces of one database (one machine) | Separate database nodes (many machines) |
| Adds capacity | No — operability only | Yes — the only way past one machine’s write ceiling |
| Joins & transactions | Intact | Cross-shard ones move to the application or vanish |
| Retention | DROP a partition — instant | Per-shard, coordinated |
| Effort | A schema change | A re-architecture |
| Choose this when | A single huge table (events, logs) that is hard to index, vacuum and prune — partition by time. | A measured single-machine write ceiling, after vertical scaling, replicas, caching and partitioning are exhausted. |