HOT Updates, Fillfactor, Index Churn, and Update-Heavy Table Design

Learning outcomes

MVCC means an UPDATE creates a new heap tuple version. Without another optimization, that can also require new entries in every index, even when the indexed key did not change. PostgreSQL's Heap-Only Tuple (HOT) optimization can avoid ordinary index maintenance when the update is eligible and the successor tuple fits on the same heap page.

1. What HOT changes—and what it does not

A HOT update still creates a new tuple version; it is not an in-place overwrite of the row. The optimization is that ordinary indexes can continue to lead to the HOT chain's root and PostgreSQL follows the chain to an appropriate version. Intermediate dead versions can also be pruned during normal page access. In PostgreSQL 18, summary indexes such as BRIN are a special case and can still need maintenance.

2. Build an update-heavy ServiceHub table

DROP TABLE IF EXISTS app.ch08_hot_probe;CREATE TABLE app.ch08_hot_probe (    work_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,    external_ref text NOT NULL UNIQUE,    status text NOT NULL,    attempt_count integer NOT NULL DEFAULT 0,    note text NOT NULL) WITH (fillfactor = 70);INSERT INTO app.ch08_hot_probe(external_ref, status, note)SELECT 'EXT-' || lpad(g::text, 6, '0'),       'queued',       repeat('x', 120)FROM generate_series(1, 5000) AS g;ANALYZE app.ch08_hot_probe;

Because the primary key and external_ref are indexed, updates to status, attempt_count, or note are candidates for HOT; updates to work_id or external_ref are not. Candidate does not mean guaranteed.

3. Capture a baseline, perform non-indexed updates, then compare deltas

SELECT n_tup_upd, n_tup_hot_upd, n_live_tup, n_dead_tupFROM pg_stat_user_tablesWHERE relid = 'app.ch08_hot_probe'::regclass;SELECT stats_resetFROM pg_stat_databaseWHERE datname = current_database();

UPDATE app.ch08_hot_probeSET attempt_count = attempt_count + 1,    status = CASE WHEN work_id % 5 = 0 THEN 'retry' ELSE status ENDWHERE work_id BETWEEN 1 AND 3000;SELECT pg_stat_clear_snapshot();SELECT n_tup_upd, n_tup_hot_upd, n_dead_tupFROM pg_stat_user_tablesWHERE relid = 'app.ch08_hot_probe'::regclass;

Record the before and after values and calculate deltas. n_tup_upd includes HOT updates, while n_tup_hot_upd counts HOT updates. pg_stat_clear_snapshot() only discards this session's cached statistics snapshot; it does not force another backend to publish counters. Cumulative statistics can lag briefly, so if a just-finished workload has not appeared yet, end the workload transaction, let that backend become idle, and read the counters again rather than inventing an exact immediate value. Exact ratios are workload-dependent. A 70% fillfactor does not promise a 70% or 100% HOT rate.

4. Make a B-tree-indexed value change: HOT is no longer eligible for those rows

UPDATE app.ch08_hot_probeSET external_ref = external_ref || '-R'WHERE work_id BETWEEN 4001 AND 4100;SELECT pg_stat_clear_snapshot();SELECT n_tup_upd, n_tup_hot_upd, n_dead_tupFROM pg_stat_user_tablesWHERE relid = 'app.ch08_hot_probe'::regclass;

Those 100 updates change an indexed B-tree key. They require index maintenance and are not classic HOT updates. As above, clearing the local statistics snapshot is not a forced statistics flush; compare eventual deltas after the workload backend has had a chance to publish its counters. The cumulative totals also include earlier work, which is why production dashboards should compare rates over an interval and correlate them with application update patterns.

5. Fillfactor trades density for update headroom

Table fillfactor defaults to 100. A lower value tells inserts to pack pages only to the specified percentage, reserving room for later row versions. This increases the chance of same-page updates, but reduces initial heap density and can increase scan footprint. There is no universal best fillfactor.

SELECT c.relname, c.reloptions,       pg_size_pretty(pg_relation_size(c.oid)) AS heap_main,       pg_size_pretty(pg_indexes_size(c.oid)) AS indexesFROM pg_class AS cWHERE c.oid = 'app.ch08_hot_probe'::regclass;

Changing fillfactor on an already-packed table changes the policy for future packing; it does not instantly redistribute old tuples. A rewrite can repack existing data, but that has locking, I/O, WAL and disk-space implications and belongs in a planned maintenance decision.

