TL;DR — Kurzzusammenfassung

PostgreSQL VACUUM und Autovacuum: Worker konfigurieren, Bloat überwachen, Wraparound verhindern, pg_repack einsetzen und Index-Bloat effektiv beheben.

Das Multiversion-Concurrency-Control-Modell (MVCC) von PostgreSQL hält alte Zeilversionen am Leben, um gleichzeitige Leser zu bedienen. Ohne regelmäßige Bereinigung häufen sich tote Tupel an, blähen die Tabellengröße auf, verlangsamen sequenzielle Scans und bedrohen schließlich die Datenbankzugänglichkeit durch den Transaction-ID-Wraparound. Dieser Leitfaden deckt alle Stellschrauben ab, die ein DBA nutzen kann: von globalen Autovacuum-Parametern bis zu tabellenspezifischen Überschreibungen, von Bloat-Überwachungsabfragen bis zur Online-Reorganisation mit pg_repack.

Voraussetzungen

Bevor Sie beginnen, stellen Sie sicher, dass Sie folgendes haben:

  • PostgreSQL 13 oder höher (die meisten Beispiele funktionieren ab Version 12+; einige Syntaxen erfordern 14+).
  • psql oder pgAdmin-Zugang mit der Rolle superuser oder pg_monitor.
  • pg_repack-Erweiterung installiert, wenn Sie Online-Reorganisation planen (weiter unten behandelt).
  • Vertrautheit mit postgresql.conf und der Fähigkeit, die Konfiguration neu zu laden.

MVCC und Tote Tupel

PostgreSQL verändert eine Zeile nie an Ort und Stelle. Ein UPDATE schreibt eine neue Tupleversion und markiert die alte als tot. Ein DELETE markiert die Zeile ebenfalls als tot ohne sie zu entfernen. Dieses Design ermöglicht Lesern, einen konsistenten Snapshot zu sehen ohne Sperren zu nehmen, aber der Kompromiss ist die physische Anhäufung toter Tupel.

Tote Tupel verbrauchen Festplattenplatz, blähen die physische Tabellengröße auf und verlangsamen sequenzielle Scans, weil PostgreSQL jede Seite lesen und überspringen muss, auch wenn die meisten ihrer Zeilen tot sind. Die View pg_stat_user_tables verfolgt die Anzahl:

SELECT
  relname,
  n_live_tup,
  n_dead_tup,
  round(n_dead_tup::numeric / nullif(n_live_tup + n_dead_tup, 0) * 100, 1) AS dead_pct,
  last_autovacuum,
  last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;

Eine Tabelle mit dead_pct über 10–15% ist ein Kandidat für sofortige Aufmerksamkeit.

VACUUM, VACUUM FULL und VACUUM ANALYZE

PostgreSQL bietet drei VACUUM-Varianten mit sehr unterschiedlichem Verhalten:

BefehlSperrt Tabelle?Gibt Platz ans OS?Aktualisiert Statistiken?Produktionssicher?
VACUUMNein (ShareUpdateExclusiveLock)Nein — markiert Platz wiederverwendbarNeinJa
VACUUM ANALYZENeinNeinJaJa
VACUUM FULLJa (AccessExclusiveLock)JaNeinMit Vorsicht

VACUUM markiert tote Tupel als wiederverwendbar. Die physische Datei schrumpft nicht — die freigegebenen Seiten werden der Free Space Map (FSM) für zukünftige Einfügungen hinzugefügt. Dies ist der Hauptbefehl, auf den Sie sich verlassen sollten.

VACUUM FULL schreibt die gesamte Tabelle in eine neue Heap-Datei und löscht die alte. Es gibt Festplattenplatz ans Betriebssystem zurück und beseitigt Index-Bloat durch Neuaufbau aller Indizes, hält aber während der gesamten Ausführung eine exklusive Sperre. Bei einer 100-GB-Tabelle kann das 30 Minuten oder mehr vollständiger Nichtverfügbarkeit bedeuten. Führen Sie VACUUM FULL niemals auf einer aktiven Produktionstabelle ohne Wartungsfenster aus — oder nutzen Sie besser pg_repack.

VACUUM ANALYZE kombiniert die Bereinigung toter Tupel mit einer Statistikaktualisierung, die der Abfrageplaner zur Wahl optimaler Ausführungspläne verwendet.

Autovacuum-Konfiguration

Autovacuum führt Hintergrund-Worker aus, die automatisch VACUUM und ANALYZE aufrufen. Die wichtigsten Parameter befinden sich in postgresql.conf:

# Anzahl gleichzeitiger Autovacuum-Worker (Standard: 3)
autovacuum_max_workers = 5

# Wie oft der Autovacuum-Launcher Tabellen prüft (Standard: 1min)
autovacuum_naptime = 30s

