The Silent Cost Behind Fast Reads

Indexes don’t make systems faster —
they shift cost from reads to writes, memory, and latency tails.

🧠 The Illusion

CREATE INDEX idx_user_email ON users(email);

Looks harmless.
But this single line changes your entire system behavior.

🧩 Cost #1 — Write Amplification

❌ What Actually Happens on INSERT

INSERT INTO users (id, email, name) VALUES (...);

This causes:

Write to base table
Write to index B-Tree
Possible page split
WAL / redo log update

🧠 Key Point

1 logical write → multiple physical writes

🧩 Cost #2 — Slower Writes (Lock Contention)

Indexes increase lock scope.

UPDATE users SET email = 'x' WHERE id = 42;

Locks:

Row lock (table)
Index node lock
Possibly parent nodes

Effect

Higher contention
Worse under high concurrency

🧩 Cost #3 — Memory Pressure (Cache Pollution)

Indexes consume RAM aggressively.

What lives in memory

Hot index pages
Root & intermediate B-tree nodes

🧠 Result

Indexes can evict actual data, hurting cache hit rate.

🧩 Cost #4 — Read Amplification Still Exists

Indexes don’t always mean 1 read.

Example:

SELECT * FROM orders WHERE user_id = 42;

Execution:

Read index page
Read table page(s)

Index scan + table lookup = 2 I/O paths

🧩 Cost #5 — Index Maintenance (Deletes & Updates)

Deletes don’t free space immediately.

Problems:

Fragmentation
Page splits
Rebalancing
Vacuum / compaction overhead

Result

Background CPU
Unpredictable latency spikes

🧩 Cost #6 — Indexes Hurt Write-Heavy Systems

Example: Like Counter

UPDATE posts SET likes = likes + 1 WHERE id = 7;

With index on likes:

Every increment updates index order
Constant tree rebalancing ❌

Better

No index
Or async aggregation

🧠 When Indexes Make Things Worse

Scenario	Index Impact
Write-heavy	❌ Bad
High churn fields	❌ Bad
Low-selectivity columns	❌ Bad
Hot rows	❌ Very bad
Cold analytical reads	❌ Wasted

❌ Example of a Bad Index

CREATE INDEX idx_gender ON users(gender);

Why bad?

Low cardinality
- What is cardinality ? will be explained in upcoming blog.
Poor selectivity
Index scan almost as big as table scan

✅ When Indexes Are Worth It

Scenario	Index
High selectivity	✅
Read-heavy	✅
Stable data	✅
Covering index	⭐⭐⭐
OLAP workloads	✅

⭐ Covering Index (The Only “Free-ish” One)

CREATE INDEX idx_user_email_name
ON users(email, name);

Query:

SELECT name FROM users WHERE email = ?;

✔ No table lookup
✔ Reduced read amplification

🔥 How This Connects to Previous Blogs

Read Amplification → indexes don’t eliminate it
Write Amplification → indexes multiply it
Hot Keys → hot index pages become bottlenecks
P99 latency → index rebalancing spikes

📘 Why Indexes Are Not Free