Lab 9: Watch row versions accumulate on a real heap page

Objective

Almost every PostgreSQL operational problem that is not a lock problem comes back to one fact: an UPDATE does not modify a row. It writes a new version and marks the old one, and the old one stays on disk until something removes it.

You can be told that. This lab lets you look at it. Using pageinspect you will read the actual line pointers and tuple headers of a heap page while you update and delete rows, and watch the page fill with versions that no query can see.

You will finish able to explain, from evidence, why a table grows when you only ever update it, why a DELETE frees nothing, why some updates are much cheaper than others, and why one forgotten session can stop cleanup across an entire table.

Architecture

One small table on one 8 kB page, with autovacuum disabled so nothing changes behind your back, inspected from two directions.

flowchart TD
    T["widgets\nautovacuum_enabled = off"] --> Q["ordinary SQL\nctid, xmin, xmax"]
    T --> P["pageinspect\nheap_page_items(get_raw_page(...))"]
    P --> LP["line pointers\nlp, lp_off, lp_flags"]
    P --> TH["tuple headers\nt_xmin, t_xmax, t_ctid, infomask"]
    Q --> V["only versions visible\nto your snapshot"]
    P --> A["every version on the page,\nvisible or not"]

Requirements

A PostgreSQL 18 cluster with superuser access. pageinspect functions are superuser-only by default.
The pageinspect extension. On Debian it is in postgresql-contrib-18, which the postgresql-18 package pulls in.
The lab creates and drops a database called lab09.

Scenario

A table holding a few thousand rows that are updated constantly has grown to several gigabytes. Nobody has inserted anything into it for months. Somebody asks how a table with a fixed row count can grow.

The answer is on the page.

Tasks

Task 1 — Set up a table nothing will clean up behind you

LAB="$HOME/rbpg-lab-09"
mkdir -p "$LAB"

docker exec -i -u postgres rbpg-lab01 psql -X -c "CREATE DATABASE lab09;"
docker exec -i -u postgres rbpg-lab01 psql -X -d lab09 <<'SQL'
CREATE EXTENSION pageinspect;
CREATE TABLE widgets(id int PRIMARY KEY, name text, qty int)
  WITH (autovacuum_enabled = off, fillfactor = 100);
INSERT INTO widgets VALUES (1,'bolt',10),(2,'nut',20),(3,'washer',30);
SQL

docker exec -u postgres rbpg-lab01 psql -X -d lab09 -c "
  SELECT ctid, xmin, xmax, id, name, qty FROM widgets ORDER BY id;" \
  | tee "$LAB/row-versions.txt"

Read-only / Safethe system columns every row carries— Captured from the lab run of 2026-08-28. All three rows were inserted by the same transaction, 824.

$ psql -X -d lab09 -c "SELECT ctid, xmin, xmax, id, name, qty FROM widgets ORDER BY id;"

 ctid  | xmin | xmax | id |  name  | qty 
-------+------+------+----+--------+-----
(0,1) |  824 |    0 |  1 | bolt   |  10
(0,2) |  824 |    0 |  2 | nut    |  20
(0,3) |  824 |    0 |  3 | washer |  30
(3 rows)

Three hidden columns, present on every table:

ctid — the physical address, as (block, line pointer). All three rows are on block 0, at line pointers 1, 2 and 3.
xmin — the transaction that created this version. All 824, the INSERT.
xmax — the transaction that deleted or superseded it. Zero means no transaction has, so this version is current.

autovacuum_enabled = off and fillfactor = 100 are for the lab only. The first stops the cleanup you are trying to observe; the second packs the page fully so you can see space pressure sooner. Neither belongs on a production table.

Task 2 — Update a row and look for the old version

docker exec -u postgres rbpg-lab01 psql -X -d lab09 -c "UPDATE widgets SET qty = 11 WHERE id = 1;"
docker exec -u postgres rbpg-lab01 psql -X -d lab09 -c "
  SELECT ctid, xmin, xmax, id, qty FROM widgets ORDER BY id;" | tee -a "$LAB/row-versions.txt"

