The Hidden Write Cost of 'Just Add an Index'

The Hidden Write Cost of "Just Add an Index"

Every PostgreSQL developer eventually hits the same moment: a slow query shows up in a monitoring dashboard, `EXPLAIN ANALYZE` points to a sequential scan, and the fix seems obvious — add an index. It's often correct advice. But indexes are not free. Every `CREATE INDEX` statement adds a new structure that must be maintained on every `INSERT`, `UPDATE`, and `DELETE` touching the indexed columns. Composite indexes, which span multiple columns to serve specific query patterns, carry this cost in a more concentrated way than single-column indexes because they're typically wider and more expensive to update.

The read-side benefit of a well-chosen composite index is easy to demonstrate: run the query before and after, compare planner cost and execution time. The write-side cost is harder to see because it's distributed across every write transaction, not concentrated in one query someone is actively watching. This asymmetry is why teams accumulate indexes over time without ever removing the ones that stopped paying for themselves.

What Actually Happens on Write

When a row is inserted or updated, PostgreSQL doesn't just write to the heap. It must also update every index that includes the changed columns, or in the case of `UPDATE`, potentially every index at all if the row moves (a "cold" update outside HOT — Heap-Only Tuple — optimization). Composite indexes make this worse in two ways: they contain more data per entry, so the B-tree pages being modified are larger and split more often, and any update to any column in the composite key triggers index maintenance, not just updates to the leading column.

This also means more WAL (write-ahead log) volume, more buffer cache churn as index pages get pulled in and dirtied, and more vacuum work later to clean up dead index entries. On high-throughput tables, this can quietly shift a workload from CPU-bound to I/O-bound.

Quantifying the Tradeoff Before You Commit

Rather than guessing, it's possible to estimate the cost with a few concrete steps. First, check `pg_stat_user_tables` for the table's current insert/update/delete rate to understand write volume. Second, look at existing index sizes via `pg_relation_size` to estimate how large the new composite index is likely to be, based on row count and column width. Third, run the candidate index creation in a staging environment under a representative write load and compare transaction latency and WAL generation before and after. Tools that support query plan monitoring can help capture this delta over a realistic time window rather than a single synthetic benchmark, and platforms like Querk are built specifically to surface that kind of before/after comparison without manual log-diffing.

Deciding Whether the Index Earns Its Keep

A useful heuristic: if the table is read-heavy and the query being optimized runs far more often than writes occur, the tradeoff usually favors indexing. If writes dominate, or the query is infrequent, a partial index or a narrower composite key may deliver most of the benefit at a fraction of the write cost. Reviewing index usage statistics periodically also catches indexes that were justified once but no longer are, since query patterns shift as applications evolve.

Every index is a standing commitment paid on every write, not just a one-time convenience for a slow query.