Skip to content
Hands-on IT IT Drills

Day 6: VACUUM, Autovacuum and Bloat

Goal: You create dead row versions, measure them, clean them up with VACUUM, VACUUM FULL and pg_repack, tune autovacuum per table and recognize when a long transaction or transaction ID wraparound is looming. · Duration: approx. 75 min · Prerequisite: Day 4

Briefing

  • Every UPDATE and DELETE leaves dead row versions behind (Day 4). VACUUM frees their space for reuse – the file doesn’t shrink in the process.
  • VACUUM FULL rewrites the table and returns space to the file system, but locks it completely.
  • Autovacuum does this in the background as soon as enough rows are dead. It can only remove what no open transaction could still see.
  • Transaction IDs are 32 bits. VACUUM freezes old rows so the counter may wrap around after roughly 2 billion.
 UPDATE      VACUUM                VACUUM FULL / pg_repack
 [old|new] -> [free|new] -> INSERT -> [new]  (smaller file)
 file 2x      file 2x, space reusable

Everything on pg1, database tag06.

Drill 1: Create and count dead tuples

We turn off autovacuum for the test table so it doesn’t interfere – that’s already the first table parameter:

createdb tag06
psql -d tag06 <<'EOF'
CREATE TABLE lager (id int PRIMARY KEY, bestand int NOT NULL, info text)
  WITH (autovacuum_enabled = off);
INSERT INTO lager SELECT i, 100, repeat('x', 100) FROM generate_series(1, 1000000) i;
VACUUM ANALYZE lager;
SELECT pg_size_pretty(pg_table_size('lager')) AS groesse,
  pg_size_pretty(pg_relation_size('lager_pkey')) AS index;
UPDATE lager SET bestand = bestand - 1;
EOF
psql -d tag06 <<'EOF'
SELECT n_live_tup, n_dead_tup, last_autovacuum
FROM pg_stat_user_tables WHERE relname = 'lager';
SELECT pg_size_pretty(pg_table_size('lager')) AS groesse;
EOF

Expected (abridged):

INSERT 0 1000000
VACUUM
 groesse | index
---------+-------
 135 MB  | 21 MB
UPDATE 1000000
 n_live_tup | n_dead_tup | last_autovacuum
------------+------------+-----------------
    1000000 |    1000000 |
 groesse
---------
 270 MB

One million rows changed, one million dead versions, the table twice as large. n_dead_tup is a statistics counter, not a measurement – fast but imprecise, and back at zero after a crash.

Save your progress? Sign up for free

Drill 2: Measure precisely with pgstattuple

The extension pgstattuple is part of the postgresql-18 package and reads the table page by page:

psql -d tag06 <<'EOF'
CREATE EXTENSION pgstattuple;
\x
SELECT * FROM pgstattuple('lager');
\x
SELECT avg_leaf_density, leaf_fragmentation FROM pgstatindex('lager_pkey');
SELECT pg_size_pretty(pg_relation_size('lager_pkey')) AS index;
EOF

Expected:

CREATE EXTENSION
Expanded display is on.
-[ RECORD 1 ]------+----------
table_len          | 282484736
tuple_count        | 1000000
tuple_len          | 133000000
tuple_percent      | 47.08
dead_tuple_count   | 1000000
dead_tuple_len     | 133000000
dead_tuple_percent | 47.08
free_space         | 1657144
free_percent       | 0.59

Expanded display is off.
 avg_leaf_density | leaf_fragmentation
------------------+--------------------
            83.85 |              49.99
(1 row)

 index
-------
 43 MB
(1 row)

Almost half of the file is dead rows. The index has grown too (previously 21 MB, Drill 1): the pages were full, the new version landed on a different page and needed its own index entry. pgstattuple reads everything – on large tables in production, prefer pgstattuple_approx(), which takes shortcuts via the visibility map.

Save your progress? Sign up for free

Drill 3: VACUUM frees space – but doesn’t give it back

psql -d tag06 -c 'VACUUM (VERBOSE) lager' 2>&1 | sed -n '1,8p'
psql -d tag06 <<'EOF'
SELECT n_live_tup, n_dead_tup FROM pg_stat_user_tables WHERE relname = 'lager';
SELECT pg_size_pretty(pg_table_size('lager')) AS groesse;
SELECT tuple_percent, dead_tuple_percent, free_percent FROM pgstattuple('lager');
UPDATE lager SET bestand = bestand - 1;
SELECT pg_size_pretty(pg_table_size('lager')) AS groesse_nach_update;
EOF

Expected (abridged):

