skip to content

In PostgreSQL, what is a HOT (heap-only tuple) update, which two conditions must an UPDATE meet to get one, and what does it save?

level: middleimportance: must knowfreq 62%

answer

  1. every UPDATE writes a new version
  2. same page, untouched indexed columns
  3. root line pointer and a chain
  4. pruning without VACUUM
  5. fillfactor and n_tup_hot_upd

basics

~20 s

A HOT update keeps the new row version on the same heap page with no new index entries. It needs room on that page and no changed column used by a non-BRIN index; it saves index writes and allows pruning without VACUUM.

solid answer

~50 s

Every PostgreSQL `UPDATE` writes a new tuple version, and normally every index on the table gets a new entry pointing at it, even indexes whose columns did not change. A **heap-only tuple (HOT)** update skips that: the new version goes on the **same page**, the old version's `t_ctid` points to it, and the indexes keep pointing at the original line pointer, so an index scan follows the chain. It needs two things: **enough free space** on that page, and **no change to any column an index references** as a key, `INCLUDE` column, expression or partial-index predicate. Summarizing indexes (BRIN is the only one in core) do not block HOT but are still updated. Besides saving index writes and index bloat, HOT lets any backend **prune** dead versions on the page, leaving a redirect line pointer, without waiting for VACUUM. A lower table `fillfactor` reserves room; `n_tup_hot_upd` in `pg_stat_user_tables` shows how often it works.

code

sql · 18 lines
sql
CREATE TABLE articles (
  id           bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  slug         text NOT NULL UNIQUE,
  title        text NOT NULL,
  body         text NOT NULL,
  published_at timestamptz,
  view_count   bigint NOT NULL DEFAULT 0
) WITH (fillfactor = 85);

-- HOT-eligible: view_count is in no index
UPDATE articles SET view_count = view_count + 1 WHERE id = 42;

-- Not HOT: slug is indexed and its value changes
UPDATE articles SET slug = 'postgresql-18-released' WHERE id = 42;

SELECT n_tup_upd, n_tup_hot_upd, n_tup_newpage_upd
FROM pg_stat_user_tables
WHERE relname = 'articles';

go deeper

for a junior

Know that an UPDATE writes a new row version and that HOT is the optimisation that keeps it on the same page without touching indexes.

for a middle

State both conditions precisely, including INCLUDE, expression and predicate columns, and explain the root line pointer, the chain and on-page pruning.

for a senior

Show you can measure HOT with n_tup_hot_upd and n_tup_newpage_upd, choose a fillfactor, and push back on indexes on frequently updated columns.

for a principal

Frame HOT as a schema-design constraint: which columns get indexed, how hot counters are modelled, and the space-for-write-cost trade of fillfactor across a fleet.

