Speicherplatz für PostgreSQL-Tabellen richtig nutzen

Würde man in einer Datenbank immer nur nacheinander lesen oder schreiben, gäbe es das Problem nicht, und frei gewordener Speicher ließe sich umgehend wieder neu einsetzen. Doch so strikt sequenziell möchte niemand arbeiten, denn das würde unnötig Zeit kosten Zeit, die paralleles Lesen und Schreiben spart. Die Technik dafür heißt MVCC, Multi Version Concurrency Control. Allerdings kann es damit passieren, dass eine Partei einen Datensatz lesen will, den eine andere im Begriff ist zu ändern. Was soll die Datenbank dann tun?

PostgreSQL behilft sich damit, dass es solange die alten Werte zurückgibt, bis der schreibende Anwender die Transaktion committed hat und die Änderungen endgültig gespeichert wurden. Noch komplexer wird das Ganze, wenn mehrere Anwender zeitgleich auf derselben Zeile schreiben. Die Konsequenz aus alldem ist jedenfalls, dass die Datenbank den Speicherplatz gelöschter oder geänderter Datensätze nicht sofort überschreiben darf, sondern die alte Version mindestens bis zum Commit aufbewahren muss.

Nach dem Commit sind die alten Versionen – man spricht jetzt von toten Tupeln – nutzlos. Die Datenbank muss sie aber noch explizit recyclen. Solange das nicht erledigt ist, muss sie für neue Daten neuen Speicherplatz auf der Festplatte belegen.

Die Beispiel-Kneipe

Das Verhalten der Datenbank lässt sich gut mit dem Prozedere an einem Tresen vergleichen. Zurückkommende Gläser, egal ob ausgetrunken oder nicht, gleichen den toten Tupeln. Bevor sie wiederverwendet werden können, müssen sie gespült werden. Je länger mit dem Spülen gewartet wird, desto mehr Gläser braucht der Umlauf. Das Spülen übernimmt bei PostgreSQL der Befehl VACUUM (kommt von staubsaugen).

Das soll ein konkretes Beispiel demonstrieren. Dazu ist eine Tabelle »bier« nötig, die zunächst 9000 Datensätze enthalten soll:

CREATE TABLE bier(bestellnummer integer, inhalt text);
INSERT INTO bier(bestellnummer, inhalt) VALUES(generate_series(1,9000), 'Bier');

Der Wirt hat Kellner, die den Gästen das Bestellte an den Tisch bringen. Übertragen auf PostgreSQL ist der Wirt das Datenbanksystem, und die Angestellten sind die User, die SQL-Befehle wie INSERT, UPDATE oder DELETE ausführen. Der Chef des Wirtes ist der Administrator. Der Administrator kann ohne Probleme selbst einsehen, wie viele Bestellungen die Gäste aufgegeben haben:

SELECT count(*) as bestelltFROM bier;bestellt
-----------
 9000

Bierkonsum analysiert

Der Administrator möchte aber vom Wirt mehr wissen: Wie viele Paletten an Biergläsern wurden dafür in Umlauf gebracht? Um diese Frage zu beantworten, muss sich der Wirt erst einen Überblick verschaffen. Den Überblick verschafft sich PostgreSQL mit dem SQL-Befehl »ANALYZE« (funktioniert auch in britischer Schreibweise: »ANALYSE« ). »ANALYZE« sorgt dafür, dass die Statistiken auf den aktuellen Stand gebracht werden. Ohne einen Überblick zu haben, nimmt PostgreSQL an, es sei noch gar nichts passiert (Listing 1).

Wird »ANALYZE« ohne weitere Angaben gestartet, wertet es alle Tabellen der Datenbank aus. Will man es nur für eine Tabelle oder gar nur für bestimmte Spalten einer Tabelle ausführen, dann sind der Tabellenname und gegebenenfalls die Spaltennamen mit anzugeben: »ANALYZE bier;« Jetzt hat der Wirt den Überblick (Listing 2).

PostgreSQL nutzt zur Speicherung 8 KByte große Pages. Wie viele Datensätze in eine solche Page passen, hängt von der Art der Tabelle und den verwendeten Datentypen ab. Werden variable Datentypen (etwa »VARCHAR« oder »TEXT« ) benutzt, dann kann die Anzahl der Datensätze pro Page variieren. Es gibt auch Sondermechanismen (»TOAST« ) für den Fall, dass ein einzelner Datensatz größer als 8 KByte ist. Darauf wird dieser Artikel aber nicht weiter eingehen.

Im Beispiel lässt sich eine Page als eine Palette Gläser vorstellen, die in Umlauf gebracht wurde. Der Wirt kann damit antworten, dass Gläser von 45 Paletten in Umlauf sind. Die Spalte »relpages« gibt die Anzahl der im Umlauf befindlichen Pages an. Um das Rechnen zu erleichtern, kennt PostgreSQL die Funktion »pg_pretty_size(Byte-Anzahl)« . Ein Kilobyte sind 1024 Byte. Da Pages immer 8 KByte groß sind, multipliziert die Funktion die Spalte »relpages« mit 8 mal 1024. Da der Wert der Spalte »reltuple« mit der tatsächlichen Anzahl der Datensätze in der Tabelle übereinstimmt, kann man davon ausgehen, dass die Statistiken auf dem aktuellen Stand sind. Ein Datensatz ist ein Tupel.

Es könnte nützlich sein, auch die durchschnittliche Anzahl an Datensätzen pro Page zu beobachten. Die Funktion »floor(Wert)« rundet ein Ergebnis immer ab. Da PostgreSQL zu Recht streikt, wenn durch null geteilt werden soll, fängt »case« diesen Fall ab (Listing 3).

Die ersten 5000 Gläser Bier sind getrunken und die Bestellung abgehakt. Sie werden aus der Datenbank gelöscht:

DELETE FROM bier WHERE bestellnummer<=5000;

Wie viele laufende Bestellungen gibt es jetzt noch?

SELECT count(*) as bestellt FROM bier;
 bestellt
-----------
 4000

Jetzt befragt der Anwender wieder die Statistiken (Listing 3).

Der Vergleich mit dem Ergebnis von »reltuple« und »count(*)« lässt sofort erkennen, dass die Statistiken noch nicht aktualisiert sind. Das holt Listing 4 nach.

Die Tabelle ist nicht kleiner geworden. Verglichen mit dem vorherigen Ergebnis kann man darauf schließen, dass es eine Menge toter Tupel geben muss – eine Menge schmutziger Gläser.

Ohne »VACUUM« (ohne Spülen der Gläser) gehen nochmals 1000 Bestellungen ein, wie Listing 5 zeigt.

Die Tabelle ist gewachsen. Der Speicher der toten Tupel wurde also nicht wiederverwendet, stattdessen kamen neue Pages hinzu. Im Beispiel würde das bedeuten: Anstatt die schmutzigen Gläser abzuwaschen und wieder zu befüllen, wurden Gläser von neuen Paletten benutzt.