PostgreSQL relies on Multi-Version Concurrency Control (MVCC) to handle concurrent transactions. While MVCC is exceptional for read-heavy workloads, it introduces a significant overhead for write operations, particularly UPDATE statements. In the traditional MVCC model, an update is effectively a DELETE followed by an INSERT. This mechanism creates "dead tuples" and forces every index associated with the table to insert a new entry, even if the indexed columns themselves did not change. This phenomenon is known as Write Amplification and leads to massive index bloat.

To mitigate this, PostgreSQL introduced Heap Only Tuples (HOT). This optimization is a silent savior for high-throughput databases, allowing the system to update rows without touching the indexes, provided certain conditions are met.

The Problem With Standard MVCC Updates

To understand why HOT is necessary, one must first look at how a standard "cold" update works in PostgreSQL.

When a row is updated in a table with five indexes, PostgreSQL creates a new version of the row (a new tuple) in the heap. Because this new tuple resides at a different physical location (a new CTID), every single one of those five indexes must be updated to point to the new location. This happens even if the update only changed a non-indexed "last_login" timestamp.

In my experience managing large-scale PostgreSQL clusters, this "index maintenance" is often the primary bottleneck for write latency. On tables with dozens of indexes, a single row update can trigger an avalanche of random I/O as the database traverses B-tree structures to insert new pointers. Over time, these indexes grow disproportionately large, containing thousands of pointers to dead row versions that haven't been vacuumed yet.

Anatomy of the Heap Only Tuple Mechanism

Heap Only Tuples (HOT) bypass the need to update indexes by creating a "chain" within the data page. When an update qualifies for HOT, PostgreSQL performs two clever tricks: Tuple Chaining and Index Pointer Redirection.

Tuple Chaining and the HOT Link

Instead of creating a new index entry for the new row version, PostgreSQL links the old tuple directly to the new one. The old tuple’s t_ctid field, which normally points to itself or a newer version in another page, is updated to point to the new tuple on the same page.

The new tuple is marked with a specific bit in its header: HEAP_ONLY_TUPLE. Conversely, the old tuple is marked as HEAP_HOT_UPDATED. This signaling tells the storage engine that the chain is active.

Redirection via the Line Pointer

PostgreSQL pages contain an array of line pointers (item identifiers) at the beginning of the page. These pointers map an index’s logical address to the physical offset of the tuple on the page.

In a HOT update, the index continues to point to the original line pointer (the "root"). When a process follows the index to that line pointer, the database sees the HEAP_HOT_UPDATED flag and automatically follows the chain to the latest HEAP_ONLY_TUPLE. The index remains completely oblivious to the fact that the row has moved or changed versions, saving the cost of an index write.

Requirements for a Successful HOT Update

HOT is not a magic wand that applies to every update. For the database to utilize this optimization, two strict criteria must be met. If either fails, the system reverts to a standard, expensive update.

1. No Indexed Columns Can Be Modified

The most critical requirement is that the UPDATE statement must not change any column that is part of any index on the table. This includes:

  • Standard B-tree indexes.
  • Expression indexes (e.g., lower(email)).
  • Partial indexes (even if the row doesn't meet the partial criteria).
  • Foreign key columns (which are usually indexed).

If you update a single column that is part of a single index, HOT becomes impossible for that transaction. The database must ensure that the index keys remain valid, and since the keys themselves are changing, a new index entry is mandatory.

2. Sufficient Free Space on the Same Page

The second requirement is physical: the new version of the row must fit on the exact same 8KB heap page as the old version. If the page is full, PostgreSQL must place the new tuple on a different page.

Because the "link" in a HOT chain cannot span across different pages (it relies on the line pointer redirection which is page-local), moving to a new page breaks the HOT opportunity. This is where the concept of fillfactor becomes vital for database tuning.

Optimizing HOT via Fillfactor Tuning

In a default PostgreSQL configuration, the fillfactor for a table is 100. This means that during initial INSERT or COPY operations, PostgreSQL fills every page to the brim. While this is space-efficient for static data, it is disastrous for HOT.

With a 100% fillfactor, there is no "breathing room" for an updated row version to be stored on the same page. Consequently, almost every update becomes a "cold" update, requiring index updates and leading to bloat.

Strategic Fillfactor Recommendations

Based on performance audits I've conducted on high-write systems, I recommend the following fillfactor strategies:

  • Read-Only Tables: Keep at 100. There is no benefit to leaving empty space if rows never change.
  • General Purpose Tables: 90 to 95. This provides a small buffer for occasional updates.
  • High-Frequency Update Tables: 70 to 80. By leaving 20-30% of each page empty, you drastically increase the probability that an update will find space on the same page, triggering a HOT update.

To change the fillfactor for an existing table, you can use: