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 Scan Normalize vs Denormalize Optimistic locking vs Pessimistic locking Read Committed vs Serializable Partitioning vs Sharding Read replica vs Shard Relational vs Document Postgres + pgvector vs Dedicated vector DB Cache-aside vs Write-through B+ tree storage engine vs LSM tree storage engine Heap table + secondary indexes (PostgreSQL-style) vs Clustered primary index (InnoDB-style)LRU vs Clock (second chance)Synchronous replication vs Asynchronous replication

	Heap table + secondary indexes (PostgreSQL-style)	Clustered primary index (InnoDB-style)
Where rows live	unordered heap pages; every index points at a TID (page, slot)	in the leaves of the primary-key B+ tree, sorted by PK
Secondary lookup	index → TID → heap page (2 structures, 1 hop)	index → PK → clustered tree (2 descents)
PK range scan	index leaves, then random heap reads unless clustered	physically sequential in the leaf chain
UPDATE of non-key column	new tuple version; every index needs a new entry unless HOT	in place (with undo); secondary indexes untouched
PK choice	matters little for layout	random UUIDs cause splits everywhere; PK size is copied into every secondary index
Bulk load	append to heap, build indexes after	sorted by PK is fast; random PK is slow
Choose this when	Wide tables with many secondary indexes, heavy updates on non-key columns, bulk loads — and PostgreSQL.	Primary-key range access, small monotonically increasing keys, workloads where PK locality dominates — and MySQL/InnoDB.