Czy możliwe jest stopniowe odświeżanie zmaterializowanego widoku w PostgreSQL, tj. Tylko dla danych, które są nowe lub uległy zmianie?

Rozważ tę tabelę i zmaterializowany widok:

CREATE TABLE graph (
   xaxis integer NOT NULL,
   value integer NOT NULL,
);

CREATE MATERIALIZED VIEW graph_avg AS 
SELECT xaxis, AVG(value)
FROM graph
GROUP BY xaxis

Okresowo nowe wartości są dodawane graphlub istniejąca wartość jest aktualizowana. Chcę odświeżać widok graph_avgco kilka godzin tylko dla zaktualizowanych wartości. Jednak w PostgreSQL 9.3 cała tabela jest odświeżana. To jest dość czasochłonne. Następna wersja 9.4 umożliwia CONCURRENTaktualizację, ale wciąż odświeża cały widok. Przy setkach milionów wierszy zajmuje to kilka minut.

Jaki jest dobry sposób na śledzenie zaktualizowanych i nowych wartości i odświeżenie widoku tylko częściowo?

Zawsze możesz wdrożyć własny stół służący jako „widok zmaterializowany”. To, co musiałeś zrobić wcześniej, MATERIALIZED VIEWzostało zaimplementowane w Postgres 9.3.

Na przykład możesz utworzyć zwykły VIEW:

CREATE VIEW graph_avg_view AS 
SELECT xaxis, AVG(value) AS avg_val
FROM   graph
GROUP  BY xaxis;

I zmaterializuj wynik jako całość raz lub za każdym razem, gdy musisz zacząć od nowa:

CREATE TABLE graph_avg AS
SELECT * FROM graph_avg_view

(Lub użyj SELECTinstrukcji bezpośrednio, bez tworzenia a VIEW.)
Następnie, w zależności od nieujawnionych szczegółów twojego przypadku użycia, możesz DELETE/ UPDATE/ INSERTzmienić ręcznie.

Podstawowa instrukcja DML z modyfikującymi dane CTE dla tabeli, taka jak :

Zakładając, że nikt inny nie próbuje pisać do graph_avgrównoczesnego (odczyt ma problemu):

WITH del AS (
   DELETE FROM graph_avg t
   WHERE  NOT EXISTS (SELECT 1 FROM graph_avg_view v WHERE v.xaxis = v.xaxis);
   )
, upd AS (
   UPDATE graph_avg t
   FROM   graph_avg_view v
   WHERE  t.xaxis = v.xaxis
   AND    t.avg_val <> v.avg_val
   )
INSERT INTO graph_avg t
SELECT *
FROM   graph_avg_view v
LEFT   JOIN graph_avg t USING (xaxis)
WHERE  t.xaxis IS NULL;

Ale to najprawdopodobniej powinno być zoptymalizowane.

Podstawowy przepis:

• Dodaj timestampdomyślną kolumnę now()do tabeli podstawowej. Nazwijmy to ts.
  • Jeśli masz aktualizacje, dodaj wyzwalacz, aby ustawić bieżący znacznik czasu dla każdej aktualizacji, która zmienia albo xaxisalbo value.

• Utwórz mały stolik, aby zapamiętać znacznik czasu ostatniej migawki. Nazwijmy to mv:

  CREATE TABLE mv (
     tbl text PRIMARY KEY
   , ts timestamp NOT NULL DEFAULT '-infinity'
  ); -- possibly more details

• Utwórz ten częściowy, wielokolumnowy indeks:

  CREATE INDEX graph_mv_latest ON graph (xaxis, value)
  WHERE  ts >= '-infinity';

• Użyj znacznika czasu ostatniej migawki jako predykatu w zapytaniach, aby odświeżyć migawkę przy doskonałym użyciu indeksu.

• Pod koniec transakcji upuść indeks i utwórz go ponownie ze znacznikiem czasu transakcji, zastępując znacznik czasu w predykacie indeksu (początkowo '-infinity'), który również zapisujesz w tabeli. Wszystko w jednej transakcji.

• Zauważ, że indeks częściowy doskonale nadaje się do pokrycia INSERTi UPDATEoperacji, ale nie DELETE. Aby to pokryć, musisz wziąć pod uwagę cały stół. Wszystko zależy od dokładnych wymagań.

Jednoczesna aktualizacja (Postgres 9.4)

Chociaż nie jest to aktualizacja przyrostowa, o którą prosiłeś, Postgres 9.4 zapewnia nową funkcję jednoczesnej aktualizacji .

Cytując dokument…

Przed PostgreSQL 9.4 odświeżenie zmaterializowanego widoku oznaczało zablokowanie całej tabeli, a tym samym uniemożliwienie jakichkolwiek zapytań o nią, a jeśli odświeżenie zajęło dużo czasu, aby uzyskać wyłączną blokadę (podczas gdy czeka ona na zapytania używające jej do zakończenia), to z kolei wstrzymuje kolejne zapytania. Można to teraz złagodzić za pomocą słowa kluczowego CONCURRENTLY:

 postgres=# REFRESH MATERIALIZED VIEW CONCURRENTLY mv_data;

Jednak w zmaterializowanym widoku musi istnieć unikalny indeks. Zamiast blokować zmaterializowany widok, tworzy tymczasowo zaktualizowaną wersję, porównuje dwie wersje, a następnie stosuje WSTAWIANIE i USUWANIE względem zmaterializowanego widoku, aby zastosować różnicę. Oznacza to, że zapytania mogą nadal korzystać ze zmaterializowanego widoku podczas jego aktualizacji. W przeciwieństwie do niejednorodnej formy, krotki nie są zamrożone i wymaga VACUUMing z powodu wyżej wymienionych USUŃ, które pozostawiają po sobie martwe krotki.

Ta współbieżna aktualizacja wciąż wykonuje pełne nowe zapytanie (nie przyrostowe). A zatem OBECNIE nie oszczędza całkowitego czasu obliczeń, po prostu minimalizuje czas, przez który widok zmaterializowany jest niedostępny do użycia podczas jego aktualizacji.