Zum Inhalt springen
Trainingslager IT-Trainingslager

Tag 6: VACUUM, Autovacuum und Bloat

Ziel: Du erzeugst tote Zeilenversionen, misst sie, räumst sie mit VACUUM, VACUUM FULL und pg_repack auf, 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 UPDATE und DELETE hinterlässt tote Zeilenversionen (Tag 4). VACUUM gibt ihren Platz zur Wiederverwendung frei – die Datei schrumpft dabei nicht.
  • VACUUM FULL schreibt 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. VACUUM friert 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;
EOF

Erwartet (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;
EOF

Erwartet:

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;
EOF

Erwartet (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';
EOF

Erwartet (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

  1. Zeige die fünf Tabellen der Datenbank mit den meisten toten Zeilen. Zeitziel: 30 s
  2. Zeige die Tabellenparameter von lager. Zeitziel: 15 s
  3. Zeige alle gerade laufenden Autovacuum-Worker. Zeitziel: 30 s
  4. 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 --version

Erwartet: 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 tag06

Das Paket postgresql-18-repack bleibt installiert; es ändert nichts am Server, solange keine Datenbank die Erweiterung anlegt.

Interview-Notiz

  • „Warum wird eine Tabelle nach DELETE nicht kleiner?“ – VACUUM gibt den Platz nur zur Wiederverwendung frei; kleiner wird die Datei mit VACUUM FULL (exklusive Sperre) oder pg_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; VACUUM friert alte Zeilen ein. Überwachen mit age(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_duration auf 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_repack oder pg_squeeze planen und vorher Platz für eine Kopie der Tabelle samt Indizes prüfen.
  • fillfactor unter 100 für Tabellen mit vielen Updates auf nicht indizierten Spalten; HOT-Anteil über n_tup_hot_upd beobachten.