Tag 6: VACUUM, Autovacuum und Bloat
Ziel: Du erzeugst tote Zeilenversionen, misst sie, räumst sie mit
VACUUM,VACUUM FULLundpg_repackauf, stellst Autovacuum pro Tabelle ein und erkennst, wann eine lange Transaktion oder der Transaction-ID-Wraparound droht. · Dauer: ca. 75 min · Voraussetzung: Tag 4
Briefing
- Jedes
UPDATEundDELETEhinterlässt tote Zeilenversionen (Tag 4).VACUUMgibt ihren Platz zur Wiederverwendung frei – die Datei schrumpft dabei nicht. VACUUM FULLschreibt die Tabelle neu und gibt Platz ans Dateisystem zurück, sperrt sie aber komplett.- Autovacuum erledigt das im Hintergrund, sobald genug Zeilen tot sind. Es kann nur entfernen, was keine offene Transaktion mehr sehen könnte.
- Transaktionsnummern sind 32 Bit.
VACUUMfriert alte Zeilen ein, damit die Zählung nach rund 2 Milliarden umlaufen darf.
UPDATE VACUUM VACUUM FULL / pg_repack
[alt|neu] -> [frei|neu] -> INSERT -> [neu] (Datei kleiner)
Datei 2x Datei 2x, Platz wiederverwendbar
Alles auf pg1, Datenbank tag06.
Drill 1: Tote Tupel erzeugen und zählen
Autovacuum schalten wir für die Testtabelle ab, damit es nicht dazwischenfunkt – das ist schon der erste Tabellenparameter:
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;
EOFErwartet (gekürzt):
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
Eine Million Zeilen geändert, eine Million tote Versionen, die
Tabelle doppelt so groß. n_dead_tup ist ein Zähler der
Statistik, keine Messung – schnell, aber ungenau, und nach einem Absturz
wieder bei null.
Fortschritt speichern? Kostenlos registrieren
Drill 2: Genau messen mit pgstattuple
Die Erweiterung pgstattuple steckt im Paket
postgresql-18 und liest die Tabelle Seite für Seite:
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;
EOFErwartet:
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)
Fast die Hälfte der Datei sind tote Zeilen. Auch der Index ist
gewachsen (vorher 21 MB, Drill 1): Die Seiten waren voll, die neue
Version landete auf einer anderen Seite und brauchte einen eigenen
Indexeintrag. pgstattuple liest alles – auf großen Tabellen
in Produktion lieber pgstattuple_approx(), das per
Visibility Map abkürzt.
Fortschritt speichern? Kostenlos registrieren
Drill 3: VACUUM gibt Platz frei – aber nicht zurück
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;
EOFErwartet (gekürzt):
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
Die toten Zeilen sind weg, die Datei bleibt bei 270 MB – zur Hälfte
freier Platz. Das nächste UPDATE aller Zeilen passt genau
hinein, die Tabelle wächst nicht. Das ist der Normalbetrieb: Eine
Tabelle mit regelmäßigen Updates pendelt sich auf einer Größe mit etwas
Luft ein. VACUUM setzt außerdem die Visibility Map (Index
Only Scans, Tag 5) und friert Zeilen ein (Drill 7). Es läuft parallel zu
Lesern und Schreibern.
Fortschritt speichern? Kostenlos registrieren
Drill 4: VACUUM FULL gegen pg_repack
Ist die Luft dauerhaft zu groß – etwa nach einem großen
DELETE –, muss die Datei neu geschrieben werden:
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"Erwartet (gekürzt):
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: Während VACUUM FULL
läuft, kann niemand die Tabelle auch nur lesen. Bei 135 MB sind das
Sekunden, bei 500 GB Stunden. Außerdem braucht es Platz für eine
komplette Kopie.
Die Alternative pg_repack baut die Kopie im laufenden
Betrieb und sperrt nur kurz am Anfang und Ende. Sie liegt im
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"Erwartet (gekürzt):
UPDATE 1000000
VACUUM
vorher
--------
270 MB
CREATE EXTENSION
pg_repack 1.5.3
INFO: repacking table "public.lager"
nachher
---------
135 MB
pg_repack braucht einen Primärschlüssel (oder
eindeutigen Index), die Erweiterung in der Datenbank und
Superuser-Rechte. Es zeichnet Änderungen während des Kopierens per
Trigger auf und spielt sie am Ende nach.
Fortschritt speichern? Kostenlos registrieren
Drill 5: Autovacuum pro Tabelle einstellen
Autovacuum startet für eine Tabelle, wenn die toten Zeilen diese Schwelle übersteigen:
autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor * Zeilen
50 + 0.2 * Zeilen
Bei einer Million Zeilen also erst ab 200 050 toten – bei einer
Milliarde Zeilen 200 Millionen. Seit PostgreSQL 18 deckelt
autovacuum_vacuum_max_threshold (Standard 100 Millionen)
die Schwelle. Für große, heiße Tabellen setzt du die Werte pro
Tabelle:
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'"Erwartet (gekürzt):
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 tote Zeilen über der Schwelle von 11 000. Der
Autovacuum-Launcher schaut alle autovacuum_naptime (60 s)
nach. Eine Minute warten, dann:
psql -d tag06 -c "SELECT n_dead_tup, last_autovacuum, autovacuum_count
FROM pg_stat_user_tables WHERE relname = 'lager'"Erwartet:
n_dead_tup | last_autovacuum | autovacuum_count
------------+-------------------------------+------------------
0 | 2026-11-19 05:18:38.627177+00 | 1
(1 row)
Nach 41 Sekunden war es erledigt. Ins Log schreibt Autovacuum nur
Läufe über log_autovacuum_min_duration (Standard 10 min) –
in Produktion meist niedriger setzen. Laufende Worker zeigen
pg_stat_activity
(backend_type = 'autovacuum worker') und
pg_stat_progress_vacuum.
Fortschritt speichern? Kostenlos registrieren
Drill 6: Eine lange Transaktion blockiert VACUUM
Zwei Terminals. Sitzung A
(psql -d tag06):
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT count(*) FROM lager;Im zweiten 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"Erwartet (gekürzt):
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“: Sitzung A könnte die alten Versionen
noch lesen, also bleiben sie. removable cutoff ist der
Horizont – die älteste Transaktion, die irgendwer im Cluster noch sehen
muss. Bei einer Transaktion, die seit Stunden offen ist, steht hier
„which was 5000000 XIDs old“, und keine Tabelle im
ganzen Cluster wird mehr sauber. Sitzung A:
COMMIT; Dann wieder im zweiten Terminal:
psql -d tag06 -c 'VACUUM (VERBOSE) lager' 2>&1 | grep -m2 -E 'tuples:|cutoff'Erwartet (gekürzt):
tuples: 100000 removed, 889383 remain, 0 are dead but not yet removable
removable cutoff: 83016, which was 0 XIDs old when operation ended
Den Horizont halten neben offenen Transaktionen auch
Replikations-Slots (pg_replication_slots.xmin), Standbys
mit hot_standby_feedback und vergessene vorbereitete
Transaktionen (pg_prepared_xacts) fest.
Fortschritt speichern? Kostenlos registrieren
Drill 7: Transaction-ID-Wraparound
Jede schreibende Transaktion bekommt eine 32-Bit-Nummer. Sichtbarkeit
wird im Kreis verglichen: rund 2 Milliarden Nummern liegen „in der
Vergangenheit“, 2 Milliarden „in der Zukunft“. Damit alte Zeilen nicht
plötzlich in der Zukunft liegen, markiert VACUUM sie als
eingefroren – sichtbar für alle, unabhängig von der Nummer:
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';
EOFErwartet (gekürzt):
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) ist der Abstand zur ältesten nicht
eingefrorenen Nummer der Tabelle, datfrozenxid das Minimum
über die Datenbank. Erreicht eine Tabelle
autovacuum_freeze_max_age (200 Millionen), startet
Autovacuum einen Anti-Wraparound-Lauf – auch wenn
autovacuum_enabled = off gesetzt ist, und er lässt sich
nicht einfach abbrechen. Kommt das Alter trotzdem in die Nähe von 2
Milliarden (etwa weil ein Horizont aus Drill 6 klemmt), warnt der Server
im Log und verweigert schließlich neue Transaktions-IDs, bis ein
VACUUM gelaufen ist. Überwache deshalb
age(datfrozenxid) – Alarm, wenn der Wert etwa 50 % von
autovacuum_freeze_max_age übersteigt (Standard: 100
Millionen).
Fortschritt speichern? Kostenlos registrieren
Übung: Speed-Drills
- Zeige die fünf Tabellen der Datenbank mit den meisten toten Zeilen. Zeitziel: 30 s
- Zeige die Tabellenparameter von
lager. Zeitziel: 15 s - Zeige alle gerade laufenden Autovacuum-Worker. Zeitziel: 30 s
- Zeige das Transaktions-Alter der ältesten Datenbank. Zeitziel: 20 s
Lösung
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'Erwartet (gekürzt):
relname | n_dead_tup
---------+------------
lager | 0
...
pid | datname | query
-----+---------+-------
(0 rows)
82272
Leere Worker-Liste ist der Normalfall; die Läufe sind meist in Sekunden vorbei.
Fortschritt speichern? Kostenlos registrieren
Kontrollpunkt
psql -d tag06 -Atc "SELECT pg_size_pretty(pg_table_size('lager'))"Erwartet: deutlich unter 270 MB (nach
pg_repack 135 MB, die Updates aus Drill 5 und 6 haben etwas
angebaut).
psql -d tag06 -Atc "SELECT dead_tuple_percent < 5 FROM pgstattuple('lager')"Erwartet: t
psql -d tag06 -Atc "SELECT autovacuum_count > 0 FROM pg_stat_user_tables
WHERE relname = 'lager'"Erwartet: t
psql -d tag06 -Atc "SELECT count(*) FROM pg_stat_activity
WHERE state LIKE 'idle in transaction%' AND datname = 'tag06'"Erwartet: 0 – Sitzung A aus Drill 6 ist
beendet.
pg_repack --versionErwartet: pg_repack 1.5.3
Challenge
Die Tabelle lager bekommt ständig Updates auf
bestand. Sorge dafür, dass diese Updates ohne neuen
Indexeintrag auskommen (HOT-Updates), und weise es mit den Statistiken
nach.
Hinweis: Ein HOT-Update braucht freien Platz auf derselben Seite – den lässt du mit einem Tabellenparameter beim Neuschreiben der Tabelle frei.
Lösung
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'"Erwartet (Zähler abhängig von deinen 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
Vorher fast kein HOT-Update, nachher alle 100 000.
fillfactor = 80 lässt beim Schreiben 20 % jeder Seite frei;
ALTER TABLE allein ändert bestehende Seiten nicht, erst
VACUUM FULL (oder pg_repack) schreibt sie neu.
HOT (Heap-Only Tuple) klappt nur, wenn keine indizierte Spalte geändert
wird – bestand hat keinen Index.
Fortschritt speichern? Kostenlos registrieren
Aufräumen
dropdb tag06Das Paket postgresql-18-repack bleibt installiert; es
ändert nichts am Server, solange keine Datenbank die Erweiterung
anlegt.
Interview-Notiz
- „Warum wird eine Tabelle nach
DELETEnicht kleiner?“ –VACUUMgibt den Platz nur zur Wiederverwendung frei; kleiner wird die Datei mitVACUUM FULL(exklusive Sperre) oderpg_repack(online). - „Autovacuum läuft, trotzdem wächst die Tabelle – was prüfst du?“ –
Den Horizont: lange oder
idle in transaction-Sitzungen, Replikations-Slots,hot_standby_feedback, vorbereitete Transaktionen; dann Schwellen und Kostenlimit. - „Was ist Transaction-ID-Wraparound?“ – 32-Bit-Transaktionsnummern
laufen um;
VACUUMfriert alte Zeilen ein. Überwachen mitage(datfrozenxid), sonst verweigert der Server irgendwann Schreibzugriffe.
Enterprise-Notiz
- Autovacuum nie global abschalten. Große Tabellen bekommen eigene
scale_factor-Werte, Autovacuum mehr Durchsatz (autovacuum_vacuum_cost_limit, mehr Worker) – die Standardwerte sind für kleine Server gedacht. log_autovacuum_min_durationauf wenige Sekunden, damit lange Läufe im Log und in der Auswertung (Tag 7) sichtbar werden.- Alarme auf älteste Transaktion,
age(datfrozenxid), inaktive Replikations-Slots und Tabellen mit hohem Anteil toter Zeilen. - Bloat-Bereinigung mit
pg_repackoderpg_squeezeplanen und vorher Platz für eine Kopie der Tabelle samt Indizes prüfen. fillfactorunter 100 für Tabellen mit vielen Updates auf nicht indizierten Spalten; HOT-Anteil übern_tup_hot_updbeobachten.