Cost of Indexes (writes, storage)

Cost of Indexes

On the last page, we added an index on user_id and our "My Orders" query went from ~800 ms to ~0.1 ms. That looks free, but it isn't. This page covers what every index costs our food delivery app.

The Problem: Fast Reads, Slower Writes

Say our orders table has 4 indexes: user_id, restaurant_id, status, and created_at.

When a customer places an order, the database must write the row and add an entry to every index, so each sorted list stays correct.

1New order placed
2  → write row to orders table        (1 write)
3  → update idx_user_id               (+1)
4  → update idx_restaurant_id         (+1)
5  → update idx_status                (+1)
6  → update idx_created_at            (+1)
7= 1 order becomes 5 writes

At dinner rush, the cost multiplies:

12,000 orders per second × 5 writes each = 10,000 writes per second
2Without the 4 indexes:                      2,000 writes per second

This is called write amplification: one logical write turns into many physical writes. UPDATE and DELETE pay the same price.

Writes are one cost. The other is space.

Storage and Memory Cost

An index is a second, sorted copy of a column plus a pointer to each row. So it takes disk space.

110,000,000 orders × ~20–30 bytes per index entry
2≈ 200–300 MB per index
3× 4 indexes ≈ ~1 GB extra

It also needs RAM (memory). An index is fast mainly when it fits in memory. If indexes outgrow RAM, the database reads them from disk, and lookups slow down again.

So every index has a price. The goal is to pay only for indexes you actually use.

Finding Indexes You Don't Need

An index that no query uses still slows every write. Check how often each index is used:

-- idx_scan = 0 means the index has never been used
SELECT indexrelname, idx_scan
FROM pg_stat_user_indexes
WHERE relname = 'orders';

	Few indexes	Many indexes
Reads	Slower for unindexed queries	Fast for many query types
Writes	Fast	Slower
Storage and RAM	Small	Large
Best for	Write-heavy data (logs, events, tracking pings)	Read-heavy data (user-facing pages)

Rule of thumb: index the columns your important queries filter or sort on. Drop indexes nobody uses.

Interview Angle

How to Explain it in an Interview

The 30-Second Spoken Pitch

"Indexes speed up reads, but every index costs something. Each insert, update, and delete must also update every index, so with four indexes one write becomes five. Indexes also take disk space and need to fit in RAM to stay fast. So I only index the columns my main queries filter or sort on, and I check usage stats to drop indexes that are never used. For write-heavy tables like event logs, I keep indexes to a minimum."

Common mistake: adding an index for every column "just in case". It slows every write and wastes memory.

Summary

1Each index    → one more write on every INSERT / UPDATE / DELETE
24 indexes     → 1 write becomes 5 (write amplification)
3Storage       → ~200–300 MB per index on 10M rows
4RAM           → indexes are fast only if they fit in memory
5Fix           → index only what queries use, drop unused ones

Since every index costs us, we want fewer indexes that each do more work. Next, we'll see how one composite index can cover several columns at once.

Next TopicComposite Indexes