HOT update

What a HOT update is

Every row in PostgreSQL is stored as a tuple on a heap page — an 8 KB block of storage. When you UPDATE a row, PostgreSQL does not modify the tuple in place. It writes an entirely new tuple (the new version) and marks the old one as dead. In a normal update, every index on the table must also be updated: each index gets a new entry pointing to the new tuple's location. This is, if I may say so, rather a lot of bookkeeping for changing a single value.

A HOT update is a special case. When two conditions are met — no indexed column was changed, and the new tuple fits on the same page as the old one — PostgreSQL writes the new tuple to the same heap page and sets a forwarding pointer (t_ctid) from the old tuple to the new one. The old tuple becomes a redirect rather than a dead end. Index entries continue to point to the original tuple's line pointer, which follows the chain to find the current version.

This is the redirect chain. An index scan lands on the original line pointer, follows the redirect to the current tuple, and returns the data. No index entry was touched. No index WAL was written. The update is "heap-only" — it happened entirely within the heap, invisible to the index layer. Elegant in its economy.

-- A HOT update skips ALL index updates.
--
-- Conditions met:
--   1. "bio" is not in any index
--   2. New tuple fits on the same heap page
--
-- UPDATE users SET bio = 'New bio' WHERE id = 1;
--
-- 1. New heap tuple written on the SAME page as the old one
-- 2. Old tuple's t_ctid points forward to the new tuple (redirect chain)
-- 3. Index entries unchanged — they still point to the original tuple
-- 4. WAL record for the heap update only
--
-- That is 1 physical write + WAL. No index maintenance at all.

Why HOT updates matter

Write amplification is the core problem, and it deserves your attention. In PostgreSQL, a single logical UPDATE can generate a surprising number of physical writes.

Consider a table with 5 indexes. A normal update to one non-indexed column produces: one new heap tuple, five new index entries (one per index), and WAL records for all six writes. That is a 6x write amplification factor for a single-column change. On a table with 10 indexes, it is 11x.

-- A normal UPDATE touches the heap AND every index on the table.
--
-- Table: users (3 indexes: pk on id, idx on email, idx on name)
-- UPDATE users SET bio = 'New bio' WHERE id = 1;
--
-- Even though only "bio" changed (not indexed):
-- 1. New heap tuple written on a (possibly different) page
-- 2. Index on id          → new entry pointing to new tuple
-- 3. Index on email       → new entry pointing to new tuple
-- 4. Index on name        → new entry pointing to new tuple
-- 5. WAL records for heap + all 3 index updates
--
-- That is 4 physical writes + WAL for a single-column change.

A HOT update for the same change produces: one new heap tuple on the same page, one WAL record. That is 1x. The index maintenance — which dominates the cost of writes on heavily-indexed tables — is eliminated entirely.

The impact scales with the number of indexes and the update frequency. A table with 8 indexes receiving 10,000 updates per second generates 80,000 index writes per second under normal updates. With HOT, those 80,000 index writes drop to zero. This is not a marginal improvement. It is the difference between an I/O-bound database and one that can receive guests without breaking a sweat.

Conditions for HOT

Both conditions must be true at the time of the update — no exceptions, no partial credit. If either fails, PostgreSQL falls back to a normal update silently.

• No indexed column changed — the UPDATE must not modify any column that appears in any index on the table. This includes primary keys, unique indexes, partial indexes, expression indexes, and covering index columns added with INCLUDE. If a column is indexed anywhere, changing it disqualifies the update from being HOT.
• New tuple fits on the same page — there must be enough free space on the current heap page to store the new tuple. If the page is full, PostgreSQL has to place the new tuple on a different page, which means the old line pointer cannot redirect to it, and a regular update is performed instead.

The first condition is determined by your schema — which columns you index. The second is determined by your storage settings — specifically fillfactor, which controls how much free space is reserved on each page for future updates. Both are within your control. I find that worth emphasizing.

Monitoring HOT updates

PostgreSQL, to its credit, makes this easy to observe. The statistics view pg_stat_user_tables tracks two columns of interest: n_tup_upd (total updates) and n_tup_hot_upd (updates that qualified as HOT). The ratio between them is your HOT update rate.