Read-only / Safethe row moved— Captured from the lab run of 2026-08-28. Row 1 is now at (0,4), not (0,1).

$ psql -X -d lab09 -c "SELECT ctid, xmin, xmax, id, qty FROM widgets ORDER BY id;"

 ctid  | xmin | xmax | id | qty 
-------+------+------+----+-----
(0,4) |  825 |    0 |  1 |  11
(0,2) |  824 |    0 |  2 |  20
(0,3) |  824 |    0 |  3 |  30
(3 rows)

Row 1 is at (0,4) with xmin = 825. It did not change in place — a new version was written at a new address by a new transaction.

An ordinary query cannot show you what happened to the old one, because an ordinary query only sees versions its snapshot can see. Look at the page itself:

docker exec -u postgres rbpg-lab01 psql -X -d lab09 -c "
  SELECT lp, lp_off, lp_flags, t_xmin, t_xmax, t_ctid
  FROM heap_page_items(get_raw_page('widgets',0)) ORDER BY lp;" \
  | tee -a "$LAB/row-versions.txt"

Read-only / Safefour tuples on a page holding three rows— Captured from the lab run of 2026-08-28.

$ psql -X -d lab09 -c "SELECT lp, lp_off, lp_flags, t_xmin, t_xmax, t_ctid FROM heap_page_items(get_raw_page('widgets',0)) ORDER BY lp;"

 lp | lp_off | lp_flags | t_xmin | t_xmax | t_ctid 
----+--------+----------+--------+--------+--------
1 |   8152 |        1 |    824 |    825 | (0,4)
2 |   8112 |        1 |    824 |      0 | (0,2)
3 |   8072 |        1 |    824 |      0 | (0,3)
4 |   8032 |        1 |    825 |      0 | (0,4)
(4 rows)

Line pointer 1 is the old version. It is still there, occupying its 40 bytes. Two things changed about it:

t_xmax is now 825, the updating transaction. That is what makes it invisible to any snapshot taken after 825 committed.
t_ctid now points to (0,4) instead of to itself. The old version carries a forward pointer to the new one.

That forward pointer is what lets a transaction that is already waiting on this row find the version it should now be looking at.

Task 3 — Decode the flags rather than guessing

The tuple header carries two bitmask fields. Do not decode them by hand; pageinspect will do it:

docker exec -u postgres rbpg-lab01 psql -X -d lab09 -c "
  SELECT lp, t_xmin, t_xmax, t_ctid,
         (heap_tuple_infomask_flags(t_infomask, t_infomask2)).raw_flags
  FROM heap_page_items(get_raw_page('widgets',0)) ORDER BY lp;" \
  | tee "$LAB/hot-vs-nonhot.txt"

Read-only / Safethe flags that describe what each tuple is— Captured from the lab run of 2026-08-28.

$ psql -X -d lab09 -c "SELECT lp, t_xmin, t_xmax, t_ctid, (heap_tuple_infomask_flags(t_infomask, t_infomask2)).raw_flags FROM heap_page_items(get_raw_page('widgets',0)) ORDER BY lp;"

 lp | t_xmin | t_xmax | t_ctid |                                       raw_flags                                       
----+--------+--------+--------+---------------------------------------------------------------------------------------
1 |    824 |    825 | (0,4)  | {HEAP_HASVARWIDTH,HEAP_XMIN_COMMITTED,HEAP_XMAX_COMMITTED,HEAP_HOT_UPDATED}
2 |    824 |      0 | (0,2)  | {HEAP_HASVARWIDTH,HEAP_XMIN_COMMITTED,HEAP_XMAX_INVALID}
3 |    824 |      0 | (0,3)  | {HEAP_HASVARWIDTH,HEAP_XMIN_COMMITTED,HEAP_XMAX_INVALID}
4 |    825 |      0 | (0,4)  | {HEAP_HASVARWIDTH,HEAP_XMIN_COMMITTED,HEAP_XMAX_INVALID,HEAP_UPDATED,HEAP_ONLY_TUPLE}
(4 rows)