# Mindestanzahl toter Tupel vor Auslösung von VACUUM (Standard: 50)
autovacuum_vacuum_threshold = 50

# Anteil lebender Zeilen, die tot sein können, bevor VACUUM ausgelöst wird (Standard: 0.2 = 20%)
autovacuum_vacuum_scale_factor = 0.05

# Mindestanzahl Zeilenänderungen vor Auslösung von ANALYZE (Standard: 50)
autovacuum_analyze_threshold = 50

# Anteil geänderter Zeilen vor Auslösung von ANALYZE (Standard: 0.1 = 10%)
autovacuum_analyze_scale_factor = 0.02

# I/O-Drosselung: Verzögerung zwischen Vacuum-Pufferoperationen in Millisekunden (Standard: 2)
autovacuum_vacuum_cost_delay = 2ms

# Maximale Kosteneinheiten vor einer Verzögerung (Standard: -1 = nutzt vacuum_cost_limit = 200)
autovacuum_vacuum_cost_limit = 400

Die Auslösungsformel verstehen

Autovacuum wird auf einer Tabelle ausgelöst wenn:

n_dead_tup > autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor * n_live_tup

Für eine Tabelle mit 10 Millionen Zeilen mit den Standardwerten:

Schwellenwert = 50 + 0.20 × 10.000.000 = 2.000.050 tote Tupel

Das bedeutet, dass 20% der Tabelle tot sein muss, bevor Autovacuum eingreift. Für eine Tabelle mit hohem Schreibaufkommen ist das zu spät. autovacuum_vacuum_scale_factor auf 0.01 oder sogar 0.005 zu senken, ist für große, häufig aktualisierte Tabellen angemessen.

Nach Änderungen an postgresql.conf die Konfiguration neu laden (kein Neustart erforderlich):

SELECT pg_reload_conf();

Bloat überwachen

Die Views pg_stat_user_tables und pg_stat_activity sind Ihre wichtigsten Überwachungswerkzeuge.

Dashboard-Abfrage für tote Tupel

SELECT
  schemaname,
  relname,
  n_dead_tup,
  n_live_tup,
  last_autovacuum,
  last_autoanalyze,
  autovacuum_count,
  pg_size_pretty(pg_total_relation_size(schemaname || '.' || relname)) AS total_size
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC;

Laufende Autovacuum-Worker finden

SELECT
  pid,
  now() - pg_stat_activity.query_start AS duration,
  query,
  state
FROM pg_stat_activity
WHERE query LIKE 'autovacuum:%'
ORDER BY duration DESC;

Tabellenspezifische Autovacuum-Überschreibungen

Globale Parameter betreffen alle Tabellen. Große, häufig aktualisierte Tabellen benötigen engere Schwellenwerte. Wenden Sie tabellenspezifische Speicherparameter mit ALTER TABLE an:

-- Vacuum auslösen wenn 1% der Zeilen tot sind (statt Standard 20%)
ALTER TABLE orders SET (
  autovacuum_vacuum_scale_factor = 0.01,
  autovacuum_vacuum_threshold = 100,
  autovacuum_analyze_scale_factor = 0.005,
  autovacuum_vacuum_cost_delay = 0
);

-- Überschreibungen überprüfen
SELECT relname, reloptions
FROM pg_class
WHERE relname = 'orders';

Dies ist die wirkungsvollste einzelne Änderung für eine aktive Tabelle. Der globale Skalierungsfaktor bleibt für kleine Tabellen konservativ, während die aktive Tabelle aggressives Vacuum erhält.

Transaction-ID-Wraparound verhindern

PostgreSQL verwendet 32-Bit-Transaktions-IDs (XIDs). Nach etwa 2,1 Milliarden Transaktionen setzt der Zähler zurück. Tupel, die älter als der Wraparound-Horizont sind, könnten wieder sichtbar oder unsichtbar werden und Datenverlust verursachen.

XID-Alter prüfen

SELECT
  relname,
  age(relfrozenxid) AS xid_age,
  pg_size_pretty(pg_total_relation_size(oid)) AS size
FROM pg_class
WHERE relkind = 'r'
ORDER BY age(relfrozenxid) DESC
LIMIT 20;

Wenn xid_age autovacuum_freeze_max_age (Standard: 200 Millionen) überschreitet, erzwingt Autovacuum ein Freeze-Vacuum auf dieser Tabelle. Überwachen Sie auf Datenbankebene:

SELECT datname, age(datfrozenxid) AS db_xid_age
FROM pg_database
ORDER BY age(datfrozenxid) DESC;

Richten Sie eine Warnung ein, wenn das XID-Alter einer Datenbank 1,5 Milliarden überschreitet.

pg_repack: Online-Tabellenreorganisation

