Every PostgreSQL Index Spends a Write-Amplification Budget

An index is usually proposed as a read optimization. In PostgreSQL it is also a recurring charge on writes, WAL, cache, vacuum, replication, backup, and deployment.

The design question is not “would this query become faster?” Almost every selective query can become faster with the right structure. The question is:

Is the saved read work worth the write amplification and operational surface for this workload?

One update can touch many structures #

PostgreSQL uses MVCC: an update creates a new row version. When indexed columns change, new index entries may also be required. More indexes mean more structures to maintain and more bytes competing for memory and storage.

Heap-only tuple (HOT) updates avoid creating new index entries when two conditions hold: the update does not modify an index-referenced column (excluding summarizing indexes such as BRIN), and the same heap page has room for the new tuple version.

This creates a non-obvious consequence: indexing a frequently changing status or timestamp can make every update more expensive even when that index serves little traffic.

Inspect the evidence:

select relname,
       n_tup_upd,
       n_tup_hot_upd,
       round(100.0 * n_tup_hot_upd / nullif(n_tup_upd, 0), 1) as hot_pct
from pg_stat_user_tables
order by n_tup_upd desc;

The ratio is diagnostic, not a target. A low value may be inherent to the update pattern; it may also reveal unnecessary indexes or insufficient page space.

Covering indexes are not free #

INCLUDE columns can enable index-only scans, but included values are stored in index tuples. Wide payloads enlarge the index, reduce fanout, increase cache pressure, and can prevent HOT updates when those columns change. “Cover the query” should not mean copying the row into every access path.

Partial indexes are often a better expression of product semantics:

create index concurrently orders_unsettled_idx
on orders (merchant_id, created_at)
where settled_at is null;

If unsettled orders are the operational working set, this index can remain far smaller than indexing the full history. The predicate must match actual query conditions, and deployment still needs monitoring.

Page splits and fillfactor #

B-tree pages eventually split as they fill. PostgreSQL’s index fillfactor leaves space during builds; a lower value can smooth page splits for update-heavy indexes, at the cost of a larger structure and lower initial density. Table fillfactor can leave heap-page room and improve HOT opportunity.

Do not tune either globally by folklore. A mostly static lookup table and a hot mutable ledger have different economics.

Index admission policy #

Require each proposed index to have:

1. a named query or invariant it serves;
2. production frequency and latency evidence;
3. estimated size and write rate;
4. a deployment and rollback plan;
5. an owner and removal condition.

Review unused-index statistics carefully: counters reset, replicas may serve reads, and rare operational queries can be critical. Removal should be evidence-driven and reversible.

For large tables, CREATE INDEX CONCURRENTLY avoids blocking ordinary writes, but it performs more work, takes longer, and can leave an invalid index after failure. Watch progress, replication lag, WAL, disk headroom, and transaction age.

Review the portfolio, not one query #

Two individually reasonable indexes can be redundant together. Compare leading columns, predicates, sort requirements, and operator classes before admitting another structure. Use EXPLAIN (ANALYZE, BUFFERS) on representative data, then evaluate the write path under realistic concurrency. Planner estimates from a small development dataset are not production evidence.

Schedule an index review after major product changes. A workflow that disappeared can leave gigabytes of write amplification behind. Removal should use a measured observation window, account for read replicas and rare administrative queries, and retain a rehearsed recreation statement.

Conclusion #

Indexes move cost; they do not erase it. They exchange repeated scanning for persistent write and storage work.

A mature index strategy treats access paths as a portfolio. Keep the structures that protect real latency or correctness, measure their write cost, and retire those whose original product assumption disappeared. The fastest query in isolation is not necessarily the healthiest database.

Further reading: PostgreSQL indexes, HOT updates, CREATE INDEX, vacuum as concurrency control, and zero-downtime schema migrations.