Abgeleitete Daten in PostgreSQL speichern: generierte Spalten versus materialisierte Views

Worum es geht

Die Dokumentation bezeichnet eine generierte Spalte als das, «was eine View für Tabellen ist – nur für Spalten»: GENERATED ALWAYS AS (expr) mit VIRTUAL (beim Lesen berechnet, keine Speicherung; Standard seit PostgreSQL 18) oder STORED (bei Insert und Update berechnet und auf die Platte geschrieben). Der Ausdruck darf nur unveränderliche Funktionen verwenden, darf keine andere generierte Spalte, keine Subquery und keine andere Tabelle referenzieren, und die Spalte kann nicht direkt beschrieben werden. Eine materialisierte View speichert die Ausgabe einer beliebigen Abfrage; REFRESH MATERIALIZED VIEW ersetzt ihren Inhalt vollständig, und die View kann nicht direkt aktualisiert werden.

Warum es wichtig ist

Beide ersparen eine Neuberechnung im Anwendungscode, und beide können von der Absicht abweichen: Eine gespeicherte generierte Spalte kostet bei jedem Update Schreibzeit und Platz, eine materialisierte View liefert bis zum nächsten Refresh veraltete Zeilen. Die falsche Wahl führt entweder zu langsamen Schreibvorgängen oder zu falschen Lesevorgängen.

So wird es angewendet

• Normalisierung je Zeile (lower(email), ein aus Titel und Body gebildeter tsvector, ein in Buckets eingeteilter Zeitstempel): eine STORED-generierte Spalte, indiziert wie jede andere Spalte; der Wert ist immer aktuell. Ein Expression-Index ist die Alternative, wenn keine Spalte gebraucht wird, doch dann muss die Abfrage den Ausdruck exakt wiederholen.
• Werte je Zeile, die billig sind und selten gefiltert werden (Einheitenumrechnungen, ein aus Teilen zusammengesetzter Anzeigename): eine virtuelle generierte Spalte, die beim Schreiben nichts kostet.
• Aggregate über viele Zeilen (Tagessummen, Rankings, denormalisierte Joins für ein Dashboard): eine materialisierte View mit einem eindeutigen Index auf ihrem Schlüssel, aktualisiert durch einen geplanten Job mit REFRESH MATERIALIZED VIEW CONCURRENTLY. Die Dokumentation verlangt für CONCURRENTLY einen eindeutigen Index auf Spaltennamen, ohne Ausdruck oder WHERE, und merkt an, dass ein einfacher Refresh Lesende blockieren kann.
• Den Zeitpunkt des letzten Refresh neben Daten aus einer materialisierten View anzeigen und alarmieren, wenn der Refresh-Job nicht gelaufen ist.
• Muss eine Zusammenfassung aktuell bleiben, während sich die Basistabellen ständig ändern, eine Zusammenfassungstabelle in derselben Transaktion wie der Schreibvorgang oder über Trigger pflegen; eine materialisierte View, die jede Minute aktualisiert wird, ist jede Minute eine vollständige Neuberechnung.

Stolpersteine

WITH NO DATA lässt eine materialisierte View bis zum ersten Refresh in einem nicht scanbaren Zustand, und CONCURRENTLY kann für diese erste Befüllung nicht verwendet werden; je View darf jeweils nur ein Refresh laufen. Virtuelle generierte Spalten können keine benutzerdefinierten Typen oder Funktionen verwenden. Eine generierte Spalte kann nicht Teil eines Partitionsschlüssels sein und keine Default- oder Identity-Definition haben.