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

	Index Scan	Sequential Scan
How it works	Descend a B-tree, fetch matching rows by pointer	Read every page of the table in order
Cost	log(n) descent + one random read per match	Linear in table size, but sequential IO
Best when	Selective predicate (matches a few percent)	Query needs most of the table, or no usable index
Worst when	Predicate matches a large fraction — many random reads	Selective predicate on a big table — reads everything to keep little
The tell in EXPLAIN	Index Scan / Index Only Scan	Seq Scan with high Rows Removed by Filter
Choose this when	A highly selective lookup on a large table — a user by id, a row by email.	A full aggregate, or a predicate that keeps more than ~20% of rows; the planner picks it and is right.