Materialized view в PostgreSQL

Статья основана на 25 видео из 30 тем курса SQL c 0 от Аристова Евгения. Ссылки на видео на платформах RUTUBE и VK видео.

В прошлой статье мы уже разобрали VIEW – представление, которое выполняется при каждом вызове. MATERIALIZED VIEW (материализованное представление) похоже на обычный VIEW, однако оно хранится на диске (то есть представляет из себя физический объект).

Синтаксис MATERIALIZED VIEW:

CREATE MATERIALIZED VIEW [ IF NOT EXISTS ] table_name
    [ (column_name [, …] ) ]
    [ USING method ]
    [ WITH ( storage_parameter [= value] [, … ] ) ]
    [ TABLESPACE tablespace_name ]
    AS query
    [ WITH [ NO ] DATA ]

Синтаксис очень похож на VIEW, но добавляется слово MATERIALIZED, также можно указать TABLESPACE, в котором будет храниться это материализованное представление.

MATERIALIZED VIEW строится один раз, а далее данные физически хранятся на диске. Важно, что при изменении или удалении таблиц, по которым было построено материализованное представление, оно само не изменяется (механизм автоматического обновления отсутствует).

Для обновления MATERIALIZED VIEW существует REFRESH:

REFRESH MATERIALIZED VIEW [ CONCURRENTLY ] name
    [ WITH [ NO ] DATA ]

При вызове REFRESH MATERIALIZED VIEW весь запрос выполняется заново. Во время обновления данные внутри материализованного представления недоступны.

При вызове REFRESH MATERIALIZED VIEW CONCURRENTLY обновляются только те данные, которые изменились в исходных таблицах. Данная операция затратна по времени и памяти, так как для данных создаются индексы. Подробнее о проблематике CONCURRENTLY в моей статье.

Практика

Создадим таблицу Склад:

CREATE TABLE warehouse (
    id serial UNIQUE,
    name text NOT NULL DEFAULT '',
    kolvo int NOT NULL DEFAULT 0,
    price numeric NOT NULL DEFAULT 0.0,
    salesTime timestamp with time zone NOT NULL DEFAULT current_timestamp, -- UTC
    salesTime2 timestamp NOT NULL DEFAULT current_timestamp
);

Добавим в неё значения:

INSERT INTO warehouse(name,kolvo,price) VALUES
    ('apple',10,10.2),
    ('grape',20,'20.2'),
    ('grape',100,'20.2');

Создадим обычный VIEW и вызовем его:

CREATE VIEW v AS
    SELECT name as n, sum(kolvo) from warehouse group by name;

SELECT * 
FROM v;

Создадим MATERIALIZED VIEW и вызовем его:

CREATE MATERIALIZED VIEW mv AS
    SELECT name as n, sum(kolvo) from warehouse group by name;

SELECT * 
FROM mv;

На данный момент результаты вызова MATERIALIZED VIEW и VIEW одинаковые.

Теперь добавим строчку в таблицу Склад:

INSERT INTO warehouse(name,kolvo,price) VALUES
    ('banana',10,10.2);

Вызовем обычный VIEW:

SELECT * 
FROM v;

В нём видим актуальные данные, так как он обновляется при каждом вызове.

Теперь вызовем MATERIALIZED VIEW:

SELECT * 
FROM mv;

В нём отсутствует новая строка, так как он не обновляется автоматически.

Обновим и вызовем:

REFRESH MATERIALIZED VIEW mv;

SELECT * 
FROM mv;

Теперь мы видим изменения.

Презентация к статье здесь.

26 из 30 тем будет скоро доступна. Если вы хотите быстрее получить доступ – присоединяйтесь к онлайн группе, ссылка доступна в описании курса.