INFO:  vacuuming "tag06.public.lager"
INFO:  finished vacuuming "tag06.public.lager": index scans: 1
pages: 0 removed, 34483 remain, 34483 scanned (100.00% of total), 0 eagerly scanned
tuples: 1000000 removed, 1000000 remain, 0 are dead but not yet removable
removable cutoff: 67055, which was 0 XIDs old when operation ended
new relfrozenxid: 67045, which is 28 XIDs ahead of previous value
frozen: 0 pages from table (0.00% of total) had 0 tuples frozen
visibility map: 34483 pages set all-visible, 17241 pages set all-frozen ...
 n_live_tup | n_dead_tup
------------+------------
    1000000 |          0
 groesse
---------
 270 MB
 tuple_percent | dead_tuple_percent | free_percent
---------------+--------------------+--------------
         47.08 |                  0 |        50.12
UPDATE 1000000
 groesse_nach_update
---------------------
 270 MB

The dead rows are gone, the file stays at 270 MB – half of it free space. The next UPDATE of all rows fits right in, the table doesn’t grow. That’s normal operation: a table with regular updates settles at a size with some headroom. VACUUM also sets the visibility map (Index Only Scans, Day 5) and freezes rows (Drill 7). It runs alongside readers and writers.

Save your progress? Sign up for free

Drill 4: VACUUM FULL versus pg_repack

If the headroom is permanently too large – for example after a big DELETE – the file has to be rewritten:

psql -d tag06 -c 'VACUUM lager' -c "SELECT pg_size_pretty(pg_table_size('lager'))"
psql -d tag06 -c 'VACUUM (FULL, VERBOSE) lager' &
sleep 0.7
psql -d tag06 -c "SELECT l.mode, l.granted, left(a.query, 30) AS query
  FROM pg_locks l JOIN pg_stat_activity a USING (pid)
  WHERE l.relation = 'lager'::regclass"
wait
psql -d tag06 -c "SELECT pg_size_pretty(pg_table_size('lager')) AS nachher,
  pg_size_pretty(pg_relation_size('lager_pkey')) AS index"

Expected (abridged):

 pg_size_pretty
----------------
 270 MB
INFO:  vacuuming "public.lager"
        mode         | granted |            query
---------------------+---------+------------------------------
 AccessExclusiveLock | t       | VACUUM (FULL, VERBOSE) lager
INFO:  "public.lager": found 0 removable, 1000000 nonremovable row versions in 34483
pages
VACUUM
 nachher | index
---------+-------
 135 MB  | 21 MB

AccessExclusiveLock: while VACUUM FULL runs, nobody can even read the table. At 135 MB that’s seconds, at 500 GB hours. It also needs space for a complete copy.

The alternative pg_repack builds the copy while the system is running and locks only briefly at the start and end. It is in the PGDG repository:

sudo apt-get install -y postgresql-18-repack
psql -d tag06 -c 'UPDATE lager SET bestand = bestand - 1' -c 'VACUUM lager' \
  -c "SELECT pg_size_pretty(pg_table_size('lager')) AS vorher"
psql -d tag06 -c 'CREATE EXTENSION pg_repack'
pg_repack --version
pg_repack -d tag06 -t lager
psql -d tag06 -c "SELECT pg_size_pretty(pg_table_size('lager')) AS nachher"

Expected (abridged):

UPDATE 1000000
VACUUM
 vorher
--------
 270 MB
CREATE EXTENSION
pg_repack 1.5.3
INFO: repacking table "public.lager"
 nachher
---------
 135 MB

pg_repack needs a primary key (or unique index), the extension in the database and superuser privileges. It records changes during the copy via a trigger and replays them at the end.

Save your progress? Sign up for free

Drill 5: Tune autovacuum per table

Autovacuum starts on a table when the dead rows exceed this threshold:

autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor * rows
            50              +             0.2               * rows

With one million rows, that’s only from 200,050 dead ones – with one billion rows, 200 million. Since PostgreSQL 18, autovacuum_vacuum_max_threshold (default 100 million) caps the threshold. For large, hot tables, you set the values per table:

psql -d tag06 <<'EOF'
ALTER TABLE lager SET (autovacuum_enabled = on,
  autovacuum_vacuum_scale_factor = 0.01, autovacuum_vacuum_threshold = 1000);
SELECT unnest(reloptions) AS option FROM pg_class WHERE relname = 'lager';
UPDATE lager SET bestand = bestand - 1 WHERE id <= 20000;
EOF
psql -d tag06 -c "SELECT n_live_tup, n_dead_tup,
  round(1000 + 0.01 * n_live_tup) AS schwelle, last_autovacuum, autovacuum_count
  FROM pg_stat_user_tables WHERE relname = 'lager'"

Expected (abridged):

               option
-------------------------------------
 autovacuum_enabled=on
 autovacuum_vacuum_scale_factor=0.01
 autovacuum_vacuum_threshold=1000
UPDATE 20000
 n_live_tup | n_dead_tup | schwelle | last_autovacuum | autovacuum_count
