Zur Erinnerung, wir haben bereits Probleme im Zusammenhang mit der Isolation besprochen, einen Exkurs zur Datenstruktur auf niedriger Ebene gemacht und dann die Zeilenversionen untersucht und beobachtet, wie Datenschnappschüsse aus Zeilenversionen abgerufen werden.

Nun werden zwei eng miteinander verbundene Probleme behandelt: In-Page-Vakuum und HOT-Updates . Beide Techniken können auf Optimierungen bezogen werden; Sie sind wichtig, werden jedoch in der Dokumentation praktisch nicht behandelt.

In-Page-Vakuum während regelmäßiger Updates

Wenn PostgreSQL beim Zugriff auf eine Seite für ein Update oder einen Lesevorgang feststellt, dass auf der Seite nicht mehr genügend Speicherplatz vorhanden ist, kann ein schnelles Unterdrucken der Seite durchgeführt werden. Dies geschieht in beiden Fällen:

1. In einem vorherigen Update auf dieser Seite wurde nicht genügend Speicherplatz gefunden, um eine neue Zeilenversion auf derselben Seite zuzuweisen. Eine solche Situation wird im Seitenkopf gespeichert und beim nächsten Mal wird die Seite gesaugt.
2. Die Seite ist zu mehr als fillfactor Prozent fillfactor . In diesem Fall wird sofort abgesaugt, ohne bis zum nächsten zu warten.

fillfactor ist ein Speicherparameter, der für eine Tabelle (und für einen Index) definiert werden kann. PostgresSQL fügt nur dann eine neue Zeile in eine Seite ein, wenn die Seite zu weniger als fillfactor Prozent fillfactor . Der verbleibende Speicherplatz ist für neue Tupel reserviert, die aufgrund von Aktualisierungen erstellt werden. Der Standardwert für Tabellen ist 100, dh, es ist kein Speicherplatz reserviert (und der Standardwert für Indizes ist 90).

In-Page-Vakuum löscht Tupel, die in keinem Snapshot sichtbar sind (jenseits des zuletzt diskutierten Transaktionshorizonts der Datenbank), dies jedoch ausschließlich innerhalb einer Tabellenseite. Zeiger auf vakuumierte Tupel werden nicht freigegeben, da sie aus Indizes referenziert werden können und sich ein Index auf einer anderen Seite befindet. Das In-Page-Vakuum reicht nie über eine Tabellenseite hinaus, sondern arbeitet sehr schnell.

Aus den gleichen Gründen wird die Karte mit dem freien Speicherplatz nicht aktualisiert. Dies reserviert auch den zusätzlichen Platz für Aktualisierungen und nicht für Einfügungen. Die Sichtbarkeitskarte wird ebenfalls nicht aktualisiert.

Die Tatsache, dass eine Seite während des Lesens gesaugt werden kann, bedeutet, dass eine SELECT-Abfrage einen Seitenwechsel nach sich ziehen kann. Dies ist ein weiterer Fall wie dieser, zusätzlich zu einer verzögerten Änderung von Hinweisbits, die zuvor erörtert wurde.

Betrachten wir ein Beispiel, wie es funktioniert. Lassen Sie uns eine Tabelle und Indizes für beide Spalten erstellen.

 => CREATE TABLE hot(id integer, s char(2000)) WITH (fillfactor = 75); => CREATE INDEX hot_id ON hot(id); => CREATE INDEX hot_s ON hot(s); 

Wenn in der Spalte s nur lateinische Zeichen gespeichert sind, belegt jede Zeilenversion 2004 Byte plus 24 Byte eines Headers. Wir setzen den fillfactor Speicherparameter auf 75%, was gerade genug Platz für drei Zeilen reserviert.

Um sich den Inhalt der Tabellenseite bequem anzusehen, erstellen wir eine bereits bekannte Funktion neu, indem wir der Ausgabe zwei weitere Felder hinzufügen:

 => CREATE FUNCTION heap_page(relname text, pageno integer) RETURNS TABLE(ctid tid, state text, xmin text, xmax text, hhu text, hot text, t_ctid tid) AS $$ SELECT (pageno,lp)::text::tid AS ctid, CASE lp_flags WHEN 0 THEN 'unused' WHEN 1 THEN 'normal' WHEN 2 THEN 'redirect to '||lp_off WHEN 3 THEN 'dead' END AS state, t_xmin || CASE WHEN (t_infomask & 256) > 0 THEN ' (c)' WHEN (t_infomask & 512) > 0 THEN ' (a)' ELSE '' END AS xmin, t_xmax || CASE WHEN (t_infomask & 1024) > 0 THEN ' (c)' WHEN (t_infomask & 2048) > 0 THEN ' (a)' ELSE '' END AS xmax, CASE WHEN (t_infomask2 & 16384) > 0 THEN 't' END AS hhu, CASE WHEN (t_infomask2 & 32768) > 0 THEN 't' END AS hot, t_ctid FROM heap_page_items(get_raw_page(relname,pageno)) ORDER BY lp; $$ LANGUAGE SQL; 

