Menu

PostgreSQL Views und Materialized Views: anlegen, refreshen

Eine View ist eine gespeicherte Abfrage, die bei jedem Lesen läuft. Eine Materialized View speichert das Ergebnis der Abfrage und ändert sich nur, wenn du REFRESH MATERIALIZED VIEW ausführst.

Diese Seite enthält ausführbare Editoren. Bearbeiten, ausführen und Ausgabe sofort sehen.

Eine View ist eine gespeicherte Abfrage, die du wie eine Tabelle liest: CREATE VIEW name AS SELECT .... Sie speichert keine Daten, jedes Lesen führt die Abfrage also neu aus und sieht immer die aktuellen Zeilen. Eine Materialized View, CREATE MATERIALIZED VIEW name AS SELECT ..., speichert das Ergebnis auf der Platte und ändert sich nur, wenn du REFRESH MATERIALIZED VIEW name ausführst.

Die Bestellung, die nach dem Anlegen der View eingefügt wurde, steht im Ergebnis (ben mit 90), weil die Abfrage der View im Moment des SELECT lief. Views geben einem Join oder Filter, den du oft wiederholst, einen Namen, verbergen Spalten vor Nutzern, die sie nicht sehen sollen, und halten alte Abfragen am Laufen, wenn sich Tabellen ändern.

Eine View ändern oder entfernen

CREATE OR REPLACE VIEW tauscht die Abfrage an Ort und Stelle aus. Die neue Abfrage muss dieselben Spalten mit denselben Namen und Typen in derselben Reihenfolge liefern; sie darf nur am Ende neue Spalten ergänzen. Für jede andere Änderung löschst du die View und legst sie neu an.

CREATE OR REPLACE VIEW paid_totals AS
  SELECT customer, sum(amount) AS total, count(*) AS orders
  FROM orders WHERE paid GROUP BY customer;

DROP VIEW paid_totals;
DROP VIEW IF EXISTS paid_totals CASCADE;  -- also drops views built on it

Über eine View einfügen und aktualisieren

Eine View über eine einzige Tabelle ist automatisch aktualisierbar, wenn sie kein GROUP BY, DISTINCT, keine Aggregate, Window Functions, kein LIMIT und keine Mengenoperationen hat. INSERT, UPDATE und DELETE auf der View ändern dann die Tabelle. WITH CHECK OPTION lehnt Zeilen ab, die die View danach nicht zeigen könnte:

Beide Änderungen sind in products angekommen. (4, 'shelf', false) über die View einzufügen, scheitert mit new row violates check option for view "active_products", weil die View die Zeile nicht zeigen würde. Für eine View, die nicht aktualisierbar ist, kann ein INSTEAD OF Trigger festlegen, was ein Schreibzugriff tut.

Eine Materialized View anlegen und refreshen

Eine Materialized View führt ihre Abfrage einmal aus und speichert die Zeilen. Lesen ist so schnell wie bei einer Tabelle, aber neue Daten erscheinen erst nach einem Refresh:

Vor dem Refresh zeigt ana noch 120, danach 180. REFRESH führt die ganze Abfrage neu aus und ersetzt die gespeicherten Zeilen. Nimm eine Materialized View für Ergebnisse, die teuer zu berechnen sind und Minuten oder Stunden alt sein dürfen: Dashboards, Berichte, Zusammenfassungen für die Suche.

CREATE MATERIALIZED VIEW ... WITH NO DATA legt sie leer an, und Lesen scheitert bis zum ersten Refresh.

Refreshen, ohne Lesezugriffe zu blockieren

Ein einfaches REFRESH sperrt die Materialized View, Abfragen darauf warten also, bis der Refresh fertig ist. REFRESH MATERIALIZED VIEW CONCURRENTLY baut das neue Ergebnis separat auf und übernimmt nur die Unterschiede, Lesezugriffe laufen also weiter. Dafür braucht die Materialized View einen eindeutigen Index auf einfachen Spaltennamen und ohne WHERE:

Ohne den eindeutigen Index scheitert der Refresh mit dem Hinweis, einen anzulegen. CONCURRENTLY kann schneller sein als ein einfacher Refresh, wenn sich wenige Zeilen ändern, und lässt sich nicht auf einer Materialized View nutzen, die nie befüllt wurde. In jedem Fall läuft immer nur ein Refresh einer Materialized View gleichzeitig. Andere Indizes funktionieren wie bei einer Tabelle, indiziere also die Spalten, nach denen deine Abfragen filtern.

Nach Zeitplan refreshen

PostgreSQL refresht Materialized Views nicht von selbst. Führ den Refresh aus einem Scheduler aus, etwa cron mit psql, oder mit der Erweiterung pg_cron, wo sie installiert ist:

# crontab: every 15 minutes
*/15 * * * * psql "$DATABASE_URL" -c 'REFRESH MATERIALIZED VIEW CONCURRENTLY customer_totals'
SELECT cron.schedule('refresh-totals', '*/15 * * * *',
  'REFRESH MATERIALIZED VIEW CONCURRENTLY customer_totals');

View vs Materialized View

ViewMaterialized View
Speichert Zeilenneinja
Daten sind aktuellimmerStand des letzten Refresh
Lesegeschwindigkeitwie die Abfragewie eine Tabelle
Indizesnein (nutzt die Indizes der Tabellen)ja
Beschreibbareinfache Views janein

Die Referenz zu CREATE MATERIALIZED VIEW listet die übrigen Optionen auf, etwa Speicherparameter und Tablespaces.

Häufig gestellte Fragen

Aktualisiert sich eine Materialized View in PostgreSQL automatisch?

Nein. Sie behält das Ergebnis ihres letzten REFRESH MATERIALIZED VIEW (oder ihrer Erstellung), bis du sie erneut refreshst. Plane den Refresh mit cron, der Erweiterung pg_cron oder in deiner Anwendung.

Ist eine View schneller als die Abfrage selbst?

Nein. Eine einfache View speichert keine Daten; PostgreSQL setzt sie in die Abfrage ein, die sie liest, und sie läuft so schnell wie die ausgeschriebene Abfrage. Nur eine Materialized View spart die Arbeit, um den Preis veralteter Daten.

Kann ich einer Materialized View einen Index geben?

Ja. Eine Materialized View wird wie eine Tabelle gespeichert, CREATE INDEX funktioniert also. Ein eindeutiger Index ist außerdem genau das, was REFRESH MATERIALIZED VIEW CONCURRENTLY verlangt.

Wie liste ich Views und Materialized Views auf?

In psql listet \dv Views und \dm Materialized Views auf. In SQL fragst du pg_views und pg_matviews ab.

Illustration der Programmiersprachen bei Coddy

Lerne mit Coddy zu programmieren

Los geht's