Kann man Postgresql lazy refreshed materialized views beibringen?

Ich habe eine Anwendung, bei der eine recht rechenaufwendige View genutzt wird, um Daten auszuwerten. Updates der zugrundeliegenden Tabellen und Auswertung erfolgen meist blockweise, d.H. es gibt Zeiten mit vielen INSERTs/UPDATEs auf den Basistabellen und Zeiten mit vielen SELECTs auf der View.

Es muß sichergestellt werden, daß die View bei Abfrage immer aktuell ist, aber aus Performancegründen wird auch die Materialisierung zwingend gebraucht.

Die für mich beste weil transparenteste Lösung wäre eine “lazy refreshed materialized View”, die automatisch bei jedem SELECT refreshed wird, wenn sich die Basisdaten seit dem letzten refresh verändert haben. So würde der Refresh bei einem Block von Abfragen höchstens einmal ausgelöst und Updates würden ebenfalls nicht ausgebremst.

Postgresql scheint das von Haus aus erstmal nicht anzubieten.oder überseh ich da was? Mit Oracle war das möglich (wobei es da auch noch inkrementelle refreshs gibt, aber das ist jetzt mal nicht soo wichtig).

Die nächste Idee war deshalb, Trigger zu nutzen:

INSERT/UPDATE/DELETE-Trigger auf den Basistabellen setzen ein “dirty” Flag, das signalisiert daß die View nicht mehr aktuell ist.

Ein weiterer Trigger auf der Materialized View prüft dieses “dirty”-Flag. ist es gesetzt, soll der passende REFRESH MATERIALIZED VIEW abgesetzt und dann das Flag gelöscht werden. Dieser Trigger müßte vor jedem SELECT laufen.

Leider scheint aber auch hier Postgres immer ein entscheidendes Feature zu fehlen. Die nicht vorgesehenen Trigger auf materialized Views könnte man ja noch umschiffen, indem man auf die Materialized View eine “normale” View draufsetzt. Aber es gibt scheinbar kein “SELECT” Trigger Event !? - kann man das vielleicht über Rules erzeugen?

Welche Möglichkeiten gibt es, diese Aufgabe transparent umzusetzen, also so, daß die Anwendung möglichst nichts davon wissen muß?

Folgende Ansätze möchte ich ausschließen:

  • Refresh im Update-Trigger der Basistabelle. Macht Aktualisierungen der Basisdaten inakzeptabel langsam.

  • regelmäßiger Refresh über einen Timer-Job o.ä… Birgt die Gefahr daß Abfragen veraltete Daten liefern, deshalb inakzeptabel.

  • Auslösung des Refresh durch den Client-. Produziert inakzeptables Risiko von Fehlern durch Nichtausführung und würde u.U. Eingriffe in Programme erfordern, die nicht geändert werden können/sollen (z.B. weil zugekauft) .

Wie stark überschneiden sich denn die Zeiten der Inserts/Updates mit den Abfragen?

Du könntest den Zugriff auf die MVIEW über eine set-returning function kapseln.

Die Funktion prüft zuerst ob ein Refresh notwendig ist und liefert dann den Inhalt der mview zurück:

Sowas in der Art:

create function get_mview_data()
  returns setof mview
as
$$
declare 
  is_dirty boolean;
begin
  is_dirty := ....;
  
  if is_dirty then 
    refresh mview;
  end if;
  
  return query select * from mview;
end;
$$
language plpgsql;

Das hat allerdings den Nachteil, dass z.B. JOINs oder WHERE Bedingungen sicherlich nicht optimal ausgeführt werden (keine Verwendung von Indizes), weil die Funktion eine Blackbox für den Optimizer darstellt.

1 Like

Das mit der set-returning function scheint ganz gut zu funktionieren und die Sache vernünftig zu beschleunigen.

@Gisman: kann man so nicht sagen. Es gibt Phasen, in denen wird zuerst erfasst und dann ausgewertet oder auch nur erfasst, es gibt aber auch Phasen in denen beides praktisch parallel läuft.

An sich wär das vielleicht ein Fall für einen Feature Request. Sowas wie CREATE MATERIALIZED VIEW x AS SELECT … REFRESH LAZY könnte auch in anderen Bereichen durchaus Existenzberechtigung haben und kann eigentlich soo schwer nicht einzubauen sein. Die Strategie könnte ja im Grunde wie skizziert laufen - dirty-flag an der View setzen wenn eine Basistabellee sich ändert (oder eine Basisspalte) und dann halt den refresh automatisch auslösen wenn die View gelesen wird und dirty ist.

Hmm noch eine Idee:

der Ansatz mit der set-returning Function hat ja den Nachteil, daß man die Möglichkeit der Indizierung und des Optimizers verliert.

Könne anstattdessen ein Konstrukt funktionieren, das die Refresh-Funktion als Parameter einer View aufruft?

Also z.B. so:

create view output AS SELECT * FROM materialized_output where prim_key_col NOT IN (refresh_dirty());

Wenn refresh_dirty VOID oder NULL liefert, müßte das doch dazu führen, daß zuerst refresh_dirty() evaluiert wird und anschließend materialized_output abgefragt wird - womit dann der Optimizer wieder zum Zuge kommen müßte?

Oder hab ich da nen Denkfehler?