0Pricing
PostgreSQL Performance & Query Optimization · Lesson

HOT Updates and Heap-Only Tuple Chains

Engineer schemas and indexes so updates stay heap-only and avoid index write amplification.

HOT Updates and Heap-Only Tuple Chains is a free PostgreSQL Performance & Query Optimization lesson on CoddyKit — lesson 2 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the PostgreSQL Performance & Query Optimization learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

The Cost of an Update in PostgreSQL

Because PostgreSQL uses MVCC, an UPDATE does not overwrite a row in place. Instead it writes a brand-new tuple (the new row version) and marks the old one dead. The old version stays until VACUUM reclaims it.

Naively, every new tuple needs a new pointer in every index on the table, even indexes whose columns did not change. With 8 indexes, one logical update means 8 index inserts plus index bloat. This is index write amplification.

  • More WAL written (each index change is logged)
  • More index bloat (dead pointers accumulate)
  • More CPU and I/O per update

This lesson is about a mechanism that lets PostgreSQL skip that index work: HOT updates.

What is a HOT Update?

HOT stands for Heap-Only Tuple. A HOT update creates the new tuple version on the same heap page as the old one and updates no indexes at all.

Two conditions must both hold for an update to qualify as HOT:

  • No indexed column changed. If you touch any column used by any index, HOT is impossible.
  • There is room on the same page for the new tuple version.

When both hold, the old tuple's line pointer is redirected to point at the new tuple, forming a HOT chain. Indexes keep pointing at the original line pointer and never need to be touched.

The HOT Chain Mechanics

Inside a page, each tuple has a line pointer (item ID). On a HOT update:

  • The new tuple gets a fresh line pointer and the HEAP_ONLY_TUPLE flag.
  • The old tuple gets the HEAP_HOT_UPDATED flag, and its t_ctid points forward to the new tuple.
  • Index entries still reference the original line pointer, so a scan follows the chain forward to find the live version.

When VACUUM later runs, it can prune the chain: dead intermediate versions are removed and the root line pointer is converted into a redirect pointer straight to the surviving tuple. This is called HOT pruning and can even happen opportunistically during a normal page read (heap_page_prune).

Inspecting HOT Activity with pg_stat

You can measure how many of your updates are going the HOT path. The view pg_stat_user_tables exposes both the total updated tuples and the subset that were HOT.

A healthy write-heavy table wants n_tup_hot_upd close to n_tup_upd. A low ratio signals that updates are touching indexed columns or that pages are full.

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;

Rule #1: Don't Index Volatile Columns

The single most effective way to enable HOT is to avoid indexing columns that change frequently. Every index over a hot-path column forces non-HOT updates whenever that column is written.

Consider a sessions table whose last_seen_at is bumped on every request. If you index it, every heartbeat is a non-HOT update with full index churn.

  • Ask: does this index serve a real query, or is it speculative?
  • Frequently-mutated, low-selectivity columns rarely benefit from a btree anyway.
  • Dropping such an index can instantly flip a workload from 0% HOT to near 100%.
-- Anti-pattern: indexing a column updated on every request
CREATE INDEX idx_sessions_last_seen ON sessions (last_seen_at);

-- Each heartbeat now forces a non-HOT update + index insert:
UPDATE sessions SET last_seen_at = now() WHERE id = 42;

Rule #2: Leave Free Space with fillfactor

HOT needs room on the same page for the new tuple. If the page is packed, the new version spills to another page and the update cannot be HOT.

fillfactor tells PostgreSQL to leave a percentage of each page empty at load time, reserving space for in-page updates. The default for tables is 100 (pack tightly), which is great for append-only data but hostile to HOT on update-heavy tables.

For tables that are updated a lot, a fillfactor of 70-90 creates the headroom HOT needs.

ALTER TABLE sessions SET (fillfactor = 85);

-- Rewrite existing pages so the new fillfactor takes effect:
VACUUM FULL sessions;  -- or CLUSTER / pg_repack for online rewrite

Putting Both Rules Together

The winning recipe for an update-heavy table is to combine both rules: keep indexed columns stable and reserve in-page space.

Here a counters table is bumped constantly. We index only the stable lookup key, never the counter, and we set a generous fillfactor so the repeated updates stay on-page.

  • Index is on key, which never changes → condition 1 satisfied.
  • fillfactor = 80 leaves room → condition 2 satisfied.
  • Result: counter increments are HOT updates with zero index writes.
CREATE TABLE counters (
    key   text PRIMARY KEY,
    hits  bigint NOT NULL DEFAULT 0
) WITH (fillfactor = 80);

-- The hot path: bumps only a non-indexed column
UPDATE counters SET hits = hits + 1 WHERE key = 'page:/home';

Watch Out: Expression and Partial Indexes Still Count

