2. Views - Virtuelle Tabellen in SQL

Views - Virtuelle Tabellen in SQL - Teil 2

In Teil 1 haben wir die Grundlagen von Views in SQL kennengelernt: Was sind Views, welche Vorteile bieten sie, und wie werden sie definiert und genutzt. In diesem zweiten Teil gehen wir tiefer ins Detail und betrachten fortgeschrittene Konzepte, Best Practices und Besonderheiten verschiedener Datenbanksysteme.

Was sind Views überhaupt?

Wiederholung: Eine View ist eine benannte, gespeicherte SQL-Abfrage, die sich nach außen wie eine Tabelle verhält, aber nicht zwingend eigene Daten speichert. Man kann sie wie eine Tabelle selektieren (SELECT ... FROM view_name), und - je nach System und Aufbau - teilweise auch ändern (INSERT/UPDATE/DELETE), wobei die Änderungen an die zugrunde liegenden Basistabellen durchgereicht werden. Views dienen in erster Linie der Abstraktion, Kapselung und Sicherheit.

Wichtig ist die Trennung:

• Normale (logische) View: speichert keine Daten, sondern nur eine Definition (die Query). Jede Abfrage gegen die View wird vom Optimizer in eine Abfrage auf die Basistabellen “umgeschrieben” (Query Rewriting).
• Neu Materialisierte View: speichert das Ergebnis der zugrunde liegenden Abfrage persistent. Sie muss manuell (oder zeitgesteuert) aktualisiert werden. Dafür liefert sie oft deutlich bessere Performance für teure Aggregationen/Joins.

Abb. 2.1: Materialisierte Views speichern das Ergebnis einer Abfrage und können manuell aktualisiert werden.

Quelle: [3]

Wofür kann man Views gebrauchen?

1. Abstraktionsschicht & API für Daten

   • Views stellen eine “vertragliche” Schnittstelle dar. Anwendungen sprechen die View an, während sich die interne Tabellenstruktur ändern darf (Migrationen, Normalisierung), solange die View weiterhin das gleiche Schema liefert.

2. Sicherheitsbarriere

   • Man kann feingranular SELECT-Rechte auf Views geben, ohne Zugriff auf die Basistabellen zu gewähren. So lassen sich Spalten ausblenden (z. B. password_hash) oder Zeilen filtern (z. B. WHERE student.clazz IN ("4AHITM", "4BHITM")).

3. Wiederverwendbare komplexe Logik

   • Häufig benötigte Joins, CASE-Ausdrücke, Window-Funktionen etc. einmal definieren, überall nutzen. Das reduziert Duplikate im Code und das Risiko von Inkonsistenzen.

4. Berichte & Analytics

   • Für BI/Reporting sind Views ideal, um den Datenkonsumenten ein stabiles, gut dokumentiertes Datenmodell zu geben - oft in Kombination mit materialisierten Views für Performance.

5. Migrationen & Refaktorierung

   • Wenn Spalten oder Tabellen umbenannt/aufgesplittet werden, kann eine View den alten Zustand simulieren, um Legacy-Applikationen übergangsweise weiter zu versorgen.

Grundlegende Syntax

PostgreSQL

-- Einfache View
CREATE VIEW reporting.orders_summary AS
SELECT c.id AS customer_id,
       c.name,
       COUNT(o.id) AS order_count,
       SUM(o.total_amount) AS total_amount
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name;

-- Aktualisierbare (auto-updatable) View - einfaches Mapping auf eine Basistabelle
CREATE VIEW app.users_public AS
SELECT id, email, display_name
FROM app.users
WHERE is_deleted = false
WITH LOCAL CHECK OPTION;

-- Materialisierte View
CREATE MATERIALIZED VIEW analytics.mv_daily_sales AS
SELECT date_trunc('day', created_at) AS day,
       SUM(total_amount) AS revenue
FROM orders
GROUP BY 1
WITH NO DATA;

Wichtige DDL-Operationen:

• CREATE OR REPLACE VIEW passt die Definition an, ohne Grants zu verlieren (Achtung bei Spalten-Layout).
• ALTER VIEW ... RENAME/OWNER/SET SCHEMA etc. für Verwaltung.
• DROP VIEW entfernt die View (ggf. DROP VIEW ... CASCADE, wenn abhängige Objekte existieren).
• Materialisierte Views: REFRESH MATERIALIZED VIEW (optional CONCURRENTLY).

Updatability von Views (PostgreSQL)

PostgreSQL macht schlichte Views über eine einzelne Basistabelle oft automatisch aktualisierbar (INSERT/UPDATE/DELETE), wenn bestimmte Bedingungen erfüllt sind:

