VIEW и Materialized View в PostgreSQL — создание и обновление

VIEW — сохранённый SELECT-запрос с именем. Materialized View — кэшированный результат запроса, хранящийся как таблица. Разбираем создание, обновление данных через WITH CHECK OPTION и когда использовать каждый тип.

Что такое VIEW и зачем он нужен

-- Сложный запрос который нужно переиспользовать:
SELECT u.id, u.email, COUNT(o.id) AS order_count, SUM(o.amount) AS total_spent
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE u.active = TRUE
GROUP BY u.id, u.email;

-- Создаём VIEW — сохраняем запрос под именем:
CREATE VIEW active_users_stats AS
SELECT u.id, u.email, COUNT(o.id) AS order_count, SUM(o.amount) AS total_spent
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE u.active = TRUE
GROUP BY u.id, u.email;

-- Теперь используем как обычную таблицу:
SELECT * FROM active_users_stats WHERE total_spent > 1000;
SELECT * FROM active_users_stats ORDER BY order_count DESC LIMIT 10;

-- VIEW не хранит данные — каждый SELECT выполняет запрос заново

Создание и изменение VIEW

-- Создать VIEW:
CREATE VIEW public_posts AS
SELECT id, title, created_at, user_id
FROM posts
WHERE status = 'published';

-- CREATE OR REPLACE — изменить существующий VIEW (без DROP):
CREATE OR REPLACE VIEW public_posts AS
SELECT id, title, slug, created_at, user_id
FROM posts
WHERE status = 'published'
ORDER BY created_at DESC;
-- Ограничение: нельзя удалить колонки или изменить их типы через OR REPLACE

-- Посмотреть определение VIEW:
\d+ public_posts
-- Или через SQL:
SELECT definition FROM pg_views WHERE viewname = 'public_posts';

-- Переименовать VIEW:
ALTER VIEW public_posts RENAME TO published_posts;

-- Удалить VIEW:
DROP VIEW public_posts;
DROP VIEW IF EXISTS public_posts;
DROP VIEW public_posts CASCADE;  -- удалить зависимые VIEW тоже

Обновляемые VIEW и WITH CHECK OPTION

Простые VIEW можно использовать для INSERT/UPDATE/DELETE — PostgreSQL транслирует операции на исходную таблицу:

-- Простой VIEW — автоматически обновляемый:
CREATE VIEW active_users AS
SELECT * FROM users WHERE active = TRUE;

-- Можно вставлять через VIEW:
INSERT INTO active_users (email, username) VALUES ('new@example.com', 'new');
-- Вставится в таблицу users с active = NULL (не TRUE!)

-- WITH CHECK OPTION — запретить запись строк вне условия VIEW:
CREATE VIEW active_users AS
SELECT * FROM users WHERE active = TRUE
WITH CHECK OPTION;

-- Теперь вставка строки с active = FALSE вернёт ошибку:
INSERT INTO active_users (email, active) VALUES ('bad@example.com', FALSE);
-- ERROR: new row violates check option for view "active_users"

-- WITH LOCAL CHECK OPTION — проверяет только этот VIEW (не дочерние):
CREATE VIEW premium_active AS
SELECT * FROM active_users WHERE premium = TRUE
WITH LOCAL CHECK OPTION;

-- WITH CASCADED CHECK OPTION — проверяет все VIEW в цепочке (по умолчанию):
CREATE VIEW premium_active AS
SELECT * FROM active_users WHERE premium = TRUE
WITH CASCADED CHECK OPTION;

Materialized View — кэшированные результаты

-- Создать Materialized View (вычисляет и сохраняет данные сразу):
CREATE MATERIALIZED VIEW monthly_stats AS
SELECT
  DATE_TRUNC('month', created_at) AS month,
  COUNT(*) AS order_count,
  SUM(amount) AS revenue,
  AVG(amount) AS avg_order
FROM orders
GROUP BY DATE_TRUNC('month', created_at)
ORDER BY month;

-- Данные хранятся в физическом хранилище — SELECT мгновенный
SELECT * FROM monthly_stats WHERE month >= '2024-01-01';

-- Обновить данные (пересчитать):
REFRESH MATERIALIZED VIEW monthly_stats;
-- Блокирует чтение на время обновления!

-- Обновить без блокировки чтения (нужен уникальный индекс):
CREATE UNIQUE INDEX ON monthly_stats(month);
REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_stats;
-- Читатели получают старые данные пока идёт обновление

-- Создать без начальных данных (заполнить позже):
CREATE MATERIALIZED VIEW monthly_stats AS
SELECT ...
WITH NO DATA;
-- После этого: REFRESH MATERIALIZED VIEW monthly_stats;

VIEW vs Materialized View — сравнение

Автоматическое обновление через cron

-- Создать функцию для обновления:
CREATE OR REPLACE FUNCTION refresh_monthly_stats()
RETURNS void AS $$
BEGIN
  REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_stats;
END;
$$ LANGUAGE plpgsql;

-- Вызов из SQL (например из pg_cron):
SELECT refresh_monthly_stats();

-- Если установлен pg_cron:
SELECT cron.schedule('refresh-stats', '0 * * * *',  -- каждый час
  'REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_stats');

-- Посмотреть задачи pg_cron:
SELECT * FROM cron.job;

-- Список всех materialized views:
SELECT matviewname, hasindexes, ispopulated
FROM pg_matviews
WHERE schemaname = 'public';

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

В чём разница между VIEW и таблицей в PostgreSQL?

VIEW — это сохранённый запрос без хранилища данных. При каждом обращении к VIEW PostgreSQL выполняет исходный SELECT заново. Таблица хранит данные физически. Преимущества VIEW: всегда актуальные данные, не требуют места на диске, можно ограничить доступ к колонкам (скрыть пароли, выдав пользователю права только на VIEW). Недостаток: тяжёлый запрос в основе VIEW выполняется каждый раз — для сложной аналитики лучше Materialized View.

Как часто обновлять Materialized View?

Зависит от требований к свежести данных и нагрузки на базу. Типичные стратегии: 1) Раз в час/день через pg_cron для аналитических дашбордов. 2) После каждой значимой операции через триггер (осторожно с частотой). 3) По запросу через API-ендпоинт когда нужны свежие данные. Используйте REFRESH CONCURRENTLY чтобы не блокировать читателей — для этого нужен уникальный индекс на materialized view. Обычный REFRESH блокирует SELECT на всё время обновления.

Можно ли создать INDEX на VIEW в PostgreSQL?

На обычный VIEW — нет, индексы не нужны потому что VIEW не хранит данные. Индексы создаются на исходных таблицах и автоматически используются при выполнении запроса VIEW. На Materialized View — да, можно создавать любые индексы как на обычной таблице: CREATE INDEX ON monthly_stats(month);. Уникальный индекс на Materialized View дополнительно позволяет использовать REFRESH CONCURRENTLY.