In PostgreSQL, UPDATE does not overwrite a row.

It creates a new version of the row and marks the old one as dead.

Why?

Because PostgreSQL uses MVCC — Multi-Version Concurrency Control.

• Readers see a consistent snapshot
• Writers create new row versions
• No blocking. No dirty reads.

That design gives you high concurrency. But it creates a new problem:

Dead rows accumulate.

And that’s where VACUUM comes in.

What Actually Happens During UPDATE

UPDATE products SET price = 99 WHERE id = 1;

Internally, PostgreSQL:

1. Writes a new row version with the updated data
2. Marks the old row version as dead (sets xmax — the transaction ID that deleted it)
3. Does not touch the old row’s storage — it stays on disk

The old row isn’t gone. It’s a dead tuple, sitting in the heap, taking up space.

Every UPDATE is really an insert + a soft-delete.

Dead Tuples at Scale

If you run 1,000 updates on the same row:

• You have 1 live row
• You have 999 dead tuples on disk

Your table grows. Indexes bloat. Sequential scans get slower. This is called table bloat.

What VACUUM Does

VACUUM scans the table and reclaims space from dead tuples.

VACUUM products;

What it does:

• Identifies dead tuples that no active transaction can still see
• Marks their storage as reusable (doesn’t shrink the file, but marks space free)
• Updates the visibility map so index-only scans work correctly
• Updates pg_class.reltuples for the planner

VACUUM FULL actually rewrites the table file and returns space to the OS — but it locks the table while doing so.

Autovacuum

PostgreSQL runs autovacuum in the background. It triggers based on:

autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor * reltuples

Default: vacuum when dead tuples exceed 50 + 20% of table size.

For high-write tables, the defaults are often too conservative. Tune them per-table:

ALTER TABLE orders SET (
  autovacuum_vacuum_scale_factor = 0.01,  -- trigger at 1% dead tuples
  autovacuum_vacuum_threshold = 100
);

The Takeaway

PostgreSQL’s MVCC is what makes concurrent reads and writes fast and consistent. But it means:

• Every UPDATE leaves a dead tuple
• VACUUM is not optional — it’s how the engine breathes
• On high-write tables, tune autovacuum aggressively or you’ll hit bloat