Tote Zeilen bleiben liegen, obwohl Autovacuum läuft
Ein Vacuum darf nur entfernen, was keine laufende Transaktion mehr sehen könnte, und genau daran scheitert es häufiger als an der Konfiguration.
KI-generiertDieses Bild wurde mit KI erzeugt · Yves Hoppe / KI / cmt
Der Speicherbedarf wächst, die Datenmenge nicht
Das Muster ist wiedererkennbar: Eine Tabelle mit vierzig Millionen Zeilen belegt 40 GB, die Abfragen darauf werden von Woche zu Woche langsamer, und die Zeilenzahl ist seit Monaten unverändert. Der Grund liegt im Mehrversionenverfahren. Ein UPDATE ändert keine Zeile, es schreibt eine neue Version und markiert die alte als überholt. Erst ein Vacuum gibt diesen Platz innerhalb der Tabelle wieder frei.
Der Schaden ist doppelt. Jeder sequenzielle Durchlauf liest die toten Zeilen mit, jeder Index wird größer als nötig, und der Anteil nützlicher Daten je gelesenem Block sinkt. Gleichzeitig steigt der Speicherbedarf im Dateisystem und in jeder Sicherung, ohne dass fachlich etwas dazugekommen wäre.
Der zweite, gefährlichere Teil betrifft die Transaktionsnummern. Vacuum hat neben dem Aufräumen die Aufgabe, alte Zeilen einzufrieren. Bleibt das über lange Zeit aus, nähert sich die Datenbank der Grenze des Nummernkreises, warnt zunächst deutlich im Protokoll und verweigert schließlich neue Transaktionen. Dieser Zustand ist selten, dafür ausgesprochen unangenehm.
KI-generiertDieses Bild wurde mit KI erzeugt · Yves Hoppe / KI / cmt
Symptom, Ursache, Lösung
Symptom
n_dead_tup steigt seit Tagen, last_autovacuum liegt drei Wochen zurück.
Ursache
Die Tabelle ist groß, und der Schwellenwert von 20 Prozent plus Grundmenge ist noch nicht erreicht. Bei 80 Millionen Zeilen wartet PostgreSQL auf 16 Millionen tote Zeilen, bevor der erste Durchlauf startet.
Lösung
Für diese Tabelle den Skalierungsfaktor senken und den Grundwert setzen, etwa ALTER TABLE bestellungen SET (autovacuum_vacuum_scale_factor = 0.01, autovacuum_vacuum_threshold = 5000). Global umzustellen ist selten sinnvoll, weil kleine Tabellen dann unnötig oft bearbeitet werden.
Symptom
Autovacuum läuft laut pg_stat_activity dauernd auf der Tabelle, n_dead_tup sinkt trotzdem nicht.
Ursache
Eine ältere Transaktion hält die Sichtbarkeitsgrenze fest. Vacuum darf nur Zeilenversionen entfernen, die keine laufende Transaktion mehr sehen könnte, und meldet den Rest als noch nicht entfernbar.
Lösung
In pg_stat_activity nach der kleinsten backend_xmin und dem ältesten xact_start suchen, häufig ist es eine Sitzung im Zustand idle in transaction. Danach idle_in_transaction_session_timeout setzen, damit die Anwendung solche Sitzungen nicht dauerhaft offen lässt.
Symptom
Auf dem Primärsystem bleiben tote Zeilen liegen, seit ein zweiter Standby aufgebaut wurde.
Ursache
Ein Replikations-Slot hält die Sichtbarkeitsgrenze zurück, damit ein abgehängter Standby seine Daten noch bekommt. Dasselbe passiert bei eingeschaltetem hot_standby_feedback mit langen Abfragen auf dem Standby.
Lösung
pg_replication_slots prüfen, besonders inaktive Slots mit alter xmin oder catalog_xmin. Nicht mehr benötigte Slots entfernen und für die verbleibenden über max_slot_wal_keep_size eine Obergrenze festlegen.
Symptom
Im Serverprotokoll steht mehrmals pro Stunde, dass eine Autovacuum-Aufgabe abgebrochen wurde.
Ursache
Autovacuum weicht zurück, sobald es eine andere Sitzung blockiert, die eine strenge Sperre anfordert. Läuft auf der Tabelle regelmäßig eine Strukturänderung oder ein Job mit LOCK TABLE, kommt der Durchlauf nie ans Ende.
Lösung
Die blockierenden Vorgänge zeitlich bündeln, statt sie über den Tag zu verteilen, und ein manuelles VACUUM in ein ruhiges Fenster legen. Ein Durchlauf zur Vermeidung des Nummernüberlaufs weicht übrigens nicht zurück, der blockiert dann seinerseits.
Symptom
Die Tabelle ist nach einem VACUUM immer noch 40 GB groß.
Ursache
VACUUM gibt Platz für neue Zeilen innerhalb der Datei frei, es schreibt die Tabelle nicht um. An das Dateisystem geht nur zurück, was am Ende der Datei zusammenhängend leer ist.
Lösung
Wird die Tabelle weiter in derselben Größenordnung beschrieben, ist der Zustand in Ordnung, der Platz wird wiederverwendet. Soll die Datei schrumpfen, braucht es VACUUM FULL mit exklusiver Sperre oder ein Umschreiben im laufenden Betrieb, danach eine Einstellung, die den Zustand nicht wieder entstehen lässt.
Fünf Fragen, die die Ursache eingrenzen
- 01 Hat Autovacuum die Tabelle überhaupt angefasst? last_autovacuum sagt es.
- 02 Ist der Schwellenwert erreicht? Bei großen Tabellen dauert das lange.
- 03 Gibt es eine offene Transaktion, die ältere Zeilen noch sehen könnte?
- 04 Hält ein Replikations-Slot oder ein Standby die Grenze zurück?
- 05 Bricht der Durchlauf ab, weil er eine Sperre blockiert? Das Protokoll zeigt es.
Was du nach dieser Seite in einer Viertelstunde klärst
Die Prüfungen laufen alle lesend und brauchen kein Wartungsfenster. Die Reihenfolge ist wichtig: Erst klärst du, ob überhaupt aufgeräumt wird, dann ob aufgeräumt werden darf, und erst zum Schluss stellst du Parameter um.
Den Zustand der Tabelle ablesen
pg_stat_user_tables liefert n_live_tup, n_dead_tup, last_autovacuum und autovacuum_count. Steht dort bei einer stark geänderten Tabelle ein Datum von vor drei Wochen, lautet die Frage nicht, warum es nicht wirkt, sondern warum es nicht startet.
Den Schwellenwert nachrechnen
Autovacuum startet in der Voreinstellung erst, wenn tote Zeilen 20 Prozent der Tabelle ausmachen, plus einer kleinen Grundmenge. Bei 50 Millionen Zeilen sind das 10 Millionen tote Zeilen, bevor überhaupt etwas passiert.
Die Sichtbarkeitsgrenze finden
Läuft irgendwo eine Transaktion seit Stunden, darf Vacuum nichts entfernen, was nach deren Beginn ungültig wurde. pg_stat_activity mit xact_start und backend_xmin zeigt die älteste Sitzung, pg_replication_slots die zweite häufige Ursache.
Pro Tabelle einstellen statt global
ALTER TABLE ... SET (autovacuum_vacuum_scale_factor = 0.02) betrifft nur die eine Tabelle. Große, heiß geänderte Tabellen bekommen niedrige Werte, alle anderen bleiben bei der Voreinstellung.
Die Bremse kennen
Autovacuum arbeitet mit einer Kostengrenze und legt regelmäßig Pausen ein, damit es den Betrieb nicht stört. Auf schnellen Datenträgern ist diese Voreinstellung oft zu vorsichtig, und der Durchlauf kommt mit der Änderungsrate nicht mit.
Platz freigeben und Platz zurückgeben unterscheiden
Ein normales VACUUM gibt Platz innerhalb der Tabelle für neue Zeilen frei, verkleinert die Datei aber praktisch nie. Wer die Datei kleiner haben will, braucht VACUUM FULL mit exklusiver Sperre oder die Erweiterung pg_repack, die die Tabelle im laufenden Betrieb neu aufbaut.
KI-generiertDieses Bild wurde mit KI erzeugt · Yves Hoppe / KI / cmt
Warum überhaupt tote Zeilen entstehen
PostgreSQL ändert Zeilen nicht an Ort und Stelle. Ein UPDATE schreibt eine neue Version der Zeile und markiert die alte mit der Transaktionsnummer, ab der sie ungültig ist. Ein DELETE macht nur Letzteres. Solange irgendeine laufende Transaktion die alte Version noch sehen dürfte, muss sie stehen bleiben, denn genau darauf beruht das Lesen ohne Sperren.
Vacuum räumt diese Versionen auf, sobald sie für niemanden mehr sichtbar sind, und trägt die freien Stellen in die Freiraumkarte ein. Neue Zeilen landen dann wieder in diesen Lücken. Wichtig ist die Unterscheidung: freigegeben heißt wiederverwendbar, nicht zurückgegeben. Die Datei bleibt so groß, wie sie einmal war, es sei denn, die letzten Blöcke sind vollständig leer. Einen Teil der Arbeit erledigt PostgreSQL allerdings nebenbei: Ändert ein UPDATE keine indizierte Spalte und ist im selben Block noch Platz, bleibt die neue Version im Block, und die alte lässt sich beim nächsten Zugriff ohne Vacuum aufräumen. Für stark geänderte Tabellen lohnt es sich deshalb, über fillfactor von vornherein etwas Luft im Block zu lassen.
Ein Sonderfall wird oft übersehen: Sehr breite Werte liegen in einer eigenen TOAST-Tabelle mit eigener Statistik und eigenen Einstellungen. Wenn sich eine Tabelle seltsam verhält, lohnt der Blick auf die zugehörige TOAST-Tabelle. Temporäre Tabellen wiederum bearbeitet Autovacuum nie, dort musst du in derselben Sitzung selbst aufräumen.
Die Schwellenwerte und warum sie bei großen Tabellen versagen
Autovacuum startet für eine Tabelle, wenn die Zahl der toten Zeilen über autovacuum_vacuum_threshold plus autovacuum_vacuum_scale_factor mal Zeilenzahl liegt. In der Voreinstellung sind das 50 Zeilen plus 20 Prozent. Für eine Tabelle mit 5.000 Zeilen ist das sinnvoll, für eine mit 80 Millionen bedeutet es, dass 16 Millionen tote Zeilen entstehen dürfen, bevor etwas passiert.
Die Antwort darauf ist nicht, den globalen Wert zu senken, sondern ihn für die wenigen großen, stark geänderten Tabellen zu setzen. Genau dafür gibt es Speicherparameter je Tabelle. Ein Faktor von 0,01 bis 0,02 zusammen mit einem festen Grundwert von einigen tausend Zeilen ist für solche Tabellen ein brauchbarer Ausgangspunkt.
Seit PostgreSQL 13 gibt es zusätzlich einen Schwellenwert für Einfügungen, damit auch reine Anhängetabellen regelmäßig bearbeitet und eingefroren werden. Vorher konnte eine Tabelle, in die nur eingefügt wird, sehr lange unberührt bleiben und dann eine ausgesprochen lange Aufräumaktion auslösen.
Der eigentliche Bremsklotz ist die Sichtbarkeitsgrenze
Die häufigste Ursache für wirkungsloses Vacuum ist keine Einstellung, sondern eine alte offene Transaktion. Solange sie läuft, muss jede Zeilenversion erhalten bleiben, die zu ihrem Beginn gültig war, und zwar unabhängig davon, ob diese Transaktion die Tabelle überhaupt liest. VACUUM VERBOSE sagt es dir direkt, es nennt die Zahl der noch nicht entfernbaren Zeilen und die älteste Transaktionsnummer, die daran hindert.
Die typischen Verursacher sind schnell aufgezählt: eine Anwendung, die eine Transaktion öffnet und darin auf einen externen Dienst wartet; ein Auswertungswerkzeug mit einer stundenlangen Abfrage; ein vergessenes BEGIN in einer offenen Konsole; ein Replikations-Slot ohne aktiven Empfänger; ein Standby mit eingeschaltetem hot_standby_feedback und langen Abfragen; eine vorbereitete Transaktion, die nie abgeschlossen wurde und in pg_prepared_xacts steht.
Gegen die ersten drei hilft idle_in_transaction_session_timeout, gegen die übrigen ein regelmäßiger Blick in pg_replication_slots und pg_prepared_xacts, und nicht erst, wenn die Platte voll ist. Ein inaktiver Slot ist der Klassiker, weil er zusätzlich WAL-Dateien zurückhält und damit zwei Probleme gleichzeitig erzeugt.
Vom Verdacht zur Messung
n_dead_tup ist eine Schätzung aus der Statistik und kein exakter Wert. Für eine belastbare Aussage über eine einzelne Tabelle gibt es die Erweiterung pgstattuple, die die Tabelle liest und den tatsächlichen Anteil toter Daten sowie den freien Platz meldet. pgstattuple_approx arbeitet mit Stichproben und ist bei großen Tabellen die verträglichere Variante.
Für laufende Durchläufe zeigt pg_stat_progress_vacuum die aktuelle Phase und den Fortschritt. Das ist die Sicht, die dir sagt, ob ein Vacuum auf einer 300-GB-Tabelle noch zwei Stunden braucht oder ob es feststeckt. Mit log_autovacuum_min_duration auf 0 protokolliert PostgreSQL jeden Durchlauf mit Dauer und Ergebnis, was für eine Ursachensuche über mehrere Tage die beste Grundlage ist.
Ein Punkt geht dabei leicht unter: Indizes räumt Vacuum zwar auf, es baut sie aber nicht neu. Ein über Jahre aufgeblähter Index bleibt aufgebläht. Dafür gibt es REINDEX, seit PostgreSQL 12 auch nebenläufig, sodass die Tabelle währenddessen beschreibbar bleibt.
Dazu passende Kurse
Was hier an Abfragen und Stellschrauben steht, üben die PostgreSQL-Kurse für Administration und Wartung an Datenbanken, die dieselben Eigenheiten mitbringen wie deine.
Aufräumverfahren unterscheiden sich je System deutlich, deshalb lohnt sich das Datenbankprogramm von cmt im Überblick , wenn bei dir mehrere davon laufen.
Wie sicher bist du beim Thema wirklich?
Lesen fühlt sich schnell nach Können an. Ein kurzer Test zeigt dir, was davon schon sitzt und wo sich ein Kurs lohnt. Kostenlos, ohne Anmeldung, mit einer Erklärung zu jeder Antwort.
Super Schulung, in der der Trainer auch auf eigene Fragestellungen eingegangen ist.
Super Grundlagenschulung mit allen wichtigen Punkten und hilfreichen Tipps!
Schön, sich 5 Tage ungestört mit Postgres beschäftigen zu können.
Häufige Fragen
Kann ich Autovacuum abschalten und nachts manuell aufräumen?
Warum sinkt die Dateigröße nach VACUUM nicht?
Wie viele Autovacuum-Arbeiter sind sinnvoll?
Was bedeutet die Warnung zum Transaktionsnummernkreis im Protokoll?
Deine Ansprechpartner
Du bist dir nicht sicher, welcher Kurs oder welches Level zu dir passt? Wir beraten dich persönlich und kostenlos.
Yves Hoppe
Weiterbildung & Beratung
Hilft dir, aus dem Datenbanken-Programm den passenden Kurs für deinen Stand zu finden.
Norbert Jansen
Beratung & Inhouse
Plant mit dir Inhouse-Trainings, die auf eure Abläufe und euren Datenbestand zugeschnitten sind.
Wartung, die nicht dem Zufall überlassen bleibt
Vacuum, Bloat und Einstellungen je Tabelle in den Griff zu bekommen, gehört bei cmt zum festen Bestandteil der PostgreSQL-Kurse für den Betrieb.