Erstellen wir auch eine Funktion, um in die Indexseite zu schauen:

 => CREATE FUNCTION index_page(relname text, pageno integer) RETURNS TABLE(itemoffset smallint, ctid tid) AS $$ SELECT itemoffset, ctid FROM bt_page_items(relname,pageno); $$ LANGUAGE SQL; 

Lassen Sie uns überprüfen, wie In-Page-Vakuum funktioniert. Dazu fügen wir eine Zeile ein und ändern sie mehrmals:

 => INSERT INTO hot VALUES (1, 'A'); => UPDATE hot SET s = 'B'; => UPDATE hot SET s = 'C'; => UPDATE hot SET s = 'D'; 

Die Seite enthält jetzt vier Tupel:

 => SELECT * FROM heap_page('hot',0); 

  ctid | state | xmin | xmax | hhu | hot | t_ctid -------+--------+----------+----------+-----+-----+-------- (0,1) | normal | 3979 (c) | 3980 (c) | | | (0,2) (0,2) | normal | 3980 (c) | 3981 (c) | | | (0,3) (0,3) | normal | 3981 (c) | 3982 | | | (0,4) (0,4) | normal | 3982 | 0 (a) | | | (0,4) (4 rows) 

Wie erwartet haben wir gerade den fillfactor Schwellenwert überschritten. Dies geht aus dem Unterschied zwischen dem pagesize und den upper Werten hervor: Es überschreitet den Schwellenwert, der 75% der Seitengröße entspricht, was 6144 Bytes ergibt.

 => SELECT lower, upper, pagesize FROM page_header(get_raw_page('hot',0)); 

  lower | upper | pagesize -------+-------+---------- 40 | 64 | 8192 (1 row) 

Wenn das nächste Mal auf die Seite zugegriffen wird, muss ein In-Page-Vakuum auftreten. Lassen Sie uns das überprüfen.

 => UPDATE hot SET s = 'E'; => SELECT * FROM heap_page('hot',0); 

  ctid | state | xmin | xmax | hhu | hot | t_ctid -------+--------+----------+-------+-----+-----+-------- (0,1) | dead | | | | | (0,2) | dead | | | | | (0,3) | dead | | | | | (0,4) | normal | 3982 (c) | 3983 | | | (0,5) (0,5) | normal | 3983 | 0 (a) | | | (0,5) (5 rows) 

Alle toten Tupel (0,1), (0,2) und (0,3) werden abgesaugt; danach wird ein neues Tupel (0.5) in den freigegebenen Raum eingefügt.

Die Tupel, die das Staubsaugen überstanden haben, werden physisch zu hohen Adressen der Seite verschoben, sodass der gesamte freie Speicherplatz durch einen zusammenhängenden Bereich dargestellt wird. Die Werte der Zeiger werden entsprechend geändert. Dadurch entstehen keine Probleme mit der Fragmentierung des freien Speicherplatzes auf einer Seite.

Zeiger auf vakuumierte Tupel können nicht freigegeben werden, da sie auf der Indexseite referenziert sind. hot_s wir einen Blick auf die erste Seite des hot_s Index (da Seite Null von Metainformationen belegt ist):

 => SELECT * FROM index_page('hot_s',1); 

  itemoffset | ctid ------------+------- 1 | (0,1) 2 | (0,2) 3 | (0,3) 4 | (0,4) 5 | (0,5) (5 rows) 

Das gleiche Bild sehen wir auch im anderen Index:

 => SELECT * FROM index_page('hot_id',1); 

  itemoffset | ctid ------------+------- 1 | (0,5) 2 | (0,4) 3 | (0,3) 4 | (0,2) 5 | (0,1) (5 rows) 

