Materialized View
Vorberechnete Sicht für schnelle Abfragen
Die Idee und ihr Nutzen
Die Sachbearbeiter einer Versicherung rufen mehrmals am Tag die Übersicht „offene Schäden je Sachbearbeiter“ auf. Die Datenbank muss dafür bei jedem Aufruf alle Schäden durchsuchen, den Sachbearbeitern zuordnen und Summen bilden, und das dauert jedes Mal vier Sekunden. Stattdessen berechnet ein geplanter Lauf das Ergebnis regelmäßig vorab und speichert es als eigene Tabelle. Ein geplanter Lauf ist ein Programm, das zu festen Zeiten startet. Die Übersicht wird dann aus dieser Tabelle in unter einer Zehntelsekunde geladen. Eine solche gespeicherte, regelmäßig neu berechnete Tabelle heißt Materialized View, auf Deutsch gespeicherte Sicht. Eine Sicht, englisch View, ist in einer Datenbank eine gespeicherte Abfrage. Bei einer Materialized View speichert die Datenbank zusätzlich das Ergebnis der Abfrage.
Wie funktioniert es?
Der geplante Lauf startet alle zwei Minuten und berechnet die Materialized View jedes Mal vollständig neu. Für die Arbeitsplanung genügt ein Stand, der wenige Minuten alt ist. Um 9:00 Uhr verbindet der geplante Lauf die Schadentabelle über die Nummer des Sachbearbeiters mit der Bearbeitertabelle und rechnet je Sachbearbeiter Zahl und Summe der offenen Schäden. Diese beiden Tabellen sind die Grunddaten, also der maßgebliche Stand, aus dem die Materialized View berechnet wird. Der geplante Lauf speichert das Ergebnis als Materialized View mit dem Stand 9:00. Um 9:01 Uhr weist eine Teamleiterin einen Schaden der Sachbearbeiterin Frau Berg an den Sachbearbeiter Herrn Kurz um. Diese Änderung steht nur in den Grunddaten, noch nicht in der Materialized View. Um 9:01:30 Uhr öffnet Herr Kurz seine Übersicht. Die Übersicht liest aus der Materialized View, lädt in unter einer Zehntelsekunde und zeigt den umgewiesenen Schaden noch nicht. Oben zeigt die Übersicht „Stand 9:00“, damit jeder erkennt, wie aktuell die Zahlen sind. Um 9:02 Uhr beginnt der nächste geplante Lauf, und um 9:02:30 Uhr hat er die Materialized View durch den Stand 9:02 ersetzt. Um 9:03 Uhr öffnet Herr Kurz die Übersicht erneut und sieht den umgewiesenen Schaden.
Die Schadentabelle enthält mehrere Hunderttausend Schäden, die Bearbeitertabelle die Sachbearbeiter. Beide Tabellen liegen in einer relationalen Datenbank. In einer relationalen Datenbank haben Tabellen feste Spalten und werden über gemeinsame Felder miteinander verbunden. Dieselbe Datenbank bedient auch die laufende Sachbearbeitung, also das Anlegen, Ändern, Umweisen und Freigeben von Schäden. Die Materialized View enthält je Sachbearbeiter eine Zeile mit Zahl und Summe der offenen Schäden. Statt Hunderttausende Zeilen zu verbinden, holt die Übersicht wenige hundert fertige Zeilen. Wie schnell das tatsächlich geht, hängt von Datenmenge und Datenbank ab. Für verbindliche Entscheidungen liest die Anwendung der Sachbearbeiter dagegen weiter die Grunddaten. Eine verbindliche Entscheidung ist zum Beispiel, ob ein Sachbearbeiter einen bestimmten Schaden freigeben darf. Eine gewöhnliche Sicht führt ihre gespeicherte Abfrage dagegen bei jedem Lesen erneut aus und spart daher keine Zeit. Statt die Materialized View vollständig neu zu berechnen, kann der geplante Lauf sie auch schrittweise aktualisieren. Dabei rechnet der geplante Lauf nur die Schäden neu ein, die sich seit dem letzten Lauf geändert haben, etwa den umgewiesenen Schaden.
Scheitert ein Lauf, bleibt der zuletzt gespeicherte Stand stehen, bis ein späterer Lauf gelingt. An der Zeitangabe ist dann zu sehen, dass die Zahlen veraltet sind.
Materialized View: Übersicht aus einer vorberechneten Tabelle
Wann ist es sinnvoll und wann nicht?
Eine Materialized View lohnt sich
- Das Ergebnis einer Abfrage, die wie die Übersicht Hunderttausende Zeilen verbindet, wird oft gelesen, und die Grunddaten ändern sich im Vergleich dazu selten. Zwischen zwei Aufrufen der Übersicht hat sich meist kaum etwas geändert. Ohne Materialized View verbindet die Datenbank trotzdem bei jedem Aufruf erneut alle Zeilen.
- Viele Nutzer stellen dieselbe Abfrage. Weil 300 Sachbearbeiter die Übersicht mehrmals am Tag öffnen, führt die Datenbank ohne Materialized View die Abfrage Tausende Male aus. Die Datenbank ist dadurch zur Hälfte ausgelastet, andere Abfragen werden langsamer, und die Sachbearbeiter warten bei jedem Öffnen. Mit der Materialized View läuft die Abfrage nur noch alle zwei Minuten einmal.
- Berichte, Übersichten und Kennzahlen müssen nicht auf die Sekunde genau sein. Die Übersicht „offene Schäden je Sachbearbeiter“ dient der Arbeitsplanung, nicht der Entscheidung über einen einzelnen Schaden. Die Fachabteilung hat einem sichtbar gekennzeichneten, wenige Minuten alten Stand zugestimmt. Ohne Materialized View rechnet die Datenbank bei jedem Aufruf einen sekundengenauen Stand aus, den die Arbeitsplanung nicht braucht.
Eine Materialized View ist nicht das Richtige
- Jede Abfrage braucht den aktuellen Stand. Ein Beispiel ist die Prüfung, ob ein Sachbearbeiter einen bestimmten Schaden freigeben darf. Die Materialized View zeigt bis zum nächsten geplanten Lauf einen bis zu zwei Minuten alten Stand, und die Prüfung würde auf veralteten Zahlen entscheiden. Dann liest die Anwendung die Grunddaten direkt. Für diese Prüfung braucht es weder einen geplanten Lauf noch eine zusätzliche Tabelle.
- Die Grunddaten ändern sich ständig, und die Übersicht soll trotzdem aktuell sein. Der geplante Lauf müsste die aufwendige Abfrage in diesem Fall alle paar Sekunden ausführen, öfter, als die Sachbearbeiter die Übersicht öffnen. Dann lassen die Entwickler die Übersicht ohne Materialized View direkt aus den Grunddaten lesen. Die Datenbank führt die Abfrage so nur aus, wenn jemand die Übersicht öffnet, und rechnet nicht ständig auf Vorrat. Dafür wartet jeder Sachbearbeiter beim Öffnen wieder vier Sekunden.
- Die Abfrage lässt sich selbst beschleunigen, zum Beispiel durch einen passenden Index. Ein Index ist ein Verzeichnis, mit dem die Datenbank passende Zeilen findet, ohne die ganze Tabelle zu durchsuchen. Dann legen die Entwickler den Index an. Die Datenbank hält den Index bei jeder Änderung selbst aktuell. Reicht die Beschleunigung aus, zeigt die Übersicht immer den aktuellen Stand, und ein geplanter Lauf, der überwacht werden muss, entfällt.
- Ein Programm braucht dasselbe Ergebnis sehr oft und darf das Ergebnis eine Zeit lang in seinem eigenen Arbeitsspeicher halten. Auch mit Materialized View müsste das Programm für jeden Aufruf die Datenbank fragen. Dann holt das Programm das Ergebnis einmal aus der Datenbank und beantwortet weitere Aufrufe aus seinem Arbeitsspeicher, so wie bei Caching beschrieben. Jeder dieser Aufrufe spart den Weg zur Datenbank, und die Datenbank braucht keine zusätzliche Tabelle.
- Die Übersicht soll sich ändern, sobald sich ein Schaden ändert, nicht erst beim nächsten geplanten Lauf. Dann meldet die Anwendung der Sachbearbeiter jede Änderung an einem Schaden an ein eigenes Programm. Dieses Programm rechnet nur die gemeldete Änderung in eine Tabelle für die Übersicht ein. Die Tabelle kann auch in einer anderen Datenbank liegen. Diese Aufteilung beschreibt CQRS. Solche Tabellen heißen bei CQRS Lesetabellen. Ein Lauf, der alle zwei Minuten alle Schäden neu verbindet, entfällt.
Nachteile und typische Fehler
Die Materialized View kann bis zur nächsten Aktualisierung veraltet sein. Die Nutzer müssen das erkennen können, und die Fachabteilung muss der Verzögerung zustimmen. Die Aktualisierung braucht selbst Rechenzeit und kann bei großen Datenmengen die Datenbank belasten. Ein Lauf jede Minute statt alle zwei Minuten halbiert das Alter der Zahlen und verdoppelt die Zahl der Neuberechnungen. Die Materialized View belegt zusätzlichen Speicherplatz. Eine schrittweise Aktualisierung spart Rechenzeit, ist aber aufwendiger einzurichten, weil erkannt werden muss, welche Schäden sich geändert haben. Die Läufe müssen außerdem überwacht werden, damit ein gescheiterter Lauf auffällt.
Typische Fehler und woran man sie erkennt:
- Unbemerkt gescheiterte Läufe: Der geplante Lauf scheitert ab Montag um 10 Uhr bei jedem Start, zum Beispiel weil sein Passwort für die Datenbank abgelaufen ist. Niemand überwacht die Läufe. Die Materialized View behält den Stand von Montag 9:58 Uhr, und die Übersicht zeigt tagelang die Zahlen dieses Stands. Erkennbar ist das an einer Zeitangabe „Stand“, die deutlich älter ist als zwei Minuten.
- Verbindliche Entscheidung auf altem Stand: Ein Programm nutzt die Materialized View für eine Entscheidung, die den aktuellen Stand braucht. Ein Beispiel ist eine Obergrenze offener Schäden je Sachbearbeiter, ab der keine neuen Schäden mehr zugeteilt werden. Ein Programm, das neue Schäden zuteilt, liest dafür die Zahl offener Schäden aus der Materialized View. Herr Kurz hat laut Stand 9:00 noch 29 offene Schäden, die Obergrenze liegt bei 30. Bis zum nächsten Lauf um 9:02 Uhr zeigt die Materialized View weiter 29, und das Programm teilt Herrn Kurz in dieser Zeit fünf neue Schäden zu. Erkennbar ist das an Sachbearbeitern, die mehr offene Schäden haben, als die Obergrenze erlaubt.
- Gesperrte Übersicht: Manche Datenbanken sperren die Materialized View während einer vollständigen Neuberechnung für Leser, sofern die Neuberechnung nicht ausdrücklich parallel zum Lesen eingerichtet ist. Öffnet Herr Kurz die Übersicht um 9:02:10 Uhr, wartet die Übersicht 20 Sekunden, bis der Lauf um 9:02:30 Uhr fertig ist. Erkennbar ist das an langen Ladezeiten der Übersicht, die immer in die Laufzeit des geplanten Laufs fallen.
- Überlappende Läufe: Die Schadentabelle wächst, und die Neuberechnung dauert drei Minuten statt 30 Sekunden. Um 9:02 Uhr beginnt der nächste Lauf, obwohl der Lauf von 9:00 Uhr noch bis 9:03 Uhr rechnet. Beide Läufe verbinden dieselben Tabellen und bremsen einander, sodass jeder Lauf noch länger dauert. Um 9:04 Uhr kommt der nächste Lauf hinzu, während der Lauf von 9:02 Uhr noch nicht fertig ist. Erkennbar ist das an mehreren gleichzeitig laufenden Neuberechnungen und einer steigenden Auslastung der Datenbank.
Weiterführend
Verwandte Ansätze
- Data Warehouse: eine eigene Datenbank für Auswertungen, die für schnelle Berichte vorberechnete Summen nutzt.
- Change Data Capture: liest Änderungen aus dem Protokoll einer Datenbank und kann damit gespeicherte Sichten schrittweise nachführen.
Werkzeuge und Umsetzungen
- PostgreSQL: bietet Materialized Views, die auf Befehl vollständig neu berechnet werden. Unter bestimmten Voraussetzungen kann die Neuberechnung laufen, während andere Programme weiter aus der Tabelle lesen.
- ClickHouse: Datenbank für Auswertungen, deren Materialized Views beim Einfügen neuer Daten schrittweise mitberechnet werden.
- RisingWave: Datenbank für fortlaufende Datenströme, die Materialized Views laufend und schrittweise aktuell hält.
- pg_ivm: Erweiterung für PostgreSQL, die Materialized Views bei jeder Änderung der Grunddaten schrittweise nachführt.
Quellen
- Microsoft: Materialized View pattern: beschreibt, wann eine vorberechnete, nur lesbare Sicht hilft, wie sie geplant oder bei Änderungen aktualisiert wird, dass sie hinter den Grunddaten zurückliegen kann und dass ihr Alter überwacht werden sollte.
- PostgreSQL-Dokumentation: Materialized Views: Dokumentation einer Datenbank, die zeigt, wie eine Materialized View ihr Ergebnis wie eine Tabelle speichert, mit einem Befehl neu berechnet wird und zwischen zwei Neuberechnungen nicht den aktuellen Stand zeigen muss.