Heap access, the visibility map and MVCC storage
Should we index the column we update most?
The idea in 30 seconds
Indexing a column changes the cost of every update to it, not just of the queries that read it. A column rewritten on every request, like last_seen, should only be indexed if the query needing it is worth turning every one of those writes into a full, index-touching update. Often a coarser column, or a different source for the query, gives the read without the write tax.
Helps to know first: How a table is stored on disk, B-tree indexes
On this page
The situation
Every authenticated request runs UPDATE sessions SET last_seen = now() WHERE id = $1. Product wants an “active in the last 5 minutes” count on the admin dashboard. An AI assistant opened a pull request.
Review taskIndex last_seen for the dashboard
Pull request: “The active-users count scans the whole sessions table. Add an index.”
CREATE INDEX ON sessions (last_seen);
-- dashboard, refreshed every 30 seconds
SELECT count(*) FROM sessions WHERE last_seen > now() - interval '5 minutes';Would you approve it? Write the review you would leave.
Your call
Model review
Request changes. The dashboard query gets faster, but every request in the product gets slower to save it.
1. It turns off HOT on the hottest update. Today the last_seen update changes no indexed column, so it is a heap-only update: same page, no index work. Indexing last_seen makes every one of those updates add entries to every index on the table. We measured 100,000 such updates on a 200,000-row copy:
| HOT updates | WAL | Time | |
|---|---|---|---|
Without an index on last_seen | 92% | 19 MB | 1.8 s |
| With the index | 0% | 38 MB | 2.4 s |
Twice the log, a third more time, and the user_id index grows too, all to speed up one query that runs twice a minute.
2. Check what the query costs now. Counting rows in a table of a couple of hundred thousand sessions every 30 seconds may already be acceptable. Measure it before paying a write tax on every request.
3. Consider a different design for the read:
- A coarser column. Store
last_seen_minute, updated only when the minute changes, and index that. Most requests then do not touch the indexed column, and stay HOT. - A different source. If request logs or an events table already exist, count recent activity there, where rows are inserted, not updated.
- An approximate answer, refreshed in the background, if the dashboard does not need the exact count.
What I would ask for: keep last_seen unindexed. If the full count is too slow, add a last_seen_minute column updated at most once a minute per session, index that, and check n_tup_hot_upd afterwards to confirm the request path stays mostly HOT.
Compare it with yours. Did your review catch the main problems?
How to decide
Before indexing a column on an updated table, ask how often that column changes. Indexing a column that changes on most updates turns those updates from heap-only into full updates that touch every index.
Prefer leaving frequently updated columns unindexed, unless the query that needs the index matters more than the write path.
Watch HOT in production: n_tup_hot_upd against n_tup_upd in pg_stat_user_tables. A sudden drop after a migration usually means a new index on an updated column.
Use fillfactor deliberately on tables with frequent updates to non-indexed columns: leaving free space raised HOT from 92% to 100% in our test.
Check yourself
Why does an index on
last_seenslow down requests that never read it?Show answer
Every request updates
last_seen. Once it is indexed, those updates change an indexed column, so they cannot be HOT and must add entries to every index on the table.How does a
last_seen_minutecolumn keep most updates HOT?Show answer
It changes at most once a minute per session. Updates that only change the unindexed
last_seenstay heap-only; only the occasional minute change touches the index.Which statistic tells you whether a table’s updates are HOT?
Show answer
n_tup_hot_updcompared withn_tup_updinpg_stat_user_tables.
Finished reading?
Saved in this browser only.
Sources
Where each claim on this page comes from.
- 1.PostgreSQL documentation: 66.7 Heap-Only Tuples (HOT)Primary· §66.7 Heap-Only Tuples (HOT)
An update that changes an indexed column cannot be HOT, so it needs new entries in the table's indexes.
- 2.PostgreSQL documentation: 27.2 The Cumulative Statistics SystemPrimary· §27.2 pg_stat_all_tables
n_tup_upd and n_tup_hot_upd show how many updates were HOT.
Go further
What to read next, and why it is worth the time.
- 1.PostgreSQL documentation: 66.7 Heap-Only Tuples (HOT)Primary· §66.7
The two conditions for HOT, which every index decision on an updated table should be checked against.
Applies to PostgreSQL 18. Draft: not yet fact-checked end to end.