Indexes: What Happens on Write

Indexes are discussed almost entirely in terms of reads. The write cost is real, it compounds with every index added, and it is invisible on a small table — which is why it arrives as a surprise later.

For a separate people-operations perspective, the complete overview covers relating labour cost to revenue.

What a write actually does

An INSERT into a table with five indexes performs six write operations, not one.

For each index: locate the correct leaf page, insert the entry in sorted position, split the page if it is full, propagate the split upward if the parent is also full, and write all of it to the write-ahead log so it survives a crash.

An UPDATE is worse than it looks. Updating a column that is not indexed still touches every index if the row moves — and in some engines a row that no longer fits in its page moves. Updating an indexed column requires deleting the old index entry and inserting a new one, in every index containing that column.

A DELETE marks entries as dead in every index, and the space is reclaimed later by a background process rather than immediately.

The write-ahead log is the multiplier. Every one of these changes is logged before it is applied, so index maintenance increases log volume, which increases disk throughput required, which increases replication traffic to every replica.

Where the cost shows up

Not usually as slow inserts. It shows up sideways.

Replication lag. More log to ship and replay. A replica that keeps up at five indexes falls behind at nine, and stale reads start appearing in the application.

Write amplification on storage. One logical row change becomes many physical page writes, and on cloud storage billed per operation this is a line item.

Vacuum or compaction pressure. Dead index entries accumulate and the cleanup process has more to do. In PostgreSQL, bloated indexes are a common cause of gradual degradation that nobody attributes to the index count.

Lock contention on hot pages. Sequentially increasing keys — timestamps, auto-increment identifiers — mean every insert targets the same rightmost leaf page. That page becomes a contention point, and adding indexes on such columns multiplies it.

Buffer cache pressure. Index pages compete with table pages for memory. Enough indexes and the working set no longer fits, and read performance drops for reasons that look unrelated to the indexes you added to improve it.

B-trees and LSM trees behave differently

Worth knowing, because the write characteristics diverge sharply.

B-tree indexes — PostgreSQL, MySQL InnoDB, SQL Server — update in place. Write cost is fairly predictable, random I/O is higher, and page splits cause occasional latency spikes.

LSM trees — RocksDB, Cassandra, and storage engines built on them — buffer writes in memory and flush sorted files, merging them in the background. Writes are fast and sequential; the cost is deferred to compaction, which consumes I/O later and can cause latency spikes at unpredictable times.

The practical difference: with a B-tree the write cost is paid now and is visible. With an LSM tree it is paid later, in a background process, and a system that looks fine under write load can degrade when compaction cannot keep up. Monitoring compaction backlog is the equivalent of watching for bloat.

Finding indexes you do not need

Most databases record index usage. This is the highest-return maintenance task in this area and almost nobody runs it.

PostgreSQL:

SELECT schemaname, relname, indexrelname, idx_scan,
       pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

Zero scans since statistics were last reset means the index has cost you on every write and returned nothing.

Two cautions before dropping:

Check the counter has been running long enough. A statistics reset last week does not tell you about a quarterly report.

Unique indexes enforce a constraint even at zero scans. Dropping one removes the guarantee, not just the lookup.

Also look for redundant indexes. An index on (a) is redundant if an index on (a, b) exists — the composite serves both, because a B-tree can be used for any prefix of its key. This is an extremely common finding and it is pure waste.

Designing indexes for both directions

Column order in a composite index matters, and the rule is specific: equality conditions first, then the range condition, then columns needed only for output.

An index on (status, created_at) serves WHERE status = 'open' AND created_at > $x well. The reverse order does not, because after the range condition the index is no longer sorted usefully for the equality.

Covering indexes remove the table lookup entirely. If every column referenced by the query is in the index, the engine never touches the table. Large read win, larger index, higher write cost. Worth it for hot queries and not as a default.

Partial indexes cover only rows matching a condition. An index on unprocessed jobs — WHERE processed = false — is tiny when most rows are processed, and it is only maintained for the rows that qualify. Both the read and the write cost drop. These are under-used and they are frequently the right answer for status columns with skewed distributions.

Avoid indexing low-cardinality columns alone. A boolean index over a million rows rarely helps, because the planner will choose a sequential scan over half the table anyway. As part of a composite, or as a partial index, it can be valuable.

Sequential keys and contention

Auto-increment identifiers and timestamp columns concentrate every insert on the same index page.

Symptoms: contention that rises non-linearly with write concurrency, and hot pages visible in wait statistics.

Options: UUIDs distribute inserts across the index and cost you locality on reads and a larger key. Time-ordered UUIDs are a compromise — mostly sequential, so locality survives, with enough entropy to spread the immediate contention. Some engines offer explicit mitigations for this exact pattern.

Do not switch to random UUIDs reflexively. The write contention improves and the read locality and index size get worse. Measure which one is actually your constraint.

An audit worth running quarterly

• [ ] Unused indexes, with the statistics window long enough to trust
• [ ] Redundant indexes where a prefix duplicates a composite
• [ ] Index size as a proportion of table size — several times the table is a signal
• [ ] Bloat, and whether a rebuild is due
• [ ] Write latency and replication lag against index count over time
• [ ] Whether any index exists for a query that no longer runs

And before adding one: what query needs it, how often does that query run, and what does it cost on every write to the table. An index added for a report that runs monthly is paid for on every insert for the rest of the table's life.

The summary

Every index is a tax on every write, and the tax is collected in replication lag, storage throughput and cleanup pressure rather than in obviously slow inserts.

Find and drop unused and redundant indexes. The query is three lines and the result is usually surprising.

Partial indexes are under-used and they reduce both sides of the trade.

Column order follows the query shape — equality, then range, then output.

Adding an index is a decision with an ongoing cost, not a free improvement, and it deserves the same scrutiny as any other recurring expense.