0Pricing
PostgreSQL Performance & Query Optimization · 课时

HOT 更新与仅堆元组链

设计表结构和索引，使更新保持为仅堆操作，避免索引写放大。

HOT 更新与仅堆元组链 是 CoddyKit 上的免费 PostgreSQL Performance & Query Optimization 课时。 这是第 2 节课，共 4 节。 你可以在下方免费阅读本课时的完整内容 — 然后在浏览器中使用内置代码编辑器和全天候 AI 导师进行实践。 这是 PostgreSQL Performance & Query Optimization 学习路径的一部分，你的进度在网页和 CoddyKit 应用中同步。 PostgreSQL Performance & Query Optimization 课程共包含 4 节课。

本课时的部分内容尚未翻译，以英文显示。

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.

常见问题解答

「HOT 更新与仅堆元组链」课时是免费的吗？

是的 — 「HOT 更新与仅堆元组链」的完整文本可在网页上免费阅读。要进行交互式练习（内置代码编辑器和全天候 AI 导师）并解锁 PostgreSQL Performance & Query Optimization 课程的其余内容，请升级到 CoddyKit PRO。 PostgreSQL Performance & Query Optimization 课程共包含 4 节课。

「HOT 更新与仅堆元组链」这节课中我会学到什么？

设计表结构和索引，使更新保持为仅堆操作，避免索引写放大。 你通过在浏览器中直接运行的动手代码来练习 PostgreSQL Performance & Query Optimization，全天候 AI 导师会在你学习这节课的过程中回答你的问题。

学习 PostgreSQL Performance & Query Optimization 需要有经验吗？

无需任何先前经验。CoddyKit 上的 PostgreSQL Performance & Query Optimization 课程适合初学者到高级学习者，你可以从这里开始或从头开始，按照自己的节奏学习。 这是第 2 节课，共 4 节。

「HOT 更新与仅堆元组链」课时需要多长时间？

大多数 CoddyKit 课程大约需要 5–10 分钟。每节课都很精短且互动，所以你能稳步进步，并在网页和应用中从离开的地方继续。

我能在这节 PostgreSQL Performance & Query Optimization 课中编写并运行代码吗？

能。每节 PostgreSQL Performance & Query Optimization 课都包含内置代码编辑器，你可以在浏览器中直接编写并运行真实代码，并获得即时 AI 反馈 — 无需本地设置。

此课程中的所有课时

  1. 元组可见性、xmin 与 xmax
  2. HOT 更新与仅堆元组链
  3. 可见性映射与仅索引扫描
  4. WAL 生成与写放大
← 返回 PostgreSQL Performance & Query Optimization