UPDATE writes a new version of the row. The old version stays in the page, marked with the id of the transaction that replaced it, its xmax.
An UPDATE in Postgres never changes a row. It writes a new one and leaves the old one behind. This note follows the old one.
Scroll to advance. The figure keeps running while you read.
UPDATE writes a new version of the row. The old version stays in the page, marked with the id of the transaction that replaced it, its xmax.
A transaction whose snapshot started before the update must still see the old value. Because of this, readers never block writers and writers never block readers.
Once no open snapshot can see a version, it is dead. Dead means invisible. It still takes up space in the page.
Autovacuum scans the table, finds dead versions and marks their space as free. It doesn't give the space back to the disk. New rows reuse it.
If the new version fits in the same page and no indexed column changed, Postgres skips the index entirely. A fillfactor under 100 leaves room for exactly this.
One session left open for hours holds back the oldest snapshot. Vacuum cannot remove any version newer than it, in any table of the database.
Tables and indexes grow, and scans read more pages for the same rows. Watch n_dead_tup in pg_stat_user_tables and the oldest xact_start in pg_stat_activity.