Wenn VACUUM keinen physischen Platz zurückgewinnen kann und VACUUM FULL wegen der Sperrung inakzeptabel ist, ist pg_repack die Lösung. Es baut die Tabelle mit einer Schattentabelle und Triggern neu auf, tauscht sie dann atomar aus — alles während die Originaltabelle vollständig verfügbar bleibt.

pg_repack installieren

# Ubuntu/Debian
sudo apt install postgresql-15-repack
-- In der Zieldatenbank
CREATE EXTENSION pg_repack;

Tabelle online reorganisieren

# Eine einzelne aufgeblähte Tabelle reorganisieren
pg_repack -d mydb -t orders

# Alle Tabellen einer Datenbank reorganisieren
pg_repack -d mydb

# Probelauf (zeigen was passieren würde)
pg_repack -d mydb -t orders --dry-run

Vergleich pg_repack vs VACUUM FULL:

Aspektpg_repackVACUUM FULL
Tabellensperre während NeuaufbauKeineAccessExclusiveLock (gesamte Dauer)
Benötigter Festplattenplatz2× Tabellengröße vorübergehend2× Tabellengröße vorübergehend
Index-NeuaufbauJa (parallel)Ja (blockierend)
Erfordert PrimärschlüsselJaNein
ProduktionssicherJaNur im Wartungsfenster

Index-Bloat und REINDEX CONCURRENTLY

Indizes häufen unabhängig von der Tabelle Bloat an. Indizes ohne Downtime neu aufbauen:

-- Einen einzelnen Index parallel neu aufbauen
REINDEX INDEX CONCURRENTLY orders_created_at_idx;

-- Alle Indizes einer Tabelle parallel neu aufbauen (PostgreSQL 14+)
REINDEX TABLE CONCURRENTLY orders;

REINDEX CONCURRENTLY baut einen neuen Index neben dem vorhandenen auf ohne Lese- oder Schreibzugriffe zu blockieren.

Interaktion von PgBouncer und Autovacuum

PgBouncer im Transaktions-Pooling-Modus kann dazu führen, dass inaktive Anwendungsverbindungen offene Transaktionen aufrechterhalten, die das älteste xmin fixieren. Konfigurieren Sie idle_in_transaction_session_timeout = '5min' in PostgreSQL um veraltete Transaktionen automatisch zu beenden.

SELECT slot_name, xmin, catalog_xmin, age(xmin) FROM pg_replication_slots;

Prüfen Sie auch Replikations-Slots, da diese xmin dauerhaft fixieren können.

Häufige Fehler

1. autovacuum = off global setzen. Tun Sie das nie in einer Produktionsdatenbank. Ohne Autovacuum ist ein Wraparound-Shutdown unvermeidlich.

2. VACUUM FULL in Spitzenlastzeiten ausführen. Die exklusive Sperre blockiert alles. Planen Sie es für Wartungsfenster ein oder nutzen Sie pg_repack.

3. Langläufige Transaktionen ignorieren. Wenn eine langläufige Transaktion ein altes xmin hält, kann VACUUM keine toten Tupel entfernen unabhängig von den Einstellungen.

4. autovacuum_max_workers zu hoch konfigurieren. Jeder Worker verbraucht Verbindungen und I/O. Passen Sie zuerst cost_delay und cost_limit an; fügen Sie Worker nur hinzu wenn die Warteschlange wächst.

5. pg_repack auf Tabellen mit verzögerten Triggern nutzen. pg_repack ist mit verzögerten Triggern inkompatibel. Prüfen Sie mit \d+ tablename in psql vor der Ausführung.

Zusammenfassung

Gesunde PostgreSQL-Performance hängt davon ab, dass VACUUM mit Ihrer Schreiblast Schritt halten kann:

  • Senken Sie autovacuum_vacuum_scale_factor für große Tabellen mit hohem Schreibaufkommen — der Standard von 20% ist viel zu locker.
  • Überwachen Sie n_dead_tup und last_autovacuum kontinuierlich über pg_stat_user_tables.
  • Prüfen Sie xid_age regelmäßig; warnen Sie ab 1,5 Milliarden um Wraparound-Notfälle zu verhindern.
  • Nutzen Sie tabellenspezifische Überschreibungen (ALTER TABLE SET) statt globale Einstellungen für alle Tabellen zu lockern.
  • Ersetzen Sie VACUUM FULL durch pg_repack für Online-Reorganisation ohne Downtime.
  • Bauen Sie Indizes mit REINDEX CONCURRENTLY neu auf wenn Index-Bloat die Abfrageleistung beeinträchtigt.
  • Untersuchen Sie langläufige Transaktionen und Replikations-Slots wenn VACUUM läuft aber tote Tupel bestehen bleiben.

Verwandte Artikel