Sie werden vielleicht bemerken, dass Zeiger auf Tabellenzeilen in umgekehrter Reihenfolge folgen, aber das macht keinen Unterschied, da alle Tupel den gleichen Wert haben: id = 1. Im vorherigen Index sind die Zeiger jedoch nach den Werten von s geordnet, und dies ist von wesentlicher Bedeutung.

Beim Indexzugriff kann PostgreSQL (0,1), (0,2) oder (0,3) als Tupel-IDs erhalten. Anschließend wird versucht, die entsprechende Zeilenversion von der Tabellenseite abzurufen. Aufgrund des Status "tot" des Zeigers stellt PostgreSQL jedoch fest, dass eine solche Version nicht mehr vorhanden ist, und ignoriert sie. (Nachdem PostgreSQL einmal festgestellt hat, dass die Version einer Tabellenzeile nicht verfügbar ist, ändert es den Zeigerstatus auf der Indexseite, um nicht mehr auf die Tabellenseite zuzugreifen.)

Es ist wichtig, dass In-Page-Vakuum nur innerhalb einer Tabellenseite funktioniert und keine Indexseiten saugt.

HEISSE Updates

Warum ist es nicht sinnvoll, Verweise auf alle Zeilenversionen im Index zu speichern?

Bei jeder Änderung der Zeile müssen zunächst alle für die Tabelle erstellten Indizes aktualisiert werden: Sobald eine neue Version erstellt wurde, muss auf diese verwiesen werden. Und das müssen wir auf jeden Fall tun, auch wenn die Felder geändert werden, die nicht indiziert sind. Dies ist offensichtlich nicht sehr effizient.

Zweitens akkumulieren Indizes Verweise auf historische Tupel, die dann zusammen mit den Tupeln selbst abgesaugt werden müssen (wir werden etwas später diskutieren, wie dies gemacht wird).

Darüber hinaus hat B-Tree in PostgreSQL die Implementierungsspezifikationen. Wenn auf einer Indexseite nicht genügend Platz zum Einfügen einer neuen Zeile vorhanden ist, wird die Seite zweigeteilt und alle Daten zwischen ihnen verteilt. Dies wird als Seitenaufteilung bezeichnet. Wenn jedoch Zeilen gelöscht werden, werden die beiden Indexseiten nicht zu einer zusammengeführt. Aus diesem Grund kann die Indexgröße möglicherweise nicht verringert werden, selbst wenn ein erheblicher Teil der Daten gelöscht wird.

Je mehr Indizes für eine Tabelle erstellt werden, desto komplexer wird es natürlich.

Wenn jedoch ein Wert in einer Spalte geändert wird, die überhaupt nicht indiziert ist, ist es nicht sinnvoll, eine zusätzliche B-Baum-Zeile zu erstellen, die denselben Wert des Schlüssels enthält. Genau so funktioniert die Optimierung mit dem Namen HOT Update (Heap-Only Tuple Update).

Während dieses Updates enthält die Indexseite nur eine Zeile, die auf die allererste Version der Zeile auf der Tabellenseite verweist. Und es ist bereits in der Tabellenseite, dass eine Kette von Tupeln organisiert ist:

• Aktualisierte Zeilen in der Kette sind mit dem Bit Heap Hot Updated gekennzeichnet.
• Zeilen, auf die nicht aus dem Index verwiesen wird, sind mit dem Bit Heap Only Tuple gekennzeichnet.
• Zeilenversionen werden wie gewohnt über das Feld ctid verknüpft.

Wenn PostgreSQL während des Index-Scans auf eine Tabellenseite zugreift und ein Tupel mit der Bezeichnung Heap Hot Updated findet, sollte es nicht aufhören, sondern der HOT-Kette folgen, wobei jedes Tupel darin berücksichtigt wird. Natürlich wird für alle auf diese Weise erhaltenen Tupel die Sichtbarkeit überprüft, bevor sie an den Client zurückgegeben werden.

Um zu beobachten, wie ein HOT-Update funktioniert, löschen wir einen Index und löschen die Tabelle.

 => DROP INDEX hot_s; => TRUNCATE TABLE hot; 

Jetzt wiederholen wir das Einfügen und Aktualisieren einer Zeile.

 => INSERT INTO hot VALUES (1, 'A'); => UPDATE hot SET s = 'B'; 

