Home › Blog › Entwicklung › Materialisierte Views mit PostgreSQL

Materialisierte Views mit PostgreSQL

Veröffentlicht am 10. April 2011 von Tom Thaler

Heute geht es um ein sehr interessante Thema, und zwar materialisierte Sichten (engl. “materialized view”) und wie diese mit PostgreSQL umgesetzt werden können. Doch zunächst sollte mal geklärt, was überhaupt (materialisierte) Sichten sind, und wozu sie gebraucht werden.

Eine Sicht ist eine logische Relation (auch virtuelle Relation oder virtuelle Tabelle) in einem Datenbanksystem, welche durch eine im Datenbankmanagementsystem (DBMS) gespeicherte Abfrage definiert wird. Der Datenbankbenutzer kann eine Sicht wie eine normale Tabelle abfragen (je nach Sicht können allerdings keine Änderungen erfolgen), wobei diese immer zuvor durch das DBMS berechnet wird. (Quelle: Wikipedia). Bei einer materialisierten Sicht handelt es sich darüber hinaus um eine in der Datenbank abgelegte Kopie einer Sicht zu einem bestimmten Zeitpunkt. Sie fungieren als Cache, um Zugriffe zu beschleunigen und die Netzwerklast zu verringern (Quelle: Materialzied Views: Techniques, Implementations, and Applications, Oracle9i Advanced Replication).

Prinzipiell werden folgende materialisierte Sichten unterschieden:

  • Snapshot: Werden ausschließlich manuell aktualisiert. Sehr einfach zu implementierung, allerdings sehr teuer.
  • Eager: Werden immer dann aktualisiert, wenn ein Datensatz, welcher die Sicht verändert, hinzugefügt, geändert oder gelöscht wird. Die Materialisierte Sicht bleibt dadurch aktuell, die Aktualisierungs-Kosten (welche in der Regel sehr gering sind) werden hierdurch allerdings versteckt auf den Aktualisierenden übertragen.
  • Lazy: Werden immer dann aktualisiert, wenn eine Transaktion committet. Ähnliches Verhalten wie bei der Eager Sicht, allerdings wird hierbei die Verwendung von Transaktionen in der Anwendung vorausgesetzt.
  • Very Lazy: Funktionieren im Wesentlichen wie Snapshot, mit dem Unterschied, dass Änderungen mitprotokolliert werden, was die Aktualisierung beschleunigt. Wie beim Snapshot ist die Materialisierte Sicht in der Regel nicht aktuell, die Aktualisierungkosten werden allerdings auf den Refresh und den Aktualisierenden verteilt.

In diesem Beitrag möchte ich mich aus mehreren Gründen speziell auf die Eager Materialized View und die Umsetzung mit PostgreSQL beschränken. Zunächst einmal kann diese View in eigentlich allen Anwendungen eingesetzt werden, unabhängig davon, ob Transaktionen verwendet werden oder nicht. Weiterhin ist diese Sicht in der Regel konsistent mit dem Datenbestand, sodass mit ihr unzählige alltägliche Probleme bearbeitet werden können. Auf die Umsetzung mit PostgreSQL möchte ich einerseits aus persönlichen Beweggründen (Erfahrungen der letzten Woche) und wegen des Open-Source Status eingehen. Die Basis für die nachfolgenden Ausführungen liefert ein Jonathan Gardner Beitrag in englischer Sprache, welche auch die Umsetzung der anderen Varianten behandelt.

Für die (Eager) Materialized View benötigen wir folgende Komponenten:

  1. Eine Relation, welche Informationen über die materialisierten Sichten hält.
  2. Einigen Funktionen für den Umgang mit Materialisierten Views (create, drop, refresh)
  3. Eine Basis-Sicht, auf welcher die materialisierte Sicht aufbaut.
  4. Die Materialisierte Sicht
  5. Zwei Funktionen für die konkrete materialisierte Sicht, welche einerseits ein Refresh auf einen einzelnen Datensatz der materialisierten Sicht durchführt und andererseits die Informationstabelle der Materialisierten Views aktualisiert.
  6. Trigger für die Relationen, auf welchen die View basiert.

1. Eine Relation, welche Informationen über die materialisierten Sichten hält.

