PostgreSQL^® 10 Bits - Verbesserter Block-Range-Index (BRIN)

Im Zuge der Veröffentlichung von PostgreSQL^® 9.5 wurde bereits einen Blogpost zum Thema veröffentlicht um eine grobe Einführung in „Block Rang INdexe“ (BRIN) sowie deren Vor- und Nachteile zu gegeben. Durch PostgreSQL^® 10.0 wird hier jedoch ein kleines Update nötig. Sollten Sie sich für eine Einführung in das Thema interessieren, empfiehlt es sich erst den ursprünglichen Post PostgreSQL^® 9.5: Block Range Index (BRIN) zu lesen und anschließend dieses Update.

Problem

Eines der Probleme bei Einsatz von BRIN ist, dass sich ändernde Tabellen die Qualität und damit Effektivität des Index verringern. Im Gegensatz zum Btree-Index, dem Standard in PostgreSQL^®, muss ein BRIN manuell gepflegt werden. Dies kann z.B. durch den Aufruf von REINDEX oder VACUUM FULL erreicht und durch Cron-Jobs automatisiert werden.

Diese Konstrukte sind jedoch problematisch, oder zumindest unschön und werden auch schnell vergessen. Daher sollten solche „Krücken“ immer mit einem entsprechenden Monitoring überwacht werden.

Lösung in PostgreSQL^® 10.0

Mit PostgreSQL^® 10.0 ändert sich die Situation jedoch. Die Erweiterungen sind selbstverständlich bereits in der Dokumentation erläutert (BRIN – Index Maintenance). Um die BRIN-Pflege zu automatisieren wurde VACUUM so erweitert, dass es beim bearbeiten einer Tabelle auch die zugehörigen Block-Range-Indizes aktualisieren kann. So wird die Aktualisierung sowohl beim manuellen als auch bei Autovacuum durchgeführt.

Anwendung

Wer bereits Block-Range-Indizes verwendet muss diese entsprechend anpassen. Damit VACUUM die Funktion übernimmt muss für jeden Index autosummarize gesetzt werden. Dies geschieht einfach beim CREATE durch den zusätzlichen Parameter autosummarize=true.

CREATE INDEX brin_test_time_auto_brin ON brin_test USING brin (TIME) WITH (autosummarize=true);
CREATE INDEX brin_test_time_auto_brin_128  ON brin_test USING brin (TIME) WITH (pages_per_range=128,  autosummarize=true);

Wenn Tabellen stark verkleinert werden und die dazugehörigen Indizes auch verkleinert werden sollen ist weiterhin ein VACUUM FULL notwendig. Auf Grund der geringen Größe der BRIN ist ein direkter Nutzen hier jedoch oft nicht gegeben.

Anwendertests

Um das Verhalten der Änderungen und die Auswirkungen besser zu verstehen, wurden von uns einige Tests durchgeführt. Hierbei wurde prompt ein Bug im geänderten Code entdeckt. Dieser führte zu einem Fehler, wenn ein BRIN mit abweichenden pages_per_range und autosummarize sowie CONCURRENTLY angelegt wurde. Trotz, dass es sich hier um eine Developmentversion handelt wurde der Fehler noch in der Nacht von der Community behoben.

Dies ist ein guter Anlass einmal darauf hinzuweisen, als interessierter PostgreSQL^®-User, gerade neue Features schon in einer frühen Phase ausgiebig zu testen.

PostgreSQL^® besitzt natürlich Regressiontests und auch das Fuzzingtool SQLsmith von unserem Kollegen Andreas Seltenreich trägt zur Qualitätssicherung bei. Jedoch sind ausgiebige Anwendertests immer sinnvoll und auch im eigenen Interesse der Nutzer. Aals Softwareentwickler bietet es sich zudem an, die eigene Anwendung hin und wieder gegen den aktuellen Stand der PostgreSQL^®-Entwicklung zu testen. Dies lässt sich natürlich auch gut in die eigene CI-Toolchain integrieren. So wird zusätzlich ganz nebenbei der Zeitaufwand, um die Kompatibilität mit dem nächsten Release sicher zu stellen, deutlich reduziert.

Weiterführende Links

• PostgreSQL^® 9.5: Block Range Index (BRIN)
• BRIN – Index Maintenance
• Segmentation fault when creating a BRIN
• Qualitätssicherung in PostgreSQL^® mit SQLsmith
• SQLsmith auf github