Two flags matter here:

HEAP_HOT_UPDATED on line pointer 1 — the old version was superseded by a heap-only update.
HEAP_ONLY_TUPLE on line pointer 4 — the new version has no index entry of its own.

That is a HOT update, and it is much cheaper than the alternative, because no index had to be touched.

HEAP_XMIN_COMMITTED and HEAP_XMAX_INVALID are visibility hint bits: cached conclusions about whether the creating and deleting transactions committed, so that later readers do not have to consult the commit log again. They are set lazily, by whichever query happens to look first.

Task 4 — Break HOT on purpose

HOT is possible only when no indexed column changed. Add an index on name, then update name:

docker exec -u postgres rbpg-lab01 psql -X -d lab09 -c "CREATE INDEX widgets_name_idx ON widgets(name);"
docker exec -u postgres rbpg-lab01 psql -X -d lab09 -c "UPDATE widgets SET name = 'nut-v2' WHERE id = 2;"

docker exec -u postgres rbpg-lab01 psql -X -d lab09 -c "
  SELECT lp, t_xmin, t_xmax, t_ctid,
         (heap_tuple_infomask_flags(t_infomask, t_infomask2)).raw_flags
  FROM heap_page_items(get_raw_page('widgets',0)) ORDER BY lp;" \
  | tee -a "$LAB/hot-vs-nonhot.txt"

Read-only / Safeline pointer 2 is superseded with no HOT flag anywhere— Captured from the lab run of 2026-08-28. Compare lp 2 and lp 5 with lp 1 and lp 4.

$ the same flag query after updating an indexed column

 lp | t_xmin | t_xmax | t_ctid |                                       raw_flags                                       
----+--------+--------+--------+---------------------------------------------------------------------------------------
1 |    824 |    825 | (0,4)  | {HEAP_HASVARWIDTH,HEAP_XMIN_COMMITTED,HEAP_XMAX_COMMITTED,HEAP_HOT_UPDATED}
2 |    824 |    827 | (0,5)  | {HEAP_HASVARWIDTH,HEAP_XMIN_COMMITTED}
3 |    824 |      0 | (0,3)  | {HEAP_HASVARWIDTH,HEAP_XMIN_COMMITTED,HEAP_XMAX_INVALID}
4 |    825 |      0 | (0,4)  | {HEAP_HASVARWIDTH,HEAP_XMIN_COMMITTED,HEAP_XMAX_INVALID,HEAP_UPDATED,HEAP_ONLY_TUPLE}
5 |    827 |      0 | (0,5)  | {HEAP_HASVARWIDTH,HEAP_XMAX_INVALID,HEAP_UPDATED}
(5 rows)

Line pointer 2 has t_xmax = 827 and t_ctid = (0,5) — superseded, just like line pointer 1 was — but no HEAP_HOT_UPDATED. And line pointer 5, the new version, has HEAP_UPDATED but no HEAP_ONLY_TUPLE.

This update had to insert an entry into widgets_name_idx, and into widgets_pkey as well, because a non-HOT new version needs index entries pointing at its new address in every index on the table.

Now update a non-indexed column on the same row and watch HOT come back:

docker exec -u postgres rbpg-lab01 psql -X -d lab09 -c "UPDATE widgets SET qty = 21 WHERE id = 2;"
docker exec -u postgres rbpg-lab01 psql -X -d lab09 -c "
  SELECT relname, n_tup_ins, n_tup_upd, n_tup_hot_upd, n_tup_newpage_upd, n_dead_tup
  FROM pg_stat_user_tables WHERE relname='widgets';" | tee -a "$LAB/hot-vs-nonhot.txt"

Read-only / Safethe counters match the page exactly— Captured from the lab run of 2026-08-28. Three updates, two of them HOT.