• Keine Aggregation, DISTINCT, GROUP BY, HAVING, UNION/EXCEPT/INTERSECT, WINDOW FUNCTIONS etc.
• Keine schreibhemmenden Konstrukte wie bestimmte Funktionen/Joins, die nicht eindeutig auf eine Zielzeile abbildbar sind oder Subqueries, es sei denn diese sind eindeutig.
• Kein “verstecktes” Mapping über mehrere Tabellen.

Tip

Wann ist eine View aktualisierbar:

1. Basistabelle eindeutig identifizierbar (Primärschlüssel).
2. Jede Spalte der View stammt direkt aus der Basistabelle (keine berechneten Spalten).
3. Keine Joins, Aggregationen, Gruppierungen, DISTINCT, UNION etc.
4. Keine Subqueries oder Funktionen, die nicht eindeutig auf eine Zeile abbilden.
5. Keine WITH CHECK OPTION-Einschränkungen, die das Update unmöglich machen.

Note

“verstecktes Mapping”: Wenn die View z.B. eine Spalte full_name hat, die aus first_name || ' ' || last_name zusammengesetzt wird, dann ist sie nicht automatisch updatable, weil unklar ist, wie ein UPDATE full_name = 'New Name' auf die Basistabelle zurückgeführt werden soll.

Wenn eine View nicht automatisch updatable ist, gibt INSTEAD OF-Trigger auf der View: Man definiert, was bei INSERT/UPDATE/DELETE passieren soll, und leitet die Operationen selbst in die Basistabellen.

CHECK OPTION in PostgreSQL:

• WITH [LOCAL|CASCADED] CHECK OPTION bei CREATE VIEW verhindert, dass über die View Änderungen erfolgen, die die Zeile “aus dem Sichtbereich” der View herausfallen lassen. Beispiel: Eine View filtert WHERE is_deleted = false. Mit CHECK OPTION kann man eine Zeile nicht auf is_deleted = true setzen, solange man über diese View schreibt.

Sicherheit

• Rechteprüfung: Um eine View zu nutzen, braucht der Benutzer SELECT auf die View; er benötigt keine Rechte auf die Basistabellen.
• Row-Level-Security (RLS): RLS gilt auf den Basistabellen. Wichtig ist zu verstehen, mit wessen Identität RLS geprüft wird (Owner vs. invoker-Kontext). In PostgreSQL greifen RLS-Policies grundsätzlich gegen den aktuellen Benutzer; wenn man mit Views Sicherheitsgrenzen bauen will, musst man die Interaktion von RLS, View-Grants und ggf. SET ROLE sauber durchdenken.

Tip

Das Thema Sicherheit ist komplex und wird hier besprochen: Benutzer und Rechte.

Performance-Aspekte

Normale Views:

• Sie sind syntaktischer Zucker; die Abfrage wird in die Basistabellen aufgelöst. Performance entspricht in etwa der “entpackten” Query.
• Nesting: Viele geschachtelte Views (also Views welche wieder auf Views zugreifen) können zu komplexen, schwer zu optimierenden Plänen führen. Der Planner in PostgreSQL ist gut, aber sehr tiefe Nestings mit vielen CTEs/Window-Funktionen/Joins können die Komplexität hochtreiben.
• SELECT * vermeiden: Ändert sich die Spaltenliste der Basistabellen, können sich Views “unsichtbar” verändern oder brechen (“column drift”). Außerdem zwingt SELECT * den Planner evtl. zu mehr Arbeit, als nötig.

Materialisierte Views:

• Sie speichern Ergebnisse persistent. Ideal für teure Aggregationen, Dimensionen, rollup-artige Sichten.

• Refresh:

  • REFRESH MATERIALIZED VIEW mv; blockiert Leser kurz, während neu befüllt wird.
  • REFRESH MATERIALIZED VIEW CONCURRENTLY mv; ermöglicht gleichzeitiges Lesen während des Refreshs, erfordert aber eine eindeutige, nicht-NULL-Schlüsselspalte bzw. Unique Index auf der materialisierten View. Außerdem darf das nicht in einem expliziten Transaktionsblock laufen.
  • WITH NO DATA beim Anlegen spart initiale Ladezeit; später REFRESH ausführen.

• Indexierung: Man kann Indizes auf materialisierten Views anlegen.

• Keine automatische Aktualisierung: Man muss Jobs planen (Cron, pg_cron, systemd Timer, CI/CD-Task, Background Worker), Trigger-basierte Inkremtalität selbst bauen oder externe Tools einsetzen, welche den REFRESH ausführen.

Do’s & Don’ts

Do’s