Und das sehen wir auf der Tabellenseite:

 => SELECT * FROM heap_page('hot',0); 

  ctid | state | xmin | xmax | hhu | hot | t_ctid -------+--------+----------+-------+-----+-----+-------- (0,1) | normal | 3986 (c) | 3987 | t | | (0,2) (0,2) | normal | 3987 | 0 (a) | | t | (0,2) (2 rows) 

Es gibt eine Reihe von Änderungen auf der Seite:

• Das Flag Heap Hot Updated gibt an, dass die ctid Kette ctid muss.
• Das Heap Only Tuple-Flag gibt an, dass dieses Tupel nicht aus Indizes referenziert wird.

Die Kette wächst (innerhalb der Seite) mit weiteren Änderungen:

 => UPDATE hot SET s = 'C'; => UPDATE hot SET s = 'D'; => SELECT * FROM heap_page('hot',0); 

  ctid | state | xmin | xmax | hhu | hot | t_ctid -------+--------+----------+----------+-----+-----+-------- (0,1) | normal | 3986 (c) | 3987 (c) | t | | (0,2) (0,2) | normal | 3987 (c) | 3988 (c) | t | t | (0,3) (0,3) | normal | 3988 (c) | 3989 | t | t | (0,4) (0,4) | normal | 3989 | 0 (a) | | t | (0,4) (4 rows) 

Der Index enthält jedoch nur einen Verweis auf den Kopf der Kette:

 => SELECT * FROM index_page('hot_id',1); 

  itemoffset | ctid ------------+------- 1 | (0,1) (1 row) 

Hervorzuheben ist, dass HOT-Aktualisierungen in dem Fall funktionieren, in dem die zu aktualisierenden Felder überhaupt nicht indiziert sind. Andernfalls würde ein Index einen Verweis direkt auf eine neue Zeilenversion enthalten, was mit dem Konzept dieser Optimierung nicht kompatibel ist.

Die Optimierung funktioniert nur auf einer Seite. Daher benötigt ein zusätzlicher Durchgang durch die Kette keinen Zugriff auf andere Seiten und beeinträchtigt die Leistung nicht.

In-Page-Vakuum während HOT-Updates

Das Staubsaugen während HOT-Updates ist ein spezieller, aber wichtiger Fall von In-Page-Vakuum.

Nach wie vor haben wir den fillfactor Schwellenwert bereits überschritten, daher muss das nächste Update ein In-Page-Vakuum verursachen. Aber dieses Mal gibt es eine Reihe von Updates auf der Seite. Der Kopf dieser HOT-Kette muss immer dort bleiben, wo er ist, da auf ihn vom Index verwiesen wird, während der Rest der Zeiger freigegeben werden kann: Es ist bekannt, dass sie keine Referenzen von außen haben.

Um den Kopfzeiger nicht zu berühren, wird eine indirekte Adressierung verwendet: Der Zeiger, auf den der Index (in diesem Fall 0,1) verweist, erhält den Status "Weiterleiten", der zum entsprechenden Tupel weiterleitet.

 => UPDATE hot SET s = 'E'; => SELECT * FROM heap_page('hot',0); 

  ctid | state | xmin | xmax | hhu | hot | t_ctid -------+---------------+----------+-------+-----+-----+-------- (0,1) | redirect to 4 | | | | | (0,2) | normal | 3990 | 0 (a) | | t | (0,2) (0,3) | unused | | | | | (0,4) | normal | 3989 (c) | 3990 | t | t | (0,2) (4 rows) 

Beachten Sie, dass:

• Die Tupel (0,1), (0,2) und (0,3) wurden abgesaugt.
• Der Kopfzeiger (0,1) bleibt erhalten, hat jedoch den Status "Weiterleiten" erhalten.
• Die neue Zeilenversion hat (0,2) überschrieben, da es mit Sicherheit keine Verweise auf dieses Tupel gab und der Zeiger freigegeben wurde (Status "unbenutzt").

Lass uns ein Update mehrmals durchführen:

 => UPDATE hot SET s = 'F'; => UPDATE hot SET s = 'G'; => SELECT * FROM heap_page('hot',0); 

  ctid | state | xmin | xmax | hhu | hot | t_ctid -------+---------------+----------+----------+-----+-----+-------- (0,1) | redirect to 4 | | | | | (0,2) | normal | 3990 (c) | 3991 (c) | t | t | (0,3) (0,3) | normal | 3991 (c) | 3992 | t | t | (0,5) (0,4) | normal | 3989 (c) | 3990 (c) | t | t | (0,2) (0,5) | normal | 3992 | 0 (a) | | t | (0,5) (5 rows) 