Zunächst benötigen wir eine Tabelle, welche Informationen über die später angelegten materialisierten Sichten enthält. Konkret wird hierin der Name der Materialisierten Sicht (mv_name), der Name der Basis-Sicht (v_name) und der Zeitpunkt des letzten Refreshs (last_refresh) gehalten. Demnach erstellen wir die Ralation wie folgt:

CREATE TABLE matviews (
   mv_name NAME NOT NULL PRIMARY KEY
   , v_name NAME NOT NULL
   , last_refresh TIMESTAMP WITH TIME ZONE
);

2. Einigen Funktionen für den Umgang mit Materialisierten Views (create, drop, refresh)

Dem Namen nach wollen wir nun drei Funktionen erstellen, welche uns den Umgang mit materialisierten Sichten erleichern. Eine Funktion zum Erstellen einer materialisierten View (create_matview), eine Funktion zum Löschen einer Materialisierten View (drop_matview) und eine Funktion zum Auffrischen einer Materialisierten View (refresh_matview). Die nachfolgenden Funktionen können 1:1 übernommen werden.

create_matview

CREATE OR REPLACE FUNCTION create_matview(NAME, NAME)
RETURNS VOID
SECURITY DEFINER
LANGUAGE plpgsql AS '
DECLARE
matview ALIAS FOR $1;
view_name ALIAS FOR $2;
entry matviews%ROWTYPE;
BEGIN
SELECT * INTO entry FROM matviews WHERE mv_name = matview;
IF FOUND THEN
RAISE EXCEPTION ''Materialized view ''''%'''' already exists.'',
matview;
END IF;
EXECUTE ''REVOKE ALL ON '' || view_name || '' FROM PUBLIC'';
EXECUTE ''GRANT SELECT ON '' || view_name || '' TO PUBLIC'';
EXECUTE ''CREATE TABLE '' || matview || '' AS SELECT * FROM '' || view_name;
EXECUTE ''REVOKE ALL ON '' || matview || '' FROM PUBLIC'';
EXECUTE ''GRANT SELECT ON '' || matview || '' TO PUBLIC'';
INSERT INTO matviews (mv_name, v_name, last_refresh)
VALUES (matview, view_name, CURRENT_TIMESTAMP);
RETURN;
 END';

drop_matview

CREATE OR REPLACE FUNCTION drop_matview(NAME) RETURNS VOID
SECURITY DEFINER
LANGUAGE plpgsql AS '
DECLARE
matview ALIAS FOR $1;
entry matviews%ROWTYPE;
BEGIN
SELECT * INTO entry FROM matviews WHERE mv_name = matview;
IF NOT FOUND THEN
RAISE EXCEPTION ''Materialized view % does not exist.'', matview;
END IF;
EXECUTE ''DROP TABLE '' || matview;
DELETE FROM matviews WHERE mv_name=matview;
RETURN;
 END';

refresh_matview

CREATE OR REPLACE FUNCTION refresh_matview(name) RETURNS VOID
SECURITY DEFINER
LANGUAGE plpgsql AS '
DECLARE
matview ALIAS FOR $1;
entry matviews%ROWTYPE;
BEGIN
SELECT * INTO entry FROM matviews WHERE mv_name = matview;
IF NOT FOUND THEN
RAISE EXCEPTION ''Materialized view % does not exist.'', matview;
 END IF;
EXECUTE ''DELETE FROM '' || matview;

EXECUTE ''INSERT INTO '' || matview
|| '' SELECT * FROM '' || entry.v_name;

UPDATE matviews
SET last_refresh=CURRENT_TIMESTAMP
WHERE mv_name=matview;
RETURN;
END';

3. Eine Basis-Sicht, auf welcher die materialisierte Sicht aufbaut.

Nun benötigen wir die Basis-Sicht, welche die Grundlage für unsere Materialisierte Sicht bildet. An dieser Stelle wird ein Beispiel verwendet um das gesamte Vorgehen zu demonstrieren. Wie verwenden hierbei drei bereits vorhandenen Tabellen (a, b und c) und erstellen daraus unsere Sicht (b_v).

Aufbau der Basis-Tabellen

CREATE TABLE a (
a_id INT PRIMARY KEY,
v INT
);

CREATE TABLE b (
b_id INT PRIMARY KEY,
a_id INT REFERENCES a,
v INT,
expires TIMESTAMP
);

CREATE TABLE c (
c_id INT PRIMARY KEY,
b_id INT REFERENCES b,
v INT
);

Erstellung der Basis-Sicht