• Schema qualifizieren: CREATE VIEW reporting.sales AS SELECT ... FROM public.orders ....
• Eindeutige Spaltensätze definieren (keine SELECT *) und View-Schnittstellen wie eine API versionieren (z. B. v1_, v2_).
• CHECK OPTION für schreibbare Views, um Datenkonsistenz sicherzustellen.
• Materialisierte Views indexieren und REFRESH ... CONCURRENTLY einplanen, wenn Verfügbarkeit wichtig ist.
• Abhängigkeiten im Blick behalten: Bei Spalten-Renames/Type-Changes bricht die View (ggf. erst zur Laufzeit). Automatisierte Tests/CI können das früh entdecken.
• EXPLAIN (ANALYZE, BUFFERS) (siehe Performance später) nutzen, um Performance von View-gestützten Abfragen zu prüfen. Gegebenenfalls die View-Definition “entpacken” und gezielt optimieren (Indizes auf Basistabellen, Statistiken, Partitionspruning).

Don’ts

• ORDER BY in Views für Sortierung als gegeben oder garantiert betrachten: Das ist nicht garantiert - Sortierung gehört in die aufrufende Abfrage. (In materialisierten Views kann die physische Ordnung anders sein; verlasse dich nie auf implizite Sortierung.)
• Tief verschachteln: Vermeide pyramidenartige “View-auf-View-auf-View”-Konstrukte. Besser: zentrale, klar dokumentierte Basissichten bauen.
• Sicherheitslogik im App-Code allein: Leake keine sensiblen Spalten in Views, die breit freigegeben werden. Nutze Views aktiv zur Least-Privilege-Durchsetzung. Gib nur die Spalten frei, die wirklich benötigt werden; keine einzige mehr.
• Refresh-Plan vergessen: Materialisierte Views ohne Maintenance-Plan sind Tick-Bomben - Daten veralten, Analysen werden inkonsistent. Die Daten sind von … wann genau? Wie alt diese Tabelle?

Arten von Views und ihre Vor- & Nachteile

Normale (logische) Views

Vorteile

• Immer aktuell, da Abfrage on-the-fly ausgeführt wird.
• Keine Speicher-Overheads für Ergebnisdaten.
• Einfach zu pflegen; CREATE OR REPLACE VIEW genügt.

Nachteile

• Performance entspricht der zugrunde liegenden (evtl. teuren) Query.
• Keine automatische Persistierung teurer Zwischenergebnisse.

Typische Einsätze

• Sicherheitsfilter, Spaltenmaskierung, API-Layer, leichte Denormalisierung.

Materialisierte Views (PostgreSQL)

Vorteile

• Sehr schnelle Abfragen auf rechenintensiven Sichten.
• Indexierbar, REFRESH ... CONCURRENTLY für hohe Verfügbarkeit.

Nachteile

• Müssen aktiv aktualisiert werden; Staleness-Risiko.
• “Echtzeit” nur mit aufwändigen inkrementellen Ansätzen.
• CONCURRENTLY erfordert Unique-Index und spezielle Rahmenbedingungen.

Typische Einsätze

• Reporting-Dashboards, Tages-Aggregationen, dimensionale Sichten, teure Joins.

Partitionierte/union-basierte Sichten (DB-spezifisch)

• In PostgreSQL kann man UNION-Views über partitionierte Tabellen bauen.

PostgreSQL

CREATE VIEW reporting.sales_all AS
SELECT id, created_at, amount FROM sales_2025
UNION ALL
SELECT id, created_at, amount FROM sales_2024
UNION ALL
SELECT id, created_at, amount FROM sales_2023;

Partitionierte Tabellen sind Tabellen, mit dem man eine logisch einzige Tabelle in mehrere physische Teil-Tabellen (Partitionen) aufteilt, z.B. nach Datum (Jahr), Region, Schuljahr etc. Das verbessert Performance und Wartbarkeit bei großen Datenmengen.

UNION ALL hängt die Zeilen einfach aneinander (keine Dublettenentfernung).

Unterschiede zwischen PostgreSQL und anderen Systemen

PostgreSQL vs. MariaDB/MySQL

Materialisierte Views

• PostgreSQL: Native materialisierte Views mit CREATE MATERIALIZED VIEW, REFRESH MATERIALIZED VIEW [CONCURRENTLY], Indexierung möglich.
• MySQL/MariaDB: Keine nativen materialisierten Views. Man simuliert sie mit Tabellen + Triggern/Events (oder Schedulern), oder nutzt externe Tools. MariaDB bietet zwar teils komfortablere Event Scheduler-Optionen, aber dennoch bleibt es eine Eigenbaulösung.

Updatability & CHECK OPTION

