FM5 — Latency Amplification
An index lookup is faster than a full table scan only when the number of heap fetches is small relative to the table size. For a query that returns 30% of a table, the index requires 30% of N heap page fetches, each a random I/O. A sequential table scan reads pages sequentially. Sequential I/O is 10–100× faster than random I/O on spinning disks; the sequential scan wins. PostgreSQL’s query planner estimates this and chooses accordingly. Forcing an index scan on a poorly selective query amplifies latency.
FM8 — Schema/Contract Violation
B-tree indexes impose an ordering on keys. Changing a column’s data type (e.g., from integer to string) requires rebuilding the index, because the ordering changes. This is a schema migration that takes time proportional to table size. In large tables, this migration can take hours and locks the table. The B-tree’s dependence on ordering makes schema changes expensive — a contract between the column type and the index structure.