When people talk about the cost of an index, they usually mean disk space and slower inserts. In PostgreSQL there is a third cost that is much easier to miss: an index can change how every single update on the table is executed.
This post explains why, and how to see it happening.
Context
PostgreSQL uses MVCC. An UPDATE does not modify a row in place. It writes a new version of the row, and the old version stays around until vacuum cleans it up.
That new version has a new physical location. And every index on the table stores physical locations. So, in the general case, one update means one new entry in every index, even for indexes whose columns did not change.
A table with six indexes turns each update into seven writes, plus the WAL for all of them.
Heap-only tuples
PostgreSQL has an optimization for this, called HOT, for heap-only tuple. When it applies, the new row version is stored without touching any index. The old version points to the new one, inside the same page, and index lookups follow that chain.
A HOT update needs two conditions:
- No indexed column changed. That includes columns used in expression indexes and in partial index predicates.
- There is room on the same page for the new version.
If either condition fails, the update falls back to the expensive path.
Seeing it
The statistics view already tells you how many updates were HOT:
sql
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
LIMIT 20;
A hot table with a hot_pct close to zero deserves attention.
How one index breaks it
The classic case is a column like last_seen_at or updated_at that is written constantly. The moment somebody adds an index on it, no update on that table can be HOT anymore, because every update changes an indexed column.
The index looked harmless in the query plan that motivated it. Its real price is paid by every write to unrelated columns of the same table.
There are three common ways out:
- Do not index the volatile column, if the query that needed it can live without.
- Move the volatile column to a small side table with a one-to-one relationship, so the wide table stays HOT-friendly.
- Accept the cost deliberately, knowing what it is.
Since PostgreSQL 16, one case got better: updates that change only columns covered by summarizing indexes, such as BRIN, can still be HOT.
Leaving room: fillfactor
The second condition, space on the page, is controlled by fillfactor. For tables it defaults to 100, which means pages are packed completely full on insert. The first update on a full page has nowhere to put the new version, so it cannot be HOT.
For update-heavy tables, leaving some space makes a large difference:
sql
ALTER TABLE sessions SET (fillfactor = 85);
Be aware of one detail: this setting only affects pages written after the change. Existing pages stay full until the table is rewritten, for example with VACUUM FULL or pg_repack.
The trade-off is that the table is physically larger, since each page holds fewer rows. For tables that are mostly read and rarely updated, a lower fillfactor is simply wasted space.
Conclusion
An index is not only a read optimization with a write tax. It is a constraint on how updates can be performed.
Before adding one to a frequently updated table, ask which columns are written most often, and whether the new index covers any of them. Every extra index entry is also WAL, shipped to replicas and replayed there, so the answer decides whether a table updates in place or rewrites seven structures for every change.
References