$ psql -X -d lab09 -c "SELECT relname, n_tup_ins, n_tup_upd, n_tup_hot_upd, n_tup_newpage_upd, n_dead_tup FROM pg_stat_user_tables WHERE relname='widgets';"

 relname | n_tup_ins | n_tup_upd | n_tup_hot_upd | n_tup_newpage_upd | n_dead_tup 
---------+-----------+-----------+---------------+-------------------+------------
widgets |         3 |         3 |             2 |                 0 |          3
(1 row)

Three updates: qty (HOT), name (not HOT), qty again (HOT). n_tup_hot_upd = 2. The counters and the page agree.

The HOT ratio is a tuning signal you can act on

n_tup_hot_upd / n_tup_upd is one of the most useful ratios on a busy table. A high ratio means updates are cheap: one page write, no index maintenance, and cleanup that can happen on the page itself without a full vacuum cycle. A low ratio means every update is also several index writes.

Two things move it, and both are within your control:

Which columns are indexed. An index on a frequently-updated column disables HOT for every update that touches it. An index that exists for one quarterly report can quietly triple the write cost of the hottest table in the system.

fillfactor. HOT requires the new version to fit on the same page. This lab set fillfactor = 100, which leaves no room. On an update-heavy table, a fillfactor of 85 or 90 reserves space for exactly this, and n_tup_newpage_upd — zero here — counts the updates that had to move to a new page because there was not enough room.

Task 5 — Delete a row and watch nothing be freed

docker exec -u postgres rbpg-lab01 psql -X -d lab09 -c "DELETE FROM widgets WHERE id = 3;"
docker exec -u postgres rbpg-lab01 psql -X -d lab09 -c "SELECT count(*) AS visible_rows FROM widgets;"
docker exec -u postgres rbpg-lab01 psql -X -d lab09 -c "
  SELECT lp, lp_flags, t_xmin, t_xmax, t_ctid
  FROM heap_page_items(get_raw_page('widgets',0)) ORDER BY lp;"

Read-only / Safetwo rows visible, six tuples on the page— Captured from the lab run of 2026-08-28. Line pointer 3 has an xmax and still points at itself.

$ a DELETE, then a count, then the raw page

 visible_rows 
--------------
          2
(1 row)

lp | lp_flags | t_xmin | t_xmax | t_ctid 
----+----------+--------+--------+--------
1 |        1 |    824 |    825 | (0,4)
2 |        1 |    824 |    827 | (0,5)
3 |        1 |    824 |    829 | (0,3)
4 |        1 |    825 |      0 | (0,4)
5 |        1 |    827 |    828 | (0,6)
6 |        1 |    828 |      0 | (0,6)
(6 rows)

The DELETE set t_xmax = 829 on line pointer 3 and did nothing else. t_ctid still points at itself, because there is no successor version — that is how a delete is distinguished from an update on the page.

Six tuples for two visible rows, and not one byte has been freed. A DELETE in PostgreSQL is a very small write that marks a row invisible. Reclaiming its space is a separate job.

Task 6 — VACUUM, and what it leaves behind

docker exec -u postgres rbpg-lab01 psql -X -d lab09 -c "VACUUM (VERBOSE) widgets;" \
  | tee "$LAB/after-vacuum.txt"

Configuration changefour tuples removed, two remain— Captured from the lab run of 2026-08-28, condensed to the informative lines.

$ psql -X -d lab09 -c "VACUUM (VERBOSE) widgets;"

INFO:  vacuuming "lab09.public.widgets"
INFO:  finished vacuuming "lab09.public.widgets": index scans: 1
pages: 0 removed, 1 remain, 1 scanned (100.00% of total), 0 eagerly scanned
tuples: 4 removed, 2 remain, 0 are dead but not yet removable
removable cutoff: 830, which was 0 XIDs old when operation ended
new relfrozenxid: 825, which is 2 XIDs ahead of previous value
visibility map: 1 pages set all-visible, 0 pages set all-frozen (0 were all-visible)
index scan needed: 1 pages from table (100.00% of total) had 2 dead item identifiers removed
index "widgets_pkey": pages: 2 in total, 0 newly deleted, 0 currently deleted, 0 reusable
index "widgets_name_idx": pages: 2 in total, 0 newly deleted, 0 currently deleted, 0 reusable
WAL usage: 11 records, 4 full page images, 34052 bytes, 0 buffers full