## Why an ordinary UPDATE is expensive in PostgreSQL PostgreSQL never overwrites a row in place. An `UPDATE` marks the old **tuple** (row version) as replaced, writes a complete new tuple, and sets the old tuple's `t_ctid` to point at the new one. Indexes point at a physical address, the `ctid` (page number plus line-pointer number), so in the general case **every index on the table** receives a new entry for the new tuple, even an index whose columns did not change. The old entries stay until VACUUM removes them. On a table with five indexes, bumping one counter means one heap write, five index insertions and, later, five index cleanups. ## The two conditions PostgreSQL takes the **heap-only tuple (HOT)** path only when both hold: 1. **The new version fits on the same heap page** as the old one. 2. **No column referenced by a non-summarizing index has changed.** Details interviewers probe on the second condition: - *Referenced* means anywhere in an index definition: key columns, `INCLUDE` columns, columns inside an expression such as `lower(slug)`, and columns in a partial index's `WHERE` predicate. - PostgreSQL compares old and new **values**, not the `SET` list. `SET slug = slug` leaves the value identical and does not block HOT. - **Summarizing indexes** do not block HOT. BRIN is the only summarizing method in core; if its column changes, the BRIN index is still updated, but the heap side stays heap-only. - The primary key gets no special treatment; it is one more index. ## What happens on the page Each page carries an array of 4-byte **line pointers**, and an index entry points at a line pointer, not at tuple bytes. A HOT update writes the new tuple on the same page and flags it heap-only. Index entries keep pointing at the **root** line pointer, and an index scan walks the **HOT chain** from the root through each `t_ctid` until it reaches the version its snapshot can see. | Line pointer state | Meaning | |---|---| | `LP_NORMAL` | Points to tuple data on the page | | `LP_REDIRECT` | Points to another line pointer: the root of a pruned HOT chain | | `LP_DEAD` | The tuple is dead; index entries may still point here until VACUUM | | `LP_UNUSED` | Free for reuse | ## Pruning: cleanup without VACUUM HOT's second benefit is that dead versions in a chain can be removed by **any backend that touches the page**, including a plain `SELECT`. When the page holds prunable versions and its free space has dropped below the larger of the fillfactor reserve and 10% of the page, the backend tries for a cleanup lock and prunes: versions no transaction can still see are removed, the root line pointer becomes a redirect to the oldest version still needed, and the space is compacted for the next update. No index is touched, because no index entry points at the intermediate versions. Pruning does not replace VACUUM. Dead line pointers left by non-HOT updates and deletes, their index entries, and the visibility map still need it. ## Making HOT likely - **Leave room on the page.** The table storage parameter `fillfactor` (10 to 100, default 100) makes `INSERT` fill pages only to that percentage, keeping the rest for updates of rows already there. Values around 80 to 90 are a common start for update-heavy tables; append-only tables should stay at 100. - **Do not index volatile columns** without a query that needs it: one index on a counter or a `last_seen_at` turns every update of that column into a non-HOT update. - **Keep hot rows narrow.** Every version is a full copy of the row, so a wide inline row fills the reserved space after a few updates; moving a frequently updated column into its own narrow table keeps its versions small. - **Measure.** `pg_stat_user_tables` counts `n_tup_upd`, `n_tup_hot_upd` and `n_tup_newpage_upd`; the HOT ratio is `n_tup_hot_upd / n_tup_upd`. To see a chain on one page, the superuser-only contrib function `heap_page_items(get_raw_page('articles', 0))` from `pageinspect` lists each line pointer with its `lp_flags` (1 normal, 2 redirect, 3 dead) and each tuple's `t_ctid`. | | HOT update | Non-HOT update | |---|---|---| | New heap tuple | Yes, on the same page | Yes, on the same or another page | | New index entries | None (BRIN still updated if its column changed) | One in every index | | Removing the old version | On-page pruning by any backend | VACUUM, including index passes | | WAL | Heap change, plus BRIN if its column changed | Heap change plus every index insertion |

  • Does UPDATE articles SET slug = slug, view_count = view_count + 1 block HOT when slug is indexed?
    No. PostgreSQL compares the old and new values of indexed columns, not the columns named in `SET`. Assigning an identical value counts as unchanged, so the update stays HOT-eligible if the page has room. That is why ORMs that write every column do not automatically defeat HOT, though a value whose stored bytes differ still counts as changed.
  • Can a HOT chain continue onto another page?
    No. A HOT chain lives entirely inside one heap page. When the new version has to go to a different page the update is non-HOT, every index gets a new entry, and `n_tup_newpage_upd` counts it. That is why `fillfactor`, the setting that reserves room in advance, matters for update-heavy tables.
  • Does HOT remove the need for VACUUM on a table?
    No. Pruning reclaims tuple space inside a page and leaves redirect or dead line pointers, but dead line pointers from non-HOT updates and deletes, their index entries, and the visibility map are still VACUUM's job. HOT reduces the work VACUUM finds; it does not replace it.

  • HOT means PostgreSQL overwrites the row in place instead of writing a new version.
  • A HOT update skips WAL because no index changes.
  • Any UPDATE that leaves the primary key unchanged is a HOT update.
  • Columns in an index's INCLUDE list do not affect HOT eligibility.
  • Lowering fillfactor on an append-only table makes it faster.