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
| Synchronous replication | Asynchronous replication | |
|---|---|---|
| COMMIT returns when | the replica has durably received (or applied) the WAL | the primary has fsynced its own WAL |
| Commit latency | + one network round trip (and replica fsync) | local only |
| Data loss on primary failure | none (for acknowledged commits) | the lag window: everything past the replica's LSN |
| Stale reads on replica | still possible unless remote_apply | yes, by the lag |
| Replica down | writes block (or fall back, if configured) | primary unaffected; WAL accumulates |
| Choose this when | Money, inventory, anything where an acknowledged write must survive the primary dying. | Read scaling and geographic distribution where seconds of possible loss are acceptable and latency is not. |