Postgres HOT Updates: How Heap-Only Tuples Avoid the Index Write Tax

Postgres uses multi-version concurrency control, which means every UPDATE is technically an INSERT of a new row version plus a mark on the old one. The naive consequence is that every UPDATE that touches an indexed table has to write a new heap row, write new index entries pointing at the new row, and eventually let autovacuum reclaim the old row. For a table with five indexes, every UPDATE is six writes minimum. The HOT optimization (Heap-Only Tuple) eliminates the index updates when certain conditions hold, reducing the write amplification by a factor of N where N is the number of indexes. The mechanism is invisible from the application but operationally consequential.

What HOT actually does

When Postgres updates a row, it checks two conditions. First, are any of the updated columns referenced by an index? Second, is there space available for the new row version on the same heap page as the old one? If both conditions hold, the update is a HOT update: the new row goes on the same page as the old one, the old row's t_ctid pointer is updated to point at the new version, and no indexes are touched at all. Index lookups continue to point at the old row, find the t_ctid forwarding pointer, and follow the chain to the current version.

The savings are substantial. On a table with five indexes, a regular UPDATE writes one heap row plus five index entries plus eventually marks the dead heap row. A HOT update writes one heap row, updates one t_ctid pointer on the dead row, and touches no indexes. The 6x write reduction is the headline number, but the secondary effects compound: less WAL volume, less index bloat, less work for autovacuum, less buffer pool pressure from index writes.

The conditions that prevent HOT

The first condition is that no indexed columns can be updated. If a table has an index on email and the UPDATE changes the email value, the update cannot be HOT regardless of any other consideration, because the indexes need to be updated to reflect the new value. The optimization is gated entirely on the application-side question of which columns get updated.

The second condition is page-local space. Postgres needs to fit the new row version on the same 8KB heap page as the old one. If the page is already full (which is the default state after the page first fills up, because the default fillfactor is 100), the new row has to go on a different page, the index entries have to be updated to point at the new page, and the update is not HOT.

The first condition is a schema-and-access-pattern question. The second is a tunable. Postgres lets you specify a fillfactor on a per-table basis at creation or via ALTER TABLE, which reserves space on each page for future HOT updates. A fillfactor of 80 means the table will fill each page to 80% on initial INSERT and reserve the remaining 20% for HOT update headroom. Tables that get frequent updates benefit dramatically from fillfactor in the 70-90 range; tables that are mostly append-only should keep the default 100 because the reserved space would be wasted.

The diagnostic

The pg_stat_user_tables view exposes per-table counters for HOT vs non-HOT updates. The relevant columns are n_tup_upd (total updates) and n_tup_hot_upd (the HOT-eligible subset). The diagnostic query is:

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
WHERE n_tup_upd > 0
ORDER BY n_tup_upd DESC
LIMIT 20;

Tables with high update volume and low HOT percentage are the candidates for optimization. The two interventions are reducing fillfactor (typically to 80 or 90) and removing indexes on frequently-updated columns. The choice between the two depends on whether the indexes are pulling their weight for read queries, which is a separate question that pg_stat_user_indexes can help answer.

The trade-offs

Fillfactor reduction is not free. A fillfactor of 80 means the table uses 25% more disk space than a fillfactor of 100 for the same row count, because 20% of each page is reserved. The buffer pool sees the same effect: cached pages contain 25% fewer rows, which can reduce cache efficiency for read-heavy workloads. The right fillfactor balances update efficiency against storage and cache efficiency, and the right answer depends on the read-to-write ratio.

The conservative starting point for high-update tables is 90. The aggressive setting for tables dominated by updates is 80. Going below 80 starts to waste substantial space without much additional HOT benefit, because the limiting factor becomes the indexed-column-update condition rather than the page-space condition.

The other trade-off is that HOT updates create chains. If a row is updated multiple times in succession, the t_ctid pointers form a chain from the original tuple through several intermediate versions to the current one. Index lookups have to follow this chain, which adds a small per-update overhead to reads. Autovacuum compacts these chains by removing dead intermediate versions, but if autovacuum falls behind on a heavily-updated table, the chains can grow long enough to measurably slow reads. The mitigation is healthy autovacuum tuning, which is its own topic.

What HOT does not solve

HOT helps with index write amplification on UPDATE. It does not help with INSERT, DELETE, or any kind of bulk operation. It does not help with WAL volume below the index-update savings. It does not help if the workload updates indexed columns frequently, because no fillfactor adjustment can make those updates HOT-eligible.

HOT also does not help with the small overhead of MVCC itself: every UPDATE still produces a dead tuple that autovacuum has to clean up eventually. The dead tuple lives in the same page as the new version, which is operationally convenient, but it still consumes space until vacuum reclaims it. Heavy update workloads will still generate substantial autovacuum work; HOT just reduces the index portion of that work.

The application-level discipline

The schema discipline for HOT-friendly designs is to keep frequently-updated columns out of indexes when possible. The classic anti-pattern is indexing updated_at on tables where every UPDATE touches updated_at. The index makes sorting by recency fast, but every UPDATE becomes a non-HOT update with full index maintenance cost. The mitigation is either to drop the updated_at index (if recency queries are rare), accept the cost (if they are common), or use a BRIN index (which has dramatically lower update cost than B-tree at the cost of less precise filtering).

The other common anti-pattern is including frequently-updated columns in multi-column indexes for query performance. An index on (account_id, updated_at) where updated_at changes on every UPDATE has the same problem as a standalone index on updated_at: every UPDATE has to maintain the index entry. The diagnostic is the same: look at the hot_pct column for the table and decide whether the index is worth the update cost.

The four-product application

Across DocuMint, CronPing, FlagBit, and WebhookVault, the HOT considerations matter most for the tables with frequent updates and multiple indexes. WebhookVault's webhook_deliveries table is the heaviest update workload, with each delivery attempt updating status and timestamps. The fillfactor on that table is 85 because the alternative was substantial index bloat that quarterly REINDEX could not keep up with. CronPing's monitor table has similar dynamics for the last_ping_at column; the table uses fillfactor 85 and updated_at is not indexed.

DocuMint's generation_log is mostly append-only with a single status update per row, so fillfactor 100 is correct: the small number of UPDATEs do not justify the storage cost of reservation. FlagBit's flags table updates rules frequently but rules are stored as JSONB and indexed via GIN, which has different update mechanics; the fillfactor on the underlying heap is 90 as a compromise.

The deeper observation is that HOT is one of the load-bearing optimizations that makes Postgres viable for heavy-update workloads at reasonable hardware cost. The optimization is invisible from application code but visible in operations, and the operational knobs (fillfactor, index choices, autovacuum tuning) have to be tuned in coordination to produce the actual write-amplification savings. Postgres has many features like this where the conceptual mechanism is simple but the production tuning involves trade-offs across multiple subsystems.

Our products: DocuMint (PDF invoice generation API), CronPing (cron job monitoring with status pages), FlagBit (feature flags API for modern teams), and WebhookVault (webhook capture and replay) put these patterns into production.