6. HOT chains are page-local and pruning can shorten them

When repeated eligible updates occur on the same page, PostgreSQL can form a HOT chain. Ordinary indexes keep a reference that can lead into the chain, and heap access follows the chain to a version visible to the current snapshot. Once intermediate versions can no longer be seen by any relevant snapshot, page pruning can remove tuple bodies and convert line pointers into redirect/reusable states. This is one reason HOT reduces both index churn and later cleanup work.

Do not infer chain length from n_tup_hot_upd. The counter tells you how many updates were HOT, while pruning can shorten physical chains over time. Lesson 5 uses pageinspect to observe one deliberately small example.

7. “Indexed column” includes dependencies you might not see in a simple column list

HOT eligibility is affected by columns referenced by expression indexes and partial-index predicates, not only plain CREATE INDEX ... (column) keys. An application team can accidentally reduce HOT opportunity by adding a convenient expression index over a frequently changing JSON/text field.

-- Conceptual example; do not add it to the lab table unless you want to test the effect.CREATE INDEX ch08_hot_status_lower_idxON app.ch08_hot_probe ((lower(status)));-- This status change now touches an expression-index input.UPDATE app.ch08_hot_probeSET status = 'done'WHERE work_id = 42;DROP INDEX app.ch08_hot_status_lower_idx;

The optimizer/index definition and heap-update machinery care about the indexed expression's attribute dependencies. Review pg_indexes.indexdef and application write patterns together when diagnosing a low HOT rate.

8. Measure HOT as an interval rate, then correlate it with business operations

A cumulative 80% HOT ratio can hide a regression if the last deployment changed a key column on every retry. Capture counters at two times, subtract them, and annotate the interval with workload/deployment context. Also inspect the database statistics-reset timestamp so a restart/manual reset does not masquerade as a drop in activity.

-- Snapshot A (store these values in your monitoring system).SELECT clock_timestamp() AS captured_at, n_tup_upd, n_tup_hot_updFROM pg_stat_user_tablesWHERE relid = 'app.ch08_hot_probe'::regclass;SELECT stats_resetFROM pg_stat_databaseWHERE datname = current_database();-- Later: capture the same counters and compute deltas outside this query/lab.

For a real performance experiment, declare dataset size, update mix, indexes, fillfactor, cache state, concurrency, storage and PostgreSQL build. HOT is a mechanism measurement, not a benchmark score that transfers between systems.

9. Wrong approaches that misdiagnose HOT

• “The updated value did not change, so HOT must occur.” PostgreSQL's index dependency and executor/storage conditions matter; do not infer from business semantics alone.
• “No directly indexed column changed.” Expression-index inputs and partial-index predicates can make columns relevant to index maintenance. Inspect definitions, not just column lists.
• “Lower fillfactor guarantees HOT.” The successor must still fit on the old page at update time.
• “More HOT is always better.” Extreme free-space reservation can make read-heavy tables larger and less cache-efficient.

SELECT indexname, indexdefFROM pg_indexesWHERE schemaname = 'app'  AND tablename = 'ch08_hot_probe'ORDER BY indexname;

10. Reproducible verification and cleanup

SELECT n_tup_ins, n_tup_upd, n_tup_hot_upd, n_tup_del,       n_live_tup, n_dead_tup,       CASE WHEN n_tup_upd > 0            THEN round(100.0 * n_tup_hot_upd / n_tup_upd, 2)       END AS cumulative_hot_pctFROM pg_stat_user_tablesWHERE relid = 'app.ch08_hot_probe'::regclass;

The percentage is cumulative since the statistics reset, not a pure measurement of the immediately preceding statement. In production, capture interval deltas and reset context before drawing conclusions.

DROP TABLE IF EXISTS app.ch08_hot_probe;

11. Production judgment and bridge

Use HOT rate as one signal in an update-heavy design, not as a target divorced from workload. Fewer indexes, stable indexed keys, controlled tuple growth, and appropriate fillfactor can help, but read amplification and space cost matter too. Lesson 4 follows the versions that updates/deletes leave behind: how reusable dead space becomes operationally significant bloat and when a rewrite is justified.

Authoritative references

• PostgreSQL 18 — Heap-Only Tuples (HOT)
• PostgreSQL 18 — CREATE TABLE fillfactor
• PostgreSQL 18 — Cumulative Statistics / pg_stat_all_tables
• PostgreSQL 18 — Indexes on Expressions
• PostgreSQL 18 — Partial Indexes