Alles braucht seine Ordnung, auch PostgreSQL-Datenbanken - Vacuuming

Was ist dieser Job und wofür braucht man ihn?

So verschieden Oracle- und PostgreSQL-Datenbanken sein mögen, eines brauchen mit Sicherheit beide: periodische Speicherplatzwartung in irgendeiner Form. Bei PostgreSQL nennt sich dieser Prozess „Vacuuming“. Sobald der Vacuum Befehl per Hand ausgeführt wird oder der autovacuum nach einer gewissen Zeit prüft, werden alle „Dead Tuples“ einer Tabelle markiert und deren Speicherbereich in die Free Space Map (FSM) eingetragen. Dieser Eintrag sorgt dafür, dass jener Bereich wiederverwendet und schlussendlich wieder überschrieben werden kann.

Was genau sind Dead Tuples...

Um die Lesekonsistenz bei Veränderungen innerhalb der Datenbank aufrechtzuerhalten (beispielsweise bei Updates und Deletes) werden bei PostgreSQL die Undo Informationen innerhalb jeder Tabelle gesichert, nicht wie bei Oracle im separaten Undo Tablespace.

Die „nicht mehr benötigten“ Rows innerhalb einer Tabelle werden somit Dead Tuples genannt.
„Nicht mehr benötigt“ bedeutet hier, wenn die Transaction ID der veränderten Row älter ist als die älteste aktive Transaction innerhalb der PostgreSQL Datenbank.

Der Vorteil dieser Art Undo Management ist, dass keine ORA-01555 snapshot too old eintreten können. Jedoch mit dem Nachteil, dass bei großen Veränderungen die Tabellengröße massiv ansteigen kann. Das sogenannte „Table bloating“

…und was ist die FSM?

Jede Tabelle besitzt eine Free Space Map (FSM), die den Überblick über verfügbaren Speicherplatz hat. Sollte diese kleiner sein als die Anzahl, der in der Tabelle gefundenen Dead Tuples, muss zwangsweise ein Vacuum Full durchgeführt werden, da ein normaler Vacuum Job keinerlei Auswirkung mehr haben wird. Natürlich lässt sich die FSM durch Parameter wie beispielsweise max_fsm_pages oder max_fsm_relations anpassen, um dem entgegenzuwirken.

Und welche Vorteile gibt es noch?

1. Die Statistiken werden, sofern autovacuum oder vacuum ANALYZE durchgeführt werden, erneuert. Diese werden für den PostgreSQL Query Planner benötigt
2. Die Visibility Map wird aktualisiert, diese wird für Index Scans benötigt
3. Um vor Datenverlust des berüchtigten“ transaction ID wraparound“ bzw. „Multixact ID Wraparound“ zu schützten (mehr dazu in einem anderen Tipp)

Wie ausführen?

Das Vacuuming wird entweder manuell durchgeführt oder mit dem entsprechenden Autovacuum Job. Die Syntax für das Doing per Hand ist wie folgt:

VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] ANALYZE [ table_name [ (column_name [, ...] ) ] ]

Autovacuum

Seit PostgreSQL 9 existiert ein Autovacuum Job. Bei einer regulären Installation ist per Default meist der autovacuum Daemon aktiv. Dieser läuft, sofern der track_counts Parameter aktiv ist, auf regelmäßiger Basis und prüft alle Tabellen auf Dead Tuples. Ferner führt er den Befehl ANALYZE aus, welcher für frische Statistiken sorgt, damit der Query Planner immer den besten Plan nutzen kann. Durch den regelmäßig laufenden Autovacuum Job wird verhindert, dass ein Full Vacuum genutzt werden muss. 

Es gibt einige Parameter, die den Autovacuum Job beeinflussen. Unter anderem kann man die Anzahl der Worker, die Gesprächigkeit für das Logfile und die Pausen zwischen den autovacuums anpassen. Alle Parameter aufzuführen würde aber den Rahmen des Tipps sprengen.

Standard Vacuum

Diese Art von Vacuuming wird mittels des oben gezeigten „VACUUM“ Befehls durchgeführt. Standardmäßig (also ohne weiteren Parameter) sorgt er für dasselbe Ergebnis wie der Autovacuum Job, nur ohne ANALYZE
Weitere Parameter sind FREEZE, VERBOSE und zu guter Letzt FULL

Vacuum FULL

Dieser Befehl benötigt einen eigenen Absatz, da es wichtig ist, diesen nicht (oder nur in seltenen Fällen) zu Betriebszeiten auszuführen. Diese Vacuum Durchführung sorgt zwar dafür, dass der Speicherplatz wieder an das OS zurückgegeben wird (nicht nur an das Objekt wie oben), aber setzt auch einen Exclusive Lock auf die entsprechenden Objekte. Demnach kann es zu schwerwiegenden Beeinträchtigungen des Datenbankbetriebs kommen. 
Zudem ist zu beachten, sofern die WAL Archivierung aktiv ist, dass es zu einer erheblichen Steigerung der WAL Dateien kommt, was unter anderem auch für einen massiven I/O-Anstieg sorgt. Des Weiteren kann es bei zu regelmäßiger Nutzung zu Fragmentierung von Indexen kommen, da die Einträge umsortiert werden müssen. Es wird nur empfohlen, diesen Befehl bei zu stark aufgeblähten Objekten zu nutzten.

Fazit

Jedes System sollte seinen Vacuuming Job entsprechend den Bedürfnissen und Umgebungen anpassen und gegebenenfalls optimieren, um so viele Standard Vacuum Jobs (oder am besten autovacuums) und so wenig Full Vacuums laufen zu lassen wie möglich. Zudem gibt es diverse Parameter, die dabei helfen das Maximum herauszuholen.
Gerne unterstützen wir Sie bei der Optimierung, sowie bei allen Fragen bezüglich PostgreSQL.