The Hidden Write Cost of 'Just Add an Index'

The Hidden Write Cost of 'Just Add an Index'

When a query is slow, the reflexive fix is to add an index. In PostgreSQL, this often works brilliantly for reads — a well-targeted composite index can turn a sequential scan into a fast index scan, cutting query time from seconds to milliseconds. But every index is also a liability on the write path. Each `INSERT`, `UPDATE`, or `DELETE` that touches an indexed column must also update every index that references it. Add enough indexes, and what was a lightweight write becomes a cascade of B-tree maintenance operations.

This tradeoff is rarely visualized before the `CREATE INDEX` statement runs. Teams measure the read-side win — the query planner's new, cheaper execution plan — but almost never quantify the corresponding write-side cost until throughput degrades in production.

Why composite indexes are especially expensive

A single-column index adds one write operation per row change. A composite index spanning three or four columns doesn't just multiply that cost linearly — it also tends to produce wider index entries, larger index sizes, and more frequent page splits as data is inserted out of key order. Wide composite indexes are particularly punishing on tables with high insert velocity, like event logs, audit trails, or order tables, where every new row triggers B-tree rebalancing across multiple indexes simultaneously.

There's also a subtler cost: index bloat. Updates in PostgreSQL (due to MVCC) create new row versions, and every index pointing to that row needs a new entry too. Tables with frequent updates and many indexes accumulate dead tuples and bloated index pages faster, which in turn increases the work `VACUUM` must do to reclaim space. A query optimization made in isolation can quietly degrade the performance of routine maintenance jobs.

Quantifying the tradeoff before you commit

Before adding a composite index, it's worth asking three concrete questions:

- How many indexes already exist on this table, and what is the write-to-read ratio for the workload hitting it?

- What is the expected row width added to the index relative to existing table size — will this materially increase I/O for vacuum and checkpoint writes?

- Can `EXPLAIN (ANALYZE, BUFFERS)` on representative write statements show a meaningful change in buffer touches once the index exists?

Testing this on a staging environment with representative data volume — not a toy table — is essential. Index behavior on a 10,000-row table rarely predicts behavior on a 50-million-row table, where page splits and random I/O patterns dominate. Reviewing query plan behavior under realistic load, alongside write-path benchmarks, gives a far more honest picture than read-only `EXPLAIN` output.

Making the read/write tradeoff visible

The core problem is that read and write costs live in different parts of the system and are measured by different tools. Query performance monitoring tracks slow `SELECT`s; write amplification shows up later, in disk I/O graphs or autovacuum logs, disconnected from the index decision that caused it. Platforms like Querk that correlate slow query patterns with underlying index and table statistics help close that gap, surfacing the full cost of an index rather than just its read-side benefit.

Adding an index is never free. Treating it as a deliberate tradeoff — quantified, tested, and monitored — rather than a reflexive fix keeps write-heavy tables healthy as they scale.