Materialisierte Views in PostgreSQL

Wenn du viel mit SQL arbeitest (idealerweise mit PostgreSQL) und Datenbanken abfragst, hast du vermutlich schon eine ganze Reihe komplexer Abfragen geschrieben, um wichtige Fragen zu beantworten. Oft brauchst du die Ergebnisse dieser Abfragen regelmäßig in deiner Arbeit. Eine komplexe Abfrage mehrfach auszuführen, kann teuer werden. Was, wenn es eine Möglichkeit gäbe, die Ergebnisse einer komplexen Abfrage zwischenzuspeichern, um die Performance zu verbessern? Genau hier kommen materialisierte Views ins Spiel.

Vielleicht denkst du jetzt: Es gibt doch auch einfache Views, die das Ergebnis einer Abfrage bereitstellen. Eine einfache View unterscheidet sich jedoch in mehreren Punkten von einer materialisierten View. Laut der offiziellen PostgreSQL-Dokumentation gilt für einfache Views:

The view is not physically materialized. Instead, the query is run every time the view is referenced in a query.

Das bedeutet: Einfache Views werden nicht physisch in der Datenbank gespeichert. Jedes Mal, wenn eine View in einer Abfrage verwendet wird, wird die zugrunde liegende Abfrage erneut ausgeführt. Das macht einfache Views in puncto Zugriffszeit etwas ineffizient.

Materialisierte Views bieten dagegen viel Flexibilität, weil sie die View physisch in der Datenbank speichern. Du kannst mit materialisierten Views arbeiten wie mit einfachen Views – allerdings mit kürzerer Zugriffszeit. Im weiteren Verlauf dieses Tutorials lernst du materialisierte Views in PostgreSQL kennen. In einem seiner Webinare zeigt DataCamps Chief Data Scientist David Robinson, wie er materialisierte Views nutzt, um Ergebnisse komplexer Abfragen effizient zu speichern.

Hinweis: Um diesem Tutorial zu folgen, solltest du mit den Inhalten aus dem Kurs Joining Data in SQL vertraut sein.

Erste Einrichtung der Datenbank

Am besten legst du in PostgreSQL eine Datenbank an und erstellst dort eine Basistabelle, damit du effektiv darauf zugreifen kannst. Das hilft dir auch in den folgenden Abschnitten dieses Tutorials. Du kannst dieses Datenbank-Backup wiederherstellen, das die Tabelle enthält, die du hier verwenden wirst. Wenn du lernen möchtest, wie man ein Backup in PostgreSQL wiederherstellt, folge dem ersten Abschnitt von diesem Tutorial.

Wenn du das Backup erfolgreich wiederhergestellt hast, solltest du eine Tabelle namens international_debt in der Datenbank sehen (falls du noch keine Datenbank hast, lege zuerst eine an). Wirf schnell einen Blick auf die ersten Zeilen der Tabelle (eine einfache SELECT-Abfrage genügt) –

Die Tabelle enthält Informationen zu Schuldenstatistiken verschiedener Länder weltweit für das aktuelle Jahr in unterschiedlichen Kategorien (siehe die Spalten indicator_name, indicator_code). Die Spalte debt zeigt die Höhe der Schulden (in USD), die ein Land in einer bestimmten Kategorie hat. Die Daten stammen aus der Volkswirtschaftslehre und werden häufig genutzt, um die wirtschaftliche Lage verschiedener Länder zu analysieren. Erhoben wurden sie von der Weltbank.

Nachdem die Datenbank steht, kannst du mit den nächsten Abschnitten dieses Tutorials fortfahren.

Materialisierte Views erstellen

Lass uns eine materialisierte View für die folgende Abfrage erstellen –

select country_name,
sum(debt) as total_debt
from international_debt
group by country_name order by country_name;

Die Abfrage liefert Ländernamen mit ihrer gesamten Verschuldung (alphabetisch sortiert). Die Ausgabe sieht in etwa so aus –

Nun die materialisierte View –

CREATE MATERIALIZED VIEW country_total_debt
as
  select country_name,
  sum(debt) as total_debt
  from international_debt
  group by country_name order by country_name;

Du solltest eine Erfolgsmeldung erhalten wie: Query returned successfully: 124 rows affected, 242 ms execution time.

Eine materialisierte View aktualisieren

Du kannst PostgreSQL beim Erstellen auch anweisen, zunächst keine Daten in die materialisierte View zu laden, indem du die Option WITH NO DATA angibst –

CREATE MATERIALIZED VIEW country_total_debt_2
as
  select country_name,
  sum(debt) as total_debt
  from international_debt
  group by country_name order by country_name
WITH NO DATA;

Dann erhältst du eine Meldung wie: Query returned successfully with no result in 41 ms. Achte auf den Unterschied zwischen den beiden Ausgaben. Mit der Option WITH NO DATA wird eine leere materialisierte View erstellt. Diese View kannst du nicht abfragen. Du kannst sie jedoch befüllen, indem du Folgendes ausführst –

REFRESH MATERIALIZED VIEW country_total_debt_2;

Eine materialisierte View abfragen

Inzwischen solltest du zwei materialisierte Views erstellt haben (country_total_debt, country_total_debt_2). Führen wir eine einfache SELECT-Abfrage auf eine der beiden aus –

select * from country_total_debt_2;

Die Abfrage liefert etwas wie –

Die Ausgabe entspricht exakt der Abfrage, die wir in die materialisierte View gepackt haben. Du kannst auf materialisierten Views auch Abfragen mit Aggregatfunktionen ausführen –

select max(total_debt) from country_total_debt_2;

Du solltest Folgendes erhalten –

Eine materialisierte View löschen

Materialisierte Views kannst du in PostgreSQL genauso löschen wie Tabellen. Du musst lediglich nach Drop noch Materialized View angeben –

Drop Materialized View country_total_debt_2;

Wenn du MySQL mit seinem zweitbeliebtesten Wettbewerber vergleichen willst, lies unseren ausführlichen Vergleich PostgreSQL vs. MySQL.

Fazit

Das war's mit diesem Tutorial. Danke, dass du bis zum Ende dabeigeblieben bist. Du hast gelernt, was materialisierte Views in PostgreSQL sind und wie du damit arbeitest. Wegen ihrer Effizienz werden materialisierte Views häufig im Data-Warehousing eingesetzt. Wenn du dich in komplexere Abfragen einarbeiten möchtest, schau dir diese DataCamp-Kurse an:

• Intermediate SQL
• Reporting in SQL