Run an UPDATE in PostgreSQL and it feels like the row changed. It didn't. Postgres wrote a brand-new copy of the row somewhere else and left the old one sitting exactly where it was, invisible but very much on disk. That one design decision explains a surprising amount of day-to-day Postgres behavior: why heavily updated tables bloat, why VACUUM exists at all, why long-running transactions are dangerous, and why a database that ignores maintenance for long enough will eventually refuse to accept writes.
The trade MVCC makes
Postgres implements Multiversion Concurrency Control (MVCC): each SQL statement sees a snapshot of the data as it was at some point in time, regardless of what concurrent transactions are doing. The payoff, in the documentation's own words, is that "reading never blocks writing and writing never blocks reading." A reporting query can scan a table for ten minutes while writers keep committing, and nobody waits on anybody.
The price is that the database can't destroy old row versions the moment they're superseded, because some running transaction might still be entitled to see them. So it doesn't. Every UPDATE is physically an insert of the new version plus a marker on the old one; every DELETE is just the marker. Reclaiming the space is deferred to a separate process.
What a row version actually looks like
Tables are stored as arrays of fixed-size pages (8 kB by default), and every row version — Postgres calls them tuples — carries a fixed header of 23 bytes on most machines. Three header fields do the MVCC bookkeeping:
| Field | Size | Meaning (per the docs) |
|---|---|---|
t_xmin | 4 bytes | insert XID stamp |
t_xmax | 4 bytes | delete XID stamp |
t_ctid | 6 bytes | current TID of this or newer row version |
t_xmin records which transaction created this version; t_xmax records which transaction deleted or superseded it (zero while the version is live). Visibility is then a comparison: a tuple is visible to your snapshot if its inserting transaction committed before your snapshot was taken and no committed transaction had deleted it yet.
You can watch this happen, because xmin, xmax, and ctid are queryable system columns:
CREATE TABLE t (id int PRIMARY KEY, v text);
INSERT INTO t VALUES (1, 'a');
SELECT ctid, xmin, xmax, v FROM t; -- ctid (0,1)
UPDATE t SET v = 'b' WHERE id = 1;
SELECT ctid, xmin, xmax, v FROM t; -- ctid moved, e.g. (0,2); xmin is the updating XIDThe row's physical address changed after the update. The old tuple at the previous position is still in the page — it's now a dead tuple, and its t_ctid points forward to the successor version, forming an update chain.
VACUUM is not one job, it's four
Dead tuples are pure overhead: they occupy pages, they're stepped over by sequential scans, and their index entries still exist. VACUUM is the mechanism that pays down this debt, and the documentation lists four distinct reasons it must run:
- Recover or reuse disk space occupied by updated or deleted rows.
- Update the statistics used by the query planner (via
ANALYZE). - Update the visibility map, which speeds up index-only scans.
- Prevent transaction ID wraparound (more on this below).
A common misconception is that plain VACUUM shrinks your table files. It usually doesn't. Standard VACUUM removes dead row versions and marks the space as available for reuse within that table, but it does not give the space back to the operating system, except in the special case where entire pages at the end of the table become free. In exchange, it runs alongside normal reads and writes. VACUUM FULL actually rewrites the entire table into a compact file — but it takes an ACCESS EXCLUSIVE lock, blocking all use of the table while it runs. The docs' guidance is blunt: for frequently updated tables, frequent standard VACUUMs beat occasional VACUUM FULLs.
In practice you rarely run VACUUM by hand. The autovacuum launcher wakes up every minute by default (autovacuum_naptime) and vacuums a table once its obsoleted-tuple count passes a threshold of autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor × reltuples — with defaults of 50 tuples and 0.2, that's roughly 20% of the table. That default is worth knowing: on a 100-million-row table, autovacuum won't fire until about 20 million dead tuples have piled up, which is why busy installations routinely lower the scale factor per table.
HOT updates: the escape hatch
There's one important case where an update dodges most of this cost. A heap-only tuple (HOT) update happens when two conditions are both true: the update doesn't modify any columns used by the table's indexes (summarizing indexes like BRIN excepted), and there's enough free space on the same page for the new version.
When both hold, the new version is written into the same page and chained from the old one, and — this is the win — no new index entries are created. The indexes keep pointing at the original item, which redirects to the live version. Even better, intermediate versions in a HOT chain can be cleaned out during normal operation, including plain SELECTs, without waiting for vacuum at all.
This is why schema design touches vacuum behavior: an index on a frequently updated column doesn't just cost you index writes, it disqualifies every update of that column from being HOT. The docs' lever for encouraging HOT is fillfactor — packing a table at, say, 90% instead of 100% leaves room in each page for updated versions to land next to their predecessors.
The doomsday clock: XID wraparound
The t_xmin/t_xmax stamps are 32-bit transaction IDs, and Postgres compares them using modulo-2³² arithmetic: for any XID, two billion XIDs count as "older" and two billion as "newer" — the XID space is a circle. A row version therefore appears to be in the past for the next two billion transactions. If it still exists unprocessed after more than that, it suddenly appears to be in the future, and its data becomes invisible — data loss from the application's point of view.
The defense is freezing: VACUUM marks sufficiently old rows as frozen, meaning they're treated as inserted by FrozenTransactionId, a reserved XID that skips normal comparison rules and is always considered older than every normal XID. A frozen row is visible forever, no matter how far the counter wraps. This is why the documentation requires vacuuming every table in every database at least once every two billion transactions — and why autovacuum_freeze_max_age (default: 200 million transactions) will force an anti-wraparound vacuum on a table even if you've disabled autovacuum for it. That's not autovacuum being pushy; it's the database refusing to let you walk off a cliff.
What this means for you
- Don't turn autovacuum off. The system that seems fine without it is accumulating both bloat and wraparound risk silently.
- Watch long-running transactions. Vacuum can only remove versions no snapshot can see; one idle-in-transaction session holds cleanup back for every table.
- Design indexes with updates in mind. Every indexed column you touch in an
UPDATEforfeits HOT and multiplies write amplification. - Tune the scale factor on big, hot tables. The 20% default translates into a lot of dead tuples at scale.
An UPDATE in Postgres is an insert wearing a trench coat. Once you internalize that, vacuum stops looking like janitorial trivia and starts looking like what it is: the other half of the write path.