Skip to content
Ameen Abozaid
Englishالعربية
MID-014

MVCC

  • 5 Lessons
  • 2 h practice (estimate)
Start module
On this page 8 topics

Overview

Topics

  • Snapshots
  • row versions
  • vacuum
  • bloat
  • visibility

Learn first

2 h of practice (estimate)

Learning outcomes

  1. Explain MVCC: row versions, snapshots, and visibility.
  2. Explain why UPDATE creates new row versions and what vacuum does.
  3. Detect table and index bloat and long-running transactions that prevent cleanup.

Lessons

  1. 01Readers don't block writers
  2. 02Row versions and snapshots
  3. 03Vacuum and autovacuum
  4. 04Bloat and long transactions
  5. 05Demo: an idle transaction blocking vacuum

Lab and exercises

Lab

MID-014-LAB1 · Observe MVCC

Outcomes: 1–3 · Time: ~2 h With two psql sessions, watch xmin/xmax/ctid change across INSERT, UPDATE, DELETE, and see what each session can see under READ COMMITTED and REPEATABLE READ. Then create bloat with an idle-in-transaction session and a hot update loop, measure it, and fix it. Acceptance: annotated session transcript covering visibility, bloat creation, detection, and the fix.

Exercises (assessment: quiz)

IDOutcomeTask
MID-014-EX011Explain in three sentences why a long read does not block a write.
MID-014-EX021Predict what session B sees in each of the two isolation levels after session A commits an update mid-transaction, then verify.
MID-014-EX032Update one row three times and show the ctid and xmin each time.
MID-014-EX042State what VACUUM does that VACUUM FULL does differently, and when each is appropriate.
MID-014-EX053Query pg_stat_user_tables to find the top three tables by dead tuples and their last autovacuum.
MID-014-EX063Find the oldest open transaction in pg_stat_activity and set idle_in_transaction_session_timeout for a role.
Show solutions
  • EX01 Readers use a snapshot of old versions; writers create new versions; no row lock is needed to read.
  • EX02 READ COMMITTED: sees the new value on the next statement; REPEATABLE READ: keeps the old value until the transaction ends.
  • EX03 ctid changes with each update (new physical location); xmin is the updating transaction id.
  • EX04 VACUUM reclaims space for reuse without blocking reads/writes; VACUUM FULL rewrites the table to return space to the OS under an exclusive lock, so use it rarely and in a window.
  • EX05 SELECT relname, n_dead_tup, last_autovacuum FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 3;
  • EX06 ORDER BY xact_start on pg_stat_activity; ALTER ROLE app SET idle_in_transaction_session_timeout = '30s'; (applies to new sessions).
  • Disabling autovacuum and open transactions in application code are demonstrated in the lab and the lecture demo.

Quiz

Outcomes: O1 MVCC and visibility · O2 row versions and vacuum · O3 bloat and long transactions.

A self-check for you: nothing is graded, saved or sent anywhere. Choose an answer, then check it. You can try again as often as you like.

  1. 1In MVCC a reader:O1
  2. 2xmin on a row version is:O1
  3. 3REPEATABLE READ takes its snapshot:O1
  4. 4An UPDATE in PostgreSQL:O2
  5. 5Plain VACUUM mainly:O2
  6. 6Autovacuum should be:O2
  7. 7The most common cause of vacuum being unable to clean:O3
  8. 8Where do you find idle transactions?O3
  9. 9A good safeguard for application mistakes:O3
  10. 10Dead tuples counted high and last_autovacuum very old suggest:O3

Practical part

Quiz is the assessment, plus the annotated session transcript from MID-014-LAB1 and exercises MID-014-EX01 to EX06. Pass: quiz 8/10 and a transcript that shows visibility changes, bloat creation, and the fix.

Common mistakes

Disabling autovacuum; leaving transactions open in application code.

Assessment

Quiz.

Career relevance

Isolation (MID-015) and database operations (MID-018).

Type to search levels, phases, modules and lessons.