• Beide unterstützen updatable Views unter bestimmten Bedingungen sowie WITH CHECK OPTION.

Query Rewrite Richtung MVs

• PostgreSQL: kein automatisches Rewrite “Query → MV”. Du musst die MV direkt ansprechen oder Views über die MV bauen.
• MySQL/MariaDB: Nicht vorhanden, da keine nativen MVs.

Praxis-Takeaway:

Wenn in MySQL/MariaDB wiederkehrende, teure Aggregationen gebraucht werden, dann sind eigene Tabellen + Refresh-Logik einzuplanen (Events/Triggers). In PostgreSQL kann man die gleiche Aufgabe mit materialisierten Views + Indexen + REFRESH CONCURRENTLY eleganter lösen.

PostgreSQL vs. SQLite

Funktionsumfang

• SQLite ist minimalistisch:

  • Views: ja, aber immer read-only, es sei denn, man definiert INSTEAD OF-Trigger auf der View.
  • Materialisierte Views: Nein. Man simuliert wie in MySQL durch Tabellen.
  • Rechte: Kaum ein Rechte-/Rollenmodell im DB-Kern - Sicherheitsebene liegt eher in der Anwendung.

Praxis-Takeaway:

SQLite-Views dienen primär der Abstraktion. Für Performance-Caching baut man Tabellen + Trigger/Jobs. Sicherheitskonzepte wie in PostgreSQL (Grants auf Views statt Basistabellen) gibt es so nicht - das liegt in der App-Logik.

Konkrete Best Practices (PostgreSQL)

1. Sicht als API behandeln

   • Namenskonventionen (app_public., reporting., analytics.).
   • Nie SELECT *, sondern Spalten explizit - stabilisiert das Interface.

2. Sicherheit korrekt schneiden

   • Nur SELECT-Grants auf die View, keine Grants auf Basistabellen.

3. Materialisierte Views bewusst betreiben

   • Refresh-Strategie pro MV:

     • “Stale-Budget” (wie alt dürfen Daten sein?),
     • “Refresh-Fenster” (nachts, alle 5min, stündlich?),
     • CONCURRENTLY falls 24/7-Verfügbarkeit nötig.

   • Unique-Index für CONCURRENTLY. Beispiel:

     CREATE UNIQUE INDEX ON analytics.mv_daily_sales (day);
     REFRESH MATERIALIZED VIEW CONCURRENTLY analytics.mv_daily_sales;

   • Abhängigkeiten prüfen: Wenn MV auf andere Views/MVs aufsetzt (z.B. MV1 → MV2 → View), plane die Refresh-Reihenfolge (M2 vor M1).

4. Updatable Views prüfen

   • Für CRUD-ähnliche Sichten zuerst versuchen, die View so einfach zu halten, dass sie auto-updatable ist.
   • Falls komplexer: INSTEAD OF-Trigger und CHECK OPTION.

5. Performance messen, nicht raten

   • EXPLAIN (ANALYZE, BUFFERS) (siehe Performance) der entpackten Query, nicht nur der View.
   • Indizes auf Basistabellen (oder MV) ableiten aus den häufigsten Prädikaten/Joins.
   • Bei vielschichtigen Views ggf. CTEs oder Zwischen-MVs einziehen, um Planner-Komplexität zu verringern.

6. Deployment/CI

   • Migrationsskripte (z. B. mit ALTER VIEW, CREATE OR REPLACE VIEW) testen.
   • Breaking Changes vermeiden: zuerst neue View-Version bereitstellen, Konsumenten umstellen, erst dann die alte entfernen.

Typische Fehlerbilder

1. “Meine View ist plötzlich langsam!”

   Ursache: Basistabellen gewachsen, Statistiken alt, Planner trifft ungünstige Entscheidungen, zu viele verschachtelte Sichten. Lösung: ANALYZE, passende Indizes, View entpacken und gezielt optimieren, evtl. Materialisierung einziehen.

2. “REFRESH MATERIALIZED VIEW CONCURRENTLY meckert über fehlenden Unique Index!”

   Ursache: MV hat keinen eindeutigen Index, der jede Zeile eindeutig identifiziert. Lösung: Unique Index anlegen (ggf. künstlicher Schlüssel), dann erneut REFRESH ... CONCURRENTLY.

3. “Upsert/Update über View schlägt fehl.”

   Ursache: View nicht (auto)updatable. Lösung: View vereinfachen oder INSTEAD OF-Trigger implementieren.

4. “ORDER BY in View greift nicht.” Ursache: SQL-Standard - äußere Abfrage bestimmt die Sortierung. Lösung: ORDER BY immer im finalen SELECT setzen.