Section 1 of 3 · 6 minutes

Understand row versions

Your goal

What you will be able to do

Explain why UPDATE creates a new row version and why old versions cannot always be removed immediately.

Why this matters at work

The practical reason

MVCC lets readers and writers work together, but it also creates cleanup work.

Learn

The idea in plain English

PostgreSQL uses multi-version concurrency control, or MVCC. An UPDATE creates a new row version instead of overwriting the old version in place. Different transactions may temporarily see different versions.

Once no active transaction can need an old version, vacuum can mark its space reusable. A long transaction can keep old versions visible and prevent that cleanup.

Remember these points

UPDATE usually creates a new row version.
Old versions remain while a transaction could still see them.
Vacuum reclaims reusable space inside the table; it does not usually shrink the file.

See it in SQL

Compare live and dead row estimates

Find tables accumulating old row versions.

SELECT
  schemaname,
  relname AS table_name,
  n_live_tup AS estimated_live_rows,
  n_dead_tup AS estimated_dead_rows
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;

These values are estimates, but they quickly identify tables worth checking in more detail.

Compare dead rows with table size, change rate, last vacuum time, and active long transactions before acting.

What you should see

Up to 20 user tables ordered by their estimated number of dead row versions.

Useful words

Important terms

MVCC: PostgreSQL's method for letting transactions see appropriate row versions without blocking every reader.
Dead tuple: An old row version that is no longer live but may still await cleanup.

Quick check · Not graded

Check your understanding

Why can a long transaction delay vacuum cleanup?

Choose one answerIt may still need to see older row versions.It permanently disables autovacuum on every table.It converts every table into an index.

Your progress is saved to your signed-in account.