Lesson 0002 · Storage & MVCC

Dead tuples, VACUUM & HOT updates

Lesson 0001 left dead tuples piling up in the heap. This lesson follows them: who removes them, when autovacuum decides to run, and the one trick — HOT — that stops an UPDATE from touching every index.

~10 minWarm-up recall + quiz + InnoDB contrastCheat sheet: here

Good. You know dead tuples accumulate and that VACUUM reclaims them. Two questions were left open: when does that cleanup actually fire, and can we make an UPDATE cheaper in the first place? Both answers matter because — remember from 0001 — every Postgres index is secondary, so by default a new row version means a new entry in every index on the table.

What VACUUM actually does with the space

Here is the fact that surprises people: plain VACUUM does not shrink your table on disk. It marks dead-tuple space reusable inside the table, but does not hand it back to the operating system. — PostgreSQL Docs: "The standard form of VACUUM removes dead row versions… and marks the space available for future reuse. However, it will not return the space to the operating system…"

So after a bloat episode, a vacuumed table stays large on disk but stops growing — new rows fill the freed slots. To physically shrink it you need VACUUM FULL, which rewrites the whole table with no dead space — but takes an ACCESS EXCLUSIVE lock, blocking all reads and writes for the duration. — PostgreSQL Docs: "VACUUM FULL actively compacts tables by writing a complete new version of the table file with no dead space… requires an ACCESS EXCLUSIVE lock."

Autovacuum: the janitor's trigger

You rarely run VACUUM by hand. Autovacuum automates it. — PostgreSQL Docs: "PostgreSQL has an optional but highly recommended feature called autovacuum, whose purpose is to automate the execution of VACUUM and ANALYZE commands." A table is vacuumed once its number of dead tuples crosses a threshold that is proportional to the table's size:

— formula and defaults from the docs: base threshold default 50 tuples, scale factor default 0.2 (20% of table size). (Recent versions also cap it with a Minimum(...) max-threshold; the proportional term is the mental model that matters.)

Read the shape, not the arithmetic: a table is vacuumed after roughly 20% of its rows have become dead. Two consequences fall straight out:

• A 1,000-row table waits for ~250 dead tuples (50 + 0.2×1000). Cheap, frequent.
• A 100-million-row table waits for ~20 million dead tuples before autovacuum stirs. On a hot, heavily-updated big table that is far too much bloat — which is exactly why the standard tuning move is to lower autovacuum_vacuum_scale_factor (or set a per-table override) so large tables get vacuumed sooner.

HOT: making an UPDATE skip the indexes

Now the payoff. Normally an UPDATE writes a new tuple and, because every index is secondary and points at a physical tuple, must add a new entry to every index on the table — write amplification that also creates more index bloat. Heap-Only Tuples (HOT) is the optimization that avoids it entirely, when two conditions hold:

1. the update doesn't change any indexed column, and — PostgreSQL Docs: "The update does not modify any columns referenced by the table's indexes…"
2. there is room on the same heap page for the new version. — PostgreSQL Docs: "There is sufficient free space on the page containing the old row for the updated row."

When both hold, the new version is chained to the old one within the page and no new index entries are created — the existing index entry still points at the page, and a redirect leads to the live version. — PostgreSQL Docs: "New index entries are not needed to represent updated rows…"

You raise HOT's hit rate by leaving free space on each page — lower the table's fillfactor below 100 so pages start with room for future in-page versions. — PostgreSQL Docs: "You can increase the likelihood of sufficient page space for HOT updates by decreasing a table's fillfactor." There's a second gift: HOT chains can be pruned during ordinary reads, reclaiming the intermediate dead versions without waiting for a full VACUUM.

Postgres vs InnoDB — the maintenance model

Check yourself

From memory — the effortful recall is the point. Two items reach back to earlier ideas on purpose.

CREATE TABLE t (id int PRIMARY KEY, note text, val int);
CREATE INDEX ON t (note);                 -- an index on note, but NOT on val
INSERT INTO t SELECT g, 'x', 0 FROM generate_series(1,1000) g;

UPDATE t SET val = val + 1;               -- changes val only (not indexed) → HOT-eligible
UPDATE t SET note = note || 'y';          -- changes note (indexed) → NOT HOT

SELECT n_dead_tup, n_tup_upd, n_tup_hot_upd
FROM pg_stat_user_tables WHERE relname='t';   -- compare hot vs total updates