Every PostgreSQL UPDATE creates a new row version. The expensive version of that operation also creates entries in every affected index and leaves more work for vacuum. Heap-only tuple updates—HOT updates—can avoid those new index entries, but only when the table’s physical design preserves two conditions.
The update must not change an indexed column, excluding summarizing indexes such as BRIN, and the heap page containing the old tuple must have enough room for the new version.
HOT is therefore not a switch. It is an agreement between schema design, page space, and workload shape.
Why the page boundary matters #
PostgreSQL indexes normally point to a tuple identifier containing a heap block and item offset. During a HOT update, the new version stays on the same heap page. The existing index entry can still lead PostgreSQL to a HOT chain containing the version visible to the current snapshot.
index entry -> heap item A -> tuple v1
-> tuple v2
-> tuple v3If the new tuple cannot fit on that page, PostgreSQL places it elsewhere and must add new index entries. One extra index on a frequently updated attribute can similarly disqualify the update.
This is why an apparently harmless index on updated_at, last_seen_at, or a mutable status column can create workload-wide write amplification.
Measure before changing fillfactor #
Start with table statistics:
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,
n_dead_tup
FROM pg_stat_user_tables
WHERE n_tup_upd > 0
ORDER BY n_tup_upd DESC;The ratio is cumulative since the statistics reset. Compare consistent windows and correlate them with WAL volume, index growth, vacuum duration, buffer writes, and transaction latency. A low HOT percentage is not automatically a problem on rarely updated tables.
For a genuinely update-heavy table, lowering fillfactor reserves page space during inserts:
ALTER TABLE account_state SET (fillfactor = 80);The setting affects future page packing. Existing pages do not reorganize themselves, so a controlled rewrite may be required to realize the full effect. Rewriting a large table has locking, WAL, replication, and free-space consequences; treat it as a migration.
Design indexes around query value #
Review indexes containing frequently mutated columns. Do not drop them merely to improve HOT rate. Ask whether each index removes enough query cost to justify its write cost.
| Pattern | Likely consequence |
|---|---|
| index immutable identity columns | HOT remains possible for other updates |
index updated_at on a hot table | most updates require index maintenance |
| lower fillfactor | more HOT opportunity, larger base table |
| wider new row version | less chance of fitting on the original page |
| frequent updates to many rows | stronger benefit, more vacuum pressure if missed |
Partial indexes can sometimes narrow the maintained population. Separating volatile counters or presence data into another table can isolate the write pattern, but adds joins and transactional coordination. Schema decomposition should follow a measured bottleneck, not a desire to maximize one ratio.
Failure and rollout policy #
Test with representative row widths and update distributions. Watch replica lag and disk headroom during any rewrite. Preserve enough free space for both the old and rewritten relation. Roll out fillfactor changes to one high-value table first, then compare equivalent traffic windows.
Do not use HOT percentage as an SLO. The product SLO is latency, throughput, recovery time, or cost. HOT is one mechanism influencing those outcomes.
Common mistakes #
- Assuming every non-indexed update is HOT without checking page space.
- Adding indexes through an ORM and ignoring update amplification.
- Changing fillfactor without planning how existing pages change.
- Comparing cumulative statistics across unequal reset windows.
- Optimizing HOT while a different constraint—locks, connections, or checkpoints—dominates latency.
Trade-offs #
Lower fillfactor spends storage and cache density to reserve future update capacity. Fewer indexes reduce write work but may make important reads slower. A table rewrite realizes the new layout sooner while creating operational risk.
The durable principle is simple: logical updates execute through a physical layout. Design both together.