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:

1. Write to base table

2. Write to index B-Tree

3. Possible page split

4. 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:

1. Read index page

2. 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