Menu

Материализованные представления и VIEW в PostgreSQL

Представление (view) это сохранённый запрос, который выполняется при каждом чтении. Материализованное представление хранит результат запроса и меняется, только когда вы выполняете REFRESH MATERIALIZED VIEW.

На этой странице есть исполняемые редакторы: меняйте, запускайте и сразу видите результат.

Представление это сохранённый запрос, который читают как таблицу: CREATE VIEW name AS SELECT .... Оно не хранит данных, поэтому каждое чтение заново выполняет запрос и всегда видит актуальные строки. Материализованное представление, CREATE MATERIALIZED VIEW name AS SELECT ..., хранит результат на диске и меняется, только когда вы выполняете REFRESH MATERIALIZED VIEW name.

Заказ, вставленный после создания представления, есть в результате (ben с 90), потому что запрос представления выполнился в момент SELECT. Представления дают имя соединению или фильтру, который вы повторяете, скрывают столбцы от пользователей, которым их видеть не положено, и сохраняют работоспособность старых запросов, когда таблицы меняются.

Изменение и удаление представления

CREATE OR REPLACE VIEW подменяет запрос на месте. Новый запрос должен возвращать те же столбцы с теми же именами и типами в том же порядке; добавлять новые столбцы можно только в конец. Для любого другого изменения удалите представление и создайте его заново.

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

Вставка и обновление через представление

Представление над одной таблицей автоматически становится изменяемым, если в нём нет GROUP BY, DISTINCT, агрегатных и оконных функций, LIMIT и операций над множествами. Тогда INSERT, UPDATE и DELETE над представлением меняют таблицу. WITH CHECK OPTION отклоняет строки, которые представление после изменения не смогло бы показать:

Оба изменения дошли до products. Вставка (4, 'shelf', false) через представление падает с ошибкой new row violates check option for view "active_products", потому что представление её не покажет. Для представления, которое нельзя изменять, поведение записи можно задать триггером INSTEAD OF.

Создание и обновление материализованного представления

Материализованное представление выполняет свой запрос один раз и сохраняет строки. Чтение работает так же быстро, как чтение таблицы, но новые данные появляются только после обновления:

До обновления у ana по-прежнему 120, после него 180. REFRESH заново выполняет весь запрос и заменяет сохранённые строки. Используйте материализованное представление для результатов, которые дорого вычислять и которым допустимо отставать на минуты или часы: дашборды, отчёты, сводки для поиска.

CREATE MATERIALIZED VIEW ... WITH NO DATA создаёт его пустым, и чтение падает с ошибкой до первого обновления.

Обновление без блокировки чтения

Обычный REFRESH блокирует материализованное представление, и запросы, которые его читают, ждут окончания обновления. REFRESH MATERIALIZED VIEW CONCURRENTLY строит новый результат отдельно и применяет только различия, так что чтение продолжается. Для этого нужен уникальный индекс на материализованном представлении, по обычным именам столбцов и без WHERE:

Без уникального индекса обновление падает с подсказкой его создать. CONCURRENTLY может работать быстрее обычного обновления, когда меняется мало строк, и его нельзя использовать для материализованного представления, которое ещё ни разу не заполнялось. В любом случае одновременно выполняется только одно обновление материализованного представления. Остальные индексы работают как в таблице, поэтому индексируйте столбцы, по которым фильтруют ваши запросы.

Обновление по расписанию

PostgreSQL сам не обновляет материализованные представления. Запускайте обновление из планировщика, например через cron, который вызывает psql, или через расширение pg_cron, если оно установлено:

# 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');

Представление или материализованное представление

ПредставлениеМатериализованное представление
Хранит строкинетда
Актуальность данныхвсегдана момент последнего обновления
Скорость чтениякак у запросакак у таблицы
Индексынет (использует индексы таблиц)да
Можно изменятьпростые представления, данет

Остальные параметры, например параметры хранения и табличные пространства, перечислены в справочнике по CREATE MATERIALIZED VIEW.

Часто задаваемые вопросы

Обновляется ли материализованное представление в PostgreSQL автоматически?

Нет. Оно хранит результат последнего REFRESH MATERIALIZED VIEW (или момента создания), пока вы не обновите его снова. Запускайте обновление по расписанию через cron, расширение pg_cron или ваше приложение.

Представление быстрее, чем сам запрос?

Нет. Обычное представление не хранит данных; PostgreSQL разворачивает его в запрос, который его читает, поэтому оно работает с той же скоростью, что и запрос, написанный целиком. Работу экономит только материализованное представление, ценой устаревших данных.

Можно ли создать индекс на материализованное представление?

Да. Материализованное представление хранится как таблица, поэтому CREATE INDEX на нём работает. Уникальный индекс к тому же нужен для REFRESH MATERIALIZED VIEW CONCURRENTLY.

Как вывести список представлений и материализованных представлений?

В psql \dv выводит представления, а \dm материализованные представления. В SQL запрашивайте pg_views и pg_matviews.

Иллюстрация языков программирования Coddy

Учитесь программировать с Coddy

Начать