docker exec -u postgres rbpg-lab01 psql -X -d lab09 -c "
  SELECT lp, lp_off, lp_flags, t_xmin, t_xmax, t_ctid
  FROM heap_page_items(get_raw_page('widgets',0)) ORDER BY lp;" \
  | tee -a "$LAB/after-vacuum.txt"

Read-only / Safethree different kinds of line pointer— Captured from the lab run of 2026-08-28. Note the lp_off values for the flags-2 rows.

$ the raw page after VACUUM

 lp | lp_off | lp_flags | t_xmin | t_xmax | t_ctid 
----+--------+----------+--------+--------+--------
1 |      4 |        2 |        |        | 
2 |      0 |        0 |        |        | 
3 |      0 |        0 |        |        | 
4 |   8152 |        1 |    825 |      0 | (0,4)
5 |      6 |        2 |        |        | 
6 |   8112 |        1 |    828 |      0 | (0,6)
(6 rows)

Per the pageinspect documentation, lp_flags values are 0 = LP_UNUSED, 1 = LP_NORMAL, 2 = LP_REDIRECT, 3 = LP_DEAD. So this page now holds:

Line pointers 2 and 3: LP_UNUSED. Fully reclaimed. Their space is available for new tuples.
Line pointers 4 and 6: LP_NORMAL. The two live rows. Note that lp_off changed — the page was compacted and the live tuples moved.
Line pointers 1 and 5: LP_REDIRECT. Not tuples at all. Their lp_off field holds a line pointer number — 4 and 6 respectively — rather than a byte offset.

The redirects are the HOT mechanism completing. Confirm it:

docker exec -u postgres rbpg-lab01 psql -X -d lab09 -c "
  SELECT itemoffset, ctid, data FROM bt_page_items('widgets_pkey', 1) ORDER BY itemoffset;"

docker exec -u postgres rbpg-lab01 psql -X -d lab09 -c "
  SET enable_seqscan = off;
  EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT qty FROM widgets WHERE id = 1;"

Read-only / Safethe index still points at (0,1), and the lookup still works— Captured from the lab run of 2026-08-28.

$ read the primary key index page, then force an index scan

 itemoffset | ctid  |          data           
------------+-------+-------------------------
        1 | (0,1) | 01 00 00 00 00 00 00 00
        2 | (0,5) | 02 00 00 00 00 00 00 00
(2 rows)

                           QUERY PLAN                              
---------------------------------------------------------------------
Index Scan using widgets_pkey on widgets (actual rows=1.00 loops=1)
 Index Cond: (id = 1)
 Index Searches: 1
 Buffers: shared hit=2
(4 rows)

The index entry for id = 1 still points at (0,1), which is now a redirect, not a tuple. The scan follows it to line pointer 4 and returns the row.

Why the redirect has to exist

Index entries are not updated when a HOT chain is cleaned up. Rewriting them would mean finding every index entry pointing into the chain, which is exactly the index work HOT exists to avoid.

So vacuum cannot simply free line pointer 1: the index would then point at an unused slot, and a future tuple placed there would be returned for the wrong key. Instead it converts the line pointer into a redirect, which costs four bytes and keeps every existing index entry correct.

The tuple’s 40 bytes are reclaimed; only the pointer survives. The chain collapses to one hop, and a later non-HOT update or a REINDEX is what eventually retires the redirect.

This is also why LP_DEAD exists as a separate state: an index scan that finds a dead tuple can mark the line pointer dead immediately, without the full vacuum pass, so the next scan skips it.

Task 7 — One open snapshot stops all of it