------------+------------+----------+-----------------+------------------
    1000000 |      20000 |    11000 |                 |                0

20,000 dead rows, above the threshold of 11,000. The autovacuum launcher checks every autovacuum_naptime (60 s). Wait a minute, then:

psql -d tag06 -c "SELECT n_dead_tup, last_autovacuum, autovacuum_count
  FROM pg_stat_user_tables WHERE relname = 'lager'"

Expected:

 n_dead_tup |        last_autovacuum        | autovacuum_count
------------+-------------------------------+------------------
          0 | 2026-11-19 05:18:38.627177+00 |                1
(1 row)

It was done after 41 seconds. Autovacuum writes to the log only for runs longer than log_autovacuum_min_duration (default 10 min) – in production, usually set it lower. Running workers show up in pg_stat_activity (backend_type = 'autovacuum worker') and pg_stat_progress_vacuum.

Save your progress? Sign up for free

Drill 6: A long transaction blocks VACUUM

Two terminals. Session A (psql -d tag06):

BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT count(*) FROM lager;

In the second terminal:

psql -d tag06 -c 'UPDATE lager SET bestand = bestand - 1 WHERE id <= 100000'
psql -d tag06 -c 'VACUUM (VERBOSE) lager' 2>&1 | grep -m2 -E 'tuples:|cutoff'
psql -d tag06 -c "SELECT pid, datname, state, backend_xmin,
  age(backend_xmin) AS alter_xmin, now() - xact_start AS dauer
  FROM pg_stat_activity WHERE backend_xmin IS NOT NULL
  ORDER BY age(backend_xmin) DESC"

Expected (abridged):

UPDATE 100000
tuples: 0 removed, 1082315 remain, 100000 are dead but not yet removable
removable cutoff: 83015, which was 1 XIDs old when operation ended
  pid  | datname |        state        | backend_xmin | alter_xmin |      dauer
-------+---------+---------------------+--------------+------------+-----------------
 28479 | tag06   | idle in transaction |        83015 |          1 | 00:00:02.905472
 28483 | tag06   | active              |        83016 |          0 | 00:00:00

“dead but not yet removable”: Session A could still read the old versions, so they stay. removable cutoff is the horizon – the oldest transaction that anyone in the cluster still needs to see. With a transaction that has been open for hours, this says “which was 5000000 XIDs old”, and no table in the entire cluster gets cleaned properly anymore. Session A: COMMIT; Then back in the second terminal:

psql -d tag06 -c 'VACUUM (VERBOSE) lager' 2>&1 | grep -m2 -E 'tuples:|cutoff'

Expected (abridged):

tuples: 100000 removed, 889383 remain, 0 are dead but not yet removable
removable cutoff: 83016, which was 0 XIDs old when operation ended

Besides open transactions, the horizon is also held back by replication slots (pg_replication_slots.xmin), standbys with hot_standby_feedback and forgotten prepared transactions (pg_prepared_xacts).

Save your progress? Sign up for free

Drill 7: Transaction ID wraparound

Every writing transaction gets a 32-bit number. Visibility is compared in a circle: roughly 2 billion numbers lie “in the past”, 2 billion “in the future”. So that old rows don’t suddenly end up in the future, VACUUM marks them as frozen – visible to all, regardless of the number:

psql -d tag06 <<'EOF'
SELECT datname, age(datfrozenxid) AS xid_alter,
       round(100.0 * age(datfrozenxid) / 2147483648, 4) AS prozent_bis_wrap
FROM pg_database ORDER BY 2 DESC LIMIT 3;
SHOW autovacuum_freeze_max_age;
SELECT relname, age(relfrozenxid) AS xid_alter FROM pg_class WHERE relname = 'lager';
VACUUM (FREEZE) lager;
SELECT relname, age(relfrozenxid) AS xid_alter FROM pg_class WHERE relname = 'lager';
EOF

Expected (abridged):

  datname  | xid_alter | prozent_bis_wrap
-----------+-----------+------------------
 notes     |     82272 |           0.0038
 template1 |     82272 |           0.0038
 postgres  |     82272 |           0.0038
 autovacuum_freeze_max_age
---------------------------
 200000000
 relname | xid_alter
---------+-----------
 lager   |     15926
VACUUM
 relname | xid_alter
---------+-----------
 lager   |         0

age(relfrozenxid) is the distance to the table’s oldest unfrozen number, datfrozenxid the minimum across the database. When a table reaches autovacuum_freeze_max_age (200 million), autovacuum starts an anti-wraparound run – even if autovacuum_enabled = off is set, and it can’t simply be canceled. If the age still approaches 2 billion (for example because a horizon from Drill 6 is stuck), the server warns in the log and eventually refuses new transaction IDs until a VACUUM has run. So monitor age(datfrozenxid) – alert when the value exceeds about 50 % of autovacuum_freeze_max_age (default: 100 million).