Das nächste Update führt erneut zu In-Page-Vacuuming:

 => UPDATE hot SET s = 'H'; => SELECT * FROM heap_page('hot',0); 

  ctid | state | xmin | xmax | hhu | hot | t_ctid -------+---------------+----------+-------+-----+-----+-------- (0,1) | redirect to 5 | | | | | (0,2) | normal | 3993 | 0 (a) | | t | (0,2) (0,3) | unused | | | | | (0,4) | unused | | | | | (0,5) | normal | 3992 (c) | 3993 | t | t | (0,2) (5 rows) 

Auch hier werden einige Tupel abgesaugt und der Zeiger auf den Kopf der Kette entsprechend bewegt.

Fazit: Wenn Spalten, die nicht indiziert sind, häufig aktualisiert werden, ist es möglicherweise sinnvoll, den Parameter fillfactor zu reduzieren, um Seitenbereich für Aktualisierungen zu reservieren. Wir sollten jedoch berücksichtigen, dass je kleiner der fillfactor , desto mehr freier Speicherplatz auf einer Seite fillfactor , sodass die physische Größe der Tabelle zunimmt.

Bruch einer heißen Kette

Wenn auf der Seite nicht genügend Speicherplatz für die Zuweisung eines neuen Tupels vorhanden ist, wird die Kette unterbrochen. Und wir müssen einen separaten Verweis vom Index auf die Zeilenversion auf einer anderen Seite erstellen.

Um diese Situation zu reproduzieren, starten wir eine gleichzeitige Transaktion und erstellen den Datenschnappschuss.

 | => BEGIN ISOLATION LEVEL REPEATABLE READ; | => SELECT count(*) FROM hot; 

 | count | ------- | 1 | (1 row) 

Mit dem Schnappschuss können die Tupel auf der Seite nicht abgesaugt werden. Jetzt machen wir ein Update in der ersten Sitzung:

 => UPDATE hot SET s = 'I'; => UPDATE hot SET s = 'J'; => UPDATE hot SET s = 'K'; => SELECT * FROM heap_page('hot',0); 

  ctid | state | xmin | xmax | hhu | hot | t_ctid -------+---------------+----------+----------+-----+-----+-------- (0,1) | redirect to 2 | | | | | (0,2) | normal | 3993 (c) | 3994 (c) | t | t | (0,3) (0,3) | normal | 3994 (c) | 3995 (c) | t | t | (0,4) (0,4) | normal | 3995 (c) | 3996 | t | t | (0,5) (0,5) | normal | 3996 | 0 (a) | | t | (0,5) (5 rows) 

Beim nächsten Update hat die Seite nicht genügend Speicherplatz, aber das In-Page-Vakuum kann nichts wegsaugen:

 => UPDATE hot SET s = 'L'; 

 | => COMMIT; -- snapshot no longer needed 

 => SELECT * FROM heap_page('hot',0); 

  ctid | state | xmin | xmax | hhu | hot | t_ctid -------+---------------+----------+----------+-----+-----+-------- (0,1) | redirect to 2 | | | | | (0,2) | normal | 3993 (c) | 3994 (c) | t | t | (0,3) (0,3) | normal | 3994 (c) | 3995 (c) | t | t | (0,4) (0,4) | normal | 3995 (c) | 3996 (c) | t | t | (0,5) (0,5) | normal | 3996 (c) | 3997 | | t | (1,1) (5 rows) 

Im Tupel (0,5) befindet sich ein Verweis auf (1,1) auf Seite 1.

 => SELECT * FROM heap_page('hot',1); 

  ctid | state | xmin | xmax | hhu | hot | t_ctid -------+--------+------+-------+-----+-----+-------- (1,1) | normal | 3997 | 0 (a) | | | (1,1) (1 row) 

Der Index enthält jetzt zwei Zeilen, die jeweils auf den Anfang der HOT-Kette verweisen:

 => SELECT * FROM index_page('hot_id',1); 

  itemoffset | ctid ------------+------- 1 | (1,1) 2 | (0,1) (2 rows) 

Leider fehlen in der Dokumentation praktisch Informationen zu In-Page-Vakuum- und HOT-Updates, und Sie sollten im Quellcode nach Antworten suchen. Ich rate Ihnen, mit README.HOT zu beginnen.

Lesen Sie weiter .