Everything above assumed vacuum was allowed to remove the dead versions. It is not always allowed.

# Hold a snapshot open in another session.
docker exec -d rbpg-lab01 bash -c "su - postgres -c \"psql -X -d lab09 <<'SQL'
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT count(*) FROM widgets;
\\\\! sleep 120
SQL\""
sleep 3

docker exec -u postgres rbpg-lab01 psql -X -d lab09 -c "UPDATE widgets SET qty = qty + 1;"
docker exec -u postgres rbpg-lab01 psql -X -d lab09 -c "UPDATE widgets SET qty = qty + 1;"

docker exec -u postgres rbpg-lab01 psql -X -d lab09 -c "VACUUM (VERBOSE) widgets;" 2>&1 \
  | grep -E "tuples:|removable cutoff" | tee "$LAB/unremovable.txt"

docker exec -u postgres rbpg-lab01 psql -X -d lab09 -c "
  SELECT pid, state, backend_xmin, age(backend_xmin) AS xmin_age
  FROM pg_stat_activity WHERE datname='lab09' AND backend_xmin IS NOT NULL;" \
  | tee -a "$LAB/unremovable.txt"

Service impact possiblefour dead tuples, none removed— Captured from the lab run of 2026-08-28. The second tuples line is the TOAST table.

$ VACUUM VERBOSE while a repeatable read snapshot is held, then find the holder

tuples: 0 removed, 6 remain, 4 are dead but not yet removable
removable cutoff: 830, which was 2 XIDs old when operation ended
tuples: 0 removed, 0 remain, 0 are dead but not yet removable

pid  |        state        | backend_xmin | xmin_age 
-------+---------------------+--------------+----------
10420 | idle in transaction |          830 |        2
10450 | active              |          832 |        0
(2 rows)

“0 removed, 6 remain, 4 are dead but not yet removable” is the single most useful line VACUUM VERBOSE produces. Vacuum ran, did its work correctly, and freed nothing — not because it failed, but because it is not permitted to remove versions that an existing snapshot might still need to see.

removable cutoff: 830 is the horizon, and the session at backend_xmin = 830 is the reason for it. It is idle in transaction — the state Labs 6 and 7 both pointed at.

docker exec -u postgres rbpg-lab01 psql -X -c "
  SELECT pg_terminate_backend(pid) FROM pg_stat_activity
  WHERE datname='lab09' AND backend_xmin IS NOT NULL AND state = 'idle in transaction';"
sleep 2
docker exec -u postgres rbpg-lab01 psql -X -d lab09 -c "VACUUM (VERBOSE) widgets;" 2>&1 \
  | grep -E "tuples:|removable cutoff" | tee -a "$LAB/unremovable.txt"

Destructivethe same vacuum, after the snapshot is released— Captured from the lab run of 2026-08-28. The cutoff moved from 830 to 832 and the tuples went.

$ terminate the snapshot holder, then vacuum again

tuples: 4 removed, 2 remain, 0 are dead but not yet removable
removable cutoff: 832, which was 0 XIDs old when operation ended

Nothing about the table changed. Only the horizon moved, and the same vacuum that removed nothing now removes everything.

Validation

test -s "$LAB/row-versions.txt"   && echo "OK row-versions"
test -s "$LAB/hot-vs-nonhot.txt"  && echo "OK hot-vs-nonhot"
test -s "$LAB/after-vacuum.txt"   && echo "OK after-vacuum"
test -s "$LAB/unremovable.txt"    && echo "OK unremovable"

grep -q "HEAP_HOT_UPDATED"  "$LAB/hot-vs-nonhot.txt" && echo "OK HOT update captured"
grep -q "HEAP_ONLY_TUPLE"   "$LAB/hot-vs-nonhot.txt" && echo "OK heap-only tuple captured"
grep -q "dead but not yet removable" "$LAB/unremovable.txt" && echo "OK horizon demonstrated"

Questions to answer without looking anything up:

A table’s row count has not changed in six months and it has tripled in size. What happened, and which column would you check first?
What are the two differences between the old version of an updated row and the old version of a deleted row, on the page?
Which flag tells you an update was HOT, and what did the server avoid doing because of it?
After VACUUM, an index entry points at a line pointer with lp_flags = 2. Why was that line pointer not simply freed?
VACUUM VERBOSE says “0 removed, 40000 are dead but not yet removable”. Is autovacuum misconfigured?

Expected Outcome

You have watched row versions accumulate on a real page, distinguished HOT from non-HOT updates by their flags rather than by description, seen a DELETE free nothing, and watched vacuum convert dead tuples into redirects and free space.

The three durable conclusions:

An UPDATE is an insert plus a mark. A DELETE is only a mark. Neither frees space.
HOT avoids index writes and is controlled by which columns are indexed and by fillfactor. n_tup_hot_upd / n_tup_upd tells you how you are doing.
Vacuum removes nothing that any live snapshot might need. When it reports dead-but-not-removable rows, the fix is upstream in pg_stat_activity, pg_replication_slots or pg_prepared_xacts — not in autovacuum settings.

Troubleshooting

ERROR: extension "pageinspect" does not exist. It ships in postgresql-contrib on Debian packaging and is present in the official image. Install the package, then CREATE EXTENSION pageinspect; as a superuser.

heap_page_items returns nothing. You asked for a page number the relation does not have. Start at page 0, and check the size with SELECT pg_relation_size('t') / 8192.

The old row version is not on the page you expect. A new version is written wherever there is room, which may be a later page — that is the whole reason fillfactor matters. Scan the pages the relation has rather than assuming page 0.

Autovacuum removed your evidence between two commands. Task 1 exists to stop this: the table must have autovacuum disabled with ALTER TABLE ... SET (autovacuum_enabled = off) before any of the observations, or a worker will tidy up mid-lab.

Every update looks HOT and Task 4 will not break it. The updated column must be one an index covers. Confirm with \d t that the index you think exists actually does, and that it includes the column you are updating.

VACUUM reports rows that are dead but not removable. Something holds an older snapshot: an open transaction, a replication slot, or a prepared transaction. That is Task 7, and no autovacuum setting changes it — the three places to look are pg_stat_activity, pg_replication_slots and pg_prepared_xacts.

Cleanup

docker exec -u postgres rbpg-lab01 psql -X -c "
  SELECT pg_terminate_backend(pid) FROM pg_stat_activity
  WHERE datname='lab09' AND pid <> pg_backend_pid();"
docker exec -u postgres rbpg-lab01 psql -X -c "DROP DATABASE IF EXISTS lab09;"

Production notes

n_tup_hot_upd / n_tup_upd is the single most useful table-level health number after dead tuples. A low ratio on a high-update table means every write is also writing every index, and the usual causes are an index on a frequently-updated column and a fillfactor of 100.
DELETE frees nothing. Estates that “archive by deleting” and then wonder why the volume never shrinks are meeting exactly the behaviour in Task 5. Partition dropping is the mechanism that returns space.
Never disable autovacuum on a production table to make a measurement reproducible. This lab does it because the container is disposable; on a real table the disabled setting outlives the person who set it.
Before tuning autovacuum, check whether anything is holding a snapshot. Tuning the workers harder when a replication slot is pinning the horizon achieves nothing at all and costs I/O.

What You Learned

An UPDATE is an insert plus a mark, and a DELETE is only a mark. Neither returns space to the filesystem.
You can see the row versions. heap_page_items shows them on the page, and the infomask flags say which is which — no inference required.
HOT avoids index writes, and whether an update qualifies is decided by which columns are indexed and whether the page has room.
Vacuum converts dead tuples into redirects and free space inside the file, which is reusable but not returned.
A single open snapshot stops all of it. Vacuum cannot remove anything a live snapshot might still need, no matter how it is tuned.
The fix for “dead but not removable” is upstream, in the session, slot or prepared transaction holding the horizon.

Objectives

Prerequisites