Reorganisieren ohne Lock

Natürlich könnte der Index einfach gelöscht und neu erstellt werden, aber »CREATE INDEX« lockt die Tabelle und kann lange dauern. Daneben gilt, während »CREATE INDEX« läuft, müssen Schreibzugriffe auf die Tabelle warten. Eine weitere Möglichkeit wäre »REINDEX« . Aber auch »REINDEX« blockiert Schreibzugriffe. Eine weitaus bessere Lösung bietet das Feature »CREATE INDEX CONCURRENTLY« . Es können zwar nicht mehrere solcher Pozesse zeitgleich laufen, sondern immer nur einer pro Tabelle, aber dafür wird nichts gelockt, und es kann auf der Tabelle weiterhin geschrieben werden. Zum Reorganisieren des Index legt man ihn mit »CREATE INDEX CONCURRENTLY« nochmal mit anderem Namen an. Anschließend kann der entartete Index mit »DROP INDEX« gelöscht werden, wonach auch der alte Name wieder für den neuen Index benutzt werden kann: »ALTER INDEX name_neu_angelegter RENAME TO name_alter « .

Bereinigt man die Indexe aus dem vorherigen Beispiel auf diese Weise, wird ein ziemlicher Unterschied erkennbar:

relname | relpages | reltuples
------------------+----------+-----------
 t_tabelle | 87 | 19457
 index_ff_50 | 99 | 19457
 index_ff_100 | 50 | 19457
 index_ff_default | 56 | 19457

Es ist einiges an Speicherplatz frei geworden und die Indexe können erstmal wieder eine Zeitlang vor sich hin wachsen.

Ist zu erwarten, dass viele Daten mit Werten dazukommen, die in der Mitte des Indexes angesiedelt werden, so ist es sinnvoll, einen kleineren Fillfactor zu verwenden, da dann Pages nicht so schnell geteilt werden. Ein Fillfactor von 100 Prozent ist nur dann sinnvoll, wenn wirklich sichergestellt werden kann, dass nicht in der Mitte eingefügt wird, sondern immer nur linear rechts.

Tiefe Einblicke

Mithilfe des PostgreSQL-Contrib-Moduls »pageinspect« lässt sich tiefer und genauer in die Indexpages schauen. Nach der Installation kann das Modul in PostgreSQL 9.1 mit :

CREATE EXTENSION pageinspect;

eingespielt werden. In älteren PostgreSQL-Versionen stellen die Co ntrib-Module nach der Installation eine SQL-Datei zur Verfügung, die hochgeladen werden muss.

Um nicht nur Nullen in der Metapage zu sehen, wird ein Blick in die Metapage eines Indexes über eine Integer-Spalte mit 110 000 Datensätzen geworfen:

SELECT root, level FROM bt_metap('hunderttausender_index');
-[ RECORD 1 ]-----
root | 290
level | 2

Die Spalte »level« gibt an, wie tief der Baum ist. Der Beispielbaum hat also eine Tiefe von 3 (Ebene 0, 1 und 2). Die Spalte »root« gibt an, in welcher Pagenummer sich die Wurzel befindet. Sie befindet sich in Page 290. Die Metapage ist Page 0, die erste Page des Baumes ist Page 1. Anhand der Statistik von Page 290 lässt sich noch mehr herausfinden ( Listing 3 ): In »blkno« ist die Pagenummer 290 gespeichert. Der »type« gibt an, ob es eine Root- (Wurzel-) oder eine Leave-Page (Blattpage) oder eine Page zwischen Wurzel- und Blattebene ist. Die Ebenen dazwischen bekommen die Abkürzung »i« für innerer Knoten. Page 290 hat Typ »r« für Root. Mit »live« - beziehungsweise »dead-items« wird angegeben, wie viele lebende oder tote Einträge die Page enthält. Die Page enthält also zwei Einträge und aus »free_size« geht hervor, dass noch 8116 Byte in der Page frei sind. Die Abkürzung »btpo« steht für B-Tree-Position. In der Spalte »btpo« ist angegeben, auf welcher Ebene sich die Page befindet. Sie ist auf Ebene 2, also auf der höchsten Ebene. In »btpo_prev« steht, welche Page sich links und in »btpo_next« welche Page sich rechts neben dieser Page befindet. Der Wert » « bedeutet hier, es gibt weder eine linke noch eine rechte Nachbarin. Da es immer nur genau eine Rootpage geben sollte, hat sie natürlich keine Nachbarinnen. Was steht denn in der Page?

SELECT itemoffset, ctid, data
FROM bt_page_items('hunderttausender_index',290);
 itemoffset | ctid | data
------------+---------+------------------
 1 | (3,1) |
 2 | (289,1) | 09 96 01 00 00 00

Der »itemoffset« zählt einfach die Einträge der Reihe nach durch. Da die Page eine Rootpage ist, sind die Datenwerte in »data« Schlüsselwerte. Der erste Wert in »data« ist leer und der zweite ist umgerechnet die Zahl 69928. Aus der Spalte »ctid« (Current Tuple ID) lässt sich entnehmen, dass alles kleiner gleich 69928 in Page 3 beginnend mit dem ersten Datensatz und alles größer gleich in Page 289 beginnend beim ersten Datensatz zu finden ist. Ein Blick in die Statistik von Page 3 verät noch mehr: Der Typ besagt, dass Page 3 ein innerer Knoten ist. Die Page beinhaltet 285 Datensätze, und es sind noch 2456 Byte frei. Die Page liegt auf Ebene 1. Ihre rechte Nachbarin ist Page 289. Da sie eine rechte Nachbarin hat, ist sie nicht die Wurzel, sondern ein innerer Knoten. Da sie keine linke Nachbarin hat und auf Ebene 1 liegt (Ebene 0 sind die Blätter), ist sie die Wurzel des Unterbaumes ganz links außen. Auch die einzelnen Einträge lassen sich mit »pageinspect« einsehen. Die Ausgabe kann lang sein. Page 3 hat 285 Einträge. Hier sind die obersten fünf Zeilen aus Page 3 ( Listing 4 ).