CREATE VIEW b_v AS
SELECT b.b_id AS b_id,
a.v AS a_v,
b.v AS b_v,
sum(c.v) AS sum_c_v
FROM a JOIN b USING (a_id) JOIN c USING (b_id)
WHERE (b.expires IS NULL OR b.expires >= now())
GROUP BY b.b_id, a.v, b.v;

4. Die Materialisierte Sicht

Mithilfe unserer im Vorfeld erstellten Funktionen können wir nun mit einem kurzen Befehl die Relation für die materialisierte Sicht erstellen. An dieser Stelle sei darauf hingewiesen, dass wir nach diesem Schritt bei einem “Snapshot” angekommen sind. Erst die Durchführung von Schritt 5 und 6 führen zur “Eager”-Funktionalität!

SELECT create_matview('b_mv', 'b_v');

5. Zwei Funktionen für die konkrete materialisierte Sicht, welche einerseits ein Refresh auf einen einzelnen Datensatz der materialisierten Sicht durchführt und andererseits die Informationstabelle der Materialisierten Views aktualisiert.

Nun benötigen wir eine Funktion, welche eine Update auf einen Datensatz in der materialisierten View durchführt.

CREATE FUNCTION b_mv_refresh_row(b_mv.b_id%TYPE) RETURNS VOID
SECURITY DEFINER
LANGUAGE 'plpgsql' AS '
BEGIN
DELETE FROM b_mv WHERE b_id = $1;
INSERT INTO b_mv SELECT * FROM b_v WHERE b_id = $1;
RETURN;
END';

Weiterhin sollte natürlich auch die Tabelle “matviews” aktualisiert werden, wozu wir folgende Funktion deklarieren.

CREATE FUNCTION b_mv_refresh() RETURNS VOID
SECURITY DEFINER
LANGUAGE 'plpgsql' AS '
BEGIN
PERFORM b_mv_refresh_row(b_id)
FROM b, matviews
WHERE matviews.mv_name = ''b_mv''
AND b.expires >= matviews.last_refresh
AND b.expires < now();

UPDATE matviews
SET last_refresh = now()
WHERE mv_name = ''b_mv'';

RETURN;
END';

6. Trigger für die Relationen, auf welchen die View basiert.

Die Trigger stellen nun den wesentlichen Aufwand zur Erstellung der Eager Materialized View dar, allerdings steckt hierin auch die gesamte Magie. Diese Trigger bestimmen, wann ein Update der materialisierten View erfolgt und damit auch die Kosten für den Anwender. Diese Trigger müssen immer auf das zu lösende Problem ausgerichtet werden.

-- Trigger für Tabelle a
CREATE FUNCTION b_mv_a_ut() RETURNS TRIGGER
SECURITY DEFINER LANGUAGE 'plpgsql' AS '
BEGIN
IF OLD.a_id = NEW.a_id THEN
PERFORM b_mv_refresh_row(b.b_id) FROM b WHERE b.a_id = NEW.a_id;
ELSE
PERFORM b_mv_refresh_row(b.b_id) FROM b WHERE b.a_id = OLD.a_id;
PERFORM b_mv_refresh_row(b.b_id) FROM b WHERE b.a_id = NEW.a_id;
END IF;
RETURN NULL;
END';
CREATE TRIGGER b_mv_ut AFTER UPDATE ON a
FOR EACH ROW EXECUTE PROCEDURE b_mv_a_ut();
CREATE FUNCTION b_mv_a_dt() RETURNS TRIGGER
SECURITY DEFINER LANGUAGE 'plpgsql' AS '
BEGIN
PERFORM b_mv_refresh_row(b.b_id) FROM b WHERE b.a_id = OLD.a_id;
RETURN NULL;
END';
CREATE TRIGGER b_mv_dt AFTER DELETE ON a
FOR EACH ROW EXECUTE PROCEDURE b_mv_a_dt();
CREATE FUNCTION b_mv_a_it() RETURNS TRIGGER
SECURITY DEFINER LANGUAGE 'plpgsql' AS '
BEGIN
PERFORM b_mv_refresh_row(b.b_id) FROM b WHERE b.a_id = NEW.a_id;
RETURN NULL;
END';
CREATE TRIGGER b_mv_it AFTER INSERT ON a
FOR EACH ROW EXECUTE PROCEDURE b_mv_a_it();