Save your progress? Sign up for free

Exercise: Speed Drills

  1. Show the five tables in the database with the most dead rows. Target time: 30 s
  2. Show the table parameters of lager. Target time: 15 s
  3. Show all currently running autovacuum workers. Target time: 30 s
  4. Show the transaction age of the oldest database. Target time: 20 s

Solution

psql -d tag06 -c 'SELECT relname, n_dead_tup FROM pg_stat_user_tables
  ORDER BY n_dead_tup DESC LIMIT 5'
psql -d tag06 -c "SELECT unnest(reloptions) FROM pg_class WHERE relname = 'lager'"
psql -d tag06 -c "SELECT pid, datname, query FROM pg_stat_activity
  WHERE backend_type = 'autovacuum worker'"
psql -Atc 'SELECT max(age(datfrozenxid)) FROM pg_database'

Expected (abridged):

 relname | n_dead_tup
---------+------------
 lager   |          0
...
 pid | datname | query
-----+---------+-------
(0 rows)
82272

An empty worker list is the normal case; runs are usually over within seconds.

Save your progress? Sign up for free

Checkpoint

psql -d tag06 -Atc "SELECT pg_size_pretty(pg_table_size('lager'))"

Expected: well below 270 MB (135 MB after pg_repack; the updates from Drills 5 and 6 have added a bit).

psql -d tag06 -Atc "SELECT dead_tuple_percent < 5 FROM pgstattuple('lager')"

Expected: t

psql -d tag06 -Atc "SELECT autovacuum_count > 0 FROM pg_stat_user_tables
  WHERE relname = 'lager'"

Expected: t

psql -d tag06 -Atc "SELECT count(*) FROM pg_stat_activity
  WHERE state LIKE 'idle in transaction%' AND datname = 'tag06'"

Expected: 0 – Session A from Drill 6 has ended.

pg_repack --version

Expected: pg_repack 1.5.3

Challenge

The table lager constantly receives updates on bestand. Make sure these updates get by without a new index entry (HOT updates), and prove it with the statistics.

Hint: A HOT update needs free space on the same page – you leave that free with a table parameter when the table is rewritten.

Solution

psql -d tag06 <<'EOF'
SELECT n_tup_upd, n_tup_hot_upd FROM pg_stat_user_tables WHERE relname = 'lager';
ALTER TABLE lager SET (fillfactor = 80);
VACUUM FULL lager;
SELECT pg_size_pretty(pg_table_size('lager'));
UPDATE lager SET bestand = bestand - 1 WHERE id % 10 = 0;
EOF
psql -d tag06 -c "SELECT n_tup_upd, n_tup_hot_upd FROM pg_stat_user_tables
  WHERE relname = 'lager'"

Expected (counters depend on your drills):

 n_tup_upd | n_tup_hot_upd
-----------+---------------
   3120000 |            77
ALTER TABLE
VACUUM
 pg_size_pretty
----------------
 170 MB
UPDATE 100000
 n_tup_upd | n_tup_hot_upd
-----------+---------------
   3220000 |        100077

Before, almost no HOT updates; afterwards, all 100,000. fillfactor = 80 leaves 20 % of each page free when writing; ALTER TABLE alone doesn’t change existing pages, only VACUUM FULL (or pg_repack) rewrites them. HOT (heap-only tuple) works only if no indexed column is changed – bestand has no index.

Save your progress? Sign up for free

Cleanup

dropdb tag06

The package postgresql-18-repack stays installed; it changes nothing on the server as long as no database creates the extension.

Interview Note

  • “Why doesn’t a table get smaller after DELETE?” – VACUUM only frees the space for reuse; the file gets smaller with VACUUM FULL (exclusive lock) or pg_repack (online).
  • “Autovacuum is running, yet the table keeps growing – what do you check?” – The horizon: long or idle in transaction sessions, replication slots, hot_standby_feedback, prepared transactions; then thresholds and cost limit.
  • “What is transaction ID wraparound?” – 32-bit transaction IDs wrap around; VACUUM freezes old rows. Monitor with age(datfrozenxid), otherwise the server eventually refuses writes.

Enterprise Note

  • Never turn off autovacuum globally. Large tables get their own scale_factor values, autovacuum gets more throughput (autovacuum_vacuum_cost_limit, more workers) – the defaults are meant for small servers.
  • log_autovacuum_min_duration at a few seconds so long runs become visible in the log and in the analysis (Day 7).
  • Alerts on the oldest transaction, age(datfrozenxid), inactive replication slots and tables with a high share of dead rows.
  • Plan bloat cleanup with pg_repack or pg_squeeze and first check for space for a copy of the table including its indexes.
  • fillfactor below 100 for tables with many updates on non-indexed columns; watch the HOT share via n_tup_hot_upd.