Widok to zapisane zapytanie, które czytasz jak tabelę: CREATE VIEW name AS SELECT .... Nie przechowuje danych, więc każdy odczyt uruchamia zapytanie od nowa i zawsze widzi bieżące wiersze. Widok zmaterializowany, CREATE MATERIALIZED VIEW name AS SELECT ..., przechowuje wynik na dysku i zmienia się tylko po uruchomieniu REFRESH MATERIALIZED VIEW name.
Zamówienie wstawione po utworzeniu widoku jest w wyniku (ben z 90), bo zapytanie widoku wykonało się w chwili SELECT. Widoki nadają nazwę złączeniu albo filtrowi, które często powtarzasz, ukrywają kolumny przed użytkownikami, którzy nie powinni ich widzieć, i utrzymują stare zapytania przy życiu, gdy tabele się zmieniają.
Zmiana albo usunięcie widoku
CREATE OR REPLACE VIEW podmienia zapytanie na miejscu. Nowe zapytanie musi zwracać te same kolumny, o tych samych nazwach i typach, w tej samej kolejności; może tylko dodać nowe kolumny na końcu. Przy każdej innej zmianie usuń widok i utwórz go od nowa.
CREATE OR REPLACE VIEW paid_totals AS
SELECT customer, sum(amount) AS total, count(*) AS orders
FROM orders WHERE paid GROUP BY customer;
DROP VIEW paid_totals;
DROP VIEW IF EXISTS paid_totals CASCADE; -- also drops views built on it
Wstawianie i aktualizacja przez widok
Widok na jednej tabeli jest automatycznie modyfikowalny, jeśli nie ma GROUP BY, DISTINCT, agregatów, funkcji okna, LIMIT ani operacji na zbiorach. INSERT, UPDATE i DELETE na widoku zmieniają wtedy tabelę. WITH CHECK OPTION odrzuca wiersze, których widok nie mógłby potem pokazać:
Obie zmiany trafiły do products. Wstawienie (4, 'shelf', false) przez widok kończy się błędem new row violates check option for view "active_products", bo widok by go nie pokazał. Dla widoku, który nie jest modyfikowalny, wyzwalacz INSTEAD OF może określić, co robi zapis.
Tworzenie i odświeżanie widoku zmaterializowanego
Widok zmaterializowany uruchamia zapytanie raz i przechowuje wiersze. Odczyty są tak szybkie jak z tabeli, ale nowe dane pojawiają się dopiero po odświeżeniu:
Przed odświeżeniem ana nadal pokazuje 120, po nim 180. REFRESH uruchamia całe zapytanie od nowa i zastępuje zapisane wiersze. Używaj widoku zmaterializowanego do wyników, które drogo się liczą i mogą mieć minuty albo godziny: dashboardów, raportów, podsumowań do wyszukiwania.
CREATE MATERIALIZED VIEW ... WITH NO DATA tworzy go pustego, a odczyt kończy się błędem aż do pierwszego odświeżenia.
Odświeżanie bez blokowania odczytów
Zwykłe REFRESH blokuje widok zmaterializowany, więc zapytania, które z niego czytają, czekają do końca odświeżania. REFRESH MATERIALIZED VIEW CONCURRENTLY buduje nowy wynik osobno i nanosi tylko różnice, więc odczyty działają dalej. Wymaga unikalnego indeksu na widoku zmaterializowanym, na zwykłych nazwach kolumn i bez WHERE:
Bez unikalnego indeksu odświeżanie kończy się błędem z podpowiedzią, żeby go utworzyć. CONCURRENTLY może być szybsze od zwykłego odświeżania, gdy zmienia się niewiele wierszy, i nie da się go użyć na widoku zmaterializowanym, który nigdy nie został wypełniony. W każdym przypadku naraz działa tylko jedno odświeżanie danego widoku. Inne indeksy działają jak na tabeli, więc indeksuj kolumny, po których filtrują twoje zapytania.
Odświeżanie według harmonogramu
PostgreSQL sam nie odświeża widoków zmaterializowanych. Uruchamiaj odświeżanie z harmonogramu, na przykład przez cron wywołujący psql, albo przez rozszerzenie pg_cron, jeśli jest zainstalowane:
# crontab: every 15 minutes
*/15 * * * * psql "$DATABASE_URL" -c 'REFRESH MATERIALIZED VIEW CONCURRENTLY customer_totals'
SELECT cron.schedule('refresh-totals', '*/15 * * * *',
'REFRESH MATERIALIZED VIEW CONCURRENTLY customer_totals');
Widok czy widok zmaterializowany
| Widok | Widok zmaterializowany | |
|---|---|---|
| Przechowuje wiersze | nie | tak |
| Aktualność danych | zawsze aktualne | stan z ostatniego odświeżenia |
| Szybkość odczytu | jak zapytanie | jak tabela |
| Indeksy | nie (używa indeksów tabel) | tak |
| Zapis | proste widoki tak | nie |
Dokumentacja CREATE MATERIALIZED VIEW wymienia pozostałe opcje, takie jak parametry przechowywania i przestrzenie tabel.
Najczęściej zadawane pytania
Czy widok zmaterializowany w PostgreSQL aktualizuje się automatycznie?
Nie. Trzyma wynik z ostatniego REFRESH MATERIALIZED VIEW (albo z utworzenia), dopóki nie odświeżysz go ponownie. Zaplanuj odświeżanie przez cron, rozszerzenie pg_cron albo w aplikacji.
Czy widok jest szybszy niż samo zapytanie?
Nie. Zwykły widok nie przechowuje danych; PostgreSQL rozwija go w zapytaniu, które z niego czyta, więc działa tak samo szybko jak wypisane zapytanie. Tylko widok zmaterializowany oszczędza pracę, kosztem nieaktualnych danych.
Czy można dodać indeks do widoku zmaterializowanego?
Tak. Widok zmaterializowany jest przechowywany jak tabela, więc CREATE INDEX na nim działa. Unikalny indeks jest też dokładnie tym, czego wymaga REFRESH MATERIALIZED VIEW CONCURRENTLY.
Jak wypisać widoki i widoki zmaterializowane?
W psql \dv wypisuje widoki, a \dm widoki zmaterializowane. W SQL odpytaj pg_views i pg_matviews.