-- Trigger für Tabelle b
CREATE FUNCTION b_mv_b_ut() RETURNS TRIGGER
SECURITY DEFINER LANGUAGE 'plpgsql' AS '
BEGIN
IF OLD.b_id = NEW.b_id THEN
PERFORM b_mv_refresh_row(NEW.b_id);
ELSE
PERFORM b_mv_refresh_row(OLD.b_id);
PERFORM b_mv_refresh_row(NEW.b_id);
END IF;
RETURN NULL;
END';
CREATE TRIGGER b_mv_ut AFTER UPDATE ON b
FOR EACH ROW EXECUTE PROCEDURE b_mv_b_ut();
CREATE FUNCTION b_mv_b_dt() RETURNS TRIGGER
SECURITY DEFINER LANGUAGE 'plpgsql' AS '
BEGIN
PERFORM b_mv_refresh_row(OLD.b_id);
RETURN NULL;
END';
CREATE TRIGGER b_mv_dt AFTER DELETE ON b
FOR EACH ROW EXECUTE PROCEDURE b_mv_b_dt();
CREATE FUNCTION b_mv_b_it() RETURNS TRIGGER
SECURITY DEFINER LANGUAGE 'plpgsql' AS '
BEGIN
PERFORM b_mv_refresh_row(NEW.b_id);
RETURN NULL;
END';
CREATE TRIGGER b_mv_it AFTER INSERT ON b
FOR EACH ROW EXECUTE PROCEDURE b_mv_b_it();

-- Trigger für Tabelle c
CREATE FUNCTION b_mv_c_ut() RETURNS TRIGGER
SECURITY DEFINER LANGUAGE 'plpgsql' AS '
BEGIN
IF OLD.b_id = NEW.b_id THEN
PERFORM b_mv_refresh_row(NEW.b_id);
ELSE
PERFORM b_mv_refresh_row(OLD.b_id);
PERFORM b_mv_refresh_row(NEW.b_id);
END IF;
RETURN NULL;
END';
CREATE TRIGGER b_mv_ut AFTER UPDATE ON c
FOR EACH ROW EXECUTE PROCEDURE b_mv_c_ut();
CREATE FUNCTION b_mv_c_dt() RETURNS TRIGGER
SECURITY DEFINER LANGUAGE 'plpgsql' AS '
BEGIN
PERFORM b_mv_refresh_row(OLD.b_id);
RETURN NULL;
END';
CREATE TRIGGER b_mv_dt AFTER DELETE ON c
FOR EACH ROW EXECUTE PROCEDURE b_mv_c_dt();
CREATE FUNCTION b_mv_c_it() RETURNS TRIGGER
SECURITY DEFINER LANGUAGE 'plpgsql' AS '
BEGIN
PERFORM b_mv_refresh_row(NEW.b_id);
RETURN NULL;
END';
CREATE TRIGGER b_mv_it AFTER INSERT ON c
FOR EACH ROW EXECUTE PROCEDURE b_mv_c_it();

Fazit

Der Aufbau und die Wartung von Datenbanken sind auch heute noch ein sehr umstrittenes Themenfeld. Einerseits möchte man eine möglichst redundanzfreie Datenhaltung, andererseits spielt auch die einfache Auswertung der Daten eine große Rolle. Manchmal geraten diese beiden Ziele in Konflikt, da eine redundanzfreie Datenhaltung im Allgemeinen mindestens die dritte Normalform impliziert. Statements zur Datenauswertung (beispielsweise OLAP) werden dadurch natürlich wesentlich komplexer, ebenso kann die Performance einer Anwendung durch lange Bearbeitungszeiten komplizieter Joins sehr in Mitleidenschaft gezogen werden. Sichten, vor allem materialisierten Sichten, stellen dazu ein mächtiges Werkzeug zur Verfügung, um Abfragen zu vereinfachen und die Performance signifikant zu steigern. Es können somit alltägliche Probleme beherrscht werden, ohne das Inkonsistenzen durch redundante Datenhaltung befürchtet werden müssen.

Insgesamt lässt sich festhalten, dass, nach einer kurzen Einarbeitung, sehr gute Ergebnisse mit diesem Verfahren auf Datenbankebene erzielt werden können.

Getagged mit: , , , , , , , , , , , , , , , , , , , , , , , , ,
Veröffentlicht unter Entwicklung, Wirtschatsinformatik