Wie funktionieren materialisierte Sichten in PostgreSQL?

Question

Accepted Answer

Eine **materialisierte Sicht** speichert die *Ergebnisse* einer Abfrage physisch (vorberechnet), im Gegensatz zu einer regulären Sicht (die ihre Abfrage jedes Mal neu ausführt). Dies macht das Lesen für teure Abfragen schnell, auf Kosten dafür, dass die Daten nur so aktuell sind wie die letzte **Aktualisierung** — ein Caching-Mechanismus innerhalb der Datenbank.

## Reguläre Sicht vs. materialisierte Sicht

```sql
-- regular VIEW — runs the query EVERY time (always fresh, but recomputed)
CREATE VIEW sales_summary AS SELECT region, SUM(amount) FROM orders GROUP BY region;

-- MATERIALIZED VIEW — stores the RESULTS physically (fast reads, but can be stale)
CREATE MATERIALIZED VIEW sales_summary_mat AS
SELECT region, SUM(amount) AS total FROM orders GROUP BY region;

SELECT * FROM sales_summary_mat;     -- reads STORED results — fast (no recompute)
```

Eine materialisierte Sicht berechnet die Ergebnisse vor und speichert sie, daher ist das Abfragen schnell (wie das Lesen einer Tabelle) — ideal für teure Aggregationen, die häufig abgefragt werden.

## Aktualisierung (Aktualisieren der gespeicherten Daten)

```sql
-- the data is stale until you refresh it
REFRESH MATERIALIZED VIEW sales_summary_mat;          -- recompute (locks reads during refresh)

-- CONCURRENTLY — refresh WITHOUT blocking reads (requires a unique index)
CREATE UNIQUE INDEX idx_sales_region ON sales_summary_mat(region);
REFRESH MATERIALIZED VIEW CONCURRENTLY sales_summary_mat;   -- reads continue during refresh
```

Sie müssen **aktualisieren**, um die Daten zu aktualisieren. `REFRESH ... CONCURRENTLY` (benötigt einen eindeutigen Index) aktualisiert ohne gleichzeitige Lesevorgänge zu blockieren — wichtig für Produktionsumgebungen.

## Wann man materialisierte Sichten verwenden sollte

```text
✓ EXPENSIVE queries (complex aggregations, joins) queried FREQUENTLY
✓ Data that doesn't need real-time freshness (dashboards, reports, analytics)
✓ Reducing load from repeatedly running the same heavy query
→ Refresh on a schedule (cron / pg_cron) or after data changes.

✗ Data needing real-time accuracy → use a regular view or query directly
```

## Der Kompromiss

```text
Fast reads (precomputed) ⟷ Stale data (until refreshed) + storage cost.
You trade freshness and storage for read speed — a caching decision.
You can also index a materialized view (it's a physical table of results).
```

## Warum es wichtig ist

Materialisierte Sichten sind eine wertvolle PostgreSQL-Funktion zur **Leistungsoptimierung** — sie bieten einen In-Database-Caching-Mechanismus für teure Abfragen, daher ist das Verständnis dieser Funktion nützlich für die Optimierung von Anwendungen mit kostspieligen, häufig ausgeführten Abfragen.

Der Kernwert besteht darin, die Ergebnisse teurer Abfragen (komplexe Aggregationen, schwere Joins) vorab zu berechnen und zu speichern, damit das Lesen schnell ist (wie das Abfragen einer Tabelle), anstatt die teure Abfrage jedes Mal neu auszuführen — dies ist eine erhebliche Optimierung für Dashboards, Berichte und Analysen, bei denen dieselbe schwere Abfrage wiederholt ausgeführt wird.

Das Verständnis des wichtigsten **Kompromisses** ist entscheidend: Materialisierte Sichten bieten schnelle Lesevorgänge, aber die Daten sind **veraltet**, bis sie aktualisiert werden (und sie verbrauchen Speicher), daher sind sie eine Caching-Entscheidung, die sich für Daten eignet, die keine Echtzeit-Aktualität benötigen.

Zu wissen, wie man sie **aktualisiert** — besonders `REFRESH ... CONCURRENTLY` (das ohne Blockierung von Lesevorgängen aktualisiert, wichtig für die Produktionsverfügbarkeit, erfordert einen eindeutigen Index) — und Aktualisierungsstrategien (nach einem Zeitplan über Cron/pg_cron oder nach Datenänderungen) ist praktisches Wissen für ihre effektive Nutzung.

Das Verständnis, **wann man** materialisierte Sichten verwendet (teure, häufig ausgeführte Abfragen mit Toleranz für leichte Veralterung — Dashboards, Analysen) versus wann nicht (Daten, die Echtzeit-Genauigkeit benötigen), spiegelt gutes Urteilsvermögen wider.

Da teure wiederholte Abfragen ein echtes Leistungsproblem darstellen und materialisierte Sichten eine effektive In-Database-Caching-Lösung bieten (vorberechnete, indexierbare Ergebnisse) mit einem klaren Kompromiss zwischen Aktualität und Geschwindigkeit, ist das Verständnis dieser Sichten — wie sie funktionieren, Aktualisierung (besonders parallel), der Kompromiss und angemessene Verwendung — wertvoll, praktisch relevantes PostgreSQL-Wissen zur Optimierung der Leistung von analytischen und Berichtsabfragen, ein häufiges Anliegen, bei dem materialisierte Sichten eine saubere, integrierte Lösung bieten.