HOT eligibility is decided by whether any indexed column's value changed, not just plain btree columns. This trips people up with:

  • Expression indexes: an index on lower(email) means writing email blocks HOT even if the lowercased value is logically identical.
  • Partial indexes: the indexed column still participates; updating it can break HOT regardless of the WHERE predicate.
  • Included columns (INCLUDE): in PostgreSQL, columns in the INCLUDE clause are also part of the index, so changing them blocks HOT.

PostgreSQL compares the old and new values of every column referenced by every index; if all are unchanged, HOT is allowed.

-- Both of these put `email` into the indexed-column set,
-- so any UPDATE that writes email becomes non-HOT:
CREATE INDEX idx_users_email_ci ON users (lower(email));
CREATE INDEX idx_users_email_inc ON users (id) INCLUDE (email);

Verifying HOT on a Real Update

The pg_stat_user_tables counters are cumulative, so you can take a before/after snapshot around a known workload to prove whether your tuning worked.

Run an update batch, then compare n_tup_hot_upd delta against n_tup_upd delta. If they move together, you are fully HOT; if only n_tup_upd climbs, something is still forcing index updates.

SELECT n_tup_upd, n_tup_hot_upd
FROM   pg_stat_user_tables
WHERE  relname = 'counters';

-- ... run UPDATE counters SET hits = hits + 1 WHERE key = 'page:/home'; x1000 ...

SELECT n_tup_upd, n_tup_hot_upd
FROM   pg_stat_user_tables
WHERE  relname = 'counters';
-- Expect both deltas to be ~1000 for a healthy HOT workload.

HOT and WAL Volume

Reducing index writes directly reduces WAL. A non-HOT update logs the heap change and every index insert; a HOT update logs only the heap change (plus, periodically, a prune record).

On a busy table with several indexes, switching updates to HOT can cut WAL generation substantially, which in turn:

  • Shrinks replication lag on standbys.
  • Reduces checkpoint and background-writer pressure.
  • Lowers archive storage for PITR.

You can quantify WAL per statement with pg_stat_statements (wal_bytes) to see the before/after effect of your fillfactor and index changes.

SELECT query, calls, wal_bytes,
       round(wal_bytes / NULLIF(calls, 0)) AS wal_per_call
FROM   pg_stat_statements
WHERE  query ILIKE 'UPDATE counters%'
ORDER  BY wal_bytes DESC;

When HOT Cannot Save You

HOT is powerful but not universal. It does not help when:

  • You genuinely need to update an indexed column (e.g. a status field that is also a search key) — the index write is unavoidable, though you can sometimes redesign the access path.
  • Pages stay full despite fillfactor because rows grow (variable-length text/JSONB expanding), pushing new versions off-page.
  • Long-running transactions hold back the xmin horizon, preventing HOT pruning so chains and bloat accumulate anyway.

The practical playbook: index only stable columns, set fillfactor on update-heavy tables, keep transactions short so pruning can reclaim space, and measure with n_tup_hot_upd and WAL stats.

Quick Check: Enabling HOT

Test your understanding of what makes an update HOT-eligible.

Recap: Keeping Updates Heap-Only

You now know how to engineer schemas and indexes so updates stay heap-only:

  • HOT update = new tuple on the same page, zero index writes, forming a HOT chain that VACUUM/pruning later collapses.
  • Two conditions: no indexed column changed, and there is free space on the page.
  • Rule 1: don't index volatile/hot-path columns; expression, partial, and INCLUDE columns all count as indexed.
  • Rule 2: set fillfactor (70-90) on update-heavy tables to reserve in-page room.
  • Measure: chase a high n_tup_hot_upd / n_tup_upd ratio and watch wal_bytes drop.
  • Limits: growing rows, genuinely indexed updates, and long transactions can still defeat HOT.

Maximizing HOT is one of the highest-leverage, lowest-risk wins for write-heavy PostgreSQL workloads.

Frequently asked questions

Is the “HOT Updates and Heap-Only Tuple Chains” lesson free?

Yes — the full text of “HOT Updates and Heap-Only Tuple Chains” is free to read here on the web, and the PostgreSQL Performance & Query Optimization course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the PostgreSQL Performance & Query Optimization course, upgrade to CoddyKit PRO.

What will I learn in “HOT Updates and Heap-Only Tuple Chains”?

Engineer schemas and indexes so updates stay heap-only and avoid index write amplification. You practise PostgreSQL Performance & Query Optimization with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start PostgreSQL Performance & Query Optimization?

No prior experience is required. PostgreSQL Performance & Query Optimization on CoddyKit is structured for beginners through advanced learners; this is — lesson 2 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “HOT Updates and Heap-Only Tuple Chains” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this PostgreSQL Performance & Query Optimization lesson?

Yes. Every PostgreSQL Performance & Query Optimization lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

  1. Tuple Visibility, xmin, and xmax
  2. HOT Updates and Heap-Only Tuple Chains
  3. The Visibility Map and Index-Only Scans
  4. WAL Generation and Write Amplification
← Back to PostgreSQL Performance & Query Optimization