-- Check HOT update ratio per table
SELECT
  schemaname,
  relname,
  n_tup_upd,
  n_tup_hot_upd,
  CASE WHEN n_tup_upd > 0
    THEN round(n_tup_hot_upd::numeric / n_tup_upd * 100, 1)
    ELSE 0
  END AS hot_pct
FROM pg_stat_user_tables
WHERE n_tup_upd > 1000
ORDER BY n_tup_upd DESC;

A HOT ratio above 90% is excellent — the household is running well. Between 50% and 90% suggests room for improvement through fillfactor tuning or index consolidation. Below 50% means most updates are paying the full write amplification cost, and I would gently suggest the table deserves your attention.

These statistics are cumulative since the last pg_stat_reset(). To measure the HOT ratio for a specific time window, note the values before and after, and compute the delta.

Maximizing HOT updates

Two levers control your HOT update rate, and both reward thoughtful configuration: fillfactor and index selection.

Fillfactor

The fillfactor storage parameter controls what percentage of each heap page is filled with data during inserts and bulk loads. The remaining space is reserved for future updates. The default is 100 — pages packed completely full, leaving no room whatsoever for HOT updates. A curious default for a database that writes new tuples on every update, but there it is.

Lowering fillfactor to 80 or 90 on update-heavy tables reserves 10-20% of each page as free space, dramatically increasing the chance that a new tuple fits on the same page as the old one.

-- Lower fillfactor reserves space on each page for HOT updates
-- Default is 100 (pack pages full). 80-90 is common for update-heavy tables.

-- Set fillfactor when creating a table
CREATE TABLE orders (
  id    bigint PRIMARY KEY,
  status text,
  total  numeric
) WITH (fillfactor = 80);

-- Change fillfactor on an existing table
ALTER TABLE orders SET (fillfactor = 80);

-- The new fillfactor only applies to newly written pages.
-- To rewrite existing pages with the new setting:
VACUUM FULL orders;
-- Or, without blocking reads/writes:
-- pg_repack -d mydb -t orders

The right fillfactor depends on your update patterns. Tables with frequent small updates to non-indexed columns benefit the most. Insert-only tables gain nothing from a lower fillfactor and waste disk space — there is no virtue in reserving space that will never be used. Start with 80 for heavily-updated tables and adjust based on the HOT ratio you observe.

Index discipline

Every index on a table is another column (or set of columns) that, when changed, prevents a HOT update. Fewer indexes mean more columns can be updated via HOT.

Allow me to be clear: this is not an argument against indexes — the right indexes are essential for read performance. But it is an argument against unnecessary ones. Unused indexes, redundant indexes (where one index is a prefix of another), and speculative indexes added "just in case" all reduce HOT eligibility with no read benefit. They are staff who draw a salary without performing any duties.

-- Find tables with many indexes (more indexes = fewer HOT opportunities)
SELECT
  schemaname,
  tablename,
  count(*) AS index_count
FROM pg_indexes
GROUP BY schemaname, tablename
HAVING count(*) > 5
ORDER BY count(*) DESC;

-- Check which columns are indexed on a specific table
SELECT
  indexname,
  indexdef
FROM pg_indexes
WHERE tablename = 'orders'
ORDER BY indexname;

Audit your indexes periodically. Remove the ones that are not serving queries. Every index you drop is an index that can no longer prevent a HOT update. A leaner household runs more smoothly.

How Gold Lapel relates

When I evaluate whether to recommend an index, I consider what it would cost you in HOT updates. Adding an index improves read performance for the queries it serves, but it also reduces HOT eligibility for updates on the indexed columns. I weigh both sides — the read benefit of the proposed index against the write cost of losing HOT updates on the table — and only recommend indexes where the net effect is genuinely positive.

This is particularly relevant for tables with high update rates. A table receiving thousands of updates per second to non-indexed columns may be enjoying a near-100% HOT ratio. Adding an index on a frequently-updated column would flip those updates from HOT to non-HOT, multiplying write I/O. I flag this trade-off explicitly when surfacing index recommendations, so you can make the decision with full visibility into the write-side consequences. It would be rather poor form to improve your reads at the expense of your writes without mentioning it.