Myth vs Fact: Why Indexing Every Foreign Key Won’t Save Your Database Performance Database optimization isn't about slapping indexes on every relational column—it’s about understanding execution plans, cardinality, and write amplification. Myth: "Always create an index on every Foreign Key (FK) column to make your relational queries and joins faster." Fact: An unconsidered index on a low-cardinality or high-write column provides negligible join speedup while degrading insert/update throughput, inflating write-ahead logs (WAL), and bloating memory buffers. [ Table Write / INSERT / UPDATE ] │ ├──▶ Writes base tuple to Heap Page ├──▶ Updates Index 1 (Primary Key) ├──▶ Updates Index 2 (FK Index A) ──▶ Write Amplification & Buffer Bloat └──▶ Updates Index 3 (FK Index B) Why This Matters in Modern Backend Engineering: The Low-Cardinality Trap: If a foreign key points to an enum-like status table or a boolean flag (e.g., user_type_id with 3 values across 5M rows), the query planner will frequently ignore the index and perform a Sequential Scan anyway. Write Amplification: Every row mutation (INSERT, UPDATE, DELETE) forces the database engine to synchronously update every associated B-tree index, splitting index pages and trashing shared memory buffers. HOT (Heap-Only Tuple) Invalidation: In engines like PostgreSQL, updating a row that contains an indexed column prevents HOT optimization, generating excessive table bloat and vacuum pressure. What You Should Do Instead: Index for Join Patterns, Not Existence: Index a foreign key only if you regularly query parent-child relationships from the child side (e.g., WHERE child.parent_id = ?) or if parent rows undergo frequent cascades (ON DELETE CASCADE). Leverage Partial & Composite Indexes: If you only query active records, don't index the whole table: SQL -- Bad: Indexes all 10M rows unconditionally CREATE INDEX idx_orders_customer_id ON orders(customer_id); -- Better: Partial index targeting active query workloads CREATE INDEX idx_active_orders_customer ON orders(customer_id) WHERE status = 'pending'; Inspect the Planner: Always run EXPLAIN (ANALYZE, BUFFERS) before committing an index to production to confirm index scans are actually chosen over sequential scans. Discussion Question What is the worst index-related production bottleneck or write-amplification issue your team has ever had to debug? CTA (Join Developers & Coding) 💻 Join the Techawks Developers & Coding Community: Connect with software engineers, backend architects, and system developers worldwide to level up your SQL performance, system design patterns, and clean code practices. 👉 [Link to Techawks Developers & Coding Community]