Postgres in depth · Chapter 44 of 65

Cleaning up after MVCC

By the end of this chapter you can measure the dead rows in a table, clean them with the right kind of VACUUM, tune autovacuum for a busy table, and say how close each table is to transaction id wraparound. Build it in your browser: a real editor and terminal, AI that explains, and tests that tell you when it works.

  • Part: Safety, security and storage
  • 4 tasks
  • PostgreSQL
  • In the all labs plan

The first 2 chapters of Postgres in depth are free. This one comes with the all labs plan.

Why you need this

Chapter 43 left a mess on purpose: every UPDATE and DELETE leaves an old version behind. Priya's heartbeat table holds 10,000 live rows but keeps growing, and Arjun's counts slowed down although nobody added data. Somebody has to remove the versions nobody can see any more.

That somebody is VACUUM, usually run for you by autovacuum. It has a second, quieter job too: Postgres's transaction ids are 32-bit numbers that run out, and VACUUM is what keeps old rows readable when they do.

What you build: 4 tasks

Each task has tests. You write the code in the lab's editor and press Check.

  1. Task 1

    dead-rows.sql: count the dead

    watch_events (the lesson makes it) had 20,000 rows, and a quarter were just deleted. Measure the table with the pgstattuple extension, creating the extension first if it is not there yet.

    Return one row with three columns: live_rows (the live tuples), dead_rows (the dead tuples) and dead_percent (the share of the table's bytes taken by dead tuples), all as pgstattuple reports them.

    The Check keeps an old transaction open while it deletes, so the dead rows cannot be cleaned up before your file runs.

  2. Task 2

    vacuum.sql: clean up without locking anyone out

    Clean watch_events after the delete with one command: remove every dead row, mark the pages all-visible in the visibility map, and refresh the planner's statistics for the table, all at once.

    Readers and writers must keep working while it runs: do not rewrite the table into a new file.

    The file is one command; VACUUM cannot run inside a transaction, so no BEGIN.

  3. Task 3

    autovacuum-settings.sql: vacuum lesson_progress sooner

    lesson_progress will get a million updates a day, and by default autovacuum waits until a fifth of it is dead. Change its own settings: autovacuum_vacuum_threshold 1000 and autovacuum_vacuum_scale_factor 0.02.

    Then, in the same file, return one column, dead_rows_needed: how many dead rows the table can collect before autovacuum vacuums it, that is the threshold plus the scale factor times the table's row estimate (pg_class.reltuples), rounded down.

  4. Task 4 · interview question

    Interview: wraparound.sql, the oldest unfrozen tables

    Transaction ids run out after about two billion, and every table must be frozen before then. List the five ordinary tables in schema public with the oldest unfrozen transaction ids.

    Return two columns: table_name and xid_age (how many transactions ago the table's relfrozenxid was). Order: xid_age from oldest, then table_name. Five rows.

    Careful: pg_class also lists indexes, sequences, views and TOAST tables. Keep ordinary tables in public only.

What you will learn

  • Dead tuples: how to count them with pgstattuple, and pruning.
  • VACUUM and VACUUM FULL: what each one does, and what it locks.
  • The visibility map: why VACUUM makes index-only scans work.
  • Autovacuum: when it starts, and per-table settings.
  • Freezing and wraparound: why every table must be frozen in time.

More in Safety, security and storage

Build "Cleaning up after MVCC" yourself

LCO, an edtech company, moves its platform onto a single Postgres server you run. A real editor and terminal in your browser, AI that explains, and a Check that says when it works.

The first 2 chapters of Postgres in depth are free. This one comes with the all labs plan.