Day 6: VACUUM, Autovacuum and Bloat
Goal: You create dead row versions, measure them, clean them up with
VACUUM,VACUUM FULLandpg_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
UPDATEandDELETEleaves dead row versions behind (Day 4).VACUUMfrees their space for reuse – the file doesn’t shrink in the process. VACUUM FULLrewrites 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.
VACUUMfreezes 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;
EOFExpected (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;
EOFExpected:
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;
EOFExpected (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';
EOFExpected (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
- Show the five tables in the database with the most dead rows. Target time: 30 s
- Show the table parameters of
lager. Target time: 15 s - Show all currently running autovacuum workers. Target time: 30 s
- 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 --versionExpected: 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 tag06The 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?” –VACUUMonly frees the space for reuse; the file gets smaller withVACUUM FULL(exclusive lock) orpg_repack(online). - “Autovacuum is running, yet the table keeps growing – what do you
check?” – The horizon: long or
idle in transactionsessions, replication slots,hot_standby_feedback, prepared transactions; then thresholds and cost limit. - “What is transaction ID wraparound?” – 32-bit transaction IDs wrap
around;
VACUUMfreezes old rows. Monitor withage(datfrozenxid), otherwise the server eventually refuses writes.
Enterprise Note
- Never turn off autovacuum globally. Large tables get their own
scale_factorvalues, autovacuum gets more throughput (autovacuum_vacuum_cost_limit, more workers) – the defaults are meant for small servers. log_autovacuum_min_durationat 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_repackorpg_squeezeand first check for space for a copy of the table including its indexes. fillfactorbelow 100 for tables with many updates on non-indexed columns; watch the HOT share vian_tup_hot_upd.